beyond count2

[dnabag]

My goal is to generate a summary report which totals the number of transactions that have occured - from only those locations where transactions actually took place.

Currently I'm using a 'static' report which lists all 100 of our locations, and using count2 formula ie "Location 1 = COUNT2("location1",'LOCATION')" to summarize the number of transactions.

As we have transactions occuring in some locations and not others each day, the result of the above is a long list of locations with '0' as the result for most.

My preference would be to have a 'dynamic' report generated which shows only those locations that had transactions, and the the number they had.

Any help would be appreciated.

[Barry]

My goal is to generate a summary report which totals the number of transactions that have occured - from only those locations where transactions actually took place.

Currently I'm using a 'static' report which lists all 100 of our locations, and using count2 formula ie "Location 1 = COUNT2("location1",'LOCATION')" to summarize the number of transactions.

As we have transactions occuring in some locations and not others each day, the result of the above is a long list of locations with '0' as the result for most.

My preference would be to have a 'dynamic' report generated which shows only those locations that had transactions, and the the number they had.


Any help would be appreciated.

Here's another approach you could try:

Create two Summary type fields
'LOCsum' formula: 'LOCATION'
'TRANsum' formula: COUNT('LOCATION')

Create a New Layout... Blank
Insert a New Part...Subsummary when sorted by 'LOCATION'

Insert both new Summary type fields above, and place them side by side in the Subsummary part of the new layout.

Remove the Body part of the layout by dragging the Body boundary to the top of the window.

To view, Sort the DB by 'LOCATION', and make sure you have Page view checked for this layout. You should get a sorted list of locations which had transactions during the period, and a count of the number of transactions recorded for each.

Regards,
Barry

[dnabag]

Here's another approach you could try:

Create two Summary type fields
'LOCsum' formula: 'LOCATION'
'TRANsum' formula: COUNT('LOCATION')

Create a New Layout... Blank
Insert a New Part...Subsummary when sorted by 'LOCATION'

Insert both new Summary type fields above, and place them side by side in the Subsummary part of the new layout.

Remove the Body part of the layout by dragging the Body boundary to the top of the window.

To view, Sort the DB by 'LOCATION', and make sure you have Page view checked for this layout. You should get a sorted list of locations which had transactions during the period, and a count of the number of transactions recorded for each.

Regards,
Barry

Thank you Barry. This alternative will suffice nicely. But now I can't figure out how to show the '% of total' as I could with the grand summary items.

Specifically, using your suggestion, I now have:

Location 1 - 5
Location 2 - 5
Location 3 - 10

for each of my locations just like I wanted.

But I would like one more field reflecting % of total transactions:

Location 1 - 5 - 25%
Location 2 - 5 - 25%
Location 3 - 10 - 50%

When I use the formula that works in my grand summary section, I get 100% for each of the locations. I presume this is because it's dividing itself by itself since it's a sub-summary count.

Any chance you can save me from hours of trial and error?

Thanks again!

[Barry]

Thank you Barry. This alternative will suffice nicely. But now I can't figure out how to show the '% of total' as I could with the grand summary items.

Specifically, using your suggestion, I now have:

Location 1 - 5
Location 2 - 5
Location 3 - 10

for each of my locations just like I wanted.

But I would like one more field reflecting % of total transactions:

Location 1 - 5 - 25%
Location 2 - 5 - 25%
Location 3 - 10 - 50%

When I use the formula that works in my grand summary section, I get 100% for each of the locations. I presume this is because it's dividing itself by itself since it's a sub-summary count.

Any chance you can save me from hours of trial and error?

Thanks again!

Your analysis sounds correct. I'm assuming you are using COUNT() in a grand summary to get a total number of transactions. Used in a sub summary, this will count only the transactions reported in the part of the database summarized in that subsummary.

I see two major problems in achieving what you want to have: Summary fields in Subsummary parts consider only the data in a subsection of the DB; and results of Summary fields cannot be used in further calculations.

Using the DB tools, the only way I see to get % of total results is to use your original plan--a grand summary reporting results from each location, which will, of course, report results from all locations, including those with no transactions during the report period.

That said, it might be possible to write an AppleScript that will extract the date you want, and that will not report zero transaction locations. Realizing that possibility is beyond my meagre AppleScript talents, though, so I'll throw it out to others.

Regards,
Barry

[dnabag]

Your analysis sounds correct. I'm assuming you are using COUNT() in a grand summary to get a total number of transactions. Used in a sub summary, this will count only the transactions reported in the part of the database summarized in that subsummary.

I see two major problems in achieving what you want to have: Summary fields in Subsummary parts consider only the data in a subsection of the DB; and results of Summary fields cannot be used in further calculations.

Using the DB tools, the only way I see to get % of total results is to use your original plan--a grand summary reporting results from each location, which will, of course, report results from all locations, including those with no transactions during the report period.

That said, it might be possible to write an AppleScript that will extract the date you want, and that will not report zero transaction locations. Realizing that possibility is beyond my meagre AppleScript talents, though, so I'll throw it out to others.

Regards,
Barry

Many thanks to you Barry for saving me countless hours of tail chasing!