SSRS - missing data in the table in my report even though no missing data in sql server

Question

I have a stored procedure returning data like:

Id   Name   fromTable
1    Alison Table1
2    Gary   Table2
3    Jack   Table1
4    John   Table3

by the code:

Select Id, Name, fromTable='Table1' from Table1
union
Select Id, Name, fromTable='Table2' from Table2
union
Select Id, Name, fromTable='Table3' from Table3

I created a report in SSRS 2008, created Dataset1 which is connected to this SP and I am inserting a table to my SSRS report which is connected to Dataset1. However, here I only see the data coming from Table1 (Alison and Gary). I cannot see the data coming from Table2 and Table3. I could not understand why it is happening and how to fix it, because seems like query is working well in the SQL Server 2008. Any help would be appreciated.

Check if your report is running against cached data, when run your report is generated press the Refresh button in the Report Viewer parameter pane. Also note `UNION` operator return Distinct rows, use UNION ALL to get all rows. — alejandro zuleta, Aug 31 '16 at 13:39
neither refreshing nor changing UNION into UNION ALL did not solve :( — Eray Balkanli, Aug 31 '16 at 13:44
Did you check if your dataset/tablix is affected by a filter? — alejandro zuleta, Aug 31 '16 at 13:50
Try adding a textbox and use this expression `=COUNTROWS("DataSetName")` to determine the number of rows returned by your dataset. Replace `DataSetName` by the actual name of yours. — alejandro zuleta, Aug 31 '16 at 14:21
I tried, it says 2 :( So 2 is there, and other 2 is missing, they never come to SSRS. why? — Eray Balkanli, Aug 31 '16 at 14:25
Check your query using SSMS and see how many rows it returns. Also try recreating your dataset maybe you set some property without intention which is causing filtering or unexpected behaviour. — alejandro zuleta, Aug 31 '16 at 14:29

score 0 · Answer 1 · answered Aug 31 '16 at 14:31

0

Hey I will do couple of things here.

I will run my query in query designer in SSRS(Visual studio) after clicking refresh fields. If you are not getting all data here you have an issue in your query.
I will delete rdl data file, to make sure you are not holding any cache.

answered Aug 31 '16 at 14:31

Hari

192
2
12

They did not fix it :( – Eray Balkanli Aug 31 '16 at 14:34
Are you getting only 2 rows in your query designer too? – Hari Aug 31 '16 at 14:36

score 0 · Answer 2 · answered Aug 31 '16 at 14:44

0

I tried the sample data you gave i can see all the data in my SSRS 2008. i will suggest you delete the report and re-create it making sure that the report is pointed to the correct data source

answered Aug 31 '16 at 14:44

JonWay

1,585
17
37

score 0 · Answer 3 · answered Aug 31 '16 at 20:55

First, validate your data in sql server. Sometimes these SP's do not do exactly what we intended them to do, although your query looks simple enough I would still double check. If that output is what you need then I would suggest just rewriting your ssrs ensuring that your datasource is correct and everything else is jiving. SSRS is super clunky!!!

score 0 · Answer 4 · edited Sep 01 '16 at 00:39

to test cache issue, make any notable change in SQL to see if it's coming through.
to test any rdl issues made any notable change in let say Header and run report
if those 2 test passed OK, then check dataset properties (Filters)
if this passed then check Tablix properties (filters), then tablix raw visibility
Check dataset properties (Query type: make sure if it's sp name = corrrect).
Check data SOURCE ! so it points to the right server / db

Is this yours rdl or somebody's else?

J

score 0 · Answer 5 · answered Aug 31 '16 at 22:06

0

This is very handy solution to add to your VS:How to add Clear Cache from VS/Tools

answered Aug 31 '16 at 22:06

Jian

1

SSRS - missing data in the table in my report even though no missing data in sql server

5 Answers5