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?