Hopefully I can explain this clearly. I have been struggling with this for three days. I have a report I'm trying to create which is usually run monthly,
which is no problem to create. My problem is that I have been requested to run this monthly report by week for a time frame of three years 2011-2014. One of
the difficulties is that I am pulling data from two different tables which are somewhat unrelated. One table contains data for parts received from our
suppliers which I am filtering by "Date Received". The other table contains data on nonconformance reports written when parts that are received are found to
be bad. The report I'm working on calculates PPMs (Parts Per Million) which is calculated with a formula (Total Quantity Rejected / Total Quantity Received)
* 1,000,000. Our database system only counts a nonconformance against a supplier when the nonconformance report is finished and closed out, not when it's
created when the parts are received and inspected. This is where my problem lies. A part may be received, inspected and rejected in say, December of 2012
but the nonconformance report may not be closed out until say January 2013. If in my main report I pull all parts received for one month say Jan 1 - Jan 31,
2013 I can "SUM" the quantities received for that time frame to get the "Total Quantity Received" part of the formula. The only way I know to pull the
correct quantities for the "Total Quantity Rejected" part of the formula is to create a subreport which is filtered for "Nonconformance Closed Date" since
some of those records may have been created prior to Jan. 2013 I can't rely on the Jan. 1 - Jan. 31 filter from the "Date Received" field of the main report
to give me an accurate "SUM" of the "Total Quantity Rejected".
This is what I have so far:
In my main report I have created two groups:
"Group 1" is the "Client Number" (a number we have assigned to each of our suppliers) This will allow me to separate each of our hundreds of suppliers in my
report.
"Group 2" is a grouping selection formula I added so when I run the report a "Parameter Fields" box appears and allows me to choose "Month" or "Week" when I
run the report.
IF {?UserDefinedGrouping} = "Month" THEN
(DATEPART("m", {@DATE RECEIVED}))
ELSE IF {?UserDefinedGrouping} = "Week" THEN
(DATEPART("ww", {@DATE RECEIVED}))
Example: When I choose "Week" and run my report with a filter for a "Date Received" date range of 1/1/13 - 1/31/13 and a filter for just one "Client Number"
"001234" this is what I see in my main report:
PH CLIENT NUMBER DATE RECEIVED QTY RECEIVED
GH1
GH2
D 001234 1/2/2013 200
D 001234 1/2/2013 178
GF2 001234 1 378
GH2
D 001234 1/7/2013 21
D 001234 1/7/2013 60
GF2 001234 2 81
GH2
D 001234 1/14/2013 100
D 001234 1/14/2014 360
GF2 001234 3 460
GH2
D 001234 1/26/2013 20
D 001234 1/26/2013 520
GF2 001234 4 720
I "hide" everything but the GF2 so I only see one row for each week. The "001234" is the Group #1 Name field I moved to GF2. The "1", "2", "3", "4" on GF2
rows are the numbers for the "weeks" which is the "Group #2 Name". Next to that is the "SUM" for the Quantities Received for each of the weeks.
Subreport Example: Using filters for "Client Number" "001234" and a "CLOSE DATE" range of 1/1/13 - 1/31/13. Grouping for the subreport is the same as the main
report. My Subreport output looks like this:
PH NC# CLIENT NUMBER QTY REJECTED CLOSE DATE
GH1
GH2
D NC-123 001234 40 1/2/2013
D NC-124 001234 15 1/2/2013
GF2 1 55
GH2
D NC-125 001234 80 1/10/2013
D NC-126 001234 142 1/10/2013
GF2 2 222
GH2
D NC-127 001234 26 1/21/2013
GF2 4 26
I hide everything but GF2. The "1", "2" and "4" are the "Group #2 Name" field indicating weeks 1,2 and 4. To the right of that is the "SUM" of the "QTY
REJECTED" field for each week in Jan.
I created a shared variable to pass the "QTY REJECTED" SUMs from the subreport to the main report. I added the subreport to the main report GH#1 row. The
shared variable output is placed in the GF2 row.
My report looks like this:
PH CLIENT NUMBER DATE RECEIVED QTY RECEIVED
GH1 1 55
2 222 <----- Subreport
4 26
GH2
D 001234 1/2/2013 200
D 001234 1/2/2013 178
GF2 001234 1 378 26 <------ Subreport shared variable output
GH2
D 001234 1/7/2013 21
D 001234 1/7/2013 60
GF2 001234 2 81 26
GH2
D 001234 1/14/2013 100
D 001234 1/14/2014 360
GF2 001234 3 460 26
GH2
D 001234 1/26/2013 20
D 001234 1/26/2013 520
GF2 001234 4 720 26
What I need is the subreport to output it's SUMs for each week and display them on the GF2 row with the corresponding weeks in the main report:
Like this:
PH CLIENT NUMBER DATE RECEIVED QTY RECEIVED
GH1 1 55
2 222 <----- Subreport
4 26
GH2
D 001234 1/2/2013 200
D 001234 1/2/2013 178
GF2 001234 1 378 55 <------ Subreport shared variable output
GH2
D 001234 1/7/2013 21
D 001234 1/7/2013 60
GF2 001234 2 81 222
GH2
D 001234 1/14/2013 100
D 001234 1/14/2014 360
GF2 001234 3 460 0
GH2
D 001234 1/26/2013 20
D 001234 1/26/2013 520
GF2 001234 4 720 26
How can I get my sub report to display the sum for week 1 on the week 1 row in the main report, week 2 and week 4 on the associated rows? I can get it to
either show week #1 for all rows or week #4.
Hopefully you could follow all of that.
Thanks for your time.
which is no problem to create. My problem is that I have been requested to run this monthly report by week for a time frame of three years 2011-2014. One of
the difficulties is that I am pulling data from two different tables which are somewhat unrelated. One table contains data for parts received from our
suppliers which I am filtering by "Date Received". The other table contains data on nonconformance reports written when parts that are received are found to
be bad. The report I'm working on calculates PPMs (Parts Per Million) which is calculated with a formula (Total Quantity Rejected / Total Quantity Received)
* 1,000,000. Our database system only counts a nonconformance against a supplier when the nonconformance report is finished and closed out, not when it's
created when the parts are received and inspected. This is where my problem lies. A part may be received, inspected and rejected in say, December of 2012
but the nonconformance report may not be closed out until say January 2013. If in my main report I pull all parts received for one month say Jan 1 - Jan 31,
2013 I can "SUM" the quantities received for that time frame to get the "Total Quantity Received" part of the formula. The only way I know to pull the
correct quantities for the "Total Quantity Rejected" part of the formula is to create a subreport which is filtered for "Nonconformance Closed Date" since
some of those records may have been created prior to Jan. 2013 I can't rely on the Jan. 1 - Jan. 31 filter from the "Date Received" field of the main report
to give me an accurate "SUM" of the "Total Quantity Rejected".
This is what I have so far:
In my main report I have created two groups:
"Group 1" is the "Client Number" (a number we have assigned to each of our suppliers) This will allow me to separate each of our hundreds of suppliers in my
report.
"Group 2" is a grouping selection formula I added so when I run the report a "Parameter Fields" box appears and allows me to choose "Month" or "Week" when I
run the report.
IF {?UserDefinedGrouping} = "Month" THEN
(DATEPART("m", {@DATE RECEIVED}))
ELSE IF {?UserDefinedGrouping} = "Week" THEN
(DATEPART("ww", {@DATE RECEIVED}))
Example: When I choose "Week" and run my report with a filter for a "Date Received" date range of 1/1/13 - 1/31/13 and a filter for just one "Client Number"
"001234" this is what I see in my main report:
PH CLIENT NUMBER DATE RECEIVED QTY RECEIVED
GH1
GH2
D 001234 1/2/2013 200
D 001234 1/2/2013 178
GF2 001234 1 378
GH2
D 001234 1/7/2013 21
D 001234 1/7/2013 60
GF2 001234 2 81
GH2
D 001234 1/14/2013 100
D 001234 1/14/2014 360
GF2 001234 3 460
GH2
D 001234 1/26/2013 20
D 001234 1/26/2013 520
GF2 001234 4 720
I "hide" everything but the GF2 so I only see one row for each week. The "001234" is the Group #1 Name field I moved to GF2. The "1", "2", "3", "4" on GF2
rows are the numbers for the "weeks" which is the "Group #2 Name". Next to that is the "SUM" for the Quantities Received for each of the weeks.
Subreport Example: Using filters for "Client Number" "001234" and a "CLOSE DATE" range of 1/1/13 - 1/31/13. Grouping for the subreport is the same as the main
report. My Subreport output looks like this:
PH NC# CLIENT NUMBER QTY REJECTED CLOSE DATE
GH1
GH2
D NC-123 001234 40 1/2/2013
D NC-124 001234 15 1/2/2013
GF2 1 55
GH2
D NC-125 001234 80 1/10/2013
D NC-126 001234 142 1/10/2013
GF2 2 222
GH2
D NC-127 001234 26 1/21/2013
GF2 4 26
I hide everything but GF2. The "1", "2" and "4" are the "Group #2 Name" field indicating weeks 1,2 and 4. To the right of that is the "SUM" of the "QTY
REJECTED" field for each week in Jan.
I created a shared variable to pass the "QTY REJECTED" SUMs from the subreport to the main report. I added the subreport to the main report GH#1 row. The
shared variable output is placed in the GF2 row.
My report looks like this:
PH CLIENT NUMBER DATE RECEIVED QTY RECEIVED
GH1 1 55
2 222 <----- Subreport
4 26
GH2
D 001234 1/2/2013 200
D 001234 1/2/2013 178
GF2 001234 1 378 26 <------ Subreport shared variable output
GH2
D 001234 1/7/2013 21
D 001234 1/7/2013 60
GF2 001234 2 81 26
GH2
D 001234 1/14/2013 100
D 001234 1/14/2014 360
GF2 001234 3 460 26
GH2
D 001234 1/26/2013 20
D 001234 1/26/2013 520
GF2 001234 4 720 26
What I need is the subreport to output it's SUMs for each week and display them on the GF2 row with the corresponding weeks in the main report:
Like this:
PH CLIENT NUMBER DATE RECEIVED QTY RECEIVED
GH1 1 55
2 222 <----- Subreport
4 26
GH2
D 001234 1/2/2013 200
D 001234 1/2/2013 178
GF2 001234 1 378 55 <------ Subreport shared variable output
GH2
D 001234 1/7/2013 21
D 001234 1/7/2013 60
GF2 001234 2 81 222
GH2
D 001234 1/14/2013 100
D 001234 1/14/2014 360
GF2 001234 3 460 0
GH2
D 001234 1/26/2013 20
D 001234 1/26/2013 520
GF2 001234 4 720 26
How can I get my sub report to display the sum for week 1 on the week 1 row in the main report, week 2 and week 4 on the associated rows? I can get it to
either show week #1 for all rows or week #4.
Hopefully you could follow all of that.
Thanks for your time.