The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
Service Desk Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Crystal Reports & HP Service Desk

SOLVED
Go to solution
Highlighted
Jmosquera
Occasional Advisor

Crystal Reports & HP Service Desk

I have recently begun writing reports for HP Service Desk and I am running into a few issues.

1) What are the key tables in SD?
2) Where is the call ID table found?
3) In crystal should I be using the Views or the tables? If the answer is Views, how do I create the views or update the existing ones?
4) I have looked at the datatree providied in this thread but I am still not sure how to port this into crystal. It seems like the codes all corelate to the my names but I'm perplexed!
5) If anyone has a report I could use as a template that would be great.
6) I want to write a report to show how many calls get routed to other teams & to do a break down of the calls we get.

Thank you all for any help!!!
6 REPLIES
Mark O'Loughlin
Honored Contributor
Solution

Re: Crystal Reports & HP Service Desk

Hi,

to begin with you can create views and run reports from them. These will help with basic reporting.

To generate database views:
1. In Service Desk, choose System from the Tools menu. The
Administrator Console is displayed.
2. In the Administrator Console, select the System Panel icon.
3. Click the Report Settings icon. The Report Settings dialog box is
displayed.
4.Click (Re)generate database views for reporting.

Views are generally prefixed with a "v_"
Colleen Talbert
Collector

Re: Crystal Reports & HP Service Desk

The main view I use is V_SERVICECALL and regenerating views for reporting will help clarify some the obsure field names. The views are easier to work with than the actual tables because of the naming structure and I've only had rare occasions where I needed to use the tables. Like any database, it just takes some experimenting to get used to the fields. I don't know what version of Crystal you're using, but I'm attaching a Crystal 9 report we use for ticket Aging as a sample. I hope it helps!

-Colleen
Ivan Sakharov
Frequent Visitor

Re: Crystal Reports & HP Service Desk

I can add that using standard Views (v_servce_call for example) in reporting is not a good idea. This view links over 70(!) of table aliases and performance of viewing reports based on this view becoming really bad. I use Crystal option "Add command" in Database expert, where you can enter direct SQL query to database. The result of this command in Crystal is equal to creating your own view in DB.
Indeed you have to know the structure of SD DB well for applying this method. I`ve attached some visio schemes of SD tables in thread:
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=1051363
Steven Zimmerma
Occasional Contributor

Re: Crystal Reports & HP Service Desk

Dependent upon your reporting requirements and end user community, you may wish to consider an out of the box solution. Our out of the box solution, Westbury Service Desk Intellegence Suite with Report Manager (http://www.westburyusa.com/wm.cgi?lng=EN&main=12&id=24), is built on Business Objects technology.

Regarding your first four questions, the database schema of Service Desk is quite complex. We have converted the technical structure into a logical structure where users and report designers do not have to ask these questions. Our solution is pre-configured to report on all SD modules with no prior knowledge of SQL or SD table structure necessary.

Additionally, our solution provides 80+ documents out of the box. These documents are 100% modifiable. Therefore points 5 and 6 are addressed here.

Steve

Jmosquera
Occasional Advisor

Re: Crystal Reports & HP Service Desk

Ok... Firstly the help you gave was perfect I am running reports now and everything seems great. I only have one more question..

How do I access the history log? I want to look at a ticket and know who opened it when? Who modified it how? and who closed it.

Guys thank you again your help has been great!
George M. Meneg
Honored Contributor

Re: Crystal Reports & HP Service Desk

Hello,

You can either use ITSM_HISTORYLINESERVICALL and join the other tables manualy, or use the appropriate view "v_historylineservicecall". However views tend to be very havy because SD joins many tables to get all information and maybe you don't need that information.

If you use analyzed data there are also some special views you can use, like v_analyzeddataservicecall.

For Analyzed Data have a look at http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=940148
menes fhtagn
//Add this to "OnDomLoad" event