locked
Scheduled Reports RRS feed

  • Question

  • Hi All,

    I have created a Scheduled Report to run at 01:00 on Monday and Friday each week. In my list of reports I see the report name with Scheduled Snapshots next to it however the reports are not being generated.

    I have logged in to the report server and tried to view the report from there however I get the following message...

    The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available. (reReportNotReady)

    I also get this message if I try to view it from within CRM not that I want to view the scheduled report from there, I am trying to get this working so that the report server can email the report to several managers.

    Could someone point me in the right direction for troubleshooting this?

    Thanks


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk
    Monday, October 4, 2010 1:12 PM

Answers

  • Lets try this, go to Database Mail under SQL Management Studio, right click Database Mail and select View Database Mail Log.  Open the log and let's see if we have any additional information about the error. 

    By the way, what options did you choose for sending the report.  Let's start with the option "Include Link" only.   Also, I am assuming this is a very simple report, nothing complex like subreports, etc.  If not, let's try this procedure on a simple report first and get it running.

    Can you also upload a screenshot of your Delivery Options page for your Subscription so I can have a look at it.  Additionally, please include a screenshot of the following:

    • Report Datasource
    • Execution

    Before doing the above, we can try one additional change:

    • Go to the Properties of the report and select DataSource from the left navigation window
    • Change your DataSource from the Shared Datasource to a Custom DataSource

    Enter / Select the following

    • Connection Type = Microsoft SQL Server
    • Credentials Stored Securely in Report Server
    • Enter a CRM Admin user login in this format - domainName\crmadmin
    • Enter the CRM Admin password
    • Check the box, "Use as Windows credentials when connecting to the data source"
    • Select Apply
    • Test run the report to ensure it is working

    Try the subscription again.

     


    Regards, Donna

    • Marked as answer by DavidHodgson Tuesday, October 12, 2010 6:22 AM
    Tuesday, October 5, 2010 5:16 PM

