Forum Discussion

MarkHoughton's avatar
MarkHoughton
Contributor
2 years ago

Drill Back to SAP ByDesign returns active month in SAP but WF Year from OneStream ?

Hi all,

I have a 'working' DrillBack script which connects happily to my SAP ByDesign system. However, I want to extend it's flexibility and get it to DrillBack to the active WF year and period which is not necessarily the current WF. However, regardless of me even hard coding a start and end date rather than receiving the WF settings, it will always take the year element from my BR script but the currently active accounting period in SAP ByDesign as the end month. 

Is it a OneStream issue or a SAP ByDesign issue that I am not understanding whereby it will ignore my web-request parameter and just go with the active period ? 

Example 1 of the WF time

Dim wRequest As WebRequest = WebRequest.Create("https://my999999.sapbydesign.com/sap/byd/odata/FINGENERALLEDGER_analytics.svc/RPFINGLAU02_Q0001QueryResults?$select=CPOSTING_DATE,CCOMPANY_UUID,CGLACCT,TGLACCT,CFISCPER,CACC_DOC_UUID,TACCDOCTYPE,CNOTE_IT,TOEDREF_OBJ_TC,FCDEBIT_CURRCOMP,FCCREDIT_CURRCOMP&$filter=PARA_COMPANY%20eq%20%27" & companyCode & "%27%20and%20PARA_SETOFBKS%20eq%20%271000%27%20and%20PARA_GLACCT%20eq%20%27" & corpAccts & "%27%20and%20(%20PARA_FISCYEARPER%20ge%20" & "1" & wfYear.ToString & "%20and%20PARA_FISCYEARPER%20le%20" & fiscPer & "%20)&orderby=CPOSTINGDATE")

Example 2 : Hard coded start and end months ( ie Month 1 to 9 of 2020 )

Dim wRequest As WebRequest = WebRequest.Create("https://my999999.sapbydesign.com/sap/byd/odata/FINGENERALLEDGER_analytics.svc/RPFINGLAU02_Q0001QueryResults?$select=CPOSTING_DATE,CCOMPANY_UUID,CGLACCT,TGLACCT,CFISCPER,CACC_DOC_UUID,TACCDOCTYPE,CNOTE_IT,TOEDREF_OBJ_TC,FCDEBIT_CURRCOMP,FCCREDIT_CURRCOMP&$filter=PARA_COMPANY%20eq%20%27" & companyCode & "%27%20and%20PARA_SETOFBKS%20eq%20%271000%27%20and%20PARA_GLACCT%20eq%20%27" & corpAccts & "%27%20and%20(%20PARA_FISCYEARPER%20ge%20" & "12020" & "%20and%20PARA_FISCYEARPER%20le%20" & "92020" & "%20)&orderby=CPOSTINGDATE")

Can anyone explain where I am going wrong ?  Thanks in advance.

Regards

Mark

Data Integration
Data Sources
  • LeeB's avatar
    LeeB
    Contributor II
    2 years ago

    The formatting of your URL isn't correct in the section where you specify the Accounting Period.

    Try:

    The yellow highlighted text is the Accounting Period. 

    Any incorrectly formatted parameters will be ignored by SAP ByDesign.  In the case of Accounting Period, if no parameters are found it will revert to the latest open Accounting Period.

     

  • LeeB's avatar
    LeeB
    Contributor II
    2 years ago

    You can also use 

    System.Web.httpUtility.UrlPathEncode
     
    You can then specify the URL without the %20 for blank spaces, %27 for ' etc.
     
    e.g.
     
    Dim url As String = System.Web.httpUtility.UrlPathEncode("webserviceendpointgoeshere")
    Dim wRequest As WebRequest = WebRequest.Create(url)
     
    • MarkHoughton's avatar
      MarkHoughton
      Contributor
      2 years ago

      Hi Lee,

      So I have tried the following, the PathEncode helps tidy it up, but whichever of these 3 I try, it is not pulling the correct month data.  What I have found though, is regardless of what SAP account I drill back to, it will only send 50 items to the DrillBack grid in OneStream. At least, when I right click and export the grid to a csv it is only giving me a maximum of 51 rows. So it does pull all the months I am 'sending' if there are less than 50 items in that particular gl account. I have searched for a 'Top50' in the code, but cannot see that it is restricting it on the OneStream side. 

      Dim url As String = System.Web.httpUtility.UrlPathEncode("https://...etc/RPFINGLAU02_Q0001QueryResults?$select=CPOSTING_DATE,CCOMPANY_UUID,CGLACCT,TGLACCT,CFISCPER,CACC_DOC_UUID,TACCDOCTYPE,CNOTE_IT,TOEDREF_OBJ_TC,FCDEBIT_CURRCOMP,FCCREDIT_CURRCOMP&$filter=PARA_COMPANY eq '" & companyCode & "' and PARA_SETOFBKS eq '1000' and PARA_GLACCT eq '" & corpAccts & "' and ( PARA_FISCYEARPER ge 32022 and PARA_FISCYEARPER le 82022) & orderby=CPOSTINGDATE")


      Dim url As String = System.Web.httpUtility.UrlPathEncode("https://...etc/RPFINGLAU02_Q0001QueryResults?$select=CPOSTING_DATE,CCOMPANY_UUID,CGLACCT,TGLACCT,CFISCPER,CACC_DOC_UUID,TACCDOCTYPE,CNOTE_IT,TOEDREF_OBJ_TC,FCDEBIT_CURRCOMP,FCCREDIT_CURRCOMP&$filter=PARA_COMPANY eq '" & companyCode & "' and PARA_SETOFBKS eq '1000' and PARA_GLACCT eq '" & corpAccts & "' and ( PARA_FISCYEARPER ge " & "1" & wfYear.ToString & " and PARA_FISCYEARPER le " & fiscPer & " )&orderby=CPOSTINGDATE")


      Dim url As String = System.Web.httpUtility.UrlPathEncode("https://..etc/RPFINGLAU02_Q0001QueryResults?$select=CPOSTING_DATE,CCOMPANY_UUID,CGLACCT,TGLACCT,CFISCPER,CACC_DOC_UUID,TACCDOCTYPE,CNOTE_IT,TOEDREF_OBJ_TC,FCDEBIT_CURRCOMP,FCCREDIT_CURRCOMP&$filter=PARA_COMPANY eq '" & companyCode & "' and PARA_SETOFBKS eq '1000' and PARA_GLACCT eq '" & corpAccts & "' and ( PARA_FISCYEARPER ge " & "3" & wfYear.ToString & " and PARA_FISCYEARPER le " & "8" & wfYear.ToString & " )&orderby=CPOSTINGDATE")


      Dim wRequest As WebRequest = WebRequest.Create(url)

       

  • Henning's avatar
    Henning
    Valued Contributor II
    2 years ago

    Hi Mark,

    Not exactly sure if the URLs in your post are genuine customer URLs, but I would suggest to take those out. Not just change the URL a little, but remove those completely, as this is a public forum 😉