<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Simple way to show multi column data in SSRS (Horizontally or Vertically)

Published on
18,940 Points
11,740 Views
7 Endorsements
Last Modified:
Approved
Hi
When we create a simple report in SSRS data show in one column. (To start using SSRS see this link Creating a Report Server Project ).

In this article we want create a report to show data in multi-column (Horizontally or Vertically).
For example: if we have one table with name [Table1] and one field  [No] and inserted number from 1 to 100  in this  table.

If we create a simple report in SSRS and show data on it
we see this report

P1
but we want see multi-column  report like this

P2
How can we do that?
1. We create a report using BIDS (used in this article ) or other tools  
after that  delete all object on it to see this picture.

P3
 2.  Put a Matrix on report like this (Matrix is in {ToolBox} under {Report Items}).

P4
In bottom we see [RowGroup] under Row Groups and [ColumnGroup] under Column Groups.
3. RightClick on  [RowGroup] and click Group Properties.
P5
4.Click {Fx} to appear Expression window then insert this expression in top textbox

=ceiling(rownumber(nothing) / 3)

P6

Then click two OK.

5.  Do  stage 3 to 4 for  [ColumnGroup]  but only in expression window type this

=ceiling(rownumber(nothing) mod 3)

Then click two OK.

Number 3 in above formulas show count of column in report result.If we need more column can change it in  above formulas.

6.Now RightClick on Data part of Matrix and select {TextBox Properties}

P7
 It will be open a new window. we fill combobox with field [No] which has been defined in Table1.

P8
Click OK.
7.Now click preview to see

P9
8.If we have more than one field in table we can use {InsertGroupRight}

P10  
 
9.If we want show result vertically swap two formulas together.(In stage 4 use this formula
=ceiling(rownumber(nothing) mod 3) and in stage 5 use this =ceiling(rownumber(nothing) / 3) )

Thanks
I hope this article be useful.
7
Comment
Author:rshq
6 Comments
LVL 67

Expert Comment

by:Jim Horn
<style tip>

The full-monitor screen shots are hard to read.  It would be helpful if you could use a graphics editing tool, or even mspaint which ships with every pc, to cut-paste only the parts needed for the article, as images.
0
LVL 4

Author Comment

by:rshq
Hi
 Thank you for the tips.
0
LVL 67

Expert Comment

by:Jim Horn
Nicely done with the images.  Voted Yes.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Expert Comment

by:JIM FUNK
The reversing of the 2 formulas does not work. It gives 3 rows of data and several columns. How would I force it to give me 8 columns where the data flows down instead of across?
0

Expert Comment

by:rehansheik
Thank you. Works great.
0
LVL 8

Expert Comment

by:Rog D
Was curious as this works well in the Preview mode, but no so good in the rendering in SSRS.  

I mean the columns work, but not the sorting order.  Any help with this?

I sorted the data coming from the dataset, but that order is great in the preview screen, but
is now handled the same on the SSRS rendering.

Rog
0

Featured Post

Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Join & Write a Comment

Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month