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
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":
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:
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 '':
Now we are ready to do some real Dynamic Columns example, in the next post.
Late Update for solutions 1&2:
From version 11g9 you need to enable this feature by adding a tag to instanceconfig.xml
Solution 3This 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.
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.