Linking tables in Crystal Reports for VS 2005

I have a lot of experience using Crystal in VS 2005, but this is the first time trying to link tables for a report.  I have two tables, table1 has an integer field that links to an integer field in table2.  These aren't set as PK/FK in the dataset, but the relationship is a one to many.

I have the join set as =, because I only want to display the items in table1 and corresponding table2 data where they are equal...there could be chances were table1 doesn't have a matching record in table2.

When I try to run the report...I get a gray screen(blank) and my cpu clocks aty about 50%.  I have to right click the form with the report viewer to even close it and the main app I have to kill as well.

Thoughts?
LVL 12
rgn2121Asked:
Who is Participating?
 
rgn2121Author Commented:
Looks like the solution to this is no solution.  mlmcc, I know you are a mod, so if this is just better off being deleted, then please do so, otherwise I will just select this as the solution and reward points as best I can...

Thanks to all for the help.
0
 
simpsolCommented:
If you do a left outer join on table1 then only records in table1 with matching records in table2 will show.
0
 
rgn2121Author Commented:
Why does that not work with an inner join...because all records don't have a match?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
rgn2121Author Commented:
Okay...i just recreated the report and did an inner join and it worked, only this time I did not group the report and I selected ENFORCE FROM on the join...?
0
 
rgn2121Author Commented:
This displayed data, but my report viewer form was still clocking at 50%...nothing in the whole app will work.  I have to kill it.  I have about 20 reports and this is the only one that behaves like this..
0
 
rgn2121Author Commented:
Added the groupings and blank display and cpu is clocking at 50%....
0
 
rgn2121Author Commented:
Left Outer Join returns the same result...blank data and clocking CPU
0
 
CodeCruiserCommented:
How many rows do you have in each table?
0
 
rgn2121Author Commented:
about 3,000 in table1 and maybe 10,000 in table2...I would have to do a count....give me a few
0
 
CodeCruiserCommented:
And if you leave the app running, it never returns?
0
 
rgn2121Author Commented:
Table1=4313
Table2=5068
0
 
rgn2121Author Commented:
I left it running for 5 minutes and got nothing.  I at least got results when I didn't group, but now that I am ...nothing.

Maybe I should be using a sub report?  I have only a few items in table1 that I want to display, but about all the items in table 2 are needed....
0
 
rgn2121Author Commented:
If I don't do any grouping it takes about 12 seconds to render this report and my cpu is still at 50% a minute later...I can't believe it would be this slow.  It is not like I am doing multiple joins or anything complex.  This is pretty straight forward....
0
 
mlmccCommented:
Left Join   - All records from table1 and matching records from table2
Inner Join - Records from table1 with matching records in table2

Do you have smart linking turned off?
Crystal has that as the default. With that Crystal tries to determine the appropriate links
In the full versions it is under
FILE --> REPORT OPTIONS
or
FILE --> OPTIONS

mlmcc
0
 
rgn2121Author Commented:
I am using an inner join because I only want to return data that matches in both tables.  I think smart linking is on because when I selected my tables and hit next, it imeediately created my link, but I checked the link and it looks correct.  

I have the reportViewer on a form and when this report is rendering, I get the first page in about 12 seconds...Other reports with 100+ pages return the first page is maybe 2 seconds.  After that, for this linked report, the buttons on the viewer control were locked up for about 6 minutes, then I could use the form normally...
0
 
rgn2121Author Commented:
I haven't seen that option anywhere in CR for VS 2005...if it is, I can't find it.
0
 
mlmccCommented:
There are differences between the capabilities of the Crystal included in VS and the full versions.
I have never used the .Net version so I don't know where it might be.

I find the smart linking gets in the way at times since some table may have several fields they can be joined on.
For instance if I were a property manager I might have
tblPropertyInfo
fkOwnerId
fkTenantId
fkLocalPOC
etc

All 3 point to my Person table and Crystl would try to use all 3 as links when I really only want 1.  Even after fixing the links I have seen Crystal relink the tables itself and mess up a report.

mlmcc
0
 
rgn2121Author Commented:
I just went back and checked...I only have that one link and it is correct.  I can't see why this would run that long...

I checked my dataTypes to make sure that they are the same.  Do I need to do Eforce Join?

I might look at on-demand subreports...
0
 
rgn2121Author Commented:
I will leave this open for a little while in case there are other suggestions.  I have switched to the on demand subreport and that loads within a few seconds.  There must be something with the linking that is causing it to search through all the records every time....
0
 
