Multiple Columns in report Matrix?!

Hi there,

I'm trying to use a matrix to create report with the following format.

Location Carrier Period Total Total YTD

C1 C2 C3

Bangkok 1 2 3 6 5

CLT 1 1 1 3 5

Totals 9 10

I'm having trouble designing my report using a matrix. Any ideas how to add "total ytd" column and the totals below??

Thanks in advance,

Elias

Which version of SQL Server are you using?

With SQL2005 there is a PIVOT command that would allow you to produce a more conventional shape of report and then flip it.

|||

I'm using SQL reporting 2005. Could you please walk me through how to do that? Many thanks.

|||

Have you just got SQL2005 Reporting or have you got a backend SQL2005 database?

|||

Hello I had the same problem. I just adjusted the body to allow 2 text fields at the top of the colum and put my Sum expression in each of them for those colums. It gives the totals at the top but that can be a good thing if some one is looking for the totals and doesn't want to scroll to the bottom of your report.

|||

using both SSRS and SQL Server 2005

|||

Could you please give more details on how to do this? Still no able to solve my problem. Thanks

|||

The solution to creating the required matrix, will require some different thinking.

Location Carrier Period Total
C1 C2 C3 Total YTD
Bangkok 1 2 3 6 5
CLT 1 1 1 3 5
Totals 9 10

Is the number of carriers fixed? If yes then the problem simplifies enormously, as there besides using a dynamic temporary table (dynamic in the sense that when an extra carrier appears an additional column will be created), a new table can be created as an intermediate in the analysis. This can be either a table within the database or an ordinary temporary table. The SELECT to read this can include a UNION to a SUM on PeriodTotal and TotalYTD as in
SELECT 'A', Location, CONVERT(VARCHAR(10), C1) AS C1,
CONVERT(VARCHAR(10), C2) AS C2,
CONVERT(VARCHAR(10), C3) AS C3, PeriodTotal, TotalYTD FROM Fred
UNION
SELECT 'B', '' AS LOCATION, '' AS C1, '' AS C2, 'Totals' AS C3, SUM(PeriodTotal), SUM(TotalYTD) FROM FRED
ORDER BY 1, 2

|||

Neither the location nor carrier are fixed. Thanks!

|||

>Neither the location nor carrier are fixed.

Since their numbers are not fixed. you will need to adopt an array of arrays approach to accumulate the data and then generate the HTML yourself. The generation of the HTML sounds complicated, but once you make a mockup of what you want to generate, it becomes quite simple.