This is your comprehensive, one-stop spot for most everything you need to know about parameters, and how they can help you to deliver reports with data that is relevant to the user and the context. A parameter is a value that you use in any of several ways to pass values into a report or filter the data that displays in a report. You can allow the user to supply this value for interactive reports, or you can supply the value behind the scenes as a fixed value, or as a value passed from a main report to a subreport or drill-through report.
How you add parameters depends on several things. One is what type of report you are using, and another is how you intend to use the parameter. This post deals with basic parameters in section reports.
Prompts vs. hidden parameters
A prompt asks the user for a value, while hidden parameters allow you to pass the value in behind the scenes.
You can set the text you use to ask users to supply a value by setting the Prompt property of the parameter in code, or when you add the parameters via the Report Explorer, you can set it in the dialog. You can help your users understand what values are acceptable in the prompt. For example, you might say "Enter a valid seven-digit customer number" or "Select the sales region."
By default, the prompt appears in a parameter panel in each viewer, or with section reports, you can opt to prompt via a dialog.
To display a dialog instead of the parameter panel, instead of using the LoadDocument method of the viewer, assign the report Document to the viewer Document with code like the following.
private void ViewerForm_Load(object sender, EventArgs e)
// Show the Parameter panel to the side of the viewer.
//SimpleParameterSectionReport rpt = new SimpleParameterSectionReport();
// Show the Parameter dialog before opening the viewer.
SimpleParameterSectionReport rpt = new SimpleParameterSectionReport();
ActiveReportsViewer.Document = rpt.Document;
'Visual Basic code
Private Sub ViewerForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
' Show the Parameter panel to the side of the viewer.
'Dim rpt As New SimpleParameterSectionReport()
' Show the Parameter dialog before opening the viewer.
Dim rpt As New SimpleParameterSectionReport()
ActiveReportsViewer.Document = rpt.Document
Hidden parameters are handy for passing values to
- drill-through reports
- text boxes (for report titles)
To hide the parameter UI in section reports, set the PromptUser property to False. You can set these properties in the parameters dialog that opens from the Report Explorer, or in a section report, you can set it in the query as the last property.
There are several ways to add parameters to a section report: in the SQL query, in the Report Explorer, or in code.
In a section report, you can use special syntax in your query to add one or more parameters to the report's parameter collection. Note that this syntax is different from that used in page reports and RDL reports for query parameters. The syntax, at its simplest, looks like this:
SELECT * FROM MyTable WHERE MyParameter = <%MyParameter%>
You can add four other properties to the parameter in your query, separated by the pipe character (this thing: |) or use pipes with no space between them for empty values when you want to use some, but not all of them. Here are all of the properties:
- The Name property corresponds to a field in the data.
- The PromptString property is the text you show users to request their input about the data they want to see in their report.
- The DefaultValue property is the value that you supply in case the user does not supply one.
Bonus: If you set a default value, the fields list populates at design time so you can drag fields onto the report.
- The Type property controls what kind of UI is displayed to the user, and corresponds to the type of data in the field. Choose from String, Date, or Boolean.
Note that for some databases you must add pound signs (these: #) around the parameter syntax for dates, and apostrophes (these: ') for strings. Find more information on rules for different data sources in the Parameters topic in the help.
- The PromptUser property is where you turn off the UI. If you set this to False, you have to pass in the parameter value in some other way, because the Parameters UI does not appear in any viewer.
Thus, a SQL query that you might use with a report drawing on ye olde Nwind.mdb sample database and using the first three properties looks like this:
SELECT * FROM Orders WHERE OrderDate >= #<%OrderDate|List orders after this date:|5/12/1995|D%>#
When you preview a report with that query string, since the PromptUser property's default value is true, the Parameters UI appears so that the user can select a date. And since we set the Type to D for Date, the UI provides a drop-down date picker. (If we did not specify the Type, the date would appear as a string, the default type, and the user would have to type in a value.) Now the user can change dates at any time and click the View Report button for an interactive reporting experience.
Report Explorer parameters
You can also add a parameter to the Report Explorer by right-clicking the Parameters node and selecting Add. Select the new parameter in the Report Explorer and you can set the same properties as in the SQL parameter, but this time in the Properties window.
This type of parameter is useful for report titles. For example, if you have drill-through reports and the detailed report that you drill through to is based on the country, you can add a country parameter in the Report Explorer, and drag the field into the report header section to have it display the current country.
You get the most control when you add parameters through code. This is how you pass parameters from your UI into a report, and how you pass parameters from one report to another. In the ReportStart event, you can reference existing parameters using the name or index, or create a new parameter in code and use the Add method to add it to the ParametersCollection. The code looks something like this:
C# code. Paste ABOVE the ReportStart event.
Visual Basic code.
C# code. Paste INSIDE the ReportStart event.
Parameter myParam = new Parameter();
myParam.Key = "myParam";
myParam.Type = Parameter.DataType.String;
//Set to False if you do not want input from user.
myParam.PromptUser = true;
myParam.Prompt = "Enter Data:";
myParam.DefaultValue = "Default Value";
Visual Basic code.
Dim myParam As New Parameter()
myParam.Key = "myParam"
myParam.Type = Parameter.DataType.[String]
'Set to False if you do not want input from user.
myParam.PromptUser = True
myParam.Prompt = "Enter Data:"
myParam.DefaultValue = "Default Value"
Then, in the FetchData event, you can assign the Value from your UI or wherever you want to get it from.
this.txtParam.Text = this.Parameters["myParam"].Value;
Visual Basic code.
Me.txtParam.Text = Me.Parameters("myParam").Value
You can also validate parameter values that your users enter with the ParameterUIClosed event. You can validate parameters with code like the following.
private void SectionReport1_ParameterUIClosed(object sender, bool Cancelled)
Visual Basic code.
Now that you have the basics down, you can try them out yourself. Here is a link to where you can download a trial of ActiveReports and do your own thing!