Cube functions: Does Slicer Name selection only work hardcoded?

Hi there,

I'm looking for a way to dymically pass on the selected slicer name from a cell in Excel on to a cube function:

CUBERANKEDMEMBER("ThisWorkbookDataModel";Slicer_KSt;COLUMN(A1))

As it is possible to pass variables to the mdx-strings with "&..ExcelCellReference..&", this doesn't seem to work with slicer identifications. They simply appear as textstrings in the formular and work as CUBESETS if hardcoded, but all aproaches I've tested so far in order to fetch that value from a cell in excel fail (cell references of any sort, named range: mostly return #NV).

Any idea would be gratefully appreciated!

Thanks

February 24th, 2015 5:02am

I'm not sure whether I've properly understood the question, but usually the result from a slicer can easily be used in a CUBEVALUE-Formula - ist that what you are looking for?

I usually use, as you do, CUBERANKEDMEMBER for retrieving the element from the slicer and "reference" it into a cell. I later do use the cell for other CUBEVALUE-formulas.

Free Windows Admin Tool Kit Click here and download it now
February 25th, 2015 4:34am

Hi Bjrn,

thanks for the reply.

Yes, retrieving the values from the first slicer is my first step. If the results of this step would be attribute values that i.e. would go into a formular like this, everything would be fine, reference to cell C23 would work fine:

=CUBEMEMBER("ThisWorkbookDataModel";"[DIM_DatumEVT].[Monat].&["&C23&"]")

However, what I'm trying to achieve is to pass it on to a formula like this:

=CUBERANKEDMEMBER("ThisWorkbookDataModel";Slicer_Titel;COLUMN(A21))

Where "Slicer_Titel" is actually the retrieved value from the 1st slicer selection.

This is sort of a double slice for dynamic charting: 1st slicer selects which attribute to be shown, but I don't wanna show all values of it, so give the user the chance in a 2nd(-level) slicer to further reduce the values to be shown. So I try to dynamically pass on which 2nd-level slicer to select.

February 25th, 2015 5:00am

"Where "Slicer_Titel" is actually the retrieved value from the 1st slicer selection."

I don't know, whether it works they way you try it. If you insert a slicer-element into a workbook it gets a unique name (e.g. "SlicerDimProduct"). Can you reference that unique name?

Maybe also try to use the 1st element in the range and not a COLUMN()-formula to determine the element. As far as I remember, the CUBERANKEDMEMBER-formula sometimes even returns #NA, if the formula is correct, but no n-th member of the range is existing.

To me it sounds that you tried to build a hierarchy with 2 slicers. For SSAS-Cubes, the slicer directly refer to each other (if you select the hierarchy to be added via slicer, you directly get 2 slicers with both have "kind of" the same ID). To be honest, I don't know much about PowerPivot/Data Models in Excel, but with SSAS the slicer could basically do everything I wanted it to do. :)


  • Edited by Bjoern25 3 hours 17 minutes ago
Free Windows Admin Tool Kit Click here and download it now
February 27th, 2015 3:35am

"I don't know, whether it works they way you try it. If you insert a slicer-element into a workbook it gets a unique name (e.g. "SlicerDimProduct"). Can you reference that unique name? "

-- Here lies the challenge: I don't want to "hardcode" "SlicerDimProduct" into my formula, but sth like: "&G1&" where the textstring in G1 is determined by the selection of my 1st slicer. Also tried to give cell G1 a name ("SelectedSlicer") but this didn't help either. It's basically about first extracting a textstring from the slicer selection which should then be identified by the cubeformula to be the Cubeset (created by a slicer called whats been selected before).

I have a workaround solution for it but it would be much more elegant to be able to (indrectly) reference it just as it's possible with the other MDX-coordinates. 

"Maybe also try to use the 1st element in the range and not a COLUMN()-formula to determine the element. As far as I remember, the CUBERANKEDMEMBER-formula sometimes even returns #NA, if the formula is correct, but no n-th member of the range is existing."

-- Good hint, but here it didn't help

February 27th, 2015 4:12am

"Where "Slicer_Titel" is actually the retrieved value from the 1st slicer selection."

I don't know, whether it works they way you try it. If you insert a slicer-element into a workbook it gets a unique name (e.g. "SlicerDimProduct"). Can you reference that unique name?

Maybe also try to use the 1st element in the range and not a COLUMN()-formula to determine the element. As far as I remember, the CUBERANKEDMEMBER-formula sometimes even returns #NA, if the formula is correct, but no n-th member of the range is existing.

To me it sounds that you tried to build a hierarchy with 2 slicers. For SSAS-Cubes, the slicer directly refer to each other (if you select the hierarchy to be added via slicer, you directly get 2 slicers with both have "kind of" the same ID). To be honest, I don't know much about PowerPivot/Data Models in Excel, but with SSAS the slicer could basically do everything I wanted it to do. :)


  • Edited by Bjoern25 Friday, February 27, 2015 8:37 AM
Free Windows Admin Tool Kit Click here and download it now
February 27th, 2015 8:35am

"Where "Slicer_Titel" is actually the retrieved value from the 1st slicer selection."

I don't know, whether it works they way you try it. If you insert a slicer-element into a workbook it gets a unique name (e.g. "SlicerDimProduct"). Can you reference that unique name?

Maybe also try to use the 1st element in the range and not a COLUMN()-formula to determine the element. As far as I remember, the CUBERANKEDMEMBER-formula sometimes even returns #NA, if the formula is correct, but no n-th member of the range is existing.

To me it sounds that you tried to build a hierarchy with 2 slicers. For SSAS-Cubes, the slicer directly refer to each other (if you select the hierarchy to be added via slicer, you directly get 2 slicers with both have "kind of" the same ID). To be honest, I don't know much about PowerPivot/Data Models in Excel, but with SSAS the slicer could basically do everything I wanted it to do. :)


  • Edited by Bjoern25 Friday, February 27, 2015 8:37 AM
February 27th, 2015 11:35am

This is my latest workaround:

In an empty cell (A2), simply refer to the slicer ("=SlicerName"). The field will look empty, but actually contains the slicer selection

Refer to it like this:

CUBERANKEDMEMBER("DataModel", "&A2&", 1)

For dynamic slicer selections in CUBEVALUE-Formula write the full cSet-definition with the "&A2&"-reference instead of the slicer name:

CUBEVALUE("DataModel", ReferenceToValueField, SlicerName1(fix), CUBESET("DataModel", "&A2&"))

Not so nice, but does the job: dynamically selecting which slicer to use in the cuberankemember or cubevalue formulas.

Will "close" this by marking as answered.

Free Windows Admin Tool Kit Click here and download it now
March 16th, 2015 6:54am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics