csvreporting-servicesexport-to-csvreportbuilder

How to create a csv file using SSRS with 2 distinct header rows and 2 rows of data for each record (template format in question)


I'm attempting to build a self-service report to produce a csv in a template format which will be uploaded to a finance system.

The template dictates that each record, in this case learners, should appear across 2 rows of data. The values in these rows include some matching and some differing values.

In addition the csv requires 2 distinct header rows, which align with the data rows.

There needs to be a blank rows in-between each learner (reference).

See below the first 6 columns required in the csv template with 3 example references.

Note The orphaned header above the table is also required as a header row (the table format in this insert doesn't allow 2 header rows).

| Type| Reference| Vendor No| Amount| Description| Borough|

Type Reference VAT code Amount Description Cost centre
AP 758375000000 D000000016 128 758375-DLSF Payment WCC
GL 758375000000 V0 128 758375-DLSF Payment W22003
AP 563753000000 D000000016 20 563753-DLSF Payment WCC
GL 563753000000 V0 20 563753-DLSF Payment W22003
AP 421765000000 D000000016 56 421765-DLSF Payment WCC
GL 421765000000 V0 56 421765-DLSF Payment W22003

My first attempt was inserting 2 separate Tables, one immediately after the other, with the 'AP' values in the first, and the 'GL' values in the second. However, the resulting output grouped all learner's AP rows together, and then the GL rows.

Inserting a List inside of a Rectangle, with all of the fields inserted across 4 rows, got me much closer to the template format in an Excel export only. However, the spreadsheet output included hidden columns in-between the populated columns, and many of cells were merged. Therefore if I attempted to save the file as a csv the format was completely lost.

Almost all reports I've created previously in SSRS have used tables for data grids. Therefore any advice on the best approach in this instance would be hugely welcomed.


Solution

  • Assuming you are using SQL SERVER for your data, I have come up with a "Hacky" way of doing this.

    The idea is to select the data you need, sorting and assigning row numbers to each row. You then manipulate the row number and union the results with blank data and then finally reordering it.

    After this step, you further union it with column header text.

    See the SQL Fiddle link below for the full code.. it might not be 100%, but it's close enough for you to do some work to get it to where you want it..

    https://sqlfiddle.com/sql-server/online-compiler?id=c2c1deb8-a3b8-4aef-aebb-f48740debc52