September 20, 2006 at 9:23 am
I have a crystal cross tab report based on sql 2000 revenue table:
columns : PL_category, booking_date, amount.
In my cross tab report, i have classified PL_category into Funded Income and Expenditure and the booking_date are rows, sum of amount is the summarised data.
I problem is how can i add another column - Profit which will be Funded Income - Expenditure?
In addition I would like the user to input a budget amout and i should also show another calculated value - variance as(budget - profit).
How best can i do this? SQL Stored procedure?
I really appreciate any help
Rob
September 21, 2006 at 12:39 am
September 21, 2006 at 2:50 am
Thanks Roi
columns: ID| PL_category|booking_date|amount
data : -----------------------------------------
1| 50000 |01/05/2006 |230.00
-----------------------------------------
2| 50100 |10/05/2006 |20 .00
------------------------------------------
3| 52000 |03/05/2006 |100 .00
------------------------------------------
4| 52500 |20/05/2006 |50 .00
------------------------------------------
I would like to clasify 50000 and 50100 as Funded Income and
52000 and 52500 as Expenditure
In addition i have a parameter to input the budget - say 120.00
Desired Results in a cross tab report:
Budget - 120
Month |Funded Income|Expenditure|Profit|Variance
----------------------------------------------------------
May 2006| 250.00 | 150.00 |100.00| 20
----------------------------------------------------------
my Profit is (Funded Income - Expenditure)
Variance is (Budget - profit)
I hope this is clear
regards
September 21, 2006 at 3:24 am
I wrote something quickly, let me know if you need more than that.
-- Simulate data
CREATE
TABLE #Report (
ID INT ,
PL_category INT ,
booking_date DATETIME,
AMOUNT INT
)
INSERT
#Report
VALUES
(1, 50000, '5/1/06',230)
INSERT
#Report
VALUES
(2, 50100, '5/10/06',20)
INSERT
#Report
VALUES
(3, 52000, '5/3/06',100)
INSERT
#Report
VALUES
(4, 52500, '5/20/06',50)
/* -- Your requests
Month |Funded Income|Expenditure|Profit|Variance
----------------------------------------------------------
May 2006| 250.00 | 150.00 |100.00| 20
----------------------------------------------------------
*/
-- Select Query
SELECT
DATENAME(MONTH,booking_date) + ' ' + CAST(DATEPART(YEAR,booking_date) AS VARCHAR) AS MONTH,
SUM(CASE WHEN PL_category IN (50000,50100) THEN AMOUNT ELSE 0 END) AS [Funded Income],
SUM(CASE WHEN PL_category IN (52000,52500) THEN AMOUNT ELSE 0 END) AS [Expenditure],
SUM(CASE WHEN PL_category IN (50000,50100) THEN AMOUNT ELSE 0 END) - SUM(CASE WHEN PL_category IN (52000,52500) THEN AMOUNT ELSE 0 END) AS Profit
FROM
#Report
GROUP
BY DATENAME(MONTH,booking_date) + ' ' + CAST(DATEPART(YEAR,booking_date) AS VARCHAR)
DROP TABLE #Report
Few things,
1. It's just an example, use the same for Variance.
2. I used Temp table in order to simulate your table
3. You can replace CASE with any otder logic (like mapping table for categories and join with that table)
Good luck,
Roi Assa
September 21, 2006 at 7:16 am
This is it Roi
Thanks so much, you have made my day
This forum is really useful
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply