Experiences with Microsoft SqlServer Reporting Services, Author: Terry Voss, 

Before I worked almost exclusively on web forms, I worked quite a bit with Crystal Reports and found it somewhat a kludge, but workable. When I tried to use it for web reporting, I found what I would expect to be about a page of HTML code would get about 20 pages of HTML generated. This led me to create my own HTML reports using one VB class called Html and 3 CSS classes called Report, Report1, and Invisible.

Imports Microsoft.VisualBasic.ControlChars

Public Class Html

Public Shared BeginTablewBorder As String = "<table border=1 cellspacing=0 cellpadding=0 width=100% class=eval1>" & CrLf
Public Shared BeginTable As String = "<table border =0 cellspacing=0 width=100% class=Report1>" & CrLf
Public Shared BeginPBTable As String = "<table border=0 cellspacing=0 width=100% class=Report>" & CrLf
Public Shared BeginRow As String = "<tr>" & CrLf
Public Shared EndCell As String = "</font></td>" & CrLf
Public Shared endRow As String = "</tr>" & CrLf
Public Shared endTable As String = "</table>" & CrLf
Public Shared br As String = "<br>" & CrLf

Public Shared Function BeginCellTxt(ByVal size As Integer, ByVal span As Integer) As String
Dim result As String
result = "<td colspan=" & span.ToString & "><font family=arial;sanserif; size=" & size.ToString & ">" & CrLf
Return result
End Function

Public Shared Function BeginCellNum(ByVal size As Integer) As String
Dim result As String
result = "<td width=33% align=right><font family=arial;sanserif; size=" & size.ToString & ">" & CrLf
Return result
End Function

End Class

 

.report
{
page-break-before: always;
margin-left: 60px;
}
.report1
{
margin-left: 60px;
}
.invisible
{
visibility: hidden;
}

Causing pseudo-code lines like this representing one row of a table:

 For Each trust As TrustEntity In trusts
  reportHtml &= Html.BeginRow & Html.BeginCellTxt & trust.Name.Trim & Html.EndCell & Html.BeginCellTxt & _
  bankNumber.Trim & Html.EndCell & Html.BeginCellNum & total.ToString("c") & Html.EndCell & Html.BeginCellNum &
  totalHours.ToString() + Html.EndCell + Html.EndRow : line += 1
  reportHtml &= Me.CheckPageBreak(False)
Next
 

 Private Function CheckPageBreak(ByVal force As Boolean) As String
  Dim reportHtml As String
  If line >= pageLines Or force Then
   reportHtml &= Html.EndTable
   reportHtml &= Html.BeginPBTable
   line = 1
   page += 1
  End If
  Return reportHtml
 End Function

The CheckPageBreak method made pagebreaks so easy that it is hardly worth thinking about anymore, and if needed you could add input to handle whether a header displays. The above work well for header info with a detail of table data below. If one needs a highly formatted set of data just use Visual Studio on an aspx page to place the labels and text fields with a variable like ||name|| for each data position. Then save the HTML as a file and do a text replacement of ||name|| with your data after grabbing the HTML into a stream. Set a literal control's text to your enhanced HTML and you have a report that looks like it did in VS. With this method you can use all the looks and color that VS easily affords us. I mention this method because I haven't seen it mentioned before even though I'm sure many use it.

I was very happy with this manner of doing reports and had no complaints from clients except maybe when they wanted to get involved in report design and they didn't know HTML at all. Simple reports took about one hour and I felt totally in control of the looks. Never again would a report package keep me from getting exactly what the client wanted. Maybe my only concern was that I'm not that artistic, so if my clients didn't input something creative my reports looked fairly simple usually, but the HTML code for a simple one page view was one page of HTML not 20. It would take a very good alternative to make me switch from my current HTML methods.

 

When I first heard about Microsoft SQLServer 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 SQLServer 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:

1) Sub-reports, drilldowns, pivottables.

2) Auto-scheduling of output to email, file share, or your custom delivery extension.
3) Exporting to most popularly used formats, Excel, Comma-delimited, Html, Xml, PDF, TIFF, and more;
4) Extensible architecture allows custom data extensions (see link below) allowing reports defined off tables, custom object collections, LLBLGen Pro collections, et al.

5) Control Security tightly.
6) 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.
7) Use webservices to get advance features out of the reports like listing folder contents of items that user has permission to view.
8) Snapshots at particular times create histories of reports over time.
9) There are good books out on it besides the books online that comes with it, my guess is that it will have incredible support irt any competitor.
10) And lastly and importantly one can use a wizard to create a beautiful simple report in about 60 seconds verus the one hour of my manual method.

 

To run a report you simply 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, tablecells of your web pages.  Other urls give a preview screen with zooming, find/findnext, 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:
 

One 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 SQLServer 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 out of the container nest and then delete. Don't delete the list around the table or you may find that nothing will show up for detail even though those fields are there. In fact if you move anything outside its list control you may find it not showing up.  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 off in any way I know of from the IDE. Books say the List grouping dialog should show a checkbox for the default value of a page break, but it is not there. Edit the xml of the .RDL file by right-clicking and choosing "View Code". Find the pagebreakatend tag and change its value to false. This workaround works fine. Table grouping does not have this bug and I like its defaults very well.

 

Sometimes I put new textboxes into a list and they spread across the screen way off the printable page and the only way I can control this is to put them inside a list inside the previous list.

 

Sometimes you will experience alignment problems which must be over-ridden by using Ctrl-Arrows to one pixel at a time adjust.

 

If you don't go into Build menu/Configuration and uncheck build and deploy for Reports, there will be a one second pause during your project build for each report in your Report Project, so 50 reports means 50 seconds more before your browser comes up in debug mode for your testing. (Very important)

 

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. Address your functions by appending the word code like this: 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 crosstab 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);

 

Response.Redirect("http://localhost/Reports/Pages/Report.aspx?ItemPath=%2freports%2fbalsheet") as deployment url give a preview type page in browser and you can right-click the report section of the HTML page to get a printout not showing the numerous options on the preview page.

 

I've been using SSRS on two projects with no major problems.

 

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