Creating user defined object

[former_member672670]

Hi Guys,

I have 3 derived tables in my universe - A, B & C. Table A represents "Local projects" & Table B represents "Foreign Projects".

Table C is the union of A & B. All 3 tables have their own classes. Also, Project ID is the common dimension in all 3 tables.

Now, I want to create an object called "Project Type" in Class C which will give me "Local projects" if the project is from Table A and "Foreign Projects" if it is from Table B.

Basically, I want to have a filter in my Webi Report for "Project Type" in which if I select "Local Projects" I get contents from Table A and if I select "Foreign projects", then I get contents from Table B.

Is this possible ? Any help is appreciated.

[Former Member · Accepted solution]

When you say C is a union of A and B, how is it created?

Is it a physical table, mainted by ETL?

Is it a view?

Is it a derived table?

Is it something else?

Whichever it is, I'd recommend amending it and adding a column to it as project type, e.g.:

select

col1, col2, col3, 'L' as ProjectType from A

union

select

col1, col2, col3, 'F' from B

That way, you'll only need table C in your universe and can create predefined conditions for Local and Foreign projects as required.

[former_member672670 · Accepted solution]

Thanks. That was easy.

[former_member4998]

Hi

If you have union of data in the C table …why are you using the A and B tables?

Can you specify ….where do you want to use the project type filter? Before running the webi Report or after running the webi Report?

If Before running

Use only C tables, in the webI query filter’s place the project type. So while running the report it will ask project type. Form LOV’s you can able to select Local project or foreign projects.

[former_member672670]

To answer your first question, I am using tables A & B because I cannot generate separate reports for them in Webi using Table C as it does not have an object (Project Type) to distinguish between "Local" & "Foreign" Projects.

For your second question, I want to use the project type filter before running the Webi report,. However, I need to create it first in the Universe. My question is - how can I create the "Project Type" object in the Universe which will have values - Local projects (Table A) & Foreign projects (Table B).

[former_member4998]

Hi

Follow the below steps.

Click on the parameters and list of values tab. and select the LVO's based on custom SQL.

1. Then you can create (Project Type LOV’s) LOV’s based on custom SQL.

          Select Distinct Project Type

          From Table A

          Union All

          Select Distinct Project Type

          From Table B

2. Create Project Type dimension in the BL and

• Select associated list of values and click on the list of values button.
• Then select the Project Type LOV’s and press OK, and Save
• To test click on the show values. Project Type" object in the Universe which will            have values - Local projects (Table A) & Foreign projects (Table B).