Advanced Expression?

I'm creating a report that looks through detail records and want it to sum
amounts based on a record type. If FieldType = 1 to sum accounts in a range
otherwise to sum all records with that account. The result is an error
[BC30201] Expression expected. The formula I have so far is:
=iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
db.owner.table with (nolock) where Fields!account between
Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
I've also tried VB's Select/Case statement rather than IIF but it still
returns an error.
If this can't be done with expressions, can it be done someother way?
Thanks,
RickThis seems you might want to try creating a stored procedure and using the sp
as the basis of your report, not trying to calc that in a report cell. You
can't use a select statement from within the iif statement.
"rickp3131" wrote:
> I'm creating a report that looks through detail records and want it to sum
> amounts based on a record type. If FieldType = 1 to sum accounts in a range
> otherwise to sum all records with that account. The result is an error
> [BC30201] Expression expected. The formula I have so far is:
> =iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
> db.owner.table with (nolock) where Fields!account between
> Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
> I've also tried VB's Select/Case statement rather than IIF but it still
> returns an error.
> If this can't be done with expressions, can it be done someother way?
> Thanks,
> Rick|||Mike,
Thanks for your reply. Are you referring to creating a stored procedure in
SQL or does RS have the ability to create stored procedures as well, like in
Report properties.custom code?
Since my post I came to the same conclusion you mention that what I'm trying
to do won't work in the reporting cell so I've been looking into creating a
function using custom code but as you mention, the select statement can't be
used within IIF. Can a stored procedure be called from IIF?
As a general custom code question, can C as well as VB code be used?
Thanks again!
Rick
"mike" wrote:
> This seems you might want to try creating a stored procedure and using the sp
> as the basis of your report, not trying to calc that in a report cell. You
> can't use a select statement from within the iif statement.
> "rickp3131" wrote:
> > I'm creating a report that looks through detail records and want it to sum
> > amounts based on a record type. If FieldType = 1 to sum accounts in a range
> > otherwise to sum all records with that account. The result is an error
> > [BC30201] Expression expected. The formula I have so far is:
> >
> > =iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
> > db.owner.table with (nolock) where Fields!account between
> > Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
> >
> > I've also tried VB's Select/Case statement rather than IIF but it still
> > returns an error.
> >
> > If this can't be done with expressions, can it be done someother way?
> >
> > Thanks,
> > Rick|||You can try an SQL user defined function returning a table with summary
details, then use the IIF statement to get more specific, or you can use a
"Where" statement in your dataset
"rickp3131" wrote:
> I'm creating a report that looks through detail records and want it to sum
> amounts based on a record type. If FieldType = 1 to sum accounts in a range
> otherwise to sum all records with that account. The result is an error
> [BC30201] Expression expected. The formula I have so far is:
> =iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
> db.owner.table with (nolock) where Fields!account between
> Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
> I've also tried VB's Select/Case statement rather than IIF but it still
> returns an error.
> If this can't be done with expressions, can it be done someother way?
> Thanks,
> Rick