Auto Correct option in Report Studio

I came across this option in the Tools menu of Report Studio and found it really useful.

When you run into validation errors while working on complex reports that involve a lot of data items and query objects, identifying the data item that is causing the error becomes difficult especially if its a complex syntax that is used across the data items. In such cases click on the Auto Correct option under Tools Menu to quickly identify all data items that have errors. This is much easier than going through the validation errors and trying to figure out the data items that have issues. But do not click on the OK button of the dialog box as this would remove the erroneous data items from the report.

Lets say we have a report that displays Product Line, Revenue, Margin, Quantity. Now the requirement is to display the values in the records only if quantity is more than 10000 else the records should display NA. So we include a case statement in all the metrics as "Case when [Sales(query)].[Sales].[Quantity] > 10000 then..". Now lets assume we made a mistake while copy-pasting this expression across the data items.Validating the report would throw the following error:

 From the error, its difficult to make out the data item that caused this error. Lets select Auto Correct option from the Tools menu:

Though this feature is useful it has some limitations like the dialog box only displays the data item name. So if there are multiple data items across query objects with the same name its difficult to identify the query that has the error.
 

Parameterized URLs for Retrieving Burst Outputs

Many of you must be aware of using URLs for inserting Cognos content in external applications. How do you frame the URL for retrieving bursted report outputs?

To retrieve burst output of the report view TEST stored in the folder TEST under Public Folders with burst key 123:

http://< cognos environment>?b_action=cognosViewer&ui.action=view&ui.object=/content/folder[@name='TEST']/reportView[@name='TEST REPORT']/reportVersion[starts-with(@name,'20')]/output[contains(@burstKey,'123')]

Note that the above URL will look through all burst outputs generated in the year starting with "20" i.e. 2000 - 2999.

You can use starts-with or contains for the reportVersion and output options.

Example: We have a report Burst PoC with Burst outputs.





The URL to retrieve the burst output for Americas would be:



Related Articles:

• Parameterized URLs for Retrieving Burst Outputs - Part 2
  
• Creating Drill-Through Style Reports using Parametrized URLs

 

Teradata Unions - Numeric Overflow Error

Requirement: There are 2 tables Sales and Revenue with columns Date, Product, Sales and Date Product, Revenue. Union data from these 2 tables and display Sales / Revenue value.

Create 2 query objects with the data items Date, Product, Sales and Revenue with Sales set to 0 in query 1 and Revenue set to 0 in query 2. You may run into numeric overflow errors with Teradata cause in Query 1 Sales is set to 0. So Teradata considers data type of Sales to be integer rather than decimal thus causing the error. Cast the 0's to appropriate data types and the issue gets resolved.
 

Avoiding Meta data Callbacks due to Hard-Coded SQLs

You would probably have noticed that Cognos creates meta data call-backs when you have reports based on Query Subjects that have SQLs hard-coded in them. To avoid such call-backs, import all the tables referenced by your query and leave them untouched. Cognos will reference the meta data from these tables while generating the SQL hard-coded in the query subject.

I would suggest it best to avoid hard-coding SQLs in query subjects but instead to create them as views in the DB and reference them through Cognos. This would help ensure better maintenance of objects and in easier impact-analysis of DB changes. Hard-Coded SQLs in query subjects should only be used when dynamicity is required through the usage of Cognos macros in the queries.