返回到SSRS的数据与SSMS查询不同

Question

我完全被难住了。

我有下面的SQL查询，它在SSMS中运行得很好，然后我将查询添加到SSRS中，一切都很奇怪。

一些数据行在没有明显原因的情况下被省略，在报告上反映时，WABill和WACost中的值也会发生变化。

我已经清除了projects文件夹中的rdl.data，没什么好运气的。

我有个隐秘的怀疑这可能和这个组织有关

报告设置有定义的日期参数，日期是从日期选择器中选择的。

我将SQL脚本作为文本添加到DataSet上。

我将感谢你的帮助。

Declare @ToDate DateTime
Declare @FromDate DateTime
Declare @WACost as Numeric (18,2)
Declare @WABill as Numeric (18,2)


Set @FromDate = '20160501'
Set @ToDate = '20160510'

Set @WACost = (
              Select
                    sum(dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) + Sum((dbo.FleetData.OwnCost + dbo.FleetData.OpCost)
                    * dbo.DataInput.Hrs)
              From       dbo.DataInput INNER JOIN
                         dbo.HRData ON dbo.DataInput.HRId = dbo.HRData.HRId INNER JOIN
                         dbo.FleetData ON dbo.FleetData.FleetId = dbo.DataInput.FleetId INNER JOIN
                         dbo.WorkCodes ON dbo.DataInput.WorkCodeId = dbo.WorkCodes.WorkCodeID
             WHERE ProdDate >= @FromDate AND ProdDate <= @ToDate and dbo.workcodes.WorkCode = 101
              ) 

Set @WABill = (
              Select
                sum(dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill) + Sum((dbo.FleetData.OwnBill + dbo.FleetData.OpBill)
                * dbo.DataInput.Hrs)
              From dbo.Datainput Inner Join
                   dbo.FleetData ON dbo.FleetData.FleetId = dbo.DataInput.FleetId INNER JOIN
                   dbo.HRData ON dbo.DataInput.HRId = dbo.HRData.HRId INNER JOIN
                   dbo.WorkCodes ON dbo.DataInput.WorkCodeId = dbo.WorkCodes.WorkCodeID

              WHERE ProdDate >= @FromDate AND ProdDate <= @ToDate and dbo.WorkCodes.WorkCode = 101
              )

