Input Control Creation/Implementation in Webi

[Former Member]

Hi All,

Hope you all having great time! We have a crazy requirement. Actually let me give you a little bit background first.

As shown in below image we have tables CA_STAT_TAB, MO_STAT_TAB etc... for all US 50 states. i.e., 50 tables we have.

So now the requirement is we need to show all the 50 tables in the webi report in alphabetical order and one below the other like shown below and then we will have an input control called region (as shown below) which will be used to see the states belonging to specific region. by default it is all regions with all states shown and when user selects East coast (lets say) the other will hide and Eastern states will be left.

This pretty much looks like a normal input control, but the problem is each state is coming from a individual table and we don't have any pre-defined object for regions which we can use. So guys, please help me implement this functionality.

Appreciate your responses . FYI. We are on BO 4.1 and Teradata backend

[former_member207052]

There are many ways to achieve this

1. Have a proper ETL that will transform the data into something more usable(the best option)

2. Use your existing web-I to and export the data to excel format and then build a web-I report based on the excel file to achieve what you want. you will need to schedule the base web-I to contantly update the data.

3. You can use the combined queries to achieve similar results as well (illustrated here)

I created a mock setup to replicate your scenario. Attached are the screenshots(first 3 - next 3 in following post). Let me know if you need any other information.

Break a leg!

Naras

[former_member207052]

Just read someone had already posted about combined queries. Take only the dimensions/measures that are in common and then you want to show in this report.

If you din't like that - try to prototype my Option two in the above post. My bad not reading the previous posts well

Screenshots

[Former Member]

how did you bring 50 table data in single query ?

are there any join between these tables?

[Former Member]

Hi Swapnil,

Yes these tables are connected to a single dimension table which doesn't have state information. They are connected through a unique ID.

[Former Member]

Since dimension is connected to all table having state info on "Unique ID" basis you can identify the state from unique Ids

So follow the steps:

1) create object on universe

states =

case when unique_Ids inlist(Put all the states which are part of East Coast) then "East Coast"

when unique_Ids inlist(Put all the states which are part of central Zone) then "Central Zone"

when unique_Ids inlist(Put all the states which are part of west zone) then "west zone"

when unique_Ids inlist(Put all the states which are part of mountain zone) then "Mountain Zone" end

2) Drag the object on report and then

3) create input control on states object.

Thanks,

Swapnil

[Former Member]

Hi Swapnil,

The problem is the unique ID doesn't really classify the states. The unique ID is more like a customer ID which is not in relation with state information.

[Former Member]

I just want to confirm one thing and that is have made 50 distinct objects for each tables??

Can you please show me your query panel??

[Former Member]

Hi Swapnil,

Unfortunately, I can't show you the query panel. It is highly confidential but below is a sample model I created to help you understand. As shown in figure there are 50 tables (for 50 states) connected to DIM_TABLE and DIM_TABLE doesn't have a state field in order to create a state join. Each table connected to the DIM_TABLE has a state filter placed on it at database level.

Hope this helps

[Former Member]

Got it.

Here on universe I guess you have created total 50 objects for State Name for each table(As total tables here are 50)

Now on report you have to merge all the 50 State object and then create one variable

Region =

case when state(Merged Object) inlist(Put all the states which are part of East Coast like 'California') then "East Coast"

when state(Merged Object) inlist(Put all the states which are part of central Zone) then "Central Zone"

when state(Merged Object) inlist(Put all the states which are part of west zone) then "west zone"

when state(Merged Object) inlist(Put all the states which are part of mountain zone) then "Mountain Zone" end

Now create Input-control on Region object.

There you go.. Your requirement is done.

[Former Member]

Ok. I think now we are on same page.

I have two issues here...

1. First of all, can we write a "case" statement in webi? I guess we can't. It should be written at universe level. The webi doesn't even recognize the word "case". I tried it.

2. I don't know why but I could not merge state objects. When I select two or more state objects and right click, it won't show the merge option highlighted.

