⚠️ This page has been archived

✅ New page with updated info: ssw.com.au

Home > Archive > SSW Standards > Developer .Net > SSW Rules to Better Reports on the Web

Rules to Better Reports on the Web

  1. Do you use the best tools to present Reports to users?
    1. Reporting Services
    2. ASP.NET
    3. Crystal
    4. Access (via Access Reporter for IIS)
    5. OWC
    6. OLAP - Reporting Services 2008 (aka Microsoft's Web Solution)
    7. OLAP - Data Analyzer (aka Microsoft's Rich Client Solution)
    8. Excel 2007 Over the Web
    9. OLAP - 3rd Party
  1. Do you use the best tools to present Reports to users?

    There are many ways to present your data to users over the Web. Below is a guide on the best options available for the Microsoft platform, with Pros and Cons for each.

    The data

    Scenario: I want my customers and staff to be able to see this data in a useful format. What are my options?

    Start Date: 1/1/2006    End Date: 1/4/2006

    Product Downloads
    SSW Upsizing PRO! 181
    SSW Code Auditor 114
    SSW .NET Toolkit 63
    SSW Performance PRO! (for Access 2000,2002,2003) 60
    SSW Exchange Reporter 58
    SSW Exchange Team Calendar (for Exchange Server 2000,2003) 53
    SSW Diagnostics 49
    SSW SQL Total Compare 49
    SSW SQL Auditor 39
    SSW Access Reporter .NET For IIS 34
    SSW SQL Deploy 33
    SSW eXtreme Emails! 31
    Total 764

    The obvious choices for displaying this data on the web are Reporting Services 2008 , ASP.NET 2.0 , and Crystal Reports . Let's take a look at how easy it is to complete this report using these and other solutions.

    1. Reporting Services 2008

      http://www.microsoft.com/sql/evaluation/bi/reportingservices.asp

      Pros

      1. Short development time - 3 hours for sample report.
      2. No Code.
      3. Parameters are easy to add to the report and bind to the data.
      4. Grouping is simple.
      5. Drill Down without needing a tree view control.
      6. Data drive Subscriptions are supported out of the box.
      7. Multiple Export formats out of the box including Excel and PDFs.
      8. Built-in security module out of the box
      9. Visual Designer
      10. Ad-hoc Reports via the Report Builder (a .NET client deployed via ClickOnce)

      Cons

      1. Poor Parameter support. You are forced to use built-in controls. For example, you can't have "next month / previous month" hyperlinks like in ASP.NET 2 (see below)
      2. Query string doesn't change when you change the parameter values in a report. You have to re-submit the page (no AJAX).
      3. Can't separate SQL into a strongly-typed dataset or middle-tier objects like in ASP.NET (unless you write a lot of code to support your own provider).
      4. Printing support isn't strong - there is no pixel-by-pixel control possible.

      Figure: SSW Product Download Report (Reporting Services 2008)

      Conclusion

      Reporting Services is the way to go if you do not need

      1. Dynamic grouping.
      2. To write to your database via the report.

      Demo

      See SSW Exchange Reporter (Samples)

      Note: When comparing two sets of values in a report, you will avoid showing change as a percentage

    2. ASP.NET 2.0

      Pros

      1. Complete control over the rendering of the output
      2. Query Strings supported
      3. Control over the parameters - e.g. "Current Month" hyperlink
      4. Can edit data via Databound Controls

      Cons

      1. Long development time of sample report - 8 hours to convert including formatting, sorting and adding 3rd party parameter controls (like Date Time Pickers)
      2. Graphs - To show a graph you need to code OWC to generate a .png or get a 3rd Party Control like Dundas Charts
      3. No Date/time picker built in - have to use a 3rd party control or the AJAX Toolkit (or write your own)
      4. No export to PDF or other formats - you have to install Acrobat and print to PDF from the browser
      5. No nice Reporting Services interface - you have to create the navigation yourself
      6. Have to configure security from scratch
      7. Hard to do drill-down. To do this, you need a treeview with code

      Conclusion

      Good if you:

      1. Want editable data
      2. Want dynamic grouping and cannot go to OLAP

      More at http://www.asp.net/ReportsStarterKit/

      Demo

      Figure: SSW Product Download Report (ASP.NET 2.0)


      http://www.ssw.com.au/timeproonline/Report/ProductDownloadGraph.aspx

      Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
      
              If Request.QueryString("DateFrom") <> "" Then
                  txtDateFrom.Text = Request.QueryString("DateFrom")
                  txtDateTo.Text = Request.QueryString("DateTo")
              End If
      
      
          End Sub
      
          Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
      
      
              Response.Redirect(Request.Url.AbsolutePath.ToString + _
                  "?DateFrom=" + HttpUtility.UrlEncode(txtDateFrom.Text) + _
                  "&DateTo=" + HttpUtility.UrlEncode(txtDateTo.Text), True)
      
      
          End Sub
      
      Figure: ASP.NET 2.0 - Code to read from and write to the query string

              ...
      		<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
                  AutoGenerateColumns="False" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None"
                  BorderWidth="1px" CellPadding="3" CellSpacing="2" DataSourceID="SqlDataSource1">
                  <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
                  <Columns>
                      <asp:BoundField DataField="FileNameURL" HeaderText="FileNameURL" SortExpression="FileNameURL" />
                      <asp:BoundField DataField="ProdName" HeaderText="ProdName" SortExpression="ProdName" />
                      <asp:BoundField DataField="DownloadCount" HeaderText="DownloadCount" ReadOnly="True"
                          SortExpression="DownloadCount" />
                      <asp:TemplateField HeaderText="Graph">
                          <ItemTemplate>
                              <img src="Images/chartpixel.gif" 
                                   height="10" 
                                   width="<%#DataBinder.Eval(Container.DataItem,"DownloadCount")%>" />
                          </ItemTemplate>
                      </asp:TemplateField>
                  </Columns>
                  <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
                  <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
                  <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
                  <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
              </asp:GridView>
      		...
      
      Figure: ASP.NET 2.0 - Implement the bar chart in the grid;
    3. Crystal

      Pros

      1. Established but not mature!

      Cons

      1. Dead technology
      2. Licensing is expensive
      3. Development environment

      Conclusion

      Only use on solutions that have a big investment in Crystal

      George Doubinski said it best: "My major pain with Crystal has been stability of their development environment. Im not sure if they've done anything in 2.0 but in 1.1 it was nearly unusable. The hoops to jump through to simply change data source was bordering on insane, for example. The other issue is, obviously, cost when scaling."
    4. Access (via SSW Access Reporter)

      Pros

      1. Good Report development environment

      Cons

      1. Access needs to be on the server
      2. Need an Access License
      3. Not designed for the web

      Conclusion

      Only use SSW Access Reporter for solutions that have a big investment in Access.

      http://www.ssw.com.au/ssw/AccessReporter/AccessReporterDemo.aspx

      Figure: Tools like SSW Access Reporter help to display your Access data on the web

      View a Sample report

    5. Office Web Controls (OWC)

      Pros

      1. Allow you to embed interactive office documents in to HTML
      2. Supports updates to the datasource via the control
      3. Easy to create - just save a document in "Interactive HTML format"
      4. Supports pivot tables, spreadsheets, charts

      Cons

      1. Require a download or to have office installed (typically you would only install on an intranet)
      2. They run as an ActiveX control - so have limited access to your local system

      http://www.ssw.com.au/timeproonline/Report/ProductDownloadGraph_OWC.aspx

      Report in OWC
      Figure: Office web components enable interactive charts inside your browser, but require Office to be installed
    6. OLAP - Reporting Services 2008 (aka Microsoft's Web Solution)

      Pros

      1. Same Pros as SQL Server 2008 Reporting Services
      2. Fast runtime experience - as the data is pre-agreggated in the cube, we can query data must faster.
      3. Very fast to develop
      4. Flexible - can add lots of dimensions and easily compare data

      Cons

      1. Using OLAP is often overkill - in the case of our "Product Download" sample - for example it is hard to design a query/cube to compare data from two arbitrary periods of time because data is pre-aggregated.
    7. OLAP - Data Analyzer (aka Microsoft's Rich Client Solution)

      Pros

      1. A visually appealing UI.
      2. Full drill-down support.
      Figure: Office Excel 2007 delivers new powerful data visualization tools

      Cons

      1. No longer supported. This functionality has been moved into Excel 2007.
      2. Microsoft dont have a web version of Data Analyzer.
    8. Excel 2007 Data Visualization

      Figure: Office Excel 2007 delivers new powerful data visualization tools

      Pros

      1. A visually appealing UI
      2. Full drill-down support with support for Cubes
      3. Can run over the Web via Web Services

      Cons

      1. Requires rich client Install
      2. Requires the Excel 2007 License
    9. OLAP - 3rd Party

      Alternatively you can use:

      1. http://www.thinslicer.com (free)
      2. http://aladdin.sdm.com.au/demo/Template/AladdinFixedReports.asp
      3. http://www.zaptechnology.com