Date between in filter and title when using presentation variable from calendar dashboard prompt or drop-down list in OBIEE

In my previous post how to change date format mask in date dashboard prompts - drop-down list and calendar I showed my solution of date format problem when using drop-down list and calendar dashboard prompt and repository variable as default. I use same prompts and localedefinitions.xml settings as in my previous post.

Now we need to create a report in Answers and then show date prompts in report title. First we use a calendar prompt for input which is in d.m.yyyy format and later in drop-down prompt which is in dd.mm.yyyy format. According to input formats our output format will be the same for both calendar and drop-down list and will be something like Amount sold from date dd.mm.yyyy to date dd.mm.yyyy. You can see that although we have two different date formats as inputs (d.m.yyyy for calendar and dd.mm.yyyy for drop-down list) there is only one format in title (dd.mm.yyyy) and it is with leading zeros for date and months values.

Example 1 - using calendar date prompts in filter and report title

Dashboard prompts:



Column formula for the first calendar date prompt:

case when 1=2 then TIMES.TIME_ID else cast ('1.1.1900' as date) end

and for the second:

case when 1=2 then TIMES.TIME_ID else cast ('1.1.2900' as date) end

Column formula for drop-down list date from field:



Column formula for drop-down list date to field is:

EVALUATE('TO_CHAR(%1,%2)' as varchar(20),TIMES.TIME_ID,'dd.mm.yyyy')

For this example we use only calendar prompts.

We created columns in Answers as in picture:



Amount sold -> SALES.AMOUNT_SOLD

p_date_from -> '@{p_date_from}'

original presentation variable date from p_date_from from a calendar

p_date_to -> '@{p_date_to}'

original presentation variable date to p_date_to from a calendar

Variable length ->



Title date from in dd.mm.yyyy format ->



*This case always returns dd.mm.yyyy date format

Title date to in dd.mm.yyyy format ->



*This case always returns dd.mm.yyyy date format

We add filter on TIMES.TIME_ID column:



TIMES.TIME_ID between '@{p_date_from}{1.4.1999}' and '@{p_date_to}{1.5.1999}'

Default values are only for test in Answers.

We add narrative view as replacement for a title view because we want to include last two column fields from Answers (Title date from in dd.mm.yyyy and Title date to in dd.mm.yyyy). We call these values in title with @5 and @6.



Delete a classic title view first:



We make a dashboard page with our dashboard prompts and Answers report, so we can test it now.

Initial start:



We didn't touch calendar, we just start report with initial repository variable which is in dd.mm.yyyy format so at this first point our presentation variables p_date_from and p_date_to is filled with dd.mm.yyyy 01.01.1999 (yellow) although prompt fields are showing d.m.yyyy. (blue). We can hide last two columns (red) because we use them only in title. Green part is populated in narrative view which use last two columns (case). Title will always show date in dd.mm.yyyy format.

If we now choose value from a calendar:



We see that the yellow part is similar to dashboard prompt fields (we populated it from a calendar). Everything else is like in previous initial start part. Title will always show date in dd.mm.yyyy format.

NQQuery.log for this example:



Example 2 - using drop-down list date prompts in filter and report title

In the report that we created in the previous example we need to replace p_date_from with p_date_from2 and p_date_to with p_date_to2 in all columns that use presentation variables and also in filter, to filter our report now with drop-down list date prompts.

So no mather if we are doing initial start with repository variable or if we select value from a list everything is the same (red), the format in the title, presentation variables and drop-down list fields is in dd.mm.yyyy format already, so we don't need our CASE for dd.mm.yyyy format in title.



This is NQQuery.log for this example:



When retreiving rows in both example 1 and 2 Oracle does implicit conversion char to date so index TIMES_PK is used on TIMES.TIME_ID column.

If you run all these statements in database:

select 1 from times where time_id='1.1.1999' --example 1
select 1 from times where time_id='01.01.1999' --example 2
select 1 from times where time_id='1.01.1999'
select 1 from times where time_id='01.1.1999'

you can see that in all cases Oracle use TIMES_PK index, so implicit conversion char to date is present according to NLS settings in session/database.

