Hi,
In my scenario, the dex report it sum all the line item and retrieve the values in the calculated field which is present in report footer(Display type: SUM). But when i place the same calculated field in the report header it does not calculate the value why? Please advice how to achieve this.
*This post is locked for comments
Hi Pragadees
The GP Report writer is a single pass report writer. If you put a sum in a header the value will be 0 as it has yet to cycle through the body records.
Sum fields will always have to be in the footer.
The only alternatives to get it in the header are for you to use VBA with ADO to SQL to sum up the data so it can be placed in the header, or use a custom RW_Function with the Support Debugging Tool or GP Power Tools to sum up the data for you and return it to a calculated fields.
Examples:
http://blogs.msdn.com/b/developingfordynamicsgp/archive/2008/10/30/using-vba-with-report-writer.aspx
David
Hi,
The reason is because RW is a "single pass" report writing tool.
For example, let's say you have a report that will generate 10 records (in the body). RW 1st generates the RH/PH sections and at this time it has no idea how many records are in the body and therefore wouldn't be able to SUM those records.
Only as RW starts printing the records in body section does it know that those records exist. As a visual add, it might help if you add your calc field into the body section of the report and make it a SUM. It actually will print like a "Running Sum". This is because when it prints the 1st record...this is the only record it knows about. And when it prints the 2nd record...then it only knows about these 1st 2 records...etc...
The way around this is:
1.) Use VBA to run a SQL statement that gets you the correct value.
2.) Use DEX to get the desired value.
Thanks,
Justin
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (