Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Report not working on Report Server Expand / Collapse
Author
Message
Posted Tuesday, May 21, 2013 12:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 3:04 PM
Points: 27, Visits: 85
I'm not sure if this is the right place to post this one, but maybe someone can help.

I have a report that I built in Report Builder and it works perfect. I have uploaded it to the Report Server, but when I try to view it, I get an error with the query:

An error occurred during client rendering.
An error has occurred during report processing.
Query execution failed for dataset 'DataSet1'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors

Here is the query that the report is running:

select
HourOfTheDay,
sum(case when TimeReceived between @startdate and @enddate then NumberOfOrdersInBatch else 0 end) as NumberOfOrders,
AVG(case when TimeReceived between @startdate and @enddate then TimeToProcess else 0 end) as AverageTime,
sum(case when TimeReceived between @startdate2 and @enddate2 then NumberOfOrdersInBatch else 0 end) as PrvWkNumberOfOrders,
AVG(case when TimeReceived between @startdate2 and @enddate2 then TimeToProcess else 0 end) as PrvWkAverageTime
from
info.batchstats with (nolock)
where
TimeReceived between @startdate and @enddate or
TimeReceived between @startdate2 and @enddate2
group by
HourOfTheDay
order by
houroftheday

Any help is appreciated.
Post #1455168
Posted Tuesday, May 21, 2013 1:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 08, 2013 7:16 PM
Points: 221, Visits: 132
Does your Report Server have access to the database in the data connection of DataSet1?

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/
You can also follow my twitter account to get daily updates: @BLantz2455
Post #1455204
Posted Tuesday, May 21, 2013 1:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 3:04 PM
Points: 27, Visits: 85
Yes it does. It is using a shared data source that works with other reports. I have tried changing it, just to test, and it gives an error that the data source could not be reached. It seems to me like a problem with the query, but I can't figure out what that would be.
Post #1455205
Posted Tuesday, May 21, 2013 2:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:16 PM
Points: 5,986, Visits: 6,931
Run a profiler trace against the target box and see what the query is sending down explicitly. It might be a simple difference in the dateformat defaults on the two boxes.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1455216
Posted Wednesday, May 22, 2013 3:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:40 PM
Points: 177, Visits: 978
It is very easy enable report errors as well. Not sure if you have to start and stop the service, though.

http://msdn.microsoft.com/en-us/library/aa337165(v=sql.105).aspx
Post #1455736
Posted Tuesday, May 28, 2013 3:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:21 AM
Points: 2,797, Visits: 3,079
Personally, I like to write report SQL within a Stored Procedure, and just call the SP within the report.

This allows me to test that the correct data is being retrieved, independantly of the report. It also helps me realise that any problems with rendering that data are due to the report design. Both things tend to keep me on the right path when dealing with SSRS.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 25 March 2014: now over 28,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1457242
Posted Wednesday, May 29, 2013 7:27 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 7:26 AM
Points: 745, Visits: 2,009
Have you checked permissions on the table? I ran into this error yesterday and it was due to the fact that the SQL user we use to connect to the database for reports didn't have permissions to the view the new report was based on. Once I added the permissions, everything worked fine.



The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.
Post #1457717
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse