Report Design

[#1]

Hello Experts,

One of my report looks like below

Amount is the key-figure and variance is the calculated key figure. Actual and Budget01 are the versions.

I want the report structure to be

I did this by hiding the columns, change the heading (result to variance) and  also by changing the cell style.

But once i change the filter or refresh the data source all the hidden columns become unhidden, and all the other changes also returns to its original state. Kindly advise how to sort this out.

I tried hide totals but it removes the total of all the measures (amount and variance) but i need the total of variance.

Please let me know how to sort this out. Your help is much appreciated

Regards

Vicky

[D_Chia · Accepted solution]

looks like the data in the infoprovider is loaded by key figure value 'type'.

i am guessing right now your Query design has got just the characteristics 'Fiscal Period', 'Key Figures' (with components 'Amount' and 'Variance') and lastly 'Version' in the Columns section.

what you want can be achieved 2 ways:-

1.   define 3 restricted key figure columns

Label	R.KFIG definition
Amount Actual	Key figure = 'Amount' Characteristics      Version = 'Actual' (use the char value)
Budget01	Key figure = 'Amount' Characteristics      Version = 'Budget01' (use the char value)
Variance	Key figure = 'Variance' Characteristics      Version = 'Actual', 'Budget01' (use the char values)

this method will utilize existing record format and relies on query aggregation/summation only

2.  define first 2 restricted key figures as above and define a own 'Variance' column as a Formula/Calculated K.Figure => (Actual - Budget)

[Former Member · Accepted solution]

Thanks for your reply. This is what exactly I have done Chia.

I have created two R.KFIG, one for amount Actual.Amount and another for Budget.Amount.

And then created a variance (Calculated Key Figure)  which is Actual.Amount - Budget.Amount.

When I am running the report, I am getting the result as shown in the first picture.

[D_Chia · Accepted solution]

please share the Row/Column layout screenshot of your Query (you only showed the 'global' RKFIG and CALC.KFIG in previous reply)

[Former Member · Accepted solution]

[D_Chia · Accepted solution]

although you did create the 2 R.KFIG and 1 CALC.KFIG (on the left hand side of the query designer) ... you did not use it in the actual query's layout definition

remove the Key Figure structure n Version in your layout screenshot ... then incorporate those 3 elements on the left (Actual_Amount, Budget_Amount, Variance) into the Columns section

**Note:  turn on technical names if you are unsure what you dragged or currently have in the query design sections.

[Former Member · Accepted solution]

Thanks Chia. That is the mistake. Now instead of creating restricted key figure and calculated key figure, I just used a selection and Formula and worked perfectly. Thanks for spotting the mistake.

But doing the same will restrict me from entering any planned data, but that's fine , need to have a separate report for data entry.

Incase i am hiding some of the rows and columns  headers how can i save that (Highlighted in the below figure) . Whenever I am applying a filter or refresh, the hidden rows are coming back. Is there a way to restrict that. Thanks in advance.

Regards,

Sai

[D_Chia · Accepted solution]

unfortunately, i can't think of an 'easy' way to just 'suppress' the highlighted row items based on your current design.

2 ways i can think of on top of my head (if you intend to pursue it later) :-

• assuming you always only have finite periods eg. 2 periods in your example above ... define the key figure per period (based on variable for flexibility) ... then you can just have one label instead of a breakdown by period like now.
• use XLS macro script to hide the elements.  this requires VBA code and saving the workbook and deploying it to user (instead of just executing the query to get result)

[Former Member · Accepted solution]

Thanks Chia. I just did the VBA and it works good. Quite complicated to change the structure of the report.

My report structure is currently

GL Account (In rows) and Year, Measures (In Columns) and i am going to add YTD

GL Account

Key Figure

YTD

So i believe it would be good if it is like

Year, Measures (In Columns) and GL Account (In rows) and YTD

Key Figure

GL Account

YTD

So is it again i need to work on macros to achieve this

regards,

Sai

[D_Chia · Accepted solution]

for clarity, i suggest you open another question thread and furnish example with sample data (inclusive of the YTD scenario) to make it easier to visualize in total what you want to achieve.

[Former Member · Accepted solution]

Sure, I have done it. Thanks

[avinashd_m]

Hi Vicky,

Even after saving the workbook with the related changes if you are getting the all the hidden columns, then you might need to check the following settings in workbook.

Choose the Data source/Query exists in the workbook by following below path and then make sure that  there is no check for "Reset Data Source on Opening of the Workbook" setting.

Path : Analysis-->Display Design Panel--> Component Tab

[Former Member]

Thanks Avinash for the swift reply. But I have already unchecked this and the problem still persists