on 01082008 11:32 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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,
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))
Hi Pavel
I'm hitchhiking 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
User  Count 

64  
8  
7  
6  
6  
6  
5  
5  
5  
4 
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.