Andy Somnifac
New Member
- Joined
- Aug 16, 2016
- Messages
- 7
Greetings all. I'll jump right in:
I have created a linked table with data for the previous, drawn from an external data source. The table is set up as so:
I have set up a measure to give me the average Metric A per case (i.e. SUM([PrevWeekMetricA])/SUM([PrevWeekCases]) ). I can stick everything in a PivotTable, with the Measure as a Value to make sure it calculates properly, and it does.
Where I run into an issue is here:
I want to create a Cubeset that contains a the EmpID's sorted by the created measure in ascending order. Seems like it should be simple enough. I create the Cubeset with:
CUBESET("Connection", "
.[EmpID].Children, "Label", 1, "[Measure].[MetricAverage]")
However, when I list out the members of that set, they are always sorted alphabetically by ID#.
CUBERANKEDMEMBER("Connection", ReferenceToCubeset, ROW(A1)) will be ID1, rank 2 will be ID2, etc.
I have to be missing something incredibly stupid and basic, but I'm pretty much on my own with this and everything I've learned has come through trial and error. So, I'm hoping one of the gurus out there might be able to take pity on me and point me in the right direction.
Thanks,
Andy
I have created a linked table with data for the previous, drawn from an external data source. The table is set up as so:
Code:
_________________________________________________________________________
| EmpID | PrevWeekCases | PrevSubsetA | PrevWeekMetricA |
-------------------------------------------------------------------------
| ID1 | 15 | 3 | 136 |
-------------------------------------------------------------------------
| ID2 | 36 | 5 | 933 |
-------------------------------------------------------------------------
| ID3 | 96 | 23 | 1203 |
-------------------------------------------------------------------------
| ID4 | 26 | 8 | 99 |
-------------------------------------------------------------------------
| ID5 | 91 | 5 | 364 |
-------------------------------------------------------------------------
| ID6 | 45 | 22 | 102 |
-------------------------------------------------------------------------
| ID7 | 52 | 12 | 685 |
-------------------------------------------------------------------------
| ID8 | 68 | 8 | 536 |
-------------------------------------------------------------------------
| ID9 | 12 | 0 | 114 |
-------------------------------------------------------------------------
| ID10 | 34 | 7 | 87 |
-------------------------------------------------------------------------
| ID11 | 17 | 2 | 24 |
-------------------------------------------------------------------------
| ID12 | 79 | 50 | 852 |
-------------------------------------------------------------------------
.
.
etc.
I have set up a measure to give me the average Metric A per case (i.e. SUM([PrevWeekMetricA])/SUM([PrevWeekCases]) ). I can stick everything in a PivotTable, with the Measure as a Value to make sure it calculates properly, and it does.
Where I run into an issue is here:
I want to create a Cubeset that contains a the EmpID's sorted by the created measure in ascending order. Seems like it should be simple enough. I create the Cubeset with:
CUBESET("Connection", "
However, when I list out the members of that set, they are always sorted alphabetically by ID#.
CUBERANKEDMEMBER("Connection", ReferenceToCubeset, ROW(A1)) will be ID1, rank 2 will be ID2, etc.
I have to be missing something incredibly stupid and basic, but I'm pretty much on my own with this and everything I've learned has come through trial and error. So, I'm hoping one of the gurus out there might be able to take pity on me and point me in the right direction.
Thanks,
Andy