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.
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