Financial Expression - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Financial Expression

Author

Topic

mp01
Starting Member

11 Posts

Posted - 2005-02-07 : 13:28:51

I'm trying to write an expression that will calculate the combined value of two stock fund accounts. The following fields are involved:

(1) Fields!AccountNumber.Value -- The account number
(2) Fields!fund_Value.Value -- The value of an individual account.

Here's the expression I'm working with which results in the "...contains an error: [BC30205] End of statement expected." error:

=(Fields!AccountNumber.Value = "1" AND "2"), Sum(Fields!fund_Value.Value)

The following expression does result in the correct amount for a single stock fund account:

Sum(Fields!fund_Value.Value)

Any assistance would be appreciated.

Thanks,
Mark

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-07 : 13:33:21

So you should group on AccountNumber then, then have Sum(Fields!fund_Value.Value) for that grouping.

Tara

mp01
Starting Member

11 Posts

Posted - 2005-02-07 : 14:54:34

I've already created a group and it works. I need to combine the fund values of accounts 1 and 2.

The following expression returns an error:

=(Fields!AccountNumber.Value = "1" AND "2"), Sum(Fields!fund_Value.Value)

How do I combine the values of multiple accounts?

Mark

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-07 : 14:57:58

I don't understand your expression. Could you provide an example of what the report looks like and what you want it to look like? It sounds like you need a top level grouping though.

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-02-07 : 15:44:58

Try using a if expression.

=iif(Fields!AccountNumber.Value = "1" AND "2"), Sum(Fields!fund_value.value), "")

mp01
Starting Member

11 Posts

Posted - 2005-02-07 : 16:57:32

ChrisKahn2000's expression resulted in the following error:

The value expression for the textbox 'txtAccountTotalValueHeader' refers to the field 'fund_value'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Here's an example of the report I'm trying to create. There are many accounts involved. In some cases accounts have to be combined, as in the case of accounts 1 and 2.

The "Total" column is calculated with the following expression:
Sum(Fields!fund_Value.Value)

What expression should I use to calculate the "Value of 1 & 2" column?

Acct
Account Symbol Value Total Value
of 1 & 2

1 $300 $1,100
AAA $100 $400
Bbb $200 $600
CCC $300 $400

2 $800
Aaa $300
Bbb $400
Ccc $100

3 $300
Aaa $100
Bbb $100
Ccc $100

Thanks,
Mark

mp01
Starting Member

11 Posts

Posted - 2005-02-07 : 17:06:19

The formatting of the sample report in the previous post is jumbled. Here's a second attempt.

Here's an example of the report I'm trying to create. There are many accounts involved. In some cases accounts have to be combined, as in the case of accounts 1 and 2.

The "Total" column is calculated with the following expression:
Sum(Fields!fund_Value.Value)


What expression should I use to calculate the "Value of 1 & 2" column?

Account			Value	 
		Symbol	Accts 	Total	Value
					of 1 & 2

1				$300	$1,100
		AAA	$100		$400
		Bbb	$200		$600
		CCC	$300		$400

2				$800
		Aaa	$300
		Bbb	$400
		Ccc	$100

3				$300
		Aaa	$100
		Bbb	$100
		Ccc	$100

Thanks,
Mark

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-07 : 17:12:26

You would need another grouping. So you'd need a top level grouping that is before Account.

Tara

mp01
Starting Member

11 Posts

Posted - 2005-02-07 : 17:57:43

Thanks Tara and ChrisKahn2000.

mp01
Starting Member

11 Posts

Posted - 2005-02-08 : 14:22:02

I need help creating the new top level group.

A new top level group was created which is based on the following expression:

=Fields!AccountNumber.Value = "1" AND "2"

In the table's grouping header I tried using several expressions, one of which is the following:

Sum( Fields!fund_Value.Value)

The result is the total value for account 1.

How do I calculate the combined values of accounts 1 and 2?

Mark

mp01
Starting Member

11 Posts

Posted - 2005-02-10 : 12:25:17

I made some progress with the top level group.

Using the following group, the correct value of all acccounts can be calculated:
FundValue Group based on: Fields!fund_Value.Value

The following expression will calculate the correct value of all acccounts:
Sum( Fields!fund_Value.Value)

I'm not able to calculate the value of only two accounts using the following expression:
Iif(Fields!AccountNumber.Value = "1" AND "2"), Sum( Fields!fund_Value.Value), "N/A"

The above expression results in the "...Argument not speci#64257;ed for parameter 'FalsePart' of Public Function..."

What expression should be used?

Thanks,
Mark

jhermiz

3564 Posts

Posted - 2005-02-10 : 16:55:56

u cant do "1" AND "2"

you have to say:

IIF(Fields!AccountNumb.Value=1 AND Fields!AccountNumb.Value=2, ...., ....)

Btw, check out your ('s )'s
they arent correct.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

mp01
Starting Member

11 Posts

Posted - 2005-02-13 : 21:35:50

Thanks jhermiz. The cause of the error was a missing set of parenthesis.

The following expressions result in the correct value of all acccounts:

(Iif(Fields!AccountNumber.Value = 1 AND 2), Sum( Fields!fund_Value.Value), "N/A")

(Iif(Fields!AccountNumber.Value = "1" AND "2"), Sum( Fields!fund_Value.Value), "N/A")

Sum(Fields!fund_Value.Value)

Sum(iif(Fields!AccountNumber.Value=11 AND 5,Fields!fund_Value.Value, Fields!fund_Value.Value))

I'm still not able to calculate only the value of accounts 1 and 2.

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources