When I first heard about Microsoft SQL Server Reporting Services, I thought it deserved a good look being Microsoft's first major reporting component for quite a while.
When I first played with SQL Server Reporting Services, I looked at the amount of HTML for a very nice and colorful (but also nicely printing) simple one page table view and found it to be around two pages of HTML in view source. Nice I thought. Also I had to weigh another couple of other nice features:
- Sub-reports, drill-downs, pivot tables.
- Auto-scheduling of output to email, file share, or your custom delivery extension.
- Exporting to most popularly used formats, Excel, Comma-delimited, Html, Xml, PDF, TIFF, and more.
- Extensible architecture allows custom data extensions (see link below) allowing reports defined off tables, custom object collections, collections, et al.
- Control Security tightly.
- Reports can be controlled by code to show a preview mode with zoom, export, paging/start/end, find/next, or just show a nice colored page that prints well.
- Use web services to get advance features out of the reports like listing folder contents of items that user has permission to view.
- Snapshots at particular times create histories of reports over time.
- There are good books out on it besides the books online that come with it. My guess is that it will have incredible support with any competitor.
- And lastly and importantly one can use a wizard to create a beautiful simple report in about 60 seconds versus the one hour of my manual method.
To run a report you simply use Response.Redirect("http://localhost/reportserver?%2freports%2freportname") or with the proper permissions, type the URL into a browser. That means a hyperlink is a very simple way to give access to your report. This makes it easy to place the report into sections, panels, frames, and table cells of your web pages. Other URLs give a preview screen with zooming, find/find next, etc where right-clicking gives a clean print also, like preview with a print button.
Currently I'm using LLBLGen Pro to get the proper data into fields of a parent/child table pair created just for the reports and then the report runs on this relation. All code and wizard to the report. No grief at all.
Gotchas:
The only problem I've had is when I went to transfer my reports to another server. The reports wouldn't run there due to some data connection problem no matter what I did. Even though I set all the data properties of the report the URL execution would only give the same error. The solution is that SQL Server Reporting Services is 2 parts, the Report designer where the data settings must be made, but also a server part that allows the management of the reports where the same settings must be also set. I suggest creating a shared data source and using that for all reports, so that the time for setting these things is reduced.
Also be sure to understand that each control in your wizard generated report is by default placed inside a list control container, and once you finish moving things around from where the wizard put everything you will see a lot of containers that maybe aren't needed since all your fields are involved with nothing but the company info, and contains no grouping. It is fine to delete these containers then, but since they are all nested, if you delete the top level everything inside will be deleted. So you must first drag the list holding your table control and the table column fields (or cut them works well) out of the container nest and then delete. If you had Country, State, Company as three fields in the header and you only wanted the Country to print when it changes, and same with state and company then that field should be in the outside list of the list nest, and state should be inside the outside list in the state list and inside the state list would be the company list etc. Maybe leave your company name and less important fields out of the default specification of what fields should be on report so subtotals are handled clearly. Subtotals for a grouping would need to be at the end of the proper nested list to sum properly. A field named sales with the sum function applied will sum the right subtotal if it is in the proper list associated with that group. The grouping property of a list control object determines how much gets summed or averaged or counted, and may contain a reference to a field in its own list or some other list as in: list2.grouping = list1_companyname; So one needs to be aware of the list structure.
By default , currently, lists have a page break associated that can't be turned of in the IDE due to a bug regarding missing checkboxes for such in the grouping aspect of the list. A workaround is to edit the RDL file xml by right-clicking the file and choosing "View Code" option, and then finding the pagebreakatend tag and changing its value from true to false. Table grouping's defaults are very acceptable to me. To get list grouping you put detail grouping fields into the page, and to get table grouping you put them into the grouping section in the wizard procedure.
Often I put new textboxes into a list and line them up fine, but in rendered version their content is spread way off the printable page. To fix this I put a new list inside the offending list to group the new textboxes and that contains them for some reason.
Sometimes you will experience alignment problems between sections or lists or between lists and tables and the workaround is to use Ctrl-Arrows to get things in line properly.
One important thing to know is that if you don't go into the Build menu/Configuration Manager of Visual Studio and uncheck the Build and Deploy checkboxes, each report will add one second to each project build you do to test in debug mode. With 50 reports, 50 seconds added!!
The header supports textboxes, but not fields, just globals and constants??
Coolness:
Sub-reports can be very easy here. Drag an existing report onto the design surface of another report that is open in layout view.
In place of a table column field expression like fields!amount.value, you can put: iif(fields!amount.value<0,(fields!amount.value), fields!amount.value) to put parentheses around negative values and set the format property of that columnar textbox to C to get commas and dollar signs with that for a nice financially clear format. You must set both format=C and use the above expression to get the desired effect. Simple. To create a custom function that can be called by property expressions, you open a report for design and choose Report Properties from the Report menu that shows up. The code tab allows you to paste in a function that you've tested elsewhere as there is no real editing and formatting capability here. To call your functions use:
code.MyFunction(myInput)
You could also use a custom assembly to facilitate the use of a reusable central repository of code to extend the functionality for multiple reports.
Page breaks are supported by rectangle, list, table, group, matrix (the cross-tab option), and chart controls by right-clicking and choosing properties causing a dialog with a number of before/after, fit on page if possible options and other things like header/footer repeating.
To place a page number: put a textbox on the header or footer, right-click the control and choose expression, expand the globals node and choose pagenumber and then replace, insert, or append.
To create a drilldown effect, set the hidden property of anything to true and then set the toggle property to any control in a containing group which are listed for you. Simple.
To add a total to a table column: put a textbox below the column in a list containing the table. Set its value to the expression: sum(fields!amount.value);
Better yet drag a numeric field from the fields list into the right list section or table grouping row, and the proper sum is added to your field for the grouping defined there.
Response.Redirect("http://localhost/Reports/Pages/Report.aspx?ItemPath=%2freports%2fbalsheet") as deployment URL gives a preview type page in the browser and you can still right-click the report section of the HTML page to get a printout not showing the numerous options on the preview page. My clients like this option.
Summation:
I've been using SSRS on two projects with no major problems. Now I have one solution for reports for Web and Windows forms applications.
Considering quality of the Visual Studio 2005 coming we needed a similar component for reports. I think we have been given that.
Good luck and send your questions to: tvoss@computer-consulting.com
or preferably put them up on the list server at: reporting@aspadvice.com
Code Magazine: 2004 Jul/Aug: Basics & Custom Data Extensions
Code Magazine: 2004 Sep/Oct: Mainly Security
120 day Trial software: SQLServer Reporting Services:
MSDN Magazine: August: Deliver User-Friendly Reports
A recent detailed article
Books Online for SQLServer Reporting Services
