My report gathers customer sales data for the past year and the current year to show sales RANK (get it??). I have a column for previous year totals and one for current sales grouped by customer as well as a column for %age of total for each period. Sorting by TopN is a no brainer BUT what i'd like to do is have a column for each period that shows the customer rank for that period. This rank should (?) be based on a constant (like total sales for the period) and wouldn't change no matter what the sort is. Therefore, if TopN sorted YTD, the rank for both YTD and previous year would display the correct customer rank for each period (which, in my case, would usually be different per period). This is where i get to be a no brainer!! Any suggestions?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
This is a RANK question for you all . . . .
- Thread starter gpv
- Start date
AndrewBaines
Technical User
Two ideas:
1) Use a stored procedure to do all the summations and return an extra field "Rank"
2) Use a subreport against each row to return the rank - very slow. Andrew Baines
Chase International
1) Use a stored procedure to do all the summations and return an extra field "Rank"
2) Use a subreport against each row to return the rank - very slow. Andrew Baines
Chase International
- Thread starter
- #3
This problem is not as simple as it seems.
The best option would be to create an sql query for this problem and then use the result to create the report.
(Create a sql view)
A sub report would not work in this instance.
I think the following would happen. I have not tested this myself so i might be wrong.
1. If you do not link the sub reports, the customer's names
would lie in different positions in the two reports
when displayed next to each other.
2. If you link the main report to the sub report on customer
name, the sub report would only run for that customer
only.
Another solution could be to run the report first for the previous year and then export the result to exel.
Run the report then for the current year and export the result to exel. Then in exel join the two reports together.
The best option would be to create an sql query for this problem and then use the result to create the report.
(Create a sql view)
A sub report would not work in this instance.
I think the following would happen. I have not tested this myself so i might be wrong.
1. If you do not link the sub reports, the customer's names
would lie in different positions in the two reports
when displayed next to each other.
2. If you link the main report to the sub report on customer
name, the sub report would only run for that customer
only.
Another solution could be to run the report first for the previous year and then export the result to exel.
Run the report then for the current year and export the result to exel. Then in exel join the two reports together.
Guest
NOT "visitor" but GPV not signed in . . . .
Ah hah!! Thanks, herbsza, for confirming that i'm not entirely inept! I find myself forced to use the "two report to Exel" (or two report to Access) method in several other instances. I was hoping for a pure CR solution but sometimes that is the most difficult to achieve (as we all know). I will procede with the 2Rpt method but am still open to pure CR methodology if anyone else would like to take a cut at the apple here . . . .
Ah hah!! Thanks, herbsza, for confirming that i'm not entirely inept! I find myself forced to use the "two report to Exel" (or two report to Access) method in several other instances. I was hoping for a pure CR solution but sometimes that is the most difficult to achieve (as we all know). I will procede with the 2Rpt method but am still open to pure CR methodology if anyone else would like to take a cut at the apple here . . . .
I have had a rethink on the statement I made about the sub reports. It would be possble to do it with sub reports using shared variables to bring the ranking values across to the main report. the only problem i see would be that the sub report would need to run through all its records each time for a customer group that you have in the main report. The time it takes to do this would all depend on the number of records involved.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Question