AFO 2.3 SAPSETDATA

Hi,

We are trying to use this formula method at a client but having some issues. Firstly, it seems to use it on going we need to Covert the using Formula's therefore removing the Crosstab. If we don't do this each time we recalculate/save data/save workbook we look the formula in the cell. This seems strange to me.

Secondly, we have a excel doing all the formula calc's and we only trying to write back to the target Direct update DSO the base key figures. (This is instead of using inverse formula's). When we change a cell in the excel sheet we get a “Exception from HRESULT: 0x800A9C68” error.

Using SAPGUI 7.30 with BW on HANA 7.31. SP7.

Anybody had any success with using SAPSETDATA?

SAP Managed Tags:
SAP Analysis for Microsoft Office

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (0)

Answers (1)

Hi Emma,

I am not near a machine with Analysis Office right now, but you should know that SAP Gui 730 went out of maintenance early last year; you should upgrade your SAP Gui as soon as possible to 740. I don't know if that will make a difference or not, but it is something you should consider doing very soon.

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Thanks Tammy. Wish we could but as it's at a client, I have no say on this matter.

Hi,

SAPSetData works well for us in AO2.3 BW 7.5.

I'm not sure I understood why you want to remove the Crosstab. Do you want to replace SAPGetData with SAPSetData?

We put SAPSetDATA in a separate tab of same Workbook.We writing back to a cube, but I don't think it makes a big difference for SAPSetData.

Hi Gersh,

I don't want to remove the crosstab but AFO doesn't retain the =sapsetdata(DS_1,H35,.......) in the cell unless we Convert to Formula's. Once you Save data, Recalculate it replaces the =SAPSETDATA with the value.

We are using the option to get the value using cell reference. In the case the cell reference value changes we need to apply the sapsetdata again but as AFO doesn't retain the formula in the cell this we cannot write the new value back.

Cheers

Hi Emma,

Did you write "... we need to Covert the using Formula's therefore removing the Crosstab..." in your initial post or I imagined that?

In cells where you see numbers there are actually formulas with SAPGetData function calls. Therefore it's not a got idea to override them. That's why when you refresh the template your formulas get overridden back to the original SAPGetData. And that's why I recommended putting your formulas in a separate sheet. Is there any reason why you still want keeping them where SAPGetData is?

If you don't override formulas that AO put in place, your formulas will stay in place as well, even if they refer to a cell.

Please let me know if this isn't clear.

Hi Gersh,

In our case, the input enabled query is in a hidden sheet, which is only supporting the write-back functionality. The business are doing all there planning in Excel with Excel formulas. In this case, I don't know how I can put your suggestion into practice.

Do you have any advice on the Exception error?

Thanks

Add another sheet, put SAPSetData formulas in it and hide it.

Try to do it first with a simple case and check if you still have that error.

Hi Gersh,

I'm not 100% clear.

In our solution,

Sheet 1 contains purely excel cells and formula's which business will use to plan there key figures. Eg. ASP (Average Sales Price), Units, Sales

Sheet 2 (Hidden) contains an input enabled bex query with a key figure called Sales

Sheet 2 is where we Convert to formula and change the cell of Sales to = SAPSETDATA to the cell of Sales on Sheet 1.

Appreciate if you could clarify what you are proposing given the above.

Many thanks

Emma

Hi Emma,

It's not clear why do you need a DataSource in Sheet2 since you don't use that data anywhere.

So, I'd suggest you either remove your DataSource from Sheet2 and put your =SAPSetData there. They will refer to cells in Sheet1.

Or you create Sheet3 and put =SAPSetData in that sheet. They'll still refer to cells of Sheet1.

Cheers,

Gersh

Thanks Gersh, I get it now and have tried this. Unfortunately still getting the Exception error."Exception from HRESULT: 0x800A9C68"

If I don't have a Datasource in Sheet 2 what do I use in the SAPSETDATA formula (DS_?....)

Yes, you are right - you need Sheet2 to refer to it in SAPSetData. Then try option 2 - include your SAPSetData in Sheet3.

But first check if your Workbook works without any formulas. Then add one formula and heck again.

If you still get same error - post your formula here.

-Gersh

P.S. Do you have any VBA in your Workbook?

Firstly, I really appreciate you taking the time to work with me on this. Thank you.

No VBA in workbook.

Still getting the error.

