How do I drill into a report footer summary value to display all detail data?

[PremHealth1]

HI, I'm using Crystal Reports 2016 connected to SQL Server.

Part 1
I made a simple that looks like this...

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...

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...

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...

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!

 

[lbass]

A subreport will work. It sounds like you are linking the subreport on your groupfield, so remove that link, and then all records should appear. If you have parameters, you should also create the parameters in the subreport and then link on the parameters so that the sub and main report have the same selection formula. When linking the parameters, be sure to use the drop down in the subreport linking screen and choose {?yourparm} instead of the default {?pm-?yourparm} to link with the subreport parm.

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

 

[PremHealth1]

As an FYI my main report gets data via SQL in a Command. Four parameters are prompted for when the main report runs. The user is prompted for a begin date, begin date offset, end date and end date offset (begin date and end date parameters are drop downs with options like year-begin, year-end, month-begin, month--end, etc.) The user does not enter an actual date anywhere. The top of the Command populates date variables (calculated from the entered parameters) and those date variables are used in the actual SQL lower in the Command to return data from the database. This is standard for all reports.

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

 

[lbass]

Save your existing report under a new name, by adding "sub" as a suffix (just so you can distinguish it). Then when you insert a subreport, choose "existing subreport" and select the newly renamed report. Then go into the Subreport linking expert and connect the parms so that you are not prompted twice. Proceed to link as I described earlier. You can also (if you wish), then remove the group within the subreport.

-LB

 

[PremHealth1]

OK I added the sub-report from an external report file into the main report. Then I linked them and I didn't link on any of the ?Pm parameters I used my parameters as you stated. Each time I click on the sub-report link though it's hitting the database and running the same query as the main report did. Is that unavoidable or have I done something wrong?

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

 

[lbass]

That's the best you can do with a subreport, since you are asking for the same data to be presented in two different ways.

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