locked
No data output from SSRS report RRS feed

  • Question

  • Hi,

    I'm new to CRM but an old hand at SQL, so please bear with my ignorance.
    I've created 5 reports in SSRS, all variations of the same report, and all producing about 1,500 records in SSRS, my problem is when I import them into CRM I get no records displayed. To add the reports I've simply gone to Reports  - New - Report Type - Existing File (added) and Name (added), then I've saved and closed. When I run the report I get no errors, just a blank report, no data output whatsoever. Have I missed something here, it's using the same Data Sources as the CRM reports that already exist and work as I ripped the template off from one of them.

    Thanks,

    Bob
    My wife says I never listen to her, or something like that anyway.
    Friday, July 24, 2009 10:34 AM

Answers

  • Well, I left it alone for 2 weeks as the CEO was on holiday, I've come back and it's working, I've done nothing, changed nothing, no reboots or patches, it just started working.
    My wife says I never listen to her, or something like that anyway.
    • Marked as answer by StraNGeLaD Thursday, August 20, 2009 8:52 AM
    Thursday, August 20, 2009 8:52 AM

All replies

  • Hi Bob,

    a report in CRM displays only that data, that you can read in CRM. Can your user read the data in CRM you want to display in the report?


    Viele Grüße

    Michael Sulz
    axcentro GmbH
    MVP für Microsoft Dynamics CRM
    Friday, July 24, 2009 11:46 AM
  • Yes, the data is coming from the CRM database via views and the user in question is me using the CRM admin account to view through IE.
    My wife says I never listen to her, or something like that anyway.
    Friday, July 24, 2009 12:42 PM
  • Bob -

    When you get the blank report screen - do you see the pages/zoom/export/print toolbar- or is it just a blank screen?

    And are you using any of the crm prefiltering techniques? (such as aliasing the view in the SSRS query with CRMAF_filteredentityname rather than using the report itself.) / does your query only reference the CRM filtered views - or are any tables referenced?
    Scott Sewell, CustomerEffective | http:\\blog.CustomerEffective.com | Twitter:@ScottSewell
    Friday, July 24, 2009 1:00 PM
    Moderator
  • It's a blank report, the header and footer are there, all of the buttons are there, there's just no data.
    My wife says I never listen to her, or something like that anyway.
    Monday, July 27, 2009 7:33 AM
  • That at least means all the systems are working correctly - now it's a matter of determining why your query isn't returning data when run in CRM.

    Can you address the 2nd question from the earlier posting? - Are you using any of the crm prefiltering techniques? (such as aliasing the view in the SSRS query with CRMAF_filteredentityname rather than using the report itself.) / does your query only reference the CRM filtered views - or are any tables referenced?
    Scott Sewell, CustomerEffective | http:\\blog.CustomerEffective.com | Twitter:@ScottSewell
    Monday, July 27, 2009 11:44 AM
    Moderator
  • Sorry, I missed the second question earlier. I've tried adding and removing prefilters but it makes no difference, all filtering is currently hard coded into the report (it's for the CEO's confidential contacts so it's set to only allow his known associates and hard coded by his username).
    My wife says I never listen to her, or something like that anyway.
    Monday, July 27, 2009 5:31 PM
  • You're issue most likely lies in the way the datasource was created in Visual Studio and the 'hard-coded' credentials'.  CRM probably doesn't like how that is configured.

    If you want only the CEO to have access to the report, I would put the report directly on the report server but not make it visible through CRM.  You can then setup a subscription to run and deliver the report to the CEO.  You could also give the CEO a shortcut on his desktop to access the report on demand.  I don't think you need to go through CRM for this report.
    Best Regards, Donna
    • Proposed as answer by Donna EdwardsMVP Monday, July 27, 2009 8:17 PM
    • Unproposed as answer by StraNGeLaD Tuesday, July 28, 2009 8:20 AM
    Monday, July 27, 2009 8:17 PM
  • Hey Bob -

    In addition to Donna's comments - you mentioned that the credentials are hard-coded in the report - is that in the datasource- or in the query itself?

    Also - is your query against tables directly or against the "filtered" views (filteredcontact)?  If it's against the filtered contact, does your user in CRM have permissions to view the contacts owned by the CEO? - (I assume you're an administrator in CRM, but just wanted to cover that in case you have a different role in production.)
    Scott Sewell, CustomerEffective | http:\\blog.CustomerEffective.com | Twitter:@ScottSewell
    Monday, July 27, 2009 8:33 PM
    Moderator
  • The CEO is already getting more than annoyed by CRM due to the number of problems we've had setting it up, the speed at which it runs (sporadic server lockups but perfmon shows nothing, which makes me think it's the pipe) and its general inflexibility to do exactly what he wants (he's unreasonable).
    Basically, he wants to look up his contacts by hobby (shooting, polo, etc) or by company or any number of other things, he doesn't want to have to filter it so each report has to be individually tailored i.e. no prefiltering or filtering, just hard coded. I've set up a CRM filtered view which contains all of his contacts, I then apply all filters in the dataset for each report (where hobby = 'shooting').
    I'm a CRM admin in dev and live so I should be able to see the report in either.
    This needs to be in the CRM, if he finds out that the CRM isn't doing what it's supposed to be doing then he'll scrap the entire project and revert back to using Outlook and paper (he is indeed that fickle).


    My wife says I never listen to her, or something like that anyway.
    Tuesday, July 28, 2009 8:35 AM
  • Bob - I think we have a shot at getting your report working.

    The requirement as best I can read seems to be fairly straightforward, but there's some confusion between us on the type of query you are using. -

    Can you post an example of the query in your report?


    Scott Sewell, CustomerEffective | http:\\blog.CustomerEffective.com | Twitter:@ScottSewell
    Tuesday, July 28, 2009 10:53 AM
    Moderator
  • a. Have you tried running the default reports which are there in the system when we install CRM System like "Account Summary" or "Account Overview" reports? Do they run fine and do they show any data?

    b. Is your report working on the Report Server :- 
            i. http://ReportServerName/reportserver        
           ii. http://ReportServerName/reports

    c. Try creating a simple report and do not use any Filtered Views, Just reference the table directly.

    Tuesday, July 28, 2009 7:41 PM
  • Also, Please check the User Permissions in the SQL Database by which you have logged in the CRM System.

    There are 2 checkboxes (db_datareader and db_datawriter) which needs to be marked :-

    Go to SQL -> Database -> Select Your Database -> Select Security -> Users -> Right the User who needs permission to access reports -> Mark checkboxes db_datareader and db_datawriter -> OK.
    Wednesday, July 29, 2009 5:54 PM
  • Well, I left it alone for 2 weeks as the CEO was on holiday, I've come back and it's working, I've done nothing, changed nothing, no reboots or patches, it just started working.
    My wife says I never listen to her, or something like that anyway.
    • Marked as answer by StraNGeLaD Thursday, August 20, 2009 8:52 AM
    Thursday, August 20, 2009 8:52 AM