Comments on: Rolling Forecast reporting in Excel

By: xlcubedblog

xlcubedblog — Mon, 19 Aug 2013 15:30:31 +0000

Hi, the best bet may be to contact XLCubed Support directly at support@xlcubed.com although a number of users at Santander are already doing this, have you talked to your BI Team?

Thanks.

By: jalal

jalal — Mon, 19 Aug 2013 15:27:12 +0000

I am looking for write back instructions for uploading large data into xlcube? plz assist?

By: arno

arno — Wed, 17 Apr 2013 15:26:49 +0000

Perfect! See this site for details: http://www.xlcubed.com/help/mdx%3A

Is there a list of functions like MDX/MDXSET? I could find it only because I knew what I was looking for, is there some documentation?

thank you
arno

By: xlcubedblog

xlcubedblog — Tue, 16 Apr 2013 19:38:00 +0000

Hi Arno,

Not sure what version you are running but in 7.2, once you are driving the exclusion from a range, you can use MDXSET: to give the MDX to use, for example:

MDXSET: {[Product].[Product Categories].[All Products].Children}

Hope that helps, if you have trouble getting it working please let us know.

Thanks

By: arno

arno — Tue, 16 Apr 2013 14:14:15 +0000

at the end of my post I mean with “result” the selected month of the slicer (unique name)

By: arno

arno — Tue, 16 Apr 2013 14:04:39 +0000

This is a nice workaround. It would be a lot nicer to make the “Exclude from Display” dialog programmable as all (!) other dialogs are that deal with member selections. I mean, allow sets {}, manual mdx… for excluding members, too. This should be possible, here I am assuming that November 2005 is coming from a slicer, the rest is just mdx, not very beautiful but demonstrating my point:

Select { } On Columns,

Except (
( {[Scenario].&[Actual],[Scenario].&[Budget]}
* {Descendants([Time].[Year].&[2005-01-01T00:00:00],[Time].[Month],SELF)})

,{([Scenario].&[Budget],[Time].[Year].&[2005-01-01T00:00:00].FirstChild.FirstChild:[Time].[Month].&[2005-11-01T00:00:00])
,([Scenario].&[Actual],[Time].[Month].&[2005-12-01T00:00:00]:[Time].[Year].&[2005-01-01T00:00:00].LastChild.LastChild)}
) On Rows
From [Sales] Cell Properties VALUE

Result:
Actual January 2005
Actual February 2005
Actual March 2005
Actual April 2005
Actual May 2005
Actual June 2005
Actual July 2005
Actual August 2005
Actual September 2005
Actual October 2005
Actual November 2005
Budget December 2005

There is not much Excel required here, simply write the result eg. into A1 and use one textformula to create the “manual mdx” eg in B1 to exclude all Budgets from January to November:
= “([Scenario].&[Budget],[Time].[Year].&[2005-01-01T00:00:00].FirstChild.FirstChild:”&A1&”)”

I have trouble with the exclude dialog that could be simply solved by accepting eg. “SomeMember.Children”.

regards
arno