I have a cube on which i have set dimension level security. I wish to do this
just to enable Visual Totals. Once i have enabled visual totals, i cannot see
this taking effect when i view this cube and use this particular dimension
via crystal analysis client.

I tested the dimension to restrict certain levels of the dimensio. This
works. So i can confirm that the role is coming into effect. But the visual
totals wont work.

I am using AS2000 with SQL 2000 and crystal analysis 10.

That's surprising, since I don't think that the client should be able to
override the Visual Totals security settings. Using the same cube data
and role, do Visual Totals work fine with another client like Excel? And
could you provide an example of what the data looks like when Visual
Totals doesn't work in Crystal 10 - I believe that this worked for my
colleagues, when we were using it.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hello Deepak,

When i view the data via excel using PTS, it by default does "Visual Totals"
for all dimensions. viz. the Grand Total field is always updated with the
total of members visible irrespective of what has been set in the role for
that dimension. So i am not sure if i am looking at it correctly.

In crystal Analysis, I have a product dimension, which has groups,
divisions, sub-divs etc. I have enabled visual totals on this dimension. When
i unselect a particular group or division, using member selector, the All
Products total remains unchanged.

I checked for other cubes too. The same thing is happening.

Anup

"Deepak Puri" wrote:

Hi Anup,

There is a distinction between enabling Visual Totals for dimension
security (which only totals members that the role has access to at the
all level), and the Visual Totals mode used by Excel, which is a
connection property. For Crystal Analysis to work like Excel, it would
have to set the Visual Totals mode for its connection to Analysis
Services.


The dimension security Visual Totals is explained here:

http://www.sqlserveranalysisservices...uctiontoDimens
ionSecurityinAnalysisServices2005.htm
...
Visual Totals

If a member of an attribute hierarchy is secured, one has two choices to
how data rolls up to the all member of the hierarchy:

- users see the true totals

- users see the totals of the data they are permitted to see.

This previous NG post describes the Visual Totals mode:

http://groups.google.com/group/micro...olap/msg/fe7f6
45622c760b0
What the paper calls 'Visual Totals Mode' is switched on by using the
'Default MDX Visual Mode' connection string property, and this is what
Excel
uses to calculate its subtotals.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,
I read both the links.

In my case, i have set the property in analysis services to show visual
totals(selected the check box for Enable visual Totals in dimension
security).
Inspite of this crystal Analysis does not show visual totals and goes
to the default of showing the actual true total. So Crystal Analysis is
not taking into consideration the settigns in the role that i created.
But it does take the role into consideration when i limit the number of
levels for this same dimension.

I tried enabling visual totals for various cubes and i seem to get the
same results with crystal analysis client.

You said that your colleagues were able to set it up at your end. Did
you do anythign special to enable visual totals for crystal analysis.

My final aim is a customised visual total. But i guess i am still some
time away from that.

Anup


Deepak Puri wrote:

Anup,

Maybe there is still some confusion on what happens when the Visual
Totals option for dimension security is enabled.

Since you have a Product dimension, suppose a role has access to Product
A and B, but is denied access to Product C. With Visual Totals enabled,
when you drop the product dimension on pivot table rows or columns, the
All Products total should only reflect Products A and B, since these are
the only permitted members. If Visual Totals were disabled, then the All
Products total would include Product C, even though direct access to it
is denied.

However, now if you manually deselect Product B, the All Products total
will still include Products A and B, unless the client tool connection
has the Visual Totals mode enabled. But as long as the Product C
contribution is never included in the All Products total, the security
Visual Totals option is working.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak,

I was able to implement dimension security and resolve my Visualtotals
problem, I removed the distant count from the cubes and it works fine now.
However, there is still an issue with a couple of the calculated members
grand total in Excel.

I'm not sure if I need to explicitly use Visual Totals function for the
calculated members. If so, how?

For instance, I have calculated member called Applicable Amount. It takes to
the (Total Sales Amount - Total Sales Credited) - Discount Amount.

In excel, the grand total is calculated $30K more than it should and I am
not sure why. In any ideas?

"Deepak Puri" wrote:

Are each of the 3: Total Sales Amount, Total Sales Credited and Discount
Amount, cube measures? And are the grand totals of each of these 3
measures correct, yet the calculation wrong?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

When I removed the distinct count I was able to the get the correct grand
total for some of the calculated members, except for 3 of them. I was
thinking that since using the dimension security and visual totals there must
be some hidden value that is calculating into the grand total but not sure
why.

"Deepak Puri" wrote: