Crystal Reports: Mixed Page Orientation Formatting

I have starting splitting this blog out by subject.  As a result, this Crystal Reports related post is now here:

http://crystalreportsexperttechniques.wordpress.com/2012/08/04/crystal-reports-mixed-page-orientation-formatting/

Posted in CodeProject, Crystal Reports | Tagged , , , , , , , | 2 Comments

Qlikview ITIL Incident Management Report (pt 1)

As this is my first Qlikview post, it feels like some sort of introduction is required.

If Crystal Reports and Xcelsius had a baby, which evolved and improved over many generations, it would be called Qlikview.

All the power of SQL and its own scripting language, mixed with more interactivity and drilldown functionality than you could ever want.

Qlikview is fantastic.

And…

ITIL is fantastic.

So here is a straightforward tutorial on how to build a simple ITIL Incident throughput and KPI table in Qlikview.

This post actually follows on quite nicely from my previous one, as it focuses on extracting several cuts of data in the most efficient and accurate way.

This is the result we are aiming for…

…though we will add some formatting to allow a user to select which columns they want to see and shorten the number of columns…

…which is often the key focal point of an Incident Dashboard, or a separate report with a couple of complementary charts.

To keep this tutorial short and sweet (in two parts!!!), the table does not include some of the columns often associated with this style of report.  Things like: Incidents on hold, re-opened, cancelled or transferred to another Business Unit.

If any of those columns are added, it can impact the integrity of the existing information as it subtly changes the context of the entire report.  So think carefully before doing so.

This tutorial is based on a simple Access Database which has only three tables, but the examples I work through should be easily applied to any ‘real life’ Service Management application:

If you’d like the actual Access database I use here, drop me an email (jasondove@hotmail.com).  To follow this tutorial exactly, you will need both this database and to set up an ODBC driver pointing to it.

What this style of report shows

The most basic version of this report shows the volume of Incidents flowing through a one month period.

Essentially, if the ‘Brought Forward’ value is consistently less than the ‘Carried Forward’, the responsible Business Unit is struggling to keep up with demand.

We will develop this part of the report before extending it to include SLAs.

Getting the Data

The trick with this style of report is to get all the data required for accurate results while keeping the actual volume of data to a minimum.

I have found that if separate Tables are added to Qlikview via individual SQL, something odd happens with the linking and the results can be questionable.

With this in mind, the main body of the data for this report is going to be gathered by one SQL statement.

From this one set of data each Incident will be counted one or more times as its criteria is tested for each summary column in the report.

1.  Open Qlikview and open a new report.

2.  Go to the menu File -> Edit Script.

3.  Click on the Connect button to point Qlikview at the database.

4.  Select your database from the list.  In this case it is ‘Dove_ITIL’.

5.  Enter the following SQL beneath the newly generated line of code for the ODBC:

SELECT Incident.Incident_ID,
Incident.Open_Date,
Incident.Resolved_Date,
Incident.Closed_Date,
Priority.Priority_Name,
Priority.Priority_ID,
Priority.Resolution_Target,
Priority.Close_Target,
Priority.KPI_Resolution,
Priority.KPI_Close
FROM Incident INNER JOIN Priority ON Incident.Priority_ID = Priority.Priority_ID
WHERE
(
Incident.Closed_Date IS NULL
OR
Incident.Closed_Date >= DateValue(Year(Now()) & ‘,’ & Month(Now())-1 & ‘,01’)
)
AND
Incident.Open_Date < DateValue(Year(Now()) & ‘,’ & Month(Now()) & ‘,01’);

6.  Click on the Save icon at the top of the window, and save the report.

7.  Click Reload icon to populate the data into the report.

The report now has access to the data we need to build the table.

Displaying the Data

With the subset of data selected, we need a container to hold the summary information required to create the ITIL stats.

In this case, we are going to use the Straight Table option within the Charts.

8.  Click the Chart icon.

9.  Enter a title and select the ‘Straight Table’ icon:

10. Click Next.

Dimensions / Grouping the Data

Because we are interesting in the SLAs for each different level of Priority we shall use this as a Dimension to focus our summaries.

11. Select the ‘Priority ID’ and click on ‘Add’ to move it to the ‘Used Dimensions’ panel.

12. With the field still highlighted, click on the ‘Edit…’ button, and type the following code:

=Priority_ID & ‘ – ‘ & Priority_Name

This will display both the Priority ID and Name together in the table and make it more meaningful.

13. In the Label, even though it is greyed out, type ‘Priority’ for the columns title.

14. Click ‘Next’.

The next screen automatically opens an Expression Edit window for the creation of one summary.

Brought Forward

We shall add the first column now, save and check the report, then add the additional summaries.

First things first: the table starts with the number of Incidents which were opened before the start of the target month and are still open as the month begins.

There should be an Expression window open from step 13…

15. Enter the following code:

=SUM(IF(Open_Date < AddMonths(MonthStart(ToDay()),-1), IF(isnull(Resolved_Date) OR Resolved_Date >= AddMonths(MonthStart(ToDay()),-1),1,0),0))

What does it do?

Well, by using ToDay() to find the current date, MonthStart() to wind the time back to the beginning of the current month and AddMonths() to move an entire month at a time we can calculate everything we could ever want for monthly report.

In this case, we want Incidents opened before the target month that were resolved during or after the month (or not resolved at all).  We put those conditions into an IF statement that evaluates to 1 if they are true (0 if not) and then SUM the 1s for the result.

16. Click ok to save the Expression and enter ‘Brought Forward’ as a Label for the Expression :

17. Click Finish.

The result should look a lot like this:

It is certainly not the most impressive thing ever created in Qlikview, but is a step in the right direction for this ITIL table.

From this point we can edit the table and add the additional rows.  For the reminder of Part I of this tutorial we shall focus on more basic summaries, similar to the Brought Forward column.

But first, we shall fix the Sort Order.

18. Right click on table and select ‘Properties…’.

19. Click on the ‘Sort’ tab and tick the ‘Text’ Sort By option.

20. Click on the ‘Expressions’ tab to be ready for the next part of the tutorial.

Open Incidents

This is a count of all the Incidents which are opened in the previous month and is one of the easiest to calculate.

21. From within the Expression tab, click ‘Add’ and enter the following code:

=COUNT(distinct IF(InMonth(Open_Date,ToDay(),-1),Incident_ID))

This code works a little differently to the previous example.  The IF Statement returns the Incident ID if true (and nothing if it does not) and these are then COUNTed.

22. Enter ‘Opened’ as the Label for this expression.

Resolved Incidents

This is a count of all the Incidents which are resolved in the previous month and is very similar to the Open Incidents in how it is calculated.

23. Add another Expression with the following code:

=COUNT(distinct IF(InMonth(Resolved_Date,ToDay(),-1),Incident_ID))

24. Label this Expression as ‘Resolved’.

Closed Incidents

Closed Incidents, the most annoying part of Service Management: getting an official sign-off of a completed job from a user who no longer cares as they are now happily working again.

This is a count of all the Incidents which are closed in the previous month and is very similar to the previous two columns in how it is calculated.

25. Add another Expression with the following code:

=COUNT(distinct IF(InMonth(Closed_Date,ToDay(),-1),Incident_ID))

26. Label this Expression as ‘Closed’.

Carried Forward

And lastly (for the moment) the number of Incidents which were opened before the end of the target month and are still open as the month ends.

This calculation is almost the same as the Brought Forward summary we started with, and when viewing successive months: the Carried Forward from the previous month should equal the Brought Forward of the next month.

27. Add another Expression with the following code:

=SUM(IF(Open_Date < MonthStart(ToDay()),IF(isnull(Resolved_Date) OR Resolved_Date >= MonthStart(ToDay()),1,0),0))

28. Label this Expression as ‘Carried Forward’.

29. Save the changes and view the Table.

And here it is:

Summary (so far)

These five columns provide the volume of Incidents and their throughput during the month.  Even if we stopped the development now, this report has some value.

As well as showing the volume of Incidents by Priority, it is easy to see if the Business Units responsible are coping or not, just by comparing the Brought Forward and Carried Forward fields.

Which is just as well, because I am going to end this tutorial here!

Part II is coming soon!  This will cover the SLA column scripts, formatting, tweaks and review the report as a whole.

Sign up on the right to get an email when it is available.

Posted in ITIL, Qlikview, ServiceNow | Tagged , , , , | 2 Comments

How Intelligent is Your Business Intelligence?

For a change from me bleating on about ServiceNow reporting, I thought I’d write something a lot more generic.

This is the number one recurring mistake I have seen consulting and pops its ugly head up time and again across all business sectors, reporting software and methodologies.

It is particularly prevalent for ITIL service reporting, but appears whenever time periods are used as a basis for measuring multiple values.

The thought process tends to go something like this:

  1. Ok, we need to know how many Incidents were opened last week, so let’s filter the report for those records.
  2. Now we need to know the summary by Priority, so add a group for the Priority and count the Incidents in each group.
  3. What’s next?  Oh yeah, Incidents which were closed last week.  Easy stuff, let’s just count the Incidents with a Status of ‘Closed’.
  4. Next, outstanding Incidents that were not closed at the end of the week.
  5. Easy, just count the Incidents that do not have a Status of ‘Closed’.
  6. Done!

It’s not done!  It’s massively wrong, misleading and hides the worst Incident resolution stats!

What this report will show is the number of Incident opened last week and the number of those opened Incidents which were closed.

Any Incident which was opened two weeks ago and closed last week will not appear in this report, not only that, it will not appear as closed in any report…ever.

Additionally, if the report for the last week is refreshed first thing on a Monday morning it will show one result, but if more Incidents from last week are closed after that and it is refreshed again: the results of the report will change.

This is horribly wrong for a report that should be a definitive record of the previous week’s results.  And a ‘solution’ of “run it once, first thing on a Monday” is amateurish in the extreme.

And if all that is not bad enough, with the above report it is possible for the KPIs for each week to be reached, but fail over a monthly or a yearly based report!

So What Went Wrong?

Quick answer: the date range.

Because the report developer’s focus was initially on the opened Incidents, the rest of the report metrics suffer.

To correct this, the date range filter needs to accommodate both open and closed Incidents within the last week.

This is what the report does currently (the solid lines depict which date ranges are evaluated in the filter):

Because the closed date is not used, only Incidents opened in the last week are selected with no regard as to whether or not they are closed.

The asterisks are there to illustrate my earlier point.  The green asterisk represents an Incident being opened towards the end of the week.  The red asterisk is the same Incident being closed in the following week.

The closing of this Incident does not fall into the report logic and will never be displayed, even though an Incident being closed (or resolved) is the best indicator of work being done.

The obvious solution may appear to be to use the closed date, rather than status, like this:

This works for the open and closed Incidents, but does not identify ALL the outstanding ones at the week’s end.

The problem is that an Incident can be opened before last week and remains open during the entirety of last week.

To cover all possibilities, a wider approach is needed:

In short, every Incident opened before the end of last week which was not closed before the start of last week.

This logic will provide the correct bucket from which the separate metrics can be extracted.

In theory this means scanning the entire database every week for long term open Incidents which can be a massive reporting overhead.

The way to handle this is to either make an arbitrary decision as to what age open Incidents can ignored in this style of report (but ideally be featured in a dedicated ‘Old Incidents’ report)…or investigate the actual data and find the real cut-off date for Incidents which are not closed and report from that point.

Finally

The above is focused purely on the conflict between measuring three simple values in the one report.

With other metrics added, the issue is compounded, especially when re-opened, re-allocated or suspended Incidents are thrown into the mix.

The key thing to keep in mind: when designing any report is to base the date filters on whole of the data required for ALL the metrics being featured in the report.

And finally, whenever adding a new metric to an existing report, re-examine the date filter in light of the new requirement.  It is sometimes the case that to get an accurate result for a new metric causes an impact on every other metric already in the report.

Posted in CodeProject | Tagged , , , | Leave a comment

Example ServiceNow Report

I posted this on Linkedin a while ago, but obviously things get pushed down in the group history until they are forgotten.

This is quite a clever Crystal Report (if I do say so myself) which displays when selected Services were impacted over a set time frame in quite a nice graphical way:

ServiceNow Report

(Obviously I deleted the service names on the left, but they will be replaced by your service names when the report is refreshed.)

If you want this report (free!), drop me an email at jasondove@hotmail.com.

For more cool information about ServiceNow Reporting, please look here!

 

Posted in Crystal Reports, ServiceNow | Tagged , , | Leave a comment

A few Random Tips for ServiceNow Reporting

I have been developing reports directly from ServiceNow ever since the ODBC connection was made available (http://wiki.service-now.com/index.php?title=ODBC_Driver).

This new functionality allows ServiceNow to be analysed directly with greater freedom and accuracy than through the built-in reporting (mentioned in more detail elsewhere in this blog).

ServiceNow have done a fantastic job with the structure and naming conventions within the table schema and it is a delight compared to a lot of systems I have reported on.

These few tips may be of interest to anyone embarking on this route.

Field Naming conventions

The fields in the ServiceNow table structure are quite consistently named to the point where general rules can be applied:

If a field name begins with “DV_”, the DV stands for “Display Value”, which is the same thing as is shown in the ServiceNow application and what the audience of your reports will expect to see as labels for groups, charts and summary names.

There is nearly always a corresponding field without the DV_ prefix which holds the same data in a less accessible way.  For reporting, it is the DV_ fields which should be used to label everything but any calculations (grouping, summaries, filtering etc) the corresponding data fields should be used.

Top tip: Ordering the database fields alphabetically within your reporting software will group all the DVs together and makes it easier to find the field you want.

Please note that both date and system fields tend not to have a DV equivalent.

Comparing DB field names to ServiceNow

The actual field names used in the database are different to those used in the ServiceNow interface which is different again to the built-in reporting field names.

This can make identifying the required fields difficult.

However, if a built-in report is exported from ServiceNow in a CSV format, the column headings match the database field names, while the names in the report template it game from match ServiceNow.

When viewed side by side, it is a simple enough task to identify which is which and can save a lot of guess work and frustration.

Correct Linking

Tables have to be linked together in order to create a report, like Incident to Service to find out what Services have been impacted.  Having this freedom to link through tables and retrieve data in any order is the true power of ODBC reporting.

The quick and easy route is to link tables based on the DV name fields, it is obvious what is being linked and you can actually look in the fields manually and compare values.

There are two problems with this:

1.  Content

There is no guarantee the content of a DV field is unique, so any filtering or grouping on these fields can lead to errors.

2.  Speed

DV fields do not have an Index (a database thing which makes any sort or search on the field massively quicker) and can make reporting painfully slow if they are used for anything other than display purposes.

Both of the above problems can be resolved by using the sys_ID  field .  The sys_ID field is unique for a particular row of data, not just in a single table, but across the whole database.

And of course, the sys_ID is also Indexed, so any report based on this field will run as quickly as is possible (bad structure and excessive data still kills performance).

It’s De-Normalised for a reason!

Ok, time to get a bit (more) nerdy!  Databases are usually designed so each piece of data is unique and identifiable from an ID.  This is called Normalisation and is the foundation of both database design and professional report development.

What the ServiceNow table structure (and a lot of other databases) does is repeat the same information in other tables to save you the effort of linking.

A good example of this is the Incident table.  The Incident table contains fields to link to other tables to retrieve the affected business unit or the root cause.  But these also exist as DV fields that can be pulled straight from the Incident table making reporting quicker to develop and run.

And Many More…

Of course this is just a handful of hints is the tip of the iceberg of the knowledge needed to get the most from ServiceNow reporting.

For more cool information about ServiceNow Reporting, please look here!

Posted in ServiceNow | Tagged , , | 5 Comments

ServiceNow Reporting and Why Direct ODBC Reporting is the Only option

ServiceNow is sold with a built-in reporting solution which is supposed to meet all business needs for accurate ITIL reporting.

And for a built-in reporting solution, it is the more feature rich and better though out than any I have seen in similar products.  But at the time of writing, it has some serious problems.

I have recently concluded a yearlong consultancy with Centrica, who were one of the first major pioneers of the ServiceNow software, in the role of Technical Lead in the MI Reporting Team.  I am also providing ad hoc ServiceNow reporting solutions for a variety of companies worldwide

During this time several shortcomings became apparent:

1.  More complex reports cannot be created and ‘tables’ can only be linked on preset paths.

2.  Both rows and columns of data are often lost for no apparent reason.

3.  Tables can only be linked with INNER joins, again, limiting the scope and quality of the data returned and potentially producing erroneous results.

Point 1 is an annoyance that limits reporting options, points 2 and 3 can escape totally undetected unless a lot of work is done to validate the results.

In February 2011, ServiceNow(the company) released direct ODBC access to the underlying database.

This is the only way to accurately report on the ServiceNow software.

I am currently available for ad hoc consultancy work in England and across mainland Europe and of course, due to ServiceNow’s Cloud Architecture am able to provide consultancy worldwide.

I am more than happy to have an informal chat with anyone about the specific challenges faced by businesses who want accurate reporting they can rely on and what I may be able to do to help.

For more cool information about ServiceNow Reporting, please look here!

Posted in ServiceNow | Tagged , , , | 1 Comment

Reporting Software and ServiceNow

Last year ServiceNow released ODBC access to their database, enabling customers to produce their own high quality reporting.

As direct reporting from ServiceNow is in increasing demand, I thought I’d put together a brief overview of the more popular choices of Reporting Software on the market.

Of course, if anyone has experience of any others, or wants to add to (or contradict!) my views, please feel free to post.

MS SQL Reporting Services (SSRS)

MS SQL Reporting Services is part of the MS BID package of Business Intelligence software but is a capable piece of reporting software in its own right which produces professional results.

But its main selling point is that it is not sold!  If you already have MS SQL Server, it is free.  This is fantastic news for software of this calibre.  And because SSRS belongs to a suite of programs within SQL Server, there is no extra cost to schedule reports to run at set times automatically.

Report development in SSRS is quite SQL heavy, which is great for DBAs and programmers, not so much for people used to Excel as a reporting tool.

Of course, the flip side of it being free if you already have MS SQL Server is that it is expensive to buy a database just to get the free reporting software.

Because SSRS is intended for use with a suite of other software, it can struggle to do some of the more complex things that other programs in this list can as it is not a full solution in its own right.  This can lead to a ‘bitty’ architectural solution with functionality being spread out without any apparent order.

SSRS is primarily designed for use with MS SQL Server.  As a result, pointing it at over databases can be trickier than the other software in this list (who were created independently of any particular database).

Hiring SSRS expertise can be difficult as consultants tend to be DBAs, rather than SSRS specialists and are in high demand.

Business Objects

‘Business Objects’ tends to refer to an actual suite of software which together form an impressive toolset for data manipulation and display.  The Business Objects component itself is focused on taking the tables from a database and transforming them into a structure ideal for reporting: called a Universe.

With the latest version of Business Objects there is a choice of Web Intelligence (WebI) and Crystal Reports for Enterprise to actually cut and display the data gathered in the Universe.

(Older versions of BO have Desktop Intelligence, but I strongly recommend avoiding this option, it is not future proof and produces reports that just look dated.)

Crystal Reports is covered as a separate solution later, but viewed purely as a means to report on a Universe, it is a match for WebI and arguably better in some respects.  But Business Objects and WebI have been used together for longer and most BO developers are also WebI developers, whereas Crystal Reports is often a separate skill set.

Most of the shortcomings in WebI functionality is covered by the work already done when developing the Universe.

One of the best things about a Universe is that all the statistics you want to create can be done in one place and then included in reports as needed.  There is no duplication of effort that tends to occur in standalone reporting and a standardisation of reporting metrics is enforced automatically.

Scheduling reports is not a problem, as the scheduling software is included within the standard Business Objects suite of products.

However, this standardisation can also be a hindrance.  Professional level ITIL reporting often requires a very flexible approach to data interrogation to cover certain measures.  Often some measurements are at logical odds with other measures based on the same data.  This is where Business Objects can get mired down trying to accommodate all requirements in one place.

Even with free software (and Business Objects is far from free!) implementing an ITIL reporting solution costs money.  Expertise usually has to be hired in, time and money is spent on requirements gathering, hardware and so on.  Setting up a Business Objects Universe and then a complementary suite of reports can take weeks or even months.

This can be too long a wait for a business eager to give their managers the information they need to work at full capacity.  This is a shame, as the table structure of the ServiceNow database is so well thought out there is almost no need for a Universe anyway beyond the aforementioned efficiency of effort.

Crystal Reports

Crystal Reports can report from virtually any data source including of course, the ServiceNow ODBC, is quick to develop with and can produce a wide variety of reporting styles.

Crystal’s inbuilt scripting language allows a huge amount of control though can take some time to learn for those new to programming.

Crystal Reports is hugely popular and used across all business sectors and is versatile enough to do just about any job.  This versatility can also be a problem and without proper work practices in place a suite of Crystal Reports can become an unmaintainable mess.

The main negative thing for Crystal Reports is that it requires a separate piece of scheduling software to automate reporting.  Whichever scheduling software is chosen, be sure to thoroughly test it within your business before deployment, especially the security if you intend distributing reports outside your own intranet.

The charting can also be a bit limiting and is starting to look a little dated now, but still crisp and clear.

Another possible minus for Crystal Reports is that if you do not have the skills in-house already, it can be tricky to hire an effective consultant.  Unlike SSRS and Business Objects developers, who tend to come from a DBA or programmer background, a large percent of Crystal Reports consultants started in office admin jobs and tend not to have the technical experience needed to solve the more difficult questions.

Xcelsius / Crystal Visualation / SAP Dashboards

This product of many names was originally developed as an add-on to MS Excel and still uses Excel for much of its underlying functionality.

The result is reporting software with a shallow learning curve which produces gorgeous, interactive looking Dashboards that can be easily exported and distributed online.

Of course, there is a downside, or two.

The main one is that plugging Xcelsius directly into databases is a pain.  It does not have that underpinning ODBC foundation like the other products in this list and data must either be piped in via another product entirely or through a third party component that plugs straight into Xcelsius.

The good news on this front is any company using ServiceNow probably has some good Java developers at their disposal that can develop Web services to connect Xcelsius to the database.

On a final note, anyone using Business Objects and/or Crystal Reports should add Xcelsius to their arsenal.  It integrates well will both software and is definitely worth the effort in this case, both for dashboard designs and more flexible/nicer looking charting in standard reports.

JasperReports

This software is not really in the same league as the above products in many respects and may look like the odd one out.

But it does have a number of strong benefits in its favour:

1.  Very capable software and produces professional results to challenge any other product in this list.

2.  It is free.

3.  Very, very similar to Crystal Reports, so similar that a Crystal Reports expert can quickly get to grips with JasperReports.

4.  Java based and can be distributed through your organisation with relative ease.

JasperReports is definitely worth a look for any serious ServiceNow reporting implementation.

Summary

All of the above software has its own pros and cons which are largely dependent on the target organisation’s existing software, infrastructure and skill set.  With this in mind, I cannot recommend a specific piece of software, but am happy to answer any questions I can.

For more cool information about ServiceNow Reporting, please look here!

Posted in ServiceNow | Tagged , , | 6 Comments