Cognos Report - Adding attendee to the report

[mamehta]

Hello All,

I am using Cognos Report Studio to create a report with the following details,

Report ID, Employee ID, Name, Approval Status, Amounts and the Attendee details (Name and Title). Attendee details is stored in different table within Concur Warehouse however I do not see any primary key to be used as a Join. Without Join while creating the report, Cognos is automatically applying cross join and creating duplicate records. For Example, if my expense report has one attendee, Cognos is creating two records one with attendee name in it, one without. I am really not able to understand how to create a report without duplicate entries.

Any help will be appreciated.

Thank You

[KevinD · Accepted solution]

@mamehta There are two solutions to this:

1. Create a repeater table report. I'm not sure how to do this, but I've seen people on our Reporting team do it. 

2. Create a Master/Detail report. This one to me is much easier. 

Have you attended our free Intelligence reporting training? One of the examples we go over is building a report with attendees. You can't beat the price of this class, right? 

The class also provides a good foundation of using our reporting tools. You can find a list of our upcoming Intelligence Webinars here: https://www.concurtraining.com/pr/reporting/intelligence.

Now, I know you might be saying, "I don't want to wait a couple weeks to know how to do this", which it totally understandable. 

You can download our training manual and go to page 174 to see the example and the steps of how to create such a report. 

The manual is found here using the link I provided above under the Download the Manuals section. 

Kevin

[mab]

HI, 

I've been working on data cleanup for our attendees, and could not find the external Id in the config table for attendees, so I had to create a join.  this is how I did it-- using the config table as #1 and Expense table as # 2 joines on attendee legacy key  1:1 = 1:1

It's not bringing me as many attendees as I have updated, I suppose that is because only the attendees used in expense are in those tables. 

Wish I could find external id in the config table of attendees.

[KevinD]

@mab I did a search for External ID in the Configuration area of the data warehouse and there were no results, so you won't be able to find it there. I also looked at the Configuration report and found External ID, but it only applied to HCP Attendees. 

Can you provide me the name of your company so I can go into your site and look at your company's configuration report and see if External ID is being used on any other Attendee forms? 

[mab]

Hi Kevin, 

We only import external id's with HCP and MASHCP, and do not auto-assign or use for user created attendees.  Would it make a difference?

[KevinD]

@mab I don't think it would make a difference. 

So, I know you are said you are trying to clean up your attendees, but why are you using a Cognos report when cleaning up attendees is done within the SAP Concur tool?

[mab]

Hi Kevin, 

I was finding a lot of duplicate attendees, and I found out that the provider of the update was creating some of those, by merging bad into the good.  I was not aware until recently.  Some of the data issues are the result of charges in their process, some are user errors. 

The list of winners, (keepers) and losers (duplicates and bad info) was provided by the number assigned to custom 25 field and also  attendee name and custon 14.  Many of the records do not have custom 25 populated, have duplicates in custom 14, or blanks and many people have the same name, or some were due to misspellings.  All of the records have an external id.  

I had over 3800 merged ids, for 2020 alone, going 1 by one was not doable, and creating an import to deactivate required the ext. id.  I wanted to make sure that when deactived I left ext id, name, custom 14 and 18 in the records. The best way to do that was creating a report to ensure that I was deactivating the same fields I was given.  It also provided an opportunity to pull records with blank or duplicate custom 14, blank custom 25 (we use for sunshine reporting). 

And yet, I still found one today.  The problem is I have 18k attendees that have been used in expense over the last 5 years, and have on multiple occassions added >50k records of HCPs at a time.  So, what that tells me is that while I have been able to cleanse a few hundred that were in expense, I likely have many more in the config table that have been imported but I cannot see because they do not join to the used records, and I have not been able to pull a report to review, and if I do there is not external id.

So that's why I used a report.

[mab]

It's only in the default hcp attendee form

[mab]

Hi Kevin, It's Relypsa, and I don't believe we are using on other forms