Friday, March 19, 2010

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.

4 comments:

Arash Z said...

I've run into exactly the same issue in regards to not being able to properly format the dates in a chart. Have you found any solutions to the issue since this was posted?

Arash Z said...

See if this helps

http://www-01.ibm.com/support/docview.wss?uid=swg21341061

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.

Zephyr said...

Thanks Arash for your input. That really helped. I have updated the article.

Rajini said...

Hi All,

We have a common issue in Cognos chart. if we have more values in dimensional field and it will not display properly in chart. Ex. I have date prompts and have product in chart and QTY in measure. so you will get different number of products in chart. how will you display default height & width if you have 100 or 200 or 500 or 1000 products in chart?

I have been facing this issue in many reports. How to set default settings to change chart size based on number of Product?