This is all beyond me.ДЇor 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. And Lo, the report looked perfect.ДЄgain, in another report Running-Totals based on data items pulled in from the package worked perfectly. 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. This happened just for the Gross Revenue line while the Customer Qty line was perfect.ДЄfter 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 now the chart has a spike for the 1st Date and the rest of the dates look perfect.

The tabular data is perfect but then the chart shows the first Date value totally off for Gross Revenue. The sorting has been set on Date to get the cumulative values correct. I have a cumulative chart that displays Date, Running-total (Customer Qty) and Running-Total (Gross Revenue). Again the calculations for % are missing not just in native SQL but also in Cognos SQL. But you get the message right? Why 3 subqueries when T2 satisfies my purpose. This is my simplified version of the native SQL generated by Cognos. Where T1.C1 = T2.C1 or T1.C1 is null or T2.C1 is null (select Date, count(orders), total(Orders_24), total(Orders_48) from Orders group by Date) T2 (select Date, count(orders) from Orders group by Date) T1, In my earlier Orders report, I notice the the query from 'Tools > Show Generated SQL/MDX' includes 3 subqueries as follows: 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. 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. 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. 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. Seems like what we see is not what we get with Chart queries. A lot of weird issues have been surrounding with he Chart queries.

Cognos sql prompt