Inserting a large formula into Adding a User Object

building reports desktopintelligence
1

I am trying to perform a series of calculations (when I did this in M.S. Access, I was able to run a series of nested queries).

As a new Bus. Obj. user and designer, I am able to insert a column into a report and use the EDIT FORMULA option. An example of such a formula is shown below and it works fine.

=ToNumber(Concatenation(SubStr(FormatNumber(Year(),"#"),4,1) ,If Length((FormatNumber(Week(),"##"))) =1 Then( Concatenation(“0” ,(FormatNumber(Week(),"##")))) Else (FormatNumber(Week(),"##"))))

The point, however, is not this formula. Since I have more calculations to perform on the result of the above formula, I thought I could go to TOOLS -
UNIVERSE - USER OBJECTS and ADD a user object containing the above formula. However, when I do so,I get "error, bad character (not a function name). "

While in that same ADD USER OBJECT window, I have tried defining the object as dimension - character; dimension - number, measure - number, and measure - character–all sorts of combinations, but none of them work. I have also removed the initial ‘=’ from the formula in the formula window as well as leaving it in===neither of these adjustments work/

Does the User object window only work for a single function? I suppose I could keep adding more functions to the above to make a very BIG formula, but I’m sure there must be an alternative.

Any suggestions would be very welcome.

–Beverly

Listserv Archives (BOB member since 2002-06-25)

2

Beverly,

I’m not sure what the limit is, but I know that we have some very large and complex formulas that, in NotePad, fill over a page. All this is in one variable or object.

Michael Welter
Sr. Technical Analyst
AirTouch

Does the User object window only work for a single function? I suppose I could keep adding more functions to the above to make a very BIG formula, but I’m sure there must be an alternative.

Listserv Archives (BOB member since 2002-06-25)

3

In a message dated 00-03-01 12:47:31 EST, you write:

As a new Bus. Obj. user and designer, I am able to insert a column into a
report and use the EDIT FORMULA option. An example of such a formula is shown below and it works fine.

=ToNumber(Concatenation(SubStr(FormatNumber(Year(),“#”),4,1) ,If Length((FormatNumber(Week(),“##”)))
=1 Then( Concatenation(“0” ,(FormatNumber(Week(),“##”)))) Else (FormatNumber(Week(),“##”))))

The point, however, is not this formula. Since I have more calculations to perform on the result of the above formula, I thought I could go to TOOLS

UNIVERSE - USER OBJECTS and ADD a user object containing the above formula. However, when I do so,I get "error, bad character (not a function name). "

There is an important distinction between VARIABLES and OBJECTS. Variables can be calculated on the report, using reporter functions. Those functions, however, are often different from what you find in your database. An OBJECT must be valid SQL.

By the way, instead of using the Concatenation() function, try using the & symbol instead. It would make your formulas more understandable.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)