Display Multiple Counts in SQL on the Report RSS

• • Reply
  

  texandude

  Member

  1 Points

  10 Posts

  Display Multiple Counts in SQL on the Report

  May 16, 2012 09:12 PM|LINK

  Hi,

  I want to display counts from 3 temp tables.

  Example:

  Select Count(ID) as total1 FROM #temp1

  Select Count(OhterID) total2 FROM #temp2

  Select Count(AnotherID) total3 FROM #temp3

  When I add Query in my Dataset for the Report I only see count of total1 which I can drag on the table in the Report.

  I do not see other 2 counts.

  How can I display counts from my query? or any other option. like create a store proc and then grab values of count from proc to display.

  Please let me know.

  Thanks.

  PS - I have to use temp tables as that's the way my business logic will work.

  I have to display like this

  Totals

  ID         OtherID    AnotherID

  232       456         789

  • Edited by texandude on May 16, 2012 09:17 PM
• • Reply
  

  kidshaw

  Participant

  1158 Points

  252 Posts

  Re: Display Multiple Counts in SQL on the Report

  May 16, 2012 09:13 PM|LINK

  Create multiple data sets, one for each total.
  If it helps, mark as answer - your approval helps with my recovery ;)
• • Reply
  

  texandude

  Member

  1 Points

  10 Posts

  Re: Display Multiple Counts in SQL on the Report

  May 16, 2012 09:16 PM|LINK

  Thanks for your response. I tried that but get an error when I try to place other 2 counts from 2 separate datasets

  I have to display like this

  Totals

  ID         OtherID    AnotherID

  232       456         789

• • Reply
  

  Ashutosh Pat...

  Contributor

  5737 Points

  1105 Posts

  Re: Display Multiple Counts in SQL on the Report

  May 17, 2012 04:14 AM|LINK

  Create a dataset and use Text while selecting the query type, and do the code there:

  Declare @ID int, @OtherID int, @AnotherID int
  Select @ID = Count(ID) as total1 FROM #temp1
  Select @OtherID = Count(OhterID) total2 FROM #temp2
  Select @AnotherID = Count(AnotherID) total3 FROM #temp3
  
  select @ID as ID, @AnotherID as AnotherID, @OtherID as OtherID

  Now use this dataset the way you wanted

   

  Ashutosh Pathak
  Blog: http://catchcode.blogspot.com
  Please mark it as answer if it helps, as clicking on the button can save time of others :)
  MCP,MCAD,MCSD,MCTS
  • Marked as answer by texandude on May 17, 2012 06:46 PM
• • Reply
  

  texandude

  Member

  1 Points

  10 Posts

  Re: Display Multiple Counts in SQL on the Report

  May 17, 2012 05:06 AM|LINK

  Thanks Ashutosh.

  I'll try to use the codee the way you have explained.

  I was trying  something like this before your reply.

  Declare @ID int,

  Select @ID = Count(ID) as total1 FROM #temp1

  Print @ID

  Pring works in SSMS but not in Report.

  I have to use select @ID as ID

  Thanks for your help. Appreciate it.

• • Reply
  

  kidshaw

  Participant

  1158 Points

  252 Posts

  Re: Display Multiple Counts in SQL on the Report

  May 17, 2012 08:33 AM|LINK

  Using a matrix instead of a table might work, for what it's worth?

  Seems you have a solution though

  If it helps, mark as answer - your approval helps with my recovery ;)
• • Reply
  

  texandude

  Member

  1 Points

  10 Posts

  Re: Display Multiple Counts in SQL on the Report

  May 17, 2012 01:19 PM|LINK

  that's good to know. have not used matrix before but will give it a shot 

  gracias!

• • Reply
  

  texandude

  Member

  1 Points

  10 Posts

  Re: Display Multiple Counts in SQL on the Report

  May 17, 2012 03:32 PM|LINK

  Hi I get the Resultset the way I wanted but when I place the Fields on the Report in TexttBox

  I get an error.'

  The defination of the Report '/ID' is invalid etc etc.

  Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope.

  Build complete -- 1 errors, 0 warnings

  Textbox1 Expression value  =Sum(Fields!ID.Value,"DataSet1")

  Can you please let me know why I'm getting an error.

  Thanks.

   

   

   

   

   

  • Edited by texandude on May 17, 2012 03:42 PM
• • Reply
  

  texandude

  Member

  1 Points

  10 Posts

  Re: Display Multiple Counts in SQL on the Report

  May 17, 2012 06:46 PM|LINK

  I finally figured it out.

  Thanks.