SELECT     

             @WACost as WACostTotal, @WABill as WABillTotal,

              dbo.DataInput.DataInputID as DataInpID, dbo.DataInput.ProdDate as ProdDate, dbo.WorkCodes.WorkCode as WCode, dbo.DataInput.Hrs as ProdHrs, dbo.DataInput.Loads as ProdLoads, 
              dbo.DataInput.Fuel as ProdFuel, dbo.FleetData.FleetNo as ProdFltNo, dbo.JobCodes.JobCode, 
              dbo.FleetType.EquipmentType as EqType, dbo.FleetModel.ModelName as Model, dbo.Sites.SiteName as ProdSiteName, 
              dbo.JobCodes.JobDetails as JCDet, dbo.JobCodes.BillMeth as BillM,
              dbo.FleetData.OwnCost as FltOwnCost, dbo.fleetdata.OpCost as FltOpCost, dbo.fleetdata.OwnBill as FltOwnBill,
              dbo.FleetData.OpBill as FltOpBill,

              (dbo.FleetModel.ModelSize * dbo.DataInput.Loads) as ProdBCM,
              dbo.HRData.EmpNo as HREmp, dbo.HRData.RatePH as EmpCost, dbo.HRData.RateBill as EmpBill, 
              dbo.DataInput.ShiftHrs as ProdShHrs, dbo.FleetModel.ModelSize as MSize, 
              (dbo.DataInput.Fuel * dbo.JobCodes.FuelRate) as ProdFuelCost,
              dbo.budget.BudDate as BdgDate, dbo.Budget.RevBCM as BdgRevBCM, dbo.Budget.PricePerBCM as BdgBCMPrice,
              dbo.budget.BCMValue as BdgBCMVal, dbo.budget.SalWage as BdgSalWage, dbo.budget.Fuel as FuelBdg, 
              dbo.budget.PG0 as PGZero, dbo.budget.PG as PGCP,
              dbo.budget.siteId as BudgSite,

              --BILLING CALCS

              --PG Billing
              IIF(dbo.Workcodes.WorkCode  = 101,
              (dbo.Budget.SalWage * (1 + dbo.Budget.SalWagePerc)) + dbo.budget.PG0 + 
              (dbo.budget.PG * (1 + PGPerc)),0) as PGBill,

              --FleetBill
              IIF(dbo.Workcodes.WorkCode  = 101,
              (FleetData.OwnBill + FleetData.OpBill) * DataInput.Hrs,0) as FleetBill,

              --LabourCost
              iif(dbo.WorkCodes.WorkCode  = 101,
              (dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill),0) as LabourBill,  

              --Total Variable Bill
              iif(dbo.Workcodes.WorkCode  = 101,
               (dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill) + 
               (FleetData.OwnBill + FleetData.OpBill) * DataInput.Hrs,0) as TotalVarBill,

               --WABill
               iif(dbo.Workcodes.WorkCode  = 101,
               ((dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill) + 
               (FleetData.OwnBill + FleetData.OpBill) * DataInput.Hrs) / 
               @WABill * ((dbo.Budget.SalWage * (1 + dbo.Budget.SalWagePerc)) + dbo.budget.PG0 + 
              (dbo.budget.PG * (1 + PGPerc))) +
               ((dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill) + 
               (FleetData.OwnBill + FleetData.OpBill) * DataInput.Hrs) , 0) as WABill,

              --COST CALCS

              --PG Cost

              IIF(dbo.WorkCodes.WorkCode  = 101,
              (dbo.Budget.SalWage + dbo.budget.PG0 + dbo.budget.PG),0) as PGCost,

              --FLEETCOST
              IIF(dbo.WorkCodes.WorkCode  = 101,
              (FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs,0) as FleetCost,

              --LabourCost
              iif(dbo.WorkCodes.WorkCode  = 101,
              (dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH),0) as LabourCost,

               --Total Variables Cost
               iif(dbo.WorkCodes.WorkCode  = 101,
               (dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) + 
               (FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs,0) as TotalVarCost,

               --WAOnly
               iif(dbo.WorkCodes.WorkCode  = 101,
               ((dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) + 
               (FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs) / 
               @WACost * (dbo.Budget.SalWage + dbo.budget.PG0 + dbo.budget.PG),0)
               as WACalc,

               --WA
               iif(dbo.WorkCodes.WorkCode  = 101,
               ((dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) + 
               (FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs) / 
               @WACost * (dbo.Budget.SalWage + dbo.budget.PG0 + dbo.budget.PG) +
               ((dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) + 
               (FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs) , 0) as WACost


FROM            dbo.DataInput INNER JOIN
                         dbo.FleetData ON dbo.FleetData.FleetId = dbo.DataInput.FleetId INNER JOIN
                         dbo.FleetMake ON dbo.FleetData.FleetMakeId = dbo.FleetMake.FleetMakeId INNER JOIN
                         dbo.FleetModel ON dbo.FleetData.FleetModelId = dbo.FleetModel.ModelId INNER JOIN
                         dbo.FleetType ON dbo.FleetData.FleetTypeId = dbo.FleetType.FleetTypeId INNER JOIN
                         dbo.HRData ON dbo.DataInput.HRId = dbo.HRData.HRId INNER JOIN
                         dbo.Sites ON dbo.DataInput.SiteId = dbo.Sites.SiteId INNER JOIN
                         dbo.Budget ON dbo.Sites.SiteId = dbo.Budget.SiteId AND dbo.DataInput.Period = dbo.Budget.Period INNER JOIN
                         dbo.WorkCodes ON dbo.DataInput.WorkCodeId = dbo.WorkCodes.WorkCodeID INNER JOIN
                         dbo.JobCodes ON dbo.DataInput.JobCodeId = dbo.JobCodes.JobCodeId

WHERE ProdDate >= @FromDate AND ProdDate <= @ToDate

Group By dbo.DataInput.DataInputID, dbo.DataInput.DataInputID, dbo.DataInput.ProdDate, dbo.WorkCodes.WorkCode, dbo.DataInput.Hrs,
              dbo.DataInput.Loads, dbo.DataInput.Fuel, dbo.FleetData.FleetNo, dbo.JobCodes.JobCode, 
              dbo.FleetType.EquipmentType, dbo.FleetModel.ModelName, dbo.Sites.SiteName, 
              dbo.JobCodes.JobDetails, dbo.JobCodes.BillMeth, dbo.FleetData.OwnCost, dbo.fleetdata.OpCost, 
              dbo.fleetdata.OwnBill, dbo.FleetData.OpBill,dbo.HRData.EmpNo, dbo.HRData.RatePH, dbo.HRData.RateBill, 
              dbo.DataInput.ShiftHrs, dbo.FleetModel.ModelSize, dbo.budget.BudDate, dbo.Budget.RevBCM, dbo.Budget.PricePerBCM,
              dbo.budget.BCMValue, dbo.budget.SalWage, dbo.budget.Fuel, dbo.budget.PG0, dbo.budget.PG,
              dbo.budget.siteId, dbo.jobcodes.FuelRate, dbo.datainput.workcodeid, dbo.budget.SalWagePerc, dbo.Budget.PGPerc, 
              dbo.WorkCodes.WorkCodeID  

Order By dbo.DataInput.ProdDate

报告：

​

​

查看DataInputID的值时，该值应为1715.41

Answer 1

从哪里开始？检查/执行以下操作：

指向相同database?

• Using具有相同parameters

• Cleared的相同数据集是否已关闭缓存?检查报告不是filtering.

• Check

• 报告不是aggregating.