cancel
Showing results for 
Search instead for 
Did you mean: 

Problem with sub reports

Former Member
0 Kudos

I have my main report and i have a sub report:

My main data base looks like this:

Cable ID
Cable 1
Cable 2
Cable 3

I just have it display in CR in table form so it pretty much looks like it does in access. I have no parameter fields or any thing like that just want a raw data dump

Now the access data base has another table that contains the Route of the cable. Lets call it Route table.

Some one made a sub report to get the route of the cable that looks like this: (see attached Subreport design.JPEG)

The problem the person that made this had it set up so i can only enter a parameter lets say "Cable 1" and i get the information for cable one and the sub report pulls the route. So in the end i have CABLE 1 and then its route. But i want it to display all the cables and each corresponding route.

I just want a raw data dump of each cable and the route.

Some thing like the table shown below:

Cable ID
Route
Cable 1Raceway 1 Raceway X Raceway y

Cable 2Raceway 2

Cable 3Raceway 3

Now from CR i did a raw data dump of the Route table (see attached Route table.jpg)

The SEQ field says how many routes it goes though but for some reason only displays the last route of the cable.

Can any one help me get my final product of Cable ID and Route in table format.

Do i need to link anything?

Thanks in advance.

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Can any one here help me with this?

jyothirmayee_s
Active Contributor
0 Kudos

Hi,

Remove the subreport link on Parameter and refresh again..

Thanks,

Jothi

Former Member
0 Kudos

Hi James,

Long time, but good you came back on this one.

I think, the joins between tables is one part and displaying the data in the desired format is another.

First, to create joins in Crystal Reports, follow these steps:

The connection and joins part:

============================

1. Connect to your Access-DB using MS Excel-Ms Access native driver or using ODBC connection(by creating a ODBC DSN poiting to the Access DB)

2. Then, while in 'Database Expert' window, select the required tables and go to the 'Links' tab that is visible on the top of the scree.

3. Make desired 'joins' between the tables using the common columns between the tables.

The display part:

===========================

1. Even if the join is correct CR will show the results in following format:

Cable 1  Raceway 1

Cable 1  Raceway X

Cable 1  Raceway Y

.... etc.

2.So, to display the data in required fomat you may have to use 'Format with multiple columns feature available in the Section Expert >> Details section.

3. Another option is to use sub reports placed side-by-side by linking the main and sub on the common column. However, beware not to use sub reports in 'Details' section to avoid performance issues.

You might want to test Mike's suggestion for creating a Access- Querry directly at the database end.

Thanks

Prathamesh

Former Member
0 Kudos

I right click on the margin on the left then i select "format section..."

then i see the sections and i select "details"

then i click on "format with multiple columns"

What do i have to do after that.

Nothing happens?

see attached two images.

when i select "format with multiple columns" the layout tab is activated. I want the "wire" section to go across and not down just like you said.

Former Member
0 Kudos

also you say not to use subreports in details section. where do i use it. Because orginal thats what i was doing. but i never exported to excel. And it took forever for it to export.

Former Member
0 Kudos

ok i put them in one query now in the data base however i have my

Cable ID

and route- but the route still goes down. when i use "format with multiple columns" it seems to do it for the cable ID as well as the route. I only want it to do it for one though. "the route".

Former Member
0 Kudos

ok i found a work around but i still need help.

i have my cablekey that is duplicated and i have CR tell me how many times it duplicated. However i want this number in a a column called "number of duplicates"

how i get CR to move my sum field in a column rather then have it underneath .

see attached.

jyothirmayee_s
Active Contributor
0 Kudos

Hi,

Try:

Cablekey = previous(cablekey) then

count(cablekey)

Insert the above formula in groupfooter..

pls post back the results.

Thanks,

Jothi

Former Member
0 Kudos

HERE IS WHAT I PUT IN:

{ROUTTEMP.CABLKEY}=PREVIOUS({ROUTTEMP.CABLKEY}) THEN

COUNT({ROUTTEMP.CABLKEY})

I GET AN ERROR RIGHT BEFORE "THEN" IT SAYS

"THE REMAINING TEXT DOES NOT APPEAR TO BE PART OF THE FORMULA.

Former Member
0 Kudos

That error is an indication that you are executing an IF..THEN statement, but you have no "IF".

JWiseman
Active Contributor
0 Kudos

hi James,

i would recommend to Insert a Group on your Cable field. then you can right click on the route and choose Insert > Summary, change the Type to Count and have it inserted on the group level.

now do the following to get the display that you wanted:

1) move the Group 1 Name to the Group Footer 1 section

2) right click on the Group Header 1 section section and choose Suppress and repeat for the Details section

3) to get the display you want for the Routes, create a new formula similar to

whileprintingrecords;

stringvar routes:= routes + {your route field} + ', '

4) place that new formula on the details section

5) create a new formula with this syntax

whileprintingrecords;

stringvar routes[1 to length(routes)-2];

6) place this on the Group Footer section

this is a string running total and will create the display that you wanted in your initial post. this assumes that you did create a report with linked tables, and that you are not using a subreport.

-jamie

JWiseman
Active Contributor
0 Kudos

p.s. attached is a sample report that uses a string running total. the output looks like this:

to view the report extract the contents and change the .txt extension to .rpt.

one step i forgot to mention above is that you need a Reset formula as well to put in the Group Header...

whileprintingrecords;

stringvar routes:= ''

cheers,

jamie

Former Member
0 Kudos

Hi James

Are you using MS ACCESS as a data source??

If so, then you should create a query in your ACCESS dB that joins the Header & Item tables.

Then use the ACCESS query as the data source for your Crystal Report.

(You could perform the join in CR, but the performance will better if you set up the join in the ACCESS dB.)

jyothirmayee_s
Active Contributor
0 Kudos

Hi,

Instead of a subreport. Did you tried selecting 2 tables in report and joining on common field.

and drag the fields on the report.

Let me know if you need more details.

Thanks,

Jothi

Former Member
0 Kudos

I tried joining them in CR and in Access.

This is the problem i run into:

Cable 1Raceway 1 Raceway X Raceway y

Cable 2Raceway 2

Cable 3Raceway 3

From the data above cable 1 goes through raceway 1, raceway X and Raceway Y

WHen i try to do a data dump in table form i get this:

Cable 1Raceway 1 

Cable 2Raceway X

Cable 3Raceway Y
Cable 4 Raceway 2
Cable 5Raceway 3

In other words instead of my raceways going across. they go down and fill in the wrong data to Cable 2 and Cable 3 and so on.... Its like it only wants to fill in one data information per cable instead of going across like i want it to.

Former Member
0 Kudos

Hi James,

I think you would need a right outer / left outer join between the tables that are holding the Cable_name and Raceway info.

YOu can link the tables from Database Expert >> Links tab.

Thanks,

Prathamesh

Former Member
0 Kudos

Ok i know how to link the data bases but how do i that "right outer / left outer join between the tables"

Keep in mind im sort of a newbie at CR so if you could give me a step by step would be greatly appreciated.