Highlighted
Absent Member.. Absent Member..
Absent Member..
685 views

How do I report on incidents and their related changes?

Good Day,
I am attempting to create a report in Crystal Reports (using the SM ODBC driver version 6.2.8). I would like to show the number of incidents opened for one assignment group. Then, I would like to show any related Changes if they exist.

When I try to set up my report, I cannot get the relationships to work correctly. I don't know if I am missing an erddef or a joindef. I am hoping that someone out there can tell me which piece I am missing.

I have been trying to use probsummary, screlation and cm3r.

I can see that some incidents have no changes related to them.
I can see that some incidents have one change related to them
I can see that some incidents have multiple changes related to them.

Can someone tell me if they have done a report like this?

Ideally, I would like to show all incidents for an assignment group and then display the related changes with the change information.

I.E.

probsummary.number, probsummary.briefdescription, related change# if exists, change description if exists

Thanks,

0 Likes
5 Replies
Highlighted
Absent Member.
Absent Member.

Re: How do I report on incidents and their related changes?

It is necessary to restart ServiceCenter/Service Manager after creating the joindefs and erddef records. You have the option of creating a single joindefs record with all three files (probsummary, screlation, and cm3r) or two joindefs records connecting probsummary to screlation and screlation to cm3r respectively. When designing the report, make sure to specify the left-outer join between probsummary and screlation.

 

As an alternative, consider bypassing the SCODBC driver and reporting directly against the RDBMS. This will allow you to use standard reporting tools and not be limited by the "virtual" relationships provided by the SCODBC driver.

0 Likes
Highlighted
Absent Member.. Absent Member..
Absent Member..

Re: How do I report on incidents and their related changes?

Hi there,


@-m- wrote:

It is necessary to restart ServiceCenter/Service Manager after creating the joindefs and erddef records. You have the option of creating a single joindefs record with all three files (probsummary, screlation, and cm3r) or two joindefs records connecting probsummary to screlation and screlation to cm3r respectively. When designing the report, make sure to specify the left-outer join between probsummary and screlation.

 

As an alternative, consider bypassing the SCODBC driver and reporting directly against the RDBMS. This will allow you to use standard reporting tools and not be limited by the "virtual" relationships provided by the SCODBC driver.


I have defined the joindef as 1 record. I have it called probsummary-screlation-cm3r

 

I restarted Service Manager after I added the new joindef.

 

I tried a new report from scratch and it still failed. Do I need to have an erddef as well?

 

I currently have an erddef for probsummary to screlation. But I do not have an erddef for cm3r to screlation.

 

 

If I try to create a report using the ODBC driver, I set up the relationships in the report like this:

 

I have also tried to use Outer joins, right joins, etc on the cm3r link.

 

I set my report up like this:

 

I go to run the report and I get this message:

 

 

I have set up ODBC logging on the driver, but it doesn't make any sense to me 🙂

 

 

 

I would love to use our DB2 ODBC driver. Unfortunately, our scheduler tool for exporting the reports to Excel files doesn't work on this report. It exports the data into multiple columns and it exports terribly. The scheduler app exports reports better using the SM ODBC driver.

 

I hope this is enough data.

 

I am going to try to create an erddef for cm3r to screlation to see if that works.

 

Any other help would be appreciated!

 

I have included a copy of my base report and I have included the odbc log. Thanks!

 

 

 

0 Likes
Highlighted
Respected Contributor.
Respected Contributor.

Re: How do I report on incidents and their related changes?

Did you find a solution? If so, can you share the solution?

0 Likes
Highlighted
Absent Member.. Absent Member..
Absent Member..

Re: How do I report on incidents and their related changes?

Hi there,

 

In October of last year, I had a third level technician from HP take over the case.

 

He told me they had recently discovered a solution concerning joindefs for reporting that he wanted to pass on to me. He told me that they had found a new potential solution that required the recreation of the joindefs in 9.21.

 

At the time he provided me the unload, we were on Release 9.30.201 build 201.

 

He had me apply the most recent patch at the time (patch 3) for 9.30. That patch level worked.

 

 

(Note: I have also stopped reporting against Service Manager using the SM ODBC driver. I am now going directly against our database.)

 

What version are you on?

0 Likes
Highlighted
Respected Contributor.
Respected Contributor.

Re: How do I report on incidents and their related changes?

Sorry for the super late reply. Currently on platform 9.63 / Application 9.63.

For Crystal, I am now going directly against our database and able to create relationships to different tables.

As for dashboard charts, in 9.63, you can now create report charts using the ToDo table and the "Drill Down Process" option which opens the chart into a Todo list so that the correct ticket module opens. For example, you can create a calculated stacked bar chart for obtaining the information in your original question, which would allow your management to obtain the information dynamically at anytime.

 

Joe

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.