10 posts

# Business Days Calculation in Smart Reporting 1902

Posted by Abhijeet Teli Mar 21, 2019
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 :

#### 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

# We proved ourselves wrong and we are happy about it!

Posted by Rahul Vedak Mar 5, 2019
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

# Reporting features introduced in Smart Reporting 1902

Posted by Suresh Kumar Palivela Feb 26, 2019
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.

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.

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

# Database Functions Support in Smart Reporting with Enhanced AR JDBC

Posted by Bhushan Desai Feb 25, 2019
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

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

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#)') 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

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.

 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

# 修改MyIT搜尋最小長度

Posted by Shih Price Nov 2, 2017
Share:|

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

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

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

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

# Crank Up Your Contents on BMC Smart Reporting

Posted by Ganesh Balu Aug 10, 2017
Share:|

Aug 23, 2017 | 11:00 AM CST

• 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

# Remedy 9.1 SP3 and Smart IT 1.6 now available - Smart Reporting includes new out-of-the-box reports, dashboard and KPIs, a new forecasting and trend-line capability, sub-query capability for advanced reports and a UX refinement

Posted by Rahul Vedak Jun 12, 2017
Share:|

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!
• 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

# How to implement filter in Dashboard of Smart reporting

Posted by Nehal Ahmad Oct 6, 2016
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)
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.

# Calculate Ageing buckets in Remedy Smart Reporting 9.1

Posted by Abhijeet Teli Aug 4, 2016
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

By date:
By tag: