Individual SELECT statements into one report.

• Kafkaesque

  SSC-Addicted

  Points: 405

  More actions

  April 9, 2009 at 5:05 pm

  #220107

  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.

• kiran.mahamkali

  SSC-Addicted

  Points: 440

  More actions

  April 9, 2009 at 11:15 pm

  #975569

  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

• Kafkaesque

  SSC-Addicted

  Points: 405

  More actions

  April 10, 2009 at 3:38 am

  #975621

  Tanks for the quick response, I'll look into.

  E

• Kafkaesque

  SSC-Addicted

  Points: 405

  More actions

  April 10, 2009 at 5:28 pm

  #975992

  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