Removing empty cells in column groups

[Supun]

Hi All,

I'm creating a report with row and column groups. But the columns groups are
displaying values in sperate rows instead on the same row leaving empty
cells. I need to remove those cells and get the column gropu values in a same
row.

Please refer to this image

http://img526.imageshack.us/img526/7139/23561415.png

[Uri Dimant]

Hi
What dataset query do you have? I think SELECT MAX(CASE WHEN .... ) should
solve the problem

"Supun" <Su...@discussions.microsoft.com> wrote in message
news:F842A7A9-7975-438E...@microsoft.com...

[Supun]

Dear Uri,
Thanks for your quick reply.

Actually I'm using this inside a asp .net project and I've created the
report in vs 2010 beta 2. I'm using the following query and using row and
column grouping.

SELECT Items.Code, Items.Name, ItemUnits.Name AS Units,
PEItems.Quantity, Supplier.Name AS Supplier, PESupplierAmounts.Rate,
PESupplierAmounts.Amount
FROM PriceEnquiry INNER JOIN
PEItems ON PriceEnquiry.Id = PEItems.PEId AND
PriceEnquiry.Id = PEItems.PEId AND PriceEnquiry.Id = PEItems.PEId INNER JOIN
Items ON PEItems.ItemId = Items.Id INNER JOIN
PESuppliers ON PriceEnquiry.Id = PESuppliers.PEId
INNER JOIN
Supplier ON PESuppliers.SupplierId = Supplier.Id
INNER JOIN
PESupplierAmounts ON PriceEnquiry.Id =
PESupplierAmounts.PEId AND Items.Id = PESupplierAmounts.ItemId AND
Supplier.Id = PESupplierAmounts.SupplierId INNER JOIN
ItemUnits ON Items.UnitId = ItemUnits.Id
WHERE (PriceEnquiry.Id = @PEId)

I don't understand how to apply your solution. Please expalin further

"Uri Dimant" wrote:

> .
>

[Uri Dimant]

Hi
COL1 COL2 COL3
ITEM1 ITEM2 VALUE1
ITEM1 ITEM2 VALUE2
ITEM1 ITEM2 VALUE3
ITEM1 ITEM2 VALUE4

ITEM3 ITEM4 VALUE1
ITEM3 ITEM4 VALUE2
ITEM3 ITEM4 VALUE3
ITEM3 ITEM4 VALUE4

--------desired result

COL1 COL2 COL3 COL4 COL5 COL6

ITEM1 ITEM2 VALUE1 VALUE2 VALUE3 VALUE4
ITEM3 ITEM4 VALUE1 VALUE2 VALUE3 VALUE4

select
col1
, col2
, max (case when col3 = 'value1' then 'value1' else null end) col3
, max (case when col3 = 'value2' then 'value2' else null end) col4
, max (case when col3 = 'value3' then 'value3' else null end) col5
, max (case when col3 = 'value4' then 'value4' else null end) col6
from
MyTable
group by
col1
, col2

"Supun" <Su...@discussions.microsoft.com> wrote in message

news:167616D7-D815-4D98...@microsoft.com...

[Supun]

Thank you very much, the grouping from the sql level solved the problem!!!