Reporting Services 2005 - Complex Report formatting

Question

We are trying to setup a reporting services report using the following data, (the last three values are just numerical data):

productcode, group, price, finish, weight, length, unit 

001AMIL, 001, 10, AMIL, 0.50, 12, 5
001BSVR, 001, 20, BSVR, 0.50, 12, 5
001CBRZ, 001, 20, CBRZ, 0.50, 12, 5
001EWHT, 001, 24, EWHT, 0.50, 12, 5
001ZZZZ, 001, 21, ZZZZ, 0.50, 12, 5
002AMIL, 002, 10, AMIL, 0.50, 12, 5
002CBRZ, 002, 20, CBRZ, 0.50, 12, 5
002EHWT, 002, 24, EWHT, 0.50, 12, 5
002ZZZZ, 002, 21, ZZZZ, 0.50, 12, 5
003AMIL, 003, 10, AMIL, 0.50, 12, 5
003CBRZ, 003, 20, CBRZ, 0.50, 12, 5
003ZZZZ, 003, 21, ZZZZ, 0.50, 12, 5

We would like a report that will show something along the lines of:

Product                                   AMIL    BSVR    CBRZ    EHWT    ZZZZ
001        Weight  0.50     Price         10      20      20      24      21
           length  12       Price/Meter   0.5     0.5     0.5     0.5     0.5
           unit    5        Product code  001AMIL 001BSVR 001CBRZ 001EWHT 001ZZZZ

002        Weight  0.50     Price         10             20      24      21
           length  12       Price/Meter   0.5            0.5     0.5     0.5
           unit    5        Product code  002AMIL        002CBRZ 002EWHT 02ZZZZ

003        Weight  0.50     Price         10             20              21
           length  12       Price/Meter   0.5            0.5             0.5
           unit    5        Product code  003AMIL        003CBRZ         003ZZZZ

In the last part with the product pricing, we would like to keep the column formatting so that if a product is not present, it's just a blank column if possible.

If anyone can help us to do this it would be most appreciated! Thanks

Nighty_ Nighty_ · Accepted Answer · 2014-02-26T13:37:29

By using some smart groups in reporting services this shouldn't be that hard of a report to create. Now, I don't have 2005 installed so the example is made in 2008 R2. Hopefully you are able to recreate it in 2005.

Here we go. The dataset is just SELECT * FROM [YourTable]. I have not calculated the Price/Meter value as it seems like you have used Weight in your example so I also did that, but if you have a calculation just exchange that field with your calculation. enter image description here The screenshot looks pretty basic right? I have used groups in order to make it look liek you wanted it. So on the row level I have grouped by [Group] so that 001, 002 and 003 shows up after eachother. Then on the column I have grouped on [finish] in order to show AMIL, BSVR, etc. So the result look like whats shown below. Grouping is really powerful once you get the grasp of it. Hopefully this will help you in your case.

enter image description here

Edit: You have to use a matrix so you get dynamic columns. This is my matrix setup: enter image description here

And this is what I have on my groups. enter image description here

Reporting Services 2005 - Complex Report formatting

1 Answers