Repeat the last column value when it is empty -SSRS

Question

Is it possible to repeat the last column value in SSRS? As in the application, all empty rows in the last column should be filled with the last value 702

I used the previous, last functions, but nothing helped

Answer 1

We can do this at the end of SQL and get the data in SSRS

Steps:

• Take a turn if necessary
• Get data in the drilldown column. Here is the Absolute Month

• Then use the SQL method to replicate the zeros / last values ​​that are empty with the last highest value Link:

  `select a.AbsoluteMonth,Mon
  ,first_value(a.S1_pax)over(partition by a.v1_p order by num ) as S_Pax
  ,first_value(a.S2_pax)over(partition by a.v2_p order by num ) as S2_Pax`
  from
  (select *
  ,sum(case when S1_pax is null then 0 else 1 end) over (order by num) as v1_p 
  ,sum(case when S2_pax is null then 0 else 1 end) over (order by num) as v2_p
   from X_Table
  )a
  

And fill in all the places accordingly. Plz link below output

Answer 2

, :

1. sql-, Year, AbsoluteMonth ..

, Year/AbsoluteMonth ONE.

2. :

=IIf(IsNothing(Sum(Fields!Amt.Value)), Last(Fields!Amt.Value, "Year"), Sum(Fields!Amt.Value))

"" - , Amt - , , , R_Pax

Step3. () , , .

1 . , , , (1) 30, 50, 60 (2) , 60 (2), (3) .., (30 + 50 + 60).

Answer 3

, .
, .

DECLARE @Today DATETIME
SET @Today = GETDATE()

DECLARE @MatrixData TABLE (
  Month1 INT
, Year1 INT
, Value INT
)

INSERT INTO @MatrixData (Month1, Year1, Value)
SELECT MONTH(DATEADD(MONTH, Id * -1, @Today)) AS Date1Month, YEAR(DATEADD(MONTH, Id * -1, @Today)) AS Date1Year, Id * 10 AS Value1
FROM (
    SELECT TOP 60 ROW_NUMBER() OVER (ORDER BY Id) AS Id
    FROM SysObjects
) A
ORDER BY Date1Year, Date1Month

SELECT * FROM @MatrixData

-- Insert blank month of last year with last value
INSERT INTO @MatrixData (Month1, Year1, Value)
SELECT A.RunningMonth, A1.MaxYear, A1.LastValue
FROM (
    SELECT TOP 12 ROW_NUMBER() OVER (ORDER BY Id) AS RunningMonth
    FROM SysObjects
) A
INNER JOIN (
    -- Get Last Value in @MatrixData 
    SELECT A.MinMonth, A.MaxMonth, A.MaxYear, A1.Value AS LastValue
    FROM (
        -- Get Max Month Last Year in @MatrixData 
        SELECT MAX(A1.Month1) AS MinMonth, A.MaxMonth, A.MaxYear
        FROM (
            -- Get Max Month & Max Year
            SELECT MAX(Month1) AS MaxMonth, MAX(Year1) AS MaxYear
            FROM @MatrixData
        ) A
        INNER JOIN @MatrixData A1 ON A.MaxYear = A1.Year1
        GROUP BY A.MaxMonth, A.MaxYear
    ) A
    INNER JOIN @MatrixData A1 ON A.MinMonth = A1.Month1 AND A.MaxYear = A1.Year1
) A1 ON A.RunningMonth > A1.MinMonth AND A.RunningMonth <= A1.MaxMonth

SELECT * FROM @MatrixData

Answer 4

Oracle . SQL Server COALESCE LAG. SQL Server. , . .

create table mytab(n number, m number);
insert into mytab values(1,null);
insert into mytab values(2,null);
insert into mytab values(3,44949);
insert into mytab values(4,null);
insert into mytab values(5,null);
insert into mytab values(6,null);
insert into mytab values(7,null);
insert into mytab values(8,null);
insert into mytab values(9,null);
insert into mytab values(10,null);
insert into mytab values(11,74631);
insert into mytab values(12,null);
insert into mytab values(13,null);
select t.*, coalesce(m, lag(m ignore nulls) over (order by n))
from mytab t;