SQL query and reports

[Drkarnivore]

Late to the party.

If you are sans reporting tool, many databases have a PIVOT / UNPIVOT function for dealing with such problems in SQL.

[Fandango]

You can use a matrix and put the AuditTypeID in the column group field, this would in give you a dynamic number of columns based on the distinct number of AuditTypeIDs you have across the rows. Effectively a Pivot table.

This vid demonstrates it in SSRS so should be applicable in RB3 although he parameterises which columns he wants to use but should give you the idea

The only caveat though is I'm pretty sure you would get a column for every AuditTypeID that appears in your dataset, and that would be applied across all your rows(invoices) regardless of whether each row has a values for each audittypeid

https://youtu.be/vgtVJf21EXU


the other option is to flatten the rows in the SQL statement and concatenate the AuditTypeID into a single string

http://www.sqlmatters.com/Articles/C...%20string.aspx

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

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

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

[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"?

[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]

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]

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

[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]

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

[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