how to merge in webi

[Former Member]

Hi

   I am trying to create a webi (BOXI 3.x SP3)report that looks like

server        supported by     name

abc             people                josh

abc             organisation        john

But I would like to bring the data in a single row like this

server  people   organisation

abc      josh         john

How can I acheive this?I would really appreciate if you could help me asap.

Thanks in advance.    

[Former Member · Accepted solution]

hi

IF YOUR data combination for suppport type and name is unique then

1)use cross tab and drop server in row header

2)use support type in column  header  and give break

3)drag and drop name in break footer and hide the original(support type header) column

Regards

Sunil

[Former Member · Accepted solution]

HI Amit/Sunil

Thankyou guys,I could fially do it.

Amit ,I did have so much data and couldnt use your method but I used the crosstabs and it worked after playing with the data a while.

[Former Member · Accepted solution]

Hi folks

   I still have a doubt , although we could manipulate the data to be placed in a way we want, could we now actually do a filter on the new column ppl or org

like if I need to now see the same 3 columns of data

server  ppl    org      but I want to filter it to one single ppl or org.

When I did it doesnt show data and I guess this wouldnt be logical since the data in the 2 new columns ppl and org are actually in the same table right?

Please advise me on this issue and correct me if I am thinking in the wrong way.

Cheers !

[Former Member · Accepted solution]

Hi

Can you explain me clealy ,becoz i didnt get you what you mean, show in sample table how you would like to see the table

regards

Sunil

[Former Member · Accepted solution]

My original table is

server        supported by     name

abc             people                josh

abc             organisation        john

def              people                dan

def              organisation        eric

But made it to look like

server  people   organisation

abc      josh         john

def       dan          eric

but now I want to do a filter to show server details with people =josh

but i couldnt do it and I am not sure since originally data for both ppl and org are in the same database.can we do this?

[amitrathi239 · Accepted solution]

Hi,

all three objects are coming from single webi query?

second if you drag only server object then two records are coming or four?duplicate records..

if you are getting two records only then create two variable with below conditions.

=[name] Where ([supported by]="organisation")

=[name] Where ([supported by]="people")

add server  and these two variables in the report automatically you will get the two row only.

Thanks,

Amit

[Former Member]

Hi,

1. Take a cross-tab and drop Server in row header

2. Drop Supported By in column header.

3. Create a variable for name as

=If([Supported By] = "People"; [Name]; [Name])

and drop it in the cross-tab.

Now you should be able to sort, filter or apply any condition to the table.Hope this helps.

Thanks,

Abhishek

[Former Member]

John,

If you have limited number of values for the supported by Object say upto 10 i would recommend trying the solution below.

Create Variable for Support Type = People

VAR1 = Name Where(Support Type="People")

VAR2 = Name Where(Support Type="Organisation")

Create a Vertical Table as below you can rename the header for VAR1 to People and VAR2 to organisation.

| Server | VAR1 | VAR 2 |

Hope this helps

[Former Member]

Hi Tauseef

   That was really helpful and I was posssible to create variables cuz they are few in numbers but I still have a problem. It is still not showing the data in a single row and also shows an extra row with blank space as shown below

Server     Var1    Var 2

abc          josh     _____

abc          _____   john

abc          _____  _____

I would appreciate if you could help me out here.

[Former Member]

Apply filter on the Block as below it will bring everything on a single line for that server.

          VAR1 IS NOT NULL

and     VAR2 IS NOT NULL

[Former Member]

Hi Tauseef

  I was thinking if I could write the condition in the variable itself like 

VAR1 = Name Where(Support Type="People") and not "Organisation"

but my code doesnt come correct in webi.Would you mind helping me with the correct format of this code.

[Former Member]

I will not recommend doing that. I would suggest applying filters

[Former Member]

I am at the client site and my senior doesnt want me to filter it but using conditions in varaible itself.

[amitrathi239]

Hi,

Let say your data is

Object1          Object 2               Object3

server        supported by     name

abc             people                josh

abc             organisation        john

Create one variable for object 2 like this.

=Replace(Replace([support];"org";"ppl");"ppl";"john")

Create one variable for object 3 like this.

=Replace([name];"john";"josh")

after that you can get single row.but not it is static only for these two records.

Thanks,

Amit