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.