How to define Ad hoc Report Expression Library
1.
Navigate to report administration
2.
Search for “Define Ad Hoc Reporting Library”.
3.
Click on New Row
Expression Library Details
Fill The information as per Table
|
Filed Name
|
Value
|
|
Expression Id:
|
Datediff
|
|
Description:
|
This custom function. Used this to find out difference between two
given date. Replace the field <Filed> with date/timestamp. Please Use Data type as Decimal.
|
|
Expression
|
days (date((<field>))) - days (date((<field>)))
|
|
|
|
|
Expression Id:
|
DayOverDue
|
|
Description:
|
This custom function. Used to find difference between current date
and given date. Replace the field <Filed> with date/timestamp. Please Use Data type as Decimal.
|
|
Expression
|
days (&DATETIME&) - days (date((<field>)))
|
|
|
|
|
Expression Id:
|
Timestampdiff
|
|
Description:
|
This custom function. Used to find difference between two timestamps.
Replace the field <Filed> with date/timestamp. Please Use Data type as Decimal.
In place of <n>, use one of the following values to indicate
the unit of time for the result:
1 = Fractions of a second
2 = Seconds
4 = Minutes
8 = Hours
16 = Days
32 = Weeks
64 = Months
128 = Quarters
256 = Years
Using timestampdiff() is more accurate when the dates are close
together than when they are far apart.
|
|
Expression
|
timestampdiff (<n>,
char(timestamp(<Field>)-timestamp(<Field>)))
|
Calculating Difference
between dates In Adhoc reporting
Introduction: -
Using the calculation on fly we can save lot number of hours
while reporting to client. In this document, we will cover two case one where
will find number of day’s between actual Finish and actual start date. In 2nd
case will we find number of day overdue since ticket is reported.
Note:- This is valid only for DB2 if you use Oracle and SQL
process will change. In DB2 when we subtract date it gives time in Microsecond
which is of no use.
Case 1:- DatedIff
1.
Navigate to Incident application or application
of your desire.
2.
Click on “Create Report”
3.
Select desired attribute, once done click on
Calculate Tab.
4.
Click on Expression lookup
Click on Expression lookup
5.
It will open Pop-up from that Select option “Datediff”
6.
Once you have selected you will get expression
as below.
7.
Replace the <Field> value with date filed
which you want to subtract. In this case we will do with “Actual Finish”
–“Actual Start”.
8.
Change data type from date to Decimal, and give Name
value which you want to display on report.
Ex. In progress time.
9.
Click on Test Expression , to make sure it do
not have any error. Then click on Add
repression.
10.
Save and Run Report
Case 2:- DatedIff
1.
Navigate to Incident application or application
of your desire.
2.
Click on “Create Report”
3.
Select desired attribute, once done click on
Calculate Tab.
4.
Click on Expression lookup
Click on Expression lookup
5.
It will open Pop-up from that Select option
“DayOverDue”
6.
Once you have selected you will get expression
as below.
7.
Replace the <Field> value with date filed
which you want to subtract. In this case we will do with “Todays date” –“Actual
Start”.
8.
Change data type from date to Decimal, and give
Name value which you want to display on report.
Ex. In progress time.
9.
Click on Test Expression , to make sure it do
not have any error. Then click on Add
repression.
10.
Save and Run Report
Case-3 Timestampdiff
1.
Navigate to Incident application or application
of your desire.
2.
Click on “Create Report”
3.
Select desired attribute, once done click on
Calculate Tab.
4.
Click on Expression lookup
Click on Expression lookup
5.
It will open Pop-up from that Select option “timestampdiff()”
6.
Once you have selected you will get expression
as below.
7.
Replace the <Field> value with date filed
which you want to subtract.
In place
of <n>, use one of the following values to indicate the unit of time for
the result:
1 = Fractions of a second
2 = Seconds
4 = Minutes
8 = Hours
16 = Days
32 = Weeks
64 = Months
128 = Quarters
256 = Years
8.
Change data type from date to Decimal, and give
Name value which you want to display on report.
Ex. In progress time.
9.
Click on Test Expression , to make sure it do
not have any error. Then click on Add
repression.
10.
Save and Run Report
