Creating Ranking on a dimension attribute

Question

I have a dimensional attribute , call price , in decimal. Can i Rank the dimensional attribute  itself instead of adding a measure to it to give ranking ?

Such as  Rank(Dimension.Price.price.Members)

Answer 1

Here's an Adventure Works query to rank products by ascending list price (excluding products with no price):

 

With
Set [ProductsByPrice] as
Extract(([Product].[List Price].[List Price]
- {[Product].[List Price].&[0]},
[Product].[Product].[Product]),
[Product].[Product])
Member [Measures].[ProductRank] as
Rank([Product].[Product].CurrentMember,
[ProductsByPrice])
select
{[Measures].[ProductRank]} on 0,
Head([Product].[Product].[Product]
* [Product].[List Price].[List Price],
10) on 1
from [Adventure Works]

 

 

 

---

- Deepak

Answer 2

this is good when i want price based on product, However what i am looking for is i just want the ranking based on price all i want is ranking that is ranked for the price for example.

Do i need to have two attributes two generate ranking, can't it be acheived using single attribute.

tthis is  my desired result .

list price        Rank1
10.00             1

11.00            2

12.00          3

14.00         4

Answer 3

Deepak , i took a back door to produce the result. but i have two questions here or concerns

1) The default behavior of Distinct value which groups  the attribute as single dimension member, when querying i don't want it to have as single member, I need it to show rank on each and every member.

2) if i have a duplicate value  which is for example cost 7.5 twice , what would be the ranking order.  I need to assign different value to it.
for example if i have cost 7.5 twice i want it to assign a different rank,

the whole purpose of me to introduce the ranking is to gurantee a uniqness in the logic i am trying to build.

can this be done

please advise

 

WITH

 

Member [Measures].[Cost] as ([Measures].[Extended Cost]/[Measures].[Quantity])

 

SET [Set] AS

 

ORDER([Project Dim].[Cost Per Ton].[Cost Per Ton].MEMBERS,

[Cost],

 

ASC)

 

Member [Measures].[Rank] As

 

Rank([Project Dim].[Cost Per Ton].CurrentMember,[Set])

 

SELECT

{[Measures].[Rank],[Cost]}

ON 0,

{([Set])}

ON 1

 

FROM EBI

Answer 4

".. if i have a duplicate value  which is for example cost 7.5 twice .." - could you give a detailed example? How can the same cost occur twice in the [Project Dim].[Cost Per Ton] attribute, if the key of the attribute is (presumably) based on that cost value?

---

- Deepak

Answer 5

Try this version of Cumulative Cost:

Member [Measures].[Cum Cost] as

Sum

 

 

(Head([AbtdSet], [Measures].[Rank]),
[Measures].[AbtCost])

 

---

- Deepak

Answer 6

this is great it works to what i am expecting performance is great as well on it.

One last question, I am trying to make this cost as a dimension attribute,  because i need to plot a graph that works for me where i have cumulative cost as dimension on x-axis , and Cumulative qty on y-axis.

I have the cost in dimension but that is a unit cost,

I have tried implementing it but apparently it does not show up as i would require it to.
or should i really create blank attribute and assign scope statment to it

CREATE

 

MEMBER CURRENTCUBE.[Asset Dim].[Asset].[Cum Cost]

 

AS Sum(Head([AbtdSet], [Measures].[Rank]),

[Measures].[AbtCost]),

VISIBLE

 

= 1

Answer 7

Could you give more chart details, like an example of how the data that drives the chart should look? The calculation you posted only creates a calculated member, not a new dimenison attribute, so some other approach to your chart may be needed.

---

- Deepak

Answer 8

Deepak

I have a Data that looks like this , i am trying to generate abatemnt curve that would show me which one would be good option to buy

ID		Series Type		Unit	Cum	Rank	Unit qty	Cum Qty	Avg Cost Divide CUM COST/CUM QTY
item1		External		12	12	1	8	8	1.50
Item 2		Internal		13	25	2	9	17	1.47
Item 3		External		13.2	38.2	3	10	27	1.41
Item 4		Internal		14.5	52.7	4	13	40	1.32

