Column group percentages

Question

How do I compare two columns in separate column groups to a row group total to get percentages? The rows are grouped on [trx_dt] The Direct and Indirect columns are grouped on [labor_type] and filtered to show direct and indirect labor respectively. The goal is to have each days total as well as the overall totals represented by percentages as well as their actual value.

And this is how the report looks now without the percentages

Edit: 7/13/15: I'm able to get percentages in the total row using

=Sum(Fields!lbr_hrs.Value)/Sum(Fields!lbr_hrs.Value, "DataSet1")

Is there a way to isolate the total value of a column group to compare to the total value of a row group?

Edit 7/16/15 In response to 1st comment: In the first row I want 4.83 and 3.17 as a percentage of 8. This will give me daily percentages.

Here is the query:

SELECT 'P' AS RecordType,CASE WHEN mctrxhst_sql.wc != 'INDIRECT' then 'DIRECT' else 'INDIRECT' END AS labor_type, mctrxhst_sql.trx_dt,mctrxhst_sql.ord_no, 
        mctrxhst_sql.oper_no, mctrxhst_sql.desc_1, mctrxhst_sql.lbr_hrs, mccrwhst_sql.emp_no, mccrwhst_sql.lbr_grd, sfordfil_sql.item_no
    FROM mctrxhst_sql 
    LEFT OUTER JOIN sfordfil_sql ON sfordfil_sql.ord_no=mctrxhst_sql.ord_no
    LEFT OUTER JOIN hzctrfil_sql ON hzctrfil_sql.dept=mctrxhst_sql.dept AND hzctrfil_sql.wc=mctrxhst_sql.wc
    LEFT OUTER JOIN hzcdefil_sql ON hzcdefil_sql.hz_cd=mctrxhst_sql.dept AND hzcdefil_sql.cd_type='DP'
    LEFT OUTER JOIN mccrwhst_sql ON mccrwhst_sql.job_no=mctrxhst_sql.job_no AND mccrwhst_sql.ord_no=mctrxhst_sql.ord_no AND mccrwhst_sql.path_no=mctrxhst_sql.path_no
                                AND mccrwhst_sql.oper_no=mctrxhst_sql.oper_no AND mccrwhst_sql.oper_seq_no=mctrxhst_sql.oper_seq_no AND mccrwhst_sql.pln_act_cd=mctrxhst_sql.pln_act_cd
                                AND mccrwhst_sql.trx_dt=mctrxhst_sql.trx_dt AND mccrwhst_sql.control_no = mctrxhst_sql.control_no
    CROSS JOIN compfile_sql
    WHERE mctrxhst_sql.pln_act_cd = '1' AND mctrxhst_sql.rec_type='O'
        AND mctrxhst_sql.trx_dt BETWEEN @from AND @to 
        AND mctrxhst_sql.dept = 'MFG'
        AND mccrwhst_sql.emp_no IN (@Employee)
UNION

    SELECT 'U' AS RecordType, CASE WHEN mctrxfil_sql.wc != 'INDIRECT' then 'DIRECT' else 'INDIRECT' END AS labor_type, mctrxfil_sql.trx_dt,  mctrxfil_sql.ord_no, mctrxfil_sql.oper_no, mctrxfil_sql.desc_1,mctrxfil_sql.lbr_hrs, mccrwfil_sql.emp_no, mccrwfil_sql.lbr_grd, sfordfil_sql.item_no
    FROM mctrxfil_sql
    LEFT OUTER JOIN sfordfil_sql ON sfordfil_sql.ord_no=mctrxfil_sql.ord_no
    LEFT OUTER JOIN hzctrfil_sql ON hzctrfil_sql.dept=mctrxfil_sql.dept AND hzctrfil_sql.wc=mctrxfil_sql.wc
    LEFT OUTER JOIN hzcdefil_sql ON hzcdefil_sql.hz_cd=mctrxfil_sql.dept AND hzcdefil_sql.cd_type='DP'
    LEFT OUTER JOIN mccrwfil_sql ON mccrwfil_sql.job_no=mctrxfil_sql.job_no AND mccrwfil_sql.ord_no=mctrxfil_sql.ord_no AND mccrwfil_sql.path_no=mctrxfil_sql.path_no
                                AND mccrwfil_sql.oper_no=mctrxfil_sql.oper_no AND mccrwfil_sql.oper_seq_no=mctrxfil_sql.oper_seq_no AND mccrwfil_sql.pln_act_cd=mctrxfil_sql.pln_act_cd
                                AND mccrwfil_sql.trx_dt=mctrxfil_sql.trx_dt AND mccrwfil_sql.control_no = mctrxfil_sql.control_no
    CROSS JOIN compfile_sql
