Reporting Services - Matrix Control

Posted on March 6, 2008 by Chris at 10:37 am

When writing reports for my various work projects, I’ve always tried to work as fast as possible, and tend to ignore the things that don’t make sense strait off the bat. These things tend to take a lot more time to learn and figure out. I never really have a lot of time, so I find another way that is faster at the time.

One of the things that I never got to play with was the Matrix control in the Microsoft reporting services. It seemed logical enough, but every time I looked at using it, the damed thing never worked, so I went and used the List control instead. This time, I was writing a report that needed to have a different number of columns depending on the number weeks in the month being reported on. So, naturally, I thought that the Matrix control would work well.

Here’s an example of what the data that I started with looks like. I was able to pull this out of my tables with a (relatively) simple sql statement.

Next I threw the Matrix control on a new report, and using the report printer that I prepared earlier in Microsoft SQL Server Reporting Sevices, I got a blank report printing out to pdf (yay!).

After a little trial and error, which I had time for this time (accounts REALLY wants this report), I finally found the Groups tab on the Properties page of the Matrix control. This was the key to getting it to work correctly. After that, it was clear sailing.

As you can see, I’ve added two Row Groups, and 1 Column group. The row groups determine what is shown in each row, while the column groups determine how what is shown in the columns. I know, it sounds like I’m doing the dummies guide, but really I do remember thinking that exact line while actually doing the work, followed by “ooh, so that’s how it works”.. guess I really am a dummy.

Anyhoo, this is what the relevant part of the group edit screen looks like. Incedently, the row groups were on section name, then staff name.

Then I added the field values into the grid items that were available, and after a little trial and error, came out with this tiny, little thing.

Although it’s tiny, it has big results.. take a look.. ooh pretty…

Filed under: Application Development Tags: , , ,

Microsoft SQL Server Reporting Sevices

Posted on December 11, 2007 by Chris at 8:45 am

I find myself doing alot of web based reporting for the current project that I am doing at the moment, which is no surprise, as the application that I am writing is basically a data-mining tool. One thing that has saved me alot of time is the reporting tools that come packaged with Visual Studio 8.

I like the way that the reporting services allows you to simply add a control to your web page, and suddenly, you can view the report on your web page. But what if you don’t want to show the form, or have something that is easier to print out, the ReportViewer control is just a little too tricky to use for most of my users. Fortunately, there is a way to print a PDF or Excel spreadsheet directly, without having to have the control placed on your page.

This method used the ReportViewer.LocalReport.Render() method, which simply draws the report, and returns you the bytes for whichever format that you choose. Here’s an example of how to draw to PDF:

//Includes
using System.Data;
using System.Collections.Generic;
using Microsoft.Reporting.WebForms;

public void GenerateReport(
   String reportPath,
   List paramaters,
   String dataSourceName,
   DataTable reportData,
   String fileName)
{
   // Definitions-a-plenty
   string mimeType, encoding, extension;
   Warning[] warnings;
   string[] streamids;
   byte[] bytes;

   ReportViewer reportViewer = new ReportViewer();

   // Add in the report data
   reportViewer.LocalReport.ReportPath = reportPath;
   reportViewer.LocalReport.SetParameters(paramaters);
   reportViewer.LocalReport.DataSources.Add(new ReportDataSource(dataSourceName, reportData));
   reportViewer.LocalReport.Refresh();

   // Render the report to PDF
   bytes = reportViewer.LocalReport.Render(
      "PDF",
      null,
      out mimeType,
      out encoding,
      out extension,
      out streamids,
      out warnings);

   // Finally, write the bytes directly to the output
   // stream
   //
   // This should cause your browser to recognise a PDF
   // coming out, rather than a web page
   //
   // NOTE: Will not work correctly if you have already
   //       written to the output stream, as it does
   //       things with page headers
   Response.AddHeader("Content-Disposition", "attachment; filename="+fileName+extension);
   Response.ContentType = mimeType;
   Response.OutputStream.Write(bytes, 0, bytes.Length);
   Response.End();
}
Filed under: Application Development Tags: , , ,