PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 09/26/2011 7:46 PM by  Rod Weir
The issue hierarchy field in v11 reports and database
 0 Replies
AddThis - Bookmarking and Sharing Button
Sort:
You are not authorized to post a reply.
Author Messages
Rod Weir
HelpMaster development team
Helpdesk Hall of Fame
Helpdesk Hall of Fame
Posts:541
Points:977


--
09/26/2011 7:46 PM

    Received via tech support regarding the change in reporting fields between v10 and v11

    I remember that the Issue 1, Issue 2, Issue 3 and Issue 4 are variables that I declared to break up the Issue field to report different category levels. How is it now with the version of helpmaster? Is there fields that I can use to report different level of call issue type?

     

    The Issue1, Issue2 etc fields are no longer part of the base reporting views.  They have been replaced with the complete field simply called Issue.

    To isolate each individual level of the Issue hierarchy, you can do one of the following.

    1. Create a formula within Crystal Reports to chop up the "Issue" field into it's individual components.  Often, just the first level of the issue hierarchy is used to get a "Top Level" code.  In a Crystal Report formula, this would be achieved using a formula like this.

    If Instr(1,{Command.Issue},">")>0 Then
        Formula = Left({Command.Issue},Instr(1,{Command.Issue},">")-1)
    Else
        Formula = {Command.Issue}
    End If


    2. Create a view in the database that returns each level and then join this view to your reporting view / command that is being used in Crystal Reports.  The old view definition for HelpMaster v10 and prior was this


    CREATE VIEW [dbo].[qryJobIssue]
    AS
    SELECT tblIssues_1.pkID, tblIssues_1.Caption AS Issue, tblIssues_2.Caption AS Issue1, tblIssues_3.Caption AS Issue2, tblIssues_4.Caption AS Issue3,
     tblIssues_5.Caption AS Issue4,tblIssues_1.IsDeleted
    FROM dbo.tblIssues tblIssues_4 LEFT OUTER JOIN
     dbo.tblIssues tblIssues_5 ON tblIssues_4.SelfLink = tblIssues_5.pkID RIGHT OUTER JOIN
     dbo.tblIssues tblIssues_1 LEFT OUTER JOIN
     dbo.tblIssues tblIssues_2 ON tblIssues_1.SelfLink = tblIssues_2.pkID LEFT OUTER JOIN
     dbo.tblIssues tblIssues_3 ON tblIssues_2.SelfLink = tblIssues_3.pkID ON tblIssues_4.pkID = tblIssues_3.SelfLink



    Note however that this SQL will return the issue hierarchy in reverse order due to the way the self-linking fields in the database works.  To get it into the right order, you can either re-write the SQL, or do it via a Crystal Reports formula as was the case in v10 of HelpMaster.

    Crystal Reports formula "Issue" using Visual Basic syntax.



    Dim strIssue As String

    if not isnull({qryJobIssue.Issue4}) then
        strIssue = strIssue & {qryJobIssue.Issue4} & " > "
    end if
    if not isnull(qryJobIssue.Issue3}) then
        strIssue = strIssue & {qryJobIssue.Issue3} & " > "
    end if
    if not isnull({qryJobIssue.Issue2}) then
        strIssue = strIssue & {qryJobIssue.Issue2} & " > "
    end if
    if not isnull({qryJobIssue.Issue1}) then
        strIssue = strIssue & {qryJobIssue.Issue1} & " > "
    end if
    if not isnull({qryJobIssue.Issue}) then
        strIssue = strIssue & {qryJobIssue.Issue} & " > "
    end if

    ' Cleanup trailling " > "
    if len(strIssue)> 3 then strIssue = left(strIssue,len(strIssue)-3)

    formula = strIssue


    HelpMaster development team
    Check out the HelpMaster roadmap
    You are not authorized to post a reply.


    Workflow Approvals and Attachments by Rod Weir in Using HelpMaster with 0 replies Here's a configuration that can be used when you have a process that requires approval that is based on a file attachment. Example Scenario: ...

    October 2019 release now available by Rod Weir in Announcements with 0 replies A new release of HelpMaster is now available.  This release focusses on workflow stability, enhancements and other fixes. For a list of the ma...

    August 2019 release now available by Rod Weir in Announcements with 0 replies A new release of HelpMaster is now available.  This release has some great workflow improvements, new control set fields, a new manager feature, ...

    Allow staff to view all jobs, all skillgroups by Rod Weir in Installation, configuration and upgrading with 0 replies By default, staff members can only view and action jobs that are assigned to the skillgroup(s) that the staff member belong to.  Fine-level contr...

    Building knowledge management and KCS into the workflow by Rod Weir in Using HelpMaster with 0 replies Here's a workflow idea to assist with improved knowledge base articles and knowledge management / KCS As part of a support workflow, build into...

    Workflow Approvals and Attachments by Rod Weir in Using HelpMaster Here's a configuration that can be used when you have a process that requires approval that is based on a file attachment. Example Scenario: ...

    October 2019 release now available by Rod Weir in Announcements A new release of HelpMaster is now available.  This release focusses on workflow stability, enhancements and other fixes. For a list of the ma...

    August 2019 release now available by Rod Weir in Announcements A new release of HelpMaster is now available.  This release has some great workflow improvements, new control set fields, a new manager feature, ...

    Allow staff to view all jobs, all skillgroups by Rod Weir in Installation, configuration and upgrading By default, staff members can only view and action jobs that are assigned to the skillgroup(s) that the staff member belong to.  Fine-level contr...

    Building knowledge management and KCS into the workflow by Rod Weir in Using HelpMaster Here's a workflow idea to assist with improved knowledge base articles and knowledge management / KCS As part of a support workflow, build into...

    June 2019 release now available by Rod Weir in Announcements A new release of HelpMaster is now available.  This release builds upon features and stability, particularly in the workflow, and change manageme...

    Workflow email object - email account by Rod Weir in Using HelpMaster The up-coming release of HelpMaster will have a lot of workflow improvements.  One of these is in the email object.  It is now possible to c...

    Change Management by Rod Weir in Using HelpMaster Change Management in HelpMaster is a relatively new feature that is evolving rapidly.  Based off live-use in small and large departments, our &qu...

    April 2019 release now available by Rod Weir in Announcements A new release of HelpMaster is now available.  This release builds upon features and stability, particularly in the workflow, change management a...

    February 2019 release now available by Rod Weir in Announcements A new release of HelpMaster is now available.  This release builds upon features and stability, particularly in the workflow and web portal. F...