Formula Syntax

[zmcanally]

I am building a report based on retail sales data. The end user wants a report that shows 'negative' transaction by cashiers (ie: cancels, voids, no sales, etc) which each has a unique detail_type ID in the sales table. My question is, I cannot seem to get the counts to display. I nned a total count of each detail type. The SQL syntax would be someting similar to "Select Count (*) From PDETAIL Where DETAIL_TYPE = x" - how to I translate this into a report field? Thanks - Zack

 

[DocFKNx]

Create a Group on DETAIL_TYPE and then create a Running Total field that counts some other field (like ROW ID if you have one or employee name etc). Have the group reset on every change of DETAIL_TYPE.

If I understand your issue correctly that would do it I think.

X

 

[GJParker]

The easiest way is to group by Detail type on your report and create a summary count field in the group header or footer

Hope this helps Gary Parker
Systems Support Analyst

 

[zmcanally]

Thanks guys - That did the trick

Zack

 

[zmcanally]

Oops - - I jumped the gun. That method works fine, but I need to group by something else first. To give you the big picture, this report will show all 'negative' transactions by stores and cashiers. The first grouping will be stores, then a drill-down to the store's cashiers. The negative transactions are stored in detail type (ie: 4 = returns, 5 = cancel sales, etc) The report should show the following: Store Number - # of total negatives - # of cancel sales - # of returns - # of voids - - - > (Drill down to cashier) Cashier Name - # of total negatives - # of cancel sales - # of returns - # of voids. Any suggestions on how to accomplish this? - Thanks again for your help

 

[GJParker]

That's fine Just add the same count summary calculations into both group sections Gary Parker
Systems Support Analyst

 

[zmcanally]

I must have missed something, because If I group by the detail_type field, I cannot get the summary data I need. I need to group by Store and then by cashier in order to get the visual look the customer wants, but I cannot do that and group by detail type. Thanks for your patience - Zack