Crystal Reports, Link Subreport To Date Group

Question

I am trying to create a report to show our overall quality tending over time by month or quarter (over a span of several years). The main report will show all of our shipments over the specified time period grouped by month/quarter, and the sub report will show all returns entered during that month/quarter that result in scrap or rework.

The issue I am having is linking the sub report to the date group in the main report. After several attempts using different methods, the best I could do is show all returns within the date parameter in every group footer.

This is my first time posting, so I'm not sure what additional info will be needed to assist. Please let me know if there is more needed.

*Edit for additional info

I would like the subreport to show data for each month or quarter in the given time period. So if the date range of the report is for 1/1/2018 - 9/30/2018 and the data is grouped by month, I would like the subreport to show only the data from each month group. General layout below.

January
Shipments
Returns (subreport showing data for January)

February
Shipments
Returns (subreport showing data for February)

*Second edit to add screenshot and more info

I stripped all the parameters from the subreport because I couldn't get any of them to work. The only parameter on the main report is the Date Range that prompts users for a start and end date.

The main report has the shipments in the details section.

Current layout below. If I can get this current issue resolved, I will be adding values passed up from the subreport to calculate the quality rating for each month, then passing them back down to a second subreport to summarize and chart.

Quality Trend Layout

*Third edit for data source and example data

The main and subreports are pulled from tables in our company database.

SQL query used for the main report. Only the "Releases" table is used to show how many and when each part was shipped. DelType=0 is specifying a customer delivery. The date range is defined by a user entered parameter.

Main Report

SELECT 
"Releases"."DateComplete",  
"Releases"."DelType",  
"Releases"."PartNo",  
"Releases"."Qty",  
"Releases"."JobNo",  
"Releases"."PartDesc"  

FROM   "COMPANY"."dbo"."Releases" "Releases"  

WHERE  "Releases"."DelType"=0 AND  
("Releases"."DateComplete">={ts '2018-01-01 00:00:00'} AND  
"Releases"."DateComplete"<{ts '2018-10-01 00:00:00'})  

I am trying to use CustReturn.DateEnt as the datetime link to Releases.DateComplete in the main report (not currently linked at a paramter because it didn't work), and only select records that are customer returns resulting in rework, scrap, sort, or repair.

Subreport

SELECT 
"CustReturn"."DateEnt",  
"CustReturn"."CustRMANo",  
"CustReturnDet"."OrigJobNo",  
"CustReturnDet"."PartNo",  
"CustReturnDet"."QtyReturned",  
"CustReturnDet"."QtyToRework",  
"CustReturnDet"."QtyToRestock",  
"NonConformance"."Disposition",  
"NonConformance"."ReturnType",  
"CustReturn"."IssueDate",  
"NonConformance"."NonConfDate",  
"CustReturnDet"."PartDesc"  

FROM   
("COMPANY"."dbo"."CustReturn" "CustReturn" INNER JOIN "COMPANY"."dbo"."CustReturnDet" "CustReturnDet" ON "CustReturn"."CustRMANo"="CustReturnDet"."CustRMANo")    
LEFT OUTER JOIN "COMPANY"."dbo"."NonConformance" "NonConformance" ON "CustReturnDet"."NonConfNo"="NonConformance"."NonConfNo"

WHERE  ("NonConformance"."Disposition"='REPAIR' OR  
"NonConformance"."Disposition"='REWORK' OR  
"NonConformance"."Disposition"='SCRAP' OR  
"NonConformance"."Disposition"='SORT') AND  
"NonConformance"."ReturnType"='CUSTOMER'

Quality Trend Example Data

Answer 1

First, I want to give a big thanks to Digital.Aaron. Your help in solving this is greatly appreciated.

Your answer was quite close to what I needed. I was still unable to get the subreport to show any data after adding the additional lines to the SQL statement, so I tried something a little different. I made the additional statements into formula fields.

Main Report Field

DATEADD("D", -1*(DATEPART("D",{Releases.DateComplete})-1),{Releases.DateComplete})

Subreport Field

DATEADD("D", -1*(DATEPART("D",{CustReturn.DateEnt})-1),{CustReturn.DateEnt})

I used these fields as the link between the main report and subreport, but still couldn't get the data to show. The issue turned out to be setting them as = to each other in the record select formula of the subreport. I named the formulas DatePeriod

Original Record Select

{@DatePeriod} = {?Pm-@DatePeriod}

Modified Record Select

{@DatePeriod} in Date({?Pm-@DatePeriod})

Once I made the change, everything fell into place.

Thanks again,

Jeff

Answer 2

So it looks like you're grouping on Releases.DateComplete. Let's assume this value is the same for all records associated with a given month. Let's also assume the following (simplified) sample data for Shipments:

ShipmentDate | PartNo | Qty | DateComplete
01/01/2018   | 0001   | 1   | 01/31/2018
01/05/2018   | 0031   | 10  | 01/31/2018
01/31/2018   | A314   | 4   | 01/31/2018

Your Returns data would then need to look something like this:

ReturnDate   | PartNo | Qty | DateComplete
01/15/2018   | 0031   | 7   | 01/31/2018

Notice they both have a DateComplete column.

Now in your Crystal Report template, you'll use the DateComplete field from the main report results set as the input to your subreport parameter. Your design layout looks like it would be correct here, as you'll want the subreport to be called in the group footer.

EDIT: So it looks like DateComplete is not the same for all records in a given month. That's fine. We're going to add a column to both the main query and the subreport query that WILL be the same for all records in a given month, and that we can then use to link the records.

Your main query would become:

SELECT 
    Releases.DateComplete,  
    Releases.DelType,  
    Releases.PartNo,  
    Releases.Qty,  
    Releases.JobNo,  
    Releases.PartDesc,
    DatePeriod = DATEADD(DAY, -1*(DATEPART(DAY,Releases.DateComplete)-1),Releases.DateComplete) 
FROM   COMPANY.dbo.Releases Releases  
WHERE  Releases.DelType=0 
    AND (Releases.DateComplete>={ts '2018-01-01 00:00:00'} AND  Releases.DateComplete<{ts '2018-10-01 00:00:00'}) 

Your subreport query would then become:

SELECT 
    CustReturn.DateEnt,  
    CustReturn.CustRMANo,  
    CustReturnDet.OrigJobNo,  
    CustReturnDet.PartNo,  
    CustReturnDet.QtyReturned,  
    CustReturnDet.QtyToRework,  
    CustReturnDet.QtyToRestock,  
    NonConformance.Disposition,  
    NonConformance.ReturnType,  
    CustReturn.IssueDate,  
    NonConformance.NonConfDate,  
    CustReturnDet.PartDesc,
    DatePeriod = DATEADD(DAY, -1*(DATEPART(DAY,CustReturn.DateEnt)-1),CustReturn.DateEnt)  
FROM COMPANY.dbo.CustReturn CustReturn 
INNER JOIN COMPANY.dbo.CustReturnDet CustReturnDet ON CustReturn.CustRMANo = CustReturnDet.CustRMANo    
LEFT OUTER JOIN COMPANY.dbo.NonConformance NonConformance ON CustReturnDet.NonConfNo = NonConformance.NonConfNo
WHERE  NonConformance.ReturnType='CUSTOMER'
AND (   
        NonConformance.Disposition='REPAIR' 
        OR NonConformance.Disposition='REWORK' 
        OR NonConformance.Disposition='SCRAP' 
        OR NonConformance.Disposition='SORT'
    ) 

DatePeriod will always be the date of the first day of the month. Now we can use this as the Link field between the main report and the subreport. You could also consider making this the field that you Group on, instead of the month value.