Skip to Content
author's profile photo Former Member
Former Member

AFO sapsetdata not work for members not in report

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!

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    Posted on Dec 24, 2016 at 02:37 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      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)
  • Posted on Dec 27, 2016 at 04:45 AM

    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?

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      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!

  • Posted on Dec 27, 2016 at 07:43 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member
      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!

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.