External table authentication and row-wise initialization in OBIEE

There is a topic in Oracle Business Intelligence Server Administration Guide about security (chapter 15 - Security in Oracle BI).

Authentication is process where Oracle BI Server checks username and password after user signs on, to verify that the user have necessary permissions to login and retrieve the data.

Authentication types in OBIEE: LDAP authentication, external table authentication, database authentication on page, etc.

I'll explain how to use external table authentication and how to work with row-wise initialization.

There is a little explanation in table format where to use repository, session and presentation variables and how to call them: OBIEE variables overview.

In my situation I have defined some users in Oracle BI server repository and other in external table as well. I know that this isn't good example in reality. But we'll see how OBIEE works with different types of authentication together.

Part1 - External table authentication

Oracle BI Server users and groups:





UserC, UserN -> Users (A-M)
UserSC -> Special Group

External table authentication (external users):

*We need to import this table to physical layer.



This is taken from chapter 15 - Security in Oracle BI:



So we created users separately from Oracle BI Server (groups are already inside BI Server).

Initialization block:



:USER and :PASSWORD represents a username and password that the user entered in start page fields.

Now, when a user begins a session we will populate system session variables (USER, GROUP, DISPLAYNAME and LOGLEVEL) with values defined in our external table:



Now, before going to a Answers we'll do one more thing. Put some filters to users and groups for restricting data for particular set of tables to test users/groups permissions. I use SALES fact table.

UserC see all the data, but group Users (A-M) see only SALES data for Tele Sales channel. Users (N-Z) see only Catalog channel data in SALES. Privileges granted explicitly to a user have precedence over privileges granted through
groups so UserC does not have his group filter.







Now, lets test external table users.

First log on with UserA and create report:



Results:



UserA is added to a group and he see only Tele Sales channel data (permissions inherited from Users (N-Z) group). Same restriction is for UserB.

Logon as UserZ now.

Results:



Only Catalog channel data in SALES (permissions inherited from Users (N-Z) group).

Sign up as UserN:

UserN does not exist in external table and is populated from Oracle BI Server security. So we can retrieve USER, GROUP, DISPLAYNAME (if we set it) variable but not LOGLEVEL:



For some reasons we cannot see LOGLEVEL although we set it on user properties in Adnimistrator:



Remove VALUEOF(NQ_SESSION.LOGLEVEL) from column expression in Answers.

Results:



DISPLAYNAME is not set.
Only Catalog channel data selected from SALES.

Logon as UserC and remove LOGLEVEL from expression.

Results:



There is no inherited permissions for UserC from his group Users (A-M, privileges granted to a user explicitly have precedence in relation to privileges granted to a group).

NQQuery.log:



Part2 - External table authentication and row-wise initialization

In part 2 we use only users that we defined from external table. External table we use only for user authentication and if user pass check then we use another database table for retrieving session variables in row-wise initialization.

What is row-wise initialization?

The row-wise initialization allows us to create and set session variables dynamically. It is similar to external table authentication except here we have fixed number of columns (name, value) and each row for a particular user represents different name (variable name) and associated value (column value).

My example use this database table:



I know that this is not in practice, but I used hybrid solution for demonstration. First I check my user after log in, and for that I use the same table as in part 1 for authentication. This is first initialization block and I'll populate only USER system variable:



And after that we use another block with row-wise initialization. For current user we pick up column SESSION_VARIABLE_NAME from OBIEE_ROW_WISE_INIT. This column represents variable name, system or non-system. So this row-wise will dynamically create session variable and set it with value in SESSION_VARIABLE_VALUE column:



For UserA there are four variables to set dynamically. One of them is non-system, MARRIED.



This block depends on the results of the previous block. This can be set in execution precedence part of block:



We could also use VALUEOF(NQ_SESSION.USER) instead of :USER.

For now we have USER variable set from the first block and other variables set from row-wise dynamically in second block. All this happens in session level, after login.

In the next step we add another block with row-wise but we set dynamically session variable that we hardcoded in select statement in block:



Select:

select 'USERS_IN_GROUP', username
from hr.obiee_row_wise_init
where session_variable_name='GROUP'
and session_variable_value ='VALUEOF(NQ_SESSION.GROUP)'

