locked
Filtering in SSRS Visual Studio works, but not when uploaded to CRM RRS feed

  • Question

  • I have an SSRS report that is filtering correctly in VS. Query returns correctly, and the Preview runs correctly. When I upload to CRM, it no longer filters properly.

    SELECT
    CRMAF_FilteredRVA_purchaseorder.rva_customerjob AS JobNo,
    CRMAF_FilteredRVA_purchaseorder.rva_jobrevenue AS JobRevenue,
    CRMAF_FilteredRVA_workorder.rva_purchaseorderidname AS PurchaseOrder,
    CRMAF_FilteredRVA_purchaseorder.rva_poid AS PO,
    CRMAF_Filteredrva_workorder.rva_buildquantity AS JobQuantity,
    CRMAF_FilteredRVA_workorder.rva_datefulfilled AS Date,
    CRMAF_FilteredRVA_workorder.rva_workordernumber AS WO

    FROM     Filteredrva_purchaseorder AS CRMAF_FilteredRVA_purchaseorder
    INNER JOIN Filteredrva_workorder AS CRMAF_FilteredRVA_workorder ON CRMAF_FilteredRVA_purchaseorder.rva_purchaseorderid = CRMAF_FilteredRVA_workorder.rva_purchaseorderid

    WHERE
    CRMAF_FilteredRVA_purchaseorder.rva_poid = @POID

    Any help is greatly appreciated.


    Ken Compter

    Wednesday, March 9, 2016 2:40 AM

Answers

All replies

  • Hello Ken,

    What is @POID? Try to delete report and recreate it. Sometimes prefiltering doesn't work when you add prefiltering to report that didn't had it before.


    Dynamics CRM MVP
    Read My blog
    Subscribe for one of my courses

    Wednesday, March 9, 2016 2:02 PM
    Moderator
  • Thanks for getting back to me Andrii. I tried what you suggested, and it still does not return results properly. What I have is 2 custom entities: Purchase Order and Work Order (1:n). The rva_poid is a generated PO number used as a unique identifier on the PO entity. From the PO, a Work Order is generated, field values mapped from PO to WO including the lookup of PO Number and is CRMAF_FilteredRVA_workorder.rva_purchaseorderid. I am running the report from the Work Order level, and want to display all Work Orders related to the PO in a table. Here is the query:it returns the correct associated work orders. the Preview works as well. when the report is run, only 1 work order shows (the current Work Order).

    Here is the Preview:


    Ken Compter

    Wednesday, March 9, 2016 2:55 PM
  • Hello,

    Please provide step-by-step screenshots how it works in your CRM.


    Dynamics CRM MVP
    Read My blog
    Subscribe for one of my courses

    Wednesday, March 9, 2016 3:35 PM
    Moderator
  • Report Settings:

    View of Active Work Orders:Note 4 WO with PO1000003

    Work Order running report:

    Report rendering: Note only 1 work order returned in table.


    Ken Compter

    Wednesday, March 9, 2016 4:53 PM
  • Hello,

    That's how it works. When you use prefiltering and run report from some record your query in report transforms to something like:

    SELECT 
    CRMAF_FilteredRVA_purchaseorder.rva_customerjob AS JobNo, 
    CRMAF_FilteredRVA_purchaseorder.rva_jobrevenue AS JobRevenue, 
    CRMAF_FilteredRVA_workorder.rva_purchaseorderidname AS PurchaseOrder, 
    CRMAF_FilteredRVA_purchaseorder.rva_poid AS PO, 
    CRMAF_Filteredrva_workorder.rva_buildquantity AS JobQuantity,
    CRMAF_FilteredRVA_workorder.rva_datefulfilled AS Date, 
    CRMAF_FilteredRVA_workorder.rva_workordernumber AS WO
    
    FROM     Filteredrva_purchaseorder AS CRMAF_FilteredRVA_purchaseorder 
    INNER JOIN Filteredrva_workorder AS CRMAF_FilteredRVA_workorder ON CRMAF_FilteredRVA_purchaseorder.rva_purchaseorderid = CRMAF_FilteredRVA_workorder.rva_purchaseorderid
    
    WHERE
    CRMAF_FilteredRVA_purchaseorder.rva_poid = @POID and 
    CRMAF_FilteredRVA_workorder.rva_workorderid = <current workorder id>

    If you don't want last filter to be applied just remove aliasing in your report and use following SQL:

    SELECT 
    FilteredRVA_purchaseorder.rva_customerjob AS JobNo, 
    FilteredRVA_purchaseorder.rva_jobrevenue AS JobRevenue, 
    FilteredRVA_workorder.rva_purchaseorderidname AS PurchaseOrder, 
    FilteredRVA_purchaseorder.rva_poid AS PO, 
    Filteredrva_workorder.rva_buildquantity AS JobQuantity,
    FilteredRVA_workorder.rva_datefulfilled AS Date, 
    FilteredRVA_workorder.rva_workordernumber AS WO
    
    FROM     Filteredrva_purchaseorder AS FilteredRVA_purchaseorder 
    INNER JOIN Filteredrva_workorder AS FilteredRVA_workorder ON FilteredRVA_purchaseorder.rva_purchaseorderid = FilteredRVA_workorder.rva_purchaseorderid
    
    WHERE
    FilteredRVA_purchaseorder.rva_poid = @POID
    Once you finished with changes delete CRM report and recreate it to remove prefiltering.



    Dynamics CRM MVP
    Read My blog
    Subscribe for one of my courses

    Wednesday, March 9, 2016 5:07 PM
    Moderator
  • You have been a great help! Almost there. Second one worked. First one gave an error when running query "Incorrect syntax near '<'

    Is the first query supposed to save me from entering the parameter?


    Ken Compter

    Wednesday, March 9, 2016 6:09 PM
  • No,

    I meant when you use prefiltering your query is transformed to first one I provided by CRM. Just use second for your report.


    Dynamics CRM MVP
    Read My blog
    Subscribe for one of my courses

    Wednesday, March 9, 2016 6:24 PM
    Moderator