Skip to Content
0

AFO sapsetdata not work for members not in report

Dec 23, 2016 at 07:19 PM

157

avatar image

Hi Experts

Need your help.. I was trying to built an dynamic input template on BPC on S4HANA(Embedded) using Analysis excel using sapsetdata() formula..

Process – User will select CostCenter & select GL from the drop down cell & enter $ and hit save..

The problem is sapsetdata() is throwing #VALUE error if the GL4 is not present in the report but works fine for GL2 which already exist in the report as shown..

So looks like sapsetdata() does not consider members which is not present in the report.

Question – How to achieve this in analysis?

Thank you for reading!

10 |10000 characters needed characters left characters exceeded

What is you formula for sapsetdata?

0

Hi Tammy

Thank you for reading my post..

My formula is =SAPSetData("DS_2",Sheet1!F203,,F$7,$C$8,$C$6,$E$8,$E$6) which is working fine if the members are present in report.

I have found a solution or workaround as explained in response to Gersh.

Thank you once again :)

0
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Gersh Voldman
Dec 24, 2016 at 02:37 PM
0

Yes, SAPSetData doesn't have "new line" functionality (yet). If number of G/L Accounts is not too big you can put them in columns, instead of rows. In this case SAPSetData is not going to consider it a new line, but rather edit of an existing line and will work fine.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Gersh

Thank you for confirming the info & it helps to look other options.

I have more detail planning with additional dimension Line item so I guess column will not work as shown..

I found a workaround solution where I use Sheet 2 to pull same query as DS_2 with master data combination (CC1 X 60 Opex GL X 20 Line item) ie 1200 lines..

My Sheet 1 will have DS_1 with report with suppress zero(on top) & input new line using =sapsetdata() pointing to DS_2 as shown.

I guess this will have little performance issue..what you think of this solution for long run?

Thank you :)

gl5.png (8.7 kB)
0
Gersh Voldman
Dec 27, 2016 at 04:45 AM
0

Sorry, I didn't get your workaround. So, DS_2 will be the 1st parameter in sapsetdata() in some cell DS_1. But if this will result in a new row in DS_2 then it's not going to work. Can you please clarify?

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Our main problem was that sapsetdata() formula will only work if the member is present in report..

So my original requirement that user will create new line is same ( in Sheet 1) and I generated another report in Sheet 2 (DS_2) wilth all opex GL & Line items..

Below is the sapsetdata() formula pointing to $100 where user is entering data ..

=SAPSetData("DS_2",Sheet1!F203,,Sheet_2!F$7,Sheet_2!$C$8,Sheet_2!$C$6,Sheet_2!$E$8,Sheet_2!$E$6)

So formula is pointing to all members from DS_2 and New Value is pointing to cell where use will enter $ amount.

This is working perfect and as per requirement because DS_2 report already have all GLs in row.

I hope this clarify the doubt.

Thank you!

0
Gersh Voldman
Dec 27, 2016 at 07:43 PM
0

Now it looks like you probably misunderstood my reply. I'd guess that DS_1 and DS_2 are input templates, not report because you can't Save data via a report. Additionally, it's not enough for a member to be in a template. For example, if you have CC1 in one row and ACC1 in another row, but no rows with CC1 and ACC1, you won't be able to save a row with CC1 and ACC1 using SAPSetData. Conclusion: you need combinations of members, not just members, to be in DS.

Documentation says that you shouldn't refer to crosstab in SAPSetData. If you do this it won't most probably affect the result, but will affect performance significantly in case you refresh both tabs at same time.

So, your solution is to base "shadow" template on MD and the question is if this can affect performance. The answer is that it can, depending on your network speed because now it has to bring much more data to the front-end.

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Now it looks like you probably misunderstood my reply. I'd guess that DS_1 and DS_2 are input templates, not report because you can't Save data via a report. Additionally, it's not enough for a member to be in a template. For example, if you have CC1 in one row and ACC1 in another row, but no rows with CC1 and ACC1, you won't be able to save a row with CC1 and ACC1 using SAPSetData. Conclusion: you need combinations of members, not just members, to be in DS.
<AK>Yes totally agree & since CC1 is on background filter so its not in my formula<AK>

Documentation says that you shouldn't refer to crosstab in SAPSetData. If you do this it won't most probably affect the result, but will affect performance significantly in case you refresh both tabs at same time.

<AK>Yes I am using input template DS_1 & DS_2 and sapsetdata is pointing to DS_2 crosstab...But everything is working perfect as per expected and I will keep track of performance <AK>

So, your solution is to base "shadow" template on MD and the question is if this can affect performance. The answer is that it can, depending on your network speed because now it has to bring much more data to the front-end.

<AK>Sure,I will check on this<AK>


Thank you Gersh for all your time & help!

0