In the Microsoft Dynamics SL world it wasn’t until version 7 that users finally had more than one choice when it came up to reporting options. In SL earlier versions, customization and development of reports had to be done exclusively with Crystal Reports. One of the main advantages of using Crystal is that it has been the standard reporting tool in the Windows applications development with Visual Studio for many years. For developers familiar with Crystal, it was relatively easy to pick up the particularities of SL report development and start creating reports as soon as they became familiar with SL’s database schema.
With Dynamics SL 7 release, Microsoft introduced support for SQL Reporting Services as an alternative to Crystal Reports. Even though the most recent Dynamics SL statement of direction still mentions support for Crystal in SL 8, I believe it is clear what will be Microsoft’s directions in future versions with respect to reports as indicated with other products as GP. In SL 7, just a few reports are included out of the box and the most important thing is that there is no need to install SSRS server in order to use them inside of SL.
So with two available options, as developers, the questions we are commonly asked, not only from clients but from applications consultants, are: What are the main advantages of switching to SSRS? When is Crystal a better option than SSRS?
From a developer with more than 8 years working with Crystal and 4 years with SSRS perspective, these are the most important things to consider when deciding which tool to use:
SSRS Advantages:
1. Portability. SSRS does not require an SL client to run reports from application databases. Once an SSRS server already included in SQL Server is deployed, non-SL users can connect through their web browsers and use reports to query data.
2. Multiple data sources. An SSRS report can have multiple data sources each one pulling data from a different query. This means that different data regions can run completely independent queries. Crystal only allows a single query with joins linking all the selected tables and affecting the entire report. The workaround in Crystal has been to create subreports that connect to the main report but with SSRS this is not required.
3. Dynamic interaction. Users can interact directly with the report sections by expanding and contracting them. For instance, if the default behavior of the report is to show a summary of a series of transactions, by clicking directly on the summary it will expand the above section and show the details that add up to the subtotal.
4. Free form design. Even though for some reporting design needs this could be considered a disadvantage, I think that more flexibility is always better. The layout in SSRS is like a canvas that doesn’t necessarily reflect the output of the report so a developer can place the data regions wherever he wants. Crystal on the opposite divides the report in sections that directly reflect the final layout of the printed report with every object restricted to the section in which it was placed.
SSRS Disadvantages:
1. Reports that will be launched from SL using ROISRS must be developed with Visual Studio .Net 2005. Being that SSRS is a relatively new technology compared to Crystal (current version 12), there have been improvements to some of the limitations stated below but with the above mentioned restriction it is not possible to take advantage of new releases.
2. Design interface does not provide a zoom feature. This can really be a problem when the report expands horizontally and millimetric adjustments are required.
3. Missing important reporting features like printing group footers at the bottom of the page, restarting page count when group changes, etc. Basic functionality that Crystal has incorporated with new releases through its existence.
4. Missing built-in functions for expressions. For instance, Crystal provides with more than 50 financial functions while SSRS only has 13.
5. Expression editor is not user friendly. Writing complex business rules can turn quite complicated in a simple text editor that lacks syntax highlighting and other standard aids.
6. Expressions limitations. In SSRS the developer cannot indicate if an expression should be evaluated before or after records are read or printed causing problems like running totals in headers showing as 0. Expression values cannot be passed to sub-reports since data cannot be shared between objects.
7. Deployment. When a report deployed to an SSRS server needs to retrieve data from different application databases, multiple instances of the report and the data source need to be created, one for each database.
The conclusion is that depending on the reporting needs, SSRS is a very good alternative to share data with users without requiring a Dynamics SL license. At this point, it is clear that Microsoft will keep the support of Crystal since its capabilities have not been matched by SSRS yet. I expect for the next SL releases to see more and more SSRS reports and hopefully be able to start using improved design tools. So if you need more flexibility, a richer user interaction, and a broader target audience for your reports keep an eye on SSRS for your future reports development.