CA Service Management

Hi Everyone,

This issue was first posted on Oct 15, 2015 by Mike Velardo, titled BOXI Report - Multiple Lines, but instead adding to that post, I thought it was better to start a new post.

I was having the same issue as Mike so I tried several of the suggested solutions but none of them worked.  We are at a point now where we need to find a resolution to this multiple line issue.

I have a report that's in a horizontal table, joining the cr.req and act.log tables in the filter section of the report: Call Req Id equal to Act.log Persistent Id, the date range is previous month, there are two variables that pull data in from the activity log, eg. if Type = "TR" then 1 and if Match([Action Desc]; "AHD63013:sla expired*") then 1 and those don't show the duplicate lines, but when I add a 3rd variable for If Match([Action Desc]; "*'Closed' to 'Open'") Then 1 that's when the report shows the duplicate lines. 

Any help would be appreciated.

Thanks,

Joyce Lilie

Parker

HI Joyce Lilie,

   I believe this was the post: BOXI Report - Multiple Lines.

  Can you detail your query?

Regards

Tiago

Hi Tiago,

Thanks for replying,

I'm not sure if you want the entire query, but here it is:

SELECT
  cr.ref_num,
  cr.group_name,
  cr.priority_symbol,
  cr.customer_combo_name,
  cr.customer_location_name,
  cr.customer_organization_name,
  cr.assignee_combo_name,
  cr.category_symbol,
  cr.category,
  cr.open_date,
  cr.resolve_date,
  cr.close_date,
  cr.type_symbol,
  cr.sla_violation,
  cr.target_resolved_count,
  cr.request_area_description,
  cr.request_area_symbol,
  alg.type,
  cr.group_location_name,
  cr.resolution_code_symbol,
  cr.symptom_symbol,
  alg.action_desc,
  alg.type_symbol
FROM
  cr,
  alg
WHERE
  (
   alg.call_req_id  =  cr.persistent_id
   AND
   cr.type_symbol  <>  'Problem'
   AND
   cr.group_name  NOT IN  ( 'PeopleSoft Confidential ','PeopleSoft Confidential-Europe','PeopleSoft Confidential-Mexico','PeopleSoft Confidential-AP','PeopleSoft Confidential-NA','PeopleSoft Confidential-LA'  )
   AND
   cr.close_date  BETWEEN  {d '2016-09-28 12:00:00 am'}  AND  {d '2016-09-28 11:59:59 pm'}
  )

Here's the variables as described in the description:

And the third variable that causes the duplicate lines:

Hope this helps, let me know if you need anything else.

Joyce

HI Joyce,

  Yes, this is it.

  Now, i can help to clarify how works. I'm Looking for your query and it's possible to identify why happens?         It's "alg.call_req_id  =  cr.persistent_id" the right join to select the CA SDM log, but now it's missing: what do you need to see?

Go to your ticket and look the Log TAB, you'll see many lines, these lines have types and another particularities. The only way to return one line is identify for BOXI which is the one? After that, for example: drag and drop the alg.type='INIT', you'll see just only one.

Example:

Regards

Tiago Macul

Hi Tiago,

Thanks for the quick reply. 

I'm not sure I understand: It's "alg.call_req_id  =  cr.persistent_id" the right join to select the CA SDM log, but now it's missing: what do you need to see? - what you mean by "it's missing".

I can identify the log line item that I want to see, which is why I do the 'match' function. but it's still showing two lines.

Thanks,

Joyce

Joyce, 

 Can you take a shot from Log TAB? You need to say for BI, which row do you need  to see.

Regards

Tiago

 RESULTS for (alg.call_req_id  =  cr.persistent_id AND alg.type='INIT')

Regards

Hi Tiago,

Here's a screen print of the multiple lines, variable being used, and the log tab with the line I'm reporting on: \

HI lilmoss , You need to select type REOPEN, BUT, still the possibility for duplicate if the ticket was reopen twice.

 RESULTS for (alg.call_req_id  =  cr.persistent_id AND alg.type='INIT')

You can use this select and look for your need

select distinct type from act_log

Regards

Tiago

Hi - it's been while since anyone has posted to the entry.  I'd like to continue working on a resolution for this issue.  I understand what Tiago Macul is trying to say - just select the log type that I need to report on, but in my original example, I have three variables with three difference log types that I need to report on.  I read in another post when someone thought there might be a way to hide the empty/blank variables so only the variables that are true show on the report instead of all three in three different rows.

Thanks,

Joyce Lilie

Hi Lilmoss

can please elaborate

Hi La-Qa,

I'm not sure what more I can elaborate on. Is there something you are looking for specifically?

Thanks,

Joyce

Hi Everyone,

It's be a while since this post has been visited.  I'd like to revisit this issue again. The report has the following join: cr.persistent_id  =  alg.call_req_id - there are three activity log types being pulled into the report: # of tickets that were transferred, the # of tickets that were reopened, and the # of tickets that breached the SLA.  I'm doing a sum of each of these variables and the sum variable is being put in the table cell.  I'm not getting any duplicate rows doing it this way.  But if I also want to add another line from the activity log - it's an event occurred type - I'm pulling in the text from that line.  When I add that variable to the table, I get duplicate rows as shown below:

All the suggested that have been made so far on this thread and others from earlier posts have not worked.

Thanks in advanced,

Joyce Lilie

HI lilmoss ,

   You need to work, the CABI do a distinct (using sum) for us therefore when we need to bring some field to reports where don't has the same value, CABI will show 2 lines. It's the same works then database. 

   How can we do? It's need to create 2 querys.

01. SUM

02. Show descriptions.

Regards

Tiago Macul

Hi Tiago,

Thanks for replying.  I'm not sure I understand what your saying.  I have two query's for each type field:

1. =If [Type] = "RO" Then +1

2. =Sum([Close to Open])

I'm not finding the SUM() Distinct command in the 4.1 version. 

Thanks,

Joyce

Hi Joyce ,

   Could you did it?

Regards

Hi Tiago,

No I have not been abel to resolve the multiple lines on a report in BOXI.

Thanks

Joyce