none
Visual Studio 2005 Matrix and adding calculated fields RRS feed

  • Question

  • I'm trying to make essentially a crosstab report like you can make in Excel but in Visual Studio for sql reporting services.

    My sql output looks like this
    Theatre	Type	Amount	Amountly	WTDAmount	WTDAmountly	WeekBudget	MTDAmount	MTDAmountLA	MTDBudget
    theatre2	Attendance	1859	499	18794	9945	20904	48108	32763	45446
    theatre2	Conc Trans Count	595	NULL	6539	NULL	NULL	17033	NULL	NULL
    theatre2	Net Admission	11876.85	2861.11	121164.81	58372.69	137895.86	318818.51	191218.09	299794.52
    theatre2	Net Concession	6528.75	1503.65	64461.37	30616.42	68983.53	162679.42	106000.9	149974.67
    theatre2	Pass Count	37	17	379	172	NULL	1046	506	NULL
    theatre2	Payroll	1888.9892	NULL	11452.8259	NULL	15727.84	29134.4792	NULL	39737.52
    theatre2	Payroll Hours	189.35	NULL	1578.42	NULL	NULL	4015.18	NULL	NULL
    theatre3	Attendance	1696	347	16277	7567	13794	37707	24666	31466
    theatre3	Conc Trans Count	522	NULL	5500	NULL	NULL	12734	NULL	NULL
    theatre3	Net Admission	9881.65	1766.97	95671.56	41728.45	85906.96	228993.58	133754.58	195967.47
    theatre3	Net Concession	5483.4	1131.8	54114.63	22607.11	43726.13	124132.76	76213.39	99746.28
    theatre3	Pass Count	22	21	168	158	NULL	471	473	NULL
    theatre3	Payroll	1365.0709	NULL	8901.8662	NULL	12777.12	21597.3858	NULL	31284.67
    theatre3	Payroll Hours	152.37	NULL	1207.77	NULL	NULL	2877.5	NULL	NULL
    theatre1	Attendance	981	367	15827	9368	13766	44574	29270	35922
    theatre1	Conc Trans Count	331	NULL	5239	NULL	NULL	14643	NULL	NULL
    theatre1	Net Admission	5363.55	1951.4	89386.44	52825.71	79747.64	261151.86	162931.78	208104.01
    theatre1	Net Concession	3749.36	1311.71	55721.05	30283.91	47354.71	151614.3	99185.54	123573.63
    theatre1	Pass Count	31	5	205	104	NULL	691	350	NULL
    theatre1	Payroll	1431.9663	NULL	9936.9052	NULL	12442.66	26559.679	NULL	33688.44
    theatre1	Payroll Hours	130.52	NULL	1371.49	NULL	NULL	3671.45	NULL	NULL
    theatre4	Attendance	386	248	10675	5544	6467	30306	16877	19901
    theatre4	Conc Trans Count	125	NULL	3587	NULL	NULL	10200	NULL	NULL
    theatre4	Net Admission	2438.34	1335.8	69285.92	32774.6	43090.97	202297.94	97963.05	132598.44
    
    I made a matrix report with the theatre name and type as rows and the various amounts as columns which is fine. What excel allows you to do is make additional calculated rows.

    What I need is something like [net admission] / attendance in a new row called "average net per ticket". I've searched all over and can't find how to do this.

    One of the things I find confusing is the matrix wizard makes you define fields for rows and columns AND data. Excel only asks for rows and columns. The wizard won't let you continue until you enter a field for all three. So after the wizard is done, I drag theatre name from the column to a row.

    What I'm looking for is
    Theatre1
             Attendance  {all the amount fields }
             Net Admittance {all the amount fields}
             "Average Net Per Ticket" = Net Admittance / Attendance

    I have about 8 rows of calculated fields that I need to show somehow.

    Thanks in advance.
    Tuesday, May 26, 2009 9:58 PM