0

Is it possible to return multiple member levels in multiple Excel columns? This would make it suitable for using as a pivot table data source.

For example:

Smart View returns data like this:

  Dec
  Nov
  Oct
Q4
  Sep
  Aug
  Jul
Q3

I would like to return data like this:

Q4 Dec
Q4 Nov
Q4 Oct
Q3 Sep
Q3 Aug
Q3 Jul

Because Q4 and Dec (for example) are levels from the same dimension, Smart View will not let you have them in multiple columns ("Member out of place").

Loconinja
  • 1
  • 1

2 Answers2

1

Unfortunately, you can't do that through Smart View.

A unpleasant solution would be creating an assisting dimension that reflects the Q1-Q4 relationship with the regular Period's level-0 members (Jan-Dec). It is not recommended since it will increase the number of blocks.

Period     Dummy Dim     <Data>
Jan        Q1             10
Feb        Q1             20
Mar        Q1             30
Apr        Q2             40
May        Q2             50
.          .              .
.          .              .
.          .              .
lovechillcool
  • 762
  • 2
  • 10
  • 32
0

Or, a better solution is, you can create a dynamic calc member (say A001) on a dense dimension, and write the formula as

IF(@ISMBR(Jan))
    A101->No_Location;
ELSEIF(@ISMBR(Feb))
    A102->No_Location;
.
.
.
ELSEIF(@ISMBR(Dec))
    A112->No_Location;
ENDIF

Where A101~A112 are supporting members where you store the text Q1,Q2,Q3,Q4 in a dedicated/static place (like No_Location). Then you put the dynamic calc member A001 onto any forms or ad hoc sheets.

lovechillcool
  • 762
  • 2
  • 10
  • 32