Merging rows on SSRS

Question

My raw data returned to SSRS.

IF OBJECT_ID('tempdb..#tmpElections') IS NOT NULL 
DROP TABLE #tmpElections
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 * from #tmpElections

On the report, status = partial, if QtySent < QtyReq, else full.

My ssrs report should display as below, merging/blanking the row cells, having same Clientid,materialType and status = 'Full'.The column QtySent should be displayed.

Desired Report Sample Whats the best approach and how to achieve this result. Should this be handled at T-SQL or SSRS.

The yellow highlighted cells should be blank on the report within each group. Sample Report

Answer 1

Thank you all for your comments and solutions. I was able to solve my problem as below.

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

Used the procedure with 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)

Answer 2

You are almost there.. what I would do is add a case statement to determine the status :

   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

Then in your report.. you just group on the first three columns that is shown in your image description... and then the rest as details

Answer 3

I'd use a sub-query to total up your QtySent for comparison, together with a CASE to assign the status text value. The rest is just SSRS formatting.

SELECT
  e.*
 ,CASE
    WHEN s.TotSent = e.QtyReq THEN 'Full'
    ELSE 'Partial'
  END AS [Status]
FROM
  #tmpElections AS e
  LEFT JOIN
    (
      SELECT
        e2.ClientId
       ,e2.MaterialType
       ,SUM(e2.QtySent) AS TotSent
      FROM
        #tmpElections AS e2
      GROUP BY
        e2.ClientId
       ,e2.MaterialType
    ) AS s
      ON
      s.ClientId = e.ClientId
      AND s.MaterialType = e.MaterialType;

Result set:

+----------+--------------+--------+---------+---------+
| ClientId | MaterialType | QtyReq | QtySent | Status  |
+----------+--------------+--------+---------+---------+
|        1 | MM1          |    100 |      50 | Partial |
|        2 | MM2          |    200 |      50 | Partial |
|        2 | MM2          |    200 |      25 | Partial |
|        3 | MM3          |    300 |      50 | Full    |
|        3 | MM3          |    300 |     150 | Full    |
|        3 | MM3          |    300 |     100 | Full    |
|        4 | MM4          |    400 |     300 | Full    |
|        4 | MM4          |    400 |     100 | Full    |
+----------+--------------+--------+---------+---------+