How to combine rows in a list report such that the data within the rows appear on one single row and are comma separated.
How to combine rows in a list report such that the data within the rows appear on one single row and are comma separated.
Business Case
Requirement is to display the column data in multiple rows of a list report on a single row comma separated. The list report is grouped on 3 other columns.
Resolving the problem
Add a crosstab object under a new column of the list report and create a master detail relationship between the list query and crosstab query to achieve the desired reports.
e.g Using Go Sales(query) sample IBM Cognos package
Steps:
1) Create a list report and drag the following into it: Product Line, Product type, Product name from under the Inventory (query) namespace's, Product query subject.
2) Drag a crosstab object to the list report as the 4th column.
3) Select the first 3 columns in the list report (Product Line, Product Type, Product Name) and group the columns.
4) Go to the Query Explorer and select Query 2. From the Inventory(query) namespace, drag the Product type and Product name to the query. Drag the Order number query item from the Sales (query) namespace->Order query subject into Query 2.
5) Go back to the report design page using Page Explorer and select any item within the crosstab object. Under the Data menu, choose "Master Detail Relationship" and create a relationship between Query 1's Product name and Query 2's Product name.
6) From the Insertable Objects pane, under the Data Items tab, drag the Order number from Query 2 to the columns section of the crosstab.
7) In the crosstab object, select the Rows and under the Properties set the Border to "White" and choose "Apply All Borders" button and click Apply button and OK.
8) In the crosstab object, select the Rows and under the Properties set the Background Color to "White" and click OK
9) In the crosstab object, select the crosstab corner and under the Properties set the Border to "White" and choose "Apply All Borders" button and click Apply button and OK.
10) In the crosstab object, select the Order Number row and under the Properties set the Border to "White" and choose "Apply All Borders" button and click Apply button and OK. Then select the Background Color property and set to "White". Click OK.
11) Choose the Unlock button on the button bar. Then, in the Insertable objects pane, under the Toolbox tab, drag a text item to the right of the Order number cell and enter a comma as the character. Click OK.
WARNING: This approach to this issue will be processor intensive as there is a master-detail relationship to give the desired report output.
Business Case
Requirement is to display the column data in multiple rows of a list report on a single row comma separated. The list report is grouped on 3 other columns.
Resolving the problem
Add a crosstab object under a new column of the list report and create a master detail relationship between the list query and crosstab query to achieve the desired reports.
e.g Using Go Sales(query) sample IBM Cognos package
Steps:
1) Create a list report and drag the following into it: Product Line, Product type, Product name from under the Inventory (query) namespace's, Product query subject.
2) Drag a crosstab object to the list report as the 4th column.
3) Select the first 3 columns in the list report (Product Line, Product Type, Product Name) and group the columns.
4) Go to the Query Explorer and select Query 2. From the Inventory(query) namespace, drag the Product type and Product name to the query. Drag the Order number query item from the Sales (query) namespace->Order query subject into Query 2.
5) Go back to the report design page using Page Explorer and select any item within the crosstab object. Under the Data menu, choose "Master Detail Relationship" and create a relationship between Query 1's Product name and Query 2's Product name.
6) From the Insertable Objects pane, under the Data Items tab, drag the Order number from Query 2 to the columns section of the crosstab.
7) In the crosstab object, select the Rows and under the Properties set the Border to "White" and choose "Apply All Borders" button and click Apply button and OK.
8) In the crosstab object, select the Rows and under the Properties set the Background Color to "White" and click OK
9) In the crosstab object, select the crosstab corner and under the Properties set the Border to "White" and choose "Apply All Borders" button and click Apply button and OK.
10) In the crosstab object, select the Order Number row and under the Properties set the Border to "White" and choose "Apply All Borders" button and click Apply button and OK. Then select the Background Color property and set to "White". Click OK.
11) Choose the Unlock button on the button bar. Then, in the Insertable objects pane, under the Toolbox tab, drag a text item to the right of the Order number cell and enter a comma as the character. Click OK.
WARNING: This approach to this issue will be processor intensive as there is a master-detail relationship to give the desired report output.
Comments
Post a Comment