Help with nonempty crossjoin....

Hello...

i have made a calculated measure named [Location Count] that has this expression:

NONEMPTYCROSSJOIN(descendants([dimTargetSet].currentmember, 9, LEAVES),descendants([dimLocation].currentmember,9,LEAVES)).count

In AS it seems to be working but when I call the measure in my query in reporting services it always returns zero. what am i doing wrong here?...

WITH SET [Locations] AS '{[dimlocation].&[2583]}' MEMBER [Measures].[Section Count] AS '[dimQuestionSection].&[323].children.count' MEMBER [dimSurveyQuestion].[Audits] AS 'Aggregate ({[dimSurveyQuestion].&[323]})' SELECT {[Measures].[Location Count]} ON COLUMNS, {FILTER(CROSSJOIN(DESCENDANTS([Locations],1,LEAVES), DESCENDANTS([dimQuestionSection].&[323],1)), [Measures].[Last Audited Date]>0)} ON ROWS FROM cubeAuditTargetSet WHERE ([dimSurveyQuestion].[Audits], [dimDate].&[2007], [dimUser].&[323], [dimTargetSet].&[323].&[9])

Could be because you're using a calculated member: [dimSurveyQuestion].[Audits] in the where clause. Does it work if you replace it with the actual member?|||

thanks for your reply... actually i need to use aggregate with [dimSurveyQuestion].[Audits] because there are instance we search for three members of the dimension...

MEMBER [dimSurveyQuestion].[Audits] AS 'Aggregate ({[dimSurveyQuestion].&[323].&[1], dimSurveyQuestion].&[323].&[2], dimSurveyQuestion].&[323].&[3]})'

any idea?... thanks...

Help with nonempty crossjoin....

Hello...

i have made a calculated measure named [Location Count] that has this expression:

NONEMPTYCROSSJOIN(descendants([dimTargetSet].currentmember, 9, LEAVES),descendants([dimLocation].currentmember,9,LEAVES)).count

In AS it seems to be working but when I call the measure in my query in reporting services it always returns zero. what am i doing wrong here?...

WITH SET [Locations] AS '{[dimlocation].&[2583]}' MEMBER [Measures].[Section Count] AS '[dimQuestionSection].&[323].children.count' MEMBER [dimSurveyQuestion].[Audits] AS 'Aggregate ({[dimSurveyQuestion].&[323]})' SELECT {[Measures].[Location Count]} ON COLUMNS, {FILTER(CROSSJOIN(DESCENDANTS([Locations],1,LEAVES), DESCENDANTS([dimQuestionSection].&[323],1)), [Measures].[Last Audited Date]>0)} ON ROWS FROM cubeAuditTargetSet WHERE ([dimSurveyQuestion].[Audits], [dimDate].&[2007], [dimUser].&[323], [dimTargetSet].&[323].&[9])

Could be because you're using a calculated member: [dimSurveyQuestion].[Audits] in the where clause. Does it work if you replace it with the actual member?|||

thanks for your reply... actually i need to use aggregate with [dimSurveyQuestion].[Audits] because there are instance we search for three members of the dimension...

MEMBER [dimSurveyQuestion].[Audits] AS 'Aggregate ({[dimSurveyQuestion].&[323].&[1], dimSurveyQuestion].&[323].&[2], dimSurveyQuestion].&[323].&[3]})'

any idea?... thanks...

Help with measure aggregation functions

Hi, I will rewrite my question.
I'm having trouble to show averages of a measure in a cube, where the normal
aggregation function for a measure is SUM.
I see no AVG aggregation function for measures (I see Min, Max, Count,
Distinct Count and SUM).
If I hide the measure (cost), and create a calculated member based on that
measure, ie Avg(cost), I have te problem of how to average it, since the
cube has two dimensions in the row axis, like:
Time
Product |
Customer | avg of cost
If I use avg(nonemptycrossjoin(product.currentmember.childr en,
customer.currentmember.children), measures.cost) I get the same average for
every cell in the cube, what's not corrrect...
Sorry to bother you all, but this thing is becoming a nightmare.
Hope you can help
Michael Prendergast
Averages are usually handled by summing and counting...and then
dividing the sub by the count in a calculated member.
MPS wrote:
> Hi, I will rewrite my question.
> I'm having trouble to show averages of a measure in a cube, where the
normal
> aggregation function for a measure is SUM.
> I see no AVG aggregation function for measures (I see Min, Max,
Count,
> Distinct Count and SUM).
> If I hide the measure (cost), and create a calculated member based on
that
> measure, ie Avg(cost), I have te problem of how to average it, since
the
> cube has two dimensions in the row axis, like:
> Time
> Product |
> Customer | avg of cost
> If I use avg(nonemptycrossjoin(product.currentmember.childr en,
> customer.currentmember.children), measures.cost) I get the same
average for
> every cell in the cube, what's not corrrect...
> Sorry to bother you all, but this thing is becoming a nightmare.
> Hope you can help
> Michael Prendergast
|||Sometimes, getting back to basics gets the job done
Thank yo very much, problem solved
Michael
"OLAPMonkey" <jjanke@.spss.com> escribi en el mensaje
news:1112202692.231562.128020@.g14g2000cwa.googlegr oups.com...
> Averages are usually handled by summing and counting...and then
> dividing the sub by the count in a calculated member.
> MPS wrote:
> normal
> Count,
> that
> the
> average for
>

Help with measure aggregation functions

Hi, I will rewrite my question.
I'm having trouble to show averages of a measure in a cube, where the normal
aggregation function for a measure is SUM.
I see no AVG aggregation function for measures (I see Min, Max, Count,
Distinct Count and SUM).
If I hide the measure (cost), and create a calculated member based on that
measure, ie Avg(cost), I have te problem of how to average it, since the
cube has two dimensions in the row axis, like:
Time
---
Product |
Customer | avg of cost
If I use avg(nonemptycrossjoin(product.currentmember.children,
customer.currentmember.children), measures.cost) I get the same average for
every cell in the cube, what's not corrrect...
Sorry to bother you all, but this thing is becoming a nightmare.
Hope you can help
Michael PrendergastAverages are usually handled by summing and counting...and then
dividing the sub by the count in a calculated member.
MPS wrote:
> Hi, I will rewrite my question.
> I'm having trouble to show averages of a measure in a cube, where the
normal
> aggregation function for a measure is SUM.
> I see no AVG aggregation function for measures (I see Min, Max,
Count,
> Distinct Count and SUM).
> If I hide the measure (cost), and create a calculated member based on
that
> measure, ie Avg(cost), I have te problem of how to average it, since
the
> cube has two dimensions in the row axis, like:
> Time
> ---
> Product |
> Customer | avg of cost
> If I use avg(nonemptycrossjoin(product.currentmember.children,
> customer.currentmember.children), measures.cost) I get the same
average for
> every cell in the cube, what's not corrrect...
> Sorry to bother you all, but this thing is becoming a nightmare.
> Hope you can help
> Michael Prendergast|||Sometimes, getting back to basics gets the job done
Thank yo very much, problem solved
Michael
"OLAPMonkey" <jjanke@.spss.com> escribi en el mensaje
news:1112202692.231562.128020@.g14g2000cwa.googlegroups.com...
> Averages are usually handled by summing and counting...and then
> dividing the sub by the count in a calculated member.
> MPS wrote:
> normal
> Count,
> that
> the
> average for
>