Prev: Cover page problem
Next: Adding Remarks in Reports
From: Supun on 15 Dec 2009 16:25
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
From: Uri Dimant on 16 Dec 2009 03:58
Hi
What dataset query do you have? I think SELECT MAX(CASE WHEN .... ) should
solve the problem

"Supun" <Supun(a)discussions.microsoft.com> wrote in message
news:F842A7A9-7975-438E-B2E5-40C72166DF63(a)microsoft.com...
> 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


From: Supun on 16 Dec 2009 10:06
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:

> Hi
> What dataset query do you have? I think SELECT MAX(CASE WHEN .... ) should
> solve the problem
>
> "Supun" <Supun(a)discussions.microsoft.com> wrote in message
> news:F842A7A9-7975-438E-B2E5-40C72166DF63(a)microsoft.com...
> > 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
>
>
> .
>
From: Uri Dimant on 17 Dec 2009 01:44
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" <Supun(a)discussions.microsoft.com> wrote in message
news:167616D7-D815-4D98-B274-F25A3F1D47F7(a)microsoft.com...
> 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:
>
>> Hi
>> What dataset query do you have? I think SELECT MAX(CASE WHEN .... )
>> should
>> solve the problem
>>
>> "Supun" <Supun(a)discussions.microsoft.com> wrote in message
>> news:F842A7A9-7975-438E-B2E5-40C72166DF63(a)microsoft.com...
>> > 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
>>
>>
>> .
>>


From: Supun on 18 Dec 2009 02:39
Thank you very much, the grouping from the sql level solved the problem!!!
 | 
Pages: 1
Prev: Cover page problem
Next: Adding Remarks in Reports