Create a daily, weekly, monthly and quarterly average for all measures

hi,

What's the best way to create an average for all time grains across all measures?

At the moment, I've got a calculated measure for each measure, which itself uses two calculated measures:

Code Snippet

DayCount =
count(descendants([DATE TIME].[Calendar], ,LEAVES))

MonthCount =
count(descendants([DATE TIME].[Calendar], [DATE TIME].[Calendar].[Month]))

...

Code Snippet

SumSales =
sum([DATE TIME].[Calendar], [Sales])

SumUnits =
sum([DATE TIME].[Calendar], [Sales Units])

...

Code Snippet

AvgDailySales =
iif([Measures].[DayCount] = 0, null, [Measures].[SumSalesUnits] / [Measures].[DayCount])

AvgMonthlySales =
iif([Measures].[DayCount] = 0, null, [Measures].[SumSalesUnits] / [Measures].[MonthCount])

...

Thanks in advance,

If you search this forum on "AVERAGE" and "TIME" you'll find a bunch of solutions addressing this issue.

B.

|||

Hi mmmman,

For the days counting and using it for avarages caculations you should create additional measure group that use the time dimension table as fact table with a measure that counts days.

If you play futher, for examle to base your calculations on count of business days, set in fact table 0 for weekend days and 1 for work days then made a sum measure on it.

The proposed approach has following advatages: is multiselect aware, hasn't performance drawbacks.

More about day counting you cat take from Mosha's blog

http://www.sqljunkies.com/WebLog/mosha/archive/2007/05/27/counting_days_mdx.aspx

where another approaches are also described.

Create a daily, weekly, monthly and quarterly average for all measures

hi,

What's the best way to create an average for all time grains across all measures?

At the moment, I've got a calculated measure for each measure, which itself uses two calculated measures:

Code Snippet

DayCount =
count(descendants([DATE TIME].[Calendar], ,LEAVES))

MonthCount =
count(descendants([DATE TIME].[Calendar], [DATE TIME].[Calendar].[Month]))

...

Code Snippet

SumSales =
sum([DATE TIME].[Calendar], [Sales])

SumUnits =
sum([DATE TIME].[Calendar], [Sales Units])

...

Code Snippet

AvgDailySales =
iif([Measures].[DayCount] = 0, null, [Measures].[SumSalesUnits] / [Measures].[DayCount])

AvgMonthlySales =
iif([Measures].[DayCount] = 0, null, [Measures].[SumSalesUnits] / [Measures].[MonthCount])

...

Thanks in advance,

If you search this forum on "AVERAGE" and "TIME" you'll find a bunch of solutions addressing this issue.

B.

|||

Hi mmmman,

For the days counting and using it for avarages caculations you should create additional measure group that use the time dimension table as fact table with a measure that counts days.

If you play futher, for examle to base your calculations on count of business days, set in fact table 0 for weekend days and 1 for work days then made a sum measure on it.

The proposed approach has following advatages: is multiselect aware, hasn't performance drawbacks.

More about day counting you cat take from Mosha's blog

http://www.sqljunkies.com/WebLog/mosha/archive/2007/05/27/counting_days_mdx.aspx

where another approaches are also described.

Create a constant calculated member

Hi everyone, my question is related to creating a calculated member that must not change when the user changes the selected filter in the dimensions.
I will try to explain it: I need the total number of rows of my cube for a concrete year (year is a dimension). I will use this value in a calculated formula to define the percentage of rows that contains a concrete value (selected in the filter dimension). It means that my calculated member has a factor that change with the filter (ie. number of rows with value 'XXX'), divide by a factor that not change (total number of rows per year).

I have tried in many different ways, but I did not succeed. I cannot believe this is not possible...Maybe some sample code or DDL will help...

I keep thinking cement for some reason...|||Hi Brett, I understand your question since this is quite strange, nevertheless, I will try to explain.

The main reason is that I need to detect the quality of the data (used to join with the dimensions) in the fact table . The formula to do that is getting the rate of rows with the correct information. One way to do it will be just dividing the total number of rows by the number of rows with dimension column equals null.

