Source language: Translate to:

Creating Summary Report

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Creating Summary Report

Postby BRobinsonS » Thu Nov 20, 2014 6:09 pm

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.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Creating Summary Report

Postby Gaev » Fri Nov 21, 2014 7:45 am

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.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Creating Summary Report

Postby Neosoft Support » Fri Nov 21, 2014 11:54 am

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.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Creating Summary Report

Postby BRobinsonS » Fri Nov 21, 2014 4:51 pm

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)?
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Creating Summary Report

Postby BRobinsonS » Sat Nov 22, 2014 11:48 am

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.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Creating Summary Report

Postby Neosoft Support » Sat Nov 22, 2014 12:52 pm

You do have to have a field to base the calculation on, but it sounds like you found another solution.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Creating Summary Report

Postby BRobinsonS » Sat Nov 22, 2014 6:02 pm

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"
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 4 guests