I love lists – to-do lists, grocery lists, running playlists – any kind of list. So, given the opportunity to use a list in SQL Server Reporting Services, I’ll do so.
It doesn’t work quite like a shopping list – it won’t remind me that I need to pick up milk from the store, or that I have a Jaycee meeting tomorrow night. Reporting Services lists are designed to group like data that comes from different sources, is displayed in different elements, or displays the same data in different ways.
I’m going to show you two examples of ways I’ve used lists.
Grouping Item Data
I had a request from the Marketing department last year. Vendors were submitting product details and images to a website, in hopes their products would be featured in our catalog. Marketing wanted a report that would list the product details and the images. They wanted it formatted very specifically. I was able to create exactly what they wanted, using a List.
First, my product dataset:
SELECT PROD.ProductNumber, PROD.Name, ISNULL(PROD.Color, 'N/A') AS Color, ISNULL(PROD.Size, 'N/A') AS Size, ISNULL(PROD.[Weight], 0) AS Weight, PP.LargePhoto FROM Production.Product PROD INNER JOIN Production.ProductProductPhoto PPP ON PPP.ProductID = PROD.ProductID INNER JOIN Production.ProductPhoto PP ON PP.ProductPhotoID = PPP.ProductPhotoID
Let’s see how this looks when using a standard table or tablix.
Here, each item is on its own line. The details are there, but the image isn’t. What if I wanted more control of my layout? I would like to change this so the fields are laid out differently and I can view the product image. I drag some textboxes onto the Design tab, fill out my fields, and come up with this:
When the report runs, on the Preview tab, you’ll notice that only one product appears on the last page.
What is happening?
The report doesn’t know that I want these textboxes to appear for each product. An easy solution is to add a List control and move the textboxes and image box onto it.
Here, I have added the list. I made the background color blue so you can see how the fields are arranged on it.
Now, when the report is run, each item’s details are grouped together.
If this is cleaned up to include a report title, have the blue background removed, and have a solid black border around each item, it looks much sharper.
The beauty of using a list this way is that I was able to combine several elements – textboxes, fields from the data set, and an image. The fields can be laid out in any order on the list, allowing great flexibility and customization.
Creating an Invoice
In an order entry and invoicing system I worked with, customer invoices were generated and printed at the company’s corporate office. If a salesperson needed a copy of the invoice, they could find and print it, but it was a cumbersome process. I was asked to create a simple-to-use report that mimicked the invoice from the system.
SELECT SOH.SalesOrderID, SOH.OrderDate, SOH.ShipDate, SOH.SalesPersonID, CON.FirstName, CON.LastName, CUST.FirstName + ' ' + CUST.LastName AS CustName, AD.AddressLine1, AD.AddressLine2, AD.City, ST.StateProvinceCode, AD.PostalCode, SOD.ProductID, PROD.Name, SOD.OrderQty, SOD.UnitPrice FROM Sales.SalesOrderHeader SOH INNER JOIN Sales.SalesPerson SP ON SP.SalesPersonID = SOH.SalesPersonID INNER JOIN Person.Contact CON ON CON.ContactID = SP.SalesPersonID INNER JOIN Person.[Address] AD ON AD.AddressID = SOH.ShipToAddressID INNER JOIN Person.StateProvince ST ON ST.StateProvinceID = AD.StateProvinceID INNER JOIN Sales.SalesOrderDetail SOD ON SOD.SalesOrderID = SOH.SalesOrderID INNER JOIN Production.Product PROD ON PROD.ProductID = SOD.ProductID INNER JOIN Person.Contact CUST ON CUST.ContactID = SOH.ContactID WHERE SOH.OrderDate >= '1/1/2003' AND SOH.OrderDate <= '12/31/2003' AND SOH.SalesOrderID = @SOID
In this example, I pulled in several elements. In the top left corner, I have an embedded image of the company logo. In the top right, I use textboxes to display the static company address. Underneath the logo, I use textboxes and fields from the dataset to create the customer’s address. I then added a table to display the products that were ordered, and sum the value of the items.
Here is the Design tab:
This is how the report appears when I run it for one order:
I can export this to PDF, and you will see that the formatting and information is maintained. It’s now ready to be printed or emailed to the customer.
Organize Your Reports Like You Organize Your Life
Lists are an easy way to take your reports from “meh” to “wow!” I suggest that you give them a try. Put different elements on a list, experiment with arranging them, group them, and have fun!