I can calculate the total number of rows creating a calculated member like "count([mydimension].AllMembers)", but this number of rows is true only for the dimension "mydimension". Other possibility is using a column with value 1 for every row, and doing a sum([mydimension].AllMembers,mycolumn1), but this is true only when I filter for mydimension.

So, the real question is, how can I get the real total number of rows, valid for any dimension?.

I hope this helps...
thx in advance
Oscar

Create a calculated member on one measure.

I have a Version dimension which is used to break out the Net measure by 'Actual' or 'Budget'. Net is the only measure I'm using. By not using the Version dimsion. The net is incorrectly totaled by both values. Both are not used together.

'Actual' and 'Budget' are the only two members of the Version dimension. I want to add a third member which subtracts 'Budget' Net measure from the 'Actual' Net measure and call it variance. A caculated measure isn't working for me because obviously subtracting Actual - Budget doesn't work so well.

Can anyone help me with that?

First, you need to mark your Version attribute as Not Aggregatable (set IsAggregatable property to false) - this will prevent summing up Actual and Budget.

I am not sure why is it "obviously" not working well, the following should work well for you:

CREATE Version.Variance = Version.Actual - Version.Budget;

HTH,

Mosha (http://www.mosha.com/msolap)

|||Where do this statement go? Sorry, I'm new to this.|||You add this statement to the MDX Script of the cube. It is under the Calculation tab in the Cube window. You may want to switch to Script view from Form view in order to copy it verbatim.|||Thanks for your prompt response. So I should add a 'New Script'?|||

I created a new script and added this to it by dragging the objects to the form and substituting in "Variance", but it didn't work:

CREATE [Version Dim V].[Version].&[Variance] = [Version Dim V].[Version].&[Actual] - [Version Dim V].[Version].&[Budget];

MdxScript(X_GL_Data) (6, 133) Parser: The syntax for ';' is incorrect.

Any ideas?

Thanks!

|||

OK I got it to the point where there are no errors, but the Variance member will still not show up in the Version dimension. WHat could be the cause?

/*

The CALCULATE command controls the aggregation of leaf cells in the cube.

If the CALCULATE command is deleted or modified, the data within the cube is affected.

You should edit this command only if you manually specify how the cube is aggregated.

*/

CALCULATE;

CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version].[Variance]

AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Net Amount] },

VISIBLE = 1 ;

Thanks!

|||I see what it's doing. Its showing up in the Version_Heirarchy on the same level as 'All', not as a sibling of 'Budget' and 'Actual'. It's unusable there. How can I add it as a sibling?|||

First - please remove NON_EMPTY_BEHAVIOR - it is defined absolutely wrong.

Second, how exactly it doesn't show up ? If you connect with SSMS to the cube and browse the dimension - don't you see this calculated member ?

|||According to your original posting, you didn't want to have All to begin with. Therefore I advised you to set IsAggregatable=false - this way you won't have level/member All in the hierarchy.|||

At the time, 'all' was allowed. I didn't have it turned off. Everytime I try to set IsAggregatable to False for the Version_Key, Version_Description and the hierarchy, I get an error:

Error 1 Errors in the metadata manager. The 'Version Key' attribute with IsAggregatable=false must not have any relationship to 'Version Description' attribute that has AttributeHierarchyEnabled=true. 0 0

If I have AttributeHierarchyEnabled=False, I get two new errors:

Error 1 Dimension 'Version Dim V' > Attribute 'Version Key' : Attribute hierarchy must be enabled if attribute relationships are used. 0 0
Error 2 Dimension 'Version Dim V' > Attribute 'Version Key' : The key attribute requires attribute hierarchy enabled. 0 0

Version_Description is the only value I want to use. It contains 'Actual' and 'Budget', and should also include the 'Variance'

Thanks

|||

I finally got 'Variance' to show up with Actual and Budget, but when I try to apply it as a filter expression, I get the error below:

