Select table based on user response?

building semantic layer
1

Forgive me if this has been asked and answered. I did a search but didn’t find it.

I have two different reports that pull the same data from two different tables. One table and report is for current year data and the other table and report is for prior year data.

I’d like to combine them into a single report with a user prompt for either current or prior year data but I can’t seem to find a way to change the table selected based on the user response.

Is there a way to do that either in the universe or the report? It seems a simple request but is proving difficult to execute. Many thanks for any help or insight you can provide!

<edit: these are WEBi reports>

XiR3 SP5

Tito (BOB member since 2012-04-04)

2

Hi Tito,

Create a dummy prompt at the Universe…

Dummy Prompt: @Prompt(‘Select Year:’,‘A’,{‘Current Year’,‘Prior Year’},mono,constrained)

This will not get parsed in the Universe.
Pull this object in the your Objects panel of your BO Query…

Create the two tables on the reports.

Select one table (block with current year data) and apply the block level filter by selecting this dummy prompt object and making it to equal = Current Year.

and now do the same to the Prior year block.

Hope this helps !

izak004 :us: (BOB member since 2010-11-10)

3

Hi izak004-

Thanks for the reply. I considered filtering the data but both tables contain 3m+ rows of data so I’m trying to find a way to execute the query without bringing back both sets.

Anthing else that might help? It seems there should be a way to do this but I haven’t figure out how yet!

Tito (BOB member since 2012-04-04)

4

hello

nice question

Why can’t to just link the universes and just create a derived table (withh your required tables) . so depending’ly you can filter at webi with universe level or webi level filter conditions .

It may reduce performance .

If any other precise solution please do post :slight_smile:

Thank you

silverware (BOB member since 2011-08-25)

5

Use a combined query UNION… this way only one query will execute.

BO_Chief :us: (BOB member since 2004-06-06)

6

Yep, that did it - Thank you so much! :wave:

Tito (BOB member since 2012-04-04)

7

Yes yes absolutely perfect … mine way is too long and too bad … my brain always stops working at right time :mrsbob:

silverware (BOB member since 2011-08-25)

8

You are Welcome.!! :wave:

BO_Chief :us: (BOB member since 2004-06-06)