Help with NULLs in calulated field

[furrelkt]

I am fairly new to Actuate. I am working on a report that has 12 fields (ETC1-ETC12) I want to add them together, then subtract ETC. Only problem is that there are NULLS in say ETC7-ETC12 because no time is recorded yet. How do i write either a SQL code or Function to handle the NULL values. Also how can i make it so there is a variable called [Var]and it is the SUM of those ETC1-ETC12 fields. Thanks so much.

 

[gdwood]

Use the SUM function to sum these just like you would witha number. SUM should ignore NULL.

You can add a control to the report for the SUM and then override the BuildFromRow method to return a SUM

Dim Var as (sting, variant, date, etc...whatever you need)
Var = SUM(field values)
me.datavalue = Var

If you need me to elaborate let me know. this is the basics

 

[furrelkt]

okay i made a variable called Var, and in the BuildOnRow, i have this,

Function BuildFromRow( row As AcDataRow ) As AcBuildStatus
BuildFromRow = Super::BuildFromRow( row )
Dim Var as double
Var = SUM()[ETC1] + [ETC2] + [ETC3] + [ETC4] + [ETC5] + [ETC6] + [ETC7] + [ETC8] + [ETC9] + [ETC10] + [ETC11] + [ETC12]) - ETC)
me.datavalue = Var
End Function

what else do i need to add? What does the me.datavalue mean?

Did i do right by making another variable and calling it Var? Thanks so very much.


Keri

 

[furrelkt]

oops, i had ()after SUM but i changed it around, i am getting this error:
NewReportApp::Frame::VarControl%BuildFromRow%AcDataRow(4): Actual: '[' Expecting: '(', '+', '-', [currency constant],
[floating constant], [hexa constant], ID, [A-Z], [date constant],
[integer constant], Null, [octal constant], [string constant], ::, BNot,
ID@, ID#, ID%, ID&, ID!, ID$, New, Not, Nothing, Width

1 Error(s) found

 

[gdwood]

cut and paste the code from the onRow please, sounds like you're missing a parenthesis.

me.datavalue is assigning the datavalue of your expression to the text box.

 

[gdwood]

And you will probably want something like:

Var = SUM(Row.GetValue("ETC1")+Row.GetValue("ETC2")+Row.GetValue("ETC3")......)

You have the field names listed above as they would appear in the ValueExp property, you have to code like the example above to actually get the value for the expression.

 

[furrelkt]

Okay here is my code for the OnRow, thanks so much for your help!

Sub OnRow( row As AcDataRow )
Super::OnRow( row )
Dim Var as double
Var = SUM(Row.GetValue("ETC1")+Row.GetValue("ETC2")+Row.GetValue("ETC3")+
Row.GetValue("ETC4")+Row.GetValue("ETC5")+Row.GetValue("ETC6")+
Row.GetValue("ETC7")+Row.GetValue("ETC8")+Row.GetValue("ETC9")+
Row.GetValue("ETC10")+Row.GetValue("ETC11")+Row.GetValue("ETC12"))
Var = Var - ("ETC")
me.datavalue = Var

End Sub


I am getting this error:
NewReportApp::Frame::VarControl%OnRow%AcDataRow(4): Illegal variable use. - (SUM)
1 Semantic Error(s) found

 

[gdwood]

OK change then + to commas, maybe it doesn't like that.

If that doesn't work, elimiate the SUM function and just add up the values.

Then make sure you subtract the Row.GetValue("ETC") as well

 

[furrelkt]

Hey there gdwood, well i got it all in and took out the SUM but now it doesnt display anything because in the ETC7-ETC12 there is going to be NULLS or Nothing cause there is no time there yet. How do i now account for the nulls? here is the OnRow function:

Sub OnRow( row As AcDataRow )
Super::OnRow( row )
Dim Var as double
Var = Row.GetValue("ETC1")+Row.GetValue("ETC2")+Row.GetValue("ETC3")+
Row.GetValue("ETC4")+Row.GetValue("ETC5")+Row.GetValue("ETC6")+
Row.GetValue("ETC7")+Row.GetValue("ETC8")+Row.GetValue("ETC9")+
Row.GetValue("ETC10")+Row.GetValue("ETC11")+Row.GetValue("ETC12")
Var = Var - Row.GetValue("ETC")
me.datavalue = Var

End Sub


the value exp property is only [Var] is that okay?

thanks,
K

 

[gdwood]

take out the VAR in ValueExp, it's not necessary, and it's not a database field, so you don't want it there. If that doesn't fix it then:

try the SUM(Row.GetValue("ETC1"), Row.GetValue("ETC2"),Row.GetValue("ETC3")......) replacing the + sign with commas?

Checking for nulls in each of these fields would be a pain.

 

[furrelkt]

WEll, now unforgunately i am really confused. I need to start over... I have 12 controls they are [ETC1-ETC12.], i want to make another control called [var] not a database field just an computed field that adds all the [ETC1-ECT12]together and then subtracts the [ETC] field. So i made a control called VarControl and in the ValueExp i have this:

Sum(( [ETC1] + [ETC2] + [ETC3] + [ETC4] + [ETC5] + [ETC6] + [ETC7] + [ETC8] + [ETC9] + [ETC10] + [ETC11] + [ETC12] ) - [ETC])

Then i need to check for NULLS because the [ETC1-ETC12]are based on time (weeks) so if i select a time like 6/1/05, the ETC1-ETC6 will be filled but the ETC7-ECT12 will be NULL. So the question is how to account for the NULLS because it returns a blank field. If i just had
Sum(( [ETC1] + [ETC2] + [ETC3] + [ETC4] + [ETC5] + [ETC6]) - [ETC]) Then it works fine so i believe the problem is that there are NULLS in the fields, correct?

 

[furrelkt]

gdwood,
okay i spoke too soon, it did work now its not... so i am confused. so let me know if you think you might have a solution for my report. thanks so much.

 

[gdwood]

Sum should ignore nulls.

what happens when you use this in the ValueExp:

[ETC1] + [ETC2] + [ETC3] + [ETC4] + [ETC5] + [ETC6] + [ETC7] + [ETC8] + [ETC9] + [ETC10] + [ETC11] + [ETC12] - [ETC]

It errors out, or gives you the wrong total?

Of course this does not define the variable for you.

To check for null you can do IsNull(Row.GetValue("ETC1")) in the OnRow. You can think about adding these up in the select statement and then assigning them a name like VAR and displaying that

And is this in the content or is the sum

This is one of the drawbacks of this kind of support, I don't have the rod or data so i can't troubleshoot on my desktop.

 

[furrelkt]

I think that i am more confused, i did have what you had above and gave me no errors but it didnt print anything in the field either. its just blank where there should be the addition of the ETC1-ETC12 fields minus the ETC.

So i am not sure where to go from here.

 

[furrelkt]

How can i declair the sum of ETC1-12 as a variable in the ql code ?

 

[gdwood]

How do you create the SQL? Are you writing it, or using the query editor?

It would be something like:

Select
A.ETC1 + A.ETC2 + A.ETC3...... - A.ETC AS VAR
From
Schema.table A

If you are using temp tables, you have to bind and declare VAR as a column and whatever type of variable you want

If you want me to look at the ROD, you can email it to me.

 

[furrelkt]

textual query, and using tables and views, no temp tables at least i dont think so. I have taken the report writing over and am very new.

 

[gdwood]

Are these oracle or db2 tables? And can you run the SQL outside the report and make sure it's working with some other tool like sql plus or qmf for windows?

 

[furrelkt]

yes it works fine when taking out the parameters. i just needed to enhance it.

 

[furrelkt]

gdwood, did you receive my rod file? thanks,