A set has been encountered that cannot contain calculated members.

The other two members of Version work fine.

Here is the setup:

Attribute:
Version Description: IsAggregatable=False, AttributeHierarchyEnabled=True

VersionKey: Key, AttributeHierarchyVisible=False, AtrributeRelationship to VersionDescription

Here is how the member is created:

CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version Hierarchy].[Variance]

AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Net Amount] },

VISIBLE = 1 ;

The relationship between dimension columns and measure group columns is VersionKey>VersioKey with a granularity attribute of 'version key'. Using Version Description as the granularity attribute fails indicating it can not find the Version_key attribute key.

Thanks

|||

Finally, I got it by changing the hierarchy to the Attribute hierarchy for VersionDescription, not the hierarchy with Version Description as the only attribute/level in the hierarchy pane.

Thanks for your help in walking me through it.

|||

Mike,

I have a very similiar problem, but didn't fully understand your solution. Can you explain how you have your dimension structured and what properties you have set again descriptively in one post? I'm a little lost trying to follow the thread.

Thanks,
-Nimitt

Create a calculated member on one measure.

I have a Version dimension which is used to break out the Net measure by 'Actual' or 'Budget'. Net is the only measure I'm using. By not using the Version dimsion. The net is incorrectly totaled by both values. Both are not used together.

'Actual' and 'Budget' are the only two members of the Version dimension. I want to add a third member which subtracts 'Budget' Net measure from the 'Actual' Net measure and call it variance. A caculated measure isn't working for me because obviously subtracting Actual - Budget doesn't work so well.

Can anyone help me with that?

First, you need to mark your Version attribute as Not Aggregatable (set IsAggregatable property to false) - this will prevent summing up Actual and Budget.

I am not sure why is it "obviously" not working well, the following should work well for you:

CREATE Version.Variance = Version.Actual - Version.Budget;

HTH,

Mosha (http://www.mosha.com/msolap)

|||Where do this statement go? Sorry, I'm new to this.|||You add this statement to the MDX Script of the cube. It is under the Calculation tab in the Cube window. You may want to switch to Script view from Form view in order to copy it verbatim.|||Thanks for your prompt response. So I should add a 'New Script'?|||

I created a new script and added this to it by dragging the objects to the form and substituting in "Variance", but it didn't work:

CREATE [Version Dim V].[Version].&[Variance] = [Version Dim V].[Version].&[Actual] - [Version Dim V].[Version].&[Budget];

MdxScript(X_GL_Data) (6, 133) Parser: The syntax for ';' is incorrect.

Any ideas?

Thanks!

|||

OK I got it to the point where there are no errors, but the Variance member will still not show up in the Version dimension. WHat could be the cause?

/*

The CALCULATE command controls the aggregation of leaf cells in the cube.

If the CALCULATE command is deleted or modified, the data within the cube is affected.

You should edit this command only if you manually specify how the cube is aggregated.

*/

CALCULATE;

CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version].[Variance]

AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Net Amount] },

VISIBLE = 1 ;

Thanks!

|||I see what it's doing. Its showing up in the Version_Heirarchy on the same level as 'All', not as a sibling of 'Budget' and 'Actual'. It's unusable there. How can I add it as a sibling?|||

First - please remove NON_EMPTY_BEHAVIOR - it is defined absolutely wrong.

Second, how exactly it doesn't show up ? If you connect with SSMS to the cube and browse the dimension - don't you see this calculated member ?

|||According to your original posting, you didn't want to have All to begin with. Therefore I advised you to set IsAggregatable=false - this way you won't have level/member All in the hierarchy.|||

At the time, 'all' was allowed. I didn't have it turned off. Everytime I try to set IsAggregatable to False for the Version_Key, Version_Description and the hierarchy, I get an error:

Error 1 Errors in the metadata manager. The 'Version Key' attribute with IsAggregatable=false must not have any relationship to 'Version Description' attribute that has AttributeHierarchyEnabled=true. 0 0

