MS SQL Server OLAP - Visual Totals in AS2000

Author

Visual Totals in AS2000

Jim_OLAP

2006-03-31, 8:26 pm

All,

Does anyone know of an issue using VISUALTOTALS() with a calculated measures
that contains a LOOKUPCUBE function?

e.g

with

Member Measures.[LookSales] as 'Lookupcube([example]....)'

select
{[LookSales]} on columns,
visualtotals({[Store].members}) on rows
from mycube

Deepak Puri

2006-03-31, 8:26 pm

Maybe the issue is that LookupCube() evaluates an expression in the
context of another cube, whereas VisualTotals() effectively creates
calculated members on a dimension in the context of the current member.
Setting up a virtual cube instead of using LookupCube() may be the
cleanest option (assuming you're working with AS 2000).

However, if you need to use, maybe this Foodmart sample query on the
Warehouse cube helps - it applies VisualTotals() separately to the
current (Warehouse) and referenced (Sales) cubes:
[color=darkred]
with

Member [Measures].[LookSales] as
'LookupCube("Sales",
"([Measures].[Unit Sales], Filter(VisualTotals(
"
+ SetToStr(StrToSet("Axis(1)")) + "), [Store].CurrentMember is
[Store].["
+ [Store].CurrentMember.Name + "]).Item(0).Item(0))")'

select
{[Measures].[Units Shipped], [Measures].[LookSales]} on columns,
Non Empty VisualTotals(Except(
Descendants([Store].[All Stores].[USA]),
{Descendants([Store].[All Stores].[USA].[WA]),
Descendants([Store].[All Stores].[USA].[OR].[Salem])})) on rows
from Warehouse[color=dark
red]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Sponsored Links