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.

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