User defined object instead of Defining variable???

I’ve written a variable within a report from Universe (as per attachment) but the report takes a long time to switch between tabs & add/remove columns etc due to the report having to recalculate the below variable following each activity. To this effect is it possible to write such a variable as a User Object on my local machine so the calculations are run on the server and not my PC?

I’ve had a quick look but I can’t see the correct function to use and hence am not sure if it’s even possible…?

below is the variable defined…

= If Between (“00:00:00” ,“00:29:59”) Then “00:00 to 00:30”
Else If Between (“00:30:00” ,“00:59:59”) Then “00:30 to 01:00”
Else If Between (“01:00:00” ,“01:29:59”) Then “01:00 to 01:30”
Else If Between (“01:30:00” ,“01:59:59”) Then “01:30 to 02:00”
Else If Between (“02:00:00” ,“02:29:59”) Then “02:00 to 02:30”

I appriciate if any body could help in defining above as user defined object.
Thanks
Vikas


vikasc (BOB member since 2003-06-20)

You could certainly create a dimension object in the universe using the SQL statement CASE WHEN…, for example:

CASE 
WHEN TableName.TopicReceivedTime Between ('00:00:00', '00:29:59')
   THEN '00:00 to 00:30' 
WHEN TableName.TopicReceivedTime Between ('00:30:00' ,'00:59:59')
   THEN '00:30 to 01:00'
ELSE 'undefined'
END

The exact syntax varies depending on the DBMS (Oracle, MS SQL Server, IBM UDB, etc.)

If you are looking for a UDO (User Defined Object) solution within Business Objects Reporter let us know, and a moderator will move this topic.


Andreas :de: (BOB member since 2002-06-20)

As some extra info on Andreas’ reply:

Most databases do support conditional calculations. For example, Microsoft SQL Server, IBM DB2 and Teradata use the ‘Case’ statement; Oracle uses the ‘Decode’ function (also ‘Case’ I believe from version 8i and so on); and Microsoft Access uses the ‘iff’ statement.

Case When example:

The syntax for the Case statement is as Andreas already stated:

CASE 
WHEN TableName.Columnname = Value1 
   THEN 'Output1' 
WHEN TableName.Columnname = Value2
   THEN 'Output2' 
ELSE 'Output3' 
END

If the Columnvalue is equal to Value1, then display as “Output1” else etc.

Oracle Example Decode

ORACLE also supports a commonly used function known as decode, which has the following syntax: decode(field,value-to-test,value-if-true,value-if-false)

A simple example is:

decode(TableName.Columnname, 'Value1', 'Output1', 'Value2', 'Output2', 'Output3')

English interpretation:

If the Columnvalue is equal to Value1, then display “Output1” else if the Columnvalue is equal to value2, then display “Output” and display other values for Tablename.Columnname as “Output3”.

You can ‘Nest’ your Decodes in one and another, but: Multiple conditions are complex to write!

The decode statement in ORACLE suffers from a number of limitations:

  • Decode can only positively test for a single condition. Multiple conditions require nested decodes but as I already said: they are complex to write.
  • Only an ‘equality’ test is supported - it is not possible to test for greater than or less than, between, etc.

Microsoft Access Example
A functionally similar calculation to decode is supported by Microsoft Access:

iff(condition,value-if-true,value-if-false)

The following calculation is similar to the ORACLE example, and derives the calculation ‘iff_example’:

iff(Tablename.Columnname = 'Value1', 'Output1', iif(Tablename.Columnname = 'Value2', 'Output3')) 

English interpretation:

If the Columnvalue is equal to Value1, then display “Output1” else if the Columnvalue is equal to value2, then display “Output” and display other values for Tablename.Columnname as “Output3”.

The iff calculation (or was it ‘iif’?) suffers from the same limitations of the ORACLE decode I guess.

These are the only ones I know, Good luck!


jobjoris :netherlands: (BOB member since 2004-06-30)

I’m curious to know whether a deeply nested CASE statement on the universe/database side has its own drawbacks.

I have address fields that are very granular (a separate field for every possible component of the address: street number, street name, street direction, unit type, unit number, etc.). As a universe developer, I want to concatenate all of that in a nice formatted object where the CASE statement conditionally adds spaces and commas and the like, but I wonder what the downside is.

Anyone have a better solution?


nunemaj (BOB member since 2004-06-29)

Downside would be that most likely database indexes cannot be used against your new universe object (which uses concatenation etc.).

If you can modify the ETL process to provide you with a column for address in the format that is needed for most reports (not suitable in an OLTP environment though).


Andreas :de: (BOB member since 2002-06-20)