If I have AttributeHierarchyEnabled=False, I get two new errors:

Error 1 Dimension 'Version Dim V' > Attribute 'Version Key' : Attribute hierarchy must be enabled if attribute relationships are used. 0 0
Error 2 Dimension 'Version Dim V' > Attribute 'Version Key' : The key attribute requires attribute hierarchy enabled. 0 0

Version_Description is the only value I want to use. It contains 'Actual' and 'Budget', and should also include the 'Variance'

Thanks

|||

I finally got 'Variance' to show up with Actual and Budget, but when I try to apply it as a filter expression, I get the error below:

A set has been encountered that cannot contain calculated members.

The other two members of Version work fine.

Here is the setup:

Attribute:
Version Description: IsAggregatable=False, AttributeHierarchyEnabled=True

VersionKey: Key, AttributeHierarchyVisible=False, AtrributeRelationship to VersionDescription

Here is how the member is created:

CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version Hierarchy].[Variance]

AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Net Amount] },

VISIBLE = 1 ;

The relationship between dimension columns and measure group columns is VersionKey>VersioKey with a granularity attribute of 'version key'. Using Version Description as the granularity attribute fails indicating it can not find the Version_key attribute key.

Thanks

|||

Finally, I got it by changing the hierarchy to the Attribute hierarchy for VersionDescription, not the hierarchy with Version Description as the only attribute/level in the hierarchy pane.

Thanks for your help in walking me through it.

|||

Mike,

I have a very similiar problem, but didn't fully understand your solution. Can you explain how you have your dimension structured and what properties you have set again descriptively in one post? I'm a little lost trying to follow the thread.

Thanks,
-Nimitt

Create a calculated field that gives me the avg 75 percentile

I'm trying to create a calculated field that gives me the avg 75 percentile.

Right now I get this value by doing the following:

Create data set:

Select top 75 percent <field>

from <table>

Then I create the following calculated field

Avg(Fields!<field>.value,"<data_set_name>")

But I wanted to be able to create a calculated field that gives me the avg 75 percentile without creating a separate data set to get the top 75 percent Value.

Is it possible?

Thanks!

Either you can use the existing dataset and have a filter on it (Edit Dataset -> Filters tab) with Top %

OR

Write a custom function in report code (Report -> Report Properties -> Code) that will take an array of values and returns avg of top 75% from that array. To do that just call the function in your textbox expression as:

Code.AvgFunction(Fields!YourField.Value)

When you just sent Fields!YourField.Value, a whole array of values in that fields will be sent and you can receive it in an object array in your function. Then use any of the most optimized logics to calculate top 75% from that array and then calculate the average using any of the vb functions.

Shyam

|||

Shyam,

Thanks for your posting...

I'm having problems displaying correct data using TOP 75 %

I use the following data set:

select top 75 percent <field>

from <table>

order by <field>

My report is grouped by month Jan 2007, Feb 2007, etc...

If I select one month date range, for instance february the TOP 75 % returns 474 rows and that's correct, however if my date range includes other months, for instance range from 01/01/2007 thtough 02/28/2007 the Top 75 % results increases for the month of february and January. It will return 490 rows for february and more rows for January too.

My report only returns correct data if I select one month range.

What am I doing wrong?

Thanks, Susan

|||

Sorry for a delayed reply. The solution for your problem is simple.

Edit your group in the report and in the Filters tab and under Expression type "=Fields!YourField.Value" and under Operator select Top % and under Value type 75.

Shyam

|||

Shyam,

Thanks for your response.

Adding the filter above didn't make a difference. It's returning the same results.

when I display data for the month of February I get the following:

@.Month--Count of records--Accept Top 75%

Feb 2007 --498--231.79

when I run for the month of February and January I get the following:

@.Month--Count of records--Accept Top 75%

Feb 2007--512--260.46

Jan 2007--565--309.43

and so fourth, here I added december:

@.Month--Count of records--Accept Top 75%

February-2007-- 508-- 252.29

