CodeCharge Studio
search Register Login  

Visual Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> PHP

 [RESOLVED]Help with Parent/Children reporting

 Print topic Send topic

Author Message
rbroder

Posts: 67
Posted: 06/22/2011, 8:14 PM
I am at a loss when trying to report on a parent record with 2 sets of children. The Master record (with it's own fields) has a number of gage records as children (from the gagechild table) and a set of material records from a second table (the materialchild table). My goals is to have the report look something like:

Master record fields
gage record fields
gage record fields
gage record fields

material record fields
material record fields

Master record fields
...

I can't seem to make this happen in the Report Builder. I don't know if I need custom sql or if this is beyond CCS at this time.
View profile  Send private message
tfertil

Posts: 43
Posted: 07/01/2011, 3:48 PM
Hi, rbroder.

I think you're right: this is beyond CCS capabilites.

But, maybe we can help you if you provide us with additional info.

Does the Gage and Material record have the same columns?
View profile  Send private message
rbroder

Posts: 67
Posted: 07/01/2011, 6:51 PM
The only field they all have in common is the master record ID. Its the primary key for the master record and the foreign key for the child records.
View profile  Send private message
mamboBROWN


Posts: 1713
Posted: 07/03/2011, 9:40 AM
rbroder,

I actually disagree with tfertil. This is not beyond CCS's capability, this is a database challenge. The challenge is to put the data in a format that CCS can display it to your specifications. Depending on the database (MySQL, PostgreSQL or MS SQL) version that you are using we can use a combination of views and or stored procedures to create the data in the specified format. What are the table definitions? I'm also assuming that you are using CCS 4.x. Please give more information.
View profile  Send private message
rbroder

Posts: 67
Posted: 07/06/2011, 2:19 PM
Thanks MB. I've been out of town. Really for this example you can make up just a few columns for each table:

MasterID
MasterField1

GageID
MasterIDkey
GageName

MaterialID
MasterIDkey
MaterialName

The database is MySQL and the CCS version is 4.3. I started to look at views today, but will have to try again tomorrow.

Thanks
View profile  Send private message
mamboBROWN


Posts: 1713
Posted: 07/10/2011, 8:46 PM
rbroder,

Here you go ( was done in MySQL 5.0.51a):

MasterTable:
  
     MasterID   |   MasterField1  
------------------------------------------  
         1      |     one one one  
------------------------------------------  
         2      |     two two two  
------------------------------------------  
         3      |     three three three  
------------------------------------------  
         4      |     four four four

GageTable:
  
       GageID  |  GageName  |  MasterID  
----------------------------------------------------  
         1     |   gage01   |      1  
----------------------------------------------------  
         2     |   gage12   |      1  
----------------------------------------------------  
         3     |   gage02   |      2  
----------------------------------------------------  
         4     |   gage03   |      3  
----------------------------------------------------  
         5     |   gage22   |      2  
----------------------------------------------------  
         6     |   gage04   |      4  
----------------------------------------------------  
         7     |   gage42   |      4  
----------------------------------------------------  
         8     |   gage45   |      4

MaterialTable:
  
         MaterialID  |  MaterialName  |  MasterID  
---------------------------------------------------------------  
             1       |   Material01   |     1  
---------------------------------------------------------------  
             2       |   Material02   |     2  
---------------------------------------------------------------  
             3       |   Material03   |     3  
---------------------------------------------------------------  
             4       |   Material04   |     4  
---------------------------------------------------------------  
             5       |   Material033  |     3  
---------------------------------------------------------------  
             6       |   Material039  |     3  
---------------------------------------------------------------

unionview (This is a View):
  
select `mastertable`.`MasterField1` AS `MasterField1`,'Gage' AS `TableName`,`gagetable`.`GageName` AS `Name`   
from (`gagetable` join `mastertable`) where (`gagetable`.`MasterID` = `mastertable`.`MasterID`)   
union   
select `mastertable`.`MasterField1` AS `MasterField1`,'Material' AS `TableName`,`materialtable`.`MaterialName` AS `Name`  
 from (`mastertable` join `materialtable`) where (`materialtable`.`MasterID` = `mastertable`.`MasterID`)   
order by `MasterField1`,'TableName',`Name`

The unionview yields the results below (which can be used in Report Builder):
  
 MasterField1           |   TableName     |  Name(which is GageName or MaterialName depending on table)  
--------------------------------------------------------  
four four four          |   Gage          |  gage04  
--------------------------------------------------------  
four four four          |   Gage          |  gage42  
--------------------------------------------------------  
four four four          |   Gage          |  gage45  
--------------------------------------------------------  
four four four          |   Material      |  Material04  
--------------------------------------------------------  
one one one             |   Gage          |  gage01  
--------------------------------------------------------  
one one one             |   Gage          | gage12  
--------------------------------------------------------  
one one one             |   Material      |  Material01  
--------------------------------------------------------  
three three three       |   Gage          |  gage03   
--------------------------------------------------------  
three three three       |   Material      |  Material03   
--------------------------------------------------------  
three three three       |   Material      |  Material033   
--------------------------------------------------------  
three three three       |   Material      |  Material039   
--------------------------------------------------------  
two two two             |   Gage          |  gage02  
--------------------------------------------------------  
two two two             |   Gage          |  gage22  
--------------------------------------------------------  
two two two             |   Material      |  Material02


Hopefully this helps.
View profile  Send private message
rbroder

Posts: 67
Posted: 07/11/2011, 5:21 PM
Great! I was on the right track as I was investigating unions, but found out as you already know, that both select result sets must have the same number of fields and the field names of the first result set are used. I was going to go to a great deal of trouble before I saw your example. I like the way you include the table name in the result set. This will make sorting and totalling much easier.

Thanks again.
View profile  Send private message
mamboBROWN


Posts: 1713
Posted: 07/11/2011, 8:04 PM
rbroder,

Glad I could help. Please don't forget to add [RESOLVED] in the title. Thanks.
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

MS Access to Web

Convert MS Access to Web.
Join thousands of Web developers who build Web applications with minimal coding.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.