D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.



My alternative to scraps of paper lying about.



Others may find bits of it useful!








Friday, 3 June 2011

Using SQL commands, parameters and subreports - CR

My report contains a number of subreports, each comprising a SQL command and using a parameter value passed from the main report.

1) Set up the main report parameter, in this instance called ?School















2) Add a subreport, Add Command with the Where clause as follows:

WHERE (School.SchoolKey = {?School})














3) For the Parameter list, Click Create:

Parameter Name = School
Value Type = Number
















It will then prompt for a parameter value, enter 1 (anything will suffice at this stage)

NOTE: When the database field is a uniqueidentifier, use 'String' type and for the prompt use a value in the uniqueidenfier format: {29D2B899-393A-4592-9B00-D289884A1F94}

4) Back in the main report, right click the subreport and Change Subreport Links:

Available Fields = ?School
Subreport parameter field to use = ?School (make sure to select the user-generated one and not the automatically generated one named similar to ?PM-?School)















5) Voila! Main report parameters should now be passed to SQL Commands in subreports.

2 comments:

Craig.Wright@TD.com said...

Dom, I like this but I don't need or can use parameters. My main report is written as a SQL Command and I now need subreports that tie on group code and account number BUT the data I need to report in the subreport is mutually exclusive (the main SQL command excludes what I want to report with my subreport(s)), so do you have any ideas on how to get that started? Thanks!

Craig A. Wright
craig.wright@TD.com

Anonymous said...

replied via email