OBIEE - Selecting columns dynamically - part4, working with and selecting none

In this part I will talk about how to remove column from analysis with column selector or prompt. That means, selecting a column I don't want to present (or actually influence the analysis at all). Why do I want such a stupid thing? When working with dynamic columns, I want the ability to control the number of columns. So if the user wants only 3 columns, out of possible 5, only 3 columns would appear in the analysis. The other 2 columns will not show.

How do I add such "None" column?
I will describe 2 options. First is created at the User Interface level, the second in RPD only and works better with session variables. Usually the second should be your default option and it is the one to be used in the next post.

Solution 1 (see update bellow)

I add a column in the repository with empty value and default this column to be hidden, system wide. Now I can use the column anywhere I want. Lets have a detailed explanation.

I'm working with the extended sample available as part of the VM here. So I'm working with real Oracle DB tables in this case. (The following doesn't work well with the XML files of the default Sample).
In the Business layer I randomly selected the Time dimension and added a column with '' value. I named it None.

As a next step I created a new Presentation Table under the Sample Sales and named the table None, then dragged the none column under it.
Now I have the None column. OBIEE enables me to create analysis with this column:

What is nice here, is the fact that the SQL doesn't join unnecessary tables:
This is the left one (only the fact table in the original From):

WITH 
SAWITH0 AS (select sum(T42433.Revenue) as c1,
     '' as c2
from 
     BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */ ),
SAWITH1 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3
from 
     (select 0 as c1,
               D1.c2 as c2,
               D1.c1 as c3,
               ROW_NUMBER() OVER (PARTITION BY D1.c2 ORDER BY D1.c2 ASC) as c4
          from 
               SAWITH0 D1
     ) D1
where  ( D1.c4 = 1 ) )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3
from 
     SAWITH1 D1
order by c1, c2 ) D1 

In a similar way, the right one is correct as well, with no unnecessary joins to date dimension.
 SAWITH0 AS (select sum(T42433.Units) as c1,
     T42412.Department as c2,
     '' as c3,
     T42412.Dept_Key as c4
from
     BISAMPLE.SAMP_OFFICES_D T42412 /* D30 Offices */ ,
     BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */
where  ( T42412.Office_Key = T42433.Office_Key )
group by T42412.Department, T42412.Dept_Key)...

Next step, as administrator I will create an Analysis with the None column and select it's Column Properties.

In Column Format I'll select "Hide".

And at the bottom of that screen I will select Save As default / Save as system-wide default for "None"."None": 

the same with Zoom-In:

That's all. Now I can use the None column in any other analysis as Column Selector, described in the first post, or as Presentation variable, described in second and third.

For example the same analysis with Column selector, selecting once the None column and then some other column:

When None is in the column of Column selector:

When Per Name Year is in the column of Column selector:

Solution 2 (see update bellow)

Just as in solution 1, I add a column in the repository with empty value and default this column to be hidden, system wide. Now I can use the column anywhere I want. I'll name it None1. The process of creating this table is the same as in Solution 1, with the formula '':

 

With one critical difference, the hiding will be done at the repository (presentation level) and not UI, as solutions 1.

At the presentation level I will go to the None properties and set the "Hide object if "condition to 1=1. This way the column is always hidden.

Now when going to Analysis we don't see the column None1:

But it doesn't mean it's not there; You just have to know it's name.

For example an analysis with Revenue and the new None1 column:

 Results:

(Thank You Dov Ziskin, from VisionBI for reminding me the second solution). 

Now we are ready to do some real Dynamic Columns example, in the next post.

Late Update for solutions 1&2:

From OBI 11.1.1.9 this feature does not work by default (Thank You Yaniv Kisler for pointing me to it). 

See OBIEE 11g/12c-Hidden Table Using 'Hide object if' in RPD is Displayed in Analysis (Doc ID 2464635.1) at oracle Support site.

From version 11g9 you need to enable this feature by adding a tag to instanceconfig.xml

<WebConfig>

<ServerInstance>

<SubjectAreaMetadata>

<HideHiddenMetadataInViews>true</HideHiddenMetadataInViews>

</SubjectAreaMetadata>

</ServerInstance>

</WebConfig>

Solution 3

 This solution is similar to solution 2, but you can hide the None 1 column by preventing access to it in the permissions.

