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.