The Power of Reporting Services – Lists

SQL Server Reporting Services 2008 R2, BIDS 2008, AdventureWorks 2008

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.

Design tab:

Preview:

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.

Dataset:

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!

About these ads

43 Comments

Filed under SSRS

43 responses to “The Power of Reporting Services – Lists

  1. Pingback: Weekly Link Post 171 « Rhonda Tipton's WebLog

  2. Hugobarb

    So your a runner and you can program in SSRS…can’t wait to check out the running section of you blog. Thank you for the detailed explantaion of the List Control and including screen prints. I amtrying to use it for a report and avoid having to use sub reports but ma not sure if is possible to do so as I have multiple data sources. When you created the mock invoice did you have one datasource with multiple rows that you then placed into a List control? Working on creating a report that combines 4 reports into one as sort of a customer dashboard. I have a series of records that will apear at the top of the page customer related with a customer with customer number. Then I have three seperate datasources that represent 3 unique and totally different items related to the customer but have multiple records. You can think of it as 3 sperate invoice detail sections but totally unrelated data so it would be difficult to combine data sets. I tried having multiple list controls but am uncertain where and how to join them to the top parent or if this is possible. And adding a table inside the top list control below the header information does not seem to work? Any ideas would be great. Regardless great blog!

    • Thanks! What you could do is: create three separate tables, one for each datasource. Put these all on the same list, all of them grouped by customer. Have the list group by customer as well. You can nest lists as well.

      • Hugobarb

        Thank you I am going to try that appraoch bc. the sub report option is running very slow.

      • matt

        I’m not so sure that you can so easily nest the lists in the way described above. Yes you can nest lists, but you cannot have a “child” list refer to a different dataset than what the “parent” list refers to – or at lease I haven’t figured out a way to do it, if you can please reply with how it was done.
        If you are using SSRS 2008 R2, you can use the lookup function(s) to lookup data from a separate dataset

      • Matt, thanks for the information. I agree – I haven’t found a way to nest list A, attached to dataset A, in list B, attached to dataset B. In my opinion, the ability to nest lists is for grouping purposes.

  3. Hugobarb

    How do you link one list ot another? Very noob question that I feel dumb asking but never used lists before.

  4. Tahir

    I want to play with the dataset before binding it to the report. I mean to first filter the dataset through C# or VB code then bind it. is it possible?

    Thanks

    • You should always use an already-filtered dataset. One of my rules when building reports is to have the dataset return only what I need to show in the report – nothing more and nothing less. As for filtering with C# or VB, that’s a good question and I’d have to look into it. You may want to post a forum question on SQLServerCentral.com.

  5. Paulus Tandang

    Jes,
    Can I have your emal address. I have SSRS 2005 report that need to border solid line around group, it is one group but need to border on 3 sub group. If I email you the screen shot will explain better.
    thanks.

  6. Paulus Tandang

    Hi Jes, thanks I have posted in the link above.

  7. Rodney

    I have three tables that each have their own dataset. I want to put them in the same list becasue they need to be grouped by workplace and date. When I drag them into the list I notice that RS uses only the first dataset. How can I resolve this?

    • Think of a list as one detail row in a table. It can only show one row of data from one data set at a time. You’ll need to think of a way to combine the three datasets into one. Could you re-write the T-SQL? Can they be joined with a UNION or UNION ALL? Can you put the queries in a stored procedure and use that to return the results?

  8. Jane

    Hi Jes,

    Recently I ran into a problem using the list control. Using your AW cycles example above, I need to have a list that can also print out the “total” page. Instead of just the invoice per customer, it will print out the “All customer” invoice in the same format.

  9. dom

    I have same requirement, but what I have to do is for each order number report should be generated separately which could be easily exported to PDF..Order Number available in drop down list as parameter. If one parameter selected one report but when 5 Order Number selected then 5 different report for each order number, following the same format that is for one Order Number, and then export to pDF

  10. Todd Sims

    Jes – Is there any way you could post a copy of this RDL? I am new to SRSS, though not new to development or SQL. I’m having some issues figuring out how to have free form data floating in a group header vs. a column format (kind of need a list embedded in a list, which isn’t allowed). I think examining this RDL might help. Much Thanks.

    • Hi Todd

      You asked if I could put my list .rdl on my blog. WordPress won’t allow that by default, and I don’t have the time tonight to mess around with multiple uploads, so I hope you don’t mind that I’m sending them directly to you. I’m sending both .rdls I used in that blog. These come with no guarantee that they will work, but I hope they do.

      Please let me know if you have any questions!

      Jes Borland

  11. hi jes, how do you created the border around the page?

  12. ClarkInte

    Good post and I am glad I came across this. I need to do exact same as Invoice report.But I a stuck on two things.

    1) How do I repeat the entire report for each order number.
    2) Some of the address fields are nulls. How do I remove the white space consumed on the report body when one of the adress field is null or empty

    Appreciate your help.

    • 1. If you have multiple orders in the dataset, a separate “invoice” will be generated for each order. The list control makes one “row” for item in the database.
      2. That’s going to involve some T-SQL or expression trickery. There is no way to automatically remove that.

  13. Waqar

    I think the same to be done by rectangle?

  14. Gary Thompson

    once you’ve added a tablix in a list (list contains group, but no details), how can you set page breaks between objects (Tablix, rectangles, etc.)?

  15. After looking into a few of the blog posts on your web page, I
    really like your way of blogging. I bookmarked it to my bookmark website list and will be checking back soon.
    Please visit my web site too and let me know how you feel.

  16. Hi colleagues, pleasant post and fastidious urging commented here,
    I am truly enjoying by these.

  17. Its such as you learn my mind! You appear to
    know so much approximately this, like you wrote the guide in it or something.
    I feel that you simply could do with a few % to pressure the message home a bit,
    however other than that, that is excellent blog. A great read.

    I’ll definitely be back.

  18. Howdy this is kind of of off topic but I was wanting to know if blogs use WYSIWYG editors or if you
    have to manually code with HTML. I’m starting a blog soon but have no coding experience so I wanted to get guidance from someone with experience. Any help would be enormously appreciated!

  19. Post writing is also a fun, if you know then you can write if not it is complicated to write.

  20. My family all the time say that I am killing my time here at web,
    however I know I am getting experience all the time by reading such pleasant articles or
    reviews.

  21. Great beat ! I would like to apprentice while yyou amend
    your website, how can i subscribe for a blog web site? The account helped me a acceptable deal.
    I had been a little bit acquainted of this your broadcast offered
    bright clar idea

  22. I am really impressed with your writing skills as well as wwith the layout on your weblog.

    Is this a paid theme or did you modfy it yourself? Anyway keep
    up the nice quality writing, it is rare to seee a great blog ike this one nowadays.

  23. Everything iss very open with a clear description of the issues.

    It was really informative. Your site iis very helpful.
    Thanks for sharing!

  24. Hey there! I know this is kinda off tolic but I was wndering if
    you knew whewre I could get a captcha plugin for my comment
    form? I’musing thee same blog platform as yours andd
    I’m having trouble finding one? Thanks a lot!

  25. Hey! I know thnis is somewhat off topic but I wass wondering iif you knew where I could locate a captcha pljgin
    for my comment form? I’m using tthe same blog platform as
    youres and I’m havng difficulty finding one? Thabks a lot!

  26. This log was… how do you say it? Relevant!! Finally I’ve found
    sokething that helped me. Many thanks!

  27. My prograammer iss trying tto convince me too move to
    .net from PHP. Ihave always disliked the idea because of the costs.

    Butt he’s tryiong nne the less. I’ve been using Movable-type on a number of
    websites forr anout a year and am worried about swtching to another platform.I have heard great things about blogengine.net.
    Is theree a wway I cann transfer all my wordpress conntent into it?

    Any help would be rrally appreciated!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s