SSRS Predicament with expressions

Question

I have an SSRS dataset that looks like this:

The dataset rows are generated independent of each other using UNION ALL. I need to display these rows in my report as is, but I need to add an additional row that will calculate Total Won / Total Lost, so the result should look like this:

This is just sample as I have more columns (1 per month) and the whole thing is broken down by product, so if I have 10 different products, I will have 10 different tablix tables.

Basically I need to somehow create an expression that will only calculate values in 2 rows of the tablix out of 3 (based on the value of the Status column) and take into consideration that some values can be zeroes.

Here's the query (I simplified it a bit for better understanding):

select * from
(
select 'Created' as 'State', fo.groupidname, fo.businessidname ' Business', fo.opportunityid
from FilteredOpportunity fo
where fo.regionidname = 'Americas Region'
and fo.createdon >= dateadd(year, -1, getdate())
and fo.regionalfeeincome >= 250000
) created
pivot
(
count(created.opportunityid)
for created.groupidname in ([Boston], [Chicago], [Colombia], [Group D.C.], [Houston], [Los Angeles], [New York], [San Francisco], [Seattle], [Toronto])
) pivCreated
union all
select * from
(
select 'Won' as 'State', fo.groupidname, fo.businessidname ' Business', fo.opportunityid
from FilteredOpportunity fo
where regionidname = 'Americas Region'
and fo.actualclosedate >= dateadd(year, -1, getdate())
and regionalfeeincome >= 250000
and fo.jna is not null 
) won
pivot
(
count(won.opportunityid)
for won.groupidname in ([Boston], [Chicago], [Colombia], [Group D.C.], [Houston], [Los Angeles], [New York], [San Francisco], [Seattle], [Toronto])
) pivWon
union all
select * from
(
select 'Lost' as 'State', fo.groupidname, fo.businessidname ' Business', fo.opportunityid
from FilteredOpportunity fo
where fo.regionidname = 'Americas Region'
and fo.actualclosedate >= dateadd(year, -1, getdate())
and fo.regionalfeeincome >= 250000
and fo.sys_phasename <> 'Pre-Bid'
) lost
pivot
(
count(lost.opportunityid)
for lost.groupidname in ([Boston], [Chicago], [Colombia], [Group D.C.], [Houston], [Los Angeles], [New York], [San Francisco], [Seattle], [Toronto])
) pivLost

TIA -TS.

Answer 1

I can't fully test this as I don't have time to build sample data but it should work...

If you use this as your report's dataset query then you should be able to add a simple matrix with a row group by State and a column group by City

/*
You can optimise this a bit but I've kept it fairly procedural so it's easier to follow
*/
-- First do your 3 basic queries but this time we don't pivot and we dump the results into temp tables
-- I've also removed columns that don't appear to be used based on your sample output and remaned a column to City to make it easier to read
select 'Total Created' as 'State', fo.groupidname as City, COUNT(*) AS Cnt
INTO #Created
    from FilteredOpportunity fo
    where fo.regionidname = 'Americas Region'
        and fo.createdon >= dateadd(year, -1, getdate())
        and fo.regionalfeeincome >= 250000

select 'Total Won' as 'State', fo.groupidname as City, COUNT(*) AS Cnt
INTO #Won
    from FilteredOpportunity fo
    where fo.regionidname = 'Americas Region'
        and fo.createdon >= dateadd(year, -1, getdate())
        and fo.regionalfeeincome >= 250000
        and fo.jna is not null 

select 'Total Lost' as 'State', fo.groupidname as City, COUNT(*) AS Cnt
INTO #Lost
    from FilteredOpportunity fo
    where fo.regionidname = 'Americas Region'
        and fo.createdon >= dateadd(year, -1, getdate())
        and fo.regionalfeeincome >= 250000
        and fo.sys_phasename <> 'Pre-Bid'

-- Now we calculate your Ratio
SELECT
    'Win Ratio' as 'State' 
    , coalesce(w.City, l.City) as City -- use coalesce in case 1 side of join is null
    , CASE WHEN ISNULL(l.cnt,0) = 0 THEN 0 ELSE w.Cnt/l.Cnt END as Cnt -- if lost is null or 0 return 0 else calc ratio
into #Ratio
 FROM #Won w
    full join #Lost l on w.City = l.City

-- finaly, get the results and add a sort to help the report row sorting.
SELECT 1 as Sort, * FROM #Created
UNION SELECT 2, * FROM #Won
UNION SELECT 3, * FROM #Lost
UNION SELECT 4, * FROM #Ratio