on 01-03-2013 8:13 PM
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 1 | Raceway 1 Raceway X Raceway y | ||
Cable 2 | Raceway 2 | ||
Cable 3 | Raceway 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.
Can any one here help me with this?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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
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
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.)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I tried joining them in CR and in Access.
This is the problem i run into:
Cable 1 | Raceway 1 Raceway X Raceway y | ||
Cable 2 | Raceway 2 | ||
Cable 3 | Raceway 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 1 | Raceway 1 | ||
Cable 2 | Raceway X | ||
Cable 3 | Raceway Y | ||
Cable 4 | Raceway 2 | ||
Cable 5 | Raceway 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.
User | Count |
---|---|
83 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.