BusinessObjects Board

Now function for aging report

I need to build a universe for an invoice aging report. I have figured out how to use the timestamp function to compare two date fields, but I need this report to use today’s date. I can’t seem to find any info about how to use the now() function.

This is what I used to figure out how long it took for an invoice to be paid.
timestampDiff(3, @select(Invoice Information\Received Date), @select(Payment Information\Check Date))/24

Now I want to find the time difference between the invoice received date and today. Any help in the information design tool?? I am using a relational connection to an Oracle database. I don’t seem to be able to use the sysdate function referenced in most of the forum strings I’ve seen.

Thank you so much!


bobanna02 (BOB member since 2012-05-15)

Hi,

Use Oracle function SYSDATE. That will you give you the current date&time.


Marek Chladny :slovakia: (BOB member since 2003-11-27)

Do you use it in a measure or dimension or something? I’ve tried and it says the sql is invalid when I tried in a dimension. I am really new to this tool and really appreciate your help.


bobanna02 (BOB member since 2012-05-15)

Hi,

A universe object defined with just SYSDATE in the select part will not parse because it does not reference any table. However, it can be used without a problem :slight_smile:


Marek Chladny :slovakia: (BOB member since 2003-11-27)

So, I tried creating a dimension with just SYSDATE in the select clause.

As you said I got this error:
The query does not reference any table when attempting to build the WHERE clause. (WIS 00022)

When I add it to a query to test, I get this error:
Error:
[] <com.businessobjects.dsl.commons.exception.NestedException: [Data Federator Driver] [Server] The column name ‘SYSDATE’ could not be found in any of the tables or subqueries of the FROM clause>

Cause of Error:
[Data Federator Driver] [Server] The column name ‘SYSDATE’ could not be found in any of the tables or subqueries of the FROM clause


bobanna02 (BOB member since 2012-05-15)

Hi,

The SYSDATE object can’t be used alone in a query. It must be used with any other normal object that does reference a real table in its definition.


Marek Chladny :slovakia: (BOB member since 2003-11-27)

Is there any chance you could give me an example of how to do what you’re talking about?


bobanna02 (BOB member since 2012-05-15)

There is an explanation in the Designer FAQ here:

Basically you can create an object like this, and it won’t parse, but as long as you include it in a query with a regular object that does reference a table then it should work fine.


Dave Rathbun :us: (BOB member since 2002-06-06)

I got it to work using a derived table. I can’t figure out how to get it to calculate the days between today and invoice receipt in the derived table. I get this error:
[Data Federator Driver] [Server] Types ‘TIMESTAMP’ and ‘TIMESTAMP’ are not compatible for operator ‘-’

I’ve italicized the field that is giving me problems. Any more help from you wonderful helpers?

SELECT
“AP”.“AP_INVOICES_ALL”.“INVOICE_ID”,
“AP”.“AP_INVOICES_ALL”.“INVOICE_RECEIVED_DATE”,
now()- “AP”.“AP_INVOICES_ALL”.“INVOICE_RECEIVED_DATE”,
now() SYS_DATE
FROM
“AP”.“AP_INVOICES_ALL”
WHERE
( “AP”.“AP_INVOICES_ALL”.“PAYMENT_STATUS_FLAG”=‘N’ )


bobanna02 (BOB member since 2012-05-15)