CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008-r2
rigamonk
rigamonk

Reputation: 1181

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 it in the designers query builder, it times out. However, when I hardcode the parameters into the query builder, it works. The whole thing works fine in Management Studio

Why is it when I provide the parameters in the query within query designer, it runs, but when i fill in the parameters via the report it does not? This is what i'm putting in the designers query builder to make it work quickly for testing.

Declare @start varchar(20),
        @end varchar(20),
        @plantid varchar(10)

        set @start='07/13/2015'
        set @end = '07/17/2015'
        set @plantid = 'mnp'

Select Division as 'Division', SUM(SALESQTY) as 'salesQTY',rtrim(Ltrim(salesline.itemgroupid)) as 'itemGroup'
FROM MiscReportTables.dbo.PlantDivisions 
inner join prodtable on prodtable.dimension2_ = MiscReportTables.dbo.PlantDivisions.Division
inner join SalesLine on SalesLine.InventrefId = ProdTable.ProdiD
WHERE PlantID IN (@plantid)
and SCHEDDATE between @start and @end
Group By Division,salesLine.itemgroupid

EDIT: I made this into a stored procedure and am running it and it takes a long time (60+ minutes) even in management studio.I added the option (recompile) to it to stop parameter sniffing.

Upvotes: 0

Views: 2343

Answers (2)

Liam Fawcett
Liam Fawcett

Reputation: 1

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..)

Upvotes: 0

Hannover Fist
Hannover Fist

Reputation: 10880

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 @start1 varchar(20),
    @end1 varchar(20),
    @plantid1 varchar(10)

    set @start1 =@start 
    set @end1 = @end 
    set @plantid1 = @plantid

Select Division as 'Division', SUM(SALESQTY) as 'salesQTY',rtrim(Ltrim(salesline.itemgroupid)) as 'itemGroup'
FROM MiscReportTables.dbo.PlantDivisions 
inner join prodtable on prodtable.dimension2_ = MiscReportTables.dbo.PlantDivisions.Division
inner join SalesLine on SalesLine.InventrefId = ProdTable.ProdiD
WHERE PlantID IN (@plantid)
and SCHEDDATE between @start and @end
Group By 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.

Upvotes: 1

Related Questions