How to create a custom yes/no prompt parameter to handle report logic?

[psrpsrpsr]

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:




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!

[CognosPaul]

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:

Code Select Expand

[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?

[psrpsrpsr]

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.

[CognosPaul]

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

Code Select Expand

[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:

Code Select Expand

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

[psrpsrpsr]

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

[CognosPaul]

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.