⚠️ This page has been archived

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

Home > Archive > SSW Standards > Rules > SSW Rules to Better Reporting Solutions

Do you agree with them all? Are we missing some? Let us know what you think.

Rules to Better Reporting Solutions

  1. Do you know the best tools for presenting reports to users?
  2. Reporting Services (Windows and Web)
  3. ASP.NET (Web)
  4. Kendo UI
  5. Power View
  6. PowerPivot
  7. Excel 2007/2010/2013 Data Visualization with SharePoint 2007/2010/2013 Excel Services
  8. PerformancePoint 2010/2013
  9. Crystal (Windows and Web)
  10. DataDynamics Active Reports (Windows and Web)
  11. XML/XSL (Windows and Web)
  12. Access (Windows and Web via SSW Access Reporter for IIS)
  13. Outlook Web Components (OWC)
  14. OLAP - Data Analyzer (aka Microsoft's Rich Client Solution)
  15. 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. In general, most reporting needs can be grouped into the following 2 categories:

    1. Detail Reports - Printable reports that show detailed information for each row in the database. (e.g. an Invoice)
    2. Summary Reports - Business Intelligence Reports that group and summarize data into counts and sums of individual rows (e.g. a Monthly sales with a chart). Typically, OLAP oriented tools MS Excel in this area.

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

    Below is a guide on the best options available for the Microsoft platform, with Pros and Cons for each.

    The scenario

    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/2014
    End Date: 1/4/2014

    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

    Conclusion

    For building reports, the most critical factors for a power user are:

    1. Development time
    2. Having the ability to put the reports on the web and having them to work on your iPad too (without installing an app)
    3. Having drill down functionality (for summary reports)
    4. Having reports with a query string and the parameters on it (e.g. so that you can click a link in the email)
    5. Offline support (only if absolutely neccessary)

    Which solution should I use when doing detail type reports? (usually printable ones e.g. an Invoice)

    Because you can't control the report length and need strong layout control, a proper reporting solution is the best option. Hence Reporting Services and Crystal (although not naturally my second choice). But the fact is you just dont know if the report is going to be multiple pages so:

    1. Web and Windows - SQL Reporting Services

    Which solution should I use when doing summary type reports (usually business intelligence ones e.g. monthly sales with a chart)

    Below is a general guide as to the order we choose for summary reports  (BTW - This list is not set in stone and we use all the below options for different clients):

    1. Web and Windows - SQL Reporting Services
    2. Web - MVC and Kendo UI
    3. Windows - Excel

  2. SQL Reporting Services 2012 (Web and Rich Client)

    SQL Reporting Services
    http://msdn.microsoft.com/en-us/library/ms159106.aspx

    Pros

    1. Short development time - 3 hours for sample report.
    2. No code.
    3. You can export reports to a large number of different formats right out of the box (Excel, PDF, HTML, Word, Images).
    4. You can schedule report execution and have your reports emailed automatically to you or a list of recipients (via Data-Driven Subscriptions).
    5. Developers can create reports using Visual Studio Business Intelligence Development Studio
    6. End Users can create ad-hoc reports using the Report Builder 3.0 click once application (same functionality as Visual Studio)
    7. Parameters are easy to add to the report and bind to the data.
    8. Grouping is simple.
    9. Drill Down without needing a tree view control.
    10. Built-in security module out of the box
    11. Rich set of charting controls and geospatial controls
    12. Reuse report parts in many reports
    13. Client side reporting using RDLC
    14. Can easily integrate into SharePoint to make publishing and sharing reports simple

    Cons

    1. Limited control set (although the controls are very rich). You are forced to use built-in controls. For example, you can't have "next month / previous month" hyperlinks like in ASP.NET (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. Difficult to integrate user input and dynamic behaviors like ad-hoc grouping
    <
    Figure: SQL Reporting Services 2012

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

    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.

    Samples

    SSW Exchange Reporter (Samples)

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

  3. ASP.NET MVC (Web Forms)

    If you have the skills, you should use MVC. This is for those who prefer the Web Forms.

    Pros

    1. Query Strings supported (e.g. URL can be emailed to another user who can then see the correct report).
    2. Complete Control over the parameters - e.g. "Current Month" hyperlink
    3. Complete control over the rendering of the output
    4. Can edit data via Databound Controls
    5. Sorting via hyperlinks possible
    6. Can make the reports more interactive, allowing such functionality as dynamic grouping, sorting and filtering

    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. Difficult to create reports that print adequately, although it is possible to do with CSS Printing.
    3. Multiple Page reports impossible.
    4. Cannot run offline unless the database is replicated and hosted locally (not recommended)
    5. Very difficult for Users to create reports
    6. Graphs - To show a graph you need to code OWC to generate a .png or get a 3rd Party Control like Dundas Charts
    7. No Date/time picker built in - have to use a 3rd party control or the AJAX Toolkit (or write your own)
    8. No export to PDF or other formats - you have to install Acrobat and print to PDF from the browser
    9. No nice Reporting Services interface - you have to create the navigation yourself
    10. Have to configure security from scratch (e.g. using the ASP.NET membership and roles providers).
    11. Hard to do drill-down. To do this, you need a Treeview with code
    12. There is OLAP support but you need to know MDX.

    Conclusion

    Good if you:

    1. Want to edit data in your report (e.g. the ASP.NET Bindable controls like the DataList and GridView have edit modes)
    2. Want dynamic grouping and cannot go to OLAP

    Samples

    More at http://www.asp.net/downloads/archive-v11/starter-kits/reports

    Figure: SSW Product Download Report (ASP.NET)
    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 - 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 - Implement the bar chart in the grid;

  4. Kendo UI

    Check out the Kendo UI website .

    Kendo UI report sample

  5. Power View

    http://thinknook.com/sql-server-powerview-2012-demo-video-2012-02-25/

    Power View report sample

  6. PowerPivot

    Check out the PowerPivot official site .

    PowerPivot

  7. Excel 2007/2010/2013 Data Visualization with SharePoint 2007/2010/2013 Excel Services

    Figure: Office Excel 2007/2010/2013 delivers new powerful data visualization tools
    SP Excel Service
    Figure: SharePoint Excel Services brings the spread sheet onto the web

    Pros

    1. Can run over the Web via Web Services
    2. Hides the Complexity of OLAP
    3. End Users can change it; most users are very familiar with Excel
    4. A visually appealing UI
    5. Full drill-down support with support for Cubes
    6. Can publish to SharePoint and allow users to interactively use the report, also gives you full version control

    Cons

    1. Requires rich client Install
    2. Requires the Excel 2007/2010 License
    3. Requires SharePoint 2007/2010 License for Excel Services

  8. PerformancePoint 2010/2013

    Create a KPI report
    Figure: PerformancePoint allows users to easily create KPI reports
    Drill through on charts
    Figure: PerformancePoint allows users to drill through on charts to get more details

    Pros:

    1. Easy user interface for creating KPIs, Scorecards and Dashboards
    2. Can share the data using SharePoint
    3. End users can change the reports
    4. Full drill down and drill through support
    5. Rich client is deployed using ClickOnce

    Cons:

    1. Requires SharePoint 2010/2013 with PerformancePoint 2010/2013 licence

  9. Crystal Reports (Windows and Web)

    rest in peace
    Report in Crystal Report
    Figure: We used Crystal (Windows) for our SQL Auditor tool to support disconnected opterations.

    Pros

    1. Quicker to develop than ASPX (around 5 hours for the sample)
    2. (Windows Client) Doesn't require a server-based product like SQL Reporting Services - can run offline
    3. Great Printing - Easy to export to .pdf for printing
    4. Easy to export to .xls for analyzing
    5. Very fine control over output appearance.
    6. Integrates well with .NET Strongly Typed Datasets and Objects.
    7. Good Drill down feature.
    8. There is OLAP support but you need to know MDX.

    Cons

    1. Custom Development environment
    2. Licensing is very expensive for more than 5 concurrent reports
    3. No sorting hyperlinks
    4. (Windows Version Only) QueryString cannot be emailed to another user
    5. Dead technology

    Conclusion

    Quite expensive for Web Clients - Only use on solutions that have a big investment in Crystal or you require an offline ability for a product (Windows Client only)

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

  10. DataDynamics Active Reports (Web and Windows)

    Basically an alternative if you were to choose Crystal. http://www.datadynamics.com/ You are going to a site outside of SSW

    Pros

    1. Cheaper Licencing than Crystal (Royalty Free)
    2. Xopy deployment of reports
    3. (Windows Client) Doesn't require a server-based product like SQL Reporting Services - can run offline

    Cons

    1. Support for product not as widespread
    2. Not in bed with Microsoft like Crystal
    3. There is OLAP support but you need to know MDX.

  11. XML/XSL (Windows and Web)

    Pros

    1. Can do great things like http://www.amorphous-media.com/client_demo/xmlreports/student_app.htm You are going to a site outside of SSW
    2. Can have client side filters and sorting
    3. (Windows Client) Doesn't require a server-based product like SQL Reporting Services - can run offline
    Cons
    1. Very long development time
    2. Impossible to make it print for multiple page reports.
    3. There is OLAP support but you need to know MDX.
    Report in XSL

    Figure: We used this method for reporting in the SSW tool Code Auditor (a Windows Forms application)

  12. Access (Rich Client and Web via SSW Access Reporter)

    This solution has worked since Access 97 to Access 2010 and 2013

    Pros

    1. Good Report development environment
    2. Very good printing support
    3. (Windows Client) Doesn't require a server-based product like SQL Reporting Services - can run offline

    Cons

    1. Access needs to be on the machine generating the reports (e.g. on the server when using SSW Access Reporter)
    2. Need an Access License
    3. Not designed natively for the web
    4. No drill-down features
    5. No OLAP support
    6. (Windows Clients Only ) Not every end user has Access (this is a show stopper if Access in not in the client's SOE!)

    Conclusion

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

    AccessReporter Demo

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

    View a Sample report

  13. 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
    Report in OWC
    Figure: Office web components enable interactive charts inside your browser, but require Office to be installed

  14. OLAP - Data Analyzer (aka Microsoft's Rich Client Solution)

    rest in peace

    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.

  15. OLAP - 3rd Party

    Alternatively you can use:

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

Acknowledgements

Adam Cogan
David Klein