Rolling Up Duplicate Records

Good afternoon! Hope I can explain this well enough for some guidance. I am writing a report that joins items from several different table, and quite of few of those have many to one relationships to the parent type.

I have supress duplicates selected on all my columns, yet the values still must repeat because due to their relationships to the items that return the largest number of members (in this case Programming Language and Language Precedence).

Items returning multiple results:

? Subject Matter Expert (2 members)
? Programming Langauge/Language Precedence (4 members each)
? Application Server (2 members)
? Sending App Name (2 members)
? Application Subsystem (2 members)

If you multiply the numbers you get 64, which is the number of data rows in the example table, which is partially displayed in the attachment.

My question is, in BIRT can I rollup all these repeats to where my result set displays a maximum of 4 rows, rather than the 64 returned by the multiplicative product of the query?

Thank you in advance.

Find more posts tagged with

Comments

AustinAlec

Attached would be my ideal output for this report/spreadsheet. Thank you again.

mwilliams

So, you just want to list the unique values for each column? There is no alignment necessary across the columns? You just need each column compressed up to its minimum number of rows with no repeating values? Is this correct?

AustinAlec

Yes, that is correct Michael. Thank you.

BTW, new to BIRT but been lurking on the forum for awhile.

mwilliams

How do you do your joins? With BIRT or outside of BIRT? If inside BIRT, what values are in what dataSet? In your first attached image, you have suppress duplicates enabled, correct? So, in reality, there are repeating values in each blank spot? Sorry for all the questions, I've got a couple different ideas, but want to know exactly how your data is available to you within BIRT.

If you can simply recreate what you're doing with scripted dataSets or with the sample database, that'd be helpful to kinda see what your data is like.

mwilliams

Oh, and welcome to the community! Glad to have you as a member!

AustinAlec

Michael,

I wrote a SQL script and used it as the data set and moved the columns from the data set over to the columns in a list report. Also, the Language Precedence column is the ordering hint in the SQL. Supress repeats is indeed enabled on each column, so yes, all the blanks you see are supressed values.

Thanks again!

mwilliams

So, you're using a scripted dataSet to write a single query? Couldn't you just manipulate your result set in script before assigning your rows? My suggestion was going to be to create a scripted dataSet to compress your data or to see if you could create a more complex query that could do the work for you, if you were using SQL.

AustinAlec

Hi Michael,

I am going to try to provide all this information via subreports. Thanks for the suggestion.

AustinAlec

Problem solved. I built two data sets: one with the columns not involved in joins and the other with the columns that are involved in the joins plus one common attribute from the first data set. I then built a joint data set based on the one common attribute and then selected the "Suppress Repeats" property on each column. This gave me a data set that was compressed to the fewest rows possible.

Thanks for your indulgence!

mwilliams

Awesome! Glad you got it working the way you want! Let us know whenever you have questions!