Nested report structure
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22849
Printed Date: 27 Apr 2024 at 12:57pm
Topic: Nested report structure
Posted By: lowspark
Subject: Nested report structure
Date Posted: 19 Dec 2019 at 1:17pm
|
New to CR so I Appoloigize if this question is a little on the basic side, I am headed down the path of trying to create a masive report by breaking it down to lots of sub reports but i am having some issues. The goal is a report that is something like this"
Department # of "Status*" work orders (WO) ----sub report of "Status*" WO by type and count of each --------sub report of each WO by status and type * Open Or Closed so for exammple Bike Department 34 closed work orders 52 open work orders ---- 34 Closed Work Orders Tire Repair 5 New Breaks 10 New Cables 14 --------- Closed Work orders for Tire Repairs WO # 4569 Bob Smith 2.4 hours WO # 4575 Bruce Collins 2.4 hours WO # 4968 Janna Robinson 2.4 hours WO # 5236 Jason Grant 2.4 hours WO # 6985 Jerry Garcia 2.4 hours Sorry for the crazy lay out. I am having a hard time trying to Find out how to do this based on having a hard time explaining it in terms for CR. Thank you in advance for any guidance!! ------------- Nothing left to do but Smile, Smile, Smile. |
Replies:
Posted By: DBlank
Date Posted: 23 Dec 2019 at 8:40am
| I don't think sub reports will work for you if you want to summarize the data from the SR into the main report. I would recommend rethinking this to just grouping. |
Posted By: lowspark
Date Posted: 24 Dec 2019 at 6:01am
|
Is it possible to point me to some good resources on how I might pull this off with Grouping? Thanks for you guidance.
------------- Nothing left to do but Smile, Smile, Smile. |
Posted By: DBlank
Date Posted: 24 Dec 2019 at 6:08am
|
Same as your other request.
You need one data set that has all the data you need in it. group 1 Group on Department In a GHb add a Crosstab or two running totals to get your department summary Group 2 Group on Status (open/closed) use a crosstab in gh2b to display the type counts put distinct data in the details section |
Posted By: lowspark
Date Posted: 26 Dec 2019 at 10:22am
I have moved over to Groups but now i have some issues with totals. This is where I am in the process: Group 1 Department Group 2 Status and Count of WORKORDER.STATUS -this count is a summary if Status and sum as count Group 3 Workorder (Hide Drill-Down ok) At this point all is good. The idea is for group 3 to be clicked on and the all the info for Department+ Status is shown, so when it says: Trash Department Closed 102 Open 38 Click on Closed and the info for the 38 Workorders will then open. The issue I am having is when I add to Group 3 like Description so that i now looks like.. Group 3 Workorder and Description (Hide Drill-Down ok) The Count for Closed and Open change to 98 and 27 respectively. Not relay sure what the step is to make those numbers stick.. the 102 and 38 are the correct numbers based on some other QC reports i have created ------------- Nothing left to do but Smile, Smile, Smile. |
Posted By: lowspark
Date Posted: 26 Dec 2019 at 10:35am
|
So it seems that I have work orders that have more than one description on them so when I add description to the data to report it counts the the work order every time it comes up , i need to find out a way to count unique Work Order ID's ------------- Nothing left to do but Smile, Smile, Smile. |
Posted By: DBlank
Date Posted: 30 Dec 2019 at 7:59am
| do a distinct count of WorkOrderID as your summary at any group level |
Posted By: lowspark
Date Posted: 30 Dec 2019 at 9:19am
|
all that seems to do is count all the Work Order ID's this is what i did :
Group 1: @Department _CountDC Catogory_SUM _______ @Department = if {WORKORDER.WOCATEGORY}= "VM" then "VEHICLE MAINTENANCE" else {WORKORDER.WOCATEGORY} _CountDC = distinctcount({WORKORDER.WORKORDERID}) Catigiry_Sum = Summary on WO Category, Calculate as Count ---------- this is what it returns Facilities 897 508 Parks 897 215 Streets 897 419 just not sure where I am going wrong ------------- Nothing left to do but Smile, Smile, Smile. |
Posted By: DBlank
Date Posted: 30 Dec 2019 at 10:06am
|
The distinct count can be used at the report level (all)
or also at each group level say if you grouped by @department. Or add a crosstab to your report header and add the column in the rows by your @programs and the summary field as the distinct count of WOid. |
Posted By: DBlank
Date Posted: 30 Dec 2019 at 10:08am
|
the group level would be
distinctcount({WORKORDER.WORKORDERID},{@Department}) |
Posted By: lowspark
Date Posted: 30 Dec 2019 at 10:31am
 ------------- Nothing left to do but Smile, Smile, Smile. |
Posted By: lowspark
Date Posted: 30 Dec 2019 at 10:31am
|
So after adding this to as a new formula:
DistinctCount ({WORKORDER.WORKORDERID},{@Department}) I get this error: "There must be a group that matches this field." I created the Formula by typing in "DistinctCount" then adding in by double clicking {WORKORDER.WORKORDERID} then type in + the Double Click @Department. not sure what i am still doing wrong. ------------- Nothing left to do but Smile, Smile, Smile. |
Posted By: DBlank
Date Posted: 30 Dec 2019 at 10:35am
|
Whatever field you are grouping on for group one needs to go into the formula.
If you are grouping by the original field use it, if you are grouping by your formula field use it. The resulting formula needs to be placed in the group header or footer for it to display the correct value. |
Posted By: DBlank
Date Posted: 30 Dec 2019 at 10:42am
|
Group 1 Department
use DistinctCount ({WORKORDER.WORKORDERID},group 1 field here) Group 2 Status use DistinctCount ({WORKORDER.WORKORDERID},{WORKORDER.STATUS}) Also you can just use the insert summary button (Sigma button) and pick the field as WorkerID Type as DistinctCount location = Group 1 (repeat for group 2). This will insert a summary field into each group footer that you select. You can drag it into the header group header. |
Posted By: lowspark
Date Posted: 31 Dec 2019 at 4:22am
|
That was it ,, Thank you so much for working through this with me, I truly appreciate it!!!
Type as DistinctCount location = Group 1 (repeat for group 2). This will insert a summary field into each Group footer that you select. You can drag it into the header group header. These are the two parts that i was having trouble with or did not even know they were that important!. Thanks so much ------------- Nothing left to do but Smile, Smile, Smile. |