The part that is the major deviation from normal SQL RS work, is how to deploy reports. Even though normal deployment might seem to work, the report won't be recognized by CRM properly.
So, what do you need to do?
1. Create a report that does NOT use a Shared Datasource. Instead create an embedded datasource called CRM that connects to the CRM database server. If you are going to use the Filtered Views, you have to use Windows Authentication. Save and build the new report (DO NOT deploy to the server).
2. Open CRM, Go to "Workplace" and select "Reports". Click "New" in the list. You will see a form where you can select the rdl-file and also select where in CRM the report will be available. Select the rdl-file that resides in the VS Project folder. Press Save.
If, at this point, you had used a Shared Data Source, you would be getting a very complicated error, as described by Menno here: http://blogs.msdn.com/mscrmfreak/archive/2006/04/27/584595.aspx
If all went well, the report will be uploaded correctly.
When uploading the report, CRM will replace the datasource in the rdl with the standard shared datasource. So, your report will still be movable.
Also, when uploading reports like this, CRM will hide all parameters starting with "CRM_". There are several parameters that CRM will fill with data for you if they exisit. For instance the parameter "CRM_URL" will be set to "http://
Please note that all deployment of reports to CRM should be done in this manner and never directly from Report Designer. This is a bit of a hazzle since it is a bit tedious compared to just deploying from the Report Designer.
In CRM 4 there is a very nice wizard in the application for generating reports, even though it is not nearly as advanced as the report designer.
Gustaf Westerlund
Microsoft Dynamics CRM Consultant
WM-Data/Logica CMG
www.logicacmg.com
Thank you, Gustaf. Short and sweet, this post gave just the information I was looking for.
ReplyDeleteHi Behzad,
ReplyDeletehappy to be of service!
Gustaf
Hi Gustaf,
ReplyDeleteI finally found somebody explaining about rdl file :).
I have srs custom report rdl(created in 3.0 and changed the connection string for 4.0) file and i tried to add into 4.0 reports.
It did add, but i get the following error message when i try to open the report.
Reporting error
The report cannot be displayed
Try this action again. If the problem continues, check the microsoft dynamics CRM community for solutions.......
Any help is appreicated.
Hi!
ReplyDeleteI'm sorry, i havn't had the opportunity to work with RDL:s in relation to CRM 4 yet, but I would start by looking into the CRM SDK for more information on how to publish new reports.
There most certainly are differences from CRM 3 since reports now support multilanguage.
Gustaf
Hi Gustaf,
ReplyDeleteThanks for the reply.
I did download the SDK for 4.0 and there is no information about report writer??.
There is no instruction about how to create SSRS report in crm 4.0.
No luck in google search either.
Uma
Hi Gustaf,
ReplyDeleteYour blog has been very informative and awesome :) Keep up the good work!!
Our company is using Reports in CRM 3.0 with SQL 2005. We use VS 2005 to write the custom reports. However, one of our CRM users keeps experiencing a problem that I've been troubleshooting for a while but there is never any progress... just wondering if you could give me a suggestion.
For some reason this one user cannot see his reports whatsoever. He still can see the list of CRM reports, but running the report will just give him blank result. Even he cannot fill in the parameters for some of the reports. Weirdly enough, there is never any error messages on his screen, so I can't really track on it. Other users who have the exact same permission as his, can run the report just fine. We are using MSCRM shared data source for the custom reports, and I've checked that he is a member of the SQL Reporting Group already. Now everytime he wants to run a report, he has to request me to run the report and print it out to him.... not so convinience.
Could you please give me an advice on how to troubleshoot this issue? I've tried running CRM Diagnostic Tool (couldn't get anything out of it though), applying the latest CRM patch, even changing his computer, but none of these work.
Any help is appreciated. Thank you very much, Gustaf! :)
-Elisabeth
Elisabeth,
ReplyDeleteThank you for your kind words! I am just about to go back to work from my 4 month parental leave and I will try to find some time to write some on this blog. Appreciative comments like yours are really encouraging!
Your problem is interesting and my first thought is that there has to be some factor that separates this user from the other users or his computer, I would check the following:
- His placement in the AD, does he have some special permissions or restrictions
- His computers placement in the AD. Any special settings in realtion to the reporting servers name/CRM servers name.
- Anything special about his computer.
I would try the following:
1. Try logging in to CRM from a computer where you know it works for another user. (Remove the CRM server temporarilly from the Local Intranet Sites or Trusted Sites to force the login credentials dialog). If it work from this computer, there is probably something wrong with the users computer or where it is placed (server name lookups etc). If it doesn't work, try the next step:
2. Try to run the reports directly from the user. Use IE and go to the site http://[reportserver]/reports and try to run the reports manually. Try the report "User Details" (I think it is called that), since it doesn't require any input. If this works, there is probably some problem in the name resolution of the report server from the users computer.
Try this out and see if it helps, if not, please explain your results here and I will see if I can help you some more.
Hi Gustaf,
ReplyDeleteWe want to use stored procedures for custom CRM SSRS reports. The problem is when we use stored procedures, the entity specific context sensitivity is lost. So, we see basically activities against all entities and not just one. It works fine if the SQL is within the report as text.
Is there any way to use stored procedures with custom CRM reports?
Thanks!
SA
Hi!
ReplyDeleteFirst of all I want to remind you that it is not supported to modify the CRM database in any way, including adding stored procedures.
The supported way of adding stored procedures is to add a new database and put the SP in this DB instead and let it address the CRM DB.
You didn't write anything on how you had acctually set it up but I would just like to make it clear. That doesn't mean you can't do it, just that it might break when installing a hotfix, rollup or upgrading.
I am not sure exactly what your problem is, but I would probably try to encapsulate the Stored Procedure in the "Extensions Database" inside a view and make sure the view works as it should first. Then use the view from SQL RS.
I am not particulary good concerning T-SQL and writing stored procedures, so please take my comment as such.
Perhaps there might be some reader of this blog with greater T-SQL/SQL RS skills that could be of service.
Hi Gustaf, how are you doing? This is Elisabeth again. Thanks a lot for your reply! :)
ReplyDeleteAbout the problem with the specific user who couldn't open his CRM report whatsoever, I actually just found out that his CRM Domain Name didn't match with his domain name on the AD (this user has a long last name and his manager had to cut it to fit as a Domain Name; however, he mistakenly put a wrong name under his CRM User Info). That was why he couldn't run the report from any computers.
So now that we changed his CRM Domain Name, he can run any CRM reports just fine on any computers EXCEPT his own machine. When he tries to run the report, the report screen just never shows up. However, he can view the report from any other machines or through VPN access or Terminal Service(with the same user permission). His pop-up blocker on his computer is off; I'm guessing this is an issue with Active-X on his Internet Explorer. Therefore I checked the setting on his IE, under Tools, Internet Option, Programs and Managed Add-On, that he actually already has "RSClientPrint.dll" (a Reporting Service Client Print) enabled on his IE.
Do you have any idea what's preventing his Report window to pop up? Any help is appreciated. Thank you very much, Gustaf! :)
-Elisabeth
Hi,
ReplyDeleteI have a fairly new system setup with Server 2008/SQL 2005 and MS Dynamics CRM 4.0. I have already customized and created new fields and it is working 100%. I do have a problem with the Reporting though. When I go to Reports in the CRM and Select for instance "User Summary" and from the following screen select any item from the following list I get the following error: "An error has occured - Try this action again. If the problem continues, check the Microsoft Dynamics CRM Community for solutions or contact your organization's Microsoft Dynamics CRM Administrator. Finally, you can contact Microsoft Support."
I did make sure everything is properly installed. Reporting services was installed prior to Microsoft Dynamics CRM?
Any possible solution?
Thanks
Kind Regards
Henk Koekemoer
Hi Henk,
ReplyDeleteI don't have any solution at the top of my head but I would suggest trying to find the error. Try the following:
1. Try from different users and different computers. Also try from the CRM server by using remote desktop.
2. Check any loggs, and switch on the CRM tracing (http://support.microsoft.com/kb/907490/en-us) if there is anything related you can find.
3. If you still havn't found anything, try to install on a VPC and see if it still doesn't work. If it works, try to find what differs between the installations.
Henk
ReplyDeleteI had this same problem. I discovered that when I had installed the latest SQL updates, it broke the SSRS connector with CRM, and therefore I had to re-install it - it should be on the CRM install disk.
When this has been done, start up the Report Configuration Manager, and redeploy it.
I hope that this helps you.
Thanks gustav!
ReplyDeleteI had a problem with uploading my rdl report and then I read your blog... so i found out that it was because I was using a shared datasource :p
Glad that you had use of my comments!
ReplyDelete