Tuesday, March 5, 2013

Dynamic Detailed Summary in Report?


Need something like this? At the bottom of the report, a detailed summary based on the entries on the report? 



Surprisingly, this is very easy to do if you know the knobs to twist.   The process and tricks involved is in creating a cursor for your report, generating another cursor out of the first cursor to hold the summary, and finally seeding those back to the original cursor.

Another is by employing grouping to break presentation of the data.  Study the sample data:




* Create a cursor for the report and seed it
CREATE CURSOR JunkReport (glcode c(30), amount i, xgroup i)
INSERT INTO JunkReport VALUES ('L04201 Savings ',10,1)
INSERT INTO JunkReport VALUES ('L04301 Current ',55,1)
INSERT INTO JunkReport VALUES ('L04201 Savings ',20,1)
INSERT INTO JunkReport VALUES ('L04301 Current ',8,1)
INSERT INTO JunkReport VALUES ('L04401 Fixed Deposit ',26,1)
INSERT INTO JunkReport VALUES ('L04401 Fixed Deposit ',29,1)
INSERT INTO JunkReport VALUES ('L04201 Savings ',330,1)
INSERT INTO JunkReport VALUES ('L04301 Current ',12,1)
INSERT INTO JunkReport VALUES ('L04401 Fixed Deposit ',2,1)
INSERT INTO JunkReport VALUES ('L04301 Current ',98,1)

* Create a summary
SELECT glcode, SUM(amount) as amount , 2 as xgroup FROM JunkReport GROUP BY 1 INTO CURSOR JunkSummary NOFILTER

* insert records of that back into original cursor
SELECT JunkReport
APPEND FROM DBF('JunkSummary')
USE IN SELECT('JunkSummary')

* Run report
REPORT FORM .....



Okay, we now have the cursor containing both details and the summary.  Now, for it to be shown properly in the report, create DATA GROUPING.  Observe in the cursor above, I created a field xGroup just for that purpose of breaking the presentation of the details and summary. We will now use it like this:


Finally your report should look something like this:


And there you are! A very easy way of creating Dynamic Summary for your report.  Although we really never use any footer or summary band for that!  Cheers!





No comments:

Post a Comment