Function to lookup row in dataset?

Is there a function to lookup a row in a dataset? Say, find the row where
column x is equal to 10?
Find(Fields!ProductNumber.Value, 10, "Category")
--
McGeeky
http://mcgeeky.blogspot.comNo, but what is it that you are trying to do. There might be another way to
accomplish it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"McGeeky" <anon@.anon.com> wrote in message
news:usbEceldFHA.3452@.TK2MSFTNGP10.phx.gbl...
> Is there a function to lookup a row in a dataset? Say, find the row where
> column x is equal to 10?
> Find(Fields!ProductNumber.Value, 10, "Category")
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
>|||I will be returning a small data set with a known number of rows in it. I
don't want to display the data in a table but rather selectively pick values
out of the data set and display them in various parts of my report.
--
McGeeky
http://mcgeeky.blogspot.com
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:#5jSf8ldFHA.3184@.TK2MSFTNGP15.phx.gbl...
> No, but what is it that you are trying to do. There might be another way
to
> accomplish it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "McGeeky" <anon@.anon.com> wrote in message
> news:usbEceldFHA.3452@.TK2MSFTNGP10.phx.gbl...
> > Is there a function to lookup a row in a dataset? Say, find the row
where
> > column x is equal to 10?
> >
> > Find(Fields!ProductNumber.Value, 10, "Category")
> >
> > --
> > McGeeky
> > http://mcgeeky.blogspot.com
> >
> >
> >
>

Function call in Dataset Query

Hello Guys,

I have a question that seems easy but I can not figure out...

Premise:

Have Custom code that fixes Divide by Zero Errors in SSRS. I have added the code to the Custom Code area in Report Properties correctly.

I have a Dataset that has a calculation for a column within a select statement

Query Pseudocode:

select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))...
,year
from

(subquery"blah" )

Union

(Subquery"blah")

Custom Code:

Public Function SafeDiv(ByVal numerator as Double, ByVal denominator as Double) as Double
if denominator = 0 then
return 0
else
return numerator/denominator
end if
End Function

How To use:

If you have a field that does division and you need to eliminate the divide by zero error that occurs with SSRS then type =code.SafeDiv(first,second) in the field.

Problem:

How do I add this code reference in the following dataset select statement

select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))...
,year
from

(subquery"blah" )

Union

(Subquery"blah") table1

---

I tried to do this:

from this:

[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income)) ...

to this

[FRC%]=code.Safediv(convert(decimal(13,2),sum(cost)),convert(decimal(13,2),sum(income))) ...

---

But it did not work...gave me this error:

TITLE: Microsoft Report Designer

An error occurred while executing the query.
Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous.

ADDITIONAL INFORMATION:

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

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=4121&LinkId=20476

BUTTONS:

OK

---

Help!

P.S.

this is a Matrix report and this select statement is within one of the datasets that fill a matrix.

anyone...?|||

Hello,

Unfortunately, you can't use custom code in your SQL query (as you've found). What you can do is supply both fields in the calculation (cost and income) to the report and have it do the percentage, or create a 'SafeDiv' function in SQL and do it there.

Hope this helps.

Jarret

|||

On the DataSet

Use Generic Query Designer

Then you can use

="Select tableName.ProductID, "& code.Safediv(Parameters) & " as ColumnName

From tableName"

Try to adapts it to your report.

I hope it help you.

|||Thanks I will try it...|||

I think this will work...I will reply with result...

Thank You!