Avatar of vasureddym
vasureddym
 asked on

MS Reporting Services IIF expression and MS SQL Server Database Money datatype

Hi All,

The textbox is using the following expression
= sum(iif( Fields!Opportunity_Type.Value = "Renewal", Fields!DT.Value, 0.00))

Previously in the database when the column DT is of type FLOAT, this expression was working fine. But after changing it to datatype MONEY it is throwing the following error:
The value expression for the textbox ‘textbox51’ uses an aggregate function on data of varying data types.  Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.

I know that I need to convert the value 0.00 to datatype money; a sort of type casting. But how?

I tried making the 0.00 to 0 but didn't work. Even $0 failed

Any little help is also appreciated.

Thanks and Regards,
Srinivas
DB Reporting Tools

Avatar of undefined
Last Comment
Yurich

8/22/2022 - Mon
vasureddym

ASKER
Hey
I found the solution. use the following code.. .. ..

= sum(iif( Fields!Opportunity_Type.Value = "Renewal", Fields!DT.Value, System.Decimal.Parse(0)))

i guess that this actually convert to the decimal datatype.. but this is working;
ASKER CERTIFIED SOLUTION
Yurich

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
vasureddym

ASKER
Hi Yurich,

Thanks very much for your help.. You'll get the points.

Can you let me know if you have resource that lists all these functions?

Cheers,
Srinivas
Yurich

huh, it might be somewhere in the books online but i couldn't find it easily... so that's how I found ;)

go anywhere to "Expressions" - right click -> Expression in any table cell, or in any control. Then, in the bottom-left window extend "Common Functions", click "Conversion" and there you'll find all conversion functions available in Reporting Services.

Regards,
Yurich
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
vasureddym

ASKER
oops.. I'm using Reporting Services with SQL Server 2000. I guess that why i don't see what you see..

when I right click -> Expression in any table cell, I don't see "Common Functions" in the left window. Actually I see only two windows Fields and Expression. Under fields, I see "Constants", "Globals", "Parameters", "Fields", "Datasets"

Cheers,
Srinivas
Yurich

hello,

that's from the msdn site:

Conversion Functions
You can use Visual Basic functions to convert data types as needed in your report.

You can use Visual Basic functions to convert data types as needed. Convert functions are frequently used to eliminate or format #Error messages in a report. The following expression displays the number of values selected for the multivalue parameter MySelection.

=CStr(Parameters!MySelection.Count)

here is the link (the stuff about conversions somewhere in the middle of the article):
http://msdn2.microsoft.com/en-us/library/ms157328.aspx

and for the vb conversion functions you can check out this links:

http://msdn2.microsoft.com/en-us/library/1bbh5ae4.aspx
http://www.w3schools.com/vbscript/vbscript_ref_functions.asp

good luck,
yurich
Marcus Aurelius

NICE...thanks you helped me outta a bind....just wanted to say THANKS...I've been racking my brain trying to figure this out.....

M
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Yurich

No worries ;) You're welcome :)