Real World OBIEE: Demystification of Variables Pt. 3
In part two of this blog series, I went over using Repository, System and Presentation Variables to make reports dynamic for any series of time. In part three, I am going to talk about making reports dynamic for periods of time using built in functions within Answers itself.
Real World
While it's a lot more efficient to create Repository Variables to use in filters and prompts for time dimensions, sometimes it is simply not possible. Perhaps you are a front end developer for OBIEE and have no access to the RPD or the database . Perhaps you have no communication with the person in your organization who handles all of the RPD development and therefore can not submit any change requests.
Don't worry. We've got you covered.
There are several functions and tricks you can use within Answers itself to make reports dynamic and eliminate having to hardcode dates.
The Scenario
I am going to use the same scenario I used for part two of this series for the example one. To recap, here are the requirements:
I have been asked to create a report that is going to reside on a products dashboard. It needs to have the same product grouping as the report I used in part one of this series, needs to contain 'Gross Rev $', 'Net Rev $' and '# of Orders' and have a prompt that can select between the first and current day of the month and every day in-between. The person who requested the report wants the prompt to change dynamically with each month and does not want users to be able to select future dates.
In part two, I used a custom SQL statment which used Repository Variables I created to populate all the date values from the first of every month to the current date for Variable Prompts. There is a gap in the data loads for # of Orders in which data does not update until the 2nd or 3rd of each new month. The person who requested the report wanted a summary of the previous months '# of Orders' to be shown until the data is updated for the current month. I used a Repository Variable that returned the value of the previous month with the current year and used a CASE statement with along with Filter Expressions to switch between the Filter Expression using the Repository Variable (Prev_Month) if the date was <=2 or if # of Orders is null and the Filter Expression which contained the Start Date and End_Date Presentation Variable placeholders which were defined in my Variable Prompts.
Example One
In this example, I have to figure out a way to make the report dynamic with only the functions available within Answers. There are two parts to this example. First I need to use a function that will return the previous month's value for the Calendar Year Month column to use with the '# of Orders' column. This will replace the Repository Variable Prev_Month I used in part two of this series. Second I need to write a new SQL statment for the Start Date and End_Date prompts I created in part two and also define a new SQL statment for the default values in those prompts.
Part 1
I am going to start by creating a new statement to return the previous month's value for the Calendar Year Month column. I can use the TIMESTAMPADD function in conjunction with the extraction syntax 'YEAR' and 'MONTH' to return the desired results. Let's take a look at the entire statment and then I will break it down.
CAST(YEAR(timestampadd(SQL_TSI_YEAR, -1,CURRENT_DATE))*100+MONTH(timestampadd(SQL_TSI_MONTH, -1,CURRENT_DATE)) AS VARCHAR(6))1. TIMESTAMPADD - This is what defines that I am going to use addition to return a date by adding or one date to another.
2. SQL_TSI_YEAR and SQL_TSI_MONTH - The first argument in the function. It defines what interval of time the function will work with.
3. -1. This the interval of time that is compared to the third argument.
4. CURRENT_DATE - The third argument in the function. This is what the second argument is compared against.
5. YEAR and MONTH - This is the extraction syntax that will return only the year and the month respectively.
Also notice that I have used VARCHAR(6) for the CAST argument. If I use VARCHAR, I can specify the exact number of characters I want returned.
Now I need to copy my column formula and paste it into the column formula that I created for '# of Orders' in part two of this series.
I am going to replace the Prev_Month Repository Variable with my statement, which will look like this.
CASE WHEN DAY(CURRENT_DATE)<=2 OR "Sales - Fact Sales"."Measures"."# of Orders" IS NULL THEN
FILTER("Sales - Fact Sales"."Measures"."# of Orders" USING ("Sales - Fact Sales"."Periods"."Calendar Year Month" =
CAST(YEAR(timestampadd(SQL_TSI_MONTH, -1,CURRENT_DATE))*100+MONTH(timestampadd(SQL_TSI_MONTH, -1,CURRENT_DATE)) as varchar(6)))) ELSE
FILTER("Sales - Fact Sales"."Measures"."# of Orders" USING ("Periods"."Day Date" BETWEEN @{pv_start_dt}{date '2015-10-01'} AND @{pv_end_dt}{date '2015-10-15'})) END
If I run the report, my results return as expected.
Part 2
Now I need to write a new SQL statement for my Start Date and End Date prompts. In order to do this, I am going to need to use two functions: TIMESTAMPS and CURRENT_DATE. First, lets take a look at the TIMESTAMP function.
I am going to use the TIMESTAMP function to filter the Day Date column for the first day of the month. To demonstrate, I am going to create a new analysis and use the TIMESTAMP function in a column formula. My column formula looks like the following:
TIMESTAMPADD(SQL_TSI_DAY, -DAYOFMONTH(CURRENT_DATE) +1, CURRENT_DATE)This formula can be broken down into four parts:
1. TIMESTAMPADD - This is what defines that I am going to use addition to return a date by adding or one date to another.
2. SQL_TSI_DAY - The first argument in the function. It defines what interval of time the function will work with (in this case days)
3. -DAYOFMONTH(CURRENT_DATE)+1 - This the interval of time that is compared to the third argument. In this case I am taking the negative value of the day of the month, adding 1 and then adding it with current date which always returns 1 or the first day of the month.
4. CURRENT_DATE - The third argument in the function. This is what the second argument is compared against.
This is only scratching the surface of what you can do with the TIMESTAMP function. If you would like more information, check out the blog on TIMESTAMPS written by Brian Hall.
I am going to add an additional column to the Criteria and use the CURRENT_DATE function in a column formula.
Now I am going to click on Results to show the results of the TIMESTAMP function and the CURRRENT_DATE function.
From the results you can see that I have both the first day of the month and the current date. Now I need to convert this into a filter for the Day Date column so that I can get the logical SQL query for my Start Date and End Date prompts.
In the New Filter window, I need to change the operator to is between and click on Add More Options to add a SQL Expression.
In the SQL Expression box, I need to put the TIMESTAMP function for current date from the previous example. In addition I need to add another SQL Expression for the CURRENT_DATE function.
When I return to my Criteria, I can see the filter I created in the Filter window.
I can click on Results to run the report. The results for the Day Date column return as expected.
Now I can click on the Advanced tab and copy the logical SQL statement to use for my Start Date and End Date prompts.
Now I am going to paste the following into my Start Date Variable Prompt
Choice List Values > SQL Results
SELECT "Sales - Fact Sales"."Periods"."Day Date"
FROM "Sales - Fact Sales" WHERE ("Periods"."Day Date" BETWEEN TIMESTAMPADD(SQL_TSI_DAY, -DAYOFMONTH(CURRENT_DATE), CURRENT_DATE) AND CURRENT_DATE)
ORDER BY "Periods"."Day Date"Default Selection > SQL Results
SELECT
TIMESTAMPADD(SQL_TSI_DAY, -DAYOFMONTH(CURRENT_DATE) +1, CURRENT_DATE)
FROM "Sales - Fact Sales"
FETCH FIRST 65001 ROWS ONLY
For the default selection, I am using a SQL statment that is selecting the first day of the month using the same TIMESTAMP function used in the above query from my subject area "Sales - Fact Sales".
Now I need to change the SQL query for both the Choice List Values and Default Selection for my End Date Variable Prompt.
I am going to use the same SQL query for the Choice List Values in my End Date prompt as I did in my Start Date prompt. I am going to change the default selection to the following:
SELECT
CURRENT_DATE
FROM "Sales - Fact Sales"
FETCH FIRST 65001 ROWS ONLY
If I go to the Display window, I can view the results of my changes.
Notice that the results are exactly the same as the results in part two of this series.
I can save the dashboard prompt and go to my dashboard and test the prompt.
In Conclusion
In part one of this series, we looked at using Bins, CASE statements to create custom grouping for values and switch between those groups and values using Presentation Variables.
In part two of this series, we looked at creating Repository Variables to make reports dynamic using those Repository Variables in Variable Prompts and passing them into column formulas using Presentation Variables.
In the third and final part of this series, we looked at making reports dynamic by using built in functions within Answers such as TIMESTAMPS and CURRENT_DATE.
My hope is that you can take these examples and apply them in your own OBIEE development. If you would like to know more about front end or RPD development, please check out the variety of training courses we offer at Rittman Mead. Until next time.