Search code examples
ssrs-2012

Sum and Percentage by using LookupSet

I have a SQL report that counts the number of instances of a field. I am pulling this into a summary report. This works using the Lookupset function. However, I want to take the total of this column and find the percentage of the rows. For example:

Preferred Employer = Yes, Preferred Employer = No I want to be able to add a Percentage Column next to the count column that would show the Percentage of each row. The end result would total 100

enter image description here

Count formula: =Join(LookupSet(Fields!pscode.Value, Fields!pscode.Value, Fields!PrefCnt.Value, "PrefEmployer"), vbCrLF + vbCrLF)

Total: =RunningValue(Fields!PrefCnt.Value,Sum,"PrefEmployer")

Percent:

=IIF(RunningValue(Fields!PrefCnt.Value,Sum,"PrefEmployer") <> 0, Join(LookupSet(Fields!pscode.Value, Fields!pscode.Value, Fields!PrefCnt.Value, "PrefEmployer"), vbCrLF + vbCrLF)/RunningValue(Fields!PrefCnt.Value,Sum,"PrefEmployer")*100,0)

Results enter image description here

SELECT
vt.pscode as pscode,
vt.PrefEmpl as PrefEmpl,
SUM(vt.PrefCnt) as PrefCnt
FROM
(
SELECT
  rtrim(p.sAddr1) + ' (' + rtrim(p.sCode) + ')' pscode,
  isnull(ltrim(lb.sPrefEmployer), 'No') PrefEmpl,
  count(lb.sPrefEmployer) PrefCnt
FROM
  property p (nolock)
  INNER JOIN tenant t (nolock) on p.hMy = t.hProperty
  INNER JOIN Leasebut28 lb (nolock) on t.hMyPerson = lb.hCode
  INNER JOIN tenstatus ts (nolock) ON (t.istatus = ts.istatus)
WHERE
  (
    t.dtMoveIn <= '2022-08-22'
    and (
      t.dtMoveOut > '2022-08-22'
      or t.dtMoveOut is null
    )
  )
  AND ts.Status in ('Current', 'Future', 'Notice')
  AND t.sUnitCode NOT IN ('COMAREA', 'WAIT')
  AND t.sUnitCode NOT LIKE ('NONRES%')
  AND lb.sCorp <> 'Yes'
  and p.scode = 'carcen2'
GROUP BY
  rtrim(p.sAddr1) + ' (' + rtrim(p.sCode) + ')',
  lb.sPrefEmployer
UNION ALL
SELECT
  rtrim(p.sAddr1) + ' (' + rtrim(p.sCode) + ')' pscode,
  isnull(ltrim(b.sPrefEmployer), 'No') PrefEmpl,
  count(b.sPrefEmployer) PrefCnt
FROM
  room r (nolock)
  INNER JOIN tenant t (nolock) ON (t.hmyperson = r.hmytenant)
  INNER JOIN unit u (nolock) ON (u.hmy = t.hunit)
  INNER JOIN unittype ut (nolock) on (ut.hMy = u.hUnitType)
  INNER JOIN tenstatus ts (nolock) ON (t.istatus = ts.istatus)
  INNER JOIN property p (nolock) ON (p.hmy = t.hproperty)
  LEFT OUTER JOIN RoomBut1 b (nolock) ON (r.hMyPerson = b.hCode)
WHERE
  isnull(r.dtmoveout, '01/01/2400') > Getdate()
  AND (
    t.dtMoveIn <= '2022-08-22'
    and (
      t.dtMoveOut > '2022-08-22'
      or t.dtMoveOut is null
    )
  )
  AND r.sRelationship <> 'Guarantor'
  AND ts.Status in ('Current', 'Future', 'Notice')
  AND t.sUnitCode NOT IN ('COMAREA', 'WAIT')
  AND t.sUnitCode NOT LIKE ('NONRES%')
  AND r.bOccupant <> -1
  and p.scode = 'carcen2'
 GROUP BY
  rtrim(p.sAddr1) + ' (' + rtrim(p.sCode) + ')',
  b.sPrefEmployer
) vt
GROUP BY
vt.pscode,
vt.PrefEmpl
Order by 2

Dataset: enter image description here

Solution

  • I may have misunderstood this but if you just want to get the Yes vs No % share of each pscode then you could just change your query from

    SELECT
vt.pscode as pscode,
vt.PrefEmpl as PrefEmpl,
SUM(vt.PrefCnt) as PrefCnt
FROM
(
...
... existing subquery here
...
) vt
GROUP BY
vt.pscode,
vt.PrefEmpl
Order by 2

    to

    SELECT DISTINCT
    vt.pscode as pscode,
    vt.PrefEmpl as PrefEmpl,
    SUM(vt.PrefCnt) OVER(PARTITION BY vt.pscode, vt.PrefEmpl) as PrefCnt
    CAST(SUM(vt.PrefCnt) OVER(PARTITION BY vt.pscode, vt.PrefEmpl) as float)
       / CAST(SUM(vt.PrefCnt) OVER(PARTITION BY vt.pscode) as float)
       as PrefCntPercent
FROM
(
...
... existing subquery here
...
) vt
Order by 2