Skip to main content

Different Items per SSRS Column Group

I had an interesting reporting scenario posed to me that I thought I would share!  (The actual business information has been changed to protect the innocent.)  In this scenario, we own multiple stores that sell a dozen or so products.  We would like a report that shows the type of products that each store has sold on a particular day.  Keep in mind that we could start stocking new products at any time, and we would still like our report to work.

Here is our existing data for the database:

3/4/2010 Sports-R-Us Virginia Softball Glove
12/15/2009 Sports-R-Us Virginia Tennis Racquet
12/15/2009 Sports-R-Us Virginia Dartboard
6/6/2009 Sports-R-Us Virginia Fishing Pole
6/6/2009 Games And More Virginia Canoe
2/21/2010 Games And More Maryland Pool Table
2/21/2010 Games And More Maryland Golf Bag
2/21/2010 Games And More Maryland Canoe
2/21/2010 Games And More Maryland Softball Glove
2/21/2010 Games And More Maryland Football

This scenario really comes into play when you have many different and unknown items in your column grouping, and you need to show them in a compact space.

We can use the following query to simulate this situation:

SELECT '03/04/2010' as TransactionDate, 'Sports-R-Us' as StoreName, 'Virginia' as StateProvince, 'Softball Glove' as Product
UNION ALL SELECT '12/15/2009', 'Sports-R-Us', 'Virginia', 'Tennis Racquet'
UNION ALL SELECT '12/15/2009', 'Sports-R-Us', 'Virginia', 'Dartboard'
UNION ALL SELECT '06/06/2009', 'Sports-R-Us', 'Virginia', 'Fishing Pole'
UNION ALL SELECT '06/06/2009', 'Games And More', 'Virginia', 'Canoe'
UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Pool Table'
UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Golf Bag'
UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Canoe'
UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Softball Glove'
UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Football'

At first glance, this seems like a simple matrix scenario.  If we create a dataset using our query, we can use the fields in a matrix that contains a detail grouping for our rows on TransactionDate, StoreName, and StateProvince with Product as a dynamic column grouping at the end.  Unfortunately, this will result in a lot of blank spaces for the Product columns, as the data per row will only display for the associated group.  If you see a matrix that looks like this, you may have a similar situation!

Original

To get the desired layout, we need to make three changes to our report.  First of all, modify the query to give a ranking per every group, which means we get to use one of my favorite clauses: OVER.  In our scenario, we will partition based on TransactionDate, StoreName, and StateProvince and order by Product.  Here is our new query:

SELECT TransactionDate, StoreName, StateProvince, Product
      , ROW_NUMBER() OVER ( PARTITION BY TransactionDate, StoreName, StateProvince ORDER BY Product ) AS ColumnGroupNumber
FROM
(
      SELECT '03/04/2010' AS TransactionDate, 'Sports-R-Us' AS StoreName, 'Virginia' AS StateProvince, 'Softball Glove' AS Product
      UNION ALL SELECT '12/15/2009', 'Sports-R-Us', 'Virginia', 'Tennis Racquet'
      UNION ALL SELECT '12/15/2009', 'Sports-R-Us', 'Virginia', 'Dartboard'
      UNION ALL SELECT '06/06/2009', 'Sports-R-Us', 'Virginia', 'Fishing Pole'
      UNION ALL SELECT '06/06/2009', 'Games And More', 'Virginia', 'Canoe'
      UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Pool Table'
      UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Golf Bag'
      UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Canoe'
      UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Softball Glove'
      UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Football'
) AS OriginalQuery

The second change that we need to make is in the matrix itself.  On the Product column grouping, we change the field so that it groups on our new "ColumnGroupNumber" field.  Don't change the field in the detail row, as we would still like to show each item.  Once we have made that change, every set of Products will start in the first column.

Our final change is to change the header expression so that it only shows the header on the first column and doesn't repeat fifty times.  We do this by modifying the expression of the textbox to this:

=IIF(Fields!ColumnGroupNumber.Value = 1, "Product", "")

When we put all of the pieces together, we end up with a report that looks pretty darn good!

Final

Comments

Anonymous said…
Thank you.
Catto said…
Hey Now,
Nice Post easy to understand & follow.

