<

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

Published on
17,452 Points
10,252 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
5 Comments
 
LVL 66

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 66

Expert Comment

by:Jim Horn
Nicely done with the images.  Voted Yes.
0
 

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Join & Write a Comment

Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month