Hi,

I am using Microsoft Data Analyzer and am using one of the "pre-built" MDX (offered by Data Analyzer) to find the %age.

The MDX statement for %age looks like below in Data Analyzer:

Length measure/sum(Members in filter for current dimension,Length measure)*100


I am trying to instead use a calculated measure on the server (for %age) rather than using Data Analyzer's solution - which is working fine and giving correct results but is slow.

Assuming that my Length measure is based on a measure called 'TotalSales' how do I convert the rest of the MDX; especially the following part:

"SUM(Members in filter for current dimension,Length measure)"


so that I can define a calculated members on my cube.

I hope my post is clear and will be very grateful for your help.

Many TIA.


************************************************** ********************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

Is this [% Total], using query axes, what you want:


http://www.tomchester.net/articlesdo...desamples.html
Flexible Percent of Total

Works with any set and any measure (assumes measure being operated upon
is in first column)

WITH MEMBER Measures.[% Total] AS

' ( StrToSet("Axis(1)").Item(0).Item(0).Dimension.Curr entMember, --
cur row mbr

StrToSet("Axis(0)").Item(0).Item(0) ) / --
1st measure on cols

( StrToSet("Axis(1)").Item(0).Item(0), --
assume 1st row=total

StrToSet("Axis(0)").Item(0).Item(0) ) ' , --
1st measure on cols

Format_String = '0%'


SELECT

{ [Store Sales] , [% Total] } ON AXIS(0),

{ Product.DefaultMember, [Product Family].Members} ON AXIS(1)

FROM Sales
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

HI deepak,

Thanks for the reply but this is not working.

Let me try to explain again:

The "built-in" solution offered by Data Analyzer is working just fine across all of my 6 dimension BUT it works locally and the reason why I cannot re-produce this solution on the server is becuase I am totally lost regarding how to convert the following part of Data Analyzer's solution ina compatable MDX form:

"SUM(Members in filter for current dimension,Length measure)"


The above line is the core of my question.

I am very much new to MDX but if I understand correctly even if your solution works, it will work via the Sample MDX application.... but I need it on the cube.

Am i making any sense here I hope so. Please let me know if I need to provide any additional details.

Thanks.



************************************************** ********************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

One more thing:

In Microsoft Data Analyzer, the "internal" syntax which it used for the following line

Members in filter for current dimension

IS

~Set:$$CurrentAspect:Selected~


Does the above provide you with any clue? Can this help you in helping me transform the following "Data Analyzer MDX"

FROM this:

Length measure/sum(Members in filter for current dimension,Length measure)*100

BTW, the internal syntax of the above in Data Analyzer is:

~Trait:Core.Length~/sum(~Set:$$CurrentAspect:Selected~,~Trait:Core.Len gth~)*100

into something like this maybe:

NetSales/SUM(?????????????,NetSales)*100


I can then make a calculated member, name it as Sales% and have the solution in the form of MDX on the cube.

Thanks.

************************************************** ********************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

HI,

sorry. One last thing i want to add:

Sobasically what I looking for is a MDX EXPRESSION which I can define on the cube(via the cube Editor) and NOT a MDX Statement.

I hope all of these bits and pieces that I have shared have hopefully made my request/post more clear.

Thanks.

************************************************** ********************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

The calculated member created in the MDX query can be defined at the
cube instead, if it does what you want.

There are some folks on this NewsGroup that are more familiar with Data
Analyzer internals than I am, so maybe they can help here. But can you
give an example of how the calculated member should work in a
conventional report?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Let me try to explain how this would work:

Suppose I have 3 dimensions - Time, Product (Levels are Type, SubType, and Name), and Customer (Levels are Type, Subtype, and Name)

Suppose I am at the ALL Time level. Now at the Customer Type level, this desired calcuated member will give me the %age sales across all times and for all products for each Customer Type. The same logic will apply to Product Type Level.

Suppose I am at the Product Sub Type Level and have selected Q1 of 1995 and one of the Customer Types. Now this desired calcuated member will show me the %age sales of all product subtypes for Q1 of 1995 and for the selected Customer type.

I hope the above 2 examples explain my requirement.

BTW, I will also really appreciate if I can come across anyone who has been using MIcrosoft Data Analyzer. It seems like there is no one using this tool [since I have never come across a post by anyone on Data Analyzer).

Thanks.

************************************************** ********************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...