locked
CRM 2013 and SSRS reports using pre-filtering on the current record RRS feed

  • Question

  • We are moving from 2011 to 2013 crm and upgraded our SQL server from 2008 r2 to 2012 R2

    I have read many articles and followed them in order to get this working. But i am beginning to this there is something not right with our setup rather than the report. I found a few things that had not been installed by the Infrastructure team.

    We do have a permissions issue with the report server as we tightened up security and are using names pipes and named application pools. Applied the necessary authentication but found that we cant use windows authentication for running the reports. After doing some investigation we found that it was  the permission on the accounts but the only account we have managed to get working with report server and get the reports displaying in crm is the administrator account. So thats issue 1 as i need to have a service account do that and not use the administrator account. 

    Second issue is with prefiltering and custom reports created in crm.

    I have a SSRS report for the SalesOrder that runs on the current record in CRM2011 and this works using a prefilter and a temp table that i can use for the other queries.

    I thought it would be a simple process of converting the report to use the new field names and re pointing the data connection to the new report server.

    Unfortunately when i uploaded the file  and went to run it on an order it did not appear in the prefiltered section. I have been messing with many variations in an attempt to fix it but no joy. It runs but on all records. So i went back to basics and did a simple prefilter and  that did not work either.  I tried creating a report in crm and that starts and then times out.

    I have looked at logs and it says an error has occurred or parameter missing. But since the only parameter is the prefilter i am a bit confused. I made sure i deleted the report and started afresh each time i did anything.

    Tried:

    Internal parameter with a default of select*from filteredsalesorder as CRMAF_SalesOrder

    and a ordernumber parameter referencing dataset1 with the following SQL:

    Declare @SQL navchar(max)

    set @SQL =  'Select fso.* from (' + @CRM_FilteredSalesOrder + ') as fso' 

    EXEC (@SQL)

    --------------------------------------------

    Hidden Parameter referencing Dataset1

    with either of these:

    Select top (20)

    CRMAF_FilteredSalesOrder.salesorderid

    CRMAF_FilteredSalesOrder.ordernumber

    From FilteredSalesOrder as CRMAF_FilteredSalesOrder

    or 

    DECLARE @SQL nvarchar(max)

    DECLARE @CRM_FilteredSalesOrder nvarchar(2000)

    set @SQL =  'Select fso.* from (' + @CRM_FilteredSalesOrder + ') as fso' 

    EXEC (@SQL)

    I would really appreciate some help as i am wasting time now.

    Friday, July 11, 2014 9:49 PM