Share:|

Smart Reporting 19.02 release added valuable enhancement in AR JDBC and users can leverage all RDBMS functions with enhanced AR JDBC.

Report writer can run and use any database function in Smart Reporting by using Freehand SQL editor for creating custom reports.

It might not be obvious to everyone which DB functions are really going to work with Smart Reporting and therefore providing some list of MS-SQL and Oracle database functions that user can execute.

 

Using direct database function in Smart Reporting - many business use case can now be achieved.

Examples -

  • Use case of getting first and last work-log entry for specific Incident.

 

Native Database SQL
AR JDBC Compliant SQL
Comments

SELECT Incident_Number,

LEAD(assigned_group, 1, 0) OVER (PARTITION BY Incident_number ORDER BY assigned_group DESC NULLS LAST) LEAD_Assigned_Group,

LAG(assigned_group, 1, 0) OVER (PARTITION BY Incident_number ORDER BY assigned_group DESC NULLS LAST) LAG_Assigned_Group

FROM aradmin.hpd_help_desk_assignment_log

WHERE incident_number='INC000000000001' and Assignment_type != 1000

ORDER BY incident_number, assigned_group DESC;

SELECT `Incident Number`,

dbfn('lead;over;partition by;order by',5,'#KWD#(',`Assigned Group`,1,0,'#KWD#)',1,'#KWD#(',1,`Incident Number`,3,`Assigned Group`,'#KWD#desc','#KWD#)') Lead_Assigned_Group,

dbfn('lead;over;partition by;order by',5,'#KWD#(',`Assigned Group`,1,0,'#KWD#)',1,'#KWD#(',1,`Incident Number`,3,`Assigned Group`,'#KWD#desc','#KWD#)') Lag_Assigned_Group

FROM `HPD:Help Desk Assignment Log`

WHERE `Incident Number`='INC000000000001' and `Assignment Type`!= 1000

ORDER BY `Incident Number`, `Assigned Group` DESC;

For this type of AR JDBC SQL - a new option Advanced Authoring is supported by Remedy Smart Reporting. After you enable this option, you can use the SQL Query editor to write database queries for creating reports.

 

  • Ranking function to provide a rank to a set of rows in a partition.

 

Native Database SQL
AR JDBC Compliant SQL
Comments

select Assignee,COUNT(Incident_Number) NumberOfInc,

RANK() over(partition by Assigned_Group  order by COUNT(Incident_Number) desc) RANK

from HPD_Help_Desk where Assignee is not null

group by Assignee

select `Assignee`,COUNT(`Incident Number`) NumberofInc,

dbfn('rank;over;order by count',2,'#KWD#(','#KWD#)',1,'#KWD#(',5,'#KWD#(',`Incident Number`,'#KWD#)','#KWD#desc','#KWD#)') RANK

from `HPD:Help Desk` where `Assignee` is not null

group by `Assignee`

For this type of AR JDBC SQL - a new option Advanced Authoring is supported by Remedy Smart Reporting.

After you enable this option, you can use the SQL Query editor to write database queries for creating reports.

 

 

 

List of database functions for Freehand SQL - 

 

SQL

Oracle

CAST

LEAD

CONVERT

LAG

ROW_NUMBER

CONCAT

RANK

REPLACE

DENSE_RANK

INSTR

NTILE

TRANSLATE

CHARINDEX

LPAD

CONCAT

MONTHS_BETWEEN

DATALENGTH

ADD_MONTHS

LEFT

LAST_DAY

LEN

EXTRACT

LOWER

NEXT_DAY

LTRIM

TO_NUMBER

REPLACE

CAST

REPLICATE

GREATEST

REVERSE

 

RIGHT

 

RTRIM

 

SPACE

 

STR

 

STUFF

 

SUBSTRING

 

TRANSLATE

 

TRIM

 

UPPER

 

DATEADD

 

DATEDIFF

 

DATENAME

 

DATEPART

 

DAY

 

MONTH

 

YEAR

 

QUARTER

 

STUFF

 

PATINDEX