sql-serverreporting-servicesssrs-2008
How to build a rolling forward SSRS report
I have been working on a report that would allow a user to review the totals rolling forward.
This a is matrix. I have included an sample on what the data looks like.
The report should start the running total from Oct with the amount of $42. Then the report will sum on the accounts. Then take the $42 + $64 = $106. The $106 will carry over to the Dec month starting the beginning balance at $106.
Report that I am working on
Example of the report
2016-011 2016-012
Beginning Balance 42 106
AP 31 41
APAJ 32 42
CJ 33 43
GEN -32 -42
Total Account 64 84
Begin Bal + Total Acc 106 190
Example of the data
DECLARE @ClosingBalTemp TABLE
(
Account_ID varchar(30),
Period_Nbr varchar(10),
Source_Code varchar(10),
Closing_Balance_Amt numeric(16,2)
)
INSERT INTO @ClosingBalTemp (Account_ID, Period_Nbr, Source_Code, Closing_Balance_Amt)
VALUES ('01-002-333', '2016-008', 'AP', 1),
('01-002-333', '2016-008', 'APAJ', 2),
('01-002-333', '2016-008', 'CJ', 3),
('01-002-333', '2016-008', 'GEN', -2),
('01-002-333', '2016-009', 'AP', 11),
('01-002-333', '2016-009', 'APAJ', 12),
('01-002-333', '2016-009', 'CJ', 13),
('01-002-333', '2016-009', 'GEN', -12),
('01-002-333', '2016-010', 'AP', 20),
('01-002-333', '2016-010', 'APAJ', 21),
('01-002-333', '2016-010', 'CJ', 23),
('01-002-333', '2016-010', 'GEN', -22),
('01-002-333', '2016-011', 'AP', 31),
('01-002-333', '2016-011', 'APAJ', 32),
('01-002-333', '2016-011', 'CJ', 33),
('01-002-333', '2016-011', 'GEN', -32),
('01-002-333', '2016-012', 'AP', 41),
('01-002-333', '2016-012', 'APAJ', 42),
('01-002-333', '2016-012', 'CJ', 43),
('01-002-333', '2016-012', 'GEN', -42)
SELECT * FROM @ClosingBalTemp
The data should look like this
The logic is almost working. The Bal type is not calculating the running balance correctly after Period 9. The 4.00 is coming from Oct that does not display.
B BAL 2016 9 4.00
T AP 2016 9 11.00
T APAJ 2016 9 12.00
T CJ 2016 9 13.00
T GEN 2016 9 -12.00
B BAL 2016 10 24.00 **Should be 33**
T AP 2016 10 20.00
T APAJ 2016 10 21.00
T CJ 2016 10 23.00
T GEN 2016 10 -22.00
B BAL 2016 11 42.00 **Should be 75**
T AP 2016 11 31.00
T APAJ 2016 11 32.00
T CJ 2016 11 33.00
T GEN 2016 11 -32.00
B BAL 2016 12 64.00 **Should be 139**
T AP 2016 12 41.00
T APAJ 2016 12 42.00
T CJ 2016 12 43.00
T GEN 2016 12 -42.00
Solution
I don't think Running totals are going to work because of the column layout. We want the top of 2016-012 to come from the bottom of 2016-011. I will say I don't quite understand your data set and I do a lot of roll forwards/trial balancesHers's s post on Trial blances. But I do believe I have solved your problem using 2008 stuff with the suggested stored procedure. Ideally, year and period would be separate fields to make calculating the periods easier. I just hardcoded them for now. With the output, you can create a column for each month in your SSRS report. You can calculate the month end totals yourself in SSRS. There may be a better way to do this in 2008 but I don't have 2008 on my server so I can't test. (I would handle the beginning balance summaries differently)
DECLARE @CurrentPeriod CHAR(10);
DECLARE @PriorPeriod CHAR(10);
DECLARE @PPPeriod CHAR(10)
SET @CurrentPeriod = '2016-012';
SET @PriorPeriod = '2016-011';
SET @PPPeriod = '2016-010';
-- First Step - Summarize the balances
WITH Balances (CurrentPeriodOpenBal, PriorPeriodOpenBal)
AS (
SELECT SUM(CASE WHEN cbt.Period_Nbr IN (@PPPeriod, @PriorPeriod)
THEN cbt.Closing_Balance_Amt ELSE 0 END)
AS CurrentPeriodOpenBal,
SUM(CASE WHEN cbt.Period_Nbr IN (@PPPeriod)
THEN cbt.Closing_Balance_Amt ELSE 0 END)
AS PriorPeriodOpenBal
FROM dbo.ClosingBalTemp cbt
WHERE cbt.Period_Nbr < @CurrentPeriod
)
-- Now we are going to combine our balances and our transaction
,
BalTrans (TransType, SourceCode, PeriodNbr, Amount) AS
(
SELECT 'B', 'BAL', @PriorPeriod, bal.PriorPeriodOpenBal
FROM Balances bal
UNION ALL
SELECT 'B', 'BAL', @CurrentPeriod, bal.CurrentPeriodOpenBal
FROM Balances bal
UNION ALL
SELECT 'T', trans.Source_Code, trans.Period_Nbr, trans.Closing_Balance_Amt
FROM dbo.ClosingBalTemp trans
WHERE trans.Period_Nbr BETWEEN @PriorPeriod AND @CurrentPeriod
)
-- Now we simply order our transactions
SELECT bt.TransType, bt.SourceCode, bt.PeriodNbr, bt.Amount FROM BalTrans bt
ORDER BY bt.PeriodNbr, bt.TransType, bt.SourceCode
--- Revised Version
-- First Step - convert period to fiscla year and period
WITH Trans AS
(SELECT cbt.Account_ID,
cbt.Period_Nbr,
CAST(SUBSTRING(cbt.Period_Nbr,1,4) AS INT) AS FiscalYear,
CAST(SUBSTRING(cbt.Period_Nbr,6,3) AS INT) AS FiscalPeriod,
cbt.Source_Code,
cbt.Closing_Balance_Amt
FROM dbo.ClosingBalTemp cbt)
-- This gets the total balance for each period - we add one to the period
-- to figure out the opening info
, Balances ( FiscalYear, FiscalPeriod, OpenPeriod, Amount)
AS (
SELECT DISTINCT
bal.FiscalYear,
bal.FiscalPeriod,
bal.FiscalPeriod + 1 AS OpenPeriod,
SUM(bal.Closing_Balance_Amt) OVER(ORDER BY bal.Period_Nbr) AS Amount
FROM Trans bal
WHERE bal.Period_Nbr >= @PPPeriod
AND bal.Period_Nbr < @StartingPeriod
)
-- Now we are going to combine our balances and our transaction
,
BalTrans (TransType, SourceCode, FiscalYear, FiscalPeriod, Amount) AS
(
SELECT 'B', 'BAL', bl.FiscalYear, bl.OpenPeriod, bl.Amount FROM Balances bl
UNION ALL
SELECT 'T', trans.Source_Code, trans.FiscalYear, trans.FiscalPeriod,
trans.Closing_Balance_Amt
FROM Trans trans
WHERE trans.Period_Nbr BETWEEN @EndingPeriod AND @StartingPeriod
)
SELECT bt.TransType, bt.SourceCode, bt.FiscalYear, Bt.FiscalPeriod,
bt.Amount FROM BalTrans bt
ORDER BY bt.FiscalYear, bt.FiscalPeriod, bt.TransType, bt.SourceCode
-- Example with cursor
DECLARE @EndPeriod CHAR(10);
DECLARE @StartPeriod CHAR(10);
DECLARE @PPPeriod CHAR(10)
-- Ideally, year and period would be separate integer fields
-- so one could calculate prior period from current Period.
-- But that's not the key part of the question
SET @EndPeriod = '2016-012';
SET @StartPeriod = '2016-009';
SET @PPPeriod = '2016-008';
DECLARE @Period_Nbr CHAR(10);
DECLARE @Source_Code CHAR(10);
DECLARE @Closing_Balance_Amt NUMERIC(16,2);
DECLARE @ReportPeriod CHAR(10);
DECLARE @ReportBalance NUMERIC(16,2);
CREATE TABLE #Output
( ID INTEGER IDENTITY (1,1) PRIMARY KEY,
Period_Nbr CHAR(10),
Source_Code CHAR(10),
Closing_Balance_Amt numeric(10,2)
)
-- the following insures that SSRS sees the output correctly
IF 1=2
BEGIN
SELECT ID,
Period_Nbr,
Source_Code,
Closing_Balance_Amt
FROM #Output;
END;
DECLARE OMGUAC CURSOR FAST_FORWARD FOR
SELECT Period_Nbr, Source_Code, Closing_Balance_Amt
FROM dbo.ClosingBalTemp
WHERE Period_Nbr BETWEEN @PPPeriod AND @EndPeriod
ORDER BY Period_Nbr, Source_Code;
OPEN OMGUAC;
FETCH NEXT FROM OMGUAC INTO @Period_Nbr,@Source_Code,@Closing_Balance_Amt;
SET @ReportPeriod = @Period_Nbr;
SET @ReportBalance = 0;
WHILE @@FETCH_STATUS = 0
BEGIN
-- When the period Changes, print the opening balance
IF @Period_Nbr <> @ReportPeriod
BEGIN
INSERT INTO #Output
( Period_Nbr ,
Source_Code ,
Closing_Balance_Amt
)
VALUES (@Period_Nbr, 'Open Bal',@ReportBalance);
END
IF @Period_Nbr > @PPPeriod
BEGIN
INSERT INTO #Output
( Period_Nbr ,
Source_Code ,
Closing_Balance_Amt
)
VALUES (@Period_Nbr, @Source_Code,@Closing_Balance_Amt);
END
SET @ReportBalance = @ReportBalance + @Closing_Balance_Amt;
SET @ReportPeriod = @Period_Nbr
FETCH NEXT FROM OMGUAC INTO @Period_Nbr, @Source_Code,
@Closing_Balance_Amt;
END;
SELECT * FROM #Output
ORDER BY ID;
DROP TABLE #Output;
CLOSE OMGUAC;
DEALLOCATE OMGUAC;
- Math.Sin() gives incorrect value
- How to run my python script when the sunOS is start booting
- Express-session: not resetting cookie expiration on each request
- Getting a stack overflow exception when normalizing a vector
- Edit default summary function in R gives error for multiple variables
- What was a For loop? Why isn't it needed in R?
- How to use download button in shiny and save results in various formats (csv, texte, pdf, spss...)?
- Why are there two assignment operators, `<-` and `->` in R?
- lm()$assign: what is it?
- How to get the value of list(...) in R and S functions
- Design matrix for MLM from library(lme4) with fixed and random effects
- how to generate elements not included in my sample
- Create a matrix with gradually changing values without a for loop
- Emacs ESS and S-plus ( S+ ) 8.1 compatability
- How to lag date-index in a time-series in R?
- Nonlinear regression in R / S
- Calling R from S-Plus?