question

markgoldin-3750 avatar image
0 Votes"
markgoldin-3750 asked Joyzhao-MSFT commented

SSRS report - split table into two columns

I need to split a table into 4 columns while the data has two columns. There result might look like this:
127656-image.png

The data is something like this:
127721-image.png

I have the following table configuration:
Row group expression:
=ceiling(rownumber(nothing) / 2)
Column group expression:
=rownumber(nothing) mod 2

Table filter:
127694-image.png

For some reason as th first image show the result has two extra empty rows.
No matter what I do I cannot get rid of them. However, teh same technique would profuce the correct results for the same number of original rows.
The problem only happens where the source table woud have two rows after filtering.

Thanks


sql-server-reporting-services
image.png (8.9 KiB)
image.png (20.3 KiB)
image.png (7.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

User a "newsletter style" pagination instead to get the table into 2 columns, see Pagination in reports (Report Builder and SSRS) => Newsletter-Style Columns


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered markgoldin-3750 commented

Hi @markgoldin-3750 ,
I did the test locally.
First of all, please filter the dataset, delete and select the required two sets of data. As shown below:
127822-01.jpg
127841-02.jpg
Then I create a Matrix in the report, as shown in the following figure:
127750-03.jpg

Use your previous expressions in row groups and column groups:

127842-04.jpg
127844-05.jpg
Row group expression:

 =ceiling(rownumber(nothing) / 2)

Column group expression:

 =rownumber(nothing) mod 2

Preview:

127817-06.jpg

Best Regards,
Joy


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


01.jpg (27.6 KiB)
02.jpg (7.4 KiB)
03.jpg (9.1 KiB)
04.jpg (8.8 KiB)
05.jpg (39.3 KiB)
06.jpg (19.0 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Regarding how to create the matrix:

127796-15.gif


0 Votes 0 ·
15.gif (598.0 KiB)

I followed your steps of creating the matrix. I get the same (bad) results.

0 Votes 0 ·

Like I said, it only works when rows to show come one after another in the dataset.

0 Votes 0 ·
markgoldin-3750 avatar image
0 Votes"
markgoldin-3750 answered Joyzhao-MSFT commented

I have two datasets. One is working fine:
127900-image.png

The other is not working:
127839-image.png

In the first one I am showing faults with fault_cnt <> 0.
In the second I am showing Category with category_cnt <> 0.

The difference in two datasets is that in the first one records to show naturally come together, while in the second they are not.
I tried sorting as an addition to filtering, but it was even worse.


image.png (292.5 KiB)
image.png (228.4 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Have you considered sorting when creating the dataset? When you use the Filter in the dataset properties, select only the data you need.

0 Votes 0 ·