SQL query and reports

[original PM]

Hi Clever People,

can anyone help

We have an audit table within one of our systems which looks like

InvoiceNumber Date EmployeeID AuditTypeID Details
M12345 07/05/2010 0 4 Invoice Printed
M12345 09/03/2010 0 5 Payment Received £116.00
M12345 24/06/2010 0 5 Payment Received £462.00
M12345 10/06/2010 0 11 Letter 1 sent

However I want to write a report which then makes the items appear in a row such as that each invoice number has only 1 row with the various audit items going horizontally

Using report builder 3 to generate the reports

Anyone any ideas?

TIA

[eek]

Hi Clever People,

can anyone help

We have an audit table within one of our systems which looks like

InvoiceNumber Date EmployeeID AuditTypeID Details
M12345 07/05/2010 0 4 Invoice Printed
M12345 09/03/2010 0 5 Payment Received £116.00
M12345 24/06/2010 0 5 Payment Received £462.00
M12345 10/06/2010 0 11 Letter 1 sent

However I want to write a report which then makes the items appear in a row such as that each invoice number has only 1 row with the various audit items going horizontally

Using report builder 3 to generate the reports

Anyone any ideas?

TIA

https://msdn.microsoft.com/en-us/library/dd220467.aspx shows you how to create a stepped report which would give you something like

M12345
07/05/2010 0 4 Invoice Printed
09/03/2010 0 5 Payment Received £116.00
24/06/2010 0 5 Payment Received £462.00
10/06/2010 0 11 Letter 1 sent

I'm not sure how you merge detail rows to appear on a single row though...

[stek]

https://msdn.microsoft.com/en-us/library/dd220467.aspx shows you how to create a stepped report which would give you something like

M12345
07/05/2010 0 4 Invoice Printed
09/03/2010 0 5 Payment Received £116.00
24/06/2010 0 5 Payment Received £462.00
10/06/2010 0 11 Letter 1 sent

I'm not sure how you merge detail rows to appear on a single row though...

Try this if you can do some Unix to the output in a file...

tr '\r\n' ' ' < origfile > newfile

[eek]

Could you sed it in Unix to remove the CR's?

you could just send it to another windows program. I just don't know enough about SSRS to say how to do it within Report Builder itself....

[darrylmg]

That sounds like a transposition.
You can do it in a stored procedure if you can program, or dump a basic report to Excel and transpose there.
Either way, I wouldn't recommend that format, because you don't know how many columns or length of data you're going to need to join together. You might run out of space to properly view it (if you know what I mean).
You might be beter off using a basic grouping function, which would group by invoice id, then have a date sorted sub-row for each of the audit details against that invoice.
This would probably be better because you could then quickly duplicate that report, changing it to group instead by date or account, or employee. Providing you with the differing views you might want to see if you were investigating fraud.

[eek]

That sounds like a transposition.
You can do it in a stored procedure if you can program, or dump a basic report to Excel and transpose there.
Either way, I wouldn't recommend that format, because you don't know how many columns or length of data you're going to need to join together. You might run out of space to properly view it (if you know what I mean).
You might be beter off using a basic grouping function, which would group by invoice id, then have a date sorted sub-row for each of the audit details against that invoice.
This would probably be better because you could then quickly duplicate that report, changing it to group instead by date or account, or employee. Providing you with the differing views you might want to see if you were investigating fraud.

So basically a Step Report....

[darrylmg]

So basically a Step Report....

I don't know what the Microsoft gui tools like to call it, but it sounds like they are one and the same.
Didn't MS Access have something similar for reporting with a "Group By"?

[DanielSQL]

Not sure I've used report builder before. But a simple group by query should give you the result you desire.

Grouping by the invoice number, date and then audit item should give you the result you require. Straight forward group by query then throw that into your report

Might be worth putting an example of how you want the result set to look. Into a spreadsheet and posting a screenshot on here. Should be able to give you some more advice - should you still require it of course.

[LondonManc]

Hi Clever People,

can anyone help

We have an audit table within one of our systems which looks like

InvoiceNumber Date EmployeeID AuditTypeID Details
M12345 07/05/2010 0 4 Invoice Printed
M12345 09/03/2010 0 5 Payment Received £116.00
M12345 24/06/2010 0 5 Payment Received £462.00
M12345 10/06/2010 0 11 Letter 1 sent

However I want to write a report which then makes the items appear in a row such as that each invoice number has only 1 row with the various audit items going horizontally

Using report builder 3 to generate the reports

Anyone any ideas?

TIA

Can you be more specific on how you want your output to look?

The solution may vary - i.e. you could cobble together a little function to concatenate date and details or something similar.

[MarillionFan]

Can you be more specific on how you want your output to look?

The solution may vary - i.e. you could cobble together a little function to concatenate date and details or something similar.

It doesn't matter now, the budget has caused him to go permie.