Subreport data not grouping correctly

[Former Member]

Hi-

I'm troubleshooting my first Crystal Reports report, working with a main report that has a subreport that is intended to display all part information for a given purchase order number. The subreport displays the individual part records for the purchase order number, and the parts are meant to be grouped by part ID, then date specified on the part, then price.

For example, part ID 123 appears on a purchase order 4 times:

1. Date: 1/1/16, Price: 1.00

2. Date: 1/1/16, Price: 1.50

3. Date: 2/3/16, Price: 1.00

4. Date: 2/3/16, Price: 1.00

Using the example, the part should be displayed in 3 groups on the purchase order, with items 3 and 4 grouped together. Each group should be assigned an item number. Using the example, following is the desired result:

Item 1 - 1/1/16 - 1.00

Item 2 - 1/1/16 - 1.50

Item 3 - 2/3/16 - 2.00

Following is what is happening:

Item 1 - 1/1/16, Price: 1.00

Item 2 - 1/1/16, Price: 1.50

Item 3 - 2/3/16, Price: 1.00

Item 4 - 2/3/16, Price: 1.00

The following code is specified in a formula on the main report:

whileprintingrecords;

stringvar    array polinepart;

datevar      array polinedate;

numbervar    array polineprice;

booleanvar supress := false;

local numbervar x := ubound(polinepart);

local numbervar i;

if not(onfirstrecord) and {PurchaseOrderDetail.PurchaseOrderID} = previous({PurchaseOrderDetail.PurchaseOrderID})

then

    (

        for i := 1 to x do

        (

            if

                polinepart[i] = trim({PurchaseOrderDetail.PartID}) & '[]' & trim({PurchaseOrderDetail.PartRevisionID})

                and

                polinedate[i] = {PurchaseOrderDetail.Date}

                and

                polineprice[i] = {PurchaseOrderDetail.Price}

            then

            (

                supress := true;

                exit for;

            )

        )

    )

;

if not supress

then

    (

        redim preserve polinepart[x + 1];

        redim preserve polinedate[x + 1];

        redim preserve polineprice[x + 1];

        polinepart[x + 1] := trim({PurchaseOrderDetail.PartID}) & '[]' & trim({PurchaseOrderDetail.PartRevisionID});

        polinedate[x + 1] := date({PurchaseOrderDetail.Date});

        polineprice[x + 1] := {PurchaseOrderDetail.Price};

    )

The variable 'supress' is used to determine whether or not to display the next sequential item number for the records returned from the subreport. Following is the code behind the item number that appears on the main report:

whileprintingrecords;

booleanvar supress;

numbervar itemnumber;

if not supress

then itemnumber := itemnumber + 1

The issue was "resolved" by suppressing the group header on the subreport, which isn't desirable. The group header contains column headers for the columns in the subreport, and all but 1 column are text fields; the other column is a formula field. The group headers should print once for each group.

As a test, I removed everything in the subreport's group header and unchecked the Suppress switch on the group header, and again noticed that the report wasn't grouping. If I suppress the group header, the report appears to group correctly.

My assumption as to why this is happening is because the code to group by item number isn't aware of the grouping that is specified on the subreport. If I'm correct, my plan was to programmatically display group headers on the subreport, but I don't know where to start to accomplish this. In the current report setup, I can't see how to employ group headers without redoing the logic in the report.

Am I on the right track with this? If not, can you tell me where I'm falling down, or suggest a better approach to accomplish the goal?

Thank you for any input.

[Former Member · Accepted solution]

EDIT: This issue has been further clarified as follows. I apologize for any confusion, etc.

Detail records (a PartID or group of PartID's) are grouped and assigned a sequential number ('Item Number') using a Running Total field, and sorted by PartID. When new PartID's are added or removed and that aren't grouped, there is naturally a good chance that the Item Number previously assigned to a detail record will get updated due to sorting. It's desired that a given Item Number always refer to the same detail record. For example, if a detail record was initially assigned Item Number 3, the detail record should always be assigned Item Number 3.

Our vendor references the Item Number and when this changes due to updates it causes issues for them.

Does anyone know how, if at all possible, to assign a number (Item Number) to a detail record within a report at runtime that won't change due to changes made to the data within the report?

Thank you for any feedback, and again I apologize for any confusion.

[abhilash_kumar · Accepted solution]

Hi William,

I don't see a way to this in CR.

You'd need a database table that keeps track of the Item Number for each part.

-Abhilash

[Former Member · Accepted solution]

Thanks for your help Abhilash.

[DellSC]

Is there a particular reason why you need a subreport for this instead of putting the data in the main report?

-Dell

[Former Member]

Hi Dell-

There is no particular reason why things are done this way in the report, other than trying to fix what was started by someone else, and my current lack of experience with Crystal which seems to prevent me from exploring another way doing this currently.

The person who started this report is one of the software vendor's consultant trainers. I'm assuming that the path that he took is one that he may have taken previously and I'm hesitant to deviate. My brief discussion with him, and how quickly he produced the report, leads me to believe that he's worked with Crystal for a while. Attempts to reach him regarding this report have failed.

I've attached a snapshot that should illustrate the end goal, based on the required grouping. Column "headers" are shaded. As stated, the only way that I can get grouping to work in the current iteration is to suppress the 2nd level headers, which isn't a workable solution.

Is there another way to accomplish this without a Subreport?

[abhilash_kumar]

Hi William,

Would you be able to send the .rpt file 'with saved data' to my e-mail (my e-mail is in my profile)?

-Abhilash

[Former Member]

Hi Abhilash-

File sent to your email.

[abhilash_kumar]

Hi William,

Inside the subreport, where you want the group, create a formula with this code:

{Part#_field} & {Date_field} & {Price}

Go to the Group Expert and insert a group on this formula field.

Move all fields from the Details section to the Group Header or footer.

You should now see that the rows 3 & 4 are grouped together.

-Abhilash

[Former Member]

Hi Abhilash-

Thanks for the help. After implementing your suggestion by creating the formula, inserting a group based on the formula, then moving the fields from the Details section into either the GH or GF, I ended up with the same result where the items aren't grouping. All lines are printing without the expected grouping.

By suppressing all but one of the sections in the Subreport and moving the fields into the visible section (doesn't seem to matter if GH, Details or GF), I get the expected grouping. It appears that if more than 1 section is visible, grouping fails.

It seems like the fault is with the code block that determines when an Item number is called for, but I don't see where the visibility of a section would cause grouping to fail.

Any thoughts?

[Former Member]

UPDATE

I stand corrected. I've taken the code block out of the loop and the issue persists. Grouping is incorrect.

I apologize, I should have been clearer on the grouping issue. Specifically, to clarify "not grouping correctly", what's happening is that 2 of the Part ID's are duplicated.