Skip navigation
Share:|

Smart Reporting 1902 release has now capability to leverage entire set of RDBMS functions and it gives ability to achieve complex calculations and use-cases which were not possible in previous releases. More details can be found here

Speaking of Use-Case, One of the popular requirement was to calculate difference between two dates excluding weekends.

 

Well the wait is over now. This blog will walk you through the steps to calculate Incident Age Excluding weekends.

 

System Prerequisite :

AR Server version             - 1902 or higher

Smart Reporting version  - 1902 or higher

 

We are creating a complex formula from nesting multiple database functions and few arithmetic operation to get the results.

So I'll break it down the Native Database formula and then will see how we can define ARJDBC compliant SQL in Smart Reporting.

 

Here is the MSSQL formula (Native SQL) :

 

select cast(DATEDIFF(DD,'2019-01-01 12:00:00','2019-01-10 12:00:00')/7 as INT)*5 + 
cast(SUBSTRING('1234555123444512333451222345111234500123450123455',(DATEPART(dw,'2019-01-01 12:00:00')-1)*7 + (DATEDIFF(DD,'2019-01-01 12:00:00','2019-01-10 12:00:00')%7)+1,1) as INT)-1

 

This entire formula can be divided into 2 parts.

1 - Count Weeks

Calculate the number of weeks between dates, This can be done by (Number of Days/7) and next multiply it by 2, This gives you number of Working days in whole week.

Eg; 19/97 = 2.71

Since we need a whole number for this we can truncate decimals using Cast functions which gives me 2 whole weeks, We will deal with decimal points in next part. Now i can simply multiply this value by 5 which result number of working days in whole week. which is 10 in this case.

This calculates first piece of query -

select cast(DATEDIFF(DD,'2019-01-01 12:00:00','2019-01-10 12:00:00')/7 as INT)

 

2 - Calculate the leftover days

Now we need to calculate the leftover days which are not considered under whole week and then add it in the result of first part which gives you exact number of Working Days.

Remember we had truncated decimal places in first part, we need to get that part and convert it into leftover days, we have divided the value and truncated decimals now we need perform modulus arithmetic operation which do the division and returns remainder value added some further logic to get correct days.

 

Second part will calculate the leftovers:

 

cast(SUBSTRING('1234555123444512333451222345111234500123450123455',(DATEPART(dw,'2019-01-01 12:00:00')-1)*7 + (DATEDIFF(DD,'2019-01-01 12:00:00','2019-01-10 12:00:00')%7)+1,1) as INT)-1

At the end of formula we have subtracted 1 day to exclude current day, If you want to consider current day as well then remove (-1) arithmetic operation at the end of formula.

So consider the Dates passed in above query

Start Date - 01/01/2019

End Date  - 10/01/2019

 

Actual Date Difference - 9

Excluding Weekends - 7

 

Now lets see how we can define it in Smart Reporting

Essentially you need 4 database functions for this calculation

  1. CAST
  2. DATEDIFF
  3. SUBSTRING
  4. DATEPART

 

Make sure this functions are defined in Remedy Form and the create Freehand SQL calculated field to define these in similar order as Native Database formula with special Keyword DBFN.

 

Below is an example to calculate Difference between Submit Date & Last Resolved Date for Incidents.

 

dbfn('cast',dbfn('datediff','DD',`Submit Date`,`Last Resolved Date`)/7,'as int')*5 +

dbfn('cast',dbfn('substring','1234555123444512333451222345111234500123450123455',(dbfn('datepart','dw',`Last Resolved Date`)-1)*7 + (dbfn('datediff','DD',`Submit Date`,`Last Resolved Date`)%7)+1,1),'as int')-1

 

Here is the Output of Sample Report

Share:|

It’s now close to 4 years since Remedy Smart Reporting was first introduced as part of Remedy 9 to deliver a modern web and mobile user experience for ITSM in-app reporting. One of the big design choice we made for Remedy Smart Reporting was adhering to Remedy permission model when extracting data. This design also aligned with Smart Reporting as an in-app reporting tool and helped Customers avoid setting up parallel permission model for data extraction in Smart Reporting (eventually helping customers with faster RoI). AR JDBC was technology enabler to ensure what users see on Remedy screen is what users see in Smart Reporting reports, dashboards and so on.

 

Smart Reporting wins hands down when it comes to self-service reporting, intuitive data model, slick UI and great visualization. But as it happens often in our lives, some things are too good to be true. While underlying AR JDBC had distinct advantages when compared to data warehousing or ETL, this layer is proprietary to Remedy. Which meant, each capability in this AR JDBC is purpose built code by BMC engineers. On other hand, as users maturity rose, users started looking for complex querying capabilities in AR JDBC; something equivalent to database’s native query capabilities. However, combining database’s query capabilities with AR JDBC was not possible in our view.

 

Alternatively, we kept enhancing AR JDBC functions incrementally in Smart Reporting releases. And soon realized that R&D resources for bridging this gap between AR JDBC functions and database’s native functions were a bottleneck. We started thinking of some new approach for expanding support for functions in Smart Reporting. While ultimate aim was to enable users create advanced reports with use of complex queries, we also wanted to leverage underlying database’s query capability for this (and without compromising Remedy permission model).

 

 

What excites us now with Smart Reporting 19.02 version

 

I am happy to share that in 19.02 Smart Reporting release, you have now access to many more database’s native functions.

 

This is done with a tweak in querying in AR JDBC. In summary, you first define in AR form as which all database function you plan to use (we have done this for you). And then in Smart Reporting go ahead and use these functions with a query predecessor string ‘DBFN’. When AR JDBC sees DBFN string followed by defined database function, it will send this query straight to underlying database and retrieve results. A short and simple use case demo for this can be seen below where ‘datepart’ function for Microsoft SQL server is used for extracting ‘Year’ and ‘Week of the year’ from a date field.

 

https://youtu.be/yw2BtV-C_0s

 

More specifics of this new capability is available in Bhushan Desai's  blog post that includes list of database functions supported for Freehand SQL as well sample AR JDBC compliant SQL https://communities.bmc.com/groups/get-started-with-smart-reporting/blog/2019/02/25/database-functions-support-in-smart-reporting-with-enhanced-ar-jdbc

 

We are looking forward to you trying this new capability in 19.02 release. As always, we are welcoming your questions, comments (and appreciation ).

 

I can’t conclude this post without acknowledging Get Started with Smart Reporting that keeps us on toes with different use cases. Also, I want to acknowledge many of the technical experts at BMC who responded to these challenges and realized the ‘unthinkable’. Thanks to all of you!

 

PS: Smart Reporting 19.02 release delivers a lot more new capabilities than just the new powerful query capability in AR JDBC. Check this blog post from Suresh Kumar Palivela where he has mentioned other exciting new capabilities in Smart Reporting in 19.02 release

https://communities.bmc.com/groups/get-started-with-smart-reporting/blog/2019/02/26/whats-new-in-smart-reporting-1902

 

 

Smart Reporting 19.02 Release Notes - https://docs.bmc.com/docs/itsm1902/19-02-enhancements-841096841.html#id-19.02enhancements-BMCRemedySmartReportingenhancements

 

Remedy Suite 19.02 List of Enhancements - https://docs.bmc.com/docs/itsm1902/19-02-enhancements-841096841.html

Share:|

As part of BMC Remedy Smart Reporting 1902 release, we have embedded our OEM partner Yellowfin 8.0 version. In addition to the features offered in Yellowfin 7.3 version, the following features are added in Yellowfin 8.0 version which are available in Smart Reporting 1902 release.

BMC Remedy Smart Reporting 1902 includes Yellowfin 7.4 and Yellowfin 8.0 features.

 

 

Smart Reporting 1902 New Features/Enhancements

 

S.No.Smart Reporting 1902 / Yellowfin 8.0 FeatureDescription
1Story

New Collaboration feature with advanced interactive capabilities.

With Stories, users can share and have a dialogue about their business metrics, and provide external context that happens outside their data and human narrative without the need for external tools, such as email, or third-party documents. Stories merges visual analysis with text, so you can perform long form analysis, in a collaborative environment, by including reports, images, videos, with text. The simple UI enables you to tell a story, much as you would be writing a blog post with ease.

2Import / Export WizardUI experience for end user is changed.
3Automated Data Discovery

Automated Insights automatically provides immediate answers without the user having to dig through all their data searching for the exclusive piece of insight.

 

There are two types of insights.

Instant Insights – This is designed for End User. Advanced algorithms instantly carry out the work of expert data analysts, providing users with automated insights based on the relevance of their data. Business users will be able to use this functionality effortlessly on their charts with minimal effort. With the click of a button, they can choose to get a deeper explanation of the data they are seeing, or compare data on a chart to understand differences.

Assisted Discovery - This is designed for Report Writers. A new Smart Analysis button has been included in the report builder. This allows users to perform assisted data discovery with the help of a new Insight Wizard.

4Bookmarks and SnapshotsBookmark/Snapshots icon has been shifted to the left side of the report output page
5Conditional Formatting OptionsEnhanced the conditional formatting rules. In a single rule itself, we can apply cell background color, font color and font styles
6More number of icons in Report CanvasMore number of conditional icons added in Report builder.
7Conditional widgets on Report canvas

Create conditional rules using canvas widgets, such as charts, text, images and shapes on the canvas.

For example, we can hide charts when a rule's condition is matched.

8ViewsNavigation Changed from Admin Console to Browse section
9Tool tipsThe tool tip has been updated to display more information, and be more interactive. The interactive features include performing auto analysis, filtering content, and data drilling.

 

For detailed information about the above mentioned features, refer to the BMC documentation.

BMC Remedy Smart Reporting version 19.02 enhancements - Documentation for BMC Remedy IT Service Management Suite 19.02 -…

 

You might want to refer to the attached pdf that lists all the features available in Yellowfin 7.3.

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

 

Share:|

在MyIT安裝資料夾中找到

BMC Software\Smart_IT_MyIT\Smart_IT_MyIT\ux\previous\myitapp\scripts\app\myit-app.min.js

 

以下這串即為搜尋的語法。

function l(){var e=f.string.trim();f.nothingFound=!1,e.length>2?(f.loading=!0,g(e)):f.filteredResults=null}

 

將e.length>2改為需要的長度即可。

 

另外相關JS條件也需要修改(跳出字元不足訊息、搜尋結果跳出條件)

BMC Software\Smart_IT_MyIT\Smart_IT_MyIT\ux\previous\myitapp\scripts\views.min.js

此為JS相關文件

 

搜尋$templateCache.put("views/unified-catalog/catalog-search-directive.html")

 

後方即為相關語法

 

字數不足時跳出的訊息條件

<h4 class="catalog-search__dropdown-caption" ng-if=\"catalogSearchState.string.length >= 0 && catalogSearchState.string.length < 3\">

 

跳出搜尋結果欄位的條件

<div class="catalog-search__results" ng-if=\"!catalogSearchState.nothingFound && !catalogSearchState.loading && catalogSearchState.string.length > 2\">

 

其他相關的都放在同一行,有需要的也可以做相關修改,或是利用網頁語法檢查找出來。

 

修改相關訊息顯示,

訊息都是以placeholder顯示在跳到i18n去尋找對應的字串locale

 

BMC Software\Smart_IT_MyIT\Smart_IT_MyIT\ux\previous\myitapp\scripts\app\i18n

裡中找到自己所屬的locale,並搜尋該字串

修改後即可。

Share:|

Vyom Labs invite you to join our webinar on


Crank Up Your Contents on BMC Smart Reporting
Aug 23, 2017 | 11:00 AM CST

 

Join us for 30 minutes to learn:

  • Background on Smart Reporting & its platform
  • Analytical capabilities of Smart Reporting
  • Transition path for BMC Analytics report to Smart Reporting and challenges faced
  • Use case walkthrough
  • Answer to all your queries

Register Now

  Submit your questions


To have your question answered during the session, please submit your question prior to the webinar on ganesh.balu@vyomlabs.com

Share:|

Communities+Remedy+9+Banner.jpg

  

We are excited to announce general availability of Remedy 9.1 SP3 and Smart IT 1.6. These new releases include several valuable enhancements for our customers, in support of our Service Management Excellence vision.

 

Smart Reporting Enhancements in Remedy 9.1 SP3 (9.1.03)

 

Technology Upgrade that Offers Easy to Navigate and Use Smart Reporting UI:

  • OEM technology upgrade to the latest version - Top rated 'Idea' for Smart Reporting is now delivered!
  • Refined UI and navigation
  • New “Quick Create” button
  • New “My Content” section keeps track of your draft and published content
  • New content creation canvas allows freeform drag-and-drop creation of infographics and rich dashboards
    • Control over positioning and resizing of charts, text, labels
    • Annotate canvases with text, images and icons
  • New pop-up window gives user choice over how to proceed for long running report

 

New Content for Smart Reporting:

  • New charts and KPIs, in addition to the new Release Management reports and dashboard
  • New ability to report on hierarchical CMDB configuration item (CI) data
    • Includes new semantic view and a sample report
  • New KPI charts for Incident and Work orders
  • New Knowledge Management reports to identify trends in keyword searches and article usage

 

Smart Reporting with Enhanced Analytical Capabilities:

  • Add forecasts and trend-lines to reports and dashboards
    • Quickly add forecasts and trend lines to new or existing charts

    • Forecasting includes moving average or simple, double, and triple exponential smoothing

    • Trend lines show moving average and linear or polynomial regression

    • Drag-and-drop statistical functions onto the chart to display more visual analysis

  • Create advanced reports with new subquery/append capability
    • Combine data from separate semantic views (Problem, Incident, Change etc) into a single report
  • New rich query capability for creating 'Calculated Fields'
    • Aggregate list string values into single column
    • Manipulate and format dates for easier charting – display first day of the month, quarter, year etc
    • Return length and position of specific character or word in given string

 

Easily Embed Smart Reports in 3rd Party Portals:

  • New JavaScript API enables embedding of reports and dashboard in 3rd party portals such as SharePoint, Intranet portals, monitoring solutions, web pages and so on
  • Fully interactive experience for embedded reporting - includes drill-down, chart/data toggle and export to multiple formats

 

For complete list of Smart Reporting 9.1 SP3 enhancements, see release notes here - 9.1.03: Service Pack 3 - BMC Remedy IT Service Management Suite 9.1 - BMC Documentation

 

Also check this blog by Peter Adams for details of other enhancements as part of Remedy 9.1 SP3 and Smart IT 1.6 release - New user experience for Release Management, Smart Reporting enhancements and upgrade improvements – Remedy 9.1 SP3 and Smart IT 1.6 now available!

 

Thank you for your continued support of the Remedy family of products and we look forward to updating you on more innovative product enhancements in the coming months.

 

Kind regards

 

The Remedy team

Share:|

I have created a dashboard, no issues. Problem what I am facing is to implement filter in Dashboard? My report is containing filter in Submit Date and I want to see the filter in Dashboard also. Can anyone locate me where to get it with screenshots. Highly appreciate your advice.

 

Thanks,

Nehal

Smart Reporting Crosslaunch

Posted by Amol Kale Aug 19, 2016
Share:|

The first ever touch point for any Smart Reporting user involves logging into Smart Reporting with a crosslaunch, typically from Midtier or Smart IT. Hence it is important to understand the required configuration and functional aspects of crosslaunch to avoid your users running into error page on their initial attempts of login.

 

There are two types of crosslaunch in Smart Reporting:

 

  1. Crosslaunch from Midtier or Smart IT to login into Smart Reporting
  2. Crosslaunch from Smart Reporting (Hyperlinked Report Field) to Midtier or Smart IT

 

Lets find out more details about each of them in following sections:

 

A. Crosslaunch from Midtier or Smart IT to login into Smart Reporting

 

Smart Reporting does not have its own authentication mechanism. It uses AR Authentication to authenticate a user and then generates a token from Smart Reporting for logging in. Essentially crosslaunch automates the above procedure of generating a token and redirecting to Smart Reporting Server URL.

 

Required Configuration in Midtier and Smart IT

  1. Smart Reporting Server URL (e.g. Tomcat URL on which Smart Reporting is installed)
  2. Server Administrator (typically siadmin, as provided during installation)
  3. Administrator Password
  4. Customer Name (customer name, as provided during onboarding of AR server or ITSM company)

 

Smart Reporting and Midtier should be of same version for crosslaunch to work correctly. This restriction is removed after Midtier 9.1 Patch 1

How to Crosslaunch (Login to Smart Reporting)

 

In Remedy Midtier, crosslaunch can be done by clicking Smart Reporting > Smart Reporting Console in Applications menu.

In Smart IT, after successful configuration Reports tab will be available which will allow you to crosslaunch into Smart Reporting.

 

Common Problems & Causes

 

Area
Problem
Possible Causes and Solutions
Midtier Configuration

Midtier

Crosslaunch

Smart Reporting option is not visible in Applications menu for MidtierSmart Reporting 9.1 onwards the Smart Reporting link is visible to users only when the Smart Reporting in installed and the AR Server or ITSM company to which user is currently logged in is onboarded successfully in Smart Reporting.

Midtier

Crosslaunch

ARERR [9430] Failed to get token for Remedy Smart Reporting

OR

 

AR error [3600] internal error

  1. AR Server or ITSM company to which user is logged in is not onboarded to Smart Reporting.
  2. Logged in user does not have fixed or floating license in AR Server.
  3. Logged in user does not fit the user group criteria specified during onboarding.
  4. Midtier configuration is missing or invalid. Make sure Validate Smart Reporting information is checked and the provided configuration is validated successfully.
  5. Logged in user is a new user in AR Server, you need to wait (default, 12 hours) for the user sync job to run before user can login to Smart Reporting. Refer to User Sync configuration in Smart Reporting
  6. Smart Reporting server may not be running or not accessible from Midtier server.
  7. In case of SSL environment, the SSL certificate of Smart Reporting Server should be imported into JVM used by Smart Reporting Server.
  8. Version mismatch between Midtier and Smart Reporting, upgrade Midtier to 9.1 Patch 1 or later version.
Smart IT CrosslaunchReports tab is not visibleSmart Reporting configuration is missing or invalid.

 

 

B. Crosslaunch from Smart Reporting (Hyperlinked Report Field) to Midtier or Smart IT

 

Some OOTB Reports in Smart Reporting are formatted as hyperlinks which can open the actual ticket in Midtier or Smart IT (e.g. Incident Number field in Incident Details report). Smart Reporting 9.1 onwards this crosslaunch is intelligent enough to take you back to the application which you logged in from originally.

 

Required Custom Parameters in Smart Reporting

  1. AR (AR Server Hostname/Alias as it appears in Midtier URL. This will be populated automatically during onboarding)
  2. MIDTIER (Midtier Hostname and Port)
  3. SMART_IT (Smart IT Hostname and Port)

How to Crosslaunch

 

Before Remedy 9.1, OOTB reports would take you back to Midtier always, unless you modify the report fields and change the URL with Smart IT URL.

With Remedy 9.1, Smart Reporting will take you back to the application which you have logged in from.

 

E.g. If you have launched Smart Reporting from Smart IT, clicking on Incident Number field in Incident Details report will open Smart IT search page with that Incindent Number as search term. On the other hand if you have launched from Smart Reporting from Midtier, the Incident Management page in Midtier for that particular incident will open.

In case the logged in session for Midtier or Smart IT has expired, the user will be redirected to login page of respective application. Default behavior is to crosslaunch to Remedy Midtier.

Crosslaunch to Midtier or Smart IT works correctly only for hyperlinked OOTB Report fields which are not modified manually. It is also possible to replace this URL with specific URL to crosslaunch to any one application.

Common Problems & Causes

 

Area
Problem
Possible Causes and Solutions
Report

Error 704: Error connecting to Midtier OR

Error 704: Error connecting to Source Application

  1. Midtier or Smart IT server is down or inaccessible from Smart Reporting server
  2. In case of SSL environment, the SSL certificate from Midtier or Smart IT web server should be imported into JVM used by Smart Reporting Server
  3. The MIDTIER and/or SMART_IT custom parameters are missing or invalid
  4. Check the generated URL in browser, verify if it is of the following form, if not the URL was modified manually:

<SMART_REPORTING_URL>/onboarding/router.jsp?midtierUrl=<MIDTIER_URL>&smartitUrl=<SMART_IT_URL>&appnameUrl=<APPLICATION_URL>

ReportUpdated URL from View is not reflected in ReportThe updated URL from view is not updated automatically in reports, you need to remove the field from Report and add it back
ReportMidtier/Smart IT asks for login even when user session is activeMake sure the MIDTIER/SMART_IT hostname in custom parameter is exactly same as one accessed from browser by the users. E.g. If users access the midtier with URL http://clm-12345.org.com:8080/arsys and MIDTIER parameter is set to only clm-12345:8080, browsers will treat it as a different destination and user will have to login again.
Share:|

Ageing buckets are useful when analyzing open Tickets in the Report, It helps to identify and Prioritize Open Items,

Below steps illustrates how to create ageing buckets in Smart Reporting.

 

Prerequisites:

AR Platform ver - 9.1 or Higher

Smart Reporting ver - 9.1 or Higher

MidTier  Version - 9.1 or Higher

 

Steps:

This Example will create 3 Ageing buckets,

Bucket 1 - 1 to 30 Days.

Bucket 2 - 30 to 60 Days

Bucket 3 - Greater that 60 Days

 

  1. 1. Create Aging Bucket for Number of Incidents using Case function.

1

Click on Create on Top Nav-bar,  Click on Report

2

Search Incident Management in the view list and click on it, It directs you to Report Designer Page

3

To create Aging Bucket we need to create two calculated objects Age of incidents and Age Bucket

4

To Create New Calculated Object Click on '+' sign in bottom-Left panel , It opens a pop-up window to define the calculation called Formula builder.

5

Steps to create Age of incidents objects

Calculated Field Name

Age of incidents

Formula Type

Select Pre-Defined in the list

Functions

Select Date Difference - in Days, ARJDBC from the list

Values

 

In Values section click on Define Values in front of Start Date and Search for Submit Date and from the search drop down Select Submit Date listed under -Date and Time- and click on OK,

Now
click on define value in front of End Date and select Current Date Option and set it as Current Date + 0 Days

Click on Save

6

 

To Create New Calculated Object Click on + sign in bottom-Left panel , It opens a pop-up window to define the calculation called Formula builder.

7

Steps to create Bucket Object

Calculated Field Name

Age Bucket

Formula Type

Select Simple in the list

 

Click on Case here as you click on these buttons, in the text box above you could see how calculation is being generated.

Now Click on When It opens up a new window to define When and Then part of the case statement.

STEP 1 : Add 1 to 30 days bucket

When Part

Select Values as mentioned below
--Select Field--        = Age of incidents
--Select Operator-- = Between click on
In Blank text box    = 1 , click on ADD Then click on AND and again enter 30 into the Blank text box and click on ADD

Formula preview text box  : 
Age of incidents BETWEEN 1 AND 30

Then Part

Select Values as mentioned below
Blank text box 
    = Between 1 to 30 days
Formula Preview text box  :
'Between 1 to 30 days'

Click on Save
Formula preview text box  : CASE WHEN Age of incidents BETWEEN 1 AND 30 THEN 'Between 1 to 30 days'

STEP 2 : Add 30 to 60 days bucket

Click on WHEN , It opens up a new window to define When and Then part of the case statement.

When Part

Select Values as mentioned below
--Select Field--        = Age of incidents
--Select Operator-- = Between click on
In Blank text box    = 31 , click on ADD Then click on AND and again enter 60 into the Blank text box and click on ADD

Formula preview text box  : 
Age of incidents BETWEEN 31 AND 60

Then Part

Select Values as mentioned below
Blank text box 
    = Between 31 to 60 days
Formula Preview text box  :
'Between 31 to 60 days'

Click on Save
Formula preview text box  : CASE WHEN Age of incidents BETWEEN 1 AND 30 THEN 'Between 1 to 30 days' WHEN Age of incidents BETWEEN 31 AND 60 THEN 'Between 31 to 60 days'

STEP 3 : Add More than 60 days bucket

Click on WHEN, It opens a new window to define else part of the case when statement

Else part

Select Values as mentioned below
Blank text box 
    = More than 60 days
Formula Preview text box  :
'More than 60 days'

8

Final Formula preview text box  : CASE WHEN Age of incidents BETWEEN 1 AND 30 THEN 'Between 1 to 30 days' WHEN Age of incidents BETWEEN 31 AND 60 THEN 'Between 31 to 60 days' ELSE 'More than 60 days' END

9

Click on Save , Created object should appear in Calculated Fields category.

10

Follow below steps to create a sample report using above created objects.

11

STEP 1 :

Drag and drop Age Bucket and Number of incidents in to column section

STEP 2 :  

Drag and drop Company object from Incident Details Category into Rows section

Filter Blog

By date:
By tag: