SSRS grouping and rows display

• s-sql

  SSCommitted

  Points: 1970

  More actions

  September 12, 2018 at 8:47 am

  #362906

  
  Attached sample proc code that is used by the report.
  I grouped the first 3 columns on the report.
  Within the group rows, I need to hide these 3 columns on all rows except for the last row within the group.
  Desired output in the Sample report image. The yellow highlighted cells should be blank on the report within each group.
  How can i achieve this display.

  
-- exec TestRptSample

  Create procedure dbo.TestRptSample
as
begin

  create table #tmpElections
(
ClientId int,
MaterialType varchar(50),
QtyReq int,
QtySent int
)

  insert into #tmpElections values (1,'MM1',100,50)
insert into #tmpElections values (2,'MM2',200,50)
insert into #tmpElections values (2,'MM2',200,25)
insert into #tmpElections values (3,'MM3',300,50)
insert into #tmpElections values (3,'MM3',300,150)
insert into #tmpElections values (3,'MM3',300,100)
insert into #tmpElections values (4,'MM4',400,300)
insert into #tmpElections values (4,'MM4',400,100)

  select b.*,a.status
from
(
select ClientId,MaterialType, max(QtyReq) as qtyreq, sum(QtySent) as qtysent
, case when sum(QtySent)<max(QtyReq) then 'Partial' else 'Full' end as [status]
from #tmpElections
group by
ClientId
,MaterialType
) A
inner join #tmpElections B on a.ClientId = b.ClientId and a.MaterialType = b.MaterialType
end


• mark.humphreys

  SSC Eights!

  Points: 867

  More actions

  September 12, 2018 at 9:18 am

  #2005444

  Hi There,

  Do you actually want to sum the qty sent, and then end up with one row per client ID rather than trying to hide rows?

  If so you could simply look at your grouping to achieve this and then end up with a single row per client ID.

  Please feel free to post more info regarding your desired output.

  Thanks
  Mark

• s-sql

  SSCommitted

  Points: 1970

  More actions

  September 12, 2018 at 9:40 am

  #2005447

  No. do not want the sum of qtySent.
  There will be bunch of other columns like DispatchDate, Location for each of those rows which have different values per row.
  The report needs to hide the "group by" columns on the detail rows within the group, except on the last row.

• s-sql

  SSCommitted

  Points: 1970

  More actions

  September 12, 2018 at 12:23 pm

  #2005481

  Thank you all for your comments and solutions. I was able to solve my problem as below.
  In the procedure, used row_number to generate row numbers within the group by sets.
  On the report, in visibility expressions of the row text boxes, used the following expression to show or hide that column.

  iif(Fields!mStatus.Value="Full" and Fields!Rowno.Value <> Fields!MaxRow.Value ,True,False)

  
Create procedure dbo.TestRptSample
as
begin

  create table #tmpElections
(
ClientId int,
MaterialType varchar(50),
QtyReq int,
QtySent int,
SentDate datetime
)

  insert into #tmpElections values (1,'MM1',100,50,'02/01/2018')
insert into #tmpElections values (2,'MM2',200,50,'02/01/2018')
insert into #tmpElections values (2,'MM2',200,25,'03/01/2018')
insert into #tmpElections values (3,'MM3',300,50,'02/01/2018')
insert into #tmpElections values (3,'MM3',300,150,'02/15/2018')
insert into #tmpElections values (3,'MM3',300,100,'03/01/2018')
insert into #tmpElections values (4,'MM4',400,300,'02/01/2018')
insert into #tmpElections values (4,'MM4',400,100,'03/01/2018')

  create table #tmpFinal
(
ClientId int,
MaterialType varchar(50),
QtyReq int,
QtySent int,
SentDate datetime,
mStatus varchar(100),
)

  Insert into #tmpFinal
select b.*,a.status
from
(
select ClientId,MaterialType, max(QtyReq) as qtyreq, sum(QtySent) as qtysent
, case when sum(QtySent)<max(QtyReq) then 'Partial' else 'Full' end as [status]
from #tmpElections
group by
ClientId
,MaterialType
) A
inner join #tmpElections B on a.ClientId = b.ClientId and a.MaterialType = b.MaterialType;

  with x as
(
select *,
ROW_NUMBER() over (partition by clientId,materialType,qtyReq
order by sentdate) as Rowno
from #tmpFinal
)

  select *
,max(rowno) over (partition by clientId,materialType,qtyReq) as MaxRow
from x
order by clientId ,sentdate

  end