Displaying Yearly Totals in Layout

[Former Member]

Good Afternoon.

How can I display on the BPS layout a yearly value for data stored in the cube monthly?

For example -

Account Month Year Amt

123 Jan 2006 500

123 Feb 2006 400

123 Mar 2006 300

I want to display on the BPS Layout 1200 <u>without</u> showing the monthly values. The yearly total (1200 in example) is NOT stored in the cube.

Is this possible to display on the layout?

Thanks in advance for your reply!

Renee

[Former Member · Accepted solution]

Srini is spot on, if it's display only a reort is the best avenue, else mothly columns can be hidden in an excel layout via the standard excel hide columns functionality whilst configuring the layout- you just need to ensure that you deselect the bps 'auto column width' option.

But assuming you have a fiscal year characteristic in addition to a month the best option is just to build your layout on a level that does not include month, then your data will automatically aggregate to the annual figure.(If you only have the fisc year/period characteristic it would be worth considering throwing FiscYear into the data model with a char dev to populate, it can come in handy sometimes)

Another path that is taken occasionally is to maintain a special period (13-16 or even '#') as an annual total, calculating via fox as you come into your layout.

Cheers,

Anderw

[Former Member · Accepted solution]

I think you first have to determine what you want to do with the accumulated data:

1 Is it display only? If so the previous responses deal with this option

2 Does it need to be stored separately? Or used in calculations? One approach here is to have a time key like YD, which is calculated from the monthly values on a monthly time key.

3 Do you need to enter (plan) data for it? I don't believe so from your question. Clearly that would change the approach.

Regards

Stephen

[Former Member]

Thank you everyone for your replies....

The path I need to take is to add the months and store them in a month we do not use (13). Since the planning occurs at the month level and they need to see (display only) the yearly budget value, it's the only option that seems to work for me.

I did try to hide the columns, but the as soon as I save, they come back again. Maybe it's the version on BPS we are on.

Renee

[Former Member]

Hi Renee,

As I mentioned in the previous reply if you want to hide the columns you have to de-select the 'auto column width' option (revealed via one of the pushbuttons at top right of toolbar when configuring an excel layout).

As hiding a column in excel is technically just setting the column width to zero, The bps 'auto column width' feature will bring them back in runtime despite your best efforts to hide them in config.

Cheers

[Former Member]

Hi, one other consideration here is if the data is to be used in reports; will it be obvious to everyone that period 13 contains YTD data?

(As indicated earlier, we used YD timekey, but its not without its drawbacks).

Stephen

[Former Member]

Renee,

There are multiple ways you can go about it.If you are using excel layout, you can hide the month rows and show only the totals row. You can create totals row as suggested.

You can also create a new level without month and create the same layout.You would be able to see 1200 without months.But the question is do you want to plan by year not by month ? As any data you enter in this layout will be stored at the yearly level with period unassigned. If it is just for viewing only and not for planning, you could do this in report.

Hope this helps.

Cheers

Srini

[Former Member]

Hi,

U can create a new item in data column part of the layout and select the total option and make it like C(1): C(10) the column u want to add then the total value for all column will be displayed.

But how to hide the other column then total i donot know.

Thanks,

Debasish