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

Passing multiple parameters by selecting single prompt

Started by AABI, 09 Jul 2018 07:27:30 AM

Previous topic - Next topic

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
([Sales (query)].[Time].[Year] * 100) + [Sales (query)].[Time].[Month (numeric)]

then, if M is a date, my filter looks like this
[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.