Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Custom SSRS report is not working in CRM 2011 RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Gurus,

    I have created a custom report via Visual Studion 2008(BIDS) , it is working fine when viewing the preview . once i imported into CRM 2011 . it is showing  the i/p parameter screen ,once i click the view report button , after some time it is throwing out an error "Report cannot be displayed
    RsProcessing aborted" .

    In my scenario CRM is installed in one server and SQL server is installed in a diffrent server .

    I have tried re-starting the Reporting services configuration manager  found as solution in internet and did couple of tricks as well.

    Unfortunately bad luck :(

    Please any one give me a suggestion .

    My custom report is built on top of a Stored procedure .

    Thanks in Advance .

     

    Thanks,

    SS


    • Edited by SSQLL Tuesday, August 23, 2011 9:14 AM
    Tuesday, August 23, 2011 8:54 AM

Answers

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote
    The account executing the report probably doesn't have access to your stored procedure. Can you try placing your stored procedure SQL in the Report itself to see if it works correctly or simply remove the reference to it in your SQL and see if the report works.
    MS CRM Bing'd - http://bingsoft.wordpress.com
    Useful Tools  CRM 4 to CRM 2011 JavaScript Converter Tool
    CRM 2011 OData Query Designer
    		CRM Forum Guidance on how to Help Us Help You
    Tuesday, August 23, 2011 8:58 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Rhett,

    I got admin access and excuting report with my id itself and also i have mentioned the stored procedure name in the dataset properties .

     Can you try placing your stored procedure SQL in the Report itself to see if it works correctly : were are you asking to place the stored procedure .

     

    Thanks,

    SS

     

    Tuesday, August 23, 2011 9:03 AM
  • Question
    Sign in to vote
    1
    Sign in to vote
    I was really just trying to get you to try the report in CRM without the Stored Procedure. Take a look at this post and although CRM 4 it should still apply. http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/b766257f-a2c4-4bd3-a306-582670ffd76d#1c96fcd9-748f-42a5-a80e-cf4843824622
    MS CRM Bing'd - http://bingsoft.wordpress.com
    Useful Tools  CRM 4 to CRM 2011 JavaScript Converter Tool
    CRM 2011 OData Query Designer
    		CRM Forum Guidance on how to Help Us Help You
    Tuesday, August 23, 2011 9:14 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Rhett,

     

    I am made changes ,but  still facing the same error .

     

    Thanks,

    SS

    Tuesday, August 23, 2011 9:25 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi All,

    I have created the stored procedure on the same CRM database . Is it the advisable/correct way to proceed ,when creating a Custom report from stored procedure .

     

    Thanks,

    SS

    Tuesday, August 23, 2011 9:59 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    You shouldn't create SP's on or modify the CRM Database in anyway, except possibly indexing. The link I provided has some useful ideas on using SP's in SSRS reports in CRM.

     Are you able to create a simply report, upload it to CRM and test it. You may like to review this thread aswell http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/4d505d4b-eb5b-40ea-9fb2-4164c6b44406

     Also here is the MSDN article on reporting which may be useful. http://msdn.microsoft.com/en-us/library/gg328097.aspx

    MS CRM Bing'd - http://bingsoft.wordpress.com
    Useful Tools  CRM 4 to CRM 2011 JavaScript Converter Tool
    CRM 2011 OData Query Designer
    		CRM Forum Guidance on how to Help Us Help You
    Tuesday, August 23, 2011 10:12 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Rhett,

    Thanks for your e-mail .

    I have created a simple custom report which is executing from a table with 1 i/p parameter  and imported into CRM server , it is working fine , so i think the issue is with stored procedure reports alone .

    If i am not supposed to create a stored procedure in CRM database , then how will i create a stored procedure .to fetch soem values .

    And also ,if i create a stored procedure in seprate database , how will the data load  going to work in future  from CRM database to the custom database .( Just now i have read some blog reagrding create a seprate database , but in that dataload issue is not mentioned)

    And also my report is not able to create via Fetch XML , i have tried this option initially .

    I am getting scared :)

    Thanks in advance .

    Thanks,

    SS

    Tuesday, August 23, 2011 10:24 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Did you ever install the  SRS data connector on the SQL server which is required for reports to work.  It's a separate installer you can find on the installation media in the /srsdataconnector folder.

     

    Jamie Miley
    Check out my about.me profile!
    http://mileyja.blogspot.com
    Linked-In Profile
    Follow Me on Twitter!
    Tuesday, August 23, 2011 10:48 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Jamie,

    Yes i did install SRS data connector  ,  all other custom reports are working fine in CRM 2011 the problem i am facing with only Stored procedure report which is created in the same CRM database .

    Thanks

    SS

    Tuesday, August 23, 2011 10:54 AM
  • Question
    Sign in to vote
    1
    Sign in to vote
    I suggest you look at the reporting services log file (in the <RS Install directory>\Reporting Services\LogFiles folder) to get more details regarding the error
    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    Tuesday, August 23, 2011 1:23 PM
    Moderator
  • Question
    Sign in to vote
    1
    Sign in to vote

    You can check out the log as mentioned by David, this should give you some detail about the error.

    With regards your stored procedure, is there any reason why you are using a stored procedure instead of the filteredviews in the CRM database. If there is no special reason why you choose to use a SP then jusst write your SQL against the CRM views.

    MS CRM Bing'd - http://bingsoft.wordpress.com
    Useful Tools  CRM 4 to CRM 2011 JavaScript Converter Tool
    CRM 2011 OData Query Designer
    		CRM Forum Guidance on how to Help Us Help You
    Tuesday, August 23, 2011 2:01 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi All,

     

    I am using FIltered view in my SP  (for eg : FilteredOpportunity) , the reason behind using stored procedure is my select statement  is very complex and i need to stored some value into #temp table .

    I ahev checekd  the log :

     

    getting some error like "

     

     

    library!ReportServer_0-2!2f0!08/23/2011-14:36:19:: Call to GetPermissionsAction(/Test_MSCRM).

    library!ReportServer_0-2!134c!08/23/2011-14:36:19:: Call to GetPropertiesAction(/Test_MSCRM, PathBased).

    library!ReportServer_0-2!2f0!08/23/2011-14:36:19:: Call to GetPropertiesAction(/Test_MSCRM, PathBased).

    library!ReportServer_0-2!134c!08/23/2011-14:36:19:: Call to GetSystemPermissionsAction().

    library!ReportServer_0-2!2f0!08/23/2011-14:36:19:: Call to ListChildrenAction(/Test_MSCRM, False).

     

    library!ReportServer_0-2!1

    library!ReportServer_0-2!2f0!08/23/2011-14:36:47:: Call to GetPermissionsAction(/Test_MSCRM).

    library!ReportServer_0-2!134c!08/23/2011-14:36:47:: Call to GetSystemPropertiesAction().

    library!ReportServer_0-2!134c!08/23/2011-14:36:47:: Call to GetPermissionsAction(/Test_MSCRM).

    library!ReportServer_0-2!134c!08/23/2011-14:36:47:: Call to GetPropertiesAction(/Test_MSCRM, PathBased).

    library!ReportServer_0-2!134c!08/23/2011-14:36:47:: Call to GetPropertiesAction(/Test_MSCRM, PathBased).

    library!ReportServer_0-2!134c!08/23/2011-14:36:47:: Call to GetSystemPermissionsAction().

    library!ReportServer_0-2!134c!08/23/2011-14:36:47:: Call to GetPoliciesAction(/Test_MSCRM).

    library!ReportServer_0-2!134c!08/23/2011-14:36:47:: Call to GetSystemPropertiesAction().

    library!ReportServer_0-2!134c!08/23/2011-14:36:50:: Call to GetPermissionsAction(/Test_MSCRM).

    library!ReportServer_0-2!2f0!08/23/2011-14:36:50:: Call to GetPropertiesAction(/Test_MSCRM, PathBased).

    library!ReportServer_0-2!134c!08/23/2011-14:36:50:: Call to GetPropertiesAction(/Test_MSCRM, PathBased).

    library!ReportServer_0-2!2f0!08/23/2011-14:36:51:: Call to GetSystemPermissionsAction().

    library!ReportServer_0-2!134c!08/23/2011-14:36:51:: Call to GetPropertiesAction(/Test_MSCRM, PathBased).

    library!ReportServer_0-2!2f0!08/23/2011-14:36:51:: Call to ListChildrenAction(/, False).

    34c!08/23/2011-14:38:19:: Call to GetItemTypeAction(/Test_MSCRM/CustomReports/{4a589528-8dcd-e011-82af-4a9c2a321208}).

    library!ReportServer_0-2!12d0!08/23/2011-14:38:19:: Call to CreateReportAction({4a589528-8dcd-e011-82af-4a9c2a321208}, /Test_MSCRM/CustomReports, False).

    library!ReportServer_0-2!128!08/23/2011-14:38:25:: Call to SetItemDataSourcesAction(/Test_MSCRM/CustomReports/{4a589528-8dcd-e011-82af-4a9c2a321208}).

    library!ReportServer_0-2!134c!08/23/2011-14:38:34:: Call to GetPermissionsAction(/).

    library!ReportServer_0-2!134c!08/23/2011-14:38:34:: Call to GetPropertiesAction(/, PathBased).

     Microsoft.Crm.Reporting.DataExtensionShim.Common.ReportExecutionException: The EXECUTE permission was denied on the object 'Test', database 'Test_MSCRM', schema 'dbo'.

       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

     

       why this execute permission is denied coming after all i am a db_admin in the SQL server .
      

     

     

     

    I have got db_owner  access to SQl server .

      

     Thanks,

     SS

     

     

    Tuesday, August 23, 2011 2:19 PM
  • Question
    Sign in to vote
    1
    Sign in to vote
    This is because the executing user is not you, it is the service executing your request. Your user context is passed to the report extension, it does not execute as you.
    MS CRM Bing'd - http://bingsoft.wordpress.com
    Useful Tools  CRM 4 to CRM 2011 JavaScript Converter Tool
    CRM 2011 OData Query Designer
    		CRM Forum Guidance on how to Help Us Help You
    Tuesday, August 23, 2011 4:06 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Rhett,

     

    how can i change this to my name  .

     

    Thanks,

    SS

    Tuesday, August 23, 2011 4:10 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Check this thread, hopefully it will solve your problem http://social.microsoft.com/Forums/en/crm/thread/92d98c6f-9051-4bf7-bb78-e189eaae8df8

     

    MS CRM Bing'd - http://bingsoft.wordpress.com
    Useful Tools  CRM 4 to CRM 2011 JavaScript Converter Tool
    CRM 2011 OData Query Designer
    		CRM Forum Guidance on how to Help Us Help You
    • Proposed as answer by Jim Glass Jr Tuesday, August 23, 2011 5:36 PM
    • Marked as answer by SSQLL Tuesday, August 23, 2011 6:03 PM
    Tuesday, August 23, 2011 4:25 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Rhett,

     

    Yes it solved my scary issue, Thanks a ton to you :) as well my infrastructure team , there was one more issue [PrivReportingGroup] my name was not added .

    Once again thanks a lot :)

     

    Thanks,

    SS

    Tuesday, August 23, 2011 6:03 PM