Visual Studio Reporting: When You Absolutely-No-Excuses Have To Produce a Sales Report with Visual Studio

Howdy readers, Lafe here. About once a month, my partner in crime, Rich Seeley, has been doing technical takeovers of this blog. Here's his latest post, which takes a look at Visual Studio reporting.

So the company you are coding for wants a nice report showing how sales of their line of jeans featuring simulated motor oil and grease stains are selling in various markets. All the information they want is stored somewhere in the sales database. It's just a matter of getting all the names and numbers to line up right so the sales reps, managers, and executives can see how they're doing.

Is this a job for a Visual Studio developer? The answer is a definitive yes and no. A Google search on "visual studio reporting" indicates that yes it can be done but no there isn't very much information on how to do it. Paging through some Visual Studio how-to after-market books draws a blank when looking for guidance on generating reports.

So you might conclude that report generation is not a popular task with Visual Studio developers. It is possible that sales assistants using something like good old Crystal Reports are doing this kind of thing. It's possible.

Some Tools Might Help

However, a quick check of Wikipedia tells you: "Crystal Reports became integrated with Visual Studio versions prior to 2010. Crystal Reports competes with several products in the Microsoft market, such as SQL Server Reporting Services, XtraReports, ActiveReports and List & Label. Users can also access Crystal Reports from outside of Microsoft environments; this allows (for instance) Java developers to build applications with Crystal Reports components."

Tools and extensions are out there for building applications with reporting capabilities. If you are not an expert in putting reporting capabilities into a sales app there are places on the web where you can get some guidance.

The Visual Studio Marketplace offers Progress Software's Telerik Visual Studio Report Designer. It is described as "a lightweight reporting solution for all .NET cloud, web, and desktop platforms which targets developers and end-users alike. Rich interactive and reusable reports can be quickly created in Visual Studio …"

Progress offers detailed instructions for using Telerik, which includes a Report Wizard that might be helpful for those of us in the Sorcerer’s Apprentice category.

What Microsoft Offers

The latest version of Microsoft’s own Microsoft RDL report designer, projects and wizards for creating professional reports with Visual Studio 2017 has garnered more than 86,464 installs on the Microsoft Reporting Services Project page in the Visual Studio Marketplace.

"This package provides support for the .rptproj type and is designed for the most recent versions of Microsoft Reporting Services," Microsoft explained on the download page. "This included the Report Designer authoring environment, where you can open, modify, preview, save, and deploy Reporting Services paginated report definitions, shared data sources, shared datasets, and report parts."

In the comments section, a few users reported problems with the install but it seemed to be isolated instances. And it figures that the users who downloaded and had no trouble are unlikely to go back to the page and write a glowing comment.

There was also a complaint that the SQL Server Data Tools (SSDT) for Visual Studio 2017 stand-alone download were not yet available when Visual Studio 2017 launched. But on a recent MSDN blog, it was noted that SSDT would be available in the "near future."

"The good news," writes Christian Wade of Microsoft, "is that the installation packages for the preview versions of SQL Server Analysis Services and SQL Server Reporting Services project types are already available as Visual Studio Deployment (VSIX) packages."

How-To Videos

If you're a fast learner and like to watch the Visual Studio screen as some else builds an app, there are some videos on YouTube. Some of the videos appear a little sketchy and are not generally up to Microsoft production standards. Some have gotten mixed reviews. You can search and see for yourself. One that seemed like a pretty good if brief introduction to building a basic sales report is "Microsoft Visual Studio Report Building - Build a Simple Sales Report".

Code Samples

For those who like to actually see procedures used in setting up and generating reports there are sources with code examples.

Visual Studio magazine published an in-depth article, Integrating SQL Server Reporting Services Reports and ASP.NET MVC, by Peter Vogel, system architect and principal in PH&V Information Services. It offers lots of code examples such as:

Initially, it might seem that adding a SQL Server Reporting Services (SSRS) report to your ASP.NET MVC application is a relatively straightforward thing to do. After all, to call an SSRS report, all you have to do is issue a request to the reporting server's URL, incorporating into the URL's querystring the name of the report, any SSRS options and any parameters that control the output of your report. A typical request might use a URL like this:

http://MySite.com/MyReportingServer?/CustomerSales&rs:Format=PDF&customerId=A123

This particular URL is asking for the CustomerSales and passing a customerId of A123 as a parameter to the report. Because the URL includes the rs:Format=PDF option, the user will be given the opportunity to download the report as a file (other rs:Format options include Word and Excel); omitting the rs:Format option causes the report to be displayed as an HTML page.

Peter offers what the editors say is "all the solutions he can think of" and if you are looking for solutions the article is worth a read.

Step-By-Step Guide

Microsoft offers a step-by-step guide to reporting with SQL Server Data Tools within Visual Studio. This guide, created by Maggie Sparkman and Craig Guyer of Microsoft, consists of six lessons that take you through the process with code examples and screen shots:

Lesson 1: Creating a Report Server Project: Shows you how to create a report server project and a report definition (.rdl) file in SQL Server Data Tools within Visual Studio.

Lesson 2: Specifying Connection Information: Learn "to define a data source, which is the connection information the report uses to access data from either a relational database, multidimensional database, or another source."

Lesson 3: Defining a Dataset for the Table Report: Takes you from defining the data source to defining a dataset. "In Reporting Services, data that you use in reports is contained in a dataset. A dataset includes a pointer to a data source and a query to be used by the report, as well as calculated fields and variables."

Lesson 4: Adding a Table to the Report: This is where you get to design the report. "You create a report layout by dragging and dropping data regions, text boxes, images, and other items that you want to include in your report to the design surface."

Lesson 5: Formatting a Report: "Now that you've added a data region and some fields to the Sales Orders report, you can format the date and currency fields and the column headers."

Lesson 6: Adding Grouping and Totals: Here you finish up organizing and summarizing your data.

Once you have gone through these simple lessons, you should be able to pass yourself off as a reports guru. But if not, you might consider attending Visual Studio Live! You could talk to one of our certified gurus and get specific guidance on your Visual Studio reporting projects.

Posted by Lafe Low on 05/26/2017