Master-Detail Searching, best approach?

Hello, I'm relatively new to using full-text search. I'm working on a =
document management program.
Basically my data it stored in two tables, [Documents], and =
[DocDetails], with a one-to-many relationship. [Documents] contains a =
record for each document stored, and [DocDetails] has many records =
concerning a document, each with an associated text field.
I'm really after the ability to search on a query '"jane" and "doe"', =
where I would be able to pull from [Documents] all documents that have =
matching records in [DocDetails]. My problem comes in how to query =
[DocDetails], some records would have "jane" in them, and some records =
would have "doe" in them, never would you see one record in [DocDetails] =
having both terms in the same text field. (I think this kinda negates =
using a strategy where the query has an "and" or "or" in it.
Maybe there is a way around this, I just haven't thought of. Right now, =
the only thing I can this is to split up my queries into only one word, =
and join the results.
If someone could give me a nudge in the right direction I'd appreciate =
it!
Thanks!,
--Michael
Raterus,
The below code was posted recently in regards to a "Parent - Child"
relationship, and should also work with your "Master - Detail" searching.
Tables:
contact(contact_id, name)
role(role_id, description)
contact_role (contact_id, role_id)
How can I find all contact where the search string is in the contact name
and or roles description? This is the quick and simplest way. You can
rewrite this query using outer-joins. The code is not tested. So expect some
syntax errors.
select * from contact where contactid in
(select contactid from contact_role where contactid in (select key from
containstable(contact,name,'search string') or
roleid in (select key from containstable(role,description,'search
string'))
You should be able to alter the above code to fit your tables. If you have
an alternative approach or additional questions, please post them here..
Regards,
John
"Raterus" <raterus@.spam.org> wrote in message
news:OI#yuv1NEHA.556@.TK2MSFTNGP10.phx.gbl...
Hello, I'm relatively new to using full-text search. I'm working on a
document management program.
Basically my data it stored in two tables, [Documents], and [DocDetails],
with a one-to-many relationship. [Documents] contains a record for each
document stored, and [DocDetails] has many records concerning a document,
each with an associated text field.
I'm really after the ability to search on a query '"jane" and "doe"', where
I would be able to pull from [Documents] all documents that have matching
records in [DocDetails]. My problem comes in how to query [DocDetails],
some records would have "jane" in them, and some records would have "doe" in
them, never would you see one record in [DocDetails] having both terms in
the same text field. (I think this kinda negates using a strategy where the
query has an "and" or "or" in it.
Maybe there is a way around this, I just haven't thought of. Right now, the
only thing I can this is to split up my queries into only one word, and join
the results.
If someone could give me a nudge in the right direction I'd appreciate it!
Thanks!,
--Michael
|||Actually that was the first post I read before I posted, it helped =
initally. I've adapted it a little bit and have come up with this. =
It's a mess, I know it :-) It is working how I want it to though, the =
only problem I forsee, is how do I have different queries for a =
different number of search terms. Using this strategy I'll have to have =
a different query if they search for "jane doe john brown" rather than =
just "jane doe" This is all for an asp.net web application, so it =
wouldn't be that difficult for me to create my own query on the fly and =
send it over, but if there is a better way, I'd love to hear it!
select * from documents where docID in
(
select t1.docID
from (
select docID
from docdetails
where ddID in
(
select [key]
from containstable(docdetails,value,'"jane"')
)
) as t1 inner join=20
(
select docID
from docdetails
where ddID in
(
select [key]
from containstable(docdetails,value,'"doe"')
)
) as t2 on t1.docID =3D t2.docID
)
"John Kane" <jt-kane@.comcast.net> wrote in message =
news:%235axQM2NEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Raterus,
> The below code was posted recently in regards to a "Parent - Child"
> relationship, and should also work with your "Master - Detail" =
searching.
>=20
> Tables:
> contact(contact_id, name)
> role(role_id, description)
> contact_role (contact_id, role_id)
>=20
> How can I find all contact where the search string is in the contact =
name
> and or roles description? This is the quick and simplest way. You can
> rewrite this query using outer-joins. The code is not tested. So =
expect some
> syntax errors.
>=20
> select * from contact where contactid in
> (select contactid from contact_role where contactid in (select key =
from
> containstable(contact,name,'search string') or
> roleid in (select key from containstable(role,description,'search
> string'))
>=20
> You should be able to alter the above code to fit your tables. If you =
have
> an alternative approach or additional questions, please post them =
here..
>=20
> Regards,
> John
>=20
>=20
>=20
>=20
> "Raterus" <raterus@.spam.org> wrote in message
> news:OI#yuv1NEHA.556@.TK2MSFTNGP10.phx.gbl...
> Hello, I'm relatively new to using full-text search. I'm working on a
> document management program.
>=20
> Basically my data it stored in two tables, [Documents], and =
[DocDetails],
> with a one-to-many relationship. [Documents] contains a record for =
each
> document stored, and [DocDetails] has many records concerning a =
document,
> each with an associated text field.
>=20
> I'm really after the ability to search on a query '"jane" and "doe"', =
where
> I would be able to pull from [Documents] all documents that have =
matching
> records in [DocDetails]. My problem comes in how to query =
[DocDetails],
> some records would have "jane" in them, and some records would have =
"doe" in
> them, never would you see one record in [DocDetails] having both terms =
in
> the same text field. (I think this kinda negates using a strategy =
where the
> query has an "and" or "or" in it.
>=20
> Maybe there is a way around this, I just haven't thought of. Right =
now, the
> only thing I can this is to split up my queries into only one word, =
and join
> the results.
>=20
> If someone could give me a nudge in the right direction I'd appreciate =
it!
>=20
> Thanks!,
> --Michael
>=20
>

Master->Detail Reporting

I'd like to put together a summary report for some of my management staff. The report should show the # of jobs that match about 20 different statuses. Each status has different criteria. For example, one might look for a date in a datetime field and the type of job. Another might look at whether a date has passed and the quantity we're shipping.

Sort of like this:
Status Items Jobs
Status #1: 50000 15
Status #2: 25251 3

I want the user to see this summary information, but also have drill down capability. I'd like the ability to print the summary or just a drill-down.

I'm also considering using Dundas Charts for RS on the report.

Can I accomplish this with Reporting Services? If so, any tips on how to do it? If this a bunch of sub-reports? Can I sum the # of jobs on a sub-report? Should I be looking at BI for this?

Or should I be working on a Forms-based?

Thanks!
Brian

bes7252 wrote:

I'm also considering using Dundas Charts for RS on the report.

Why would you need a chart for this? Basically you would use either a table or matrix for this.

bes7252 wrote:

Can I accomplish this with Reporting Services? If so, any tips on how to do it? If this a bunch of sub-reports?

Yes, you can accomplish this with RS. You need to craft the proper SQL query that will give you the data you need for the table. If you get stuck, there are many SQL pros here that can help you. This doesn't have to be a bunch of sub-reports. It could be as little as two -- one to drilldown on the Item field and one to drilldown on the Jobs field.

Tips:

1. Create the parent report and get the data for the report that you need using a SQL query.

2. Create the subreport layout and pass parameters as needed from the parent report to subreport.

bes7252 wrote:

Can I sum the # of jobs on a sub-report? Should I be looking at BI for this?

1. Yes

2. If you want to, but it isn't necessary.

You will find that the power of these reports is SQL. If you aren't comfortable with SQL, you probably won't be very comfortable doing this.

|||> Why would you need a chart for this? Basically you would use either a table or matrix for this.

I should have used the word Gague instead of Chart. The report is sort of a dashboard look at a section of our business. I figured if I wanted to provide more info for each status (like avg jobs/day over the last 2 weeks) then a gague might provide a nice visual.

I am comfortable with SQL, but an not familiar with all the ways to use MSSQL. It seems like every time I figure out a way to do something, someone shows me a better way. I think you're suggesting I create the columns in the SELECT statement like this:

SELECT (SELECT COUNT(*) AS EXPR1
FROM Person.Contact
WHERE (EmailPromotion = '1') AND (LastName > 'S')) AS col1,
(SELECT COUNT(*) AS EXPR1
FROM Person.Contact AS Contact_1
WHERE (EmailPromotion = '0') AND (LastName <= 'S')) AS col2

Assuming I've done this correctly, I have a concern. One of the reasons I considered a sub-reports is then I know the data from each subreport will match the total on the parent. If I use a WHERE condition (like shown above), can I make sure the drill-down data always uses the same condition? Maybe store it in a variable? I'm concerned that down the road we'll change the condition in the parent, but forget to change the detailed view.

Brian
|||

bes7252 wrote:

> If I use a WHERE condition (like shown above), can I make sure the drill-down data always uses the same condition? Maybe store it in a variable? I'm concerned that down the road we'll change the condition in the parent, but forget to change the detailed view.

Brian

The where condition shown above is what we call a 'hard-coded' value. You would want to create parameters in place of those values. In reporting services, we typically use parameters (similar to variables).

It would be something like WHERE EmailPromotion = @.emailPromotion AND LastName <= @.lastName.

When I use parameters, I like to put them in a stored procedure (as opposed to a text query).

The final step to this (once you get a stored procedure working in the parent report with parameters) is to pass the values by right clicking on the chart -> properties -> navigation. Here you can pass the parameters to the drill down.

In the drill down you would use a similar approach:

WHERE EmailPromotion = @.emailPromotion AND LastName <= @.lastName

Therefore, it would get the passed parameters from the parent report. Then the values are not "hard-coded".

|||

Greg,

That makes sense. Can I define the entire Where clause as a parameter? Then pass it in to the stored procedure and have the SQL built on the fly?

(This one might be outside the scope of this forum.) I'll probably use a ReportViewer control embedded in a WPF application. Once the user drills down he'll see job numbers. When the user clicks on a job #, can I have launch a form? Perhaps this would involve some kind of event binding between my code and the ReportViewer control?

Brian

|||

bes7252 wrote:

Can I define the entire Where clause as a parameter? Then pass it in to the stored procedure and have the SQL built on the fly?

I would just use the parameters that I indicated with @. signs as parameters. I wouldn't pass the entire where clause as a parameter.

bes7252 wrote:

When the user clicks on a job #, can I have launch a form? Perhaps this would involve some kind of event binding between my code and the ReportViewer control?

I use Visual Studio to handle all of the event binding. Do you have Visual Studio 2005 or just SQL 2005? Visual Studio handles this quite well.

|||

>> I would just use the parameters that I indicated with @. signs as parameters. I wouldn't pass the entire where clause as a parameter.

Will I suffer performance loss if I do this? I ask because the WHERE clause for each status will be drastically different. I'd prefer to guarentee the parent always matches the child by storing the entire string in a parameter.

>> I use Visual Studio to handle all of the event binding. Do you have Visual Studio 2005 or just SQL 2005? Visual Studio handles this quite well.

I have VS2005 Pro. The app uses WPF for the interface, but the ReportViewer is inside a Winform control. (I can't remeber what it's called). I'll look through the event bindings.

Thanks much for your help.

Brian