CR and SQL2000 2

[safo2000]

hi

i am using the CR that comes with VB.Net 2003.
i have i parent SQL table with four child tables. the relation is a primary key(auto no) on the parent to the 4 other tables. i am trying to make a report that fits one record from the parent table with all related records of each of the other four tables.
i can make it when it is one parent and one child by using a view, but when it becomes more than one child i get in trouble... i am a newbie in CR so please bear with me. help will be greatly appreciated

thanks in advance

 

[lbass]

If you are getting duplicate records, then you could remove the child tables from the main report and insert a subreport for each of these tables instead. Link each subreport to the main report on the primary key field, and then place the subreports side by side in a group header_b section. The group in the main report should be on the primary key field that you are using for linking.

-LB

 

[synapsevampire]

As LB states, subreport{s} can accomplish this, however the performance suffers.

Since you can build the proper data using 1 child table, you should be able to do so using multiples as well, however this is subject to the nature of the data, which you haven't shared.

If a subreport is too slow, or you would prefer to create a reusable object on the database, please post specifics, such as:

Example data (show examples for the Parent and child)
Expected output (base this on the example data)

-k

 

[safo2000]

hey synapsevampire

as u suggested performance/speed is an essence.
here is an example of data, mind u each table have more fields but i reduced for example purposes

parent table (a maintenance request)
PK(autono) job# job date
1 A-1 01/01/02
2 B-1 01/01/03
3 D-23 01/02/03

child 1

PK(auto no) FK toolcode
1 1 BC001
2 1 VB002
3 2 VB002

child 2

PK(auto no) FK taskcode
1 1 001
2 1 002
3 2 002

child 3

PK(auto no) FK invcode
1 1 QQQ
2 1 DFC
3 2 GGG

child 4

PK(auto no) FK laborcode
1 1 MECH
2 1 ENG
3 2 PLUM

the result in report

job # : A-1 Job Date : 01/01/02 .....

tools used
BC001....+other related info
VB002....+other related info

task required
001....+other related info
002....+other related info

inv used
QQQ...........+other related info
DFC..............+other related info

needed labor
MECH....+other related info
ENG....+other related info


thats how i want the report to be done.
(if possible direct me to an example so i can analyze and follow....bare in mind that i am a newbie in CR)

thanks again for the help you guys

 

[synapsevampire]

Consider a UNION to accomplish this for the child tables, it's not the MOST efficient, but it will be reasonable:

create view MyView
as
select 1 as Child, PK, FK, toolcode code from child1
union all
select 2 as Child, PK, FK, taskcode code from child2
union all
select 3 as Child, PK, FK, invcode code from child3
union all
select 4 as Child, PK, FK, toolcode code from child 1

Now you have all of the rows with a unique designator in one View which can be joined to the main table.

To display the results (you will have row inflation still), Group by the Job#.

Insert the Job fields in the Job Group header.

Sort by the Child.

Right click the Details and select insert section below 3 times, place the fields in each section, and then place a conditional suppression formula in each details as in:

child <> 1

child <> 2

child <> 3

child <> 4

You could make this faster with more optimized SQL but thsi would take me too long to code out here. This should prove reasonable, at least it will significantly outrun subreports.

If you need to make it even faster, try posting the SQL in the SQL Server forum, you'll find some great T-SQL coders there with the time.

-k

 

[safo2000]

thnanks it worked really well.
i also made it work with subreports....

one little question....u know when u format a field 'Can grow', well it grows, but it grows over the info below it, there should be a way to let the other fields scroll down..???

thanks again

 

[lbass]

Put the fields below the object that is growing in a separate section, i.e., if the problem is in a group footer, insert a group footer_b section for the fields below. Then growing objects in group footer_a will simply push the group footer_b down the page.

-LB

 

[rosemaryl]

Dear Safo2000:

I have to do these types of reports all the time to show Master - Detail releated Tables. What I have discovered works really well without subreports and without unions is as follows.

Insert a group in the order of display on what is unique (primary key) for each joined table. Make the join type from the Main Parent table to the *child* tables a left outer join so that data for the *main* table will show if there are no children on a particular join.

Suppress the details section.
Suppress Group Footers for each group except Group 1.

Place the data to show for each table in it's associated group. If you need header columns, Do an insert section below and put column headers in the A section and Data in the B Section. Create your column headers for that section's data and then Format Section/Suppress No Drill Down X-2 Formula Editor (This Group Table.Primary Key <> minimum(This Group Table.Primary Key,Prior Group Table.Primary key)

For each child group, Format the Section, and choose the Suppress No Drill Down x-2 formula editor and enter:

isnull(This Group Table.primary key)
or
(Prior Group table.primary key) <> maximum(Prior Group Table table.primary key, Parent Table.Primary key)

What this does is suppress printing of the group until the group above it finishes printing:

This results in exactly what you requested:


GH1A (Column Header Maintenance Request - if desired Prints at start of Manintenace Request)
GH1B (Data for Maintenance Request)
GH2A (Column Header for Tool Code - Prints Once for Each Maintenance Request, if Desired)
GH2B (Tool Code Data for Maintenance Request) Repeats as necessary until all Tool Code Data for this maintenance request is done
GH3A (Column Header for taskcode. Prints Once for Each Maintenance Request, if Desired)
GH3B (Data for TaskCode - Repeats for each taskcode related to Maintenance Request)
GH4A Data for Invcode - Repeats for each invcode related to Maintenance Request)
GH4B (Data for InvCode - Repeats for each taskcode related to Maintenance Request)
GH5A(Data for LaborCode - Repeats for each LaborCode related to Maintenance Request)
GH5B(Data for LaborCode - Repeats for each LaborCode related to Maintenance Request)

That is all you have to do. If you need to show totals and such, just play with suppression formula to make data print in an additonal section at the end of a group ... as desired.

Once again, I have to do these all the time for example Change Request, Change History, Change Approvers, Change Assessments and so on to gather all the information that relates to a change. If you just keep in mind that you link from the Main Table to all the children as a left outer join on the foreign key that relates the child to the main table and then Group Each Table on each Table's primary key, and then use suppression formulas as indicated above ... it works flawlessly.

It does take a little time to set up, but no subreports and now messing with the SQL statement.

Best Regards,

ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.