CODEWITHSUNDEEP
sql-servert-sqlreporting-services
sql_newb
sql_newb

Reputation: 9

SQL Server: divide results of a column based on value from 2 other columns

I have a table like this:

enter image description here

I want to display a report like this (I plan to do this in SSRS):

enter image description here

The % is calculated by BG_SALES/TOTAL_SALES for the current period and previous period.

How do I go about doing this? Appreciate any help .. thanks

Upvotes: 0

Views: 73

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21703

I would do this in two parts. First I would calculate the % you require in SQL

Below I've reproduced your sample data and then returned it with the %'s appended.

DECLARE @t TABLE (Category varchar(10), [Period] varchar(4), [Type] varchar(20), Amount float)
INSERT INTO @t VALUES
('TTL_SALES', 'CURR', 'CLOTHES', 1234000),
('TTL_SALES', 'PRE', 'CLOTHES', 1355657),
('TTL_SALES', 'CURR', 'ACCESSORIES', 34000),
('TTL_SALES', 'PRE', 'ACCESSORIES', 36000),
('BG_SALES', 'CURR', 'CLOTHES', 550000),
('BG_SALES', 'PRE', 'CLOTHES', 575000),
('BG_SALES', 'CURR', 'ACCESSORIES', 12000),
('BG_SALES', 'PRE', 'ACCESSORIES', 9000)

SELECT * FROM @t
UNION ALL
SELECT 
    'BG_%' as Category
    , t.[Period]
    , t.[Type]
    , b.Amount/t.Amount
FROM (SELECT * FROM @t WHERE Category = 'TTL_SALES') t
    JOIN (SELECT * FROM @t WHERE Category = 'BG_SALES') b
        ON t.[Period] = b.[Period] and t.[Type] = b.[Type]

This gives us the following results

enter image description here

I used this query as the dataset query in my report. I added a matrix with two row groups, one for Type and one for Category I added a column group for Periodd I set the [data] cell to Amount

I added a blank row within the type group and removed any unnecessary columns.

Finally I reversed the sort order on the row groups so the result came out as expected.

The final design looked like this...

enter image description here

One more quick change.... I set the Format property of the Amount textbox to the following...

=IIF(Fields!Category.Value = "BG_%", "p2", "n0")

This will format the BG_% row as a percentage with 2 decimals and any other rows as numbers with thousand separators and zero decimal places.

The final output looks like this.

enter image description here

Upvotes: 1

Related Questions