Sign in
Microsoft.com
 
Microsoft
 
 
 

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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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