Showing posts with label ssrs. Show all posts
Showing posts with label ssrs. Show all posts

Monday, March 19, 2012

FUNCTIONS

I just installed SSRS 2005 and I have experience with SQL.

How come this function does not work?

SELECT SUBSTRING(YEAR_MONTH, 1, 2) AS Expr1
FROM table1

I get a message which states that this command is not supported by the provider?

It works fine with other SQL tools like winsql?

thanks

Refer to the responses to your identical post in the Transact_SQL forum.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

Friday, March 9, 2012

Function Reference

Where can I get reference documentation to all the functions that can be used in SSRS cells and expressions. I cant seem to find it in the help.

Regards

Frik

It's listed under Using Expressions in Reporting Services.

http://msdn2.microsoft.com/en-us/library/ms159673(en-US,SQL.90).aspx

-Lukasz


This posting is provided "AS IS" with no warranties, and confers no rights.

|||

In addition to the built-in RDL functions, you can use any object / function from the .NET framework which is available within the following namespaces: System, System.Convert, System.Math, and Microsoft.VisualBasic.
A reference of available expression functions in the VB runtime library is available at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp

-- Robert

Function Reference

Where can I get reference documentation to all the functions that can be used in SSRS cells and expressions. I cant seem to find it in the help.

Regards

Frik

It's listed under Using Expressions in Reporting Services.

http://msdn2.microsoft.com/en-us/library/ms159673(en-US,SQL.90).aspx

-Lukasz


This posting is provided "AS IS" with no warranties, and confers no rights.

|||

In addition to the built-in RDL functions, you can use any object / function from the .NET framework which is available within the following namespaces: System, System.Convert, System.Math, and Microsoft.VisualBasic.
A reference of available expression functions in the VB runtime library is available at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp

-- Robert

Wednesday, March 7, 2012

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!