How to get sections of SSRS report data on separate lines in the output file

Question

I have a stored procedure in SSMS that looks something like this:

SELECT
    1 AS [Transaction Type 1]
    ,SOMEFIELD AS [Some Field]
    ,ANOTHERFIELD AS [Another Field]
    ,...

    ,2 AS [Transaction Type 2]
    ,SOMEOTHERFIELD AS [Some Other Field]
    ,YETANOTHERFIELD AS [Yet Another Field]
    ,...

    ,4 AS [Transaction Type 4]
    ,MOREFIELDS AS [More FieldS]
    ,EVENMOREFIELDS AS [Even More Fields]
FROM SomeTable
JOINS
WHERE
ORDER BY

The customer is requiring that, for each record in the output, each Transaction Type section (1,2,4) is on its own line in the file that SSRS generates when the report is run. They're processing the data from the output file that SSRS generates, so they're not concerned with how the report looks in the browser when the report is run, but the file should look something like this:

1|10354|99999-9999|O|SOMENAME|11161947|M|999-99-9999|SOMEADDRESS|SOMEADDRESS|SOMECITY|ST|99999|999-999-9999|CLI|99999999|99999999|O|S|
2|10354|99999-9999|O||||||||MSOMENAME|U61434|1790702207|465|||PODSTTRY|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
4|117245592|SOMECOMPANY|STDSNP|117245592|1|Self
1|10723|99999-9999|O|SOMENAME|08081934|M|999-99-9999|SOMEADDRESS||SOMECITY|ST|99999|999-999-9999|ER |99999999|99999999|O|M|
2|10723|99999-9999|O||||||||SOMENAME||1356982441|3119||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
4|4VH2R42VX92|SOMECOMPANY||4VH2R42VX92|1|Self
1|12318|99999-9999||SOMENAME|07201973|F|999-99-9999|SOMEADDRESS||SOMECITY|ST|99999|999-999-9999|CLI|99999999|99999999|O|M|
2|12318|99999-9999|||||||||SOMENAME||1194703439|1639||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
4|STLW01260703|SOMECOMPANY|36650|STLW01260703|1|Self

As I said above, in the file output each 1, 2, & 4 Type section is on its own line. However, I had to manually do the above file output sample for the purposes of this question, because the actual file output looks like this:

1|12318|99999-9999||SOMENAME|99999999|F|999-99-9999|SOMEADDRESS||SOMECITY|ST|99999|999-999-9999|CLI|99999999|99999999|O|M|2|12318|99999-9999|||||||||SOMENAME||99999999|1639||G68462||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||4|STLW01260703|SOMECOMPANY|36650|STLW01260703|1|Self
1|12328|99999-9999|O|SOMENAME|99999999|M|999-99-9999|SOMEADDRESS||SOMECITY|ST|99999|999-999-9999|CLI|99999999|99999999|O|S|2|12328|99999-9999|O||||||||SOMENAME||99999999|2447||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||4|CPWW01093606|SOMECOMPANY|20041|CPWW01093606|1|Self
1|12508|99999-9999|O|SOMENAME|99999999|F|999-99-9999|SOMEADDRESS||SOMECITY|ST|99999|999-999-99991|CLI|99999999|99999999|O|W|2|12508|99999-9999|O||||||||SOMENAME||99999999|2707||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||4|1XM0TM6DT02|SOMECOMPANY||1XM0TM6DT02|1|Self
1|13167|99999-9999||SOMENAME|99999999|F|999-99-9999|SOMEADDRESS||SOMECITY|ST|99999|999-999-9999|CLI|99999999|99999999|O|M|2|13167|99999-9999|||||||||SOMENAMEA||99999999|2159||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||4|9TP1NQ5MR03|SOMECOMPANY||9TP1NQ5MR03|1|Self
1|13673|99999-9999|O|SOMENAME|99999999|F|999-99-9999|SOMEADDRESS||SOMECITY|ST|99999|999-999-9999|CLI|99999999|99999999|O|M|2|13673|99999-9999|O||||||||SOMENAME||99999999|2707||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||4|DSTW00629242|SOMECOMPANY|XA061|DSTW00629242|1|Spouse

Each record generated by the stored procedure is on its own line. Sections 2 & 4 are on the same line as its correcsponding section 1 for each respective record.

The confusing part is, in VS for Business, where I'm working on the RDL, the layout looks like this:

And when the report is run in SSRS, the output looks like this:

You'll notice that each Type section 1, 2, & 4 are on its own line for a given record, as intended.

I just can't figure out how to get it to save like that in the pipe-delimited text file. Frankly, we're stumped and are wondering if this is even possible in an SSRS RDL, save for creating a batch file after the file is generated that would put each section on its own line.

Answer 1

You can achieve this by running some script which manipulates the output file generated by the procedure (which produces the result in a single line for a transaction type section group).

try the following:

declare @sql varchar(1000)
exec master..xp_cmdshell 'bcp "exec dbname.schemaname.procedurename" queryout "filepath" -T -c -r\n -S servername\instanacename'
set @sql = '%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\powershell.exe -c "[System.IO.File]::ReadAllText(''filepath_which_is_generated_by_procedure'') -replace ''\|([24]\|)'',\"`r`n`$1\"  | Set-Content ''finaloutputfilepath'' 
exec xp_cmdshell @sql

I used PowerShell to accomplish this.

Output: