Converting a Crystal Reports function 1

[pk400i]

In Crystal Reports, you can specify fields that should be summarized. This creates a new field that holds the summary or also can do max value of all fields and holds that. This summary you would place on the detail line and could surpress the detail so you would only have the summary.

I am interested to do this in SSRS, how is this done?

 

[RiverGuy]

SSRS works with expressions. So let's say you have a detail section and a single group section. You can definitely hide the detail section. Just select the entire row and set the Visibility property.

Since SSRS is expression-based, in your group section, you input expressions into the textboxes. If your summary is a sum, your expression would look like =SUM(Fields!YourField.Value), whereas in the details section it would simply look like =Fields!YourField.Value. Note that you can also use other forms summarization: =COUNT(Fields!YourField.Value) for instance.

 

[RiverGuy]

I should also mention that while SSRS is expression-based, there are several areas of functionality which overlap with Crystal's formula functionality.

In addition to what I outlined aboved, you can add calculated fields to your dataset itself. So instead of typing =Fields!Field1.Value + Fields!Field2.Value + Fields!Field3.Value and =SUM(Fields!Field1.Value + Fields!Field2.Value + Fields!Field3.Value), you can create a calculated field with the expression: =Fields!Field1.Value + Fields!Field2.Value + Fields!Field3.Value and then reference it in your report like =Fields!MyCalculatedField.Value and =SUM(Fields!MyCalculatedField.Value). This has the potential for more accuracy (your expression is stored in just one place) and can save you a bit in development time.

Another overlap area is adding custom code. You can add Visual Basic functions to manipulate values (let's say for example you simply want to multiply a field by two). You can create a function to accept a value as input and return another value as output. So your function would look like the following:

Function NewValue(ByVal SomeValue As Integer) As Integer
  Return SomeValue * 2
End Function

You would then call the function as follows: =Code.NewValue(Fields!MyField.Value). Of course, you might not want to waste your time creating functions for a simple multiplication, but when it comes to more complex business rules, custom code and functions are a great tool to encapsulate your logic.

 

[pk400i]

Thank you very much RG, this is very helpful.

Would there also be a way for getting the max value of a set?

We have several reports that show this.

 

[RiverGuy]

You're welcome. Yes, MIN and MAX are valid aggregate functions.