sql-server - SQL Report Builder：列可见性怪癖
问题描述
呜呜……这很奇怪。我正在通过换出数据集来彻底检查报告。我的参数之一利用列可见性来隔离不同的视图。在我大修之前，它工作得很好，我并没有弄乱它或任何东西（我非常小心能够替换我的数据集并且仍然像以前一样工作，这是一个例外）。
这是我的“正常”视图报告。参数选项是 Sales、Collected 和 Both，并按月份分组，如您所见：
当我单独选择已收集或销售（在此示例中为已收集）时，它会删除我的 10 月组和总计列：
这就是它变得奇怪的地方......如果我再次单击查看报告，我可以在加载时看到我想查看的数据，但它不会像它应该的那样吐出它。
然而，还有更奇怪的。我发现如果我将日期选择更改为不实际使用九月，那么它工作正常。（@Start 和 @End 日期变量：我可以选择 @Start 作为八月，@End 作为十月，没有问题。但如果 @Start 或 @End 实际上指定了九月，那就是我的问题发生的地方）
在 SQL 查询中，9 月的数据没有什么特别之处，会导致问题，但会发布代码以防有人想查看验证。
USE PDI_Warehouse_2049_01
DECLARE @Salesperson VARCHAR(30)
,@Start DATE
,@End DATE
,@SalesType VARCHAR(30)
,@Customer VARCHAR(30)
SET @Salesperson = 'all'
SET @SalesType = 'all' --Options are CFN, Bulk, or All
SET @Start = '9/1/2019'
SET @End = '9/7/2019'
SET @Customer = 'all'
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
IF OBJECT_ID('tempdb..#temp_collectedGM') IS NOT NULL
DROP TABLE #temp_collectedGM;
IF OBJECT_ID('tempdb..#getsalesperson') IS NOT NULL
DROP TABLE #getsalesperson
IF OBJECT_ID('tempdb..#CFN') IS NOT NULL
DROP TABLE #CFN
IF OBJECT_ID('tempdb..#CFN_Bulk') IS NOT NULL
DROP TABLE #CFN_Bulk;
--------------------------------------
--GET CFN VALUES
--------------------------------------
----1.First query gets list of salespeople associated to customers
SELECT DISTINCT c.Cust_Alias
,s.SalesPerson_Name
INTO #getsalesperson
FROM PDICompany_2049_01..CP_Transactions cp(NOLOCK)
INNER JOIN PDICompany_2049_01..Customers c(NOLOCK) ON CPTrn_Cust_Key = Cust_Key
LEFT JOIN PDICompany_2049_01..Customer_Sales_Info csi(NOLOCK) ON csi.CustSls_Cust_Key = c.Cust_Key
AND CustSls_Salesperson_Key IS NOT NULL
LEFT JOIN PDICompany_2049_01..SalesPersons s ON csi.CustSls_Salesperson_Key = s.SalesPerson_Key
WHERE cp.CPTrn_Tran_Type != 0
-----2.Second query captures CFN values joining with #getsalesperson to bring it all together
-----NOTE: The GP originates as Sales GP. As it is collected, it remains the same value for Sales GP but becomes Collected GP as well.
SELECT DISTINCT c.Cust_Alias
,'CFN' AS [Prod Type]
,gsp.SalesPerson_Name
,case
when datepart(year, ch.Chg_Last_Rcpt_Date) between datepart(year, @Start) AND datepart(year, @End) then datepart(year, ch.Chg_Last_Rcpt_Date)
when datepart(year, cp.CPTrn_Business_Date) between datepart(year, @Start) AND datepart(year, @End) then datepart(year, cp.CPTrn_Business_Date)
end as 'year'
,case
when datepart(month, ch.Chg_Last_Rcpt_Date) between datepart(month, @Start) AND datepart(month, @End) then datepart(month, ch.Chg_Last_Rcpt_Date)
when datepart(month, cp.CPTrn_Business_Date) between datepart(month, @Start) AND datepart(month, @End) then datepart(month, cp.CPTrn_Business_Date)
end as 'month'
,sum(CPTrn_Units) 'Gallons'
,CASE
WHEN si.Site_ID BETWEEN '2000'
AND '2003'
THEN sum(cp.CPTrn_Pretax_Ext_Amt) - sum(CPTrn_Ext_Cost)
WHEN si.Site_ID BETWEEN '2900'
AND '2999'
THEN sum(cp.CPTrn_Total_Ext_Amt) - sum(CPTrn_Ext_Cost)
END AS 'GP'
,sum(cp.CPTrn_Total_Ext_Amt) 'Sales w/tax'
,p.Prod_ID
,ch.Chg_Ref_No
,CASE
WHEN (datepart(year, ch.Chg_Last_Rcpt_Date) BETWEEN datepart(year, @Start)
AND datepart(year, @End)
AND datepart(month, ch.Chg_Last_Rcpt_Date) BETWEEN datepart(month, @Start)
AND datepart(month, @End)
or datepart(year, cp.CPTrn_Business_Date) BETWEEN datepart(year, @Start)
AND datepart(year, @End)
AND datepart(month, cp.CPTrn_Business_Date) BETWEEN datepart(month, @Start)
AND datepart(month, @End))
AND Chg_Status = 1
THEN '1'
WHEN datepart(year, cp.CPTrn_Business_Date) BETWEEN datepart(year, @Start)
AND datepart(year, @End)
AND datepart(month, cp.CPTrn_Business_Date) BETWEEN datepart(month, @Start)
AND datepart(month, @End)
--AND Chg_Status = 0
THEN '0'
END AS 'status'
INTO #CFN
FROM PDICompany_2049_01..CP_Transactions cp(NOLOCK)
INNER JOIN PDICompany_2049_01..Customers c(NOLOCK) ON CPTrn_Cust_Key = Cust_Key
INNER JOIN PDICompany_2049_01..Products p(NOLOCK) ON cp.CPTrn_Prod_Key = Prod_Key
LEFT JOIN PDICompany_2049_01..CP_Billing_Details CPBD(NOLOCK) ON CPBd.CPBillDtl_CPTrn_Key = cp.CPTrn_Key
AND CPTrn_Cust_Key = CPBD.CPBillDtl_Cust_Key
AND CPBD.CPBillDtl_Rec_Type = 1
LEFT JOIN PDICompany_2049_01..Customer_Locations cl(NOLOCK) ON c.Cust_WhPrcNtc_Def_CustLoc_Key = cl.CustLoc_Key
AND ((CustLoc_Type & 2) <> 0)
LEFT JOIN #getsalesperson gsp ON c.Cust_Alias = gsp.Cust_Alias
LEFT JOIN PDICompany_2049_01..Charges ch(NOLOCK) ON ch.Chg_Ref_No = cpbd.CPBillDtl_Invoice_No
LEFT JOIN PDICompany_2049_01..Sites si(NOLOCK) ON si.Site_Key = cp.CPTrn_Site_Key
WHERE cp.CPTrn_Tran_Type != 0
AND CPBillDtl_Invoice_No IS NOT NULL
AND Salesperson_Name = CASE
WHEN @Salesperson IN (Salesperson_Name)
THEN @Salesperson
WHEN @Salesperson = 'All'
THEN Salesperson_Name
END
AND c.Cust_Alias = CASE
WHEN @Customer IN (c.Cust_Alias)
THEN @Customer
WHEN @Customer = 'All'
THEN c.Cust_Alias
END
and ((datepart(year, ch.Chg_Last_Rcpt_Date) BETWEEN datepart(year, @Start) AND datepart(year, @End)
AND datepart(month, ch.Chg_Last_Rcpt_Date) BETWEEN datepart(month, @Start) AND datepart(month, @End))
or (datepart(year, cp.CPTrn_Business_Date) BETWEEN datepart(year, @Start) AND datepart(year, @End)
AND datepart(month, cp.CPTrn_Business_Date) BETWEEN datepart(month, @Start) AND datepart(month, @End)))
and CPBillDtl_Invoice_No is not null
and ch.Chg_Ref_No is not null
GROUP BY c.Cust_Alias
,gsp.SalesPerson_Name
,datepart(year, ch.Chg_Last_Rcpt_Date)
,datepart(month, ch.Chg_Last_Rcpt_Date)
,datepart(year, cp.CPTrn_Business_Date)
,datepart(month, cp.CPTrn_Business_Date)
,ch.Chg_Ref_No
,p.Prod_ID
,cp.CPTrn_Key
,CPTrn_Ext_Cost
,si.Site_ID
,ch.Chg_Status
ORDER BY ch.Chg_Ref_No
-------------------------------------------------------------------
----GET BULK VALUES
-------------------------------------------------------------------
SELECT cu.Customer_Alias
,'Bulk' AS 'Prod Type'
,sp.SalesPerson_Name
,case
when datepart(year, ch.Chg_Last_Rcpt_Date) between datepart(year, @Start) AND datepart(year, @End) then datepart(year, ch.Chg_Last_Rcpt_Date)
when datepart(year, ch.chg_invoice_date) between datepart(year, @Start) and datepart(year, @End) then datepart(year, ch.chg_invoice_date)
end as 'year'
,case
when datepart(month, ch.Chg_Last_Rcpt_Date) between datepart(month, @Start) and datepart(month, @End) then datepart(month, ch.Chg_Last_Rcpt_Date)
when datepart(month, ch.chg_invoice_date) between datepart(month, @Start) and datepart(month, @End) then datepart(month, ch.chg_invoice_date)
end as 'month'
,CASE
WHEN p.Report_Group_Desc IN (
'diesel'
,'gasoline'
,'aviation'
,'def'
,'kerosene'
,'propane'
)
OR p.Product_Id IN (
'WEXALTERNATIVE'
,'WEXDEF'
,'WEXDSL'
,'WEXGAS'
)
OR p.Product_Id = CASE
WHEN cu.Customer_Alias = 'ONCOR ELECTRIC - WEX'
THEN 'WEXPURCHASES'
WHEN cu.Customer_Alias = 'ONCOR ELECTRIC - WEX'
THEN 'WEXREBATE'
END
THEN sum(isnull(sales_qty, 0))
ELSE 0
END AS 'total gallons'
,sum(isnull(Ext_Retail_Base, 0) + isnull(Ext_Retail_Freight, 0)) - sum(isnull(Ext_Cost_Base, 0) + isnull(Ext_Cost_Freight, 0)) 'Sales GM'
,sum(isnull(Ext_Retail_Base, 0) + isnull(Ext_Retail_Freight, 0)) 'Sales'
,p.Product_Id
,fwsf.Invoice_Number
,CASE
WHEN (datepart(year, ch.Chg_Last_Rcpt_Date) BETWEEN datepart(year, @Start)
AND datepart(year, @End)
AND datepart(month, ch.Chg_Last_Rcpt_Date) BETWEEN datepart(month, @Start)
AND datepart(month, @End)
or
datepart(year, ch.Chg_Invoice_Date) BETWEEN datepart(year, @Start)
AND datepart(year, @End)
AND datepart(month, ch.Chg_Invoice_Date) BETWEEN datepart(month, @Start)
AND datepart(month, @End))
AND Chg_Status = 1
THEN '1'
WHEN datepart(year, ch.Chg_Invoice_Date) BETWEEN datepart(year, @Start)
AND datepart(year, @End)
AND datepart(month, ch.Chg_Invoice_Date) BETWEEN datepart(month, @Start)
AND datepart(month, @End)
--AND Chg_Status = 0
THEN '0'
END AS 'status'
INTO #temp
FROM PDI_Warehouse_2049_01.dbo.Fuel_Wholesale_Sales_Fact fwsf
JOIN PDI_Warehouse_2049_01.dbo.calendar c ON fwsf.Calendar_key = c.calendar_key
JOIN Salesperson sp ON sp.SalesPerson_Key = fwsf.Salesperson_Key
JOIN Customer cu ON fwsf.Customer_Key = cu.Customer_Key
JOIN PDI_Warehouse_2049_01..Petroleum_Product p ON p.Petroleum_Product_Key = fwsf.Petroleum_Product_Key
JOIN PDICompany_2049_01..Charges ch ON ch.Chg_Ref_No = fwsf.Invoice_Number
WHERE sp.Salesperson_Name = CASE
WHEN @Salesperson IN (Salesperson_Name)
THEN @Salesperson
WHEN @Salesperson = 'All'
THEN Salesperson_Name
END
AND ((datepart(year, ch.Chg_Last_Rcpt_Date) BETWEEN datepart(year, @Start) AND datepart(year, @End)
and datepart(month, ch.Chg_Last_Rcpt_Date) BETWEEN datepart(month, @Start) AND datepart(month, @End))
or
(datepart(year, ch.Chg_Invoice_Date) BETWEEN datepart(year, @Start) AND datepart(year, @End)
AND datepart(month, ch.Chg_Invoice_Date) BETWEEN datepart(month, @Start) AND datepart(month, @End)))
and Invoice_Number is not null
-- AND isnull(fwsf.sales_qty, 0) != 0
AND cu.Customer_Alias = CASE
WHEN @Customer IN (cu.Customer_Alias)
THEN @Customer
WHEN @Customer = 'All'
THEN cu.Customer_Alias
END
GROUP BY sp.SalesPerson_Name
,datepart(year, ch.Chg_Last_Rcpt_Date)
,datepart(month, ch.Chg_Last_Rcpt_Date)
,datepart(year, ch.chg_invoice_date)
,datepart(month, ch.chg_invoice_date)
,p.Report_Group_Desc
,p.Product_Id
,Invoice_Number
,cu.Customer_Alias
,p.Product_Id
,ch.Chg_Status
HAVING sum(isnull(sales_qty, 0)) != 0
---------------------------------------
----------final result--
-----------------------------------------
CREATE TABLE #CFN_Bulk (
Customer_Alias VARCHAR(50)
,[Prod Type] VARCHAR(5)
,Salesperson_Name VARCHAR(50)
,year int
,month int
,[total gallons] FLOAT
,[Sales GM] FLOAT
,Sales FLOAT
,[Product_Id] VARCHAR(30)
,Invoice_Number VARCHAR(30)
,Chg_Status BIT
);
INSERT INTO #CFN_Bulk
SELECT *
FROM #temp t
UNION ALL
SELECT *
FROM #CFN c;
SELECT Customer_Alias
,[Prod Type] 'Prod_Type'
,Salesperson_Name
,year
,month
,CASE
WHEN Chg_Status = 1
THEN sum([total gallons])
END AS 'Collected_gallons'
,CASE
WHEN Chg_Status = 0
OR Chg_Status = 1
THEN sum([total gallons])
END AS 'Sold_gallons'
,Sales
,[Product_Id]
,Invoice_Number
,Chg_Status
,CASE
WHEN Chg_Status = 1
THEN sum([Sales GM])
END AS 'Collected_GM'
,CASE
WHEN Chg_Status = 0
OR Chg_Status = 1
THEN sum([Sales GM])
END AS 'Sales_GM'
FROM #CFN_Bulk
WHERE [Prod Type] = CASE
WHEN @SalesType = 'CFN'
THEN 'CFN'
WHEN @SalesType = 'bulk'
THEN 'Bulk'
WHEN @SalesType = 'All'
THEN [Prod Type]
END
AND Salesperson_Name = CASE
WHEN @Salesperson IN (Salesperson_Name)
THEN @Salesperson
WHEN @Salesperson = 'All'
THEN Salesperson_Name
END
AND Customer_Alias = CASE
WHEN @Customer IN (Customer_Alias)
THEN @Customer
WHEN @Customer = 'All'
THEN Customer_Alias
END
AND Salesperson_Name != 'undefined'
GROUP BY Customer_Alias
,[Prod Type]
,Salesperson_Name
,year
,month
,[total gallons]
,Sales
,[Product_Id]
,Invoice_Number
,Chg_Status
ORDER BY Invoice_Number
任何关于可能导致这种情况的想法将不胜感激。我真的对此感到很困惑。
更新 1：列可见性表达式非常简单。这是 Collected 列的示例（因为表达式是为隐藏视图预先确定的）：
=Parameters!View2.Value = "Sales"
因此，当我从下拉列表中选择 Sales 时，它会隐藏此 Collected 列。我认为我的任何逻辑/表达式都不会有任何问题，因为如前所述，它与我的上一个数据集和这个新数据集完美配合，如果我避免直接提到 9 月，它就可以工作。并不是说这一定是一个正确的断言，只是一个合乎逻辑的断言。
解决方案
推荐阅读
- reactjs - Chrome 没有详细说明为什么 React Elements 需要很长时间才能在 User Timing 中呈现
- c++ - 无法理解它是如何工作的？
- c++ - 如何在 C++ 中将 base64 编码图像加载为 SDL 纹理？
- symfony - omines/datatables 捆绑包不会搜索日期时间列
- ios - 在 swift 4 中显示 Firebase 通知弹出窗口
- linux - lsof 可以在 alpine linux 上显示开放端口吗？
- c++ - 如何评估包含代码的字符串并在 C++ 中运行它？
- javascript - 在 push() 中插入子节点数组的问题
- lambda - What is the purpose of trailing lambda syntax (Kotlin)?
- assembly - NASM floating point - invalid combination of opcode and operands