Creating Summary Report

[BRobinsonS]

I have a membership program which has 4 categories of members.
I would like to get a summary report which counts the number in each category and shows as a report.
I don' t want the names listed only the total number in each category.
I do have a report but it lists all the names too. Not what I want.

Example:
Life 7
Ordinary 120
Associate 240
Affiliate Voting 45
Affiliate Non-Voting 5

Total: 417

Can't seem to think how to do it.

Alternately, I could have a popup which gives me this information.

[Gaev]

Brian:

One way to achieve this would be to ...

1) do a dbpQuery for each of the 4 (or 5 ?) categories ... after each query, [ID.Table.$RecCount] will give you the number of matching records

Note that you can also get this count directly using dbpExecSQL e.g. ...

Code: Select all

SELECT COUNT(CategoryName) AS RecordsInLifeCategory FROM yourTableName
WHERE CategoryName=Life;

2) If you want to just display these numbers, you can make up a Simple Text object with [variables] ... and copy the counts to each of them

If you need a printed report, you can make up a dummy Table ... with two fields (CategoryName and RecordCount) ... add one record for each Query in (1) above ... then do a simple report of this Table.

[Neosoft Support]

The Report Designer should be able to accomplish this, but you would need to design the report with the correct partitions. Then it is absolutely critical that you sort the table on the proper field (categories?) BEFORE running the report to get the correct summary information.

[BRobinsonS]

My first attempts with Report Designer seemed that the Data band required a field to calculate the count.
Is there a way to not print or not include the data band and therefore only print the Group Footer (with the total count)?

[BRobinsonS]

I went the Query Route and saved the results to variables.
I just used a Report with Page header and displayed the Categories and Variable Count to the page as well as I used Math to calculate the sum for display.
I did not use bands.

[Neosoft Support]

You do have to have a field to base the calculation on, but it sounds like you found another solution.

[BRobinsonS]

The bands I used was Page Header, Page Footer. And Placed the Labels e.g. LF = [LFNumber] on the space between Header and Footer.
Seemed to work.

.LF Count
dbpQuery "AddrBook" "Contacts" "MemType=[#34]LF[#34]and ActiveStatus = True"
SetVar "[LFNumber]" "[AddrBook.Contacts.$RecCount]"
dbpShowAll "AddrBook" "Contacts"
.ordinary count
dbpQuery "AddrBook" "Contacts" "MemType=[#34]OR[#34]and ActiveStatus = True"
SetVar "[OrdNumber]" "[AddrBook.Contacts.$RecCount]"
dbpShowAll "AddrBook" "Contacts"
.associate count
dbpQuery "AddrBook" "Contacts" "MemType=[#34]AS[#34]and ActiveStatus = True"
SetVar "[ASNumber]" "[AddrBook.Contacts.$RecCount]"
dbpShowAll "AddrBook" "Contacts"
.Affiliate Voting count
dbpQuery "AddrBook" "Contacts" "MemType=[#34]AV[#34]and ActiveStatus = True"
SetVar "[AVNumber]" "[AddrBook.Contacts.$RecCount]"
dbpShowAll "AddrBook" "Contacts"
.Affiliate Non-Voting count
dbpQuery "AddrBook" "Contacts" "MemType=[#34]AN[#34]and ActiveStatus = True"
SetVar "[ANNumber]" "[AddrBook.Contacts.$RecCount]"
dbpShowAll "AddrBook" "Contacts"

Math "[LFNumber]+[OrdNumber]+[ASNumber]+[AVNumber]+[ANNumber]" "0" "[TotalNumber]"

dbpPreviewReport "AddrBook" "MemberTypeSummary.dbr" "DisplayMode=FitWidth;InitialPage=1"
.dbpUnSort "AddrBook" "Contacts"
dbpShowAll "AddrBook" "Contacts"