Custom Code Calculation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Custom Code Calculation

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-29 : 14:45:31

What's wrong with this custom code:

Public Shared Function HoursUsed(Fields!Textbox29.Value as String) AS String
If (Fields!Textbox29.Value IS Nothing) OR (Fields!Textbox29.Value = "0") Then
Return = "0"
Else
Return = ((Fields!Textbox15/Fields!Textbox29) * 100)
End If
End Function

Also in my text field where I display this custom code, I have it set like this.

=Code.HoursUsed

Not sure if this is right. Please advise.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-29 : 14:50:58

You can't use your fields like you are doing. You'll pass in the their values and put them into variables then deal with the variables. Let me show you an example of what I have:

Public Function DisplayMessage(ByVal startDate As Date, ByVal endDate As Date) As String
DisplayMessage = ""
If startDate > endDate Then
DisplayMessage = "End date must be greater than or equal to start date." & vbNewLine
End If
End Function

The above is a custom function that I have embedded in a few reports.

Here is the call to it from a cell:

= Code.DisplayMessage(Parameters!StartDate.Value, Parameters!EndDate.Value)

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-29 : 15:13:52

How would I do the calculation though? Do I use a variable for the 2 fields that I'm trying to calculate? My custom code doesn't seem to work. Also how would I call this since it's not a parameter. All i'm doing here is check if Textbox 15 or 29 is Null or has 0. If it does put 0 in there else do the calculation.

Public Shared Function HoursUsed(ByVal Hours as String) AS String
Dim ActHours AS String
Dim EstHours AS String

ActHours = Fields!Textbox15.Value
EstHours = Fields!Textbox29.Value

If (Hours IS Nothing) OR (Hours = "0") Then
Hours = "0"
Else
Hours = ((ActHours/EstHours) * 100)
End If
End Function

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-29 : 15:20:06

I'm going to get you started on this so that you learn how to do it rather than me doing this for you:

Public Shared Function HoursUsed(ByVal a As String, ByVal b As String) AS String
...play with a and b here, they now contain the values
End Function

Then:

= Code.HoursUsed(Fields!Textbox15.Value, Fields!Textbox29.Value)

Field values can be put anywhere that a parameter value can.

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-29 : 15:33:28

When trying to call the custom code for the cell, this is the error I'm getting with by using the textbox.

The value expression for the textbox ‘textbox17’ refers to the field ‘Textbox29’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-29 : 16:19:05

Is it possible to just use an conditional expression? For example:

=iif(Fields!Textbox29.Value IS Nothing, "0", iif(Fields!Textbox29.Value = "0", "0", "(Fields!Textbox29.Value/Fields!Textbox15.Value) * 100"))

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-29 : 16:24:31

There's gotta be a way to add one cell with another is there?

Like Textbox1 + Textbox2 in the cells. When I try to add one cell to another I'm getting

"Report items expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."

This is all gibberish to me since I'm fairly new at Reporting Services.

jhermiz

3564 Posts

Posted - 2004-12-29 : 16:27:21

Do this:


=IIF(Fields!txtBox.Value IS NOTHING OR Fields!txtBox.Value="0", "0", Fields!txtBox.Value/Fields!txtBOx2.Value * 100)

Place this as a calculated control on your report. You can define calculated controls on the right side pane of the report.

Jon

A new beat on the web -- http://www.web-impulse.com

jhermiz

3564 Posts

Posted - 2004-12-29 : 16:32:50

Sorry not the right side of the pane but the left :), forgot how vs.net placed these things.

Jon

A new beat on the web -- http://www.web-impulse.com

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-29 : 16:44:39

Was just going to ask you where hehehe. But even after adding in the field as a caculated control, I'm still geting the same error.

"Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."

This is so easy to do in Oracle Reports, but Reporting Services is totally a new beast to tackle with.

jhermiz

3564 Posts

Posted - 2004-12-29 : 16:49:11

Is this what is happening ? I mean are you referring to fields from within the query you are reporting on ???
I just created a basic report with that same if with no problems. I am using the current data source so I did not recieve this error.

Make certain you are not referring to fields that are not within the data set.

A new beat on the web -- http://www.web-impulse.com

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-29 : 16:53:02

What I'm trying to do is calculate text boxes and not fields that's in my dataset. Like TextBox1 + Textbox2. I seem to be not able to do this so not sure if this is a feature not supported by Reporting Services.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-29 : 17:04:13

I think I know what's going on now. It seems like I have to calculate the fields within my dataset first. Then use that calculated field to do some more caculation .

For example in my dataset I did this.

Fields!EstimatePrice.Value - Fields!ActualPrice.Value

WHich is name as PriceDifference.

Now when I do my calculated control I use:

=iif(Fields!PriceDifference.Value = "0", "0", Fields!EstimatePrice.Value/Fields!PriceDifference.Value * 100)

What I was trying to do before this is trying to just add the output result. Like I already have to textbox that has the output I want and I want to just calculate them together.

Like: Textbox1/Textbox2 * 100, which is what I'm used to.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-29 : 17:11:18

Thanks Tara and Jon. I finally figure it out. I realized that I can't calculate the output of one textbox to another, but I can calculate the fields from the dataset.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources