Concurrent Query Execution

Even though Concurrent Querying is supported and can be defined in Report Studio, it isn't necessary that concurrent querying takes place behind the scenes. This depends on the number of processes set up and the values configured for the various concurrent query execution parameters.

By default Cognos executes queries sequentially unless the property is set in the report and also provided concurrent execution is enabled in the environment.

To enable this in the environment, at the minimum the below parameters must be added to the report and/or batch report services:

RSVP.CONCURRENTQUERY.NUMHELPERSPERPROCESS,
RSVP.CONCURRENTQUERY.MAXNUMHELPERSPERREPORT

More details on the parameters can be found in the Administration and Security guide.

The NUMHELPERSPERPROCESS sets the number of query execution helpers. It needs to be kept in mind that each query execution helper spawns a new data source connection.

The MAXNUMHELPERSPERREPORT sets the maximum query execution helpers available per report.

In an environment that has:

4 processes (2 high and 2 low affinity)
NUMHELPERSPERPROCESS set to 2
MAXNUMHELPERSPERREPORT set to 2

The NUMHELPERSPERPROCESS allows for a maximum of (2 + 2 + 2) * 4 = 16

If a report has 4 queries set to concurrent execution then this report will consume all query execution helpers with 2 queries being executed concurrently even though 4 have been set to execute concurrently.

Thus it isnt' necessary that a query marked for concurrent execution will execute concurrently.

Defect - Duplicate Queries generated by Cognos

I seem to be on a ride running into numerous defects over the last couple of months. The latest one being similar duplicate queries generated by Cognos in a particular scenario.

As can be seen from the screenshot, Cognos generates 2 queries with no differences and both having the same name.

This happens in the below scenario:

When you have a singleton object before all other Objects in the report.
And when you have Prompt Macro as the first filter in the first object of the report.

And if you have value prompts after the singleton but before the first chart/list/crosstab object, then these queries get duplicated as shown below:

When each query counts towards performance, this isn't good.

So the work-around is to have a Required prompt without macros as the first prompt in a list/crosstab/chart object. If one doesn't exist create a dummy required filter to get around-this.

Using Text Wrap property instead of setting Widths

A lot of beginners in Cognos try to set list column widths to fit data to avoid text wrapping rather than using the White Space property. This is probably because the property name is misleading.

So to all those beginners who didn't know, set the White Space property of a list column to No Wrap instead of trying to set fixed widths if the intention is to avoid the data displayed in the column from wrapping.

InScope Report Function

How do you display a measure with different data formats against different Series elements in a chart? This is where conditional style and InScope report function comes to the rescue.

Requirement: Display a chart with Revenue measured against Product Line members in the primary axis and against web Order Method against the secondary axis. Display Revenue with 3 decimal places against Product Lines and 2 decimal places against Web order method.

This requirement can be extended to display different data formats against different series members.

Solution:

Step 1: Create the chart with Revenue as the default measure, Product Line dragged into Primary Series and Web Order Method dragged into Secondary Series and Year dragged into Categories.

Step 2: Select Revenue and create an advanced conditional style with the expression as InScope(Web)

Set the format in the style properties for this condition.



Likewise create another advanced condition for Product Line.

Cognos BI and TM1 Integration

Before people start wondering if I am working on TM1, the answer is no. But I am working on using TM1 as a data source and hope to pick up on TM1 in due course.

Here are a couple of points that I think people who are planning on using TM1 as a datasource should consider:

• TM1 cubes require an explicilty declared Measure dimension for the cubes to get imported into Cognos.



• The cubes need to have named levels enabled on them for the levels to show up in Cognos.



• Description attribute needs to be named as Description for the rolevalue of memberDescription to work in Cognos.



• Cube based aggregation functions do not work on cells in TM1 that have rules defined on them.

