Computed Fields/Multiple Datasources

I am getting started with Reporting Services.
I am having trouble with computed fields. I have two issues:
1. I have a special dataset that I use to read parameters from a
database table. This table has only one row. I would like to add a
computed field that divides one of the columns in this row by another.
I created a computed field called ProRatedMultiplier with the following
definition:
=Fields!OPERATINGDAYSINMONTH.Value/Fields!INVOICINGDAY.Value
The problem is that when I insert this computed field into my report,
it wants to sum the output:
=Sum(Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS")
If I remove the SUM() function, I get compile errors.
I need to use this computed value in many places on the report and thus
would like to have the formula defined only once (instead of repeating
it in each field).
It seems SRS sees it as returning one or more rows and thus wants to
aggregate. Am I going about this the wrong way? Can I make any changes
to make this work?
2. Assuming I get the above to work, I will have this new computed
field on my dataset. I would like to create a computed field on another
dataset that uses this first computed field value.
This new, second computed field would multiply the first computed field
value (which is a pro-rata multiplier) by the sum() aggregate of a
column in the second dataset.
The definition would like something like this:
=SUM( Fields!INVOICEAMOUNT.Value) *
Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS"
This also will not compile.
Am I going about this the wrong way?For #1:
Use the First aggregate function:
=First(Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS")
So even if your InputDataDS dataset has more than one rows (for whatever
reasons), you will still get the expected result. Since you use the
calculated field value in another dataset, you cannot omit the aggregate
function (otherwise the fields collection would be scoped to the wrong
dataset).
For #2:
You could write an expression in a textbox like this:
=SUM( Fields!INVOICEAMOUNT.Value) * First(
Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS")
However, you cannot define a calculated field which uses aggregate
functions.
Probably a better solution for #1 & #2:
Since you have only 1 row - did you look into hidden textboxes, which
calculate the formulas instead of calculated fields? You could then
reference the value of the formula textbox in other textboxes by using an
expression like
=ReportItems!FormulaTextbox.Value
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hunter Hillegas" <hunter.hillegas@.gmail.com> wrote in message
news:chqjea$pq0@.odak26.prod.google.com...
> I am getting started with Reporting Services.
> I am having trouble with computed fields. I have two issues:
> 1. I have a special dataset that I use to read parameters from a
> database table. This table has only one row. I would like to add a
> computed field that divides one of the columns in this row by another.
> I created a computed field called ProRatedMultiplier with the following
> definition:
> =Fields!OPERATINGDAYSINMONTH.Value/Fields!INVOICINGDAY.Value
> The problem is that when I insert this computed field into my report,
> it wants to sum the output:
> =Sum(Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS")
> If I remove the SUM() function, I get compile errors.
> I need to use this computed value in many places on the report and thus
> would like to have the formula defined only once (instead of repeating
> it in each field).
> It seems SRS sees it as returning one or more rows and thus wants to
> aggregate. Am I going about this the wrong way? Can I make any changes
> to make this work?
> 2. Assuming I get the above to work, I will have this new computed
> field on my dataset. I would like to create a computed field on another
> dataset that uses this first computed field value.
> This new, second computed field would multiply the first computed field
> value (which is a pro-rata multiplier) by the sum() aggregate of a
> column in the second dataset.
> The definition would like something like this:
> =SUM( Fields!INVOICEAMOUNT.Value) *
> Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS"
> This also will not compile.
> Am I going about this the wrong way?
>