Passing multiple parameters by selecting single prompt

[AABI]

Hello all,

I am building a report that shall display data for a selected month (M) , the month prior to the selected (PM) , and same month previous year. (PYM) Ex M=May-2018 , PM = April-2018, PYM = May-2017.
The user shall only select month M from the prompt , and data will be generated for all 3 periods ; M,PM and PYM.
I have created in report studio a query that calculates the three months (Month_SQL), and also based on the data model i have created three identical queries that will be used in the crosstabs to display the data.
Ex. select May-2018 as M , April-2018 as PM, May-2018 as PYM from dual

Query1 has detail filter condition Month = ?pM?  , Query2 has detail filter condition Month = ?pPM?  and Query3 has detail filter condition Month = ?pPYM?   
 How can i build a prompt where i can just select the month M , and it will pass M to parameter pM of Query1, PM to parameter pPM of Query2 and PYM to parameter pPYM of Query3 ?

I don't want to use sql and include parameters in the sql script, that way would have been easy .

Any idea ?
AA_BI

[CognosPaul]

Is this relational or dimensional? CQM or DQM?

At the end of the day a parameter selected from a prompt is an object you can work with. If it's a dimensional source you could just do prevmember(#prompt('M','mun')#). If it's relational, I'd recommend using a date as the source for the month to simplify date calculations.

[dougp]

Pass only the month parameter (M).  Use that value to calculate each of the others.  You can either use a date for the value, or use something that you can easily convert to a date.

From the sample data, if I define YrMo as

Code: [Select]

([Sales (query)].[Time].[Year] * 100) + [Sales (query)].[Time].[Month (numeric)]
then, if M is a date, my filter looks like this

Code: [Select]

[YrMo] in ((year(?M?) * 100 ) + month(?M?), (year(dateadd({month}, -1, ?M?)) * 100 ) + month(dateadd({month}, -1, ?M?)), ((year(?M?) - 1) * 100 ) + month(?M?))
This is written for MS SQL.  You may need something different, or you may want to perform the calculations in Cognos macros rather than in SQL.