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.

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.