From the maintenance and documentation perspective, it is advisable to keep the number of query items in report query subjects to the minimal. In this recipe, we will see some good practices to ensure this.
We will use the dimensional GO Sales SSAS Cube package for this.
From Tools | Options, check the options of Alias member unique name and uncheck Delete unreferenced query objects.
- We will start by creating a simple crosstab report. Open Cognos Report Studio for a new crosstab report. Drag Time Month Dim | Current Year level on the columns.
- From the Insertable Objects pane, drag a new Set Expression on rows.
- Give it a name, for example, Accounts and choose the Account Dim hierarchy.
- In the Data Item Expression window, expand the Account Dim hierarchy to locate children of Assets (total).
- Select the three children of Assets (total) and drag them onto the expression definition. They will appear as comma separated values. Put them within a
SET()function.
- Pull Stmt Year from the measures in the crosstab intersection.
- Run the report to test it. It should work fine. Now go back to the studio and examine the report Query Subject.
- You will notice that there are six query items.
- Now go back to Report Page and delete the Current Year from columns. Drag Current Month there instead.
- Go back to Query Explorer to examine the query subject. Notice that both, Current Year and Current Month are present.
- Run the report to test. It works fine. However, there is a redundant item in the query subject (that is, Current Year) which is not used anywhere in the report.
- Now go back to Cognos Report Studio. From Tools | Options, uncheck the options of Alias member unique name and check Delete unreferenced query objects (opposite of what we had in the beginning).
- Delete the crosstab from report page. Again create it by following steps 1 to 9. Examine the query subject this time. You will see that only three query items are present this time.
- Finally, we will try one more thing. Select the Current Month from columns and hit Ctrl+X to cut it. You will see that it is removed from crosstab.
- If we want to bring it back, we have two places. Either, we can drag it from the Model/Source pane, or we can get it from Data Items pane.
- First drag it from the model or source tab. Notice it is called 'Current Month 1' now and a duplicate query item is created for it in the query subject.
- Hit the Undo button. Drag it this time for the Data Item pane. Notice that no new data item is created this time.
We have already talked about the environment options in this book previously. This recipe highlights how these options can be useful in reducing the number of query items.
By checking this option, we make Cognos Report Studio create a new data item for each member used within any data item or filter. This means a greater number of query items. We uncheck this option to stop the creation of separate aliases.
When this is unchecked, the data items that you delete from the report page are still maintained within the query subject. Hence, we check this option to automatically clear the unused items. You can still 'Cut' the item from report purposely to keep it in the query subject, if required.
Whenever an item is already present in query subject, if it needs to be pulled again on the corresponding container, it should be pulled from data items pane. Dragging it again from the model/source would mean creation of a duplicate query item. This duplicate query item will have name appended with number '1'. Use this as an indication to identify duplicate item and remove it.