Top or Bottom Selling Products against Dimensional Model

If you have tried creating a report that should display either Top or Bottom 5 products based on Revenue against a dimensional model in Cognos version 8.4, you would have noticed that using a case statement seems to cause validation issue.

The reason for the same has been explained in the article : Case Statement Issues in 8.4 with Dimensional Model

To work around this issue, the steps have been explained below:

Step 1: Create the following 2 Set Expression

TopCount Products - filter(topcount([Cube].[Products].[Products].[Product],5,[Revenue]), ?TopBottom? = 1)

BottomCount Products - filter(bottomcount([Cube].[Products].[Products].[Product],5,[Revenue]), ?TopBottom? = 2)

Where ?TopBottom? is a prompt that accepts as its values - 1 (Top Count) or 2 (Bottom Count)

Step 2: Create the following Union Set Expression

Products - union([TopCount Products],[BottomCount Products])

Step 3: In the list report, drag Products and Revenue measure.

Here if you notice, I have avoided using Case statement as they seem to be unsupported or do not work as expected in 8.4.

The report now works as expected.

-ve Numbers Weird Issue for ALL Static Choice in Value Prompt

You must have used a static ALL value in your prompts to handle the "1000 Values in the In Clause" in Oracle issue. Now if you had set the use value for this to be -9999 or any -ve number in order to set this to a value that will never be available in your table Cognos seems to be behaving weirdly.

It throws error RSV-VAL-004 Unable to find query information for XYZ. Change the case statement to a numeric value and the report validates perfectly.

Using -ve numbers seem to be causing weird issues.

Case Statement Issues in 8.4 with Dimensional Model

This particular issue deals with Coercion errors. Your report works fine in 8.1 and when you have converted it to 8.4.1 you get an error that looks similar to the following:

QE-DEF-0478 Invalid coercion from 'value' to 'measure' for 'when ?Measure? = 'R' then [Revenue] when ?Measure? = 'Q' then
[Quantity] ' in 'case when ?Measure? = 'R' then [Revenue] when ?Measure? = 'Q' then [Quantity] end'.

This is due to the usage of Case statements against dimensional models. Cognos had earlier warned against usage of this against Dimensional Models now they seem to have started restricting the same.

Try using an If Then Else to work around this. But Cognos definitely needs to come up with a similar construct for Dimensional Models as well as most work revolves around the usage of Case statements.

Blog Template Changes

I have modified the temmplate as I felt there was a huge wastage of space on both sides with the earlier template. And after including images I felt they looked too crunched up. Hence the new template.

Unfortunately, I don't have too much of patience to make it look better than it is right now. I have just about modified the colors to match up with the older template. Thats about it. Probably you can expect another change of template sometime in the near future when I do have the real interest to change things up.

Removing Parameter Name from Value Prompts

This has been covered in numerous mails, sites and posts. I am including the same here for the benefit of those who visit and use the site for all their JavaScript needs.

By default Single Select Value Prompts, display the Query Item Name as the first item in the drop down and the second item is a dotted line.



To remove the first 2 lines of value prompts follow the steps below:

Name the value prompt as PL.

Insert an HTML item below the prompt and include the below script:

<script>
var fW = (typeof getFormWarpRequest == "function" ?getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined)
{ fW = ( formWarpRequest_THIS_ ?formWarpRequest_THIS_ : formWarpRequest_NS_ );
}

var objProductLine = fW._oLstChoicesPL;

objProductLine .remove(1);
objProductLine .remove(0);
objProductLine .removeAttribute('hasLabel');

</script>

The first 2 lines in the value prompt are now removed.

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

Refresh Button Cosmetic Issues

Just noticed that my refresh Button doesn't look the same in all reports. The refresh buttons in some reports seem to have a at least 10 px of extra space at the top between the outer frame of the button and the text inside the button. And I don't have any padding set anywhere near the button. I copied the button onto an empty report and still noticed the space.

This may sound too trivial an issue, but would you believe that this was a UAT comment received and so had to be fixed.

