Let's say I work for a website, creating a report with namespaces for 'Customers', and 'Events'. 'Events' returns actions that Customers take, like subscribing to the website.
I have a report that includes a metric that requires a customer-level join from 'Events' on day X to the value of a billing data item in 'Customers' on day X+3. For a week, the date range looks like this:
(http://i1305.photobucket.com/albums/s557/PSRNHME/date_ranges_zpsgczdegdl.jpg) (http://s1305.photobucket.com/user/PSRNHME/media/date_ranges_zpsgczdegdl.jpg.html)
So I have 3 queries:
1.) 'Events' (A 'prior week flag' is available from a date dimension)
2.) 'Events offset' (This must be filtered to events that occurred during a -3 day offset from the 'regular' date range.)
3.) 'Customer' (A 'prior week flag' is available from a date dimension)
I want to make it as easy as possible for users to select whether they want to see prior week, prior month, etc. So what is a more effective way to achieve this output?
#1.) Creating a parameter based on the 'prior week flag' for 'Events', then create a filter in 'Events offset' like:
[Event Date] BETWEEN _ADD_DAYS( MINIMUM([Events].[Event Date]) , -3 ) AND _ADD_DAYS( MAXIMUM([Events].[Event Date]) , -3 )
Pros: Doable
Cons: Requires user to select two parameters instead of 1, requires cross join
OR,
#2.) Create a custom 1/0 or yes/no prompt parameter (p_prior_week_yes) which is used in all 3 queries to modify the filter ranges via CASE statement, like:
Events:
[Event Date] BETWEEN CASE WHEN p_prior_week_yes = 1 THEN _add_days(_add_days(current_date, - _day_of_week (current_date,1)) , -7) ELSE 2016-01-01
AND CASE WHEN p_prior_week_yes = 1 THEN _add_days(current_date, - _day_of_week (current_date,1)) ELSE CURRENT_DATE-1
Events Offset:
[Event Date] BETWEEN CASE WHEN p_prior_week_yes = 1 THEN _add_days(_add_days(current_date, - _day_of_week (current_date,1)) , -10) ELSE 2015-12-29
AND CASE WHEN p_prior_week_yes = 1 THEN _add_days(current_date, - _day_of_week (current_date,1) , -3) ELSE CURRENT_DATE-1
Customers: (same as Events)
Pros: User friendly, they only have to select 1 parameter
Cons: I don't know how to create this parameter :)
My question is: how do I create a custom yes/no or 1/0 parameter to accept user input if I don't have a field in my framework to offer those values? Please be as descriptive as possible for how to achieve this.
Thanks!
Are you using DQM? If so, it is possible to use a case statement in a macro function. If not, it's still possible but a little more difficult.
Assuming you are using DQM, you could do something like this:
[Dim].[Time].[Date] between
#
case prompt('prior_week_year','integer','1')
when 1 then '_add_days(_add_days(current_date, - _day_of_week (current_date,1)) , -7) and _add_days(current_date, - _day_of_week (current_date,1))'
else '2016-01-01 and current_date-1'
end
#
Is event offset supposed to call the same date field as event? If you're looking for all subscribers from the previous 3 days, why not just extend the previous week range to 10 days?
Hi CognosPaul, I am not using DQM.
Event Offset is supposed to call the same date field as Events, but not the same date values - it is supposed to offset the date range of the prior week by -3.
So for example, I need the following date ranges to create the metric:
Events: Mon. 10/16/17 to Sun. 10/22/17
Events Offset: Fri. 10/13/17 to Thurs. 10/19/17 (-3 day offset)
Customers: Mon. 10/16/17 to Sun. 10/22/17
I think I can achieve this fairly easily by use CASE statements in the filters, but it is creating the prompt parameter that I don't know how to do.
Okay, I understand.
Cognos will usually intelligently replace a simple case statement on a prompt with the correct values.
With the following filter, selecting Week
[Date] >=
case #prompt('dateRange','string',sq('Week'))#
when 'Week' then current_date - 7 day
when 'Month' then '2017-02-01'
when 'Year' then '2017-03-01'
else '2015-01-01' end
and
[Date]<=current_date
I get the following filter in the SQL:
"TIME_DIMENSION"."DAY_DATE" >= CAST(DATEADD(DAY, -7, CAST(CURRENT_TIMESTAMP AS DATE)) as DATETIME) AND
"TIME_DIMENSION"."DAY_DATE" <= CAST(CAST(CURRENT_TIMESTAMP AS DATE) as DATETIME)
You should be able to use that logic to populate the filters in all 3 queries the way you need.
Thanks CognosPaul, 2 questions:
1.) What is the sq('Week') function in your example?
2.) How can I incorporate this prompt as a selectable value from a prompt page? That is the convention on the BI team I work on, so I want to make sure my report design is in accord.
Something like a value prompt:
View Current Week Only? Select 1 for yes.
1
0
Thanks
In the prompt function the third value is the default. If I were to leave it just as 'Week', it would return Week as a literal, which is not a function or a reserved word. So that would fail. Wrapping it in the SQ function, it wraps the value in single quotes turning it into a string.
You can create a prompt object and give it static values.
Use | Display
-------+------------
1 | Yes
0 | No
Then you can set the default on the prompt to 1 or 0.