Monthly Archives: August 2020

Uncategorized

Excel Activate Toolbox to add Buttons for macros/VBA

Leave a comment

Problem

You want to add a Button or another Control, like a TextBox or RadioButton to your Excel Worksheet to a specific cell.

Excel does not display the option to add a Button from the ControlBox and/or you cannot add it because the ControlBox’s Input Menu item is grayed out (disabled).

Solution

First, you have to add the menu band “Developer Tools” (in German “Entwicklertools”) to be visible. Just right click on the menu band on top of Excel, select Edit Menu band and check the “Developer Tools” option in the right list. See first screenshot.

Second, go to File | Options | Advanced | Scroll down to ~half of screen to the group “Displayed options for this workbook: (NAME OF YOU WORKBOOK)” and select “All” for “Displayed objects:” instead of “None (Hide objects)”. See second screenshot for more details.

Analysis Services

MDX: Tail

Leave a comment

To retrieve the last member of a set you can use the TAIL() function.

The following example returns the total turnover over the last member in 2020-02 for company 4711.

SELECT {
	TAIL(NONEMPTY([DatumStapel].[JMT].[T].Members, { ([Datenherkunft].[Datenherkunft].&[200], [Company].[Company].&[4711], [DatumStapel].[JMT].[M].&[2020]&[2]) }))
} on columns
, { [Measures].[Umsatz] } on rows
FROM [Finance]
Analysis Services

MDX: ParallelPeriod with CurrentMember

Leave a comment

Problem

You want to create a Calculated Member that displays the Totals of the previous year using ParallelPeriod.

Solution

See below for a Year- and at the end of the post for a Month-based version.
It is important to mention, that the Hierarchy used in ParallelPeriod function is the same Hierarchy used on an axis!
And another clue is, to use CurrentMember property on the hierarchy’s level and not below on a Sub-Level (here: J, M or T)!

WITH MEMBER [Measures].[UmsatzNegiertLY] AS
'
	([Measures].[Umsatz Negiert], 
	ParallelPeriod([DatumStapel].[JMT].[J]
		, 1
		,[DatumStapel].[JMT].CurrentMember)
	)
'
SELECT 
{ [DatumStapel].[JMT].[J].&[2019] : [DatumStapel].[JMT].[J].&[2020] } ON columns
,{
	[Measures].[Umsatz Negiert], 
	Measures.UmsatzNegiertLY } ON rows
FROM Finance


WITH MEMBER [Measures].[UmsatzNegiertLY] AS
'
	([Measures].[Umsatz Negiert], 
	ParallelPeriod([DatumStapel].[JMT].[M]
		, 12
		,[DatumStapel].[JMT].CurrentMember)
	)
'
SELECT 
{ [DatumStapel].[JMT].[M].&[2019]&[1] : [DatumStapel].[JMT].[M].&[2020]&[12] } ON columns
,{
	[Measures].[Umsatz Negiert], 
	Measures.UmsatzNegiertLY } ON rows
FROM Finance