Is it possible to "SELECT DISTINCT" in an expression, to prevent duplicate data?

[Ian]

I have two tables, one with the header Name, and the other showing the projects associated with each name. I want the Name table to show only one value for name, with all corresponding projects listed in the 2nd table. However, where there is more than one project per name there will be that many duplicate names, which I don't want.

It seems that this might normally be solved by grouping, however if I group these table values together I get repeating table-headers that I don't want also.

So is it possible to prevent duplicate data in an expression, rather than grouping? Where my current expression for the name is:

= Fields.[Name]

...these are my pseudo-code ideas for expressions to solve this:

• = Fields.[Name] DISTINCT

• = Fields.[Name] WHERE Fields.[Name] <> Previous(Fields.[Name])

[Ian]

Actually, I just came close with the help of this Telerik forum answer, with my expression being:

= IIf(RowNumber("the name of my name table") = 1, Fields.[Name], Null)

The problem with this though, is that the Null value is being displayed as a new-line and so there is extra unwanted space. If there are a lot of duplicate values, then there is a lot of extra space. Also the empty String "" instead of Null has the same effect.

[Nasko]

Hello Ian,

It is possible to achieve the desired layout using grouping, or using nested data items. 
I created a sample report demonstrating the latter approach, which you can find attached.
The nested data items are connected using a technique described in the How to use the ReportItem.DataObject property in expressions help article.


Regards,
Nasko
Telerik

 

 

[Ian]

Ah, that was easier than I thought, thanks Nasko!

There's a few issues I'm now having with this NestedTables idea:

• the Name header doesn't always stay with the Name field on page break (the Name header can be at the bottom of one page, and the Name field at the top of the next page)
• ColumnHeadersPrintOnEveryPage = True isn't working on my table

I have my textboxes and table in a group, instead of in the details section as in your attached NestedTables.trdx file, because I need to sort the names and group the group the project table.

So about the first issue, I have tried setting my group to KeepTogether = True and PrintOnEveryPage = True without any luck. I have also tried to anchor or dock the textboxes to the top, but wasn't helping.

Do I need to somehow bind the textboxes to each other, and maybe to the table too?

[Nasko]

Hello Ian,

The ColumnHeaderPrintOnEveryPage property works for the Table item when it has headers. However, it doesn't work in the provided sample, since a List item with specific structure without headers is used. Thus, there is nothing to repeat on each page.
You can change the report structure to use Table items with headers. This approach is demonstrated in the attached report.

The Name header and the fields can be kept on a single page via the GroupKeepTogether property, as shown in this screenshot.

Regards,
Nasko
Telerik