Ssrs Report Query Not Working, But Does When Hardcoded Parameters

I have an SSRS report that has 3 parameters: @start (datetime) @end (datetime) @plantid (string from an external query) When I run the report regularly, it times out. When i run i

Solution 1:

Have you tried assigning your parameters to a different variable in the query?

I forgot what the theory was but this worked for me before in a similar instance.

Declare@start1varchar(20),
    @end1varchar(20),
    @plantid1varchar(10)

    set@start1=@startset@end1=@endset@plantid1=@plantidSelect Division as'Division', SUM(SALESQTY) as'salesQTY',rtrim(Ltrim(salesline.itemgroupid)) as'itemGroup'FROM MiscReportTables.dbo.PlantDivisions 
innerjoin prodtable on prodtable.dimension2_ = MiscReportTables.dbo.PlantDivisions.Division
innerjoin SalesLine on SalesLine.InventrefId = ProdTable.ProdiD
WHERE PlantID IN (@plantid)
and SCHEDDATE between@startand@endGroupBy Division,salesLine.itemgroupid

Another thing to try (if you have one) is to deploy to Report Server and running it from there. Some reports take forever in Visual Studio but run quick in RS.

Solution 2:

I had a similar issue, used a workaround which was to create separate parameters, but having additinal paramters is not feasible for usability. So I then changed the parameters to have default values which use the values from the dates passed in , this works! (will just need to have the second set of parameters hidden..)