locked
USING SSRS from CRM with parameters RRS feed

  • Question

  • I have created a report in BID and imported it into CRM2011. I want to run this report on a specific record, which from my understanding i need to amend my SQL query to use 'pre-filtering.

    I am not 100% sure of what amendment i need to make. In this instance i need to pass a value licence from the licence field in the entity

    SELECT 
    		     @Licence,
    			CONVERT( SMALLDATETIME,h.c.value('@currentProcessingDate[1]' , 'VARCHAR(20)')) AS ReportDate,
    			s.c.value('@userName[1]' , 'VARCHAR(100)') AS UserName,
    			s.c.value('@userNumber[1]' , 'VARCHAR(6)') AS SUN,
    			w.c.value('@bankName[1]' , 'VARCHAR(50)') AS bankName,
    			w.c.value('@branchName[1]' , 'VARCHAR(50)') AS branchName,
    			w.c.value('@sortCode[1]' , 'VARCHAR(8)') AS sortCode,
    			w.c.value('@number[1]' , 'VARCHAR(8)') AS number
               FROM
    		   (SELECT CAST([new_xmlfile] as XML) AS Xmlreport FROM [XXX_MSCRM].[dbo].[new_Entity] WHERE new_aruddreportsId = @Id) d
    	        CROSS APPLY Xmlreport.nodes('//Data/ARUDD') AS a(c)
     			OUTER APPLY a.c.nodes('Header') h(c)
    			OUTER APPLY a.c.nodes('ServiceLicenseInformation') s(c)
    			OUTER APPLY a.c.nodes('Advice/OriginatingAccountRecords/OriginatingAccountRecord/OriginatingAccount') w(c)


    Dont ask me .. i dont know


    • Edited by Pete Newman Monday, October 7, 2013 7:49 AM security
    Monday, October 7, 2013 7:47 AM

All replies

  • I have amended my SQL to use the following. After importing the report back into CRM, when running the report against a record, I am getting a different report, in testing this agains several different records, i am aleays getting he same report and not the filtered one

    SELECT 
    		        d.new_licencename AS Licence,
    			CONVERT( SMALLDATETIME,h.c.value('@currentProcessingDate[1]' , 'VARCHAR(20)')) AS ReportDate,
    			s.c.value('@userName[1]' , 'VARCHAR(100)') AS UserName,
    			s.c.value('@userNumber[1]' , 'VARCHAR(6)') AS SUN,
    			w.c.value('@bankName[1]' , 'VARCHAR(50)') AS bankName,
    			w.c.value('@branchName[1]' , 'VARCHAR(50)') AS branchName,
    			w.c.value('@sortCode[1]' , 'VARCHAR(8)') AS sortCode,
    			w.c.value('@number[1]' , 'VARCHAR(8)') AS number
               FROM
    		   (SELECT new_licencename, new_aruddreportsid, CAST([new_xmlfile] as XML) AS Xmlreport FROM [XXXX_MSCRM].[dbo].[Filterednew_MyEntity] ) d
    	        CROSS APPLY Xmlreport.nodes('//Data/ARUDD') AS a(c)
     			OUTER APPLY a.c.nodes('Header') h(c)
    			OUTER APPLY a.c.nodes('ServiceLicenseInformation') s(c)
    			OUTER APPLY a.c.nodes('Advice/OriginatingAccountRecords/OriginatingAccountRecord/OriginatingAccount') w(c)


    Dont ask me .. i dont know

    Monday, October 7, 2013 9:25 AM
  • I'm still struggling wit this. I have read a few more threads and according to the threads, i needed to have 'pre-filtering' on which is done by adding 'CRMAF_' to my query. After importing the report back into CRM, I am still not getting the option to run the report on a selected record. My understanding is that this should be possible. New SSIS SQL below

    SELECT CRMAF_ARUDD.[new_licencename], CONVERT( SMALLDATETIME,h.c.value('@currentProcessingDate[1]' , 'VARCHAR(20)')) AS ReportDate, s.c.value('@userName[1]' , 'VARCHAR(100)') AS UserName, s.c.value('@userNumber[1]' , 'VARCHAR(6)') AS SUN, w.c.value('@bankName[1]' , 'VARCHAR(50)') AS bankName, w.c.value('@branchName[1]' , 'VARCHAR(50)') AS branchName, w.c.value('@sortCode[1]' , 'VARCHAR(8)') AS sortCode, w.c.value('@number[1]' , 'VARCHAR(8)') AS number FROM (SELECT CRMAF_ARUDD.new_licencename, CRMAF_ARUDD.new_aruddreportsid, CAST(CRMAF_ARUDD.[new_xmlfile] as XML) AS Xmlreport FROM [XXXX_MSCRM].[dbo].[Filterednew_MYENTITY] CRMAF_ARUDD ) CRMAF_ARUDD CROSS APPLY Xmlreport.nodes('//Data/ARUDD') AS a(c) OUTER APPLY a.c.nodes('Header') h(c) OUTER APPLY a.c.nodes('ServiceLicenseInformation') s(c) OUTER APPLY a.c.nodes('Advice/OriginatingAccountRecords/OriginatingAccountRecord/OriginatingAccount') w(c)



    Dont ask me .. i dont know

    Monday, October 7, 2013 10:33 PM
  • Pete,

    Pre-filtering or CRMAF prefix to the filtered view in a query will provide the Advanced Find screen prior to the actual report...similar to the OOB reports.

    To get a report in the record form or in the list view you have to set the 'Related record types' and 'Display Area' (as Forms & lists of the record type) while uploading the report.See sample below


    If my response helps you in finding your answer then please click 'Mark as Answer' and 'Vote as Helpful'

    Tuesday, October 8, 2013 12:27 AM
  • Hi ,

    I have those set already 

    When in the Arudd Entity View, I select a record and run report

    As you can see, the report does not appear in the 'Run on Selected Records' section


    Dont ask me .. i dont know

    Tuesday, October 8, 2013 1:11 AM
  • pls refer to this blog. You will need to update the query with the enable pre-filtering and upload a fresh report instead of updating existing report.


    If my response helps you in finding your answer then please click 'Mark as Answer' and 'Vote as Helpful'

    Tuesday, October 8, 2013 1:54 AM
  • Hi,

    That blog refers to fetchXML , this report has been generated in bids using SQL.

    I have already deleted this report from CRM and made sure that it was removed from reporting services. I recompiled the report in bids and imported it into CRM.


    Dont ask me .. i dont know

    Tuesday, October 8, 2013 2:04 AM
  • my bad., sorry for the FetchXML one.

    One thing you can do is to download the report directly from SSRS and check the modified query (CRM would have modified it). I see that you have a "Facilities Mgmt" report in the 'Run on Selected reports', It might help to compare how CRM is passing the parameter for that and the new report.

    If this doesn't help then you can try updating the query with explicit pre-filtering. Check limitation of Automatic pre-filtering


    If my response helps you in finding your answer then please click 'Mark as Answer' and 'Vote as Helpful'

    Tuesday, October 8, 2013 2:56 AM