All replies

  • Hi David,

    Did you have a look at Donna's article :

    http://blogs.msdn.com/b/crm/archive/2009/08/05/creating-report-subscriptions-in-microsoft-dynamics-crm-4.aspx

    It's described how to send scheduled reports on email.

     


    My Dynamics CRM Blog: http://bovoweb.blogspot.com
    Monday, October 4, 2010 1:16 PM
  • What is the nature of the report? Could it be a long running operation?

    I think you need to first run the execution snapshot and then run the report against it.

    See this post:

    http://blogs.msdn.com/b/jgalla/archive/2006/08/30/live-reports-and-rsreportnotready-error.aspx

     


    Jeff Loucks | Available Technology | 888-474-2237 | http://msmvps.com/blogs/jeffloucks
    • Proposed as answer by Jeff Loucks Monday, October 4, 2010 1:17 PM
    Monday, October 4, 2010 1:17 PM
  • Hi Kgorczewski

    Yes, that is exactly the article I followed to set this up but it seems that the report is not being generated.

    Regards


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk
    Monday, October 4, 2010 1:18 PM
  • David,

    Try solution which Jeff mentioned (ISOLATION) if still will not be working try this solution:

    http://www.experts-exchange.com/Database/Reporting_/Q_22493901.html

     

    KG


    My Dynamics CRM Blog: http://bovoweb.blogspot.com
    Monday, October 4, 2010 1:25 PM
  • Thanks all, I will try the suggestions as soon as I get 5 and get back to you.

    Many thanks


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk
    Monday, October 4, 2010 1:37 PM
  • I think you might have missed a step in your setup.  The reason I say this is because I don't think you should see the reference "Scheduled Snapshots" in the report description.  I think if you see this, it means you selected to run the report as Scheduled, rather than On Demand.  It is an easy step to miss.

    You can delete the report from CRM that was created when you selected the 'Schedule Report' option.  Don't delete your primary report, just the second one that was created.  Now walk through the steps again and ensure you select the On Demand option rather than Schedule.  Here is the specific reference from the post:

    "A wizard dialogue window will open. Select On Demand and Next. We don’t need to select On Schedule because we don’t necessarily want to run Report Snapshots; we want to setup a Subscription for delivery. If you decide you want to create Snapshots in addition to a Subscription, you can do that later from the Report Server:"


    Regards, Donna

    Monday, October 4, 2010 2:17 PM
  • Hi Donna,

    I was hoping you would get back to me on this one. I completely misinterpreted that part of the article and did it the opposite way around. Should perhaps try configuring CRM at normal hours and not when I should be fast asleep!

    Anyway, I have recreated the report and set up the email subscription however I now have a message on the subscription tab on the report server site stating...

    An error has occurred during report processing

    I cannot see anywhere that allows me to see what the error was, there is nothing in the event viewer on the report server.

    Once again I would appreciate some input.

    Regards


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk
    Monday, October 4, 2010 7:41 PM
  • Quick update,

    I tried a different report and got the following...

    Failure sending mail: The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available.Mail will not be resent.

    Regards


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk
    Monday, October 4, 2010 8:02 PM
  • Hi David,

    Ok, just to ensure the subscription part of the report is setup correctly, I'll assume you see the words 'on demand snapshots.....' in the report description.

    If the above is true, go to the Edit icon of the report and go to the Subscription tab.  For a test, schedule a time for the report to run that is in the next few minutes, then let's see if it runs as expected. 

    Just so you know, I'm also assuming that you've configured your SQL server to deliver e-mail subscriptions.  If not, you'll need to complete that configuration process before this will work.


    Regards, Donna

    Monday, October 4, 2010 8:31 PM
  • Hi Donna,

    Yes I have the words 'on demand snapshots', I set the reports to run within minutes and get the errors.

    I presume when you say configured SQL server to deliver email subscriptions you mean in the Report Server configuration to set up email, if yes I have done that.

    Regards


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk
    Monday, October 4, 2010 8:34 PM
  • Do you know how to check the SQL job to look at the error?  If so, then we can get a quick glimpse into the error.  I'm trying to avoid sending you to the log files for the error, because that can be challenging.

    In the absence of being able to look at the environment, I think that the issue might now be related to configuring SQL to deliver e-mail.  Not sure what version of SQL you're on, so here are a couple of links, one for SQL 2005 and one for SQL 2008.  Can you take a look at these and ensure SQL is configured to deliver mail.

    SQL 2005

    http://www.sql-server-performance.com/articles/dba/email_functionality_p1.aspx

    http://www.mssqltips.com/tip.asp?tip=1100

    SQL 2008 - might be similar to 2005

    http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/


    Regards, Donna

    Monday, October 4, 2010 8:49 PM
  • I will take a look tomorrow, I believe that the SQL Server may not be set up correctly for emailing reports.

    Will let you know how I get on and thanks for your help so far.


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk
    Monday, October 4, 2010 9:02 PM
  • You're welcome David.

    Regards, Donna

    Monday, October 4, 2010 9:23 PM
  • Hi,

    OK I have checked the email settings on the Report Server and they all seem OK, I have allowed the Report Server to relay through our Exchange server so there should be no problem.

    I have set the report to run and again I have the following error...

    "Failure sending mail: The selected report is not ready for veiwing. The report is still being rendered or a report snapshot is not available. Mail will not be resent."

    So what am I doing now, try the logs on me, I might be able to handle it LOL

    Regards

     


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk
    Tuesday, October 5, 2010 12:03 PM
  • Ok good, we've ruled that out.  Based on the error, it looks to me like the subscription is still trying to send the report based on a snapshot so let's check a few settings.

    • Please go to the properties of the report and select Subscriptions
    • Ensure the value under Trigger = Timed Subscription
    • Go To Execution from the left navigation menu under Properties and ensure the first option is selected "Always Run this Report with the most Recent Data" and "Do Not Cache copies of the Report"

    If those options are set, then we can take a look at other items.  To expedite this, since it has taken so long, I am always willing to do a remote session with you and have a look at your environment to see if I can more quickly identify the issue, if you are ok with that.  If that is not good for you, then we'll continue to work through it using this method.

    Thank you for your patience.

    By the way, I have no doubt about your ability to handle the logs and we can use that as a last resort.  I'm not an expert log analyzer, although I've looked at many, so I use it as a last resort for SQL report issues.


    Regards, Donna

    Tuesday, October 5, 2010 1:08 PM
  • 1. Make sure that Reporting Servcies have privilege to visit RsTempFiles folder. By default, the path is

    C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services

    2. Recreate application pool of the Reporting Servcies in IIS.

    3. Disable snalshot isolation on the ReportServer and ReportServerTempDB databases. You may use the SQL statement like:

    ALTER DATABASE reportserver$mssql2005 
    SET ALLOW_SNAPSHOT_ISOLATION OFF; 
    

    and

    ALTER DATABASE reportserver$mssql2005tempdb 
    SET ALLOW_SNAPSHOT_ISOLATION OFF;
    

    Jeff Loucks | Available Technology | 888-474-2237 | http://msmvps.com/blogs/jeffloucks
    Tuesday, October 5, 2010 3:15 PM
  • Hi Donna,

    Apologies for the slow response, bit manic here as we are migrating a lot of servers to a virtualised environment :)

    I have checked the settings you pointed out, the second was not actually set to "Always Run this Report with the most Recent Data" it was using snapshots. Therefore I have changed it and set a schedule for the report to run. I am still presented with an error.

    Failure sending mail: An error has occurred during report processing.Mail will not be resent.

    Regards


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk
    Tuesday, October 5, 2010 3:20 PM
  • We are making progress, that error is different.  It is a mail failure error which indicates that something is not set quite right on the SQL database to send e-mail.

    Can you try sending a test e-mail from the SQL server.  Here are the steps if you need them. 

     To verify that the SQL Mail profile is correct, you can do the following:
    1. Run SQL Server Enterprise Manager.
    2. Expand a server group; then expand a server.
    3. Expand the Management folder and select Database Mail.
    4. Right-click Database Mail and select Send Test E-mail

    Also ensure that the machine is not setup to use a default mail provider like Outlook, Outlook Express or some other default mail service otherwise you'll have to open that program for the mail to get sent.  You'll need to ensure the SMTP service is working on the machine 


    Regards, Donna

    Tuesday, October 5, 2010 3:56 PM
  • Hi Donna,

    Thanks for that, had not set up the Database Mail, I was setting it up in Reporting Services Configuration.

    I set it up and sent a test email, all OK. Reschduled the report but same error as last time.

    What next LOL


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk
    Tuesday, October 5, 2010 4:22 PM
  • are you on SQL 2005 or 2008.  What we are working on now is not related to CRM but rather SQL Subscriptions so the causes could be many like security rights, the SQL job requires a little tweeking or something different.  I've spent a few hours troubleshooting these types of issues with the ability to dial into the server so this could take some time since I don't have access to the environment.

    My guess is that it is an few small tweaks, but tracking them down will be the challenge.  I think at this point we are going to need to dig into the logs. 


    Regards, Donna

    Tuesday, October 5, 2010 4:48 PM
  • Lets try this, go to Database Mail under SQL Management Studio, right click Database Mail and select View Database Mail Log.  Open the log and let's see if we have any additional information about the error. 

    By the way, what options did you choose for sending the report.  Let's start with the option "Include Link" only.   Also, I am assuming this is a very simple report, nothing complex like subreports, etc.  If not, let's try this procedure on a simple report first and get it running.

    Can you also upload a screenshot of your Delivery Options page for your Subscription so I can have a look at it.  Additionally, please include a screenshot of the following:

    • Report Datasource
    • Execution

    Before doing the above, we can try one additional change:

    • Go to the Properties of the report and select DataSource from the left navigation window
    • Change your DataSource from the Shared Datasource to a Custom DataSource

    Enter / Select the following

    • Connection Type = Microsoft SQL Server
    • Credentials Stored Securely in Report Server
    • Enter a CRM Admin user login in this format - domainName\crmadmin
    • Enter the CRM Admin password
    • Check the box, "Use as Windows credentials when connecting to the data source"
    • Select Apply
    • Test run the report to ensure it is working

    Try the subscription again.

     


    Regards, Donna

    • Marked as answer by DavidHodgson Tuesday, October 12, 2010 6:22 AM
    Tuesday, October 5, 2010 5:16 PM
  • Hi Donna,

    Apologies once again for the delay and thank you again for your help it is appreciated even more now that we know this is a SQL issue rather than CRM.

    If it is easier with you a dial in session would be most helpful to assist with getting this resolved, it was not possible before with the amount of other stuff I had to get done.

    I have not tried any of your most recent suggestions however if you would prefer me to rather than you connect to me let me know.

    Regards


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk
    Tuesday, October 5, 2010 6:19 PM
  • Hi,

    Tried the last part, changing the credentials, set the schedule, same error. Clicked on View to run the report got the following...

    • An error occurred during client rendering.
      • An error has occurred during report processing.
        • Cannot create a connection to data source 'CRM'.
          • Keyword not supported: 'mscrm data connector connection string ;integrated security'.


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk
    Tuesday, October 5, 2010 6:31 PM
  • oops forgot one important point, you have to set the connection string to this format when you select SQL Server:

    Data Source=CRMSQLServerName;Initial Catalog=CRM_Org_Name


    Regards, Donna

    Tuesday, October 5, 2010 6:45 PM
  • what is your time zone so I can see if I have some availability where you also have time.

    Regards, Donna

    Tuesday, October 5, 2010 6:46 PM
  • Hi Donna,

    It worked, view and scheduled. You are a genius :) Question is do I have to do that for all of the reports that I want to run in this manner?

    Regards


    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk
    Tuesday, October 5, 2010 7:11 PM
  • GMT London / Edinburgh / Lisbon / Dublin
    David Hodgson
    Add me on Twitter and LinkedIn
    www.infinitygroup.co.uk
    Tuesday, October 5, 2010 7:23 PM
  • Terrific, it is most likely a security issue that would take a bit of effort to track down, but at least it is working for now.

    There are most likely easier ways to get report subscriptions working.  This is one that I am fairly familar with so it is my 'fall back' since I haven't had any time to look at this part of CRM.  So yes, for now, you'll need to modify the datasource for your subscription reports.  The upside is that you don't have to worry about someone seeing data they shouldn't because you control who receives the subscription.


    Regards, Donna

    Tuesday, October 5, 2010 7:48 PM