This discussion is archived
5 Replies Latest reply: Apr 3, 2013 9:00 PM by gauravnankar RSS

Writeback implementation of filters in OBIEE

rb Newbie
Currently Being Moderated
Hi Folks,

My Client has come up with a requirement in which writeback has to be implemented in OBIEE 11g reports. We have successfully implemented writeback for textboxes in which we can write and the same is geting inserted in reports. Problem is that we want to insert the selected filter value in the database as well. For example if there are few options in a dropdown filtern box and a user selects one of them. The value of this filter should be written in the DB. Can anyone please help me on this ?

Thanks much in appreciation !
  • 1. Re: Writeback implementation of filters in OBIEE
    gauravnankar Explorer
    Currently Being Moderated
    Hi,

    Say if you have the filter dropdown on the Region column, you can include this column in your table view and hide it. And then when you create your Wirteback XML, you can update the Region value in the database as well using the '@<columnnumber>' syntax.

    Hope this helps,
    Gaurav
  • 2. Re: Writeback implementation of filters in OBIEE
    rb Newbie
    Currently Being Moderated
    Hi Gaurav,

    Thanks for your prompt response !

    i will try to specify this a little more..In the report i have 4 filters which are there in prompt section. I have another textbox where i can write stuffs to be inserted into DB from report using writeback. I want to update my backend table with values from both these filters using writeback. Can you please elaborate a little more on to this.

    Cheers,
    Rit
  • 3. Re: Writeback implementation of filters in OBIEE
    gauravnankar Explorer
    Currently Being Moderated
    Hi,

    Let me visualize what you are saying first.

    Prompts
    Filter 1 Filter 2 Filter 3 Filter 4

    Report

    Some Columns Writeback Column
    _____________ <Text Boxes>

    And you want to update the values Filter 1, Filter 2, Filter 3, Filter 4 and Text Box value into your database, right?



    If this is the case, then just add the Filter columns to your report. Your report will look like:


    Report

    Filter Column 1 Filter Column 2 Filter Column 3 Filter Column 4 Some Columns Writeback Column

    And then you can have the following update stmt in your Writeback xml:


    update table set databasecolumn 1 = @1, databasecolumn 2 = @2, databasecolumn 3 = @3,databasecolumn 4 = @4,databasecolumn n = @n where <some filters based on other columns>;


    Hope this clarifies what I am trying to say.

    Thanks,
    Gaurav

    Edited by: gauravnankar on Apr 2, 2013 9:06 PM

    Edited by: gauravnankar on Apr 2, 2013 9:07 PM
  • 4. Re: Writeback implementation of filters in OBIEE
    rb Newbie
    Currently Being Moderated
    Hello Gaurav,

    Thanks Gaurav !!

    We can't join the reference table and fact table. So the filters are coming from a reference table but the below writabck column is coming from Fact table. And here we can not join this fact table and reference table. So we just want to pass the value from filter to the below column, without any join.

    Please let us know if there is any way to cater such scenario.

    Cheers,
    Ritesh
  • 5. Re: Writeback implementation of filters in OBIEE
    gauravnankar Explorer
    Currently Being Moderated
    Hi Ritesh,

    If you can't join the two tables, then you can't apply the filters to your request either. Looking at your id, I assume you are working for BT; I can imagine a dashboard with prompts that don't necessarily apply to the report below ;)

    So in a situation where you want to include columns from a table which does not join to any of the other tables included in a report, you can define the other columns at the Total level of the unjoined table. If the rpd is correctly designed, OBI will generate two separate With clauses for the joined and unjoined tables and subsequently outer join them to fetch the result. This might work, but I can't say for sure as I dont know the complete design.

    You may want to revisit the dashboard design also, as I still think having a dashboard with prompts not applying to the reports below is a bit vague.

    Thanks,
    Gaurav

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points