Cascading parameters

In some cases, it is desirable to have the user select a high level category in order to limit the number of parameter choices a user sees. For example, let's say we were looking at a report where a user needed to look at products. In the case of a large company, there could be thousands of possible products or product codes. It is better to filter based on a product line to find the actual products one is looking for. Using the Classic Cars example, we have many different types of car models such as the 1969 Harley Davidson Ultimate Chopper, Dodge Charger, and 1948 Porsche 356-A Roadster. If we want to limit what is displayed, we limit our parameter display by product lines such as Classic Cars, Motorcycles, or Vintage Cars. Once a user has selected one of these, they would see only the vehicles under the corresponding product lines. These are called cascading parameters.

Let's look at an example. In this report, we are going to modify the Employee-Chapter6.rptDesign report we created earlier to allow for a drop-down parameter list that allows a user to select a manager and list the employees who are under the manager, and then select an employee and get the report for that employee.

  1. Open Employee-Chapter6.rptDesign.
  2. Save the report as Employee-Cascading-Chapter6.rptDesign.
  3. From the Data Explorer delete the Report Parameter dsprmEmployeeID.
  4. Create a new dataset called dsEmployeeManager and enter the following query:
    SELECT
employeenumber,
lastname || ‘, ‘ || firstname employeeName,
(SELECT
lastname || ‘, ‘ || firstname
FROM
EMPLOYEES managerEmployees
WHERE
employeenumber = currentemployee.REPORTSTO
) managerName
FROM
EMPLOYEES currentemployee
union all
SELECT
employeenumber,
lastname || ‘, ‘ || firstname employeeName,
‘All' managerName
FROM
EMPLOYEES currentemployee
  5. In the Data Explorer, right-click on Report Parameters and choose New Cascading Parameter.
    Cascading parameters
  6. Enter rcprmEmployeeManagers as the parameter name.
  7. Select the Single Data Set option.
    Cascading parameters
  8. Click the Add button.
  9. Enter the following information in the dialog window:
    Cascading parameters
  10. Click OK.
  11. Now, click Add again, and enter the information for the Employee:
    Cascading parameters
  12. We can enter the Prompt text along with the default values for each of these rows, just like we would enter regular parameters.
    Cascading parameters
  13. Click OK. Under the Data Explorer, select dsEmployeeInfo and double-click to open the Data Set Editor.
  14. In the parameters, select dsprmEmployeeID, and click the Edit button.
    Cascading parameters
  15. In the Edit parameter window, set the Linked to Report Parameter option to rprmEmployee.
    Cascading parameters
  16. Save the report and preview it.

Now, when we run the report, we will have two new parameters. When we select the Manager drop down, the second list will get smaller. So, if we select All, then all of the employees will appear in the second drop-down list. Change the Manager drop-down option to something else and the list of employees changes.

Cascading parameters
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset