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.
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!
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:
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.
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:
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.
Open up the properties of the nested tablix and switch to the Filters page.
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.
Change the Operator as shown in the screenshot, > instead of <=.
Let’s render that report!
Looks good doesn’t it? We’ve got 606 records and the first item in the second column is 304!
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.