Another point that I am still banging my head about is with security. With transformer cubes we have the cloak feature but that doesn't seem to be the case with TM1. Example, if a user has access to Products 1,2,3 belonging to Product Line 1 but does not have access to Products 4,5,6 belonging to the same Product Line, with a transformer cube when the user accesses Product Line 1 he will see the total specific to Products that he has access to but with Transformer he will see the total value of all the products belonging to the product line rather than the products that he has access to which accroding to me is a huge disadvantage. Still trying to work through this. Hope someone out there can shed more light on this.
 

Using Stored Procedures in Reports

Here's a simplified requirement that requires using stored procedures in reports to set values.

Requirement: Create a chart report to display Target Sales value. Provide users with option to set Target Sales Values that should get reflected when other users run the same report.

Solution:

Step 1: Create a simple Stored Procedure that inserts ProdId, Date and Target Value into the target table. For our example and for simplicity I have created a SQL Server Stored Procedure. The Stored Procedure should return a dummy value so that we can create a Data Query Stored Procedure in FM.

Stored Proecures are of 2 types - Data Query and Data Modification. Data Query Stored procedures are visible for use in report studio while Data Modification Stored Procedures are not visible for use in report studio.



Step 2: Import the Stored Procedure in FM and set the prompt values.

Step 3: Create a Prompted report to get the input values from the user. Insert text box prompts in the prompt page as shown below.



Step 4: In the report page drag the dummy return value and set the visible property to No. This will force the SP to get executed.Insert a text item to display a message to the users on sucessful execution of the Stored Procedure.



Step 5: Create the main report to display the chart object. Insert an HTML item to open the Prompted report:

HTML Item:

<script>
function SetValues()
{
window.open('<Prompted Report URL>','','width=400,height=400,menubar=no,toolbar=no');

}
</script>

<input type="button" value="Set Values" onclick="SetValues();">

Insert a RePrompt Button to refresh the report.

My First Fusion Chart

Thanks to the articles provided by PaulM I was able to create a simple FusionChart. But a disadvantage that I discovered with the integration using embedded object method as mentioned on the FusionCharts website is the non-support of rendering charts with pure JavaScript option using this method.

Since we have some users who would like to use iPad/iPhone for viewing Cognos reports in the future this would not be an optimal solution for us. I wanted to highlight some such limitations provided in the FusionCharts website so people can make informed decisions regarding such integrations.

"When embedding using OBJECT/EMBED method, only Flash charts will show up. Additionally, features such as JSON data support, managed printing in Mozilla based browsers and enhanced browser-specific error handling would not function as well, as they are dependent on the FusionCharts JavaScript class."

Also when using pure JavaScript rendering of charts not all features available with the flash charts are supported. Like the scroll bars available for flash based charts that I was really impressed with are not available in the JS version of the charts. Hoping that this would be addressed in future releases.

I am still working on the CMS and JS rendering approach due to the limitation discussed above. Hope support for JS rendering of charts using object embedding method is brought out in future releases.

Using Layout Component References

This is in continuation with my previous article about the advantages of using Layout Component References. Creating and using Layout Component References is very easy. You create a report, let us call this the source report, that has all the components like Header items, Footer items that you would be frequently using across various reports. Name all the components in the reports.



In the target report, drag and drop the Layout Component Reference object. Choose the source report and the components you wish to use.



If you wish to override some of the objects pulled in then select the object and click on the "Overrides" property and choose the named sub-component that you wish to over-ride. For example, for some reports you would like a different Logo to be displayed. You can override the report title but continue to use the styles, fonts set for that table cell.



A sample of the overridden layout component where the logo and the report title objects have been overridden:

Templates vs Layout Component References

Often I have been asked if it is a best practice to create report templates and have developers use them for basing their reports. But instead I would suggest going with layout component references and using them in reports. Layout Component References offer a lot of advantages over the traditional Template approach. The references can be used as a base and can be over-ridden for particular report requirements. And any time there is a change to the layout component the reports will all reflect the changes unless the object has been over-ridden. This saves developers a lot of time making the changes across reports.

