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

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