SSRS 报告查询不起作用，但在硬编码参数时起作用

问题描述

我有一个包含 3 个参数的 SSRS 报告:

I have an SSRS report that has 3 parameters:

@start (datetime)
@end (datetime)
@plantid (string from an external query)

当我定期运行报告时，它会超时.当我在设计器查询构建器中运行它时，它超时了.但是，当我将参数硬编码到查询构建器中时，它可以工作.整个过程在 Management Studio 中运行良好

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

我把它变成了一个存储过程并正在运行它，即使在管理工作室它也需要很长时间(60 分钟以上).我向它添加了 option (recompile) 以停止参数嗅探.>

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.

推荐答案

您是否尝试过将参数分配给查询中的不同变量?

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

要尝试的另一件事(如果您有的话)是部署到报表服务器并从那里运行它.一些报告在 Visual Studio 中需要很长时间，但在 RS 中运行很快.

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.

这篇关于SSRS 报告查询不起作用，但在硬编码参数时起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！