Data set parameter binding with reports?
When I've used BIRT before, with JDBC data sources, I could pass a parameter to a data set with the "Dataset Parameter Binding" button. Is this possible with Jazz data sources?
For example, suppose I have a simple data set that lists all of the users in the repository. I place that data in a table on the report.
http://i497.photobucket.com/albums/rr335/OldGrandet/DatasetParameter1.gif
In each table row, I would like to list all of the work items that are owned by that particular user. So I create a second data set to list work items. I place that data on the report as a table within the other table.
http://i497.photobucket.com/albums/rr335/OldGrandet/DatasetParameter2.gif
I'd like to pass the ID of the user in the current row of the outer table to the inner work items table, so the inner table would show only the work items for that user. With a JDBC data source in BIRT, I do this by setting the data set parameter binding. I select the inner table and bind its user ID parameter to the user ID of the outer table data set.
http://i497.photobucket.com/albums/rr335/OldGrandet/DatasetParameter3.gif
However, this doesn't seem to work with the Jazz data source. When I open the data set parameter binding window for the inner table, I cannot insert values from the outer table.
http://i497.photobucket.com/albums/rr335/OldGrandet/DatasetParameter4.gif
Ordinarily, the outer table would show up in that list. I'd select that table and then the user ID field from that table. The value of the inner table's parameter would be "row._outer". I've tried typing that value in, but it seems that the report ignores it and returns every work item anyway.
Is there another way to pass a parameter from one data set to another? I could outer join the tables, but the report I hope to create requires a few nested data sets, so using table joins would be too complex.
For example, suppose I have a simple data set that lists all of the users in the repository. I place that data in a table on the report.
In each table row, I would like to list all of the work items that are owned by that particular user. So I create a second data set to list work items. I place that data on the report as a table within the other table.
I'd like to pass the ID of the user in the current row of the outer table to the inner work items table, so the inner table would show only the work items for that user. With a JDBC data source in BIRT, I do this by setting the data set parameter binding. I select the inner table and bind its user ID parameter to the user ID of the outer table data set.
However, this doesn't seem to work with the Jazz data source. When I open the data set parameter binding window for the inner table, I cannot insert values from the outer table.
Ordinarily, the outer table would show up in that list. I'd select that table and then the user ID field from that table. The value of the inner table's parameter would be "row._outer". I've tried typing that value in, but it seems that the report ignores it and returns every work item anyway.
Is there another way to pass a parameter from one data set to another? I could outer join the tables, but the report I hope to create requires a few nested data sets, so using table joins would be too complex.
Accepted answer
5 other answers
Thanks, Rafik.
I've created work item 71506 to address this.
I found a workaround, though I don't think this is how BIRT is supposed to pass parameters:
1. Start with a new report and create data sources for the common and work item tables.
2. Create a simple data set from the CONTRIBUTOR table that shows every user in the database; for output columns, include only the CONTRIBUTOR_USERID and the CONTRIBUTOR_NAME, and include no parameters.
3. Create a data set from the LIVE_WORKITEM_CNT table that shows every work item in the database; for output columns, include only WI_ID, SUMMARY, and CONTRIBUTOR_NAME, and for parameters, include only CONTRIBUTOR_USERID. Supply no default value for that parameter.
4. Drag the users table to the report.
5. Add an additional column to the table of users on the report.
6. Drag the work item table to the new empty column of the users table.
7. In the data explorer view, double-click the work items table.
8. On the Parameters page of the Edit Data Set window, set the CONTRIBUTOR_USERID parameter to row
9. Preview or run the report. Now the nested work items table shows only the work items associated with the user in the current table row.
I've created work item 71506 to address this.
I found a workaround, though I don't think this is how BIRT is supposed to pass parameters:
1. Start with a new report and create data sources for the common and work item tables.
2. Create a simple data set from the CONTRIBUTOR table that shows every user in the database; for output columns, include only the CONTRIBUTOR_USERID and the CONTRIBUTOR_NAME, and include no parameters.
3. Create a data set from the LIVE_WORKITEM_CNT table that shows every work item in the database; for output columns, include only WI_ID, SUMMARY, and CONTRIBUTOR_NAME, and for parameters, include only CONTRIBUTOR_USERID. Supply no default value for that parameter.
4. Drag the users table to the report.
5. Add an additional column to the table of users on the report.
6. Drag the work item table to the new empty column of the users table.
7. In the data explorer view, double-click the work items table.
8. On the Parameters page of the Edit Data Set window, set the CONTRIBUTOR_USERID parameter to row
9. Preview or run the report. Now the nested work items table shows only the work items associated with the user in the current table row.
Silly of me -- that table I'm pointing to in red in the screencap is in fact the outer table. However, when I select the user ID of that outer table to be used as the ID parameter of the inner table, I still get every work item and not just the work items that are associated with the user ID.
This may be a bug, or do parameters in Jazz reports work in a different way than I'm used to?
This may be a bug, or do parameters in Jazz reports work in a different way than I'm used to?
Sorry to keep replying to myself, but I wanted to be clear to everyone about what I was trying to do.
1. Start with a new report and create data sources for the common and work item tables.
2. Create a simple data set from the CONTRIBUTOR table that shows every user in the database; for output columns, include only the CONTRIBUTOR_USERID and the CONTRIBUTOR_NAME, and include no parameters.
3. Create a data set from the LIVE_WORKITEM_CNT table that shows every work item in the database; for output columns, include only WI_ID, SUMMARY, and CONTRIBUTOR_NAME, and for parameters, include only CONTRIBUTOR_USERID. Supply no default value for that parameter.
4. Drag the users table to the report.
5. Add an additional column to the table of users on the report.
6. Drag the work item table to the new empty column of the users table.
7. With the work item table selected, open the Properties view to the Binding tab.
8. Ensure that the work items data set is selected in the Data Set field.
9. Next to the data set name, click "Dataset Parameter Binding."
10. In the Dataset Parameter Binding window, select the CONTRIBUTOR_USERID parameter and then click Edit.
11. In the expression builder, insert the code row . Note from the available column bindings that this code refers to the data from the outer table of users, not the inner table of work items.
12. Preview or run the report.
I expected the user ID parameter would be passed from the current row of the outer users table to the table of work items. Instead, the report shows every work item in the database, not just the work items related to the user in the current table row.
Is this a bug or am I missing something about how the parameters work in Jazz?
1. Start with a new report and create data sources for the common and work item tables.
2. Create a simple data set from the CONTRIBUTOR table that shows every user in the database; for output columns, include only the CONTRIBUTOR_USERID and the CONTRIBUTOR_NAME, and include no parameters.
3. Create a data set from the LIVE_WORKITEM_CNT table that shows every work item in the database; for output columns, include only WI_ID, SUMMARY, and CONTRIBUTOR_NAME, and for parameters, include only CONTRIBUTOR_USERID. Supply no default value for that parameter.
4. Drag the users table to the report.
5. Add an additional column to the table of users on the report.
6. Drag the work item table to the new empty column of the users table.
7. With the work item table selected, open the Properties view to the Binding tab.
8. Ensure that the work items data set is selected in the Data Set field.
9. Next to the data set name, click "Dataset Parameter Binding."
10. In the Dataset Parameter Binding window, select the CONTRIBUTOR_USERID parameter and then click Edit.
11. In the expression builder, insert the code row . Note from the available column bindings that this code refers to the data from the outer table of users, not the inner table of work items.
12. Preview or run the report.
I expected the user ID parameter would be passed from the current row of the outer users table to the table of work items. Instead, the report shows every work item in the database, not just the work items related to the user in the current table row.
Is this a bug or am I missing something about how the parameters work in Jazz?