Highlight Current, Past and Future Data in Crosstab

Those who have tried conditional formatting in crosstab know that its a pain to do so especially if your condition is based on data items not part of the crosstab.

Requirement: Display for each month, for each weekending date the Revenue spread across the days of the week. Highlight the past weeks and past dates in Current week in Orange and current date and future dates in current week in Yellow and all other Future Dates in white.



Now the challenge is that the dates are not displayed rather the Day of Week. So the only items we have to work with are Weekending Date and the Day of Week.

Solution:

Step 1: Create a list report to display the Months.

Step 2: Create the crosstab inside the list with master - detail set on Month and section on Month.

Step 3: Drag the Weekending Date data item and create the Weekday data item: _day_of_week([DTE],1) and use case statement to display Monday - Friday.

Step 4: Create the conditional variable to highlight the required data as below:


case when
(
string2date([SummaryQ2].[Week Ending Display]) < _add_days(date2timestamp(today()),7 -_day_of_week(date2timestamp(today()),1))
)
or
(
string2date([SummaryQ2].[Week Ending Display]) = _add_days(date2timestamp(today()),7 -_day_of_week(date2timestamp(today()),1))
and _day_of_week(date2timestamp(today()),1) > ([SummaryQ2].[Days])
)
then 'Orange'
when string2date ([SummaryQ2].[Week Ending Display]) = _add_days(date2timestamp(today()),7 -_day_of_week(date2timestamp(today()),1))
and _day_of_week(date2timestamp(today()),1) < = ([SummaryQ2].[Days])
then 'Yellow'
else 'White' end


Step 5: Associate the crosstab intersection with the conditional variable and set the corresponding colors.

Setting Date Format on Chart X Axis

For those who didn't know, to set formats for Dates on a chart's X axis, change the Chart Node Member's source type from Member Caption to Data Item Value.

Count Distinct and Summaries

When you have data items with aggregate type set to sum and rollup aggregate set to sum and you include summary footer in your report the SQL Cognos generates would be something like:

select sum(Column 3) over(partition by column 1, column2), sum(column 3) over (partition by column 1).

But a count distinct over partition is not supported by the DBs and if your requirement is to display count distincts for the detail records as well as the summary footer then Cognos generates 2 SQLs like :

select T0.c1,
T0.c2,
T0.c3,
T1. c2

from
(
select
column 1 c1, column 2 c2,
count(distinct column 3) c3
group by 1,2
) T0,
(
select
column 1 c1
count(distinct column 3) c2
group by 1,2
) T1......

Now the disadvantage is if your T0 hits huge fact tables then you would be hitting those fact tables twice. The above scenario is at least a little acceptable but imagine a scenario wherein you have union clauses. I have a report that has 5 union clauses so the count distinct actually causes the 5 union queries to be executed twice which impacts performance.

Solution was to include 1 more union to get the summarized data with dummy data items thrown in to sort and get them to align after the detailed records and then conditionally format the report to make the summarized row appear as a footer.

Cognos SDK Training

Been in Cognos SDK training for the past 3 days..It was great..Now I know what is and what isn't possible with SDK. A whole lot of times when you say something isn't possible you get a question whether it is possible with the SDK and I never could ever respond to that cause I didn't know whether it was or wasn't. So this training has now helped me answer that.

The thing about SDK is that whatever is possible with the Cognos tool is what is possible with the SDK. Basically you can use it a lot for automation like creating a screen for managing users, getting a list of Cognos objects / reports and so forth. I am still looking for real world examples wherein this can be used. If someone out there has been using the SDK you could probably share your experiences with us.

One great thing I got to know is what is called as Cognos Mashup Services or CMS in short which according to me is a hot thing. I will post more about this in another article.

And here's another tit-bit that I got to know - the next version of Cognos in all probability is going to be called Cognos 10 and would be released in the 4th Quarter of 2010.

Burst Reports and Master Detail Relationships

Burst Reports with Master Detail relationships is not a good option to use in Cognos. This seems to be a major issue with Cognos and hope they rectify it soon. When you have reports that have master detail relationships and you need to burst the same, be aware that the report queries will be executed for each burst query group item.

The drawback is that you almost always end up with a report which requires a master detail relationship either because it has multiple pages with each page associated with a different query or some such reason. Its almost impossible to eliminate master detail relationships. And what you end up having because of this is a headache wherein either there is a huge number of queries fired against the DB one after the other or the scheduled process taking time due to the burst reports being executed one after the other.

What IBM needs to do is to have a property that report authors can set as to whether the detail query needs to be run for each master item or if the detail query needs to be executed once and then have it associated / sliced based on the master query.

Saving Reports to a Local Directory

This topic has been covered in Cognos Docs. But thought would explain in simple terms and include images for the benefit of others.

Step 1: In Cognos Configuration, Click on Actions menu. Select "Edit Global Configuration" in Actions menu. Choose General Tab.



