I need to create a report, but I'm unsure how to get the layout correct.
The way the report should look is Here
The data is currently formatted like so:
Customer No | Sale Price | Cost Price | Margin | Date
Customer A | 200 | 100 | 100 | 1/1/14
Is it possible to design a report with this layout? I suspect so, but I haven't encountered this issue yet.
Any hints and tips to get me on my way?
Since what you want is actually a cross-tab, it is easier if your dataset is structured like this:
Customer No Price Type Date Amount ----------- ---------- ------- ------ Customer A Sale 1/1/14 200 Customer A Cost 1/1/14 100 Customer A Margin 1/1/14 100 ...
To achieve this, simply use UNION ALL in the SQL statement of your dataset, like so:
SELECT [Customer No], 'Sale' AS [Price Type], [Date], [Sale Price] FROM MyTable UNION ALL SELECT [Customer No], 'Cost' AS [Price Type], [Date], [Cost Price] FROM MyTable UNION ALL SELECT [Customer No], 'Margin' AS [Price Type], [Date], [Margin] FROM MyTable
With a dataset like this, it is straightforward to get the report layout you want, for example using the Tablix Wizard.
I managed to get it working. It actually wasn't too difficult, to my surprise. This probably isn't the best way to do things, but it worked well for me!
SELECT sa.[Document No_] ,[sa.Customer No_] ,DATEPART(m, sa.[Posting Date]) AS MonthName ,sa.Quantity ,sa.[Amount (LCY)] ,sa.[Cost (LCY)] ,sa.[Profit (LCY)] ,c.[Salesperson Code] ,c.NAME FROM [Sales Analysis] AS sa INNER JOIN [Customer] AS c ON c.[No_] = sa.[customer no_] WHERE [Posting Date] BETWEEN '2014-01-01' AND '2014-05-31' AND [Customer No_] IS NOT NULL AND [Customer No_] <> ''
Results Thanks for the help!