WHERE mctrxfil_sql.pln_act_cd = '1' AND mctrxfil_sql.rec_type = 'O'
        AND mctrxfil_sql.trx_dt BETWEEN @from AND @to
        AND mctrxfil_sql.dept  = 'MFG'
        AND mccrwfil_sql.emp_no IN (@Employee)

Answer 1

I've gone for SQL solution here as without knowing what version of everything you have it's tricky to give a working answer. I've based this on SQL2008 (NOT R2) so it will work on that or above.

This approach will mean the report will have to be resigned but it should be very straight forward as most of the work is done on the SQL side.

In this example, I've just built a temp table (#t), dumped some values in it and then run a query on that to get the results. The main part that does the calcs is two SELECTs UNION'd together one part calculates the data by employee/date and the next part does the employee totals.

If you decide to implement this then you can replace the references to dbo.#t with your query, however I personally would take the results of your query and dump them into a temp table as I did in the example, it just makes it a little clearer (IMHO).

First, check the output of this SQL Fiddle and make sure it works as expected http://sqlfiddle.com/#!3/9eecb7/2070/0

Assuming it looks OK (Ignore the date format, I'm in the UK) then do the following, and forgive me if this is dumbed down a bit but I don't know your experience level.

In your report, change your query by wrapping the whole thing in a SELECT.. INTO and then adding the bit that does the calculations immediately after, like this...

SELECT o.* INTO #t
FROM
(
  **your entire original query here**
) as o

SELECT DISTINCT -- this part does the emp/date totals
t.emp_no, t.trx_dt
, SUM(case t.labor_type WHEN 'INDIRECT' THEN t.lbr_hrs ELSE 0 END) over(PARTITION BY t.emp_no, t.trx_dt) AS InDirect -- sums indirect hours
, SUM(case t.labor_type WHEN 'DIRECT' THEN t.lbr_hrs ELSE 0 END) over(PARTITION BY t.emp_no, t.trx_dt) AS Direct -- sums direct hours
, SUM(case t.labor_type WHEN 'INDIRECT' THEN t.lbr_hrs ELSE 0 END) over(PARTITION BY t.emp_no, t.trx_dt) / SUM(t.lbr_hrs) over(PARTITION BY t.emp_no, t.trx_dt) AS InDirectShare -- sums indirect hours then divides by total
, SUM(case t.labor_type WHEN 'DIRECT' THEN t.lbr_hrs ELSE 0 END) over(PARTITION BY t.emp_no, t.trx_dt) / SUM(t.lbr_hrs) over(PARTITION BY t.emp_no, t.trx_dt)   AS DirectShare -- sums direct hours then divides by total
FROM dbo.#t t
UNION ALL 
SELECT DISTINCT -- this section is as above but with the date partitioning removed
t.emp_no, NULL
, SUM(case t.labor_type WHEN 'INDIRECT' THEN t.lbr_hrs ELSE 0 END) over(PARTITION BY t.emp_no) AS InDirect
, SUM(case t.labor_type WHEN 'DIRECT' THEN t.lbr_hrs ELSE 0 END) over(PARTITION BY t.emp_no) AS Direct
, SUM(case t.labor_type WHEN 'INDIRECT' THEN t.lbr_hrs ELSE 0 END) over(PARTITION BY t.emp_no) / SUM(t.lbr_hrs) over(PARTITION BY t.emp_no) AS InDirectShare
, SUM(case t.labor_type WHEN 'DIRECT' THEN t.lbr_hrs ELSE 0 END) over(PARTITION BY t.emp_no) / SUM(t.lbr_hrs) over(PARTITION BY t.emp_no)   AS DirectShare
FROM dbo.#t t

You might want to add a sorting column in the final bit to help you sort the results in the report. I haven't included that in this example but maybe just a 0 AS SortOrder in the first section and a 1 as SortOrder in the second part. You could then sort the report by SortOrder then Date

Hope this helps, and if any of it is unclear let me know and I'll do my best to explain...