0CALDAY
dimension.The limitation is that for BW Live models, the platform has no awareness of Fiscal / Financial periods. SAC offers no native mechanism to dynamically present common reporting scenarios such as Financial Year to Date, or even just Current Financial Year. All dynamic options Calendar based only.
0FISCYEAR
and 0FISCPER3
.FISCYEAR
and FISCPER3
.FISCYEAR
is created as NUMC(4), with Master Data and also Texts (optional) selected:Not all of these are critical for the solution, in fact only the last two will be exposed to SAC later in the demo. The other flags we have in use for specific unrelated modelling requirements.
FISCPER3
is created as CHAR(3) to mimic 0FISCPER3
. It just has Master Data defined.FISCYEAR
master data provides a generated list of Financial years, determining all the required attributes in addition to the Display Text used in other reporting:FUNCTION "_SYS_BIC"."ZBW.Functions::TF_FISCYEAR_MASTER" ( )
RETURNS TABLE ( FISCYEAR VARCHAR(4),
CURR_YEAR VARCHAR(1),
LAST_YEAR VARCHAR(1),
CURR_YEAR_LP VARCHAR(1),
LAST_YEAR_LP VARCHAR(1),
YEAR_OFFSET VARCHAR(5),
YEAR_OFFSET_LP VARCHAR(5),
TXTSH VARCHAR(20))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
DEFAULT SCHEMA SAPHBW AS
BEGIN
DECLARE CURR_YR INT = YEAR(CURRENT_DATE);
DECLARE CURR_YR_LP INT = YEAR(ADD_MONTHS(CURRENT_DATE,-1));
IF MONTH(CURRENT_DATE) > 6
THEN CURR_YR = CURR_YR + 1;
END IF;
IF MONTH(ADD_MONTHS(CURRENT_DATE,-1)) > 6
THEN CURR_YR_LP = CURR_YR_LP + 1;
END IF;
RETURN
SELECT
ABAP_NUMC(GENERATED_PERIOD_START, 4) AS FISCYEAR,
CASE GENERATED_PERIOD_START
WHEN :CURR_YR THEN 'X' ELSE ''
END AS CURR_YEAR,
CASE GENERATED_PERIOD_START
WHEN :CURR_YR - 1 THEN 'X' ELSE ''
END AS LAST_YEAR,
CASE GENERATED_PERIOD_START
WHEN :CURR_YR_LP THEN 'X' ELSE ''
END AS CURR_YEAR_LP,
CASE GENERATED_PERIOD_START
WHEN :CURR_YR_LP - 1 THEN 'X' ELSE ''
END AS LAST_YEAR_LP,
CASE
WHEN GENERATED_PERIOD_START - :CURR_YR = 1 THEN 'NEXT'
WHEN GENERATED_PERIOD_START - :CURR_YR = 0 THEN 'CURR'
WHEN GENERATED_PERIOD_START - :CURR_YR = -1 THEN 'LAST'
WHEN GENERATED_PERIOD_START - :CURR_YR < -1 THEN '-' ||
ABAP_NUMC(GENERATED_PERIOD_START - :CURR_YR,2)
ELSE ABAP_NUMC(GENERATED_PERIOD_START - :CURR_YR,2)
END AS YEAR_OFFSET,
CASE
WHEN GENERATED_PERIOD_START - :CURR_YR_LP = 1 THEN 'NEXT'
WHEN GENERATED_PERIOD_START - :CURR_YR_LP = 0 THEN 'CURR'
WHEN GENERATED_PERIOD_START - :CURR_YR_LP = -1 THEN 'LAST'
WHEN GENERATED_PERIOD_START - :CURR_YR_LP < -1 THEN '-' ||
ABAP_NUMC(GENERATED_PERIOD_START - :CURR_YR_LP,2)
ELSE ABAP_NUMC(GENERATED_PERIOD_START - :CURR_YR_LP,2)
END AS YEAR_OFFSET_LP,
ABAP_NUMC(GENERATED_PERIOD_START - 1, 4) || '/' ||
ABAP_NUMC(GENERATED_PERIOD_START,2) AS TXTSH
FROM "PUBLIC"."SERIES_GENERATE_INTEGER" (1, 1970, 2100);
END;
It is worth noting that the above code assumes the Australian Financial Year, beginning in July and ending June the following calendar year. Within my organisation we do not have a requirement to support international reporting for different Financial Year Variants. If there was a requirement to do so, then the likely approach would be to compoundFISCYEAR
against0FISCVAR
as per standard BW, and enhance the code above to provide determinations for the different Fiscal Year Variants.
SERIES_GENERATE_INTEGER
to provide a generated input list of Years. I chose to provision data for a range of 1970 - 2100. This range could be enlarged or reduced by updating the parameters, or even dynamically determined using the current year to provide a sliding window.FISCPER3
master data provides a generated list of Financial Periods from 000 - 012, determining all the required attributes for both contexts of based on current open period, or last completed period.FUNCTION "_SYS_BIC"."ZBW.Functions::TF_FISCPER3_MASTER" ( )
RETURNS TABLE ( FISCPER3 NVARCHAR(3),
CURR_PERD NVARCHAR(1),
LAST_PERD NVARCHAR(1),
YTD_L_PER NVARCHAR(1),
YTD_C_PER NVARCHAR(1))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
DEFAULT SCHEMA SAPHBW AS
BEGIN
-- Generate initial list of periods using SERIES_GENERATE_DATE
-- The years of the dates passed to SERIES_GENERATE_DATE are not critical as we just need one record for the 1st of each month
-- Initial DUMMY SELECT starts table with record for period '000'
-- Current and Last Period flags derived from current system date, so are calendar based
PeriodTab =
SELECT '000' FISCPER3,'' CURR_PERD,'' LAST_PERD FROM DUMMY
UNION
SELECT
ABAP_NUMC(ELEMENT_NUMBER,3) FISCPER3,
CASE WHEN MONTH(GENERATED_PERIOD_START) = MONTH(CURRENT_DATE) THEN 'X' ELSE '' END CURR_PERD,
CASE WHEN MONTH(GENERATED_PERIOD_START) = MONTH(ADD_MONTHS(CURRENT_DATE,-1)) THEN 'X' ELSE '' END LAST_PERD
FROM SERIES_GENERATE_DATE('INTERVAL 1 MONTH', '2000-07-01', '2001-07-01');
-- Return statement uses initial generated table plus two extra columns derived for the Current and Last Period
-- The inline nested SELECT statements look inefficient but performance is actually better than assigning Scalars up front
RETURN
SELECT
FISCPER3,
CURR_PERD,
LAST_PERD,
CASE WHEN FISCPER3 <= (SELECT FISCPER3 FROM :PeriodTab WHERE LAST_PERD = 'X') THEN 'X' ELSE '' END AS YTD_L_PER,
CASE WHEN FISCPER3 <= (SELECT FISCPER3 FROM :PeriodTab WHERE CURR_PERD = 'X') THEN 'X' ELSE '' END AS YTD_C_PER
FROM :PeriodTab;
END;
SERIES_GENERATE_DATE
is used to supply a source list of dates which are processed for the attributes. The generation start and end Years are not important as long as the months align with the start and end months for the required Financial Year construct.0FISCVAR
and the function updated accordingly.0FISCYEAR
and 0FISCPER3
can be directly assigned.In the Output tab, all Navigation Attributes are then enabled for both FISCYEAR
and FISCPER3
.
With the Navigational Attributes now available in the Composite Provider, they can be added directly to any Queries used as BW Live data models in SAC. The new Nav Attributes will then become available as dimensions within the Live BW Model.
Note that for the flag based dimensions, short texts have been maintained in BW just for presentation purposes.
Because the Period flags have been added to FISCPER3
rather than FISCPER
, it is generally necessary to restrict on the Fiscal Year dimension also to avoid a selection spanning multiple years. However in some situations this also may be useful. For example, in the figure below the Financial Year filter has been intentionally omitted from the Restricted Measures. Instead, a breakdown by Year is performed indicating the year on year performance against budget for the same YTD time frame. Here a filter is applied at the Widget level, selecting the LAST, -02, -03, -04, -05, -06 & -07 relative years. This time window would also increment dynamically as the years rolled over.
KPI widgets for showing Current Period values are as simple as filtering the widget for Current Period = YES (or 'X') and Year Offset = CURR. Again the selection is dynamic and will roll forward automatically.
0FISCYEAR
and 0FISCPER3
already exist in the Composite Provider).You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
36 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 | |
4 | |
3 | |
3 |