I have a sales report that includes dollar amount, tonnage, and profit margin among other things. They are currently sorted by tonnage sold from highest to lowest. I'd like to be able to place a number in a column counting 1 up for tonnage ranking. I'd also like to get a number ranking for sales amount ranking along with profit margin ranking. The most tonnage sold might not have been the biggest sale nor had the highest profit margin.
Does this sound like something that can be done within SSRS?
I should ad I'm runing MDX queries against a cube so I can't use T-SQL for ranking.
John,
You might be able to do this in the code window of the reports, but I think it might be simpler for you to do this in your MDX.
Here's a sample query (based on the FoodMart cube) that gives a rank for Store Sales and a rank for a made up member of Negative Store Sales, to show that the rankings work independently:
Code Snippet
with
member [Measures].[Negative Store Sales]
as [Measures].[Store Sales] * -1
member [Measures].[Store Sales Rank]
as Rank([Store].CurrentMember, [Store].[Store Name], [Measures].[Store Sales])
member [Measures].[Negative Store Sales Rank]
as Rank([Store].CurrentMember, [Store].[Store Name], [Measures].[Negative Store Sales])
select
{[Measures].[Store Sales Rank]
, [Measures].[Store Sales]
, [Measures].[Negative Store Sales Rank]
, [Measures].[Negative Store Sales]} on columns
, [Store].[Store Name] on rows
from [Sales]
Will that work for you?
Jessica
Thanks Jessica.
I played around yesterday and came up with this MDX code, which does almost everything I need (I think):
with
SET [Sales Rank 2006] AS
ORDER
(
NONEMPTY([Customer Name].[Customer Name].members),
[Measures].[Sales], BDESC
)
MEMBER [Measures].[Sales Rank] AS
RANK([Dim Customer].[Customer Name].CurrentMember, [Sales Rank 2006])
SELECT NON EMPTY
{[Measures].[Sales Rank], [Measures].[Sales]} on 0,
NON EMPTY [Dim Customer].[Customer Name].MEMBERS on 1
from [Heidtman DW]
WHERE [Date Shipped].[Year].[2006]
I'm not sure what to do with it. Do I create calculate members in my cube? Do I create a calculated member in my data set in SSRS? It doesn't seem like I can use this code directly to do either. I use year as a parameter in my reports. Will SSRS run the ranking code against the set of data (year) chosen at run time? Or do I have to create rankings by each year in my data set and store them somewhere?
Thanks again.
|||If you think there will be multiple queries/reports that will need to use sales rank, I would put the calculated measure in your cube. If this is a one time reporting requirement, I would put it in the query.
As for getting the rank in a calculated measure, there are a few modifications I would make. Since you are passing the year in as a parameter to your query, you will not need to directly specify it in your calculated measure. Once you slice on the year and use your calculated measure, it will rank over the slice you have specified. Also, if you pass the measure into the rank function, you don't need to order your set beforehand.
So to use your example of customers, I would create a calculated measure that looks similar to this:
Code Snippet
with
member [Measures].[Sales Rank Over Customers] as
Rank([Dim Customer].[Customer Name].CurrentMember, [Dim Customer].[Customer Name], [Measures].[Sales])
Then you can use [Measures].[Sales Rank Over Customers] in your query on the same axis as your sales measure. As long as your [Dim Customer].[Customer Name] hierarchy is on the other axis, and you are slicing on the date, that should work for you.
-Jessica
|||That builds an akward data set, increases the processing time by several orders of magnitude, and doesn't produce a rank result.....back to the drawing board.
Could I just create a named set in my cube that was:
ORDER
(
NONEMPTY([Customer Name].[Customer Name].members),
[Measures].[Sales], BDESC
)
This, to me, would order the customers by sales from highest to lowest? Then all I need to do is somehow assign an integer to each customer in the ordered list, counting from 1 to n? I don't need to rank them as they're already sorted in the order I'm looking for?
How to assign an integer?
Thanks.
