Pivot Table with "Faked Dimensions"

Pivot Table with "Faked Dimensions"

Post by Chris Lock » Fri, 21 Jun 2002 05:47:34



Thanks to those of you who helped me understand how to
fake a dimension for analytics; what a time saver and
design improvement.

I have two analytic dimensions in each of my cubes
(Analysis1 and Analysis2).  Both dimensions have one
member "Current".  In each cube I create calculated
members so the two dimensions wind up looking like:

Analytics1:
Current
MTD (month to date sub total)
YTD (year to date sub total)

Analytics2:
Current
Prior Year (Prior year function)

This design works like a champ in the cube browser.  When
I create a pivot table, I get weird results.

Basically, in a pivot table with the two fields in the
column position, both dimensions appear, but only
the "Current" fields are shown - the three calculated
measures will not display in the table.  When you click on
the selector for each dimension, the calculated members
appear with checks next to them, but again, the aren't
viewable in the pivot table.

If you move the dimensions to the Background position, you
are able to select the calculated measures and the data
does in fact correctly display.  Again, in the row or
column positions, these calculated measures don't display.

I am using a machine with Excel 2000 - is this the
problem?  I have another reporting tool (Intelligent Apps
for Excel) and that program is able to display the
calculated measures properly.  So it seems to be an excel
quirk or deficiency.

Anyone have any advice?

Thanks

Chris Locke

 
 
 

Pivot Table with "Faked Dimensions"

Post by Bluetoot » Fri, 21 Jun 2002 16:44:04


Quote:> Basically, in a pivot table with the two fields in the
> column position, both dimensions appear, but only
> the "Current" fields are shown - the three calculated
> measures will not display in the table.  When you click on
> the selector for each dimension, the calculated members
> appear with checks next to them, but again, the aren't
> viewable in the pivot table.

[SS] It probably depends on what your current Time slicer settings. Drop
your time dimension to page slicer area nd try to select different levels
(year, month etc). Your calc members list should change.

HTH
Szymon Slupik, CDN S.A.
Krakw, Poland

 
 
 

1. Error in Pivot table "#NUM"

Hi,

I created an excel file, when I copied this file into the
PC of the client and opened it to test security roles,
everything went ok, but when I tried to expand a Dimension
tree(to get a more details displayed in excel) the
measures were not calculated error was diplayed, the cells
contained #NUM instead of a value. When I drilled up or
put it back to less detail the values were calculated
properly....

I then created the file from the client PC and everything
was fine. the error only occurs when I take the file
already created and open it in the clients machine.

Thank you for your help
Suzete

2. Can't connect to SQL Server

3. Saving "fake" recordset

4. Controls in Grids

5. Table "dimension" problem

6. Runtime error 48 - error loading .DLL

7. max of ("...","...","..")

8. News update on the Tool for Oracle Application Developers.

9. "Pivot" Statement in SQL

10. "Hidden" Dimension

11. "Optional" Page Axis Dimension in MDX

12. "default" rows and columns dimensions