While generating static reports is useful, many reports must support the selection of report criteria by the user to narrow the scope of the report.
For this example, we’ll start with the simple Customer List report created in Chapter 2, and add the capability to limit the customer list by country.
Step 1: Copy the basic report.
First, make a copy of the “Chapter2Report” report, and rename the report “Chapter6Report1”.
Step 2: Add the criteria selection to the report
There are a number of input elements available to collect input from the user. For visual clarity, you may care to collapse the dtCustomers element, hiding all of its child elements.
In this example, add the following beneath the
Body element and move them above the dtCustomer element: a
Label element to prompt the user for the criteria, a
Space element, an
InputText element to accept input from the user, and a
New Line element. Give the
Label and
InputText elements appropriate IDs.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuOPMYuiP7tnwLWNv57TJhmRXOil8byDRig2xUnkFXhNTR9trkQT-3gbgxB4d_EPU6hGc1skrtGRBml02NZIhcfUAPDY8NhrPFJQfVsRgwyr_izFDm6c-6rS5GWykSJ3k4xr6cP4AQ-wON/s1600/AAAAAA.png) |
Figure 6.1: Definition Editor Panel after adding input elements |
Set the
Label element’s
Caption attribute to prompt the user for input (i.e. “Enter the Country:”).
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8s9SLR1hO9N2y9UtcjvJNxj4E2tx24CDCOT_DtlcRpGzwjowK1v8R4MfdyVWESFkE6Qo9f4z4C_K99yyRe-DwKtE-nloLUi-AzGzD2aaLtntHPKz5goCMEJ-EWLFzsMphLhjR1LoYDuSh/s1600/AAAAAA.png) |
Figure 6.2: Attributes Panel after setting Label element attributes |
Hint: An alternative to using a Label element to prompt the user for input is to set the InputText element’s Caption attribute.
Step 3: Add a button to execute the report
Add New Line and Button elements below dtCustomers to execute the report.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZPbKYFy9Kqvo1euhupr19v6jv8dVSgx4tJ5_xi79SzhUobSyAf_mOV3hBKYQbH0IocSHWBYQuIJpQms18kLlUlUBlvY_t_71y1-bx91oCZ6-q6rFF88CSU-KAFpU-ijVygxCpx1rICJte/s1600/AAAAAA.png) |
Figure 6.3: Definition Editor Panel after adding Button element |
Set the
Button element’s
Caption attribute to provide a description of the button action (i.e. “Run Report”).
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikZRfZTTYJLvaHvMFOXUwabEr_G3nX5dN0oj283d-PTO22muYtIVGeWlCSJVxpAmHFHN9XF1icqd_X_Z9NoYQhg0J9L7ipdCvbdt4vn5G_Eq8rq_eSKNPhtIL8wYr6fpWlWPl-EVcjwI0j/s1600/AAAAAA.png) |
Figure 6.4: Attributes Panel after setting Button element attributes |
Beneath btnRunReport add
Action.Report and
Target.Report elements to specify the report to run when the button is clicked.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjMcTWF4k9TlioYNk8Vc5XVjYjxAsy65lxSjf1HYuir-EtwyZeNqJoOG_eNOcp4GcsEFJuJr3reFizX9i3kQNJDn1caI_5XCI5T_poubR_Nk4Dh0icGIoFRTP-_16I1B3JSXdlccvsuDGYj/s1600/AAAAAA.png) |
Figure 6.5: Definition Editor Panel after adding Action and Target elements |
In this example, the report executes itself with the desired criteria. Therefore, set the Target element’s Report Definition File attribute to this report (i.e. “Chapter6Report1”).
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdfpMzwep96TQxSUVM7iFkOUqLE0NlO-kd292bcGiNAnl8epOQ2uZgM5Qj0GPuCGGXnARjHmgtmfjWfCAsQ2CmdWB_4zdjNvQ5hycAhOTBlOXovjrI1SGY7dMayycJUsGwDv1Y1Jzxkgw_/s1600/AAAAAA.png) |
Figure 6.6: Attributes Panel after setting Target element attributes |
Expand dtCustomers and modify the dlCustomerData data layer element’s Source attribute to use the criteria:
SELECT * FROM Customers
WHERE Country LIKE '@Request.txtCountry~%'
When the button is clicked, the HTML form in the report that contains the text input element will be submitted. Form fields submitted in this manner are “sent” to the target report definition as values identified by @Request tokens, followed by the input element ID. So @Request.txtCountry~ represents the country value entered by the user. The percent symbol (%) in the query is SQL syntax is a “wildcard” that enhances matching and is not part of the token.
Now, preview the report. Because the report expects an @Request token, you’ll be prompted to enter one. This is a handy Studio feature that lets you feed request variables to reports when testing. For now, however, just click Continue.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjECEUJCQSR62PPmvuTlnCLt5PC2RBVjbWTZmxZBdR77NQ9zMo3axIJUh3yO8oZBiPe7iwd5SlvbHY6Tf0JADxHHT9bjO18JAEgXA-HELCbj_vDxbdjU9TpKZg-SYA5IVeIvPziFa3832SV/s1600/AAAAAA.png) |
Figure 6.7: The Test Parameters Input dialog box |
Your report should appear in the Preview window and will contain all customers. Remember, we haven’t provided any criteria yet to limit the report to specific countries.
Enter some country criteria (the names of most countries or abbreviations, such as USA or UK, for some) and click the button. Your report should be limited to the correct entries.
To see the effect of that percent sign (%) in the SQL query, enter just the letter “F” in your criteria and click the button. The report will include customers from Finland and France.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLvHiwEAwJLZIIxKkX6KaLgmF_ety_muMcxHyy1GCp9UivCRFQwSCJ9xJ3x6dFuHTgUYki9aU6_YHHDR6UKlhyphenhyphenUmIRMAB2rjqZbTGiRguIdpO0jvsFXtks7eTG9RGe20MTCGTrykE2Gmkw/s1600/AAAAAA.png) |
Figure 6.8: Report Preview Panel after criteria “UK” entered |
Step 5: Remembering the selection criteria
Notice, however, that our criteria textbox is cleared each time the report runs so we’re not really sure what criteria we’re viewing. To allow the report to “remember” the value entered by the user, set the Input Text (txtCountry) element’s Default Value attribute to the value “@Request.txtCountry~”.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjh6ZbOcqB4K-Cw4HUp8EXJk4mtMmDvpuBX1xAkz8-lW3OlQq7Eg5DFTiZndUMKwOBWaSOUBDw21_VLzJU9iz6zc9eb8412_DjHHPjD7c5K5nr0MOOkH2TOnEYd3XD_MR2i7xpHR7F2Wxpj/s1600/AAAAAA.png) |
Figure 6.9: Attributes Panel after setting Input Text element attribute |
The value entered will now appear in the Input Text element each time the report runs.
Step 6: Using a drop-down list
Instead of requiring the user to type in the country, a more user-friendly approach is to allow the user to select the country from a list of valid values. Remark the Input Text (txtCountry) element, and add just below it an Input Select List element.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyvtbrho6IhavIPdos6ZwrRjL0Xw88sgRvJOi_CuZjTlsOYuHZcZmS46-R1p1u8-auPU2IMGYvXpmXLuU-AAQNGxo9LeaOpDCC7pHsC5M-mbgXHB4jczmNh1V7dvzfEaJMB43FzfUxar6-/s1600/AAAAAA.png) |
Figure 6.10: Definition Editor Panel after adding the Input Select List element |
To supply valid values for the
Input Select List, add a
DataLayer.SQL element beneath it and give it an appropriate ID.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiu6rLktKWXJfsUjgSJ06C6sWTFnwWROppMnaySLBz-fYLCs5DpgiUbLmRSfgvPSMx_ajI43dn9AoZA42fOSBtxGOILbsPcFlw40h4mw7USqwgHO7IIwIeetSSnB50M8tD-6orxEJUQCZw_/s1600/AAAAAA.png) |
Figure 6.11: Definition Editor Panel after adding the DataLayer element |
Set the data layer’s
Connection ID attribute to “connNorthwind” and, to retrieve a list of unique country names from the database, use the DISTINCT keyword in the SQL query in the
Source attribute. The attributes should be as shown below:
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSsjF7SeaT32joykQj4pOVEqMU4yec-w8EkqSAus8B9XDNeYiC6jTPA4uSBy9dKcSNhkB1jRDxMOnrs30Q75W9yLqfmfjh5m4uEQPZeUIE1nTyHio-fQPowL6j4mOBFe9GGlvmiNMTxphF/s1600/AAAAAA.png) |
Figure 6.12: Attributes Panel after setting the DataLayer element attributes |
Set the Input Select List element’s Caption Column and Value Column attributes to the name of the appropriate column in the Data Layer (i.e. “Country”). In this example, these attributes are both set to the same column. But often the Caption Column is set to a user-friendly value and the Value Column is set to a key.
In addition, set the Input Select List element’s Default Value attribute to the request token that will contain the value selected by the user.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDBkYQqNejp86qOoF_xB1L1c8ZA1m24J-1LG3qKMqhYM-EltiaJpk7-Xuxr1uiNWsQVucq0ZON3bxkPG7zEFCqnLK1osgvHzHJAukptXX8hyAx4hdeSPoEowg-YpvJYySFxW3wzDXzcnAp/s1600/AAAAAA.png) |
Figure 6.13: Attributes Panel after setting the InputSelectList element attributes |
Hint: The Input Select List element’s Include Blank attribute is used to add an empty item to the list and the Include Blank Caption attribute is used to specify an item to add to the list (i.e. “All” or “None”). When the user selects the “blank” item, the @Request token returns an empty string.
Finally, modify the Data Layer (“dlCustomerData”) element’s Source property to use the country name selected from the list:
SELECT * FROM Customers
WHERE Country = '@Request.selCountry~'
Notice that the percent sign (%) is no longer used in the query.
Now, preview the report and select different countries from the list.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiP0JNwExfeDosuUPi8L72FyKj9R45djNFbrJX7VIG5SFdMe-dP-l2n7na7Txq7hs1GzHGpCuGXbo2lxPcvCVvjY8xp9D8-uu3PvhrZzRSsbilFjBBYwYoKRbnpyh-EX1LDgLbaDD3cjsvw/s1600/AAAAAA.png) |
Figure 6.14: Report Preview Panel |
To allow the user to select multiple items from the list, set the Input Select List element’s Multiple Selections attribute to “True”. In addition, modify the Data Layer (“dlCustomerData”) element’s Source property to retrieve all the selected items.
SELECT * FROM Customers
WHERE Country IN (@SingleQuote.Request.selCountry~)
When an input select list allows multiple items to be selected, the @Request token returns a comma-separated list of items. The @SingleQuote token inserts single quotes around each item selected in the input select list. These two tokens used together allow the selected items to be easily used with the SQL statement IN clause.