After some time of investigation I discovered that the reason for this was that Font of Arial 10pt had been set for the text and in some this had been set for the button and the text inside the button. Setting this in both the places resolved my
issue.

My refresh button Specs (cause this doesn't happen for all specs):

Class: Button
Size: Width - 2 cm Height - 0.6 cm
Font: Arial 8 pt

Cognos Disasters : Charts

A lot of weird issues have been surrounding with he Chart queries. Seems like what we see is not what we get with Chart queries.

Issue 1:

One such issue is I have a chart query with Total Orders, Orders Shipped in 24 Hrs, Orders shipped in 48 Hrs and so on. I also have % Orders Shipped in 24 Hrs, % Orders Shipped in 48 Hrs and so on which are thing but a calculation of Total Orders Shipped in say 24 or 48 or x Hrs / Total Orders.

Now the SQL query looks fine but if I look at the SQL by clicking the "Tools > Show Generated SQL/MDX" I notice that % calculations are not included as part of the query either Native or Cognos. Now isn't that weird?

Here is one more for you, Issue 2:

In a similar chart I notice that even though I have only 2 measures dragged in, the SQL from "Tools > Show Generated SQL/MDX" seems to be spawning at least 6 similar columns and for what reason? I don't know. Again the Query SQL looks fine.

Another one, Issue 3:

In my earlier Orders report, I notice the the query from "Tools > Show Generated SQL/MDX" includes 3 subqueries as follows:

Select T0.C1, T1.C1, T2.C2, T2.C3
from
(select count(orders) from Orders) T0,
(select Date, count(orders) from Orders group by Date) T1,
(select Date, count(orders), total(Orders_24), total(Orders_48) from Orders group by Date) T2
where T1.C1 = T2.C1 or T1.C1 is null or T2.C1 is null

This is my simplified version of the native SQL generated by Cognos. But you get the message right? Why 3 subqueries when T2 satisfies my purpose. Again the calculations for % are missing not just in native SQL but also in Cognos SQL.

One more, Issue 4:

I have a cumulative chart that displays Date, Running-total (Customer Qty) and Running-Total (Gross Revenue). The sorting has been set on Date to get the cumulative values correct. The tabular data is perfect but then the chart shows the first Date value totally off for Gross Revenue. And its just the first value. So now the chart has a spike for the 1st Date and the rest of the dates look perfect. This happened just for the Gross Revenue line while the Customer Qty line was perfect.

After investigation I found that the only difference between the Customer Qty and Gross Revenue lines is that Running-Total calculated on Customer Qty is based on another Data Item that drags just the Customer Qty item into it from the package.

So for Gross Revenue line too, I created another data item with Gross Revenue pulled in from the package and then based the Running-Total calculation on this. And Lo, the report looked perfect.

Again, in another report Running-Totals based on data items pulled in from the package worked perfectly. This is all beyond me.

And the last one, Issue 5:

For Issue 4, while I was experimenting I noticed that for Date, I had dragged in a query calculation that does a to_char() on the date field to get it in the required format. Now when I replace this with the Date field itself the report works fine and the cumulative values are perfect. But now to get the dates which is in timestamp format in the desired MMM DD, YYYY format I apply the data format property on the x-axis labels and guess what, that doesn't work no matter whether you try setting a pattern or you set the various Date properties in the Data format options.

[User Comment: Arash Z]
Click on the field on the chart that should appear as a date. On the properties click on the Source Type and change the source type to Report Expression. Under report expression pull the field you want to display. In the Data Format you can now change it to date and make the appropriate changes and it should appear on the report.

From all my above experiences, here is what I can say:

• Chart behaves differently than list reports.


• A chart query is not the same as SQL Query nor the same as the query generated by clicking "Tools > Show Generated
  SQL/MDX".


• In all the above cases I had at least 1 data item that was based on Count Distinct Aggregate function. Not sure if this
  has something to do with my issues. Though logically it shouldn't but since I have no other reasons to offer I may as well
  take the liberty.

Bottom line, charts are screwed in Cognos and are unreliable.

If anyone could explain the above scenarios that would be great. I am dying to figure this out

So there this was what was keeping me busy over the last week.

Cognos Disaster Week

Its been a while since I last blogged and that's because we have been having a ghastly week with Cognos behaving weirdly. I have noticed a lot of weird scenarios with Cognos not behaving as expected especially when working with Charts. And I a going to blog about what I have worked on over the last week and what weird issues we have faced here. Probably any of you who has come across this earlier could throw more light on the same.

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

Do you need to implement Date prompts in report pages without using macros? Then here is the solution using Render Variable. Remember a date prompt will always have a value (current date by default) and default selections cannot be dynamic values. They need to be static dates.

Requirement: Display Year, Retailer, Order Method, Revenue, Gross Profit for default date of current date - 30 days to current date.

Solution:

Step 1: Create basic list report using GO Sales package by dragging the required data items from the package.

Step 2: Create 2 Date prompts called From Date and To Date and name the prompts as FromDate and ToDate for referencing in JavaScript and include a Finish Prompt button.

Step 3: Create a filter in your query as [Date] between ?From Date? and ?To Date? and set it to required.

Step 4: Let us call this list and query as "Date Prompts Selected".



Step 5: Now to handle the default scenario. Copy paste the "Date Prompts Selected" list and query and name them as "Date prompts Default".





Step 6: Create a String Conditional Variable called "Date Prompt Values" with the following condition:

case when paramcount('From Date') = 0 then 'Date prompts Default' else 'Date prompts Selected' end

Step 7: Apply the conditional variable to the render variable property of the "Date Prompts Selected" list and "Date prompts Default" list.

Step 8: Now to implement the Current Date - 30 to Current Date scenario in the "Date prompts Default" query, modify the filter to:

[Date] between _add_days(current_date, - 30) and current_date

Step 9: To implement the Current Date - 30 to Current Date scenario in the Date Prompts using JavaScript:

Insert a text box prompt to identify the default run of the report. Name this as RunValue. Set the default selection to 1 to identify this as Run 1. Set the Visible property of this object to No.



Now insert an HTML Item below the prompts and insert the following script:

<script type="text/javascript">

function DefaultDateSelection()
{
var fW = (typeof getFormWarpRequest == "function" ?getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined)
{ fW = ( formWarpRequest_THIS_ ?formWarpRequest_THIS_ : formWarpRequest_NS_ );
}

var months = new Array('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');

var RunValueObj = fW._textEditBoxRunValue;
if(RunValueObj.value==1)
{
var ToTime=new Date();
var ToMonth=months[ToTime.getMonth()];
var ToDate= ToTime.getDate();
var ToYear= ToTime.getUTCFullYear();
var ToDate = ToMonth + ' ' + ToDate + ', ' + ToYear;

var FromTime = ToTime;
FromTime.setDate(FromTime.getDate()-30);
var FromMonth=months[FromTime.getMonth()];
var FromDate= FromTime.getDate();
var FromYear= FromTime.getUTCFullYear();
var FromDate = FromMonth + ' ' + FromDate + ', ' + FromYear;

pickerControlFromDate.setValue(FromDate);
pickerControlToDate.setValue(ToDate);

RunValueObj.value=2;
}
}
DefaultDateSelection();
</script>

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

Missing Features in Cognos

I wanted to maintain a list of good-to-have features in Cognos that are currently missing:

• Prompts - Tool tips for Prompt values, Data Items.
• Prompts - Default Selections in Prompts to be set to a data item rather than hard-coded values thus eliminating the need for Java Scripts to implement dynamic default selections.
• Chart - set width in % doesn't work. Not setting width makes the chart look ugly when there are less data points.
• Chart - unable to set Min and Max property to dynamic values.
• Chart - Unable to set data format for labels in some cases.
• FM - Using Parameter Maps to dynamically select schemas and not run into meta data callback issues.
• Reports - Ability to specify if burst reports with Master - Detail Relationships be run as 2 queries (one for Master and One for Detail) or have detail run for each master record.
• Reports - Include Burst Key in Subject Line of Email 
• Chart - setting values dynamically for color boundaries of the gauge palette, besides Max and Min. [User Contribution]
• Chart - better excel-like label options(for example, being able to show both % and absolute values for a pie slice (near the slice itself), or show a label (i.e. sales $ for region) on top of a column of a stacked chart which depicts a different (but linked) measure (i.e. % contribution to total) [User Contribution]
  

I will keep updating this list with items as and when I face issues. Your comments and updates are most welcome. There is no great purpose as such to maintaining this list. But probably it can serve as a good starting point for those wanting to verify the feasibility of certain functionality in Cognos.

Optional Prompt Pages

Based on a query I have received in my previous post from Dave on how to create optional prompt pages I have provided the solution below:

Requirement: To display Product Line / Product Type in report. User would be prompted to select either Product Lie Prompt / Product Type prompt in a prompt page. Based on the selection made, the next prompt page gets displayed showing either Product Line or Product Type prompts. This is a simple but not practical example just to demonstrate optional prompt pages option.

Step 1: Create 3 prompt pages. In Prompt Page 1 include a drop down prompt (with parameter name as, say Prompt Type) with static choices for selecting Product Line / Product Type. Remove the Finish, Back prompt buttons.

Step 2: In Prompt Page 2 include Product Line prompt. Remove Next prompt button.

Step 3: In Prompt Page 3 include Product Type prompt. Remove Next prompt button.

Step 4: Create a conditional string variable with the expression - case when ParamDisplayValue('Prompt Type') = '1' then '1' else '2' end.

Step 5: In Prompt Page 2 apply the conditional variable for the render variable property and set the page to render for 1.

Step 6: Likewise in Prompt Page 3 apply the conditional variable for the render variable property and set the page to render for 2.

Run the report, you will be prompted for prompt type choice. When a prompt type is selected, the corresponding prompt page is displayed.

Creating a Row in a Crosstab that Calculates the Difference between the Columns

If you would like to create a crosstab that calculates the difference between columns as shown below:



Create a calculated Measure running-difference([Revenue]) where [Revenue] is the measure for which you wish to calculate the difference.

Some questions that I have with the above method is that running-difference is not available in the list of functions when you use a dimensional model. This is a relational function that we are using against a dimensional model, so I am not sure of the performance impact that this would have.

What other information that I have noticed is that no longer does the Native SQL gets generated. Only the Cognos SQL gets generated. Once the running-difference item is deleted then the Native MDX gets generated.

So now, my question is why doesn't Cognos include a lot many functions as part of the dimensional model function set? This causes a lot of issues for the developers.

Layout Calculations involving _add_days / _add_years functions

Note that the _add_days, _add_years functions in query calculations take a date expression as an input while the same in a layout calculation takes a timestamp as an input. Hence while using layout calculations you need to convert the date to timestamp as shown below:

_add_years(date2timestamp(Today()),-1)

Cognos Deployment Across Environments - Part 2

This is a continuation of deployment topic. We would be focussing on the import options for the archive. Import is fairly simple. Once you have the archive copied over from the source system to the target system, follow the below steps to do the import:

Step 1: Navigate to Content Administration from Launch > IBM Cognos Administration > Configuration

Step 2: Click on the New Import Icon.

Step 3: Select the Archive you wish to import.

Step 4: Provide a name for the import activity. You can save the activity without running the import ad can run it a later point of time.

Step 5: Most options are disabled as they are imported from the export settings.

Step 6: You can set the owner of the imported objects. Please refer to notes on this from Part 1. Click Next.

Step 7: Review the options and select Next

Step 8: Select options to save the import and run once or save the import and schedule it for a later date or to save only.

Step 9: Click Finish

Step 10: If you had selected Save and Run once, then select the time you wish to run this import at and click Run.

The reports or packages are now imported into the target environment.