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
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.
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.