Using Charts and Graphs to Visualize Your Data¶

This developer blog article was originally published on MetaCommunication’s website in March, 2010 and showcases the use of Microsoft Visual Studio and Microsoft SQL Server Reporting Services (SSRS) for using charts and graphs in Virtual Ticket reports.

The original article is no longer accessible on MetaCommunication’s website, but the text has been preserved below.

In this article, we take a look at how Microsoft SQL Server Reporting Services (MS SSRS) Reports can include charts and graphs based on your Workgoups 2010 data.

Graphic illustrating a Workgroups 2010 Productivity Report rendered as a text-based report and as an easy-to-interpret line chart.I admit it. I’m a visual person and a self-proclaimed fan of presenting information in as visual a manner as possible. Give me two versions of the exact same Virtual Ticket form, and ninety-nine percent of the time I’ll choose to use the version that I find more visually pleasing. Of course the information presented still needs to be meaningful and useful to me, but I’m convinced that an engaging presentation of information is directly related to how well end-users accept, adopt, and ultimately use the solutions with which they are provided.

To that end, I’ve recently been spending time working with Microsoft’s Visual Studio and MS SQL Server Reporting Services - two technologies that integrate directly into the Workgroups 2010 environment as custom SQL reports. Using these tools, administrators of Virtual Ticket can create some pretty detailed and extensive reports from the Workgroups 2010 system. Our Advanced Workflow & Scheduling solution, for example, includes a number of custom SQL reports for viewing Production Status information, reviewing Production Timeline data, and evaluating Resource Allocation for scheduling purposes. Each of the custom SQL reports included in this solution were built using Visual Studio and are run directly from Virtual Ticket through SQL Server Reporting Services.

Using Visual Studio, administrators can embed all the “regular” items you might expect on a report - static textboxes, lines, images/graphics, database fields, etc. These can be formatted in any number of ways, with data being grouped, subtotaled, totaled, etc...

The area I’ve been spending time with, however, has been with the Chart tool - a tool that allows you to easily embed charts into reports. The type of charts available will vary depending on your version of Visual Studio (for example, the 2003 version versus 2005 versus 2008, etc.), but with my version (Visual Studio 2008 Professional), I’m able to embed Column, Line, Shape, Bar, Area, Range, Scatter, and Polar charts into the reports I create. Regardless of the type of chart you use, there are three primary types of fields Visual Studio looks for when creating charts:

Data Fields: Data fields are the fields upon which you want to measure, or base, your report. For example, if you created a chart that displayed the sales by customer, you could use the sum of the invoice total [Sum(Total)] to show the sales amount for each customer within the chart.
Category Fields: Categories allow you to group data, and are also used to provide the labels for chart elements. For example, in a Sales by Customer chart, you could use the Customer Name to provide labels (and categories) for the displayed totals.
Series Fields: Series fields are optional elements that can add additional dimensions to your chart. For example, in a Sales by Customer chart, you could extract the Year from the Invoice Date, and use that as a Series field to display the current and prior years’ worth of data for the sake of comparison.

Depending on the data you are reporting on and the type of chart you want to embed in your report, you may elect to use any combination of these three field types to provide visual appeal to your reports. For example, you might include a pie chart for the jobs by customer in a given month, include a bar chart for the job charges by department or task on a given job, or include a line chart to compare the number of jobs this year compared to last year. The possibilities are endless. To illustrate the potential of adding charts to your reports - and to show how much more useful your reports can be - I’ve created a series of examples [TS18637 - Embedding Charts in SQL Reports - Index] that you’re welcome to download and try out.

Beyond giving your reports a more ‘visual’ feel, you might wonder what advantages there are to incorporating charts and graphs into your reports. For me the biggest advantages are that charts and graphs can provide a more useful way to present complex information in a way that is easier to understand for users, can show trends, and reveal otherwise hidden relationships. For example, I can create a report to list the sales by customer over a given timeframe [TS18638 - Sales by Customer Report], and see the details of each invoice; but adding a chart to this lets me more easily see the relationship of how much in sales came from each customer. I can also create a report to list the work performed and costs incurred by a department over a given timeframe [TS18639 - Costs by Department Report] and see the details of charges that were made; but adding a chart lets me visually see not only the department totals against each other, but also the cost center totals that make up each department.

What are some of the reports you’re running from your system? Could you use charts help make them more useful? Let me know what you think - I’d love to hear about them!