Progress Report Builder - Unknown Values

Yahoo Archive
1
Calvin,

Not so ... Changing the display attributes to show a zero when a value is
unknown does not change the contents of the variable --- just the way it
displays. Same effect as a edit word. ( Suppressing leading zeros for
display purposes does not insert spaces into the internal value of the
variable. )

If you use the variable in a calculation the value is still unknown even
though it will display as a zero. To actually change the internal value of
the variable you have to use the if statement "IIF(old-var = ?,0,old-var)".
Which then negates the reason to change the display value because since the
internal value is a zero the display value will also be a zero.

Makes perfect sense doesn't it ? ... Of course, "Help" wasn't any ...

Todd Anderson
J. Rubin & Co.

-----Original Message-----
From: Calvin Dekker [mailto:calvin@...]
Sent: Wednesday, June 14, 2000 6:44 AM
To: vantage@egroups.com
Subject: Re: [Vantage] Progress Report Builder - Unknown Values



Todd -

You can also double click the field in the report design view and set the
field's property for unknown value to 0. This will bypass the need for IF
statements in the report and may speed things up if there are many
'unknown' values in calculated or aggregate fields.

Calvin
2
Trivia,

I've was working on a report builder report in progress yesterday where I
had a primary table being joined to a secondary table as a left outer join.
In other words - include all records from table "A" and any records from
table "B" if they exist - which they may not.

Problem was that I wanted to use one of the numeric values from table "B" in
calculation with fields from table "A".

When ever you have a record from table "A" with no match from table "B" the
variables in table "B" show as a "?" - unknown value.

For my purposes what I wanted was for the "B" variables to default to ZERO
for purposes of calculation. Otherwise the entire formula defaults to
unknown.

This seemed so easy - I was not expecting to have to actually think about it
...

Here is how you handle this ...

In your calculation use "Ytd-Bal = IIF(OpenBalance = ?,0,OpenBalance) +
Tot-Deb + Tot-Crd " ...

Translation : If the variable OpenBalance is unknown because the record
does not exist then FORCE its value to ZERO, else leave the variable alone.
Then add that number to Tot-Deb and Tot-Crd ... etc ...

This also works for situations where you want to SUM a column of numbers -
create a calculated field "New-Var = IIF(OldVar = ?,0,OldVar)" and then
print New-Var on your report and auto-sum on New-Var instead of OldVar.

This is probably in the category of who cares but I found it amusing ...
Yes, I know I'm strange ...

Todd Anderson
J. Rubin. & Co.
3
Actually Todd, this information is quite useful for the less experienced
report builder users and answers many of the "Why didn't this work?"
questions that I see on the list.
Regards
Jim Stetter

-----Original Message-----
From: Todd Anderson [mailto:tanderson@...]
Sent: Wednesday, September 13, 2000 10:09 AM
To: 'vantage@onelist.com'
Subject: [Vantage] Progress Report Builder - Unknown Values




Trivia,

I've was working on a report builder report in progress yesterday where I
had a primary table being joined to a secondary table as a left outer
join.
In other words - include all records from table "A" and any records from
table "B" if they exist - which they may not.

Problem was that I wanted to use one of the numeric values from table "B"
in
calculation with fields from table "A".

When ever you have a record from table "A" with no match from table "B"
the
variables in table "B" show as a "?" - unknown value.

For my purposes what I wanted was for the "B" variables to default to ZERO
for purposes of calculation. Otherwise the entire formula defaults to
unknown.

This seemed so easy - I was not expecting to have to actually think about
it
...

Here is how you handle this ...

In your calculation use "Ytd-Bal = IIF(OpenBalance = ?,0,OpenBalance) +
Tot-Deb + Tot-Crd " ...

Translation : If the variable OpenBalance is unknown because the record
does not exist then FORCE its value to ZERO, else leave the variable
alone.
Then add that number to Tot-Deb and Tot-Crd ... etc ...

This also works for situations where you want to SUM a column of numbers -
create a calculated field "New-Var = IIF(OldVar = ?,0,OldVar)" and then
print New-Var on your report and auto-sum on New-Var instead of OldVar.

This is probably in the category of who cares but I found it amusing ...
Yes, I know I'm strange ...

Todd Anderson
J. Rubin. & Co.



We no longer allow attachments to files. To access/share Report Files,
please go to the following link: http://www.egroups.com/files/vantage/
(Note: If this link does not work for you the first time you try it, go
to www.egroups.com, login and be sure to save your password, choose My
Groups, choose Vantage, then choose Files. If you save the password, the
link above will work the next time you try it.)




[Non-text portions of this message have been removed]
4
Hi Todd

Concerning your trivia, sadly, I enjoyed it! Anytime you're in the UK you
must come to dinner, you'd fit in particularly well with the regulars.

Regards Chris Reed


----- Original Message -----
From: Todd Anderson <tanderson@...>
To: 'vantage@onelist.com' <vantage@egroups.com>
Sent: Wednesday, September 13, 2000 3:09 PM
Subject: [Vantage] Progress Report Builder - Unknown Values


>
> Trivia,
>
> I've was working on a report builder report in progress yesterday where I
> had a primary table being joined to a secondary table as a left outer
join.
> In other words - include all records from table "A" and any records from
> table "B" if they exist - which they may not.
>
> Problem was that I wanted to use one of the numeric values from table "B"
in
> calculation with fields from table "A".
>
> When ever you have a record from table "A" with no match from table "B"
the
> variables in table "B" show as a "?" - unknown value.
>
> For my purposes what I wanted was for the "B" variables to default to ZERO
> for purposes of calculation. Otherwise the entire formula defaults to
> unknown.
>
> This seemed so easy - I was not expecting to have to actually think about
it
> ...
>
> Here is how you handle this ...
>
> In your calculation use "Ytd-Bal = IIF(OpenBalance = ?,0,OpenBalance) +
> Tot-Deb + Tot-Crd " ...
>
> Translation : If the variable OpenBalance is unknown because the record
> does not exist then FORCE its value to ZERO, else leave the variable
alone.
> Then add that number to Tot-Deb and Tot-Crd ... etc ...
>
> This also works for situations where you want to SUM a column of numbers -
> create a calculated field "New-Var = IIF(OldVar = ?,0,OldVar)" and then
> print New-Var on your report and auto-sum on New-Var instead of OldVar.
>
> This is probably in the category of who cares but I found it amusing ...
> Yes, I know I'm strange ...
>
> Todd Anderson
> J. Rubin. & Co.
>
>
>
> We no longer allow attachments to files. To access/share Report Files,
please go to the following link: http://www.egroups.com/files/vantage/
> (Note: If this link does not work for you the first time you try it, go
to www.egroups.com, login and be sure to save your password, choose My
Groups, choose Vantage, then choose Files. If you save the password, the
link above will work the next time you try it.)
>
>
5
Todd -

You can also double click the field in the report design view and set the
field's property for unknown value to 0. This will bypass the need for IF
statements in the report and may speed things up if there are many
'unknown' values in calculated or aggregate fields.

Calvin