INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Sub Report and Main Report Grouping Output Problems

Sub Report and Main Report Grouping Output Problems

(OP)
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.

RE: Sub Report and Main Report Grouping Output Problems

(OP)
It looks like it screwed up my table formatting. Sorry. That makes it a lot harder to read. I'm not sure how to go back in and edit it.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close