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.
 

Hiding Left-Most Crosstab Columns

How do you hide left-most columns in a crosstab without impacting the alignment of other objects on the crosstab?

You may ask, why would anyone want to hide columns in the first place when you have the "Properties" property? That's because for some reason on 10.1.1 this doesn't seem to be working under certain scenarios. May be because there are prompt macros in the data items I want to use for drill throughs even though I don't want them on the layout. I had even set them on all possible edges. Once I have this figured out with IBM, I will update on why the Properties property isn't working.

Latest update from IBM on why the "Properties" property is not working in a crosstab against a cube in 10.1.1 is possibly because of a defect.

So as a work-around, I had to pull in the columns and hide them.

Properties that you need to set to hide the columns:

Background Color : White

Foreground Color : White

Font : 0px

Size & Overflow : 0px

Border : 1 pt None Black

Padding : 0px 0px 0px 0px

The only down-side is that when exported to excel you will see 2 empty columns to the left of the crosstab object.

User Query - Display Totals for Top 2 Ranks while displaying data for all other ranks in a crosstab?

This is in response to a user query.

Requirement: Display Ranks across Columns in a Crosstab and the Total column needs to include only the Top 2 ranks along with Overall Total column for all Ranks.

Solution:

Step 1: Create the base query Query 1 with the following data items: Product, Year, Sales, Rnk, Top 2 Sales Total.

Rnk - Rank([Sales] for [Product])
Top 3 Sales Total - case when [Rnk] < 3 then [Sales] end

Step 2: Create the outer query Query 2 by referencing the base query Query 1. Drag the data items - Product, Year, Sales, Rnk, Top 2 Sales Total from the referenced query.

Step 3: Set the aggregate function of Rnk to None.

Step 4: Create the crosstab by dragging in the data items Product, Sales, Top 2 Sales Total from Query 2.

Step 5: Create the Overall Total column by clicking on the Summary button for Rank column.




 

Disabling Drill Through on Totals in Crosstab

If you have Crosstab Reports with drill through on the measure and you also have Totals. You would notice that the drill through is applied on the Totals column as well. If you try removing the drill through on the Totals the drill through on the measures also gets removed.



Solution:

Step 1: In the Total Column, set the Define Contents property to Yes. The measure value disappears in this column.



Step 2: Now re-drag the total measure into the column from Insertable Objects after unlocking the column.



Step 3: Navigate to Crosstab intersection for the column and set the Source Type to Cell Value.



The Drill Through Link is no longer applied on the Total Column.



 

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.
 

Highlight Current, Past and Future Data in Crosstab

Those who have tried conditional formatting in crosstab know that its a pain to do so especially if your condition is based on data items not part of the crosstab.

Requirement: Display for each month, for each weekending date the Revenue spread across the days of the week. Highlight the past weeks and past dates in Current week in Orange and current date and future dates in current week in Yellow and all other Future Dates in white.



Now the challenge is that the dates are not displayed rather the Day of Week. So the only items we have to work with are Weekending Date and the Day of Week.

Solution:

Step 1: Create a list report to display the Months.

Step 2: Create the crosstab inside the list with master - detail set on Month and section on Month.

Step 3: Drag the Weekending Date data item and create the Weekday data item: _day_of_week([DTE],1) and use case statement to display Monday - Friday.

Step 4: Create the conditional variable to highlight the required data as below:


case when
(
string2date([SummaryQ2].[Week Ending Display]) < _add_days(date2timestamp(today()),7 -_day_of_week(date2timestamp(today()),1))
)
or
(
string2date([SummaryQ2].[Week Ending Display]) = _add_days(date2timestamp(today()),7 -_day_of_week(date2timestamp(today()),1))
and _day_of_week(date2timestamp(today()),1) > ([SummaryQ2].[Days])
)
then 'Orange'
when string2date ([SummaryQ2].[Week Ending Display]) = _add_days(date2timestamp(today()),7 -_day_of_week(date2timestamp(today()),1))
and _day_of_week(date2timestamp(today()),1) < = ([SummaryQ2].[Days])
then 'Yellow'
else 'White' end


Step 5: Associate the crosstab intersection with the conditional variable and set the corresponding colors.

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.