I have few lines query which has multiple count statements. I have created Stored procedure for the select count statements and it is giving results when i run it on SSMS but when i use the stored procedure in SSRS it is giving only 1st query output. How do I get other queries output too while using ssrs?
Code from Comments:
select count(*) as Field1
from #TempTable1;
select count(*) as Field2
from #TempTable2;
select count(distinct ANo) as F3
from #TempTable1;
select count(distinct BNo) as F4
from #TempTable2;
select count(*) as F5
from Table1
where InsertDate >= StartDate
and InsertDate <= EndDate
and value1 >= 2
and TestField = 0;
select count(*) as F6
from Table1
where InsertDate >= StartDate
and InsertDate <= EndDate
and value1 >= 2
and TestField = 1;
select count(*) as F7
from Table1
where InsertDate >= StartDate
and InsertDate <= EndDate
and value1 >= 2
and TestField = 0;
Finally Found the answer
Here is what I've done Declared local variables and used them as below in query
select @A= count() from Table1 Select @B= count() from Table 2
Select @A as Field1,@B as Field2
This gives the output in single row.