We were very excited when we discovered the standard SAP delivered CDS C_TRIALBALANCEQ0001 for displaying the Trial Balance as the calculation of opening and closing balances can be tricky and the Universal Journal ACDOCA table was new to us, as was S/4HANA.
During our testing using the embedded BW we realised that the opening and closing balances it was returning was incorrect and upon consultation with SAP they confirmed that additional new Fiscal configuration is required on an S/4 HANA environment.
They guided us to Note 2458367 - Trial Balance: incorrect ending balance (created on 13.04.2017, two days after we opened the ticket) which led us to the configuration in SPRO below for doing an initial fill of the required Fiscal tables (we applied the SAP S/4 HANA on-premise section of the note). After we applied the note our opening and closing balances were 100% correct.
I've detailed the steps on how we discovered the problem just to provide insight to anyone not familiar with embedded analytics and CDS Views, as well as the Embedded BW.
The Trial Balance ABAP managed CDS is comprised of a DDL source C_TRIALBALANCEQ0001 which generates a DDL SQL view CFITRIALBALQ0001.
If you look at the DDL source (I am using the ABAP perspective in HANA Studio) you will see the link to the name of the view that is generated. Take note that the VDM.viewType annotation is set to #CONSUMPTION.
Here I am looking at the generated view using transaction SE11 via the SAP GUI:
Scrolling down in the DDL source you will see from which view it selects its data from which is I_Glacctbalancecube.
Opening DDL source I_Glacctbalancecube (again using the ABAP perspective in HANA Studio) shows us that it will generate a view called IFIGLBALCUBE. Take note that the VDM.viewType annotation is set to #COMPOSITE.
This information shows us that we have two related views:
We can now go to transaction RSRT on S/4 HANA to see the generated objects:
We then executed the Trial Balance query with the appropriate parameters select the correct Fiscal Year and Leading Ledger:
Making sure to also create a wide interval for the mandatory posting date variables:
We sliced and diced the output to show the value for GL Account 22269600 for all available Fiscal periods. Note that it showed me an Starting Balance value of -166,974,00 ZAR and a Closing Balance value of 0,00 ZAR.
We then went to transaction FAGLB03 to double-check the results:
The results showed me that since Period 008 had no values, the Starting Balance should have been 0 and the closing balance should have been 166,974
After applying the note all the results showed correctly.
Hope this blog has helped anyone struggling with a similar problem or anyone planning on using this CDS content.
During our testing using the embedded BW we realised that the opening and closing balances it was returning was incorrect and upon consultation with SAP they confirmed that additional new Fiscal configuration is required on an S/4 HANA environment.
They guided us to Note 2458367 - Trial Balance: incorrect ending balance (created on 13.04.2017, two days after we opened the ticket) which led us to the configuration in SPRO below for doing an initial fill of the required Fiscal tables (we applied the SAP S/4 HANA on-premise section of the note). After we applied the note our opening and closing balances were 100% correct.
I've detailed the steps on how we discovered the problem just to provide insight to anyone not familiar with embedded analytics and CDS Views, as well as the Embedded BW.
How we found the problem...
The Trial Balance ABAP managed CDS is comprised of a DDL source C_TRIALBALANCEQ0001 which generates a DDL SQL view CFITRIALBALQ0001.
If you look at the DDL source (I am using the ABAP perspective in HANA Studio) you will see the link to the name of the view that is generated. Take note that the VDM.viewType annotation is set to #CONSUMPTION.
Here I am looking at the generated view using transaction SE11 via the SAP GUI:
Scrolling down in the DDL source you will see from which view it selects its data from which is I_Glacctbalancecube.
Opening DDL source I_Glacctbalancecube (again using the ABAP perspective in HANA Studio) shows us that it will generate a view called IFIGLBALCUBE. Take note that the VDM.viewType annotation is set to #COMPOSITE.
This information shows us that we have two related views:
- Composite view called IFIGLBALCUBE - when activated it generates a transient provider for the Embedded BW with the naming convention 2C<SQLViewName> so in this case 2CIFIGLBALCUBE
- Consumption view called CFITRIALBALQ0001 - when activated it creates a transient query for the Embedded BW with the naming convention 2C<SQLViewName> so in this case 2CCFITRIALBALQ0001
We can now go to transaction RSRT on S/4 HANA to see the generated objects:
We then executed the Trial Balance query with the appropriate parameters select the correct Fiscal Year and Leading Ledger:
Making sure to also create a wide interval for the mandatory posting date variables:
We sliced and diced the output to show the value for GL Account 22269600 for all available Fiscal periods. Note that it showed me an Starting Balance value of -166,974,00 ZAR and a Closing Balance value of 0,00 ZAR.
We then went to transaction FAGLB03 to double-check the results:
The results showed me that since Period 008 had no values, the Starting Balance should have been 0 and the closing balance should have been 166,974
After applying the note all the results showed correctly.
Hope this blog has helped anyone struggling with a similar problem or anyone planning on using this CDS content.
- SAP Managed Tags:
3 Comments
