cancel
Showing results for 
Search instead for 
Did you mean: 

adding new customized column in the Bex Report

Former Member
0 Kudos

Hi Experts,

I have an existing column (say A ) in Bex and I want to add one more column (say B) based on the values that I have in column A.

For e.g.,

A B

-


5 5 (=A1)

8 3 (=A2 - A1)

15 7 (=A3 - A2)

I am able to create a new column B and bring the values based on above formula to get the expected results. Though the results are correct and also I saved the report as workbook, when I try to refresh the query again and open the workbook, the new column 'B' is getting disappeared.

Could someone please help me on this?

Thanks in advance for your help.

Bregards,

Krish

View Entire Topic
majeed_mohammedabdul
Active Participant
0 Kudos

Hi Krish,

What you can do is goto the second sheet and reference the values of the first sheet and do the calculation in the second sheet. In this way when you refresh the workbook the formatting will not disappear.

Hope this helps

Thanks

Regards

Majeed

Former Member
0 Kudos

Majeed,

Could you please elaborate further on this?

Do you want me to create an additional tab in the same excel sheet and do the calculation over there?

Also I don't understand on creating second sheet and reference the values of first sheet.

Please advise.

Thanks,

Bregards,

Krish

majeed_mohammedabdul
Active Participant
0 Kudos

Hi Krish,

Yes an additional tab. You remember when we open a excel we see three sheets in there, I was talking about those sheets in other words tabs. Reference the results from the first tab on the second tab and hide the first tab. Do the calculation in the second tab.

Thanks

Majeed

pavel_afanasiev
Contributor
0 Kudos

Krish, are you inserting your additional column between the columns in the analysis grid? If so, upon refresh the analysis grid resets itself - that is why Majeed suggested taking the calculations to another worksheet.

Another way of preventing this from happening is to use the BExGetData formula - you can right click on the grid and choose "convert to formula". The advantage of this is that the format will no longer change, but the disadvantage is that your grid just became static.

If you don't know much about using the BExGetData formula, let me know and I will send you guide I wrote for my business users.

Thanks

Former Member
0 Kudos

Hi Pavel Afanasiev,

I have a problem with convert to formula, when I add different Cells (Formaulas) e.g., to get a quarter value from the period values, if one period has no value and it is blank, then in the result cell (Quarter Value) shown as error (#Value). Don't know how to solve this, may be your Guid can help me out here!

Can you please send me this user Guid? (mahamd01@hotmail.com)

Many thanx in advance for you help,

kind regards,

pavel_afanasiev
Contributor
0 Kudos

Surendra,

here is your situation:

when you pull the values in a query through a formula, if there is a blank cell, typically means that there is no data being pulled. The reason that you get #Value when you try to use that cell in calculations is because excel doesn't know how to deal with it. Here are your two solutions:

1. Create a calculated KF with a formula: nodim(KF) + 0 This way even if there is no value pulled from the query, excel will pull 0. You do loose your dimensions though, but you can reformat using Excel.

2. Use the excel IF(xxxxx) formula. Here is an example (if nothing [highlighted] then convert the value to 0, otherwise show the value): =IF(bexgetdata("GABCS",R$1,$A13)="",0,'bexgetdata("GABCS",R$1,$A13))

Former Member
0 Kudos

Hi Pavel,

thanx a lot, I will try both solutions

Former Member
0 Kudos

Hi Pavel

I'm hitch-hiking on this thread.

Can you please explain what do you mean by "the grid becomes static" when you use "Convert to Formula"?

I'm studying Excel integration in the BEx Analyser and it seems to me that when you select Convert to Formula and save the query, then you load new records into the Cube, when you open the query again it's no longer refreshed (even if you select "Refresh Workbook on Open". Is it what you mean?

I'd appreciate more detailed explanation. I'd appreciate too this documentation about BExGetData.

Thanks

César Menezes