Merge two rows in one row with extra column

[former_member186487]

Hello,

We are on BI 4.1 on Windows. I am an administrator and want to write a basic WebI report. How can I merge the master and details rows into one row. The customer no, Name, item comes from TAB1 and code and value comes from TAB2.

Customer no     Name     Item     code     value

12345               Name1     IT1     17          23.00

12345               Name1     IT1     63          45.00

Teh TAB2 will always have multiple rows for one item based on the code field. I am only interested in two always. The code 17 means price and 63 means maintanance cost for 5 years.

I want it to display in one rows like -

Customer no     Name     Item     Price     MaintCost

12345               Name1     IT1     23          45.00

The value of Price and MaintCost will defined based on the code field, which is in TAB2.

How it is possible in Web Intelligence 4.1? I am hoping it is something simple to model for a technical administrator.

Please let me know.

Best Regards,

SS

[former_member225163 · Accepted solution]

Hi Surendra,

As you are saying Code and Values are dependent on Item, pull Item also in TAB2 [ am assuming you

are referring data provider as TAB] and then merge TAB1 and TAB2 on Item column.

After merging, create two detail variables for Price and MainCost and associate with Merge dimension Item. Use below formulas for Price and MainCost.

Price:

Case when code=17 then [Price] End

MainCost:

Case when code=63 then [Maincost] End

Hope this helps..

Thanks,

Bala

[Former Member · Accepted solution]

Hi,

Create two queries one for Tab1 and another one for Tab2. merge these queries by Customer No only. Make unchecked option of auto-merge dimension in document properties. ie only merged by Customer No.(if Unique Id for both table).

Then drag the objects Customer No from merged dimension and Name and Item from Table 1 and define the variable for price and Maincost base on Code value in Table2.

ie) if(Q2.Code=17;[Price]) & if(Q2.Code=63;[Maincost]

Option 2:

Define Detail object for Tab2 values associated with merged dimension, then define condition based on these detail objects.

Thanks,

Parthi.

[Former Member]

Hi,

Create two queries one for Tab1 and another one for Tab2. merge these queries by Customer No only. Make unchecked option of auto-merge dimension in document properties. ie only merged by Customer No.(if Unique Id for both table).

Then drag the objects Customer No from merged dimension and Name and Item from Table 1 and define the variable for price and Maincost base on Code value in Table2.

ie) if(Q2.Code=17;[Price]) & if(Q2.Code=63;[Maincost]

Option 2:

Define Detail object for Tab2 values associated with merged dimension, then define condition based on these detail objects.

Thanks,

[Former Member]

You can do this at the universe or at the report level.

At the universe, you would create a price object as:

CASE WHEN table.code=17 THEN table1.value END

Create a MaintCost object the same way but swap 17 for 63

At the report level, you would use If then else logic, with a price variable defined as:

=If([code]=17;[value])

Again, swap 17 for 63 to define the MaintCost variable.

Regards,

Mark

[former_member186487]

Thank you all for a reply.

I was able to define two variables as dimention. I see them in the screen and I am able to drag them in Design mode in the report.

I create a column on right click, insert column to the right. I drag and drp the variable but the as soon it is added it creates a second line, which not I want.

I want them in one line.

Sorry for such a basic question.

Your help will be greately appreciated.
Thank you,

SC

[Former Member]

Hi surendra singh,

Customer no     Name     Item     Price     MaintCost

12345               Name1     IT1     23          45.00

How you r getting this price as 23??

what exactly is ur requirement??

Thanks

Mahesh...