Step 2: Enter file system path in "Archive Location File System Root" property. Example: file://\\c:\ReportOutput. This will serve as the root file system. You can create sub-folders inside this root folder to hold the report outputs for each individual project. Save the changes and re-start the Cognos Server.



Step 3: In Cognos Connection, Launch IBM Cognos Connection and select Configuration tab. Choose Dispatchers and Services option.



Step 4: Click on Define File System Locations icon. Click New. Provide a Name. In the file system location text box enter the sub-folder name. Example: Project 1 and click Finish.



And here is a contribution from RoadwarriorPB. A big thanks to RoadWarriorPB for sharing the below information -

RoadwarriorPB -

In Step 5, you can define any number of File System locations. These definitions are nothing more than 'objects' in the Cognos Content Store, and can be secured like any other object. This can be very beneficial in a hosted, SaaS environment..you can define one 'location' per tenant,and secure the location for the Group associated with the tenant. Users will only 'see' the location they have rights to use.

The file system location is 'local' or relative to the Cognos Content Manager service, not the Report Dispatchers. If you have one or more Standby Content Managers, you will want to define network shares mapped to the same physical location for every Content Manager to insure that all Content Managers utilize the same physical file system location for output.

And here is a contribution from Nicolas. A big thanks to Nicolas for sharing the below information -

Nicolas -

This setting ("Save report outputs to a file system?") is used with the Content Manager advanced option "CM.OUTPUTFOLDER" which exports ALL executed reports to a designated folder (check the description provided by Cognos Configuration for that particular option). This was an old and rudimentary way for Cognos to handle output to file system, and later Cognos provided an Output Processor which handled this a little bit better.

JavaScript Samples

Here's a collection of all JavaScript Articles that can be found in this blog:

• Removing Parameter Name from Value Prompts


• Implementing Default Date Prompt Scenario in Report Pages using Javascript and Render Variable Concept


• Set Font for Date Prompts using JavaScript


• JavaScript for Date Validation


• Default Date Prompts to Current Date and Current Date - 30 days


• JavaScript to Deselect Value Prompt Box Selections


• Expand / Collapse Javascript


• My First Fusion Chart


• Hide/Display Drill Through Links based on Levels


• Default Date Prompts to Current Date and Current Date - 30 days


• Tree Prompt on Relational Models using Prompt API

Note: These techniques use JavaScript against underlying report objects in a IBM Cognos 8 BI report. For this reason, there is no guarantee that reports created using these techniques will migrate or upgrade successfully to future versions without requiring modifications. Any such modifications are the responsibility of the report designer.

Dip in Posts!!

Just noticed that I have posted only 5 articles in the month of April as against 14 in the month of Mar. So I am going to try and post at least 2 new articles every week.

Happy Reading!!

Metadata Callbacks and Teradata Data Sources

For the past couple of days we have been stumped with an issue. We are using Teradata as our DB and any report seems to be firing numerous Help Column statements against the DB. And these are numerous statements ranging in 100 - 300 depending on the tables we are hitting.

Setting the Governor Property - Allow enhanced model portability at run time - unchecked is not helping. When you look at the query response though you will notice that for metadata callbacks though the information provided is that the metadata information retrieved is from the Query Subject (QS) that doesn't seem to be the case.

Here's what seems to be working: Removing the Data Source name from the Data Source Query Subjects and replacing it with the schema names and removing the schema name component from the Data Source in FM.

For example replace the SQL:

Select * from [TEST].Table1

To

Select * from Test_Schema.Table1

This seems to have removed the "Help Column" statements.

Here's my take on how this solution works: For Teradata based QS, when the schema name is set and the QS select statements are based on the Data Source, Cognos doesn't probably store metadata information since schema name resolution is dynamic and hence metadata is not being stored or Cognos is forced to go the DB for metadata retrieval.

Schema name provided as part of the Data Source is not stored as part of the metadata resulting in no storage of metadata for the QS and this could be getting resolved during run time thus forcing Cognos to retrieve metadata from DB.

When the schema name is removed from the Data Source and when the schema name is provided as part of the QS, we are informing Cognos that this is the schema name and will not change unless we change and save the model during which time the metadata for the QS also gets saved. In this case Cognos probably stores the metadata information and is not required to go to the DB for metadata retrieval.

Any thoughts on the same?

And here is a Reader Input. A big thanks to Greg for sharing the below information -

Greg -

Cognos will always have situations where it makes metadata callbacks to the data source, and you should always attempt to model in a manner that reduces these whenever feasible. Teradata is different in that it processes metadata call backs at a much lower priority than other request types when compared to other databases, so you need to be very mindful of how you model metadata on top of TD data sources in order to minimize metadata callbacks entirely. You should not have to remove the data source aliases from the data source query subjects - doing so makes your model less portable and could complicate future updates.