Multiple Field grouping in Crystal report

Question

I have generated a SQL query which contains the information of Purchase & Production information.

Using this SQL query, I want to develop a crystal report layout with parameters set as data range.

If my client selects the data range he needs this Purchase Vs production report based on each production document in separate page.

This is how my SQL Query output looks like:

FLAG         DOC NO DOCUMENT DATE   VENDOR  ITEM    QTY PRICE   TOTAL
PURCHASE       1    10/11/2019       ABC    RM      100   10    1000
PRODUCTION     1    10/11/2019       NULL   FG001     50   5     250
PRODUCTION     1    10/11/2019       NULL   FG002     25  7.5   187.5
PURCHASE       2    11/10/2019       CBD    RM02      75   15   1125
PRODUCTION     2    11/10/2019       NULL   FG005     75   25   1875

I need to build a crystal report like this:

In first page, I should get the information of first production document: 1

Purchase:
PURCHASE    1   10/11/2019  ABC RM  100 10  1000

Production:
PRODUCTION  1   10/11/2019  NULL    FG001   50  5   250
PRODUCTION  1   10/11/2019  NULL    FG002   25  7.5 187.5

And in second page like the above format, I should get in the next page.

I used the grouping option & Grouped using the Flag field in a specified order.

But after providing the data range & run the report, I am getting the result like this:

Purchase:
PURCHASE    1   10/11/2019  ABC RM  100 10  1000
PURCHASE    2   11/10/2019  CBD RM02 75 15  1125

Production:
PRODUCTION  1   10/11/2019  NULL    FG001   50  5   250
PRODUCTION  1   10/11/2019  NULL    FG002   25  7.5 187.5
PRODUCTION  2   11/10/2019  NULL    FG005   75  25  1875

How do it spit the both document into separate page in Crystal report

Answer 1

If the "doc no" is the relationship between the "purchase" data and the "production" data, you should:

1. Group by "doc no"
2. Group by flag in that specific order (or just in descending order)
3. Put data in detail section

If you can't assume that relationship, than let your query order the data and do not group - just put the data in detail section. Or use group expert to tell it to do not reorder data.