=SAPSetData("DS_2",Excel!I6,,"CC_SUBCAT_Y1V1","0FISCYEAR","Z22017","ZFISCWEEK","8")

Try do the following.

Go to the tab where you have your DataSource. Click "Convert to Formula" and copy 2nd parameter you have in that formula. Paste it in your formula instead of CC_SUBCAT_Y1V1 (3rd parameter).

Now try saving.

CC_SUBCAT_Y1V1 is the technical name for the measure. Not sure why I'm taking the cell reference into the measure?

Hi Emma,

If you look in detail into SAPGetData and SAPSetData you can see that ...Set... has one more parameter than ...Get.... That's why shift in that place.

Yes, this is measure name (in both formulas), but same measure is represented by a GUID. You can get it from Query definition or from SAPGetData. I suggested the later because it's easier to find.

And that's how it works in our case.

-Gersh

Hi Gersh,

After replacing the technical name with GUID, I'm still getting the Exception error. Also, what's happening , the formula's in the excel sheet are being overwritten with values on pressing recalculate. Frustrating.

Hi Emma,

So, the 1st screenshot is pure Excel, 2nd is your SAPSetData formulas. Where is your SAPGetData spreadsheet?

Also, can you please clarify. If CC_SUBCAT_Y1V1 is tech name of the measure, is it a calculated key figure? I'm using it for a KF that is just defined in the column. Can you try this?

Regards,

Gersh

Hi Gersh,

The screenshots in my last post were just of the excel sheet showing how the formula's are being randomly overwritten. If you see my screenshots from earlier posts at 02:35 this is where the sapgetdata query is.

Back to the exception error issue...

The key figures in the query are all base key figure. Not calculated or restricted. CC_SUBCAT_Y1V1 is the technical name of the key figure configured in the query. Our key figures are defined in the rows. But same thing really.

Thanks

So, in that screenshot at 02:35, rows 1 - 18 are SAPGetData and rows 21 and down are SAPSetData, correct?

You are showing what you have in cell J23. Can you please show what you have in cell J3?

Can you please also tell where that 1000 in J23 is coming from? It's supposed to come from EXCEL!I6 (also can you try putting double quotes around Excel!I6), but in screenshots of your Excel tab I6 = 50.

Can you also try putting some number in J19 of Sheet1 and replace Excel!I5 with J19 in formula in J23. Would Recalculate work in that case?

Regards,

Gersh

Looks like it's bug when you write a formula. If you write a number - works fine.

Hi,

Yes, apparently fixed with AFO 2.3 SP02 due for release CW 37 2016.

Hope if fixes my other issue where the formula is overwritten.

Let's see...

Thanks for your help.

You can either wait 2 weeks for SP02 or use workaround I suggested earlier.

It works for me:).

-Gersh

Tried it but it didn't work in our case. I think the issue starts when we have multiple formula's referencing Sales for example

Sales = Items * Price

Discounts % = Input

Discounts = Input \ Sales

Markdown % = Input

Markdown = Input \ Sales

It's kinda hard to believe that number of references can affect that.

Have you tried adding 1 formula at a time? After adding which formula does it break?

Yes, I have.

Can you please tell then up to what formula it worked and starting from which it stops working? I want to try it on my system.

Thank you,

Gersh

No problem.

Excel part:

Transactions Input-read (IR)

Baskets: IR

Items: Transactions * Baskets

ASP: IR

PMR Discounts %: IR

Clearance markdowns %: IR

PMR Discounts: PMR Discount % / Sales

Clearance markdowns: Clearance markdown % / Sales

Sales: Items * ASP

SAPSETDATA key figures:

Transactions

Items

PMR Discounts

Clearance Markdowns

Sales

Steps taken:

1. Added SAPSETDATA for Transaction, Items, Sales - Worked

2. Added excel formula to PMR Discounts, then added SAPSETDATA for PMR Discounts - Worked

3. Added excel formula to Clearance markdown, then added SAPSETDATA for Clearance markdown. Changed transaction. Exception error.

Let me know how you get on.

Hi Emma,

Now that your exception error is solved with SP2 can you please try modifying your SAPSetData statement.

It has an optional parameter usually left empty between cell to be written and measure name. One of the possible values is "SetOnly". Can you please put it there. It fixed the issue for me.

Regards,

Gersh

Ask a Question

Top Q&A Solution Author

User

Count