How do I drill into a report footer summary value to display all detail data?
How do I drill into a report footer summary value to display all detail data?
(OP)
HI, I'm using Crystal Reports 2016 connected to SQL Server.
Part 1
I made a simple that looks like this...
Part 2
Then I grouped by Procedure Code and get this...
Part 3
So my preview pain looks like this...
Part 4
Then I added a summary of Insurance Payments at the Report footer level so my preview pain now looks like...
In the above in Part 3 or Part 4 a user can drill on ABC or DEF to open a new tab that will display the related detail rows from Part 1. What I want the user to be able to do is drill on 6,350.00 in Part 4 so a new tab displays that shows all detail rows for all Procedure Codes (all detail rows from Part 1).
While researching this I saw a post stating that you can't put a drill on a Report Footer object. That made me think the only way to do this is add a sub-report (hopefully an On-Demand sub-report). My intent was to pass all the detail data from the main report to the sub-report without having the sub-report query the database again.
- I attempted to create an on-demand sub-report multiple times but all I could ever get was the last row from Part 3 to display.
- I attempted to use shared variables but again same kind of problem.
Is there a way to do this without running a second query?
Thanks!
Part 1
I made a simple that looks like this...
CODE -->
Page header row in design mode: Procedure Name Procedure Code Insurance Payment Detail row in design mode: Hip Replacement ABC 50.00 Detail row in design mode: Hip Replacement ABC 100.00 Detail row in design mode: Hip Replacement ABC 200.00 Detail row in design mode: Broken Arm DEF 1000.00 Detail row in design mode: Broken Arm DEF 2000.00 Detail row in design mode: Broken Arm DEF 3000.00
Part 2
Then I grouped by Procedure Code and get this...
CODE -->
Page header row in design mode: ...row is suppressed. Group header row in design mode: ...row is suppressed. Detail row in design mode: ...row is suppressed. Group footer #1 row in design mode: Group #1 Procedure Code Insurance Payment Report footer row in design mode: ...row is suppressed. Page footer row in design mode: ...row is suppressed.
Part 3
So my preview pain looks like this...
CODE -->
Group footer #1 row 1 in preview mode: ABC 350.00 Group footer #1 row 2 in preview mode: DEF 6000.00
Part 4
Then I added a summary of Insurance Payments at the Report footer level so my preview pain now looks like...
CODE -->
Group footer #1 row 1 in preview mode: ABC 350.00 Group footer #1 row 2 in preview mode: DEF 6000.00 Report footer row 3 in preview mode: 6,350.00
In the above in Part 3 or Part 4 a user can drill on ABC or DEF to open a new tab that will display the related detail rows from Part 1. What I want the user to be able to do is drill on 6,350.00 in Part 4 so a new tab displays that shows all detail rows for all Procedure Codes (all detail rows from Part 1).
While researching this I saw a post stating that you can't put a drill on a Report Footer object. That made me think the only way to do this is add a sub-report (hopefully an On-Demand sub-report). My intent was to pass all the detail data from the main report to the sub-report without having the sub-report query the database again.
- I attempted to create an on-demand sub-report multiple times but all I could ever get was the last row from Part 3 to display.
- I attempted to use shared variables but again same kind of problem.
Is there a way to do this without running a second query?
Thanks!
RE: How do I drill into a report footer summary value to display all detail data?
Another approach would be to create an outer group that includes all records. Create a formula like:
"All"
...and then group on it and make it your outer group. Then drill down will be available. However, then detail records will still be clustered within your Group #2 field. You could make the grouping conditional, by grouping on a formula with a parameter:
If {?GroupBy}="No Group" then
"All" else
If {?GroupBy}="Group by Code" then
{table.procedurecode}
...but the report would then have to be run twice to see results each way, so the subreport route seems the best approach.
-LB
RE: How do I drill into a report footer summary value to display all detail data?
When I select insert/sub-report I'm prompted to select a data provider. I choose Command but what am I supposed to put in here?
It won't let me put nothing so I put in "SELECT CURRENT_TIMESTAMP" to get past this screen and then from the Field Explorer I created parameters that matched the main report. But I have no fields to add to the sub-reports output. Makes sense as the command returns nothing. I was hoping the sub-report based upon some linkage existing might just make available the fields in the main report.
- Did the above but put the complete SQL and parameters from the main report in the sub-report. This worked but the sub-report is running the entire query a second time when I click on the sub-report link. I was wanting to avoid hitting the database twice.
Am I missing something from what you described?
Thanks
RE: How do I drill into a report footer summary value to display all detail data?
-LB
RE: How do I drill into a report footer summary value to display all detail data?
I guess it doesn't matter but is this the only way to do this (didn't plan on having a second external sub-report file)? Is it not possible to have a sub-report embedded in a report accessing the same data as the main report?
Thanks
RE: How do I drill into a report footer summary value to display all detail data?
A better solution is the one I mentioned earlier, only I realized I forgot something crucial. Create a formula:
Whilereadingrecords;
"Total"
Insert a group on this and then go into design mode and click on the new GH2 in the gray area to the left of the report and drag it so that it is now Group 1.
Next, go into the section expert and unselect "hide" for the detail section and instead go into details->suppress (don't check it)->x+2 and enter:
Drilldowngrouplevel=0
Then you will see all details if you double click on "Total" or just those in any group you click on.
Another approach would be to add a crosstab in the report footer that uses a unique id as the Row field and then the procedure code as a second id field (if you wish) and then the amount as the summary field.
-LB