Often in analytics we want to compare our current numbers to the numbers on a different date. For this we could get some BI/BW expert to write some code for us to get the data together; or we can do it ourselves in one CDS Cube, using a help view...
The Use Case
To showcase the topic of this blog, I have following use case: my company has treasury items and I would like a report where I want to compare the current value of a treasury item (all the way up to the current date) with the value the treasure item had at the end of last year at year-end-closing. This sounds like you need a developer doing two separate selects and then combining them into one report, right? Wrong! We can do this all in CDS...
Help View for previous Year-End-Value
We create a help view in CDS, which sole purpose is to get the data up until a certain date: we won't hard-code any specific date of course, but when we use this later, this would be start date of the current accounting year:
@EndUserText.label: 'Treasury Value on Key Date'
@VDM.private: true
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType {
...
}
@Metadata.ignorePropagatedAnnotations: true
define view ZP_TreasuryValOnKeyDate
with parameters
P_KeyDate : vdm_v_key_date
as select from I_TrsyPositionValueCube
{
// "Key" fields
key TreasuryValuationArea,
key TreasuryValuationClass,
key CompanyCode,
key SecurityAccount,
key SecurityClass,
// Currency
PositionCurrency,
// Values
@Semantics.amount.currencyCode: 'PositionCurrency'
sum( BookValueAmtInPositionCurrency ) as BookValueOnKeyDatePosCry
}
where TreasuryPositionLedgerDate < $parameters.P_KeyDate
group by TreasuryValuationArea, TreasuryValuationClass, CompanyCode,
SecurityAccount, SecurityClass, PositionCurrency
Let's go through this CDS. We create a private view, because this CDS view is not meant to be used on its own.
Next, we expect a parameter: the key date. We then use this date to select all the data up until that date, but not on the date itself. Say for example your accounting year (or monthly period, if you are interested month-by-month; both would work with this CDS help view) is from January 1st to December 31st. We are right now in 2024. So I want to compare my current treasury value in 2024, with what it was at the end of 2023. So the interval for the whole final report would be 2024-01-01 until TODAY. The key date for the help view is then 2024-01-01. Meaning we get the treasury value it had by 2023-12-31 (before 2024-01-01)
Our data comes from an interface view that exists in SAP Standard; in this case, all the treasury value positions. This means we will have several lines of data per treasury item, which is we we sum up (line 23) and group (line 26-27) per treasury item. Confused. Let's make a mini example.
Say we have just 5 lines in our Treasury Position Values until 2024-01-01:
Treasury Item A | 2023-03-15 | EUR | 10'000
Treasury Item A | 2023-06-22 | EUR | -2'000
Treasury Item B | 2023-01-05 | EUR | 50'000
Treasury Item B | 2023-11-22 | EUR | 5'000
Treasury Item A | 2023-12-09 | EUR | 1'000
These line would then get grouped (by the full key we defined) and consolidated, so we would have 2 lines in the output of our private CDS view:
Treasury Item A | EUR | 9'000
Treasury Item B | EUR | 55'000
That's it for the help view. This view will now return to us the value of all the treasure item up until a certain date.
Analytical Cube
Next up, we build our cube for the final report, where we use the help view. I will add some of the CDS code for that cube, but not all.
@EndUserText.label: 'Treasury Period End Comparison'
@Metadata.ignorePropagatedAnnotations: false
@ObjectModel:{
usageType.serviceQuality: #X,
usageType.sizeCategory: #L,
usageType.dataClass: #MIXED,
supportedCapabilities: [ #ANALYTICAL_PROVIDER ],
modelingPattern: #ANALYTICAL_CUBE
}
@Analytics: { dataCategory: #CUBE, dataExtraction.enabled: true }
define view ZI_TreasuryValueComparisonCube
with parameters
P_CompanyCode : bukrs,
P_ProductType : pro,
P_DateFrom : cds_from_date,
P_DateUntil : cds_to_date
as select from I_TrsyPositionValueCube
association[0..1] to ZP_TreasuryValOnKeyDate as _TOnKeyDate
on $projection.TreasuryValuationArea = _TOnKeyDate.TreasuryValuationArea
and $projection.TreasuryValuationClass = _TOnKeyDate.TreasuryValuationClass
and $projection.CompanyCode = _TOnKeyDate.CompanyCode
and $projection.SecurityAccount = _TOnKeyDate.SecurityAccount
and $projection.SecurityClass = _TOnKeyDate.SecurityClass
{
// "Key"
TreasuryValuationArea,
TreasuryValuationClass,
CompanyCode,
SecurityAccount,
SecurityClass,
// Attributes
...
// Key Date
TreasuryPositionLedgerDate as KeyDate,
// Product Type
FinancialInstrumentProductType as ProductType,
// Currency
PositionCurrency,
/* Compare Values Previous Period -------------------------------------- */
@DefaultAggregation: #MAX
@Semantics.amount.currencyCode: 'PositionCurrency'
@EndUserText.label: 'Book Value Prev.Per'
_TOnKeyDate(P_KeyDate:$parameters.P_DateFrom).BookValueOnKeyDatePosCry,
/* Position Values Current Period -------------------------------------- */
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'PositionCurrency'
@EndUserText.label: 'Book Value'
BookValueOnKeyDatePosCry,
...
}
where CompanyCode = $parameters.P_CompanyCode
and FinancialInstrumentProductType = $parameters.P_ProductType
and TreasuryPositionLedgerDate
between $parameters.P_DateFrom and $parameters.P_DateUntil
Let's unpack this CDS. We are defining a view for an analytical CDS cube.
We define certain parameters, which we must have such as company code and of course the date interval for the report: this would be the "current period" in my use case. Meaning the first date of my financial accounting year, up until TODAY.
We select from the same interface view as before, because that is the view that has all our data.
Next we associate our help view, which requires us to enter the key fields (which is why we had to define proper key fields in that help view, so that our consolidation works correctly).
And lastly, we define all the needed fields for the report. Here we no longer define actual key fields, but measures and dimensions for the analytical report.
And now, how to use our help view? I'll copy it here once more:
_TOnKeyDate(P_KeyDate:$parameters.P_DateFrom).BookValueOnKeyDatePosCry,
We call our association and here we pass the parameter that we defined on our association, which is the same as the start date of our report's date-interval. Also important to note is the annotation "@DefaultAggregation: #MAX". We need this, because the value from the previous period should not be selected on ever treasury position value line and then summed up. Depending how many lines in the current period we have for our treasury item, that could suddenly give some huge value. So, let's come back to the previous example, we had this:
Treasury Item A | EUR | 9'000
Treasury Item B | EUR | 55'000
And now let's say, in the current period, we have a couple more lines:
"Key" | Trans.date | Curr | Val.prev.per. | Transaction value
Treasury Item A | 2024-02-15 | EUR | 9'000 | 500
Treasury Item B | 2024-02-28 | EUR | 55'000 | -5'000
Treasury Item A | 2024-03-01 | EUR | 9'000 | -1'000
So, this cube-CDS will return on each line the value of the previous period, for that treasury item (key). Hence we have to make sure we don't sum this value up, otherwise for Treasury Item A in my example, the report would state that the value at then of last year was 18'000, instead of 9'000, which would be very wrong.
And that's it. From this CDS cube we can create our analytical report where we can see the value of our Treasury Item from the previous period and compare it to the current value. Simple, right?
Hope this is helpful! And keep using CDS and RAP and don't fall back to old-school ABAP reports 😉