[Former Member]

oh sorry for that In webi you can go for if statement.

Like

if state(Merged Object) inlist(Put all the states which are part of East Coast like 'California') then "East Coast"

elsif  state(Merged Object) inlist(Put all the states which are part of central Zone) then "Central Zone"

elsif state(Merged Object) inlist(Put all the states which are part of west zone) then "west zone"

elsif state(Merged Object) inlist(Put all the states which are part of mountain zone) then "Mountain Zone" else 'NA'

-------

for merging please make sure that al the state objects from each tables should have same data type.

[Former Member]

All the state objects have same data type called string. By the way, they all coming in a single query. Is this why I can't merge them?

[Former Member]

Yes that's the reason why you are not able to merge it.

I think you should create combined query for each state and Union all the datasets.

No after gettting result on WEBi you can apply logic as mentioned above on State object.

Thanks,

Swapnil

[Former Member]

Sorry Swapnil,

We can't do combined query for this because 50 queries is definitely not a best solution. Moreover, not every state table has same number of fields. Some tables have more fields and some have less. Such as Tax_Free_State (Y/N) etc... Its just a difficult task to maintain 50 queries and we have so much stuff in the report to show, this is just a part of it.

Let me know if there's an alternate solution.

Thanks a lot for your efforts man. I really appreciate your help.

[Former Member]

you can try one solution

1) create one derived table called "states"

define it as

select 'CALIFORNIA' FROM DUAL

UNION

SELECT 'LA' FROM DUAL

UNION

...

Add all the states

so there will be 50 select statement.

2) now join statewise tables to this newly created derived table on  basis of state.

3) on your report just drag only state object from derived table and then as per my above post create the condition for input control

I hope this will work.It's very lenghtly process.

but as of now I can think this as a best solution.

[former_member189638]

If you don't want to do any change on the Teradata end then you can hardcode this in WebI end.

You can create a variable in Webi like below and use it in the Input Control Checkbox.

=If([State] InList ("New York";"Massachusetts")) Then "East" Else If ([State] InList("Texas")) Then "South" Else "All Region"

Note: I have just shown for 3 states. You need to do this for all 50 states.

[Former Member]

Thanks for your response, Rakesh.

Like I said in the beginning, each state has its own table. i.e., one state=one table and that's it.

We can't do InList based on one object like you said in NY and MA example because they don't come from same table.

[former_member189638]

Hey! RV .. yeah.. sorry I missed that part. One solution I can think about is create a Dervived Table with 1 column (region) having values South, East, West, North, Mid.

Then use this table column for the Input Control. Now create a variable Flag

=ReportFilter([region])

Now Select each table and assign a filter Fo r eg.  Select the Texas State table and assign the filter Flag = "South".. Hope this should work.. please test and let us know.

[Former Member]

Rakesh, let me reiterate what you said...

You are telling to create a derived table at the universe level with one field (Region) and List of Values as East, Central, Mountain and West and use it in the report as an input control.

and then.., create a variable (lets say., TempVar) =ReportFilter([Region])

and then.., for Texas table assign [Region] = "Central" as a filter from the analysis tab.

If I understood it correct, I have few concerns on this process..

1. If we create a derived table that is not joined to any other table in the universe, does it not create Cartesian product when we pull it into the report?

2. Also, do I have to create a filter on each table from Analysis tab? or do I have to add a new column to the report table and give =[Region]="Central" and hide that column?

[former_member189638]

1. I was thinking to create 2 queries(One for report and other for Input Control). But I don't think that would work because Query 1 filter will not have an impact on Query 2. How about creating the derived table with Region and State and then joining it on state. Then you can drag the region as well. let me know if this works. 

2. You have to create Filter for each table from Analysis tab or you can create a conditional Hide for each table (Right click table -> Format table -> General -> hide when Formula)

[Former Member]

Rakesh,

In our case we have 50 tables for 50 states. Each state is coming from a different table. So we cant join derived table to a single table and expect all states data.