How to find what query items are used in a report and where they come from:

A common question posed to Cognos administrators is, “what query items are being used in our reports?” It’s a straightforward question that is often challenging to answer*. Fortunately, MetaManager’s™ Document bundle comes with modules that can help you answer this question.

The first use case that will be explored will involve using Report Documenter. A feature of Report Documenter allows you to generate a Query Item Report. This option will generate an Excel file that contains all the elements of the reports that were selected – including query items.

To begin, select the reports you would like to include in the search and drag-and-drop them to the center object area. Next, check the box next to the “Include Lineage” and “Produce Query Item Report” options. Be sure both of these options are selected. Also, select a destination on the file system to save the output. This configuration should look similar to the screenshot below. 

Once ready, click the Document button to generate the report. When MetaManager™ finishes it should automatically launch Excel and open the report. If it doesn’t, navigate to the location on the file system where you saved the report.

The Excel report is broken up in to many columns. Each column has a header defined and is ready to be filtered. Navigate to the right and locate the Query Items column. This will contain all the query items being used by the reports that were selected. The spreadsheet can be filtered on individual query items so you can see the corresponding report that is using it. Alternatively, the spreadsheet can be filtered on an individual report as well if there are specific questions on a certain report.

If you stop here, you'll have documentation for all of your reports and which packages they are tied to, and the query items involved.  You can go a step further and document your Framework Manager Modules, including the Query Items details to be able to link those models to packages and query items as well as datasource and column level information.  

This documentation task will need to be repeated for all of your in-use Framework Models if you want full report lineage for all of your Cognos reports. To begin, open the Model Documenter module in MetaManager and load a framework model. Use MetaManager's build in Quick Filter in the top toolbar to select Query Items, and below that you can select the specific Framework Model fields you want to document, the first time you create this documentation it may be easiest to document all fields so that you can sort and filter the resulting output based on your requirements.

Select a location to save your output, and then hit the Document button.  

The resulting output will now contain all of the FM model information for Query Items, and your Report Documenter Query Item report includes the same from the package and report side. The final step is to merge these outputs (one for each FM model and the query item report) using the Query Item name to link the reports to the model documentation.  This is a manual task, and can often be made more burdensome by shared query item names or ambiguous naming conventions that may cause different query items to have the same name between different FM models.  Typically, this last step is the most labor intensive and requires a skilled Excel or DB user to create functional lineage tables/outputs**.

---

*If you are using Data Modules, you can use the Report Lineage module to get all of the above information in a single screen.  Just drag and drop your reports or data module into the editing screen and you'll quickly be able to see the full lineage of items and where they come from. The key difference here is that with data modules - all of the required data lives in the Cognos content store, while with standard Framework Models, Packages and Reports, that information is spread out between objects inside and outside of the content store.

The Report Lineage module can show the tables and columns included in the data module and highlight reports that contain a specific field.

 **BSP Software can help!  BSP Software, in partnership with MicroStrategies now does Cognos and BI Services work.  If you are interested in BSP Services or extra help with your Congos tasks, let us know by emailing Nikki Malone, nmalone@microstrat.com.