Dynamic adjustment of Column Total

[Former Member]

Hi SAP Experts,

Problem with "TOTAL" Col. in the last & restriction of Time dim months display.

I have three categories - V1 & V2. V1 represents the budget for the full year whereas V2 represents 2nd half year (i.e. from July to Dec).

User will be generating report based on Profit Center & Account dimension in Row axis and Time & Category in the Col. axis. with last Col. being "Total".

User will be selecting either Category V1 or V2. In either of the cases, user wants only those specific Time DIM Members to appear and not all. For example, when user runs report for category V2, report should display only months starting July to Dec.

Is it possible to restrict reporting specific months based on Category? how to we dynamically move Total Col. in case if the user selects V2 which need to have only 6 Col.

Hope you understand my issue.

Thanks,

Subbu.

[former_member186338 · Accepted solution]

Hi Subbu,

Strange requirement - normal finance users will prefer fixed 12 months format and total as a last column.

You can create STARTMONTH property in CATEGORY dimension, get it after CATEGORY selection and using Excel formulas in a table generate Override for TIME dimension.

Vadim

[dinesh_veluru · Accepted solution]

Hi Subbu,

Agree with Vadim.

as a workaround you can try with the below option.

A7= category

A12= time from context assuming parent is coming from context

B1= time six periods(july to dec) please derive it as per the context 2015.07,2015.08,2015.09,2015.10,2015.11,2015.12

=IF(A7="V1",EPMDimensionOverride("000","TIME","BAS("&A12&")"),IF(A7="V2",EPMDimensionOverride("000","TIME",B1),A12))

in the edit report in column  axis for time dimension click on member sorting and grouping and create local member as per screenshot.

Thanks,

Dinesh.V

[former_member186338 · Accepted solution]

More universal approach:

Let's assume that CATEGORY dimension has property STARTMTH and A3 cell contains the page axis version member. Also B1 contains the year of report.

Then:

=SUBSTITUTE(MID("#.JAN,#.FEB,#.MAR,#.APR,#.MAY,#.JUN,#.JUL,#.AUG,#.SEP,#.OCT,#.NOV,#.DEC";(EPMMemberProperty(; EPMMemberID($A$3); "STARTMTH")-1)*6+1;200);"#";$B$1)

will do the job for 2013.JAN,... month format

Or:

=SUBSTITUTE(MID("#.01,#.02,#.03,#.04,#.05,#.06,#.07,#.08,#.09,#.10,#.11,#.12";(EPMMemberProperty(; EPMMemberID($A$3); "STARTMTH")-1)*5+1;200);"#";$B$1)

for 2013.01... month format

Result will be (for STARTMTH=7):

Vadim

P.S. The comma separated list of TIME members will be used in EPMDimensionOverride... Just attach &$B$1&".TOTAL"

P.P.S More compact formula:

=SUBSTITUTE(MID("#JAN,#FEB,#MAR,#APR,#MAY,#JUN,#JUL,#AUG,#SEP,#OCT,#NOV,#DEC";(EPMMemberProperty(; EPMMemberID($A$3); "STARTMTH")-1)*5+1;60);"#";$B$1&".")