If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Dynamically constructed date mun performs slow

Started by bberryhill, 15 Nov 2013 01:41:54 PM

Previous topic - Next topic

bberryhill

With the help of Paul and others on the forum I created a report using a dynamically constructed MUN for the date.

My approach uses a value prompt that displays relative date options along with the last 6 months from my date dimension.  The prompt's parameter name is pSelectedDates.

Use                          Display
[CurrentWeek]        Current Week
[CurrentMonth]       Current Month
[PreviousMonth]      Previous Month
July
August
September
October
Novemeber

My report query contains a data item (SelectedPeriod) that prompts for the selected date using a prompt macro (same parameter name, pSelectedDates, as the value prompt on the prompt page):
#promptmany('pSelectedDates','token')#

The query also contains data items for the relative date options.  This is the [PreviousMonth] item.  It uses a parameter map in FM to return the end portion of the MUN.  Day, week, month etc MUNs can be returned based on the parameter passed.  It also uses dimensional navigation to get the previous month (the MUN returns the current month).
prevMember ([Dimensions].[Date].[Date].[Month]->#$CurrentPeriodLookup{3}#)

The problem is there is a significant performance difference when the user selects one of the relative date options as opposed to selecting a month option created by the prompt query.  If the user selects "October", the results are returned about 10 times faster than if the user selects "Previous Month".

As far as I can tell, both options return a MUN to the SelectedPeriod data item.  I can not figure out why the huge difference in performance. I'm using Cognos 10.1.1 on a DMR.  I am not using dynamic query mode but plan to get there one day.

Any ideas?