Bugmesh avatar image
Bugmesh asked

ReportServer report returns only 1 row

I could use a sanity check please. I am working on a report to display the status of DR testing across the enterprise. When I run the code below in a Query Analyzer it returns 238 rows (which is what I would expect. but when it is run from the Report Server I get a single row. I have tried adding/modifying/deleting GROUP BY statements with no change in the result set. Would someone be able to eyeball this and let me know wht the heck is missing? I appreciate your anticipated assistance and support. SELECT z.ServerName, v.Environment, z.DiskName, z.DskTotalSizeInGB, a.Project, a.Status FROM (SELECT y.ServerName, y.DiskName, y.Label,y.DskTotalSizeInGB,y.DateAdded FROM (SELECT ServerName, MAX(DateAdded) AS Rundate FROM Svr.DiskInfo GROUP BY ServerName) AS x INNER JOIN CentralDB.Svr.DiskInfo AS y ON x.Rundate = y.DateAdded AND x.ServerName = y.ServerName)z INNER JOIN (SELECT ServerName, InstanceName, Environment, [Description], BusinessOwner FROM CentralDB.Svr.ServerList) v ON z.ServerName = v.ServerName JOIN CentralDB.dbo.Disaster_Recovery a ON z.ServerName = a.ServerName WHERE a.Project IN(@Project) AND z.DiskName IN (@DiskName) AND z.ServerName IN (@ServerName) AND v.Environment IN (@Environment) ORDER BY ServerName
sql queryreportserver
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is the report server the same server you are executing it against within the query analyzer? Also, are you running the query from the report itself or directly against the report server in QA?
0 Likes 0 ·
I am connecting to the database through SSMS and opening a new query and then running the code sans parameters. In the same vein, the Reporting box connects to the same database. I did about 8 of these last week and this is the only one that is causing consternation. I thought it might be about group clauses (or lack of them) and have played around with these in several combinations with no joy. Thank You again for your assistance and support
0 Likes 0 ·
Is that server name listed in all of the appropriate tables? You might want to switch each join to a LEFT join (one at a time) to see if the joins are filtering things out.
0 Likes 0 ·
Good point. One of the tables in this report is not used in the others. I will start with that one. Thanks
0 Likes 0 ·

0 Answers


Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.