CA Service Management

Expand all | Collapse all

BOXI Report - Multiple Lines

  • 1.  BOXI Report - Multiple Lines

    Posted Jun 06, 2014 06:48 PM

    CA SDM 12.6, Win2008R2, BOXI using WebInt

    I'm creating a report using various criteria that frankly is all working fine. On the Edit Query tab, I'm setting the query to search for tickets that have a particular Incident Area, assigned to a specific group, are either Closed or Resolved between a particular set of dates. Up to this point no problem. On the Edit Report tab, I can get a list of all the tickets that meet that criteria with one ref_num on each line. But when I create a variable testing for a certain Action Description and if present display something in an additional field that's where I get the multiple lines. Let me explain. Here's the code used in the Variable:

    =If Match([Action Desc];"*completed*") Then "Survey Sent and Received"

    On its own, this code works perfectly and does exactly what I expect. However, once added to a column in the existing report I now get an extra ref_num line entry for each ticket that meets that criteria.

    Example of results
    Ref_NumSummary
    15663This is a sample ticketSurvey Sent and Received
    15663This is a sample ticket

    What I really want is to print the "Survey Sent and Received" in a field at the end of the existing row for that ref_num.

    Any ideas? As always, any help or guidance would be greatly appreicated.

    Mike



  • 2.  RE: BOXI Report - Multiple Lines

    Posted Jun 10, 2014 07:37 AM

    I bet you're also querying the Activity Log for the ticket, that's why you get duplicate lines. Try to add the new variable as a 'Measure' instead of 'Dimension' and see if it works like this. Another option would be to not create a variable, but add an empty cell at the end of the row and edit the contents pasting the variable formula (Excel style).

     



  • 3.  RE: BOXI Report - Multiple Lines

    Posted Jun 10, 2014 10:03 AM

    Thanks for the reply Mitu. Yes I am querying both the Detail and Activity log. I tried changing the variable to a 'Measure' and it gave me the #MULTIVALUE error. So then I tried adding the query directly to a blank cell and that continued to give me the extra lines. 

    Would I help myself if I query only the Activity Log? I can change the original report and do that. It won't affect the data I'm gathering.

    Thanks in advance for your help.

    Mike



  • 4.  RE: BOXI Report - Multiple Lines

    Posted Jun 10, 2014 10:54 AM

    Thought I would mention that I just re-created the report soley from the Activity Log. And I'm still getting the extra lines.

    So if you have any other ideas I would welcome them.

    Thanks,

    Mike



  • 5.  RE: BOXI Report - Multiple Lines

    Posted Jun 11, 2014 03:35 AM

    What do you need from the Activity Log ? You should make your query from the Activity Log return only 1 record per ticket (i.e. either get the Resolved activity or the Closed one), this way you'll not get duplicates.



  • 6.  RE: BOXI Report - Multiple Lines

    Posted Jun 11, 2014 02:07 PM

    Well this part of the report is in need of multiple activity log entries, such as "Survey Sent" and "Survey Submitted" type entries. So I have to look for those specific ones and report "true" if they're present on the ticket. To compound my problem, for another part of the report management informed me yesterday they also want to know any ticket that has more than 7 days between any activity. Assuming multiple actvities on each ticket, I need to just count the ref_num once per ticket, if any part of it violated the "7 days between" requirement. 

    In my attempt to begin this part, I have the following query that seems to be working, sort of. It's actually getting more tickets than it should, which is my inherit problem again. sad

    =Count([Request Ref Num] Where (Not (Match([Action Desc];"*from 'Resolved' to 'Closed'"))
    And (DaysBetween([Time Stamp];Previous([Time Stamp])) >7) And [Request Open Date]
    Between('1/1/2014'; '2/1/2014')))

     

    To say the least, the code above probably isn't the most efficient, but it seems to be getting the job partially done. The problem is it's also reporting a few tickets that don't meet the criteria and I can't understand why yet.

    Thanks for looking at this and for any help you can suggest.



  • 7.  RE: BOXI Report - Multiple Lines

    Posted Jun 12, 2014 12:18 AM

    What is the database type? - Thanks



  • 8.  RE: BOXI Report - Multiple Lines

    Posted Jun 12, 2014 10:45 AM

    CA SDM 12.6 Cum#3

    Windows 2008R2

    MS SQL



  • 9.  Re: RE: BOXI Report - Multiple Lines

    Posted Jul 10, 2014 03:53 AM

    Hello V.Venkat,

     

    Do you have any further comments for Mike?

     

    Thanks, Kyle_R.



  • 10.  Re: RE: BOXI Report - Multiple Lines

    Posted Jul 10, 2014 06:40 AM

    Please don't mark this as 'resolved' yet -- We're also having issues with multiple lines per ticket showing in a report that joins the call_req and activity tables, once a formula on the activity table is included...



  • 11.  Re: BOXI Report - Multiple Lines

    Posted Jul 10, 2014 11:43 PM

    Hello tzadell,

     

    This thread won't be marked as "Assumed Answered." I only do that if it looks like a response has been given and the thread has gone quiet, but I typically mark it with an "ADMIN" flag text if that is the case so you'll know. The thread originator you can always reverse these. And the thread originator (Mike) is responsible for deciding whether something gets the "Correct Answer" flag or not.

     

    Having said that, it looks like the main early contributors have dropped off. Sorry about that. Can anyone else have a look at the thread here?

     

    Maybe you could put in a recap at this point to make it easier for new people to engage with the questions? I'm not sure that people would know what is required from the last post alone. If the queries are very different to the original thread, please log out separately.

     

    Thanks, Kyle_R.



  • 12.  Re: BOXI Report - Multiple Lines

    Posted Jul 11, 2014 06:22 AM

    Thanks, Kyle.  The recap is that our person who handles reporting is also having an issue with a BOXI report that seems to be the same as this original post.  The report has the call_req table and activity table joined, and we need to save the report in the CSV format.  When she just pulls in several basic field values from the call_req table, all of the fields appear in one line.  But when she starts adding fields that are the results of formulas ---- (the same as Mike:  '...create a variable testing for a certain Action Description and if present display something in an additional field') --- we are also getting multiple lines for each ticket in the report.  I've sent her a link to this thread, but she's on vacation until Monday so won't be able to join the conversation until then.  But I've been keeping my eye on this post for possible solutions!

     

    Thanks,

    Tammy



  • 13.  Re: BOXI Report - Multiple Lines

    Posted Jul 11, 2014 10:55 AM

    UPDATE:

     

    I have removed the requirement for testing the time between activities to be greater than 7 days. I just couldn't figure out a way to get it since all activities timestamp is called exactly that - 'timestamp'. So I'm exporting to Excel and processing that part in a macro within the spreadsheet.

    The original issue of getting multiple lines per ticket is sitll happening. As Tammy stated, when I remove the join to the act_log and just pull data from the call_req there is no issue. Single line per ticket. To further test this, I removed the call_req and just pulled data from the act_log and it was also fine on its own. The issue occurs when you join them and try to pull data from both. At this point, if this can't get solved I figure I will just create 2 separate reports: 1 for call_req and 1 for act_log. Then grab the columns from each that I need into a spreadhseet and manipulate the data there. Certainly NOT the most efficient, but at least I can produce results I can feel confident passing on to management.

     

    If anyone has any further suggestions, I'm quite open to trying anything.


    Thanks

    Mike



  • 14.  Re: BOXI Report - Multiple Lines

    Broadcom Employee
    Posted Jul 11, 2014 11:35 AM

    Hi Mike and Tammy,

     

    1) Considering the original requirement, I think what you see below  is expected.   The formula/filtering that you applied here would be on the Column and not Rows, if I'm understanding this correctly.  So, what the report shows now is,  if there's a matching string1 it shows the string2 you want it to display,  if it doesn't find a match it shows an empty cell.

     

    Ref_NumSummary
    15663This is a sample ticketSurvey Sent and Received
    15663This is a sample ticket

     

     

    2) Report showing OK data but export to CSV showing duplicates.  Depending on the query though native report may issue only one main query to fetch data, for CSV BOXI may end up issuing separate queries against the d/b,  and so, export to CSV could have duplicate records.  This should not happen for all reports from what I know.  SAP techs confirmed that the above behavior is a limitation of the way CSV exports work

     

    3) If you are joining objects from two different classes (tables),  a manual join would normally make it show unique records (of course matching the filter criteria).   For example, if you want to pull some objects from incident detail and you want to display all act_log recordds.         incident details -->  ref_num, assignee_userid, status   AND   incident activity --> analyst_userid,  last_mod.                     You then need to create a join forcibly between these two classes,  by dragging   incident details --> persistent_id     Equal_TO    incident activity --> call_req_id.

     

    If this is not done, you may end up seeing a call_req record with all act_log records, and it repeats for each call_req record.

     

    4) Assuming (3) was taken care of, if you still have duplicates then its probably because of uniqueness.  Maybe certain criteria you're using is infact not unique and that's why the duplicate record shows up.  I've seen some customers edit the query and manually the query to a custom query to have a left outer join or right outer join or what ever criteria that they really wanted there.  That might be an option somehow?

     

    _R



  • 15.  Re: BOXI Report - Multiple Lines

    Posted Jul 11, 2014 11:57 AM


    First I can say, #3 was not done. I did not forcibly join call_req and act_log in the Designer. The join I am doing is in the SQL of the query within the report. If the manual join is required I can try that this weekend.

     

    Your point #2 does not apply. The only display I am concerned with is within BOXI. I have not exported to a CSV yet to see what I get. My thought is if I'm getting multiple lines in the report writer then there's no point in exporting it.

     

    Your point #1 is only accurate to the extent that it is showing a line for each TRUE result. So first, the blank cell is the ticket that qualifies for the original criteria (call_req). The second cell is whether there was a survey "sent" to the customer (act_log) and the third requirement is whether a survey was received back from the customer (act_log). It's not keeping any uniqueness at all.

     

    Regarding the join, isn't it the same thing if I do that join in the query set of the report? I have the following in the SQL query:

                        AND

                           cr.persistent_id  =  alg.call_req_id

     

    Thanks for your reply Raghu and any help you can provide.

    Mike



  • 16.  Re: BOXI Report - Multiple Lines

    Broadcom Employee
    Posted Jul 11, 2014 01:05 PM

    Well, if your query already has  the join, then another explicit join is not needed.  So, I think you're safe about (3) above.

     

    About (1)  I can see the behavior myself too.  Given the logic that we were applying a filter on a column, I was thinking that it makes sense that its showing empty value when there's no match.  Let me check if there are any options that are available to hide the record itself  when a matching value doesn't exist.   Am I correct in understanding that it is what you're looking for ?

     

    In this case the duplicate records do show up with in the main report,  so you are correct, exporting to CSV showing duplicates  is not of a concern on this yet.  That was geared more towards Tammy's concern.

     

    _R



  • 17.  Re: BOXI Report - Multiple Lines

    Posted Jul 11, 2014 01:09 PM


    Yes that is what I'm looking for. Each row should only have 1 entry with 3 possible characteristics. Either just the basic data OR the basic data plus the survey 'sent' message OR the basic data and the 'survey sent/received' message.



  • 18.  Re: BOXI Report - Multiple Lines

    Broadcom Employee
    Posted Jul 22, 2014 11:50 AM

    Sorry about the delay folks, I checked some external threads about the same requirement too. There are two options that might help here:

     

    1) One option that some report developers were proposing was to have a filter based off what we want to display. 

    Example: =If Match([Action Desc];"*completed*") Then "Survey Sent and Received"

    Now create a Filter based on "Survey Sent and Received"  so the records will show up only if it has a value  and wont if it doesn't. 

     

    2) Other idea was to create appropriate clause/search criteria as a Universe object itself, so its already in the query.  This also means that the load of processing is shifted from reporting processes to  SDM application/database.



  • 19.  Re: BOXI Report - Multiple Lines

    Posted Jul 25, 2014 10:31 AM

    Thanks Raghu. Option #1 seems to be helpful and the most efficient for me to implement. Thank you very much.



  • 20.  Re: BOXI Report - Multiple Lines

    Posted Jul 30, 2014 03:52 AM

    Thanks Mike,

     

    As the thread originator, could you please flag Raghu's post as "Correct Answer" if that's what you're going with?

     

    Thanks, Kyle_R.

    Admin.



  • 21.  Re: BOXI Report - Multiple Lines

    Posted Jul 11, 2014 04:51 PM

    Thanks, Raghu.  I'm sorry that you're now dealing with 2 similar (but slightly different) things in the same post.  (I didn't mean to but in on your post, Mike!)

     

    I'll wait for Joyce to add more detail when she's back from vacation, but here's what I know about your 3 points:

     

    #1 - I believe that Joyce is doing a count of the # of times a particular activity occurs (such as # of Transfers).  So yes, I think that when there isn't any transfers, it's probably producing a blank cell.  But she could probably get around that by making her formula say that if there aren't any, then 0 -- that way there is always a value to present.  Right?

    #2 - For this particular report, CSV is our only option that I'm aware of.  The data in the report is being loaded into SQL tables in a separate tool, to be manipulated and displayed on a portal.  So it sounds like we're not going to be able to resolve the duplicate lines in a CSV format?

     

    #3 - This sounds like the 'distinct' join that Joyce has been trying to do.  There's an option in BOXI to do a distinct join, but that didn't appear to be resolving it.  So she is currently waiting to work with our DBAs to try to get the right SQL syntax to join the appropriate fields from one table to another.  (I was under the impression that this needed to be done on more than one field - is that not correct?)

     

    However, if #2 is true and this won't work for a CSV format, then it sounds like she shouldn't even bother with #3!

     

    Tammy



  • 22.  Re: BOXI Report - Multiple Lines

    Posted Jul 16, 2014 08:41 AM

    Hi everyone,

     

    Tammy wanted me to join this conversation as well.  I have the same problem with duplicate rows when I try to add a formula to the report that is accessing objects from the activity log and call request tables.  The duplicate rows are showing on the BOXI report as well as when the report is saved to either a .csv or a .xls (Excel) format.  The query is using a join of the activity log and the call-req tables.  Is the default join considered an outer join or an inner join?




  • 23.  Re: BOXI Report - Multiple Lines

    Posted Sep 17, 2014 09:53 AM


    Hi - This multiple line issue has come up again for us and we need to have a resolution asap.  The report I'm creating is being uploaded to a IT Portal for IT Team to use for their team metrics, so this data needs to be as accurate as possible.  We cannot have duplicate rows being pushed to this Portal.  Any help would be appriciated.

     

    Thanks,

     

    Joyce



  • 24.  Re: BOXI Report - Multiple Lines

    Posted Oct 14, 2015 04:50 PM

    what type of table are you using to display data?  if horizontal give it a try with crosstab and let me know.



  • 25.  Re: BOXI Report - Multiple Lines

    Posted Oct 15, 2015 09:03 AM

    Hi Sudip,

     

    Thanks for responding.  The table I'm using is horizontal. I'll see if I get the multiple line after switching to the crosstab format.  I will let you know the results.

     

    Thanks,

     

    Joyce



  • 26.  Re: BOXI Report - Multiple Lines

    Posted Oct 15, 2015 12:56 PM

    Hi Sudip,

     

    The horizontal table I'm using is very wide because of all the different object that are being returned, so I don't think I can change the table to a crosstab format.  The screen print below shows a small area of the table display:

     

     

    Thanks,

     

    Joyce



  • 27.  Re: BOXI Report - Multiple Lines

    Posted Oct 15, 2015 01:26 PM

    extra axle.jpg

     

    Change Order Report.jpg

     

    Both are cross tab reports, first one formatted it to look like horizontal. it contains 26 columns. page size is A2. Second one looks like Service Desk web page for Change Order displays all the OOTB+Z fields

    I got another one which has 50+ Issue Category properties and each Property column parsed using report dimensions and displayed on a crosstab report page size A1.

     

    Am I missing anything?