0

I'm getting more than 200 records (for example, however, the number of records are dynamic) in a single table column in SSRS. I would like to do a line break after 25 records.

ReportTable result (Just an example):

            Value
            -----
            jklsa
            dfv
            b
            grt
            trj
            h
            muik
            rg
            kuu
            wd
            gb
            nm
            wef

Final Rrport table desired output:

                     Values
                -----------------
                 jklsa |muik | wef
                 dfv   |rg   |
                 b     |kuu  |
                 grt   |wd   |
                 trj   |gb   |
                 h     |nm   |

That is, after introducing line break, the records has to be moved to the "right side".

So, in this case instead of single column with 200 records, It should be 8 columns [column name can be same] each having 25 records.

This is what I have tried : 

    =IIF(CountRows(Fields!Request_Number.Value) > 25,vbCrLf,Fields!Request_Number.Value)

    and 

    =IIF(CountRows(Fields!Request_Number.Value) > 25,Fields!Request_Number.Value, "") & vbCrLf

However, it throws error:

"The value of expression has a scope parameter that is not valid for an aggregate function"

Hope I have explained this correctly. What am I doing wrong? how to get the correct result? Thanks

AskMe
  • 2,495
  • 8
  • 49
  • 102

1 Answers1

1
CountRows(Fields!Request_Number.Value)

wont give you correct count for your Rows on SSRS. Instead use

RowNumber("DataSet1")

Note:"DataSet1" is your Dataset name.

By the way what exactly you wish to achieve. As you will be using Table or matrix do you wish to add pageBreak because linebreak works with Textbox and not with entire table.

Link for Ref:

https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms159225(v=sql.105)

AnkUser
  • 5,421
  • 2
  • 9
  • 25
  • Thanks. I have updated my question and I would like a line break. As number if records are dynamic, 1st, after 25 records, the column has to shift to right and then when one report page report has 10 columns, it should go to next Page. In this case after 10 column (i.e. 250 records), the records should move to next page. – AskMe Jun 03 '19 at 08:50
  • I got what you wish to achieve as end Result. I don't think this is possible . Reason SSRS works on per Row basis and your rows returned are dynamic as well. – AnkUser Jun 03 '19 at 09:38