Search Unrelated tables

[Kim296]

This is just a curiosity question for anyone who has time.

We have a huge database with hundreds of different available tables.

For example:
Incident Reports Table
Arrest Table
Accident Table
Field Contact Table
Citations Table

Each table has hundreds of availabe fields to use in an individual report. Within each table, there is a "Notes" field to add a summary of an event. I can use keywords to search these notes fields for each individual table to extract specific information. For instance, I may be looking for any suspicious activity regarding a "red truck" in a particular area in which we've had a rise in burglaries. The search feature works great when applied to an individual report. I'm looking for a way to apply this search feature to all of my "notes" fields in each table; however, the tables are unrelated to each other.

I haven't been able to figure out if it's possible to have multiple un-related tables and fields in one report for this purpose?

I tried adding all of the tables to my database, unlinking all links, then creating a subreport using the "notes" section from each table putting them in a group by their tableid, but this doesn't work. Does anyone have any suggestions as to how to make this work?

 

[kenhamady]

Adding unlinked tables to the same report is going to generate crazy inflation, but you could create a separate subreport for each table and combine them into one report. Another option is to write a SQL Command that appends the corresponding fields from each table into one long list. That would allow you to create a parameter and filter the data to your search term.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[Kim296]

Hi Kenhamady,

Thank you for your response. I have created subreports for each table as you suggested, but I'm having a problem because I don't know where to place them in my "pulled together report". I created groups for each table and placed the subreport in the footer. I added two subreports and practiced the search feature to see if this would work and it did pull back the correct data, then I added the third subreport and queried (to make sure it still worked) and it went haywire.

What is the best placement (in your opinion) to a report that only uses subreports in the content? I'm still learning this and I think this will work, but I just have to find the right combination. Thank you for the SQL tip as well. I'm not good with this, but I will do some research to see if I can figure this option out.

Thank you.

 

[kenhamady]

I would create several report footer subsections. To do this right click on the report footer name and use "insert section below".
Put one subreport in each subsection. Adding a subreport that is not linked should not have any effect on the main report.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[PeterDimitrov]

The best way will be to create a command. If you do not feel comfortable using commands you can try with a view.
Create a view to union all tables. Something like this:
Select xxx FROM Incident Reports Table
UNION
Select xxx FROM Arrest Table
UNION
Select xxx FROM Accident Table
UNION
Select xxx FROM Field Contact Table
UNION
Select xxx FROM Citations Table
Then use it in your report as a table

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[Kim296]

Thank you both for your suggestions. I will try working with the footers tomorrow and I may also try to learn the command union suggestions from Peter. It's not that I'm not comfortable creating a command union; I've never tried. If either of you can suggest a good learning book; I would love to purchase one to learn some of these things that I do not know how to do. Thank you both for your pointers. I will try them both.

 

[kenhamady]

I have written a guide to using SQL in Crystal and it covers commands and expressions. It is on the library page of my site.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[Kim296]

Thank you Ken,
I have saved your website to my favorites and plan on reading your materials. Thank you for sharing. Kim

 

[Charliy]

I've done a few reports searching text fields, and the biggest problem is that people can't spell (or make typos).
If you look for "Red Truck" (making sure to use a case-insensitive search) you will miss references to rde truck, read truck, red truk, red truch, etc.

I did a report searching a text field for "homicide". The requester said the results were too sparse so I changed it to include homocide, homiside, etc. That got what they were looking for.

 

[Kim296]

Hi Charliy,
I've noticed the same problems with spelling or abbreviating in my notes searches. I'm like you, I try to think out of the box a little with regards to this problem. I also changed all of my search fields to the same upper case as not to miss things because of case sensitivity. Thank you so much for your response.