A friend of mine recently asked me if I could make an example for him of a Crystal Report with a parameter to control whether the report detail is shown or hidden. So I decided that I would answer his question through this post on my blog. I decided to approach the problem by having a sub-report to display the detail data, there are also other ways to achieve the same result – this is the method which came to mind at first. When a parameter called ‘ShowDetail’ is set to ‘1’ then the sub-report will not be suppressed, and when the parameter is set to ‘0’ the sub-report will be suppressed.
For this example I have used the Microsoft Northwind database. You can download a backup of the database from this link Northwind.bak and restore it to your MSSQL database server. Restore the database with the name ‘Northwind’. Here is the link to my Crystal Report file.
Once you have restored the database you will need to set up an ODBC connection to your database using the ODBC wizard. Make sure that the ODBC wizard is for the same platform as your Crystal Reports is for. ie if you are using a x86 version of Crystal Reports you will need to use the x86 ODBC wizard. For my example I also created a separate SQL authenticated login to the database called ‘northwind’ and assigned the ‘Northwind’ database as the default database for the user.
When using Crystal Reports I prefer to use a database ‘Command’ to select the database. Once you have added the ODBC connection in Crystal Reports one needs to select the ‘Add Command’ button under the connection.
Create the sub-report, you will see that in the example I have made the sub-report really small (you may have trouble finding it), but have allowed it to grow, this is to make sure that when it is suppressed that it does not cause the report to have big spaces between the rows.
When entering the database command for the sub-report, you will see that I have created a parameter called ‘PRM_OrderID’ which is used in the SQL where clause to filter the data. This parameter needs to be set as numeric, as if it is not numeric then it will not match when the report is linked from the main report. When viewing the main report, right click on the sub-report area and choose the option ‘Change Subreport Links…’. Here you will see how I linked up the OrderID from the main report to be carried forward and mapped to the parameter which I created in the database command. I prefer to do the data filtering using the database rather than in the report itself as I understand the performance of the report will be better.
In the main report I have added a formula for the detail row fields to control whether the field is displayed with a bold font or a regular font. To see the formula right click a field and choose ‘Format Object’ and select the ‘Font’ tab, then select the formula button on the right of the Style parameter. The formula is as follows:
IF {?ShowDetail} = ‘1’ THEN crBold ELSE crRegular
A similar formula is used to display or hide the sup-report itself. When you right click on the sub-report select ‘Format Object’ you will see that there is an option to suppress the sub-report. Click on the formula button and you’ll see that the I have entered the following formula which will suppress the sub-report and thus hide the report detail.
IF {?ShowDetail} = ‘1’ THEN FALSE ELSE TRUE
The next step of this project would be to build this report into an application and have the user select whether the report detail is to be shown through a user interface. The application would then pass the selected parameter through to the report. A future blog post could be done on how to do this in the future if there is enough interest shown in this post.
I hope that you have fun experimenting with Crystal Reports.
Thank you very much Peter, as per our discussion, I was looking for something that uses the standard Microsoft built-in reporting, the RDLC, but your article is wonderful thank you.