January-2007-- 560-- 300.59

December-2006-- 488-- 258.64

I expected results for February to remain the same when add months to my view.

Thanks, Susan

|||Remove the TOP 75% clause from your SQL query and have them only in group filters.|||

Shyam,

I had tried that before but the Top% in the Filter tab of the group does't seem to work. Whem I remove the top 75 percent from the sql statement and just leave the Top% =75 filter it totally ignores the filter and it displays the total row count and total average instead.

Well, thanks for the help.... :-)

|||

Top 75% of feb month alone is always going to be different from top 75% of both jan and feb combined. So first of all, you have to remove the top 75% from your sql query. Then make sure you are adding the filter on the group on the same field which you had used in your query. Also, try to convert the field to string using CStr function in the filter expression.

Shyam

|||

Shyam,

Thanks for all your help so far.

I noticed that the filter works based on the row count of the group it's in. My Month group only returns on row, that's why the filter doesn't seem to work, because it's trying to return 75% of one row, which is one row. If I put the filter in the detail row of the report it works, but the problem is that I have to group the detail row before the filter can work, if I group the detail row I won't return all the detail data I need. Is there a way to make the month group filter look at the field of the detail row?

Also, in the report I have to use Bottom% =75 which is the equivalent of the SQL - top 75 percent.

|||

You can use any field from the dataset that is bound to the table for filyering in your group. Using the appropriate field for filtering also makes a big difference in the results obtained.

Shyam

Create a calculated field that gives me the avg 75 percentile

I'm trying to create a calculated field that gives me the avg 75 percentile.

Right now I get this value by doing the following:

Create data set:

Select top 75 percent <field>

from <table>

Then I create the following calculated field

Avg(Fields!<field>.value,"<data_set_name>")

But I wanted to be able to create a calculated field that gives me the avg 75 percentile without creating a separate data set to get the top 75 percent Value.

Is it possible?

Thanks!

Either you can use the existing dataset and have a filter on it (Edit Dataset -> Filters tab) with Top %

OR

Write a custom function in report code (Report -> Report Properties -> Code) that will take an array of values and returns avg of top 75% from that array. To do that just call the function in your textbox expression as:

Code.AvgFunction(Fields!YourField.Value)

When you just sent Fields!YourField.Value, a whole array of values in that fields will be sent and you can receive it in an object array in your function. Then use any of the most optimized logics to calculate top 75% from that array and then calculate the average using any of the vb functions.

Shyam

|||

Shyam,

Thanks for your posting...

I'm having problems displaying correct data using TOP 75 %

I use the following data set:

select top 75 percent <field>

from <table>

order by <field>

My report is grouped by month Jan 2007, Feb 2007, etc...

If I select one month date range, for instance february the TOP 75 % returns 474 rows and that's correct, however if my date range includes other months, for instance range from 01/01/2007 thtough 02/28/2007 the Top 75 % results increases for the month of february and January. It will return 490 rows for february and more rows for January too.

My report only returns correct data if I select one month range.

What am I doing wrong?

Thanks, Susan

|||

Sorry for a delayed reply. The solution for your problem is simple.

Edit your group in the report and in the Filters tab and under Expression type "=Fields!YourField.Value" and under Operator select Top % and under Value type 75.

Shyam

|||

Shyam,

Thanks for your response.

Adding the filter above didn't make a difference. It's returning the same results.

when I display data for the month of February I get the following:

@.Month--Count of records--Accept Top 75%

Feb 2007 --498--231.79

when I run for the month of February and January I get the following:

@.Month--Count of records--Accept Top 75%

Feb 2007--512--260.46

Jan 2007--565--309.43

and so fourth, here I added december:

@.Month--Count of records--Accept Top 75%

February-2007-- 508-- 252.29

January-2007-- 560-- 300.59

December-2006-- 488-- 258.64

I expected results for February to remain the same when add months to my view.

Thanks, Susan

|||Remove the TOP 75% clause from your SQL query and have them only in group filters.|||