Thx 4 the info,
Catto
Hiren said…
Nice Post,

Easy to understand. Great Stuff..
Relieved! said…
Wow!!! You're a savior Jessica I had this exact same problem where the columns would just get placed further and further down the page for every column I had and I was totally stuck on a solution!

Just wondering, is there any other way to possibly do this without changing the sql query to use the over and partitioning? Like is there anything you can do in reporting services alone to achieve the same thing?

Thanks again.
Jessica M. Moss said…
Hi Relieved,

Depending on your need, you may be able to use some of the logic from a great post from Stacia Misner: http://blog.datainspirations.com/2010/05/09/reporting-services-its-a-wrap/

HTH,
Jessica
Anonymous said…
Hi,
We have a requirement like :

Start Time End Time Appointment details
8:00AM 5:00PM ABC
Name Date Name Date
A1 12-5-2012 A2 12-5-2012
A3 12-5-2012 A4 12-5-2012
A5 13-5-2012

can you please suggest. how do we design the rdl.l
Hi, Jessica...

I am doing a large application where I am using column grouping in my reports. Unfortunately, the performance is pitifully slow, and my customer is complaining about it. As an example, if they run a report for a 24 hour period, it takes ~10 minutes to return (~800 "pages" of data). If they run it for a month, it may never return!

The query itself for a 24 hour period returns in ~20 seconds. The balance of the time is pivoting and generating the report.

Do you have any suggestions as what I could do?

Thanks!
Jessica M. Moss said…
Hello Jason,

I recommend that you post your question to the Reporting Services MSDN forum: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/threads. You'll get more feedback, and the answers will help others in the future!

Best,
Jessica

Popular posts from this blog

SSIS Configuration to Configuration to Configuration Schema

I've gotten several requests to put down in writing the configuration schema that I use as the base of my SQL Server Integration Services framework. It contains a set of configurations: an indirect environment variable configuration, which points to an XML configuration file configuration, which points to a SQL Server configuration. I learned this configuration from the Project REAL Reference Implementation . If you're getting started with a BI implementation, I highly recommend that you download it for some great ideas and best practices. Steps to implement: 1) Create an environment variable on your machine with the name of SSIS_CONFIG_FILE and the value of: C:\SSIS\Config\MasterConfigFile.dtsConfig. 2) Create an SSIS configuration file at C:\ SSIS\Config\MasterConfigFile.dtsConfig with the line: <configuration valuetype="String" path="\Package.Connections[CONFIG_SERVER].Properties[ConnectionString]" configuredtype="Property"><configuredv

Execute SQL Task Designer Limit

After migrating a package from DTS to SSIS, I had a problem with an Execute SQL Task. I couldn't change any characters in the SQLStatement property; I couldn't add any new characters; I could delete characters, but not retype them! After googling several variations of "integration services" "read only" and "Execute SQL Task", I deleted about half of the entry in a fit of frustration. Lo and behold, I could type again. Apparently, there is limit on the size or number of characters that can be entered in the SQLStatement property. From my experimentation, I came up with a limit of 32767 characters. The interesting thing is that the restriction only seems to be on the designer. If you set the SourceType to "Variable" and use a variable that contains more than 32767 characters, the task will execute. Also, if you use the "Direct Input" SourceType and modify the package XML to set the SQLStatement longer than 32767 characters,

Reporting Services 2008 Configuration Mistake

To start working with the management side of SQL Server Reporting Services 2008, I decided to set up a report server and report manager. Unfortunately, I made a mistake while setting up my configuration that left me a little perplexed. Here are the steps I took to cause, track down, and solve the issue. Problem: I began by opening the Reporting Services Configuration Manager from the Start Menu. I clicked through each of the menu options and accepted the defaults for any question with a warning symbol, since warning symbol typically designate an action item. After two minutes, all of the warning symbols had disappeared, and I was ready to begin managing my report server. Unfortunately, opening up a browser and trying to open up the report manager resulted in the dreaded " The report server has encountered a configuration error. (rsServerConfigurationError) " message. Sherlock-ing it: I put on my sleuthing hat and went to the log file directory: C:\Program Files\Microsoft