In my next article, I will touch base on creating and using Layout Component Objects.
 

Scrollable Lists

Requirement: Display a Prompts Pane and a List Pane. The list should display all rows in a single page with the capability to scroll. Note: the Prompts pane should remain visible to the users while scrolling down.

The simplest way to achieve the above requirement would be to use CSS. The advantage of providing scroll bars as part of the list is you can have other objects on the page still in user's visibility.

Solution:

Step 1: Create a table with 2 columns. Add the required Prompts to Column 1 and the list object to Column 2. Set the height for the table cell in which you would like to place the scrollable list.

Step 2: Insert HTML items as shown below:







HTML 1: < div style="height: 100%; overflow: auto;" >

HTML 2: </div>
 

FusionCharts Rocks!!!

Been working on a PoC integrating FusionCharts with Cognos and I have to say, FusionCharts really look amazing. With these charts so many of the limitations that we come across using Cognos charts can be overcome. There are some awesome charts/widgets out there like funnel charts that are still not available with Cognos 10.

With FusionCharts, you can even have charts that scroll thus not compromising on the amount of data displayed along the x axis nor the look and feel. There are so may properties that can be set for the chart too. And I think with this you can set a lot of values dynamically like dynamic trend line values. Really cool. But I think the drill through functionality might be a little lacking when we try using this through Cognos. FusionCharts does have a linked report concept that I am hoping is similar to drill through functionality but need to check it out.

PaulM's contribution:

The drill through (clickURL) functionality works perfectly. You can even perform a self drill through to mimic a drilldown.

The trick is to use repeaters not repeater tables or lists with HTML items. Lists and repeater tables insert useless table and cell tags. Text items are wrapped in spans.

There are two ways of embedding the data directly into the graph.

1. You can use the setDataXML() function.

2. You can also use the HTML embedding method detailed here: http://www.fusioncharts.com/docs/FirstChart/HtmlEmbed.html

 

Date Difference function in Teradata

To get the difference between dates in Teradata you do not have to use any date diff functions. Its a simple A - B calculation where A and B are date columns. This works in a SQL query. But when you try the same through Cognos, the difference calculation gets pushed to the Cognos server side rather than being handled at the DB side. To work-around this, use the _days_between Cognos function and then notice that the Native SQL now includes an A - B calculation.
 

Deleting Report Output Versions

The easiest way to deleting report output versions across reports would be to re-export and re-import the reports and unchecking the Include Report versions property while creating the export. This then deletes all report output versions that have so far been created in your production environment for the reports imported. But this works only for Public Folders reports. To delete report output versions created in users My Folders or to delete all report output versions except the last x versions you would need to use SDK.
 

Cognos 8.4 Query Studio Bug - Including Model and Query Filter

Recently I ran into an issue with Cognos 8.4 Query Studio. When I try to include both a model filter and a query studio filter the query studio report just hangs forever and then it throws an "object not found" error on the Model filter even though the object exists in the model.

This has been identified as a bug in Cognos 8.4 and seems to have been corrected in version 8.4.1.

So here's one more reason to move to 8.4.1.
 

Value Prompts 1st Line of Display

Value prompts display the Data Item Names on which they are based as the 1st Line inside the Value prompt just above the -----.

When you set the Name and Label to what actually needs to be displayed the Value prompt may not still display the required value.

Set the parameter name to the desired label name and then create a dummy data item and drag the use value of the prompt into this and set the value prompt's use value to the newly created data item. The value prompt will then start displaying the parameter names.

Impact of Sumary Filters on reports - Part 1

Consider the following columns in your report: Product, Sales and Rank - Rank(Sales).

To display only top 5 products you create a summary filter where Rank < 5.

Scenario 1: No grouping in report, no scope in summary filter
Result: No filtering effect

Scenario 2: Product grouping in report, no scope in summary filter
Result: No filtering effect

Scenario 3: Rank grouping in report, no scope in summary filter
Result: Filtering effect

