CODEWITHSUNDEEP
sqlreporting-servicessql-server-2008-r2dynamics-crmdynamics-crm-2011
Yacine Zine
Yacine Zine

Reputation: 619

Data pre-filtering on SQL based Report

I made a SQL-based report in Visual Studio 2008 and uploaded it to the CRM. I indicated that you can only run it on "Forms for related record types". I thought this would generate 1 report for the current record you are in.

This isn't the case. It generates a report for each record of the specific entity (Order), 1 record per page in the report.

I know I have to use Data-Prefiltering (automatic or specific?), but there are no good examples online and there is even a thread here on stackoverflow but I jsut can't figure it out.

Link to thread: 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

To pre-filter the data only to use the current Order:

FROM FilteredSalesOrder AS CRMAF_FilteredSalesOrder INNER JOIN FilteredAccount

EDIT:

This is my new entire SQL where I used the CRMAF_ on FilteredSalesOrder. But alas it does not work. It still gives me only the opportunity to run it against all records, and thus generates all the Salesorders that exist (luckily there are not that many).

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

Solution: Deleted Report and redeployed it.

Upvotes: 2

Views: 7240

Answers (1)

Thijs Kuipers
Thijs Kuipers

Reputation: 485

See Sample: Make a Report Context-Sensitive on MSDN.

You only need to filter on the current SalesOrder.

The documentation on automatic prefiltering in CRM 2011 is sparse, however, I think it hasn't changed much between CRM 2011 and CRM 4. So please refer to Using Filters in a Report on MSDN regarding SSRS reports in CRM 4 for a better explanation.

What I often use in reports are parameters like CRM_FilteredSalesOrder combined with a SQL query like the following:

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

It is the same principle as automatic prefiltering, but now you control where the prefilter is inserted. Also remember that an automatic prefilter can't be used more than once in a single query in an SSRS report.

EDIT: OK, let me just literally use your example:

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

If you want to understand what the CRM inserts, put the CRM_FilteredSalesOrder parameter in a textbox on the report. It will be a query in the form SELECT f0.* FROM FilteredSalesOrder AS f0 WHERE f0.salesorderid = '3641478b-5022-40fa-90c1-a4d407e8b99b' when it's on "Forms for related record types". This is effectively a subquery for this one particular Order. That's what they call "prefiltering", you'll just query a subquery.

Upvotes: 5

Related Questions