Shyam,

I had tried that before but the Top% in the Filter tab of the group does't seem to work. Whem I remove the top 75 percent from the sql statement and just leave the Top% =75 filter it totally ignores the filter and it displays the total row count and total average instead.

Well, thanks for the help.... :-)

|||

Top 75% of feb month alone is always going to be different from top 75% of both jan and feb combined. So first of all, you have to remove the top 75% from your sql query. Then make sure you are adding the filter on the group on the same field which you had used in your query. Also, try to convert the field to string using CStr function in the filter expression.

Shyam

|||

Shyam,

Thanks for all your help so far.

I noticed that the filter works based on the row count of the group it's in. My Month group only returns on row, that's why the filter doesn't seem to work, because it's trying to return 75% of one row, which is one row. If I put the filter in the detail row of the report it works, but the problem is that I have to group the detail row before the filter can work, if I group the detail row I won't return all the detail data I need. Is there a way to make the month group filter look at the field of the detail row?

Also, in the report I have to use Bottom% =75 which is the equivalent of the SQL - top 75 percent.

|||

You can use any field from the dataset that is bound to the table for filyering in your group. Using the appropriate field for filtering also makes a big difference in the results obtained.

Shyam

sql

create a "Yesterday", "This Week", "Last Week" calculated member

Hi all,

I'd like to add a yesterday dimension member to a new dimension, like a "Time Utility" dimension, that references the second last day of non empty data in a cube.

At the moment, I'm doing this:

Code Snippet

create member [MIA DW].[DATE TIME].[Date].[Yesterday]
as [DATE TIME].[Date].&[2007-01-01T00:00:00]

select [Measures].members on 0,
non empty [DATE TIME].[Date].members on 1
from [MIA DW]

But the [yesterday] member does not seem to belong to [DATE TIME].[Date].members?

So I guess there's two questions:

1) Can I have a new empty dimension which contains all these special members like "Yesterday" or "This Week" and "Last Week" (these last two obviously refer to a set of Dates)

2)How come the Yesterday member is not returned by the .members function?

Thanks

Greg
1.

Yes. In the DataSourceView add a named column called SpecialDates with the value 'Normal'. Then add that column as an attribute in the time dimension, and then create the calculated members on that dimensionattribute. Another possibility is to create a Named Set with the same functionality.

2.
i think calculated members are not returned by .MEMBERS. This will work I think

create member [MIA DW].[DATE TIME].[Date].[Yesterday]
as [DATE TIME].[Date].&[2007-01-01T00:00:00]

select [Measures].members on 0,
non empty ADDCALCULATEDMEMBERS([DATE TIME].[Date].members) on 1
from [MIA DW]
|||Awesome thanks.

Counting members (with calculated members?)

Hi,
I'm building a cube to analyze Job entries, using Targit as a front-end.
After very disappointing hours of trying hopefully one of you know to tackle
my problem.
My fact table (simplified):
Date, Job#, Customer#, Resource#, Hours Spent
And some linked tables for CustomerName/Group, ResouceName
Dimensions are:
Job#
Region, Customer
Customer
Time (Y-Q-M-D)
Summing the quantity of hours spent can easily be done by a measure.
BUT now my problem:
I would like to have a count of the different jobs and the number of custome
rs and number of different resources.
When the end user is creating a pivot table with Region en time on the axis,
the count of resources should reflect on the crossings. And I really don't
know how to create the correct MDX for the calculated member in the cube.
E.G.
Date, job, resource, customer, hours
04-02-13, 1, A, CustX, 8
04-02-14, 1, B, CustX, 8
05-01-01, 1, C, CustZ, 8
Pivot on Year And Resource with count of resources should give
A B C
2004 1 1 0
2005 0 0 1
All 1 1 1
While a pivot on Year and Customer
CustX CustZ
2004 2 0
2005 0 1
All 2 1
Please helpF.Y.I.
Take a look at Analysis Services: DISTINCT COUNT, Basket Analysis, and
Solving the Multiple Selection of Members Problem
http://msdn.microsoft.com/library/d...r />
inct2.asp
"Marcel" wrote:

