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

[MarkHoughton]

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_Q0001Query..." & 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_Q0001Query..." & 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

[LeeB]

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]

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]

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)

[LeeB]

50 records is the default number of records returned by the SAP ByDesign Web Service.

https://blogs.sap.com/2015/03/10/odata-for-sap-business-bydesign-analytics/

You need to add $top=50000 (or whatever number of records you'd like to return).

[Henning]

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 😉 

[LeeB]

I think we're good. The URL doesn't refer to an active system.  The majority of the URL is referring to generic fields and objects, similar to this content https://blogs.sap.com/2015/03/10/odata-for-sap-business-bydesign-analytics/.