How to create report relating 3 tables to a master table. 1

[rleyba828]

Hi Team,

I have to run a report where I need to do pull data from multiple tables in a MySQL database and run summaries on one of the tables. I am familiar with SQL, mySQL and Crystal Reports XI but this particular data extraction stumps me a bit.

My requirement is very simple: I have 4 mySQL tables. A ChangeRecord table showing IT changes for the day with ChangeRecordNumber as a primary key, a Firewalls table with ChangeRecordNumber as foreign key, and the same with Routers and Servers table (they also have ChangeRecordNumber as foreign key).
A ChangeRecord may involve 0 or more of Firewalls, Routers and servers in the change and I just need to GROUP by ChangeRecordNumber and detailing how many Firewalls, Routers and Servers are involved in that ChangeRecord.

The final output I need to show is something like this (4 columns):

ChangeRecordNumber:    Firewalls    Routers       Servers
0000111                Firewall-A   Router-A    
                                    Router-B
                                    Router-C

0000112                Firewall-D   Router-B       Server-A
                                                   Server-B

My question is I was trying to setup the links in Crystal Reports XI and I am having the arrows of the links all pointing to the ChangeRecordNumber field. I am also using left outer join so it can group by ChangeRecordNumber. My issue is if I link only one table at a time, (i.e. Firewalls --> ChangeRecord or Routers --> ChangeRecord) then it works. But once I do multiple links i.e. Firewalls --> ChangeRecord, Routers --> ChangeRecord and Servers --> ChangeRecord, I get the error of ('...warning..multiple starting points" ... or something like that), and then I get no output in the report although the record count goes into the tens of thousands, as though the link caused a cross-join somewhere.

How do I setup a report where multiple Child tables link to the same Foreign Key in the parent record and have it output like in my specimen above?

Thanks for all the help.

 

[lbass]

Your links should be left joins FROM the ChangeRecord table to each of the other tables.

Then try grouping on change record number, firewalls, routers, and servers and see if that gives you the desired display (use the group header sections and format them to "underlay following sections" and suppress the details).

-LB

 

[rleyba828]

Hi lbass,

Thanks very much for your tip. That was a very good starting point. I now get the logic. I tried to do it with two groups first to simplify things...so group on ChangeRecordNumber, then Firewalls, then Routers.

I ended up with a group Heirarchy of something like this:

Group#1 Name        <---Change Record
 Group #2 Name      <--Firewalls
   Group # 3 Name   <--Routers
Detail             Firewalls   Routers   <---Suppressed

Because the Group # 3( Routers) is a level below Group # 2 (firewalls), I end up with a report where if the Changerecord number has 4 firewalls and only 1 router, the router "Group" is repeated 4 times, like this:

ChangeRecordNumber:    Firewalls    Routers      
0000111                Firewall-A   Router-A    
                       Firewall-B   Router-A
                       Firewall-C   Router-A
                       Firewall-D   Router-A

It should be like this:

ChangeRecordNumber:    Firewalls    Routers      
0000111                Firewall-A   Router-A    
                       Firewall-B   
                       Firewall-C   
                       Firewall-D

The Overlay feature works, though, but functionally, what I need is to have the Routers and Firewalls on the SAME LEVEL under the ChangeNumber, but don't know how to do it in Crystal...something like this:

Group #1   <---Change Number
Group#2a <---Firewalls    Group#2b   <--- Routers

Could you advise how I might go about doing this? Thanks very much.

 

[lbass]

I think the simplest approach would be to add only the firewall table and the changerecord table to the main report. Then group on changerecordnumber and then group on firewall and format group#1 to underlay following sections. Then insert a subreport that accesses the router table, link it to the main report on the changerecordnumber field, and place it in the group header #1 (positioned to the right of where the group #2 will appear. Then repeat by adding a second subreport using the server table.

-LB

 

[rleyba828]

Yes, that worked. Thanks very much!