rgn2121Author Commented:
Since we couldn't find a resolution to the linking, how can export my report that contains a subreport, so that the sub report is viewable...?  Or can I do this with an on demand subreport?
0
 
mlmccCommented:
The ENFORCE JOIN is only necessary if you have tables that are joined but no fields are included in the report.  Crystal when it creates the SQL only includes the tables/fields it needs for the report.

Thus in your case if you were trying to use the join to limit the records from table1 without including any fields in table2 on the report

Without enforcing the SQL would be
SELECT Table1.Name ...
FROM Table1

If you enforced the join you would get
SELECT Table1.Name ...
FROM Table1 INNER JOIN Table2 on Table1.Id = Table2.Id

Thus the second SQL might return fewer records if you had table1 records that didn't have table2 records.

I have seen a method or tool that says it can print the reports and the on demand subreports but I forget what it is.

mlmcc
0
 
rgn2121Author Commented:
I would want the second so I will leave it as is....thanks for the definition.

I will leave this open until tomorrow in case you might remember where exactly I have to make the change to get the sub reports to show up.  I could probably change them from on demand reports and it would show up all the time...

Thanks!
0
 
James0628Commented:
What db are you using?  If your "tables" are actually something like Excel files, joining can be extremely slow, since they're not indexed.  It sounds like you're using actual tables, but thought I'd mention that anyway, just in case.

 Do you have any other reports that use the same tables, linked the same way?  If so, do they perform OK?  If you don't have any other reports that use the same tables, you could try creating a new report using those tables to see if it was also slow.

 You said that the fields aren't keys in the tables.  I suppose it might have something to do with that, although this sounds extreme for that many rows.

 I don't know if you have these options in your version, but in the full CR 10, under File > "Report Options", there are at least a couple of options that would be worth checking - "Perform Grouping On Server" and "Use Indexes Or Server for Speed".  You might see if you have similar options.  Normally you'd probably want both of those checked, but if you're having problems and they were checked, you could also try unchecking one or both of them to see if that helped.

 James
0
 
rgn2121Author Commented:
Thanks for the reply James, but this data is in tables in my in memory DataSet.  I didn't assign PKs because I don't see the need for what I am doing and the one table doesn't really have a unique field.  These get uploaded to oracle later where they are indexed, but that is it.

I am using the built in CR with VS 2005, so I don't think those options apply here.
0
 
rgn2121Author Commented:
I moved to a subreport method, not on demand, and it originally came back in a few seconds...661 pages.  Now, the original report when all the data was in one big table generated about 341 pages.

So I decided I could clean up the formatting and just use this since it was returning so fast.  Well, I got it all formatted and ran it again and it took 12 minutes for 277 pages.  I didn't change any grouping, but actually took out some group totals...

Only thing I can think right now is that by me foratting the report so I could get more on a pages, it had to calculate more at a time...?
0
 
rgn2121Author Commented:
I'm thinking now that it might be something to do with the data that didn't show up before due to it bing in one table...I am going to leave this open another day or so to see if I can find it.
0
 
James0628Commented:
I don't use datasets like that, so I don't know how that might factor in.  Some of the results that you're getting, like making some "formatting" changes and having the report take fewer pages, but much longer to run, do sound pretty strange.

 When you said that "one table doesn't really have a unique field", I'm not sure what you meant by that, but I assume that you're using some field to link the tables.

 What about my question about other reports that use the same tables/dataset?  Do you have any?  If so, how do they perform?  If not, could you try creating a new report and see how it performs?

 James
0
 
rgn2121Author Commented:
I have about 20 reports...they all work fine.  This however, is the only one where I am using two tables either by linking or by subreports.  The two tables that I am using are in a one to many relationship, so that is why I said I don't have a unique field on one of the tables.  I don't just use a PK in my datasets just to give me a PK.

So, I am linking the tables by a sequence number.  Both tables have the same field (Same exact name), same datatype (Integer) and that is how I am linking them together.

I have tried creating a "test" report with these two tables setup in the report/subreport way, and it will work if I just leave all the spacing alone.  When I start formatting...BOOOM....12 minute report!

Makes absolutely no sense...I have a couple of reports that have to be linked and then this app is done.  This seemingly simple task has held me up for 3 days...!
0
 
rgn2121Author Commented:
I had created a "key" field earlier that goes in the main table and contains the info from the child table.  I will just have to go back to this and parse my key to get what I need.  I tried the same setup with report viewer and it is churning as well.  Unless anyone has other ideas I will close this at the end of the day selecting a comment be me as there was linking type solution to the issue and award points for the suggestions.
0
 
mlmccCommented:
I am not a mod.  Mods don't participate as experts in questions.  I am what is known as a Zone Advisor and as such have additional permissions.

I think the best resolution at this time would be to close this and use your comment as the solution.  No need to award the points.

mlmcc
0
 
James0628Commented:
Having "formatting" affect the report doesn't make any sense, unless you're not just talking about things like field and section size, position and format.  If "formatting" includes things like adding groups or sorts, that could certainly affect the report performance, although I can't think of anything that should have that extreme of an effect.

 This is kind of a longshot, but if the "formatting" includes adding fields from one of the tables, that could explain it, _if_ you weren't actually using any fields from that table before that.  When CR generates the query, it normally doesn't include any tables that it doesn't actually need.  If you put 2 tables in a report and link them together, but only use fields from one of the tables, CR will normally not include the second table in the query, since the report isn't actually using any data from that table.  So, if your report starts with fields from just one table, and then you apply some "formatting" that includes adding fields from the other table, that could have a significant impact on the report's performance, because CR may not have actually been using the second table before that.

 Like I said, it's probably a longshot, but I thought I'd throw it out there, just in case.

 James
0
 
rgn2121Author Commented:
I was using fields from both tables...just changed the font and size to fit more on a page.   At first , only about 4 records would show on a page because of formatting.  Once I finished about 20 fit on a page...that is it.
0
 
mlmccCommented:
Where are the totals calculated?

Are they simple summaries or are you using formulas and variables?

mlmcc
0
 
rgn2121Author Commented:
Simple summaries...

I think it is just the amount of data.  I tried creating something similar and I got an out of memory exception using the MS ReportViewer.

I have done everything I know to do to get this to work...

I have tried creating the report with subreports
  I have used on demand (Which I have never found a way to export parent and child info)

I have tried creating the report in code and directly exporting without displaying

I have tried creating the report by linking the tables
  I have linked from parent to child
  from child to parent
  by key
  by name
  grouped on the parent and the child
  grouped on just the parent
  without any grouping
  used inner join, left outer join, enforced the joins, not enforced the joins

I do not like green eggs and ham, I do not like them sam I am...  

I might come back to this at some point, but right now I have to many other things to do besides spending more time on this report.  I have found a workaround that allows me to generate what I need outside of crystal linking up my tables.

Thanks
0
 
James0628Commented:
That's a strange one.  While I hate to leave something like this unsolved, I'm pretty much out of ideas at this point.  If you do come back to this at some point, it might help if you could u/l the report so that we could take a look at it.  Even better if you can do it with saved data.  Don't know why I didn't think to suggest that earlier.

 James

 PS: I liked the "green eggs and ham" quote.  :-)  It does seem to fit.
0
 
rgn2121Author Commented:
Just so you guys would have an idea of what I had done to get around this....

The data that I am dealing with allows for an item to have multiple detail IDs.  Every item has at least one and could have 10+.  The item has generic details as well as very specific deals that relate to each detail ID.
So the point of this was to show the generic details and then show something like a running list of detail specific info under item item.

The data is in 2 tables so I wanted to link them or use subreports, but I guess the amount of data or number of checks for matches that crystal had to run just took forever...about 12 minutes or so.

What I did was create a DetailKey for each Item.  This key is made up of everybit of info for each detailID surrounded by [].  Each bit of detail data is prefaced by a character (*,#,~,etc)...this way I can tell exactly where one starts and stops.

This provides me a way to throw all that in one line for the sake of reporting...this data does not get uploaded to the backup storage...the 2 tables do since they contain all the necessary info to create the key.

Anyway, I just used formula fields and parse the detailKey for each item.  I tack on the line feed Chr(13) at the end to drop the line down and give the appearance of a "Details Section" and I set the formula fields to "Can Grow"

All of this gives the appearance of a single line of generic item info, with detailed info in a "Details Section".  Maybe not the ideal solution, but it works, displays in about 7-8 seconds, exports pretty quick for about 200 pages and gives the look I needed.

Thank you all for your help in trying to solve this...
0
 
rgn2121Author Commented:
Couldn't find a suitable resolution.
0
 
James0628Commented:
Interesting solution.  Thanks for sharing it.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.