Archive

Archive for June, 2010

Summary SmartLists in Builder (Sales by Item for a date range, etc)

June 3, 2010 3 comments

One of the great features of SmartList builder is the ability to create a summary SmartList. Great for Sales reporting as well as many other uses. Here are some tips & tricks to get your summary SmartLists up and running:

  • Build your SmartList like normal.  Add your desired tables and restrictions.  Check your data.
  • Some restrictions to think about:
    • Voided records
    • Only include certain Document Types  (In Sales Order processing you typically only want to include SOP Type =3 (Invoice) and SOP Type = 4 (Return) )
  • Make sure you have the “proper” signs on numbers you will be adding up… for example you will want Returns or Credits to be negative.   You will need to add calculate fields for these.  See my post Transaction Amounts with correct signs
  • If you want Quantities make sure you are using the same Unit of Measure.  See my post Item Quantities, Unit of Measure and Base U of M
  • Uncheck in the Display column ALL fields for ALL tables (select the little arrow by Display for the option to Unmark all)
  • Now only check in the Display & Default column for fields that you want to:
    • Group by - for each value it will make a new row on your SmartList  (The “by” in your title)
    • Sum – add up values in this field for all records with the same values in the Group by field
    • Other options to consider:
      • Max or Min  – for fields that MIGHT have different values but you DON’T want separate rows
      • Count – great for fields that you want users to be able to enter a search criteria on – but you Don’t want to Group by.  (Don’t check these fields as Display options – the data will actually be a count of records and will confuse your users!)

Let me point that out again with an example.  It is an important “trick”:  
You want to make a SmartList for Sales by item for ANY date range. 
You want one row returned for Each item with total Sales $ and Qty for that date range.   

Group by fields – Item, Item Description
Sum fields — Qty and Ext Price (Calculated fields for sign correction)
Count field — Document Date and/or GL Posting Date 

This trick works because SmartList Builder sends the query into SQL with the restrictions THEN group by’s / sum’s the data.  

blockquote>

  • Press the Options Button on the top
    • Check the Summary box
    • Click on the first table on the left
    • Listed on the right you will see the fields you selected in the prior step, double-click on a field to select
    • Press the dropdown to select the correct summary method (Group by, Sum, etc.) for each field and press Save
    • Then continue with each of your tables on the left including the Calculated Fields
  • Load your SmartList and check your data.
  • And when your users ask for a new SmartList for Sales by Customer by Item for a date range, use you the copy feature to copy this SmartList (Options on Top menu bar > Duplicate), check Customer ID & Customer Name fields as Default & Display and change the Summary options to add them a Group By fields and you are done!

    Happy Building,
    Devon

Transaction Amounts with correct signs (What you want Credit memos to be negative?)

Isn’t it irritating that for most Transactions (all in Payables Management & Receivables Management) are a “positive” number even Credit Memos and Payments?   Don’t you wish you could export GP SmartList to Excel, run the Subtotal feature and get a REAL total?  

Here is how to “fix” this in the Builders (SmartList, Excel & Navigational Lists):

  • Determine what field you can use to decide if the value should be negative or positive - your “type field” (Document Type for Payables; RM Document Type-ALL for Receivables; SOP Type for Sales Order Processing) 
  • Verify the field data type   (Press the Show Details (down chevrons) on the far left of the field header line, Now you should be seeing the Data Type on the second row under each field name)
    • If the Data Type is not Dropdown list (or Yes/No)  you will need to do a calculated field – scroll down… for Option #2
    • If the Date Type is Dropdown list (or Yes/No), then this field will be available in the next step.  But only if you have set the dropdown field options. See my post Drop Down Lists in SmartList Builder for details
  • Select the amount field that you want to correct the sign by clicking on the Display Name of that field
  • Press the Blue Arrow between Display Name and Display on the Field header line to launch the Set Field Options window. (This window changes its appearance based upon the data type for the field.)
  • Press the Negative values tab
  • Select your “type field” from above
  • Check Mark the boxes for the type values that should be negative

Note – these values are only used for displaying in your List.  If you want to do a Summary list – these sign corrections will not be used – you will need to do a Calculated Field.

Option #2 – A Calculated Field to correct the sign of a field:

Here we are going to use our handy-dandy Case Statement:  (For more info see Try CASE )

CASE {type field}
WHEN 4 THEN – {amount field}
WHEN 5 THEN – {amount field}
ELSE {amount field}
END

or

CASE
WHEN {type field} >=7 THEN – {amount field}
ELSE {amount field}
END

Uncheck the Default & Display boxes for the original amount field and check the Default & Display boxes for your new Calculated field so that users see this new field instead.

Works like a charm.

Happy building,
Devon

Follow

Get every new post delivered to your Inbox.

Join 25 other followers