Author Topic: Passing multiple parameters by selecting single prompt  (Read 2216 times)

Offline AABI

  • Associate
  • **
  • Join Date: Jul 2018
  • Posts: 1
  • Forum Citizenship: +0/-0
Passing multiple parameters by selecting single prompt
« on: 09 Jul 2018 07:27:30 am »
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

Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,851
  • Forum Citizenship: +265/-1
    • Paul's Cognos Blog
Re: Passing multiple parameters by selecting single prompt
« Reply #1 on: 09 Jul 2018 01:52:40 pm »
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.


Offline dougp

  • Statesman
  • ******
  • Join Date: Jul 2014
  • Posts: 453
  • Forum Citizenship: +21/-1
Re: Passing multiple parameters by selecting single prompt
« Reply #2 on: 10 Jul 2018 06:15:52 pm »
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.

 


       
Twittear