Function in select statement

How can I put a function in a select statement such as

SUM(code.GetValue( A, B, C, D, E)) AS TC_Reserve

I want to pass the function several values and have it perform a complex formula

and return a value. And then sum the value returned for each row.

This data is then used to create a chart "Dollars by Product Category"

Is this possible. I've only been at this for a week so I have no idea if it can done.

I get the message below

===============================================================

TITLE: Microsoft Report Designer

Could not generate a list of fields for the query.
Check the query syntax, or click Refresh Fields on the query toolbar.

ADDITIONAL INFORMATION:

Cannot find either column "code" or the user-defined function or aggregate "code.GetValue", or the name is ambiguous. (Microsoft SQL Server, Error: 4121)

It isn't possible to use a function that you've created in your report in a SQL statement. If you give an in-depth explanation of what you are trying to accomplish there may be a workaround I could help you with.

Another alternative is you could create a User Defined Function (UDF) and store it in your SQL Server database or you can create a stored procedure. Then you could reference it from a Select statement.

See this link for an intro to UDFs

http://msdn2.microsoft.com/en-us/library/ms179545.aspx

See this link for an intro to Stored Procedures

http://msdn2.microsoft.com/en-us/library/ms187451.aspx

|||

Thanks for the offer so here goes:

I need to pass

sales data (25 comma seperated value) - create an array (split function works great)

date of first activity

quantity on hand

factor (.59, .80. 1.00, blank, etc)

units (1.00, 60.0, blank)

months of sales to use ( 6 or 12)

function code (1 or 2)

cost (9999.9999)

what I forgot 1 (I'm sure I left 1 or 2 out)

what I forgot 2

=====================================================================

If function code = 1 then

if the date of first activity is < 365 days from today's date then

return 0.0

else

if the sum of the first 12 values in the sales data array = 0 then

return ( (qoh * (units * cost) ) * .90 )

else

if qoh > the sum of the first 6 values in the sales data array then

return ( (qoh * (units * cost) ) .50)

else

return 0.0

end

If fuction code = 2 then same as above but multiple the return value by the factor value

example: return ( ( (qoh * (units * cost) ) * .90 ) * factor )

Some of the data is string used as numeric values so it has to tested and converted to number.

Some of the string data could be blank so it has to tested and a default inserted.

Thanks for your efforts.

Note: Don't spend a lot of time on this it's not a required part of the report. I can do the basic report as above but I wanted to insert a chart (jazz it up and great learning experience) and I needed to have all the values done in the query so I can call it as a " jump to report". I got everything to work except the sum(function)) part. If I replace it with something like "sum(QOH) as TC_Reserve" it works great just the data is not correct.

|||

I think I understand what you want to do. This blog post has some details on a workaround. It's basically a custom aggregate hacked into Reporting Services:

http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

Let me know if this will work for you or you have some questions.

|||You may want look into Calculated Fields. You could use one based on an expression, which calls your custom code. Calculated field expressions allow you to access other field values--they will be the values from the current row.

In Report Designer, to add a calculated field right-click on the Data Set, and choose Add.... In the Add New Field dialog enter the name for the field, choose Calculated field, and then enter the expression for the Calculated Field. In your case, the expression would look something like what you mentioned. Aggregates are not supported in Calculated field expressions, though.

Your query would not have a reference to this calculation, so you would simply remove it from the field list.

Ian