In BIRT, using report parameters is the highly powerful (and easy!) way to customize the report according to the end user needs. Report parameters provides runtime values such as start and end dates for reports, specific item for which report needs to be run, etc based on the user input. While this is very useful, some of the reports may need multiple parameters that have dependent relationship among them. For example, if your report needs to list Customers in a specific state in a specific country, the "regular" report parameter will not meet your requirements because you will need to "dynamically" adjust the list of states available for selection based on the Country that the user chooses. How can you do this in BIRT? Read on.
In BIRT, you will need to use the "Cascading Parameters" to define the relationship between the parameters. Continuing the above example, let us take the CUSTOMERS table in BIRT CLASSICMODELS sample database.
- Create a new dataset called COUNTRIES to select all unique Country information. e.g. A sample SQL is "SELECT distinct COUNTRY from CUSTOMERS".
- Create a new dataset called StatesInCountry to select all unique State information for a given country. e.g. A Sample SQL is "SELECT distinct STATE from CUSTOMERS WHERE COUNTRY = ?". Leave the parameter binding for the first parameter (param_1) to NONE for now. We will change it later.
- Create a new Cascading Parameter Group called "paramStates". Choose "Multiple Dataset" option as we will have to select information from two datasets created above.
- Click on Add Button on the "New Cascading Parameter" dialog to add a new Cascading parameter. Enter the name for the cascading parameter as "paramCountries", choose COUNTIRES dataset created in Step1 as the dataset and select the "COUNTRY" fields for value and display text.
- Click on Add button again to add the state cascading parameter. Enter the name for the cascading parameter as "paramStatesInCountry", choose the StatesInCountry dataset created in Step 2 as the dataset and select the "STATE" field for value and display text.
- Click Ok to close "New Cascading Parameter" dialog. Please see the attached screenshot containing the Cascading Parameter Group information.
- Now edit the StatesInCountry dataset created in step 2, goto Parameters section, edit the param_1 parameter binding and set the LinkedToReport Parameter value to the "paramCountries" cascading parameter created in step 4. Click ok to save the changes.
Now your reports are ready to use the cascading paramters. If you run the report now, whenever you change the COUNTRY in the parameter selection, the available STATE selection also changes accordingly. The attached screenshots show the State information for USA and Australia.
PS: It is easier to do these things in BIRT than telling how to do them. I have tried my best to describe the steps involved. If you have questions or need clarifications on the above steps, please feel free to write to me.
3 comments:
i have two parameters in reports that depend on a single parameter
for example country ,city ,state.
but in my case city depends on country and one more parameter that is also depends on country at the same time. with cascade i can handle only i.e city. how to handle that that one also
Amit,
Adding additional parameter should be straight forward as well.
For example, to add a City/State/Country cascading parameter, you will have to create three datasets.
1. Country datasets (gets all the countries)
2. State datasets (gets all the States in the specific country).
3. City dataset (gets all the Cities in the specific State AND specific Country).
4. In cascading parameter group, you'd add all three datasets just as mentioned in the blog.
5. You will then edit the parameter binding of the State dataset so that Country parameter is bound to Countries parameter in cascading parameter group.
6. Edit the parameter binding of the Cities dataset so that Country parameter is bound to Countries parameter AND State parameter of the dataset is bound to State parameter of the cacading parameter group.
Now, you have a dependent parameter that works for three different datasets.
PS: I couldn't post images in comments, but let me know if this is not clear, I will publish a blog article with screenshots.
-Venkat
Amit,
Adding additional parameter should be straight forward as well.
For example, to add a City/State/Country cascading parameter, you will have to create three datasets.
1. Country datasets (gets all the countries)
2. State datasets (gets all the States in the specific country).
3. City dataset (gets all the Cities in the specific State AND specific Country).
4. In cascading parameter group, you'd add all three datasets just as mentioned in the blog.
5. You will then edit the parameter binding of the State dataset so that Country parameter is bound to Countries parameter in cascading parameter group.
6. Edit the parameter binding of the Cities dataset so that Country parameter is bound to Countries parameter AND State parameter of the dataset is bound to State parameter of the cacading parameter group.
Now, you have a dependent parameter that works for three different datasets.
PS: I couldn't post images in comments, but let me know if this is not clear, I will publish a blog article with screenshots.
-Venkat
Post a Comment