I have Avg Cost which is on y-axis , and i have cum qty which is on x- axis

So when i plot the avg cost on one axis and Cum qty on x- axis

i wil have two series which would show me external and internal

but since that i can't have measure on both axis or measure on x-axis , i cannot generate the graph.

the ranking and the graph should be generated at run time.

this will allow me to identify which source should i buy from , should that be an external source or internal source.

if i want to buy 30 qty , what would be ideal , would it be  internal or external for decision making

Answer 9

".. but since that i can't have measure on both axis or measure on x-axis .." - but if you're using SSRS charts, you can have a measure on x-axis in scalar mode:

Get More Out of SQL Server Reporting Services Charts
...
X-Axis Category Mode and Scalar Mode

The x-axis has two modes. The mode is set by using the Numeric or time-scale values option on the X Axis tab in the Chart Properties dialog box.

• Category mode

  The category group expression values determine the individual categories for the x-axis. Labels are shown for only the actual categories present in the data. The sort order within a group and explicit sort expressions are important in category mode, as the chart control will not reorder categories. The format code defined for the x-axis is applied only if the group expression (or the group label expression if explicitly defined) evaluates to a nonstring object.

  Grouping spans for categories are shown if you have multiple levels of category groupings.

• Scalar mode

  The x-axis value range is determined by the minimum and maximum category group expression values. Consequently, the group expression values must be numeric or DateTime values in order to compare and sort. Gaps in the data (for example, you use a DateTime category grouping and you only have data for July and September) are shown on the x-axis, as the categories are scaled either to a numeric or a DateTime axis. Only one category grouping is allowed in scalar mode.

---

- Deepak

Answer 10

Thanks Deepak

That information is certainely helpful , wasnt aware of such property.

Apparently we are not using SSRS. We are using Dundas Control on ASP.net to define our charting.

Currently , I have discussed hoping that we could read the dataset in an object and then once we read it object , we hope to have it get disconnected with SSAS in memroy and capture the two columns and plot them on graph. which would plot one measure on x-axis and other measure on y-axis. 

I am hoping this doable. I am not really a .net person. I am trying to look for such set of examples , as I am in phase of prototyping the deliverable.


If you happen to have any information this matter, do forward it.

Thank you 

Answer 11

".. Apparently we are not using SSRS. We are using Dundas Control on ASP.net to define our charting .." - Dundas Chart has similar concepts of X-axis type (below). You can probably get detailed help on that elsewhere.

Dundas Chart for ASP.NET

Axis Types

Overview

Charts are plotted using X and Y axes. Y axes are always value axes used to plot data (e.g. number of sales, etc.). X axes are normally categorical (e.g. months of the year, etc.), but can be used as value axes as well (e.g. a scatter-type plot).

---

- Deepak

Answer 12

Encountering a little problem on the query which i started with , new requirment mentions that i need to have a different ranking for each type , the query is mentioned belowed


I have no hierarchies they are all attribute ,

Please advise on the mistake i am making, i have been trying to look around but apparently does not succeed

# Error : Rank function expects a tuple expression for 1 argument  , A string numeric expression was used.




WITH

SET

 

[AbtSet] AS

ORDER

 

(([Project Dim].[Project Type Name].[Project Type Name].members *

[Project Dim].[Cost Per Ton].[Cost Per Ton].

MEMBERS),

[Measures].[Unit Cost],

ASC

 

)

Member

 

[Measures].[Rank] As

Rank

 

(( [Project Dim].[Project Type Name].Currentmember

* [Project Dim].[Cost Per Ton].

CurrentMember)

,

[AbtSet])

Select

 

{[Measures].[Unit Cost], [Measures].[Rank]}on 0,

[Abtset]

on 1

From

 

(Select({[Project Dim].[Cost Per Ton].&[1.003E1]&[15307],

[Project Dim].[Cost Per Ton].&[1.003E1]&[16498],

[Project Dim].[Cost Per Ton].&[1.009E1]&[15154],

[Project Dim].[Cost Per Ton].&[1.E1]&[16556],

[Project Dim].[Cost Per Ton].&[1.032E1]&[16662],

[Project Dim].[Cost Per Ton].&[1.072E1]&[15586]

,[Project Dim].[Cost Per Ton].&[9.23]&[16802]

,[Project Dim].[Cost Per Ton].&[10.23]&[16801]

,[Project Dim].[Cost Per Ton].&[9.97]&[14913],

[Project Dim].[Cost Per Ton].&[7.87]&[14742]

})

ON COLUMNS

FROM

 

[BI])



