Create simple report that lists only one row per unique keyword value.

[Tim_Chrapko]

I'm trying to create a report for a group of users so that they always have a current list of officers in the company. Initially I created a simple keyword-based data provider to look at one document type and feed into a grid the officer's name, effective date, and termination date. I also added a filter to remove any blank line items. The result is very close to what I need, but because the system receives multiple documents of this doc type for each officer I end up with a row for each document in the system. 

How can I re-design or filter so that I only get one row for each unique officer name keyword value?

[aboucher]

HI @Tim Chrapko,

You can try creating a Custom SQL Query Data Provider Type and write your own SQL query including a SELECT DISTINCT statement to remove duplicate officer name keyword values.

Refer to the Reporting Dashboards - OnBase Foundation EP5 Module Reference Guide on pages 186 - 203.

I hope this helps.

~Alan

[Tim_Chrapko]

This sounds promising, however I have no training with SQL. Might be relying on our vendor for this one and I can begin to learn.

Thanks!

[Mike_Walkuski]

Hey @Tim Chrapko,

Reporting Dashboards should automatically only show a single row for each unique set of values. I am thinking you may be seeing multiple because you have multiple columns that contain different values in the other columns. You can see this in my example below. When I include another Keyword or the Document ID, I get a row for each unique set. But when I have just the Description Keyword, I only get 1 row.

Using Description as my Keyword Type and the value of 'TEST' as the Keyword....

Because I am including the Document ID, each row will be truly unique so you can see I have 11 rows returning 1 document all with the same value.

If I choose a different Keyword as an additional column, there are some cases where multiple documents have the same set of Description and Alphanumeric Keywords, therefore, we only see 6 rows returned but the count is higher for some of them.

Now if I only include the Description Keyword, I will only get 1 row returned with a count of 11.

Do you have any more information or screenshots you can provide to better demonstrate what you are seeing?

[Tim_Chrapko]

Let's see if I can clarify well enough to make sense. 

At present, reporting on the values in 'Effective Date,' 'Officer Name,' and 'Termination Date' results in a line for each document in the system. In the screenshot below, every line shown is a different document related to the same Officer.

I want just one line per officer so that my user can have a simple report that shows each officer and their effective & termination dates. We don't care what document/s has the keyword value for these three pieces of information. If we index another document for this officer that doesn't have an effective date value, but does have a termination date value, then we simply want the termination date added to the one line for that officer.

This is the most stripped down report we might use. I would eventually like to accomplish the same thing in scenarios that involve more doc types and KW values.

Thank you for your earlier response.