May 8, 2009 at 3:15 pm
I have a simple report that includes two tables, one that contains data relating to sales by dept. & the other contains data relating to labor by dept. In the layout I have the tables side by side for comparison purposes (sales v labor). The data comes from two separate databases. I can perform a calculation on the totals of each table (total sales - total labor) but can't figure out how to do the same calculation on an individual department. If I pick a dept and subtract it's labor from sales I get the dept's labor minus the total sales as an answer. I think this may be a scope issue and that I need to narrow the scope but I'm at a loss.
May 11, 2009 at 8:58 am
I've run across something similar in the past, but I was very limited with what I could do. Hopefully you can write another dataset that would pull from both locations and allow you to create aggregates. It's much better to have the SQL statement do that kind of work, not the Report Viewer.
Another idea would be to lump it all into one dataset and then the aggregates would be simple. I realize that just saying "lump it together" is much easier than actually writing a query to do that.
May 11, 2009 at 10:51 am
sc2techie (5/8/2009)
I have a simple report that includes two tables, one that contains data relating to sales by dept. & the other contains data relating to labor by dept. In the layout I have the tables side by side for comparison purposes (sales v labor). The data comes from two separate databases. I can perform a calculation on the totals of each table (total sales - total labor) but can't figure out how to do the same calculation on an individual department. If I pick a dept and subtract it's labor from sales I get the dept's labor minus the total sales as an answer. I think this may be a scope issue and that I need to narrow the scope but I'm at a loss.
This is a very tricky scenario; however, it can be done. You may need to post some more details on the issue or some examples. Or if I understand what you are trying to do correctly, try something like Sum("Sales field","GroupBySales") - Sum("Department field","GroupByDepartment") in the expression of the field.
Good luck 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply