Where are the functions in Reporting? 1

[olitvinov]

As I remember from Crystal reports, there is a generic functions area. Where one would be able to create a new function and use it as a data in any field of the report if necessary.

Whereas in Business Intiligence studio there are expressions available for existing fields, but no place to put a generic function.

Am I missing something or functions are just non-existing?

Thanks,
Oleg

 

[MDXer]

This what you are looking for?
[URL unfurl="true"]http://blogs.digineer.com/blogs/jasons/archive/2006/08/18/374.aspx[/url]

 

[RiverGuy]

I probably did not explain clearly enough when I answered your previous post.

1. Go to the layout tab of your report if you're not already there.

2. There is a little square near the upper left corner, right between the two rulers. Click on that square. A little black square should appear. Right-click that black square and choose "properties."

3. Choose the "Code" tab. This is where you can enter generic functions to be used in expressions. The syntax (at least for 2005 version) is Visual Basic .Net.

Here is a sample:

Function GetAmountPerUnit(ByVal Amount As Decimal, ByVal Units As Integer) As Decimal
  If Units = 0 Then
    Return 0
  Else
    Return Amount/Units
  End If
End Function

4. You can then call this function in an expression like follows:

=Code.GetAmountPerUnit(Fields!Amount.Value, Fields!Units.Value)

 

[olitvinov]

I'm getting it.
What I was interested in was having acces to Fields!SomeColumn.value inside that formula.

It seems like I can pass some simple types to Code module and write it in VB.net style, but Access to 'Fields' collection object is not available.

Therefore this function is completely have no knowledge of the report objects.

I tried the variables tab in Properties window. Added a variable and went to add a formula. Inside the formula Fields collection doesn't exist. Actually, when I try using Fields in there message: 'Report item not linked to a dataset' shows up. may be there is a solution for that as well.

Does anybody have an example where someone passes collection to the VB.NET 'Code' area?

To RiverGuy:
While this formula

 =IIF(Parameters!SomeParameter.Value = 'Some Value', SUM(Fields!SomeField.Value), SUM(Fields!SomeField2.Value))

is working, how do you reuse it in another expression field other than copying and pasting?

Thanks,
Oleg

 

[RiverGuy]

To RiverGuy:
While this formula
CODE
=IIF(Parameters!SomeParameter.Value = 'Some Value', SUM(Fields!SomeField.Value), SUM(Fields!SomeField2.Value))
is working, how do you reuse it in another expression field other than copying and pasting?

I assume what you're meaning here is using the same aggregate in different grouping levels. I haven't found a way to avoid the copying and pasting. If you're using SSRS 2008, I'm not sure if there is new functionality for this. However, for most reports, this usually isn't much of an issue unless you're dealing with a large number of columns and a large number of groupings.