Visual Studio 2008 Report Designer (complex general ledger)

I'm combining reports to create a single general ledger report, currently it consists of 84 seperate reports. My idea is the end user will have a drop down for the department and month. These are my columns:

Account Number Account Description Current Period Actual

YTD Actual YTD Budget YTD Budget Variance

Total YR Budget Account Status

I have most of it figured out, but can't understand how to figure YTD Actual and YTD Budget since these will require a Sum of multiple Fields depending on what month and department is selected.

My where statement goes something like this and takes care of the current period actual and account number:

Where ( gl_master.acct_cde = gl_master_comp_v.acct_cde ) and
( gl_master.budget_officer = budget_off_mstr.budget_officer ) and
( ( gl_master_comp_v.acct_comp1 = '01' ) AND
( budget_off_mstr.budget_officer IN (@BudgetOfficer) ) ) AND ((@Month = 1 AND gl_master.post_bal_mon_1) OR (@Month = 2 AND gl_master.post_bal_mon_2)...

How can I have the query recognize what needs to be put into the column when there are multiple fields being summed.

Thanks for any insight. If you made something like this before a small sample of it would be very helpful.

-------------Problems Reply------------

I worked it out.

It needs to be done in a calculated field within the dataset. Then a sum can be done on the field in the textbox.

small chunk of calculated field:

=Cdec(Switch(Parameters!Month.Value = 1, Fields!post_bal_mon_1.Value,
Parameters!Month.Value = 2, Fields!post_bal_mon_2.Value + Fields!post_bal_mon_1.Value,
Parameters!Month.Value = 3, Fields!post_bal_mon_3.Value + Fields!post_bal_mon_1.Value + Fields!
Parameters!Month.Value = 4, Fields!post_bal_mon_4.Value + Fields!post_bal_mon_1.Value + Fields!
post_bal_mon_2.Value + Fields!post_bal_mon_3.Value,...))

textbox for sum(I place this in footer):

=SUM(Fields!post_bal_mon_1.value, "DataSet1")

Category:visual studio Views:0 Time:2011-08-18

Related post

Copyright (C), All Rights Reserved.

processed in 0.060 (s). 11 q(s)