Skip to Content

There are some report developers who want to be able to have the end user open or close a section in a report by clicking on a button / icon. This would allow the end user to drill from a group into its details without moving to another tab or without eliminating the other group headers from the view. If you are working with BusinessObjects Enterprise or Crystal Reports Server, then this blog may help you out.
For example, the end user has already opened the group for Brazil,

/wp-content/uploads/2014/01/3_374501.jpg

 

and now they close the Brazil info by clicking on the ‘-‘ icon. Note that the group for Canada stays closed through this process and all other group headers are still shown.

/wp-content/uploads/2014/01/2_374500.jpg

 

Although you can’t do this directly in the Crystal Reports designer, you can create a report to use in your server environment that uses OpenDocument as well as a parameter / prompt to reopen the same report, basically keeping track of what is supposed to be open and what is supposed to be closed.

 

I have attached a sample report (DrillInPlaceUsingURLParameters which is below) so that you can copy the main formula from the sample to your report. This formula is already set up for a report where you are opening or closing a Group Header 1 B section. You would then just change a couple of values in the formula to adapt the technique to your report. To see how the report is going to work, extract the contents of the attachment to this blog post and change the .txt extension to .rpt.

 

You can also save this report directly to your enterprise environment and as long as your using BI 4.x and save the report as DrillInPlaceUsingURLParameters to your system, you should be able to see it working right in your environment.

 

Steps to adapt this technique to one of your reports:

 

1) copy the formula to your report and this should also copy the parameter {?OpenG1} as well…if not create a new parameter with that name OpenG1, that allows multiple values.

 

2) edit the formula to use your field that you’re grouping on and also set the report name and the path to opendocument in your system.

 

3) ensure that you’ve got a Group Header B and that there’s a Suppress formula copied from the Suppress formula for GH1b on the sample.

 

4) in your Group Header B section, you should use cross-tabs, charts, or a subreport that contains the details for the group. it is important to note that you need to keep the Details section suppressed.

 

The first couple of clicks may be a bit slow depending on your server performance, but you can work with your enterprise administrator on setting the caching for the report effectively.

 

Also attached to this blog post are two additional reports for hierarchies…these are here due to the response from Mike Lunquist which is below.

 

1) for hierarchies there is a formula there that allows the end user to open and close sections…you can save the OpenCloseHierarchy report to your enterprise system and open and close.

 

2) the second report shows the hierarchy being passed to a subreport so that charts and crosstabs can be used on hierarchy data, and actually show the data in the hierarchy’s format. this is explained more in this blog post. this report is included as it uses the same formula as the open close hierarchy report mentioned above. this one does not have the open close feature activated though.

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Mike Lundquist

    This is ingenious!!!

    I published your report onto my BOE server and it worked like a charm.

    I also broke up your “final output” section to print the variables in the report.  This helped me in understanding what you did.

    I did run into one small glitch.  As I navigate in the report the opensections variable fills up with a lot of duplicate [XXXXXX] values. 

    drillInPlace.jpg

    I tweaked the OpenCloseMaster formula to eliminate these duplicate values.
    //

    if length(opensections) > 1
        then opensections:= opensections[1 to length(opensections)-1];
        else opensections:='[XXXXXX]’;                                   //<<<<<< inserted code

    // syntax for completing the report url
    //reporturl:= reporturl + ‘&lsM’ + parametername + ‘=[XXXXXX],’ + opensections;  //<<<<< original code
    reporturl:= reporturl + ‘&lsM’ + parametername + ‘=’ + opensections;  //<<<<< inserted code
    //

    Brilliant idea!  Can’t wait to try it with multiple groups . . . or a hierarchy.
    You should get an award!

    (0) 
    1. Jamie Wiseman Post author

      thanks so much Mike! and thanks even more for the adjustment on the big glitch. i’ve put in your changes in the attachment and referenced you in the formula if that’s okay.

      and now that you mention hierarchies, please see the two new attachments that are posted on the blog now…this was something that i was working on a while ago and was used for two different purposes..

      1) for hierarchies there is a big formula there that allows the end user to open and close sections…you can save the OpenCloseHierarchy report to your enterprise system and open and close…please do let me know if you find any issues with this

      2) the second report shows the hierarchy being passed to a subreport so that charts and crosstabs can be used on hierarchy data, and actually show the data in the hierarchy’s format

      and thanks again so much for the help!

      -jamie

      (0) 

Leave a Reply