USERS_IN_GROUP represents a list of values (we can use this as filter in Answers) of all users that have same group as the user that we use for login.

Note that there is also execution precedence.

Test, login as UserA.

USERS_IN_GROUP returns UserA and UserB in the list for Users (A-M) group, according to our OBIEE_ROW_WISE_INIT table.

Create a report in Answers with two expressions:



Expression 1:

case
when CHANNELS.CHANNEL_DESC='Catalog' then 'UserA'
when CHANNELS.CHANNEL_DESC='Direct Sales' then 'UserB'
else 'UserN'
end

Results without filter:



If we add row-wise session variable USERS_IN_GROUP as list of values in the filter to expression 1 column:



Filtered results:



This works correctly.

How to change date format mask in date dashboard prompts - drop-down list and calendar

If you have ever asked yourself how to change the format mask of date dashboard prompt that used calendar control, here is the solution of this problem.

We know that we can use drop-down list and calendar control for the date dashboard prompt, and there is a file localedefinitions.xml in D:\OracleBI\web\config for editing locale settings. Just for my test, I'm using english-base (en) part of this file.

Editing dateShortFormat entry as in picture:



will give us desired date format for drop-down list:





But if we use a calendar:



we see that dateShortFormat entry doesn't have any influence on it:



Let's change now dateSeparator and dateOrder entries in localedefiniitions.xml to see what will happen. I want to see date in my user friendly format dd.mm.yyyy, with or without leading zeros (01.01.2008 or 1.1.2008), so I changed dateOrder to dmy and dateSeparator to zero (.).



Remember, dateShortFormat is still in dd-MM-yyyy form. We will see later how it affects on calendar.

After restarting presentation service, a new format mask is applied on calendar:



Ok, so far so good.

But what if we want to apply some default date value to calendar prompt? What is the format in which default value should be?

Let's go into BI Administrator and create initialization block and repository variable for test.



Variable rv_test_date_to_char is in character format ('dd.mm.yyyy').

We set rv_test_date_to_char as default value to a calendar prompt:



After preview, we can see that the default variable rv_test_date_to_char (character) is converted to a date format before getting into calendar prompt field using dateShortFormat dd-MM-yyyy entry in localedefinitions.xml:



We don't like this, because if you choose value from a calendar, you'll see a difference between these two formats, the default one and the one from a calendar:





Solution is to synchronize all formats.



dateShortFormat -> d.M.yyyy
dateSeparator -> .
dateOrder -> dmy

Don't forget to enter date format d.M.yyyy in dateFormats entry if it doesn't exist there:



After final test our repository variable is converted from character '01.01.1999' to a date 1.1.1999, before getting into calendar prompt field:



Now, if you choose same value from a calendar, you'll get the same format as before, and that's correct:





With this solution you'll have a full control of date format when you are using calendar so u can use default repository variable that is a character and later you can parse value from a date dashboard prompt to a presentation variable and use it wherever you want to (title, filter).

With above settings in localedefinitions.xml, if we now include default variable rv_test_date_to_char in drop-down list we will get:



No conversion of character 01.01.1999 to a dateShortFormat d.M.yyyy for drop-down list prompts?

So, if we like to see the same format in drop down list with the one we have in variable (character) here is a solution:

In edit column formula write to populate character format:

case when 1=2 then cast(TIMES.TIME_ID as char) else EVALUATE('TO_CHAR(%1,%2)' as varchar(20),TIMES.TIME_ID,'dd.mm.yyyy') end

Open SQL Results and add for test purpose:

SELECT case when 1=2 then cast(TIMES.TIME_ID as char) else EVALUATE('TO_CHAR(%1,%2)' as varchar(20),TIMES.TIME_ID,'dd.mm.yyyy') end FROM "Normal model" where TIMES.CALENDAR_MONTH_DESC='1999-01' order by TIMES.TIME_ID

After a preview we see that the data in drop-down list is in character format (like variable) and order by is correct.



We should use drop-down list only with constraint option (with selecting months as parent) because it's confusing to see to many month values in list.