Total of topN, total of other employees, total of all
-
I have a table which includes the columns Branch, Employee, Year, Sales and Revenue. I want to create a report which, for each branch, ranks the top N(parameter) employees by and the sum of their Sales. Then at the bottom, I want to have the total Sales and Revenue for those employees then two other cells with the total Sales and Revenue for all other employees outside the top N and the total Sales of all employees. Basically like this grouped by branch:
What would be the best way to go about implementing this in SSRS? I have a stored procedure that takes top N and Year as parameters, however I am unable to display the total for all other employees since it filters out for only the top N. Is there an alternate way to do this that would be better?
-
SSRS can only display and do calculations on data that is provided to it. If you want the subtotal and totals, you will need that data pulled into SSRS.
There are multiple ways you could do this. You could have 2 data sets - one that is filtered and one that is unfitlered, and count. You could modify the stored procedure to return the top N as well as a subtotal and totals. You could move the filtering of the top N over to SSRS and pull in all the data. How you do it is up to you. My opinion, modifying the stored procedure will likely be the easiest way to do this.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. - Mr. Brian Gale wrote:
SSRS can only display and do calculations on data that is provided to it. If you want the subtotal and totals, you will need that data pulled into SSRS.
There are multiple ways you could do this. You could have 2 data sets - one that is filtered and one that is unfitlered, and count. You could modify the stored procedure to return the top N as well as a subtotal and totals. You could move the filtering of the top N over to SSRS and pull in all the data. How you do it is up to you. My opinion, modifying the stored procedure will likely be the easiest way to do this.
Thanks, yeah this is what I was thinking. I wasn't sure if there was an easier way to do it using the functionality available in SSRS. If I use 2 data sets, I cannot use the values in the same table however, right?
-
You can, but it depends on what you are showing in the datasets. There are some blogs about this, my quick search brought up this one which has good examples of multi-dataset SSRS reports:
https://ellarr.com/how-to-use-multiple-datasets-in-a-single-table-in-ssrs/
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply