Defect - Advanced Drill Link with Higher level Default prompt selections?

We have report with a value prompt on the report page and its default value is controlled by a prompt macro. The report also has a Pie chart and a Crosstab both based on different queries whose data items are linked for drills.The report is based on a cube.

The observation is that when the default value for the prompt is set to a higher level of the dimension say the year level in a Time dimension and when a user selects any value from the drop down and does a drill on the pie chart categories member the drill links don't work in the Crosstab only the pie chart gets updated.

This happens only the first time a selection is made. Selecting any other value and executing a drill on the pie chart categories and then selecting the same value and then executing a similar drill does not re-produce the above error.

This also does not happen if you execute the drill without making any selections. Any selection later doesn't re-produce the error.

So basically it seems like this happens for the  first selection made and when you have a higher level member set as the default through a prompt macro. Once a drill is executed and then any number of drill executions later doesn't re-produce the error.

This has been observed in a Pie - Crosstab combination and not in a Stacked Chart - Crosstab combination.

Some screenshots against Go Sales Cube to demonstrate the observation against a simmplified requirement:

First run of the report:


On selection of January 2004 from the drop down and then executing a drill up on Pie chart for January 2004:


The drill does not get reflected in the crosstab.

On selection of Q1 2004 and then executing a drill up on the Pie chart:


The drill is reflected in the crosstab.

When January 2004 is set as the default, first run of the report:


On selection of February 2004 and then executing a drill up on the pie chart:

The drill is reflected in the crosstab.
 

InScope Report Function

How do you display a measure with different data formats against different Series elements in a chart? This is where conditional style and InScope report function comes to the rescue.

Requirement: Display a chart with Revenue measured against Product Line members in the primary axis and against web Order Method against the secondary axis. Display Revenue with 3 decimal places against Product Lines and 2 decimal places against Web order method.

This requirement can be extended to display different data formats against different series members.

Solution:

Step 1: Create the chart with Revenue as the default measure, Product Line dragged into Primary Series and Web Order Method dragged into Secondary Series and Year dragged into Categories.

Step 2: Select Revenue and create an advanced conditional style with the expression as InScope(Web)

Set the format in the style properties for this condition.



Likewise create another advanced condition for Product Line.

Cognos 10 Chart Properties

While I was on the subjects of Cognos 10, thought I will write about some properties of the Charts that are positioned differently between the older versions of Cognos and the newer ones as I don't want people to get frustrated like I did trying to find some properties with the newer version.

The Line Style and Marker properties are now positioned on the Palette dialog box which according to me isn't meaningful.





I had spent considerable time trying to find these properties out that had almost given up with the newer charts and had planned on using the legacy charts and opening a ticket with IBM. Hope this helps others in some way.

Chart Conditional Measures - Conditional Legend

When you create a chart based report that accepts multiple measures as input using case statements the legend is displayed for all the series and not just for the measures selected. To avoid this, disable the chart provided legend and create a manual legend next to the chart and use the colors set in the chart palette to display the right colors for the right series.




 

Bursting Charts / Crosstabs

Note: Charts / Crosstabs cannot be bursted using a single query as Burst query, Burst Recipient Query and Crosstab query.

You would need to create a Master Query and use that as Burst query and Burst Recipient query. Create a master detail relationship with the Chart / Crosstab query.
 

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.

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.