Skip to Content

Subreport data not grouping correctly

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    Posted on Jul 18, 2016 at 08:28 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 09, 2016 at 03:50 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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.

  • Posted on Jun 09, 2016 at 01:24 PM

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

    -Dell

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.