22 Limitations or Things Hard to Do in SSRS
Today many customers are moving away from Crystal Reports. Knowing the limitations of SSRS can help you to avoid pitfalls.1. Hard to Debug because of Vague Error Messages
It is time consuming to fix issues in rdl because the error message usually is not clear. For example, when we tried to make subreport showing up, there was an error message "Subreport can not be shown." There is no detailed information why the subreport cannot work. We checked the SubreportProcessing handler, and nothing seems wrong. The subreport itself seems OK as well. After a lot of trials, finally we found SSRS does not like the filter in the table of the report. The platform is Visual Studio 2005, and the filter expression is logically correct because the same one can work in SSRS 2008. After we changed the filter to the way, subreport worked very well. We found that error message is not clear enough is a general issue.
No detailed error message causes debugging and fixing errors in rdl very hard and cost more time. A good enough message should tell users why things go wrong, where the problem is, and how the issue can be fixed.
2. Object Overlapping
In Crystal Reports, report object overlapping is very common. The can save extra space and makes the report more compact, and have more information in one page. If the z-order of the overlapping report objects is correct, objects can function as expected. However, in SSRS, you need avoid report object overlapping as much as possible because overlapped object will not work as expected. It might have the correct result in pdf like print preview, but not in the regular preview.
Please refer to the following image that show two rectangles overlap each other, and you will see clear what is wrong.
The above picture show a report in design, preview and print preview respectively. You can see in the preview, the rendering is incorrect.
The report shows the bug above can be downloaded here. In Crystal Reports, report object overlapping is very common since overlapping can save space and has special effect for report layout. Unfortunately SSRS cannot handle report object overlapping well since the behavior is not consistent. Because of this bug in SSRS, after Crystal Report is converted to SSRS, more manual fixed is needed to change the overlapping object sizes and positions.
3. No Report Explorer or Field Explorer
There is no Crystal Reports like "Report Explorer" in embedded designer or Report Builder. Even if you have quite a few report objects, from report explorer, you can find them easily.
Report Builder does not have the feature like report explorer, if a report has hundreds or even thousands of report objects, it is very hard to find a report object you are looking for. Report Builder also lacks the field explorer, so there is no way to list all the expressions used in a SSRS report. It is true there are list of parameters and database used in the report, but there is no list for expression used in a report.
For simple reports, this is not a big deal, but for complicated reports, lacking report explorer impacts usability.
4. Hard to Debug Expression or Custom Code
When you are in Crystal Reports Designer, you can go to Formula Workshop to change the formula, and you can also verify whether the formula is valid by click the "check" icon in the toolbar or the formula will be automatically checked when you close the formula workshop. The formula validation mechanism can help to debug any potential formula errors.
However, in SSRS, there is no way to validate an expression from the expression editor. Here is the screenshot of the expression editor from Report Builder.
In SSRS formulas can be validated only when you try to run the report. Even if there is a report expression error reported, it is hard to find the formula and fix it. It would be very helpful if Microsoft can provide a mechanism to validate an expression from the expression editor.
5. Group Selection Formula is Not Supported
In Crystal Reports, you can filter you data either from record selection formula or group selection formula. Group selection formula provides a neat solution to filter report data based on the summary of groups. For example, you have customer table, there is country and sales fields, you can filter data from customer table based on summary of sales great a certain number.
In SSRS, there is no feature for Group Selection formula, to achieve the same result in SSRS, there are two solutions, one is the change the sql query by using subquery. It is doable, but you need know more about how to write subquery. Another solution is to dynamically show or hide group in the report based on summary values of the group. We would like to say either of the two ways is not as easy as group selection formula in Crystal Reports. By the way, if you want to filter the group data in either dataset or matrix level, it will not work because you cannot use aggregation function there. This is a limitation in SSRS. If you need more details on how to do group selection in SSRS like Crystal Reports, please refer to our article on converting Crystal Group Selection formula to SSRS.
6. TopN with Others
In Crystal Reports, many customers like the TopN or BottomN feature. You can easily get the top or bottom 10 sales by country group, and put all the rest to "Others". In SSRS there is a way to do TopN and BottomN, but you cannot put all the rest into "Others" in the report level.
There are some discussions on TopN with "Others" you can find from internet. The solution is to implement "Others" in query level. There is no easy way to do this. In addition, even if you implement the topN with others in query level, you probably still need change the report as well.
In short, TopN with Others is one of the most difficult things to do in SSRS.
7. Do not Use Page Number or Total Pages in Report Body
Not sure why there is such a limitation in SSRS. In SSRS, you can absolutely not use "PageNumber" or "TotalPages" in report body area, and you can only use these two special fields in Page Header or Page Footer. This seems by design. If you really need refer to page number or total pages in the report body, you only chance is do the pagination calculation yourself. This is doable for simple report, not for reports with complicated layout and conditional formatting.
In Crystal Reports, page number and total pages special fields can be used anywhere including report detail section. Usually they are referenced to do the conditional formatting, for example, making some fields show or hide depending on page number. When you convert reports with this, you have to remove these references to page number or total page.
8. Limitations on Vertical Lines and Rectangles
Some reports look more like a form. Data for these will come from database, but there are some parts for customers to fill in. For these kind of reports, usually there are rectangle boxes or lines span from page head straight to page footer. This kind of reports can be easily done with Crystal Reports, but it will encounter technical difficulties in SSRS.
The problem is not because SSRS is not powerful enough, but because SSRS have limitations in rectangles and lines. It is very hard if not impossible to have a rectangle to span from page header to page footer. Some customers complain this and submit a bug to request a fix, but there is no plan to fix this in the near future from Microsoft side. There is no good approach to achieve this. Some suggested having a background image for a workaround. To make a good background image just fit is not easy.
When you use SSRS, please keep in mind there are some limitations on vertical lines and rectangle objects.
9. Watermark in a Report
In Crystal Reports, Watermark can be done with the feature called Section Overlay. You can add any image at any position. In SSRS, you can add the watermark as report background image or report body or tablix background image. It seems all right, but there is a problem here, usually you have to change the background image just right. You need modify the original image to a good size. In Crystal Reports you usually do not need touch the original image for background.
The reason SSRS watermark requires more work is that, it lacks the feature to position and size the watermark. If SSRS had the background properties like the "Sizing" in SSRS image, adding watermark would be much easier.
SSRS background image lacks the properties like what showed above.
10. "Continue" to Next Page
If there is more data to show on next page, some Crystal Reports will show a message like "Continue to Next Page...". This can be easily done in Crystal Reports, but very hard to do in SSRS if not possible. The reason is due to the fact that there is no "Next" function in SSR although there is "Previous" function in SSRS.
This is a small usability issue. It is nice to have. But if you do not have it, it will not lose any meaningful information because you can use "Page N of M". If N and M is not equal, you know there is more data from next page.
11. Too Many Blank Pages
It is true Crystal Reports has the blank page issues too, but blank page happens very occasionally in Crystal Reports. In SSRS, many customer experienced the blank pages issue. According to Microsoft, if the report width plus left margin and right margin is greater than the page width, or report height plus top margin and bottom margin is greater than page height, blank page can show up. The two sizes must be equal or less than the page width or height. By fixing these numbers can reduce blank pages. However we also find blank pages show up even if there is no problem of page margin and report sizes. Actually blank page happens more often in SSRS 2005 than SSRS 2008.
12. Keeping Together does not Work well
Keeping together is a feature to avoid part of content in one page and part of them in another page. Logically there is occasion that content must extended more than one page which makes keeping together impossible. This feature is to avoid splitting content into more than one page if possible. In Crystal Reports, almost anything can keep together, report object, section, area, group, and keeping together feature is almost always working as expected. While in SSRS, the feature is called "Keep contents together in a single page if possible. In SSRS only group and rectangle can be kept together. Rectangle can be used as a container to host other report objects in SSRS. Objects or row of tablix cannot be kept together.
From our experience, in SSRS, Keeping together works for grouping, and although it is available for rectangle, it does not work well for rectangle. If it works you are lucky. This feature is buggy in SSRS.
13. Inflexible Pagination
Crystal Reports is known for printing and pagination is one of Crystal's advantages over SSRS. Because you can reset Page Number After, create New Page Before, and New Page After in both section level and report area level. You can create new page or reset page number almost anywhere you like. While in SSRS, reset page number is only introduced in SSRS 2008. You have to find a workaround for SSRS 2005. In addition, all these three are only available in group level, not in the row level of tablix.
In your Crystal Reports, if you have section that resets page number, create new page before or after the section, this kind of pagination information will be lost when you convert the Crystal Reports to SSRS.
14. Adjust Alignment is not Supported
There are four alignment options in horizontal text adjustment in SSRS. They are "Left", "Center", "Right" and "Adjusted". In SSRS, "Adjusted" alignment is not available. Some developers complained when they reported to their managers, their managers could not believe adjusted alignment cannot be done in SSRS. Unfortunately this is a limitation in SSRS and we cannot do much to get around.
15. Drop Shadow Adornment is not Supported
In Crystal Reports, you can do drop shadow as the adornment for object border as follows.
Drop shadow is not supported in SSRS. This is a feature not available. It seems tiny, but could be useful for some customers. When your Crystal Reports object with drop shadow is converted to SSRS, the drop shadow information will be lost. In addition, in SSRS, you can choose just one line style for adornment border while in Crystal Reports, you can choose line styles for left, right, top and bottom independently.
16. Rounding Rectangle is not Supported
Here is a round rectangle you can do in Crystal Reports.
In SSRS, rounding rectangle is not supported. When your Crystal Reports round boxes are converted to SSRS, they will become regular rectangles and rounding information will be lost.
17. Ranged Parameter is Not Supported
In Crystal Reports, when you would like to filter data based on parameter with lower boundary value and upper boundary value, you can use ranged parameter. When the ranged parameter is prompted for input, it will ask for two values, one is the lower value, and the other is upper value. When you refer to the ranged value in Crystal Reports, usually you can use "Min({?RangedParameter}) to retrieve the lower value, and "Max({?RangedParameter}) to get the upper value.
Because there is no ranged parameter support in SSRS, you need use two parameters instead of one ranged parameter in SSRS. If you use Crystal Migration Services to convert your reports, and ranged parameter will automatically be converted to two parameters.
18. MultiColumn Report can be Done, but very Tricky
Multicolumn report can be used to print labels. In Crystal Reports, there is built-in support for mulitcolumn reports. In Report Creation Wizard, there is "Mail Label" choice, and this will guide you to create a multicolumn reports. Here is part of the screen shot for two column mailing label.
In SSRS, there is no native support to create multcolumn reports and this kind of reports is very tricky. Basically you need use tablix inside tablix for multicolumn reports. It is doable, but the workaround needs more effort. Crystal Migration Services can convert your multicolumn Crystal Reports for you. So you do not need know the details of how it is done. If you are interested, you can download a sample from Crystal Migration Services samples page.
19. Limitations on Aggregation Functions
In Crystal Reports, you can use aggregation functions almost anywhere.
In SSRS, if you do not use aggregation correctly, you may get the following error message.
- Aggregate functions cannot be used in dataset filters or data region filters.
- Aggregate functions cannot be used in data row sort expressions.
- Aggregate functions cannot be nested inside other aggregate functions.
- Aggregate functions cannot be used in group expressions.
When your Crystal Reports file is converted to SSRS, if you encounter any errors above, you need find a workaround. In many cases, changing the report logic is necessary.
20. Dynamical Page Header or Footer
In Crystal Reports, page header and page footer can have more than one section. You can have the conditions to determine which section will show and which one will hide. However in SSRS, there is only one place for page head, one page footer. You can not split them. We have to merge all the page header sections into one page header in SSRS, and all the page footer sections into one page footer as well. For dynamical page header or footer Crystal Reports, after conversion, the page header and footer will not looks good. The will be extra spaces in either page header or page footer. One thing you can do is to squeeze them together. This seems working, but many objects will overlap each other.
21. No way to pass values from Subreports to Main Report
In Crystal Reports, main reports and subreports can share variables. It is a two way process, from main report to subreport and from subreport report to main report. So you can calculate summaries in two subreports, and add them together in the main reports.
In SSRS, you cannot share any variables between main reports and subreport, and the only thing you can do is to pass values from Main Report to subreport by report parameters. There is no way to pass values from subreport to main report. There is no such mechanism existed.
Therefore, if any Crystal report has shared variables between main report and subreport, most likely you need redesign the report and change the logics to avoid variable sharing.
22. Extra Space because of Page Header
This seems a problem only in SSRS 2008. SSRS 2005 is OK. If you have page header in a report, and in the first page, not showing the page header, the space for page header is still there. There is no way to get rid of this extra space.
If a page header on a report is suppressed with PrintOnFirstPage, it generates empty space on the report first page. For more details, please check the following link.