Case Statement (Bins) filter.


If we have a requirement to have case statement on column and it has some filtering condition on it. Filtering cannot be achievable by make use of presentation case statements or Bins, but using repository it’s possible.
Create a new logical column with required case statement in repository pull it on to Presentation, this will works fine with all filters.
Just to demonstrate I have made a dash board page with page prompt and report. Page prompt consist rpd case statement column and also presentation case column. Report consists of same above columns and one measure in a table.
Now if you try selecting any value in presentation case prompt, report will not effected with selection where as rpd -coulmn will be works fine.
Under line concept is using rpd column formula will be placed in physical SQL’s Where clause as well as Select, contrast Bins(presentation case) – column will put case statement in only SQL’s Selection clause.

Hope helpful to you

Posted by

9 comments:

  1. SriniNovember 4, 2009 at 2:16 PM

    Srinivas, thanks. That is informative.
    Can you elaborate how you defined the YearflaginRPD column?

    You said "Create a new logical column with required case statement.."...so what case statement did you use in column definition?

    Thanks again

    ReplyDelete
  2. Srinivas MalyalaNovember 4, 2009 at 2:36 PM

    @Srini

    Using expression builder you can use cases statement logical column expression would look some thing like this

    CASE WHEN YEAR("s_nq_acct”.”start_date”) = 2009 THEN 'Current_Year' ELSE 'Previous_Year' END

    Let me know if you need more information.


    Thanks
    Srinivas

    ReplyDelete
  3. SriniNovember 4, 2009 at 3:07 PM

    Sounds good. I have a requirement that is a bit different. I want to use a case statement in the where clause and the flows in the case statement are based on the value of a presentation variable(set from a dashboard prompt); so I cannot directly use the same approach as yours because the pres variable value is not available to the RPD.

    Maybe I can set the value of the prompt to a request variable which is probably accessible from the rpd.

    Please let me know if you have any thoughts about the same.

    ReplyDelete
  4. Srinivas MalyalaNovember 4, 2009 at 4:19 PM

    Sounds cool.

    ReplyDelete
  5. UnknownDecember 15, 2010 at 1:34 PM

    Can results of bins (case statements) be passed to other Answers request in the form of value interaction (navigate)?

    ReplyDelete
  6. Srinivas MalyalaDecember 30, 2010 at 9:50 AM

    @Lewis.

    Yes we can pass. We need to put same formula in target report with isprompted.

    ReplyDelete
  7. AjitMarch 23, 2011 at 12:33 AM

    Hi Srinivas,
    As explained I have created logical column on Fact table. Acctually in my scenario : I have to get the sum and count from fact table based on condition of Dimension table ie

    Two logical columns are created in RPD: one for sum(amount)where product type=A and Count(records) where product type=B. There is a hierachy created on Product type Dimension (product type--> product skey are the levels)

    Problem: In report, unless and until I choose the Product type, the result is not correct. But i don't need product type in result as adding this make results to two rows (product A for sum & B for count). I want both Sum(Amount) and count(records) to be displayed in one row in answers.

    Please suggest how to achive this.

    PS: If I do this using 'Filter' in Answer level which works fine, but I want to push this logic to RPD level.

    Thanks
    Ajit Hegde

    ReplyDelete
  8. PrasannaDecember 22, 2011 at 6:45 AM

    hi,
    i am prasanna.your post is informative.but i have question about case statement in dashboard prompts.i have a requirement like i need to need 3 strings in drop down list by using dashboard prompt.please guide me how to do that.


    thanks inadvance
    prasanna

    ReplyDelete
  9. Srinivas MalyalaDecember 22, 2011 at 8:12 AM

    @Prasanna ,
    I'm sorry i did not understand your requirement, can you explain me in detailed.

    ReplyDelete

Please give me your feed back

Subscribe to: Post Comments (Atom)