Merge option in Excel and WEBI report

[Former Member]

Hi Experts,

I have been working on a requirement wherein I have to create a WEBI report based on an excel file in BO3.1.

If I create a WEBI report based on the table below. it works as expected.

Prices	Company Name
XXXXX	Company 1
YYYYY	Company 1
11111	Company 2
22222	Company 2
33333	Company 2

Our requirement is to merge the duplicate entries of the column "Company Name" as below and to create a webi report.

Prices	Company Name
XXXXX	Company 1
YYYYY
11111	Company 2
22222
33333

I have created a webi report based on the above table which has merged cells, however it did not work as expected. Please have a look at the screenshot below.

This report should have the merged cells as we have them in the excel file. I have tried this in BI4.1SP06 as well, however got the same results.

I am not a developer but still I have tried my best to explain our issue and the requirement.

Appreciate all your help.

~SwapnilY

[Former Member · Accepted solution]

Hi Swapnil,

This can achieve by applying Breaks on company name.

Please follow the steps mentioned below:

1. Right click on table and Goto "Break" -> Click "Manage Breaks"

2. Click on Add button and add Dimension "Company".

3. On right hand side you will see drop down for "Duplicate Values" -> Select "Merge" in it.

4. Now click OK.

Hope this will help you 🙂

Regards,

Veer

[Former Member · Accepted solution]

Hi Veer,

Awesomeeeee, you saved my day

It has worked now as expected, I got few more questions for you.

- Do we have this manage breaks option in BO3.1?

- As you can see the column names(Company and Price) are getting repeated for all the rows, is there a way to get rid of them?

- Can we have these column names only in the first row of the report?

Thank you so much for all your help Appreciate it.

~SwapnilY

[amitrathi239 · Accepted solution]

Hi,

Click on the Break column->Goto the properties window->Break.

For removing the repeated headers you needs to unchecked the  option "Show Break Header".After that insert one more table in the report  (same number of columns).After inserting the header text use right Align->Relative position feature to bound these two tables together.

Amit

[Former Member · Accepted solution]

Hi Swapnil,

I am glad that the solution works for you,

Please find answers to your questions below:

- As you can see the column names(Company and Price) are getting repeated for all the rows, is there a way to get rid of them?

- Can we have these column names only in the first row of the report?

Answer:

> There is no option to omit redundant column names however you can use following trick:

1. Right click on existing column headings and hide it.

2. Create dummy row just above first row.

3. Enter desired column names and apply cell formatting (colors, fonts etc.)  so that it looks like original table heading.

Please let me know in case of any quires.

Regards,

Veer

[Former Member]

Apply break on comapany name and go to breaksetting manage a break there select duplicate values option select merge option u will see like this 

Name	Comapany
xxxxxxx	Company 1
yyyyyyyy
	Company 1
Name	Comapany
AAAAAA	Company 4
zzzzzzzzz
	Company 4

then delete the row and change setings in manage break option uncheck the header 

xxxxxxx	Company 1
yyyyyyyy
AAAAAA	Company 4
zzzzzzzzz

select table table properties and check the table header .

Name	Comapany
xxxxxxx	Company 1
yyyyyyyy
AAAAAA	Company 4
zzzzzzzzz

hope it works

Thanks and regards

Dinesh

[former_member220624]

Hi Swapnil,

So basically you want to see Company 1 appearing first and then Company 2.

In that case please select Company name column and sort it as per your requirement.

Please let me know in case of any issues.

Regards,

Amit

[Former Member]

Hi Amit,

Thank you for your inputs.

This is not what I am actually looking for. Sequence doesn’t matter to us at all.

We want them to appear in the WEBI report as they appear in the excel file. Please have a look at the screenshots below.

Excel File:

WEBI Report:

Excel files shows the merged cells of Company 1 and Company 2 for all the values of Prices column whereas WEBI report shows Company 1 for only XXXXX and the cell for YYYYY is empty. Cell for XXXXX and YYYYY should have a merged cell of Company 1 as shown in the excel file. Same should be with Company 2 as well.

Appreciate your help.

~SwapnilY

[former_member220624]

Hi Swapnil,

Do you have any measure in the report ?

I remember i once used the sub-total function to achieve a similar requirement.

Regards,

Amit

[Former Member]

Hi Amit,

No, I don't. It is a simple excel file which has two columns for "Prices" and "Company Name".

Prices column has the numeric values and Comnay Name has the company names.

~SwapnilY

[former_member220624]

Hi Swapnil,

Since you mentioned it's working correctly in 4.1 version i guess it's a limitation of 3.1 version.

Regards,

Amit

[Former Member]

Hi Amit,

No it did not work in BI4.1 as well. I mentioned that we got the same results.

Appreciate all your help

~SwapnilY

[former_member220624]

Hi Swapnil,

Can you try playing around with the border setting to achieve the desired format.

It might work.

Regards,

Amit

[Former Member]

Hi Amit,

Could you please elaborate how to use border setting in the WEBI report? Since I am not the developer I would need some help.

Thanks in advance.

~SwapnilY

[former_member220624]

Hi Swapnil,

Select the cell and right click.

Goto -> Format cell option,there you can find the border setting.

Hope it helps.

Regards,

Amit

[Former Member]

Hi Amit,

Border settings will just change the border of any cell. This is not going to help us what we are trying to achieve.

~SwapnilY