Wednesday, April 29, 2015

OBIEE - Selecting columns dynamically - part5, the single prompt solution

If you read posts 2,3,4 of this "OBIEE - Selecting columns dynamically" saga, you can create a set of prompts where each prompt controls a column and enables the user to select the column formula or decide not to show it at all. 
Something like this:

But the users might ask you to have a single prompt with friendly column names, in which they can select all the relevant columns for the analysis (or 2 prompts, one for attributes and one for measures).
Something like one of those 2:



This is what this post talks about.

What I intend to do?
  1. Create the above prompt to allow the user to select desired columns.
  2. Create a hidden set of prompts where I split the above prompt to column names and have additional hidden prompts for their actual formula.
  3. Create an analysis where the column values are either the selected column or the None / Hidden  column from solution 2 of post 4 of this series.  
As a result while user doesn't select any columns, the analysis is mostly empty and as columns are selected it grows.  

For example in my analysis there is only one default measure column, so all I see is:
But if I select few values in the prompt:
I get the following result of the same analysis:


How to achieve it?


To get such a list I will create a table of prompt values, in this case, column names. Since I was lazy, I created it by a set of "select from dual", instead of actual table (don't do it in real life):

With 4 columns:
  • Column Name - A user friendly representation of the column (example 'Year')
  • Column Code - The formal Column definition in Analysis formula (example ( "Time"."T05 Per Name Year")
  • Column Type - In case I decide to split the prompt between attributes and measures and deal with special formats such as date.
  • Column Sort  - This will allow me to control the selected values in prompt. (better explanation later in this post).
Next I created a specific Business Mode just for the Prompt table and use one of the options for single table Model described here.  In my case, I duplicated the table at the Business layer and defined the copy as Dimension (after adding a key). Later I used only the "Fact" part in the Subject Area (See presentation layer on the left, Business Model on the right):


With that, I can create a prompt based on the column col_name. It can be a check boxes:


Or  a choice list:
  
Now I have a Presentation Variable (P_col_list) with all the column "friendly" names I want.
It's value might be something like this:'Company, Customer Name, Department'. 
What I need is to find columns that are in that string and control how do I get them in some controlled method.

The naive approach is treating the P_col_list Variable as one long string and cut it to peaces. I don't recommend it. The functions are long and unfriendly. It's much easier to filter the column table by this presentation Variable and then select a specific row, using the Rank function (with different value of Rank for each final Column).
So I need to check for each potential column, it's in the P_Col_list and select it's rank to get them in an orderly manner.

To check if the column is in the P_col_list I will use the function Locate: LOCATE("Prompt List"."Col_name",'@{P_col_list}') .To get the Ranking I will use RANK("Prompt List"."col_sort") . (The "col_sort' column from above allows me to control the columns. I can set numeric values there and use them. You might notice my values of col_sort column goes from higher to lower. Since the Ranking goes, by default, from larger to smaller values, that is the way to control the order of columns.)

(There is another / better option instead of the Locate above. See P.S 2 why I didn't use it)  

For example, the first selected column formal column name is:


SELECT "Prompt List"."Col_code" saw_0
FROM "ZZ_Prompt_list"
WHERE (LOCATE("Prompt List"."Col_name",'@{P_col_list}') > 0)
 AND RANK("Prompt List"."col_sort") = 1


and the first "friendly" name is:

SELECT "Prompt List"."Col_name" saw_0
FROM "ZZ_Prompt_list"
WHERE (LOCATE("Prompt List"."Col_name",'@{P_col_list}') > 0)
AND RANK("Prompt List"."Col_sort") = 1



In a similar way the second pair would be: 

SELECT "Prompt List"."Col_code" saw_0
FROM "ZZ_Prompt_list"
WHERE (LOCATE("Prompt List"."Col_name",'@{P_col_list}') > 0)
 AND RANK("Prompt List"."col_sort") = 2


SELECT "Prompt List"."Col_name" saw_0
FROM "ZZ_Prompt_list"
WHERE (LOCATE("Prompt List"."Col_name",'@{P_col_list}') > 0)
AND RANK("Prompt List"."Col_sort") = 2

 


So I decided that my case would have up to 5 dynamic columns (you can do as many as you like) and my second (soon to be hidden) prompt is, named one_list_actual:


Where P_C1A is the friendly Prompt name and P_C1 is the formal formula.

P_C1:

P_CA1: 



The analysis would be:
1. A set of 5 columns with formula like:IFNULL(@{P_C1}, "None"."None") and Column Header like: @{P_C1A}:


2. The original '@{P_col_list}' prompt, defined as hidden in the column properties. (I need it so the analysis "knows" that prompt changed values and refresh).
3. As many columns I want that would be constant in the analysis. It should be at least one measure from a fact for the system to always work properly. It's "Base Facts"."2- Billed Quantity" in my case.

This is how the criteria looks:

 

The last step i did was to put the 2 Dashboard Prompts and the analysis in a single dashboard and see it all works fine and then hide the second prompt (one_list_actual) with all the P_C... variables.

 



After selecting few values:


Last part is to hide the second prompt.I already explained this step in the post: OBIEE - Creating dependent - implicitly declared presentation variable: In Dashboard Properties, under Filters and Variables I'll add the Dashboard Prompt one_list_actual.



and delete it from the dashboard.
this is how it looks with no values selected.




thi sis after selecting several values in the prompt:





You might want to split the Prompt that select column to 2 prompts, one for measures and one for attributes. The "Null" column for measures should probably be default as 0.0, to force it to be float.


   

P.S. 1

In case I wanted the Prompt solution to be reusable in many dashboards, I don't need many tables of column lists. I could add a column / columns with filtering values and filter the prompt by it, each time.

P.S. 2


LOCATE("Prompt List"."Col_name",'@{P_col_list}') will return numbers greater than zero if the column name is in the Presentation Variable. So we can use the condition:  (LOCATE("Prompt List"."Col_name",'@{P_col_list}') > 0). It is problematic in cases where one value is contained in another, for example 'Product' and 'Product Type'. To prevent this problem you might add a comma string before and after the P_col_list an search for values with comma before and after.
(LOCATE(','||"Prompt List"."Col_name"||',',','||'@{P_col_list}'||',') > 0

 So the select statement for P_C1 should be actually:
SELECT "Prompt List"."Col_code" saw_0
FROM "ZZ_Prompt_list"
WHERE locate(','||"Prompt List"."Col_name"||',', ','||'@{P_col_list}'||',')>0
AND (RANK("Prompt List"."Col_sort") = 1)


Why did I use it and not a better option of looking for specific value in multivalued Presentation variable,cover  here by Deepak:
If I want to check whether the column "Prompt List"."Col_name" is part of the values in presentation Variable P_col_list, I can run the following: WHERE "Prompt List"."Col_name" IN (@{P_col_list}['@']) .
Well I had some voodoo problem with. When I didn't select any value in the initial P_col_list variable I had an error:
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27002] Near <,>: Syntax error [nQSError: 26012] . (HY000)
SQL Issued: {call NQSGetQueryColumnInfo('SELECT IFNULL(, "None"."None") FROM "A - Sample Sales"')}
SQL Issued: SELECT IFNULL(, "None"."None") FROM "A - Sample Sales"
It happens only if no columns at all where selected. One default was enough to prevent this error.   

P.S. 3

I would like to thank Rakefet Barad from Oracle ACS and Dov Ziskin from Vision BI for the chats we had on the issue.

28 comments:

  1. Hello,

    I am finding this blog very useful. I am very new to OBIEE. I have issue with the dynamic sql:
    When I select multiple columns to display from the list, it populates in hidden list as well but sql is not able to send multiple columns. Please help.
    Thanks

    ReplyDelete
  2. Really useful trick on displaying multiple columns in the analysis dynamically.
    But I see a issue with this approach as the result of the analysis would not get refreshed automatically upon selection of values after second time.
    The first prompt values would be automatically passed to the second prompt but the values of presentation variables in second prompt would not be applied instantly, the use would have to refresh the report manually the see the changes

    ReplyDelete
    Replies
    1. That is why you need the first prompt in the analysis, as hidden column. This way there is no need for manual refresh.

      Delete
  3. Hats off to you man .. Thank you so much for this blob

    ReplyDelete
  4. Can I create a union report using this logic. I tried creating a report with two union which are based on the same fact and only difference would be the filter applied to each individual. This is for a given customer and for entire enterprise.

    When I execute this report in dashboard it throws this error:- State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 17001] Oracle Error code: 604, message: ORA-00604: error occurred at recursive SQL level 1 ORA-01727: numeric precision specifier is out of range (1 to 38) at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)

    Can you please suggest some method to do this

    ReplyDelete
  5. Can we pass presentation variable like these to union reports ?.
    Can any of you confirm this please

    ReplyDelete
  6. Hi,

    I tried hiding the column dynamically. But i have the following problem.. Please help..

    When i select the columns through prompts, aggregation is applied to the measures based on the selected column.. But the selected column appears hidden.

    ReplyDelete
    Replies
    1. Sathish, it's the expected behavior. You can change the measure to aggregate by formula to control it.

      Delete
    2. For example sum(Revenue by Department, Year)

      Delete
    3. Hi Boris,

      Thank you.. I am new to OBIEE. Please bare with my basic questions
      I think my last comment is not clear. Columns in my Analysis are

      Business Unit --> Formula : CASE WHEN CAST(@{P_Col1} AS CHAR)='0' THEN "None"."None" ELSE CAST(@{P_Col1} AS CHAR) END
      Plant --> Formula : CASE WHEN CAST(@{P_Col2} AS CHAR)='0' THEN "None"."None" ELSE CAST(@{P_Col2} AS CHAR) END
      Quantity ---> formula : SUM(Quantity)


      Note :Instead of NULL I checked for '0' because if i dont select the column in prompt its returing numeric 0. "None"."None" is hidden in RPD layer

      When i select Business Unit, i am getting aggreated quanity as,

      Quantity: 10,20,40

      But the Business unit column is not getting diplayed in the report. If I select Plant, I am getting the Plant level aggregation
      but the plant attribute column is not getting displayed. So my issue is not with aggregation, attribute columns are invisble

      What am I missing here ? Kindly help. Thanks in advance

      Delete
    4. sathish, I'm not sure what you have, but why don't you let the Quantity formula be just quantity and leave the aggregation rule on it as SUM, unless you haven't define an aggregation rule on Quantity in the RPD. In That case, please do.

      Delete
    5. Hi Boris,

      Finally it worked. The issue was due to some OBIEE version or configuration setting. Initially i tried in LAB machine and it didnt work. Now it worked in our Development OBIEE server.

      Thanks alot for this awesome tutorial.

      Delete
    6. Hi Sathish,

      Could you please let me know the version or configuration setting you have done. I am also facing same issue. Please help.

      Delete
    7. Hi Boris,

      Please help, business is asking for this functionality but I am facing similar issue sathish faced. If you could help me it will be great help for me.

      Thanks a lot in advance.

      Delete
  7. I am also doing this but instead of dimensions I am playing with measure ,but some of the columns are not getting displayed . please let me know is there any work around .

    ReplyDelete
  8. Hi Satish,
    Can you please tell me what configuration you have changed ?

    ReplyDelete
  9. Hi Boris,

    I am facing error while using IFNULL(@{P_C1},"None"."None") in the edit formula section.
    Error: Nonexistent Column "None"."None"

    Could you please provide a way to handle this?


    Thanks,
    Maya

    ReplyDelete
    Replies
    1. Maya, the creation of none.none is described in the previous post (part 4).

      Delete
  10. Thanks Boris.
    The problem now I am facing is the columns are not getting hidden. Even after using the none column by applying the hidden method in rpd level, the query results something like this:
    case when convert( CHARACTER ( 30 ), 0 ) = '0' then '' else convert( CHARACTER ( 30 ), 0 ) end as c4,
    where it is clearly evident that the column values are converted to null, but the column as a whole is not disappearing from the analysis.

    Thanks ,
    Maya

    ReplyDelete
    Replies
    1. hi Maya, did you figure out a solution to this? Im having the exact same issue

      Delete
  11. Thanks Boris.
    As you have mentioned in your previous blog i created none.none but it is not hidding. I am using below :
    case when 1=1 then "none.none" else "column A" end
    and it is giving 0.0 in the output.Please suggest?

    ReplyDelete
    Replies
    1. Please read the post again. The 1=1 condition should be in the Administration, as condition to the hide definition of the column "none" (or whatever name you give it).

      Delete
  12. Hi
    i am getting below error while trying this
    Error
    View Display Error
    The number of columns returned in retrieving column metadata was invalid (expected: 1; received: 2)
    Error Details
    Error Codes: SSJ6N6WZ:OI2DL65P
    Location: saw.views.evc.activate, saw.httpserver.processrequest, saw.rpc.server.responder, saw.rpc.server, saw.rpc.server.handleConnection, saw.rpc.server.dispatch, saw.threadpool.socketrpcserver, saw.threads

    ReplyDelete
  13. Hi Boris,

    Great blog, I have implemented all this but I have one problem, I have created 5 Column List and it is working perfectly fine with 2 attributes but with other 3 attributes it is not working. When I select those three attributes it generates the query properly behind the scene but not displaying the column.

    If you could help it would be greatly appreciated.

    ReplyDelete
  14. Hi All,

    Can anyone help me. I think I am doing something wrong in writing the formula for Hidden Original Prompt.

    What should we write in Hidden Original Prompt. Is it @{P_Col_list} or something else.

    ReplyDelete
    Replies
    1. Vivek, if it works for 2, it should work for 5. Please make sure you are not reusing same Variable names and that all P_col_list_X variables you have are in the analysis.
      I didn't understand your last question.
      P.S.
      As rule of thumb, I rarely "support" my blog readers. Sorry too much to do.

      Delete
    2. Thanks for your response. I checked everything twice and thrice, recreated it 2-3 times but everything is correct.
      Measure column is aggregating as per selection list but it is not displaying specific columns and working fine for others. Same problem Sathish is facing.

      If you could help with OBIEE version or configuration setting it require then might be we can solve this issue.

      And I am very thankful for your post. It helped me a lot and appreciate all the help you are providing.

      Thanks.

      Delete
  15. My presentation variable does not return null it returns a 0. So i out the following case statement CASE WHEN CAST(@{P_C1} AS CHAR)='0' THEN "None"."None" ELSE CAST(@{P_C1}AS CHAR) END

    But the column just does not hide. PLEASE HELP!!!

    ReplyDelete