Comparing HTML Reporting and MS SQL Server Reporting Services
 
Published: 23 Sep 2004
Unedited - Community Contributed
Abstract
I thought I had it made, using my own HTML reporting methods for Web applications and Crystal Reports for Windows applications, but a new tool is now of interest. With MS SQL Server Reporting Services I now have one solution for reports for Web and Windows forms applications.
by Terry Voss
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 32630/ 103

HTML Reporting

Before I worked almost exclusively on web forms, I worked quite a bit with Crystal Reports and found it clunky, 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.

Here is the VB class:

 
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

Here are the CSS classes:
.report

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

Causing pseudo-code lines, in the web form, 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 page breaks 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 works well for a header, followed by a table of detail data. 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. 
 

Microsoft SQL Server Reporting Services

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:

  1. Sub-reports, drill-downs, pivot tables.
  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, 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 web services 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 come with it. My guess is that it will have incredible support with any competitor.
  10. 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

 

1. Professional SQL Server Reporting Services
by Paul Turley, et al; Paperback
 
2. Microsoft SQL Server 2000 Reporting Services (Database)
by Brian Larson; Paperback
 
3. Hitchhiker's Guide to SQL Server 2000 Reporting Services
by Peter Blackburn, William R. Vaughn; Paperback
 


User Comments

Title: Code for preview mode   
Name: Terry Voss
Date: 2007-04-26 5:27:01 PM
Comment:
For example in following report running url:
Response.Redirect("http://" & Utl.ComputerName & "/Reports/Pages/Report.aspx?ItemPath=%2fnwmrpts%2fpicklist0&rs:Command=Render&rc:toolbar=true")

Changing to: &rc:toolbar=false takes the toolbar with the mentioned controls on it. I haven't done more than that so don't know if there are any more customizations, but I'd guess yes.
Title: How to code to show preview mode on web   
Name: bp
Date: 2007-04-26 4:41:48 PM
Comment:
Can you elaborate:

"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."

I have a big book on SSRS, as well as the help with SSRS itself, and they don't appear to cover how to do this for display on a web page.
Title: Difference   
Name: Sanket Shah
Date: 2006-06-21 1:43:49 AM
Comment:
Hi .
It's nice article.but can u provide me the more description on difference between crystal reports & reporting services and what are the prons & cons of the both ...

email id : sanketshah_18@yahoo.com

Thanks in advance.
Title: can u give me more info   
Name: vrunda
Date: 2005-12-10 5:52:26 AM
Comment:
hi its nice articale but m not cleared abt reporting services. i have read that i can edit report by editing RDl file of Report but after editing RDL file have i to do anything els for running existing report with changes
pls can u reply me on id vrunda@icenet.net






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2018 ASPAlliance.com  |  Page Processed at 2018-10-19 4:43:19 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search