Nesting Data Items in Chart

Requirement: Display Country wise Revenue and Quantity for Quarters in each Year with Year and Quarter displayed along x axis and each countries revenue appearing as a different series.



Solution:

In chart x axis, drag Quarters and Nest Years on top of Quarters to display quarters in each Year. Then for displaying country wise revenue drag Countries into the Series section and nest Revenue underneath the Countries data item.

Implementing Optional Tables / Filters Inclusion based on Prompt

Requirement : Prompt the user to select either Order Method or Retailer Name and based on selection filter the data for Order Method = Fax or Retailer Name = 'ActiForme'.

Solution :

Create a filter in the query as follows:

Case when ?UserSelection? = 1
then ([Sales (query)].[Retailer].[Retailer name] = 'ActiForme')
else
([Sales (query)].[Order method].[Order method] = 'Fax')
end

where 1 - Retailer Name and 2 is Order Method.

Another approach you would mention for the above requirement is to use a filter as below:

(?UserSelection? = 1 and [Sales (query)].[Retailer].[Retailer name] = 'ActiForme')
or
(?UserSelection? = 2 and [Sales (query)].[Order method].[Order method] = 'Fax')

The disadvantage with this approach is that your query will include both the Retailer and Order Method Tables. Now assume your requirement was to optionally display data either from Detail Fact Table or Summary Fact Table that includes a filter on Detail / Summary fact table based on selection.

For this requirement, if you had created a filter like the second one mentioned your query would have included both the detail and summary tables thus resulting in a stitched query that would be hitting 2 fact tables. This would lead to performance problems. Hence the 1st approach is beneficial in such cases.

Sorting Asc / Desc on Same Column

Reqmt: Create a report displaying Product and Sales and Sort Product Asc or Desc depending on user selection.

Solution:

Step 1: Create a Prompt Page to accept user selection for Asc / Desc. Lets call this prompt as SortSel and have use value for static choices set as 1 for Asc and 2 for Desc.

Step 2: Create report page that includes Product and Sales Data Item.

Step 3: Create 2 data items called as SortAsc, SortDesc.

SortAsc - Case when ?SortSel? = 1 then Product else '1' end
SortDesc - Case when ?SortSel? = 2 then Product else '1' end

Step 4: In Advance Sorting for the list Include SortAsc and set sort on this to Asc. Then include SortDesc and set Sort on this to Desc.

When User selects 1, the SortAsc data item is populated with Product and SortDesc is populated with 1 and hence Sorting Asc on SortASc produces the desired result and a sort desc on SortDesc has no impact.

When User selects 2, the SortAsc data item is populated with 1 and SortDesc is populated with Product and hence Sorting Desc on SortDesc produces the desired result and a sort asc on SortAsc has no impact.

New Articles in the Offing

I have been caught up with work over the last week and hence couldn't post any new article. Don't think I will be able to post any over the next couple of days as well.

Anyways just thought I would make a note of the various topics to cover in my articles.

So here's the list:

• Tree Prompts on Relational Models


• Bursting reports to local Directory


• Nesting Data Items in x axis

Let me know if there is anything in particular you would like me to cover.

Custom Calendar / Date prompt Control in Cognos

How many times have there been requirements to make the Date Prompt behave in a manner that it is not programmed to do so? I was stumped with a similar such requirement recently.

The requirement was to have the date prompt default to the corresponding Saturday's date if the user selected a week date or even better to disable all weekdays in the date prompt.

Now any Cognos developer would agree with me that this is impossible to achieve using the default Cognos Date prompt. Agreed and I even tried reasoning with the clients but to no avail. They were already aware that Cognos allowed JavaScript to be written and so they were adamant in their requirement. Though Cognos allows JavaScript it is not meant to be misused so much. Anyway, I cautioned them about upgrade issues, non-support of JavaScript by Cognos and got to work.

For those of you with such requirements the best would be to get a Custom Drop Down Calendar Script from the innumerable Java sites and paste the same in a HTML item in your report and you are good to go.

I did the same.

• Found a good to use calendar prompt and included the same in my Cognos report.


• Modified the code to handle my weird scenarios.


• Created a HTML submit button and a hidden text box prompt.


• Wrote a script behind the Submit button to populate the hidden text box prompt with the selected date value and submit the report to Cognos.