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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

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

Yurich

No worries ;) You're welcome :)