Flatten data inside a crystal report

Question

Ok, this might be a weird request, but is it possible to essentially flatten my dataset inside a crystal report?

I have a datatable in C# that was created with a join, so when it hits the report its 2 records. Most of the columns have identical data, with the last few displaying a different address.

Instead of printing the detail section multiple times with mostly similar data, I need to display 1 'record' with the common data printed once, and each records address arranged next it. As in, all the common fields displayed in one area, and then next to that the address fields from the record where 'AddressType = 1', then next to that the address fields where 'AddressType = 2'

Is this a subreport thing? Because even with subreports I can't get it to only print 1 detail section with the data from just the first record.

Is this even possible with crystal? For long drawn out reasons, I can't flatten the data before it gets to the report.

Answer 1

Ok, someone here in the office showed me the way, so I'll put this out there.

Given data with cols A,B,C all common and D,E different across multiple rows, this is how I 'flattened' the dataset in crystal:

1. Create a group based on col A, and put A,B,C inside that group header - get rid of the details section entirely
2. Create a subreport in the group header for each row of data, in my case 2 subreports
3. Inside each subreport, put fields D,E. Important: There are NO links for these subreports!
4. For each subreport go into the Select Expert and create a condition that shows only 1 particular row of data. This conditional will have the same field for each subreport, but different values. In my case it was AddressType='A', and AddressType='B'

This will produce 1 report, with A,B,C listed once, and D,E listed once for each subreport(once for each row of data)

This was confusing, time consuming, and I hate crystal reports now more than ever.

Answer 2

It would be pretty ugly, but you could add a group for each common field in Group Expert and then display the data for the common fields in the last group header. So if your common fields are field1 through field5, you would create five groups and put all five fields in the group header of field5. Then you would put the unique address fields (call them field field6 through field8) in the details section.

Now the trick is getting everything to line up correctly. You can set "underlay following sections" on the group header for field5; this will cause field1 through field5 to "fall down" into the details section. You just need to make sure that field6 through field8 are all to the right of field1 through field5 so the text does not overlap.

Now, if you want the two address records to print horizontally, I think you will need a subreport with multiple columns for that. But the same principle applies -- just make sure the subreport is to the right of field1 through field5 so the data doesn't overlap.

Answer 3

Have you tried the suppress if duplicated option on each non-address field?

Otherwise, you could group by the common id, put the common fields in that header, and then display the multiple addresses in the details section.

Or, you could remove the addresses from the datasource and use a subreport to fetch this data for each record. This would bypass the join and be the slowest option performance wise, due to having to select the addresses for each record.

Answer 4

Ok, firstly let's see if I understand this right :

You want a report that would be in the format

MainDetails Address(type1)
MainDetails Address(type2)

to instead be in the format

MainDetails Address(type1) Address(type2)

?

Assuming there are only two address types, you can do the following :

1) Group by Main details (whatever the unique entry is
2) Put the address details in the group header next to them, on the right
3) Also put the address details in the details section, but positioned as if they were in the Address(type2) column positions, so it looks like :

GH MainDetails Address
D.......................................Address

4) Next, add a sort to the report on the AddressType field, so that AddressType=1 shows first.
5) Add a conditional suppression formula to the Details section saying {AddressType=1}
6) Using the Section Expert, in the Group Header tick the 'underlay following sections' box

This should work as long as the number of addresses is either 1 or 2.