gpt4 book ai didi

sql - 基于SQL的报表数据预过滤

转载 作者:行者123 更新时间:2023-12-02 22:28:40 24 4
gpt4 key购买 nike

我在 Visual Studio 2008 中制作了一份基于 SQL 的报告并将其上传到 CRM。我指出您只能在“相关记录类型的表格”上运行它。我认为这会为您所在的当前记录生成 1 份报告。

事实并非如此。它为特定实体(Order)的每条记录生成一个报告,报告中每页1条记录。

我知道我必须使用数据预过滤(自动的还是特定的?),但是网上没有很好的例子,stackoverflow 上什至有一个线程,但我就是想不通。

线程链接:CRMAF Filtering in CRM 2011

SELECT FilteredSalesOrder.customerid AS x1, FilteredAccount.accountid AS x2, FilteredSalesOrderDetail.salesorderid AS x3, FilteredSystemUser.systemuserid AS x4,
FilteredProduct.productid AS x5, FilteredAccount.address1_city, FilteredAccount.address1_line1, FilteredAccount.address1_postalcode, FilteredAccount.name AS AccountName,
FilteredSalesOrder.ordernumber, FilteredSalesOrderDetail.quantity, FilteredSystemUser.fullname, FilteredProduct.fmcg_erpreference, FilteredProduct.name AS ProductName
FROM FilteredSalesOrder INNER JOIN FilteredAccount
ON FilteredSalesOrder.customerid = FilteredAccount.accountid INNER JOIN FilteredSalesOrderDetail
ON FilteredSalesOrderDetail.salesorderid = FilteredSalesOrder.salesorderid INNER JOIN FilteredProduct
ON FilteredProduct.productid = FilteredSalesOrderDetail.productid INNER JOIN FilteredSystemUser
ON FilteredSystemUser.systemuserid = FilteredSalesOrder.ownerid

预过滤数据仅使用当前订单:

  • 销售订单表使用 CRMAF_,就够了吗?

FROM FilteredSalesOrder AS CRMAF_FilteredSalesOrder INNER JOIN FilteredAccount

  • 所有表使用 CRMAF_?

编辑:

这是我在 FilteredSalesOrder 上使用 CRMAF_ 的新的完整 SQL。但可惜它不起作用。它仍然只给我机会针对所有记录运行它,从而生成所有存在的销售订单(幸运的是没有那么多)。

SELECT CRMAF_FilteredSalesOrder.customerid AS x1, CustomerAccount.accountid AS x2, 
FilteredSalesOrderDetail.salesorderid AS x3, FilteredSystemUser.systemuserid AS x4,
FilteredProduct.productid AS x5,CRMAF_FilteredSalesOrder.fmcg_supplierid as x6,
CustomerAccount.address1_city as customerCity, CustomerAccount.address1_line1 as
customerStreet1, CustomerAccount.address1_postalcode as customerPostalCode,
CustomerAccount.name AS CustomerAccountName, CRMAF_FilteredSalesOrder.ordernumber,
FilteredSalesOrderDetail.quantity, FilteredSystemUser.fullname,
FilteredProduct.fmcg_erpreference, FilteredProduct.name AS ProductName,
SupplierAccount.name as supplierAccountName, SupplierAccount.address1_city AS
supplierCity,SupplierAccount.address1_line1 as supplierStreet1,
SupplierAccount.address1_postalcode as supplierPostalCode,
CRMAF_FilteredSalesOrder.requestdeliveryby, CRMAF_FilteredSalesOrder.createdon

FROM FilteredSalesOrder AS CRMAF_FilteredSalesOrder INNER JOIN FilteredAccount AS CustomerAccount
ON CRMAF_FilteredSalesOrder.customerid = CustomerAccount.accountid INNER JOIN FilteredSalesOrderDetail
ON FilteredSalesOrderDetail.salesorderid = CRMAF_FilteredSalesOrder.salesorderid INNER JOIN FilteredProduct
ON FilteredProduct.productid = FilteredSalesOrderDetail.productid INNER JOIN FilteredSystemUser
ON FilteredSystemUser.systemuserid = CRMAF_FilteredSalesOrder.ownerid INNER JOIN FilteredAccount AS SupplierAccount
ON CRMAF_FilteredSalesOrder.fmcg_supplierid = SupplierAccount.accountid

解决方案:删除报告并重新部署。

最佳答案

参见 Sample: Make a Report Context-Sensitive在 MSDN 上。

您只需过滤当前的销售订单。

关于 CRM 2011 中自动预过滤的文档很少,但是,我认为它在 CRM 2011 和 CRM 4 之间没有太大变化。所以请引用 Using Filters in a Report在 MSDN 上关于 CRM 4 中的 SSRS 报告以获得更好的解释。

我经常在报告中使用的是像 CRM_FilteredSalesOrder 这样的参数,结合如下所示的 SQL 查询:

EXEC('
SELECT FSO.*
FROM (' + @CRM_FilteredSalesOrder + ') AS FSO
')

它与自动预过滤原理相同,但现在您可以控制预过滤器插入的位置。另请记住,不能在 SSRS 报告的单个查询中多次使用自动预过滤器。

编辑:好的,让我直接使用您的示例:

EXEC('
SELECT
FSO.customerid AS x1,
FilteredAccount.accountid AS x2,
FilteredSalesOrderDetail.salesorderid AS x3,
FilteredSystemUser.systemuserid AS x4,
FilteredProduct.productid AS x5,
FilteredAccount.address1_city,
FilteredAccount.address1_line1,
FilteredAccount.address1_postalcode,
FilteredAccount.name AS AccountName,
FSO.ordernumber,
FilteredSalesOrderDetail.quantity,
FilteredSystemUser.fullname,
FilteredProduct.fmcg_erpreference,
FilteredProduct.name AS ProductName
FROM (' + @CRM_FilteredSalesOrder + ') AS FSO
INNER JOIN FilteredAccount ON FSO.customerid = FilteredAccount.accountid
INNER JOIN FilteredSalesOrderDetail ON FilteredSalesOrderDetail.salesorderid = FSO.salesorderid
INNER JOIN FilteredProduct ON FilteredProduct.productid = FilteredSalesOrderDetail.productid
INNER JOIN FilteredSystemUser ON FilteredSystemUser.systemuserid = FSO.ownerid
')

如果您想了解 CRM 插入的内容,请将 CRM_FilteredSalesOrder 参数放在报告的文本框中。当它位于“相关记录类型的表单”时,它将是 SELECT f0.* FROM FilteredSalesOrder AS f0 WHERE f0.salesorderid = '3641478b-5022-40fa-90c1-a4d407e8b99b' 形式的查询。这实际上是针对这个特定订单的子查询。这就是他们所说的“预过滤”,您只需查询一个子查询。

关于sql - 基于SQL的报表数据预过滤,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/12603747/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com