> Hi,
> I'm building a cube to analyze Job entries, using Targit as a front-end.
> After very disappointing hours of trying hopefully one of you know to tack
le my problem.
> My fact table (simplified):
> Date, Job#, Customer#, Resource#, Hours Spent
> And some linked tables for CustomerName/Group, ResouceName
> Dimensions are:
> Job#
> Region, Customer
> Customer
> Time (Y-Q-M-D)
> Summing the quantity of hours spent can easily be done by a measure.
> BUT now my problem:
> I would like to have a count of the different jobs and the number of custo
mers and number of different resources.
> When the end user is creating a pivot table with Region en time on the axi
s, the count of resources should reflect on the crossings. And I really don'
t know how to create the correct MDX for the calculated member in the cube.
> E.G.
> Date, job, resource, customer, hours
> 04-02-13, 1, A, CustX, 8
> 04-02-14, 1, B, CustX, 8
> 05-01-01, 1, C, CustZ, 8
> Pivot on Year And Resource with count of resources should give
> A B C
> 2004 1 1 0
> 2005 0 0 1
> All 1 1 1
> While a pivot on Year and Customer
> CustX CustZ
> 2004 2 0
> 2005 0 1
> All 2 1
> Please help
>

Counting members (with calculated members?)

Hi,
I'm building a cube to analyze Job entries, using Targit as a front-end.
After very disappointing hours of trying hopefully one of you know to tackle my problem.
My fact table (simplified):
Date, Job#, Customer#, Resource#, Hours Spent
And some linked tables for CustomerName/Group, ResouceName
Dimensions are:
Job#
Region, Customer
Customer
Time (Y-Q-M-D)
Summing the quantity of hours spent can easily be done by a measure.
BUT now my problem:
I would like to have a count of the different jobs and the number of customers and number of different resources.
When the end user is creating a pivot table with Region en time on the axis, the count of resources should reflect on the crossings. And I really don't know how to create the correct MDX for the calculated member in the cube.
E.G.
Date, job, resource, customer, hours
04-02-13, 1, A, CustX, 8
04-02-14, 1, B, CustX, 8
05-01-01, 1, C, CustZ, 8
Pivot on Year And Resource with count of resources should give
A B C
2004 1 1 0
2005 0 0 1
All 1 1 1
While a pivot on Year and Customer
CustX CustZ
2004 2 0
2005 0 1
All 2 1
Please help
F.Y.I.
Take a look at Analysis Services: DISTINCT COUNT, Basket Analysis, and
Solving the Multiple Selection of Members Problem
http://msdn.microsoft.com/library/de.../distinct2.asp
"Marcel" wrote:

> Hi,
> I'm building a cube to analyze Job entries, using Targit as a front-end.
> After very disappointing hours of trying hopefully one of you know to tackle my problem.
> My fact table (simplified):
> Date, Job#, Customer#, Resource#, Hours Spent
> And some linked tables for CustomerName/Group, ResouceName
> Dimensions are:
> Job#
> Region, Customer
> Customer
> Time (Y-Q-M-D)
> Summing the quantity of hours spent can easily be done by a measure.
> BUT now my problem:
> I would like to have a count of the different jobs and the number of customers and number of different resources.
> When the end user is creating a pivot table with Region en time on the axis, the count of resources should reflect on the crossings. And I really don't know how to create the correct MDX for the calculated member in the cube.
> E.G.
> Date, job, resource, customer, hours
> 04-02-13, 1, A, CustX, 8
> 04-02-14, 1, B, CustX, 8
> 05-01-01, 1, C, CustZ, 8
> Pivot on Year And Resource with count of resources should give
> A B C
> 2004 1 1 0
> 2005 0 0 1
> All 1 1 1
> While a pivot on Year and Customer
> CustX CustZ
> 2004 2 0
> 2005 0 1
> All 2 1
> Please help
>