Just remember to change the parameter PROJECT_INACCESSIBLE_COLUMN_AS_NULL  in nqsconfig.ini to Yes, or you will get an Error.

P.S.

Never test the above with administration user like weblogic. Unless you want to spend hours testing it and questioning the idiot who wrote the above.
User weblogic can see everything, including the hidden columns.

OBIEE - Selecting columns dynamically - part3, better looking prompts

Sorry for the long delay, I was busy performing services in exchange for currency (mostly interesting services).

In the previous post I demonstrated how to use Prompts with column names to select column dynamically. In this post I will do the same but with User Friendly values in Prompt.

The result from the previous post was the ability to select first column using prompt, including it's header and selecting the second measure, based on the prompt (if the first column is Year, we wanted to see Discount Amount, else Billed Quantity):

There are 3 UI problems in that solution:

1. Unfriendly column names.
2. The selected column header.
3. The second measure header.

The faithful readers of this blog might be guessing I will use here the method described in the post OBIEE Prompts - 2 prompts on same column - Part 3, of hidden prompts. 

 The solution:

I will create a variable prompt with user friendly values (Year, Product Type, Department). Its value will be placed in a presentation variable that will be in the header of the first column and used to update 3 other presentation variables:

1. The real column function.

2. The measure name. 

3. The second measure function.

If you insist you can do 1 and 3 at the analysis level.

Step1, Create the user friendly prompt:

I created a Variable Prompt, lets name it P_col1_header and default it to be Year. I prefer the Radio Buttons UI.

This is the user friendly Prompt:

Unfortunately, there is no hidden line option in the same prompt (Yes, I know, I should fill an enhancement request). So we need another Variable Prompt. This one will be hidden.

Step 2, The Hidden Prompt:

I created another Dashboard Prompt with 3 rows of Variable Prompts. Since I plan to hide it, I don't care how it looks.It will update 3 Presentation Variables:

• P_Col1
• P_Col2
• P_col2_header

They all look the same: Presentation Variable, Text Type with Default Selection "SQL Result". The only difference is the SQL.

P_Col1 (returns the dimension column):

SELECT CASE '@{P_col1_header}{Year}'
WHEN 'Year' THEN '"Time"."Per Name Year"'
WHEN 'Product Type' THEN '"Products"."Product Type"'
ELSE '"Offices"."Department"' END saw_0 

FROM "Sample Sales Lite"

P_Col2: (returns the measure column) :

SELECT CASE WHEN '@{P_col1_header}{Year}'='Year' 

THEN '"Base Facts"."Discount Amount"' 

ELSE '"Base Facts"."Billed Quantity"'  END saw_0 

FROM "Sample Sales Lite"

P_col2_header (the measure header):

SELECT CASE WHEN '@{P_col1_header}{Year}'='Year' 

THEN 'Discount Amount' 

ELSE 'Billed Quantity'  END saw_0 

FROM "Sample Sales Lite"

All is left is to hide the second dashboard Prompt and modify the Analysis.

To hide the Prompt, Edit the dashboard and select Dashboard Properties in the little menu:

Next place the cursor on the relevant dashboard page and press on the filters and prompts icon.

Now add the hidden Prompt there:

Last step is to modify the analysis. I used defaults for the presentation variables, just to make the development easier.

So the first column now has:

Header: @{P_col1_header}{Year}

Formula: @{P_Col1}{"Time"."Per Name Year"}

 

The last column:

Header: @{P_col2_header}{Discount Amount}

Formula: @{P_Col2}{"Base Facts"."Discount Amount"}

and the other 2 are regular: "Orders"."Order Type" and "Base Facts"."Revenue".

Now I have a dashboard:

I select Product Type, press Apply and... NOTHING. Nothing changed! Why? Because, as I mentioned in that post (OBIEE Prompts - 2 prompts on same column - Part 3), the analysis doesn't "know" that the prompt changed. The fact that the variable is in the header of the first column is not enough. We have to add a column with formula '@{P_col1_header}' and set it as hidden in the Column Properties.  

Now everything works fine:

I'm happy,  but the customer has new ideas: "you know, sometimes I don't want the first column at all". Can you add this option as well?

This will be cover in the next post: OBIEE - Selecting columns dynamically - part4, working with none.