April 9, 2009 at 5:05 pm
I am trying to create a report that accumulates results from different select
statements but puts them all into one report.
The following is an example:
Table 1
col1
col2
col3
Table 2
col1
col2
Table 3
col1
col2
col3
Each total is comprised of a single SELECT statement.
SELECT Count(col1) FROM Table 1
SELECT Count(col2) FROM Table 2
SELECT Count(col3) FROM Table 3
My report should display totals from each table, but in the one report like below:
Total for Table 1 : 100
Total for Table 2 : 360
Total for Table 3 : 125
Grand total for all tables : 585
This may be very simple. I'm not familiar with Reporting Services so i suppose a simple solution might exist.
April 9, 2009 at 11:15 pm
Hi enzomenoni,
You can achieve the task in the following way.
1. Create seperate datasets for all the three queries.
2. Create a fourth dataset which "UNONS" all the three queries. Like
query1
UNION
query2
UNION
query3
In the above way you can calculate the totals for individual queries (from individual datasets ) and total of all the tables using the 4th dataset.
Hope this answers your question.
Thanks
Kiran Mahamkali
April 10, 2009 at 3:38 am
Tanks for the quick response, I'll look into.
E
April 10, 2009 at 5:28 pm
Hi,
I was trying out your suggestion using the orthwind dbase. I have created 3 datasets.
Job_Title
Territory
No_Of_SalesReps
How do i use them within the fourth dataset? Perhaps i picked you up wrong but i did the following:
Job_Title
Union
Territory
Union
No_Of_SalesReps
Thanks in advance
E
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply