Creating Multi-Column Reports: The Top-Down Version [SSRS]

About half a year ago I wrote an article in which I explained how you can get newsletter-style reports implemented which work in every renderer (except just the image ones), I called that article Creating Multiple-Column Reports.  And last week that article received an interesting comment, a comment which requires a bit more than just a couple of lines in reply.  So I decided to write an article instead!

I’ll be using the same technique as in my previous article but the output (and part of the technique) will be different.  So I will not go into all detail on the parts that overlap.  If something in this article isn’t clear, please have a read through my previous article first.

The resulting RDL can be downloaded from my Skydrive.

The Scenario

Just like in my previous article the data should get rendered over multiple columns, in the example I’ll implement two columns but the technique can be used easily for more columns as well.  However, the difference is this: instead of spreading the data horizontally, from left to right, it should spread vertically, from top to bottom!

The Query

Let’s start with a really simple query:

select DP.ProductKey, DP.EnglishProductName
    , COUNT(*) over () RecordCount
from dbo.DimProduct DP

When executed against the AdventureWorksDW2012 database it gives something like this:

Output of my simple query

What you see is a list of products.  The last column, RecordCount, has been added by using the OVER clause and represents, guess what…, a record count.  It will become clear later on why this is needed.  In case you’re not familiar with the trick with the OVER clause, have a look at this article: Aggregating Data With The OVER Clause.

The Tablix

Start by adding an empty Table to the report and remove one of the columns.  Also remove the header line and the default (Details) Row Group:

Empty table without row group

Connect the table to the dataset by setting the DataSetName property.

Nest another Table inside the left cell of the main table.  This is explained in detail in my other article, if needed.

Nesting a tablix inside another tablix

Open up the properties of the nested tablix and switch to the Filters page.

Filtering the tablix to show only the first 50% of records

The expression in the Value box is this:

=Fields!RecordCount.Value / 2

As you can see, we’re using the count of records to decide which records should be shown.  As we want half of them, we’re dividing by two.  This also assumes that the dataset contains a field with an increasing number.  Out of convenience, I used the ProductKey from the DimProduct table.

That’s the left column completed.  Now select the nested tablix and copy/paste it into the remaining empty cell on the right of the main tablix.  Open up its properties and switch to the Filters page.

Filtering the remaining 50%

Change the Operator as shown in the screenshot, > instead of <=.

Let’s render that report!

Data rendered in two columns, top down!

Looks good doesn’t it?  We’ve got 606 records and the first item in the second column is 304!

Conclusion

In this spin-off on my previous article on rendering data over multiple columns I have demonstrated a method which can be used to render the data top-down and spread over two columns.

Have fun!

Valentino.

Share

Tags: , , ,

  1. PortletPaul’s avatar

    extended the approach for further columns using modulus ?

    select
    y
    , max(case when x = 1 then blurb else null end) as c1
    , max(case when x = 2 then blurb else null end) as c2
    , max(case when x = 0 then blurb else null end) as c3
    from (
    select
    blurb
    , row_number() over (order by ID) as rowref — order by whatever is relevant
    from SomeTable
    ) as derived
    cross apply (
    select rowref % 3, (rowref-1) / 3
    ) as ca1 (x, y)
    group by
    y

    Reply

    1. Valentino Vranken’s avatar

      Hey there Paul,

      Interesting query! :) But (unless I’m missing something here) the result is not exactly the same as what I’ve described here (data doesn’t go top-down but left-right). In fact, it’s really similar to what I’ve done in my other article (http://blog.hoegaerden.be/2012/11/01/creating-multiple-column-reports), except for the category groups.

      cya @EE,
      V.

      Reply

  2. PortletPaul’s avatar

    :) OK, I’ll look at the other one – nice blog btw

    Reply

    1. Valentino Vranken’s avatar

      Thanks :)

      Reply

  3. Steve’s avatar

    Ah, but what if you don’t have the sequential integer? You can’t use RowNumber in a calculated field or in a filter expression.

    Reply

    1. Valentino Vranken’s avatar

      That’s right, it’s indeed a requirement that you can edit the source query to add that increasing integer…

      Reply

  4. Chris’s avatar

    Hi, Valentino. What if I need three columns?

    Reply

    1. Valentino Vranken’s avatar

      Hi Chris,

      I haven’t tested this but I think it’s just a matter of changing the expression to divide by three: =Fields!RecordCount.Value / 3

      Besides that your main tablix will of course need three columns too.

      Good luck getting that to work!

      Valentino.

      Reply

  5. Mizanur Rahman’s avatar

    please, for 3 column wchich operator can I choose ,please hurry up

    Reply

  6. Nathan R’s avatar

    I was excited to find this blog as this is exactly what I was trying to accomplish. I have a set of rows and I want it to fill down the left before it fills down the right.
    Unfortunately I can’t get it to work. I’ve tried setting the filter expression value to be both a row_count and the varchar ID value from my application’s database. Neither have worked. In reviewing the blog I noticed that the Value chosen for the expression was listed as an Integer once and as a text the next time. Since they both appear to be the same value derived from the database [ProductKey] and the datatype field is auto populated I’m not sure how they could be different.

    Reply

  7. Henry Kenuam’s avatar

    Very nice. Thanks for sharing!

    Reply

© 2008-2017 BI: Beer Intelligence? All Rights Reserved