Query to include all dimensions on invoice line

njward

Hello,
I'm looking at creating a query for invoice lines to include any dimensions. I can do this but most lines have more than one dimension so I get a new line for each one. Is there a way of displaying all dimensions on the same line as the main invoice line?

My intention is to publish this using Odata and use PowerPivot to extract the information into Excel for further analysis.

I'm new to this, so apologies if I have not explained this very well.

Thanks

Nick

davmac1

This type of SQL Server query is a dynamic cross tab query and can be handled by a complex stored procedure.
If you have a static number of dimension types you can use a case statement to parse the dimensions into separate columns. You could also do the same thing with a report - one dimension set per dimension type (department, project, and so on) - this way they would all appear on the same line.

njward

Thanks David, It turned out that just needed one of dimension code so I used a filter in the query. Which as a good job as I'm still learning and it will take me a while to work out how to do stored procedures.

Nick