title: Data pre-filtering on SQL based Report
body: <p>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.</p>
  
  <p>This isn't the case. It generates a report for each record of the specific entity (Order), 1 record per page in the report.</p>
  
  <p>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. </p>
  
  <p>Link to thread: <a href="https://stackoverflow.com/questions/10856035/crmaf-filtering-in-crm-2011">CRMAF Filtering in CRM 2011</a></p>
  
  <pre><code>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
  </code></pre>
  
  <p>To pre-filter the data only to use the <strong>current</strong> Order:</p>
  
  <ul>
  <li>Use CRMAF_ with <strong>only</strong> the SalesOrder Table, is that enough?</li>
  </ul>
  
  <p>FROM FilteredSalesOrder AS CRMAF_FilteredSalesOrder INNER JOIN FilteredAccount </p>
  
  <ul>
  <li>Use CRMAF_ with <strong>all</strong> the tables?</li>
  </ul>
  
  <p>EDIT: </p>
  
  <p>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).</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Solution: Deleted Report and redeployed it.</p>