Results that are produced  are

Dim Attribute	Dim Attribute2	Measure	Rank
External	7.87	7.87	#Error
External	9.97	9.97	#Error
External	10	10	#Error
External	10.03	10.03	#Error
External	10.03	10.03	#Error
External	10.09	10.09	#Error
External	10.32	10.32	#Error
External	10.72	10.72	#Error
Internal	9.23	9.23	#Error
Internal	10.03	10.03	#Error
Internal	10.23	10.23	#Error

Desired results

Dim Attribute	Dim Attribute2	Measure	Rank
External	7.87	7.87	1
External	9.97	9.97	2
External	10	10	3
External	10.03	10.03	4
External	10.03	10.03	5
External	10.09	10.09	6
External	10.32	10.32	7
External	10.72	10.72	8
Internal	9.23	9.23	1
Internal	10.03	10.03	2
Internal	10.23	10.23	3

Answer 13

Hi,

I think you should use 
([Project Dim].[Project Type Name].Currentmember , [Project Dim].[Cost Per Ton].CurrentMember )

instead of

[Project Dim].[Project Type Name].Currentmember * [Project Dim].[Cost Per Ton].CurrentMember

as first argument for Rank().

* would be used to cross-join sets, but you need a tuple here. And * of course also can be used for multiplication of values, which is what the engine seems to understand here.

Frank

Answer 14

i have used that , but the problem is it assigns the rank to everything from 1 to 10, it does not break the rank for internal and external . it should be using assigning difrerent rank for  internal and external


Producing this results ( but this are not what i am looking for )

Column1	Column2	Column3	Column4
		Unit Cost	Rank
External	7.87	7.87	1
External	9.97	9.97	2
External	10	10	3
External	10.03	10.03	4
External	10.03	10.03	5
External	10.09	10.09	6
External	10.32	10.32	7
External	10.72	10.72	8
Internal	9.23	9.23	9
Internal	10.23	10.23	10

Desired results

Dim Attribute	Dim Attribute2	Measure	Rank
External	7.87	7.87	1
External	9.97	9.97	2
External	10	10	3
External	10.03	10.03	4
External	10.03	10.03	5
External	10.09	10.09	6
External	10.32	10.32	7
External	10.72	10.72	8
Internal	9.23	9.23	1
Internal	10.03	10.03	2
Internal	10.23	10.23	3

Answer 15

Hi,

Normally, you would use Generate() to achieve that. But as you have just two cases, maybe IIf and defining two sets would work as well:

WITH

SET [AbtSet Ext] AS

ORDER

 

 

 

[Measures].[Unit Cost],

 

ASC

 

 

)

SET [AbtSet Int] AS

ORDER

 

 

 

[Measures].[Unit Cost],

 

ASC

 

 

)

Member

 

 

IIf( [Project Dim].[Project Type Name].Currentmember is [Project Dim].[Project Type Name].[External],
 Rank

 

 

, [Project Dim].[Cost Per Ton].

 

[AbtSet Ext]

),
 Rank

 

(( [Project Dim].[Project Type Name].Currentmember,

, [Project Dim].[Cost Per Ton].

 

[AbtSet Int]))

CurrentMember) ,
CurrentMember) ,

 

Select

 

{[Measures].[Unit Cost], [Measures].[Rank]}on 0,

[Abtset]

 

on 1

From

 

