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.
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.
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
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
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.
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’ )