Variable or Subreport Conundrum
I currently have a very simple report setout as follows:-
Category (Group A)
Consultant A (Group B) 1st £X,XXX
Consultant B (Group B) 2nd £X,XXX
Consultant C (Group B) 3rd £X,XXX
The position of the consultant is based on the value (using group sorting expert). So in theory this shows who has sold the most revenue for that category. The report is fixed to show a 9 month period from the currentdate the report is ran.
I have now been asked to show the information in a different way. I need to show each Consultant (as Group A), with a grouping of the categories showing revenue, and also showing the position they are in for that category (i.e. 1st, 5th etc).
Consultant A (Group A)
Category A 1st £X,XXX
Category B 5th £X,XXX
Is this possible with either a sub-report or a global variable that records the position in the first report for the particular consultant and category and then is able to re-sort/re-group it in another report?
Hope you can assist.
Ian Waterman replied
The only other way is to use a stored procedure which creates a temp table which groups consultants by category and orders by amount and some how adds a ranking number. Unfortunately whilst I can write the query to group categories by consultant, I have no idea how you would apply a ranking number.
You could then use a subreport linked to that SP to bring back the data you require.