(Select({[Project Dim].[Cost Per Ton].&[1.003E1]&[15307],

 

ON COLUMNS

FROM

 

 

[BI])


Frank

[Project Dim].[Cost Per Ton].&[1.003E1]&[16498],

[Project Dim].[Cost Per Ton].&[1.009E1]&[15154],

[Project Dim].[Cost Per Ton].&[1.E1]&[16556],

[Project Dim].[Cost Per Ton].&[1.032E1]&[16662],

[Project Dim].[Cost Per Ton].&[1.072E1]&[15586]

,[Project Dim].[Cost Per Ton].&[9.23]&[16802]

,[Project Dim].[Cost Per Ton].&[10.23]&[16801]

,[Project Dim].[Cost Per Ton].&[9.97]&[14913],

[Project Dim].[Cost Per Ton].&[7.87]&[14742]

})

(( ( [Project Dim].[Project Type Name].Currentmember,[Measures].[Rank] As

Answer 16

Hi,

Somehow the forum software completely messed up my proposal above. What I was proposing was to define two sets, [AbtSet Ext] and [AbtSet Int] where you use the External and Internal member explicitely instead of all the members.
And then the Rank measure has an IIf which checks the [Project Type Name].Currentmember and then switches between a Rank of one or the other set.

Frank

Answer 17

this may not work, i cannot swtich the rank between sets as i would need it to be part of the same query, because, i am using rank to calculate my cumulative value on non time member. this will be kind of a union that would be on one table ,

the outcome which i am trying to get is  ( i have with one type which works ) but does not work for both together.

		Unit Cost	Rank	Abat Cum QTY	Totalcost	Abt Cum Total Cost	AVGcost
10	External	10	1	108,740.00	1,087,400.00	1,087,400.00	10.00
10.03	External	10.03	2	261,984.00	1,537,037.32	2,624,437.32	10.02
10.03	External	10.03	3	503,037.00	2,417,761.59	5,042,198.91	10.02
10.09	External	10.09	4	526,557.00	237,316.80	5,279,515.71	10.03
10.32	External	10.32	5	548,379.00	225,203.04	5,504,718.75	10.04
10.72	External	10.72	6	684,722.00	1,461,596.96	6,966,315.71	10.17
9.23	Internal	9.23	1	108,740.00	1,003,670.20	1,003,670.20	9.23
10.23	Internal	10.2	2	261,984.00	1,563,088.80	2,566,759.00	9.80
11.23	Internal	11.23	3	561,984.00	3,369,000.00	5,935,759.00	10.56

Answer 18

How about this version of [Rank]?

[Measures].[Rank] As
Rank
(([Project Dim].[Project Type Name].Currentmember,
[Project Dim].[Cost Per Ton].CurrentMember),
Exists([AbtSet],
{[Project Dim].[Cost Per Ton].[All]}))

---

- Deepak

Answer 19

Hi,

I did not say you should gave two Rank measures, but one that uses different expressions depending on situation:

The main definition of this one measure [Measures].[Rank] would be:

IIf( [Project Dim].[Project Type Name].Currentmember is [Project Dim].[Project Type Name].[External],
    Rank( ..., [AbtSet Ext]),
    Rank( ..., [AbtSet Int]))


Frank

Answer 20

I have this works close to what i am looking , but it does not assigs the value of  rank at run time , meaning the value has to reset the rank every time i run the query based on the values selected.

 

 

WITH

 

MEMBER Measures.[Cum Sales] AS

 

Sum

(

Head

(

Order

([ Project Dim].[Hierarchy].

Siblings

,[Measures].[Unit Cost]

,

ASC

)

,

Rank

([ Project Dim].[Hierarchy].

CurrentMember

,

 

order(

[ Project Dim].[Hierarchy].

Siblings

,Measures.[Unit Cost]

,

ASC)

)

),Measures.[Unit Cost]

)

Member

 

[measures].[rank] as

Rank

([ Project Dim].[Hierarchy].

CurrentMember

,

 

order(

[ Project Dim].[Hierarchy].

Siblings

,Measures.[Unit Cost]

,

ASC))

 

Select

 

{[Measures].[Unit Cost], Measures.[Cum Sales], [measures].[rank]}on 0,

non

 

empty([ Project Dim].[ Project Type Name].[ Project Type Name] *

{

[ Project Dim].[Hierarchy].[ Cost Per Ton],

Order

 

(

[ Project Dim].[Hierarchy].[ Cost Per Ton].[ Cost Per Ton].

children,

Measures.[Unit Sales],

ASC)})

 

on 1

From

 

(Select({[ Project Dim].[ Cost Per Ton].&[1.003E1]&[15307],

[ Project Dim].[ Cost Per Ton].&[1.003E1]&[16498],

[ Project Dim].[ Cost Per Ton].&[1.009E1]&[15154],

[ Project Dim].[ Cost Per Ton].&[1.E1]&[16556],

[ Project Dim].[ Cost Per Ton].&[1.032E1]&[16662],

[ Project Dim].[ Cost Per Ton].&[1.072E1]&[15586]

,[ Project Dim].[ Cost Per Ton].&[9.23]&[16802]

,[ Project Dim].[ Cost Per Ton].&[10.23]&[16801]

,[ Project Dim].[ Cost Per Ton].&[9.97]&[14913],

[ Project Dim].[ Cost Per Ton].&[7.87]&[14742],

[ Project Dim].[ Cost Per Ton].&[1.E1]&[15295],

[ Project Dim].[ Cost Per Ton].&[1.E1]&[15847],

[ Project Dim].[ Cost Per Ton].&[7.5]&[14594]

,[ Project Dim].[ Cost Per Ton].&[7.5]&[14735]

})

ON COLUMNS

FROM

 

[BI])

Column1	Column2	Column3	Column4	Column5	Column6
		Unit Cost	Cum Sales	rank
External	10.00	10.00	10,466.50	1,198
External	10.00	10.00	10,476.50	1,199
External	10.00	10.00	10,496.50	1,201
External	10.03	10.03	10,616.74	1,213
External	10.03	10.03	10,636.80	1,215
External	10.09	10.09	10,828.04	1,234
External	10.32	10.32	11,941.33	1,343
External	10.72	10.72	14,048.18	1,543
External	7.50	7.50	7.50	2
External	7.50	7.50	15.00	3
External	7.87	7.87	1,398.63	183
External	9.97	9.97	10,356.68	1,187
Internal	10.23	10.23	19.46	2
Internal	9.23	9.23	9.23	1

Answer 21

Hi,

Maybe then you will have to use Generate() as I wrote above.  Have a look at the BOL documentation of this function at http://msdn.microsoft.com/en-us/library/ms145526.aspx, and replace the TopCount in the third example with your Ranking definition.

Frank

Answer 22

this seems to be working fine and i get the result fine.

So to calculate cumulative would the approach still be the same

MEMBER

 

Measures.[Unit Cum Sales] AS

Sum

 

(Head(

Iif

 

([Abatement Project Dim].[Abatement Project Type Name].Currentmember is [Abatement Project Dim].[Abatement Project Type Name].&[External],[AbtdSet],[AbtdSet1])

, [Measures].[Rank]),

[Measures].[Unit Cost])

Answer 23

this works fine,.

thank you

MEMBER

 

Measures.[Unit Cum Sales] AS

Iif

 

([Abatement Project Dim].[Abatement Project Type Name].Currentmember is [Abatement Project Dim].[Abatement Project Type Name].&[External],

(

Sum(Head([AbtSet], [Measures].[Rank]),

[Measures].[Unit Cost])),

(

Sum(Head([AbtSet1], [Measures].[Rank]),

[Measures].[Unit Cost])))

Answer 24

this is the entire solution that produces the result, this creates ranking based on different protject type, refer  to results

WITH

 

 

SET

 

[AbtSet] AS

ORDER

 

(([ Project Dim].[ Project Type Name].&[External] *

[ Project Dim].[ Cost Per Ton].[ Cost Per Ton].

MEMBERS),

[Measures].[Unit Cost],

ASC

 

)

SET

 

[AbtSet1] AS

ORDER

 

(([ Project Dim].[ Project Type Name].&[Internal] *

[ Project Dim].[ Cost Per Ton].[ Cost Per Ton].

MEMBERS),

[Measures].[Unit Cost],

ASC

 

)

Member

 

[Measures].[Rank] As

Iif

 

([ Project Dim].[ Project Type Name].Currentmember is [ Project Dim].[ Project Type Name].&[External],

Rank

 

(([ Project Dim].[ Project Type Name].currentmember,

[ Project Dim].[ Cost Per Ton].

CurrentMember),

AbtSet),

Rank

 

(([ Project Dim].[ Project Type Name].currentmember,

[ Project Dim].[ Cost Per Ton].

CurrentMember),

AbtSet1))

MEMBER

 

Measures.[Unit Cum Sales] AS

Iif

 

([ Project Dim].[ Project Type Name].Currentmember is [ Project Dim].[ Project Type Name].&[External],

(

Sum(Head([AbtSet], [Measures].[Rank]),

[Measures].[Unit Cost])),

(

Sum(Head([AbtSet1], [Measures].[Rank]),

[Measures].[Unit Cost])))

 

 

MEMBER

 

Measures.[Abat Cum QTY] AS

Iif

 

([ Project Dim].[ Project Type Name].Currentmember is [ Project Dim].[ Project Type Name].&[External],

(

Sum(Head([AbtSet], [Measures].[Rank]),

[Measures].[ Quantity])),

(

Sum(Head([AbtSet1], [Measures].[Rank]),

[Measures].[ Quantity])))

MEMBER

 

Measures.[Totalcost] AS

[Measures].[Unit Cost] * [Measures].[ Quantity]

MEMBER

 

Measures.[Abt Cum Total Cost] AS

Iif

 

([ Project Dim].[ Project Type Name].Currentmember is [ Project Dim].[ Project Type Name].&[External],

(

Sum(Head([AbtSet], [Measures].[Rank]),

Measures.[Totalcost])),

(

Sum(Head([AbtSet1], [Measures].[Rank]),

Measures.[Totalcost])))

 

MEMBER

 

Measures.[AVGcost] AS

Measures.[Abt Cum Total Cost]/ Measures.[Abat Cum QTY]

 

 

Select

 

{[Measures].[Unit Cost],Measures.[Unit Cum Sales], [Measures].[Rank],[Measures].[ Quantity], Measures.[Abat Cum QTY], Measures.[Totalcost], Measures.[Abt Cum Total Cost], Measures.[AVGcost]}on 0,

Abtset1 + Abtset

on

 

1

From

 

(Select({[ Project Dim].[ Cost Per Ton].&[1.003E1]&[15307],

[ Project Dim].[ Cost Per Ton].&[1.003E1]&[16498],

[ Project Dim].[ Cost Per Ton].&[1.009E1]&[15154],

[ Project Dim].[ Cost Per Ton].&[1.E1]&[16556],

[ Project Dim].[ Cost Per Ton].&[1.032E1]&[16662],

[ Project Dim].[ Cost Per Ton].&[1.072E1]&[15586]

,[ Project Dim].[ Cost Per Ton].&[9.23]&[16802]

,[ Project Dim].[ Cost Per Ton].&[10.23]&[16801]

,[ Project Dim].[ Cost Per Ton].&[9.97]&[14913],

[ Project Dim].[ Cost Per Ton].&[7.87]&[14742],

[ Project Dim].[ Cost Per Ton].&[11.23]&[16803]

})

ON COLUMNS

FROM

 

[Project])

		Unit Cost	Unit Cum Sales	Rank	Abatement Quantity	Abat Cum QTY	Totalcost	Abt Cum Total Cost	AVGcost
Internal	9.23	9.23	9.23	1	56,354	56354	520147.42	520147.42	9.23
Internal	10.23	10.23	19.46	2	46,854	103208	479316.42	999463.84	9.683976436
Internal	11.23	11.23	30.69	3	110,500	213708	1240915	2240378.84	10.4833644
External	7.87	7.87	7.87	1	112,357	112357	884249.59	884249.59	7.87
External	9.97	9.97	17.84	2	17,178	129535	171264.66	1055514.25	8.148486895
External	10	10	27.84	3	108,740	238275	1087400	2142914.25	8.993449795
External	10.03	10.03	37.87	4	153,244	391519	1537037.32	3679951.57	9.399164715
External	10.03	10.03	47.9	5	241,053	632572	2417761.59	6097713.16	9.639555908
External	10.09	10.09	57.99	6	23,520	656092	237316.8	6335029.96	9.655703712
External	10.32	10.32	68.31	7	21,822	677914	225203.04	6560233	9.677087359
External	10.72	10.72	79.03	8	136,343	814257	1461596.96	8021829.96	9.851717529

Answer 25

The ranking is irrespective of type , It creates ranking based on the lowest cost.

WITH

 

SET

 

[AbtdSet] AS

ORDER

 

([ Project Dim].[ Cost Per Ton].[ Cost Per Ton].MEMBERS,

[Measures].[Unit Cost],

ASC

 

)

 

Member

 

[Measures].[Rank] As

Rank

 

([ Project Dim].[ Cost Per Ton].CurrentMember,[AbtdSet])

MEMBER

 

Measures.[Unit Cum Sales] AS

Sum

 

(Head([AbtdSet], [Measures].[Rank]),

[Measures].[Unit Cost])

MEMBER

 

Measures.[Abat Cum QTY] AS

Sum

 

(Head([AbtdSet], [Measures].[Rank]),

[Measures].[ Quantity])

MEMBER

 

Measures.[Abt Cum Total Cost] AS

Sum

 

(Head([AbtdSet], [Measures].[Rank]),

Measures.[Totalcost])

 

MEMBER

 

Measures.[Totalcost] AS

[Measures].[Unit Cost] * [Measures].[ Quantity]

MEMBER

 

Measures.[AVGcost] AS

Measures.[Abt Cum Total Cost]/ Measures.[Abat Cum QTY]

 

 

 

 

Select

 

{[Measures].[Unit Cost], Measures.[Abt Cum Total Cost], [Measures].[Rank],[Measures].[ Quantity],Measures.[Abat Cum QTY], Measures.[Totalcost], Measures.[Abt Cum Total Cost], Measures.[AVGcost]}on 0,

[ Project Dim].[ Cost Per Ton].[ Cost Per Ton] * [ Project Dim].[ Project Type Name].[ Project Type Name].

members

 

Having

 

[Measures].[Rank] > 0

 

on

 

1

From

 

(Select({[ Project Dim].[ Cost Per Ton].&[1.003E1]&[15307],

[ Project Dim].[ Cost Per Ton].&[1.003E1]&[16498],

[ Project Dim].[ Cost Per Ton].&[1.009E1]&[15154],

[ Project Dim].[ Cost Per Ton].&[1.E1]&[16556],

[ Project Dim].[ Cost Per Ton].&[1.032E1]&[16662],

[ Project Dim].[ Cost Per Ton].&[1.072E1]&[15586]

})

ON COLUMNS

FROM

 

[Project])



Result

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10
		Unit Cost	Abt Cum Total Cost	Rank	Abatement Quantity	Abat Cum QTY	Totalcost	Abt Cum Total Cost	AVGcost
7.87	External	7.87	884,249.59	1	112,357.00	112,357.00	884,249.59	884249.59	7.87
9.23	Internal	9.23	1,404,397.01	2	56,354.00	168,711.00	520,147.42	1404397.01	8.32
9.97	External	9.97	1,575,661.67	3	17,178.00	185,889.00	171,264.66	1575661.67	8.48
10	External	10.00	2,663,061.67	4	108,740.00	294,629.00	1,087,400.00	2663061.67	9.04
10.03	External	10.03	4,200,098.99	5	153,244.00	447,873.00	1,537,037.32	4200098.99	9.38
10.03	External	10.03	6,617,860.58	6	241,053.00	688,926.00	2,417,761.59	6617860.58	9.61
10.09	External	10.09	6,855,177.38	7	23,520.00	712,446.00	237,316.80	6855177.38	9.62
10.23	Internal	10.23	7,334,493.80	8	46,854.00	759,300.00	479,316.42	7334493.8	9.66
10.32	External	10.32	7,559,696.84	9	21,822.00	781,122.00	225,203.04	7559696.84	9.68
10.72	External	10.72	9,021,293.80	10	136,343.00	917,465.00	1,461,596.96	9021293.8	9.83
11.23	Internal	11.23	10,262,208.80	11	110,500.00	1,027,965.00	1,240,915.00	10262208.8	9.98