February 10, 2014 at 2:34 pm
I created a report which has ranking on it. The columns are Rank, Category, Sales1, Sales2 and Trend%.
It has parameter based on which report is sorted i.e. either on Sales1 or Trend%.
I added rank using formula RunningValue(Fields!Category.value,SUM,Nothing)
The report sorts correctly and ranking number is displayed perfectly.
Now I added interactive sorting, so when I click on Category after report is ran, the Rank field shows as 1, 2 etc though it is sorted by Category.
How can I make the Rank column static?
Before: When report is ran to sort by sales
Rank Category Sales1 Sales2 Trend%
1 Cycle $100 $50 50%
2 Bike $60 $120 -50%
I want it like below, when category is clicked on interactive sorting
Rank Category Sales1 Sales2 Trend%
2 Bike $60 $120 -50%
1 Cycle $100 $50 50%
But it displays like,
Rank Category Sales1 Sales2 Trend%
1 Bike $60 $120 -50%
2 Cycle $100 $50 50%
Please help me out.
February 12, 2014 at 4:18 am
1) Change your DataSet and add a column with ROW_NUMBER() OVER(ORDER BY <Field> DESC)
2) Use this column as Rank column
Hope it works
February 12, 2014 at 7:02 am
Sorry, i did not understand. Are you asking to create an other dataset and use lookup function to link both of them? I tried it it did not work.
February 12, 2014 at 8:00 am
I think the suggestion is to use the existing dataset and add a column to it using
ROW_NUMBER() OVER(ORDER BY category DESC)
February 12, 2014 at 8:00 am
You need to add the RankNumber to your Dataset. Something like this:
SELECT ROW_NUMBER() OVER(ORDER BY value DESC) as RankNumber,*
FROM (
SELECT 'test1' AS name, 100 AS value, '12/16/2013 10:10' AS logdatetime
UNION ALL
SELECT 'test2' AS name, 200 AS value, '12/16/2013 10:12' AS logdatetime
UNION ALL
SELECT 'test3' AS name, 300 AS value, '12/16/2013 10:14' AS logdatetime
UNION ALL
SELECT 'test4' AS name, 400 AS value, '12/17/2013 9:05' AS logdatetime
UNION ALL
SELECT 'test5' AS name, 500 AS value, '12/17/2013 9:07' AS logdatetime
UNION ALL
SELECT 'test6' AS name, 600 AS value, '12/17/2013 9:09' AS logdatetime
) I
Then you show the RankNumber and not the RunningVAlue(.......)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply