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

Thanks for the sample data. It was very helpful. This is a good first question! For future questions, you may find these links helpful. I use the tips and tools in them all the time. SO - How do I ask a good question? and How to post a T-SQL question on a public forum — Eric Brandt
– Eric Brandt, Commented Sep 6, 2018 at 21:20

Eric Brandt · Accepted Answer · 2018-09-06 21:18:39Z

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    |
+----------+--------------+--------+---------+---------+

Harry · Accepted Answer · 2018-09-06 21:25:16Z

0

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

edited Sep 6, 2018 at 21:25

answered Sep 6, 2018 at 20:38

Harry

2,9691 gold badge23 silver badges36 bronze badges

3 Comments

satish takkalapalli Over a year ago

The case statement would mark all the rows as partial. Among the sample rows, Rows with clientid 3 and 4, should have the status as 'full'.

Harry Over a year ago

See my edited code.. if you want to know all the details.. then you can join back to the table again on client id and Material type and return everything

satish takkalapalli Over a year ago

The SQL part looks good. 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. How can i achieve this display.

satish takkalapalli · Accepted Answer · 2018-09-12 18:15:12Z

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)

Collectives™ on Stack Overflow

Merging rows on SSRS

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related