Strange Prompt Problem

building semantic layer
1

I have a strange requirement, which I think is not possible:

Customer wants to have a Prompt to choose between Regions and States. Now, when Customer chooses Regions, the next prompt should populate all regions, if he chooses States, the following prompt should populate all States.

He will choose either Regions or States.

But, since these are two different columns (for Region and State), I cant make both the conditions works together.

Any suggestions?

P.S.: There is an option for optional prompts, I am just looking for the above requirement.

Smith85 (BOB member since 2009-12-10)

2

is this what you are looking for?

REGION IN (CASE When @Prompt(‘Enter Region/States’,‘A’,{‘States’, ‘Region’},MONO,FREE) = ‘Region’ Then TABLE.REGION Else ‘1111111’ END)
OR
STATES IN (CASE When @Prompt(‘Enter Region/States’,‘A’,{‘States’, ‘Region’},MONO,FREE) = ‘States’ Then TABLE.STATES Else ‘1111111’ END)

define this as a condition object.

jprasanthram :switzerland: (BOB member since 2010-12-10)

3

Thanks for your response, but I did not understand your solution.

The ‘Then’ part should return something, what would I use there?

Suppose a User picks up Region from the first prompt, in the next prompt (which I believe would be cascaded one) should show him all the regions. In this case, the region column would be equated with the regions he chooses to result in the report. Same goes for States.

Smith85 (BOB member since 2009-12-10)

4

if I follow the logic, if you don’t select region, that half of the OR statement resolves to ‘region in 11111’ - false. It basically turns that half of the or into no results.

It’s pretty slick, wish I had thought of it first : )

B

bdouglas :switzerland: (BOB member since 2002-08-29)

5

This solution won’t give me a second Prompt. If I choose Region, all Regions will get equated, if I choose States, all States will get equated.

I actually want the User to get the other prompt to choose Regions (or States) - depending upon his first prompt choice. :slight_smile:

I believe this is just not possible (unless I use VBA). Am i thinking correct? :oops:

Smith85 (BOB member since 2009-12-10)

6

You can replace the TABLE.REGION and TABLE.STATES with different @Prompts separately; but in WebI reports you’ll see all the 3 prompts in the prompt dialog.

Not sure of DeskI though, give it a try and post your progress.

jprasanthram :switzerland: (BOB member since 2010-12-10)

7

@Smith

I can tell you one solution, although it may not be the desired one.

You can create a Derived Table based upon the following query:

select
'Region' as Name,
<Region Column> as Value
from <Table>
UNION
select 
'States' as Name,
<State Column> as Value
from <table>

Now, you would have data like :

Name | Value

Region | R1
Region | R2
State | S1
State | S2

This table can be joined with a dimension table in your Universe, possibly with a Join as :

Derived Table.Value = Dimension Table.Region_Column 
or 
Derived Table.Value = Dimension Table.State_Column

You may need to modify the join condition based upon your requirements.

Next, you will have to create a Cascading Prompt (between Name and Value from Derived Table) and a Condition Object (with the Prompt definition). And you are done !!

On your report, you can select the Objects, along with the Condition Object, it will prompt you for Regions/States, and on selecting one of them, you would be prompted for the (corresponding) values.

This will work. :slight_smile:

Rajat Sapru :us: (BOB member since 2008-08-28)

8

I’m working on a blog post that shows how to do this now. I actually started it months ago based on a question from SCN but have not finished it. The prior post (just prior to mine) has a solution that is similar to what I am working on. It certainly has the same concepts.

Dave Rathbun :us: (BOB member since 2002-06-06)

9

Thanks for the solution Rajat. I am trying that as of now.

The join condition you mentioned will work in combination? Never used such ones… :oops:

Also, I can trim off certain part from Region/State? They contain code as well…I am using teradata…

Smith85 (BOB member since 2009-12-10)

10

That’s great Dave :slight_smile: It is indeed one of the (multiple) sought after problems.

Certainly :slight_smile: It will create a Complex Join in Universe.

Yes, you can do this. You can use Substring() (If I am not wrong, you can even use Substr() on Teradata). You will have to make changes in Derived Table, as well as in the Join condition. Something like:

Derived Table.Value = Substr (Dimension Table.Region_Column, X, Y)

Rajat Sapru :us: (BOB member since 2008-08-28)

11

Thanks Rajat, the solution worked :slight_smile:

I thought to update this thread, in case anyone has the same issue.

Smith85 (BOB member since 2009-12-10)