Use SAP Analytics Cloud Analytics Designer Script Editor as Chart Data Source

[apurgert]

We have a requirement to build an SAC story that will:

1. Count the number of new maintenance notifications
2. Count the number of closed notifications
3. Count the number of weeks a notification was open

Leveraging OData service C_ObjPgMaintNotification as the Model, I have been able to create additional columns in the model that calculate the week based on the date.  For example, a notification opened January 1, 2024 will show a start week of December 31, 2023 and end week of January 6, 2024.  The same is true for a completed notification.

I have used the SAC Analytics Designer to build three key-value pair dictionaries that will hold the start week and the count of new, open, and closed notifications (newNotificationsByWeek, openNotificationsByWeek, and closedNotificationsByWeek).  New and closed notifications are easy to get as they are dimensions in the model.

For open notifications, I have the SAC Analytics Designer loop through the notifications and add to the count for the respective week.  If we had a notification opened January 1, 2024 and closed February 1st, 2024 and a second notification opened January 18 and closed January 29, the output would look like this:

newNotificationsByWeek
Sun Dec 31 2023: "1"
Sun Jan 07 2024: "0"
Sun Jan 14 2024: "1"
Sun Jan 21 2024: "0"
Sun Jan 28 2024: "0"
openNotificationsByWeek
Sun Dec 31 2023: "1"
Sun Jan 07 2024: "1"
Sun Jan 14 2024: "2"
Sun Jan 21 2024: "2"
Sun Jan 28 2024: "0"
closedNotificationsByWeek
Sun Dec 31 2023: "0"
Sun Jan 07 2024: "0"
Sun Jan 14 2024: "0"
Sun Jan 21 2024: "0"
Sun Jan 28 2024: "2"

Now the issue that I have is how can I use the key-value pairs in a chart?  I have tried to loop through them and use API calls such as Chart.addDimension(), Chart.addMember, etc. but these appear to only allow dimensions and measures that are part of the model.  I looked to see if there was a way to push the values to a separate dataset or model and use that as the chart, but that doesn't seem to be possible.

Is there a way to use script variables as the chart's data source so that a bar chart with the date along the x-axis and count on the y-axis can be viewed?

[apurgert]

In case anyone else comes across this, it seems that you are unable to use script variables in charts.

I was able to get around this by creating a new CDS view that creates a cartesian product on I_CalendarDate based on my start and end dates by using >= or <= in the join and DATS_ADD_DAYS as needed.  For the DATS_ADD_DAYS, I used the creation date and then -6 to get the first week it was opened.  For the close date, I couldn't add days or items would be missed (such as records opened and closed in the same week).

Since I only needed the first day of the week, which happens to be a field in the I_CalendarDate already, I was able to get the charting we needed by:

1. Setting the WHERE clause in the new CDS view to only get anything <= the current week and where the first day of week = calendar date
2. Using dateAdd functions to create columns in the SAC Model that determined if the record was opened or closed in the week (since there is a cartesian product, I created a one to many based on how many weeks it was opened)
3. Used measures to count the total records open records by week, then subtract out the records that closed in the week

[apurgert · Accepted solution]

In case anyone else comes across this, it seems that you are unable to use script variables in charts.

I was able to get around this by creating a new CDS view that creates a cartesian product on I_CalendarDate based on my start and end dates by using >= or <= in the join and DATS_ADD_DAYS as needed.  For the DATS_ADD_DAYS, I used the creation date and then -6 to get the first week it was opened.  For the close date, I couldn't add days or items would be missed (such as records opened and closed in the same week).

Since I only needed the first day of the week, which happens to be a field in the I_CalendarDate already, I was able to get the charting we needed by:

1. Setting the WHERE clause in the new CDS view to only get anything <= the current week and where the first day of week = calendar date
2. Using dateAdd functions to create columns in the SAC Model that determined if the record was opened or closed in the week (since there is a cartesian product, I created a one to many based on how many weeks it was opened)
3. Used measures to count the total records open records by week, then subtract out the records that closed in the week