Scenario 4: Product grouping in report, Product scope in summary filter
Result: Filtering effect, validation warning

Scenario 5: Rank grouping in report, Rank scope in summary filter
Result: Filtering effect, validation warning

Scenario 6: Product Grouping in report, Rank expression changed to include for Product, no scope in summary filter
Result : Filtering Effect

Scenario 7: Product Grouping in report, Rank expression changed to include for Product, A summary calculation - max - maximum(Rank) included for Product Grouping level, summary filter changed to max < 5, set scope of this filter to Product then only those groups that have maximmum ranks of 5 are displayed. Not setting scope will result in no data.

A summary filter should hence be used only when groups need to be filtered out otherwise an after aggregation filter should be used.

Parameter Maps in 8.4

Parameter Maps can now be based on Query Subjects in 8.4.

You can now have a query subject that retrieves the Key and Value pair as columns and base the Key - Value pair of Parameter Map on this. So now we no longer need to hard-code Key-Value pair which was earlier making parameter maps a unusable feature of Cognos.

Lists and Crosstabs

Lists and Crosstabs work differently with DMR. My requirement is to allow the user to select any member from the Product Hierarchy and the report should display the members in the selected level along with the children of each such member and the corresponding revenue.

Basically if the user selects any Product Line then the report should display Product Line, Product Type and Revenue or if the user selects any Product Type then the report should display Product Type, Product and Revenue.

I have created 2 data items for the same -

Parent - members(level([great_outdoors_company].[Products].[Products] -> ?Parent?))
Child - descendants([Parent],1)

and included Revenue measure.

When I drag the same into the crosstab the results are perfect while a list report based on the same query results in no data.

Has anyone else come across this issue?

[Update] The same can be achieved in List by creating the expressions using a Calculated Member rather than a Query Calculation. This feature is available 8.2 onwards.

Use Singleton to display data when there is no data in Report Page Query

In a report that was migrated from Cognos 8.2 to Cognos 8.3, I noticed that when there was no data in the report page, there was no data getting displayed for other queries in my report page. I have 2 queries being used in the report page - one for the main list report and another to return the user name using a macro. Now when no data was returned by the main query my user name was also not getting displayed. The user name was being displayed by a list with no borders and formatted to look like a singleton object as this was developed using 8.2. Now to rectify this I used a singleton object instead of the list and it worked fine.
 

List vs Chart

I have just finished delivering a set of Cognos reports for UAT and am awaiting for the results ;-)..So in the meantime thought I would post about some issues that I faced while creating the reports. You are welcome to post your thoughts on the same..

Have you ever noticed that a list report and a chart report behave differently when it comes to querying. I found that out fairly recently. Let me give you an example of how differently the two work. Assume you are to display for each selected Month the Top Products by Sales along with the monthly % contribution towards the sales. Ideally you would drag Product, Month, Sales, Rank(Sales for Month), Total(Sales for Month) - This is the total sales for the top products, % Contribution being - Sales / Total (Sales for Month) - basically how much did this Product contribution towards the total sales. I have a rank filter - rank < = 5 with after auto aggregation property set.

Now when I drag Product, Month, and % Contribution into my Chart report and List report, I see that the values are different. The list report displays the expected value while the chart report doesn't. On closer look I notice that the chart report doesn't apply the rank prior to the calculation of Total (Sales for Month). This means that my chart is calculating the total sales across all products for the month rather than Total sales for the Top 5 products.

Now that is weird when the List and chart behave differently. To work around this, I had to create a subquery which gets me the Product, Month, Sales and Rank with filter Rank < = 5. On top of this query I have another query that does the % contribution calculation. Now the list and chart work perfectly.

In retrospection I think the chart was working as expected, the rank filter was getting applied after aggregation. It was the list that wasn't working fine even though it returned the data I required.

The point though that I would like to make here is that not always lists and charts work the same way or fire the same query.

Reader Tips:

shiv shankar
Using solve order would have solved the issue.