I am new to Crystal so please bear with me. I need to find conditional data, all from one table. I am to find data that matches w and x and then based on query results, find all data that matches y and z. It would be better explained if I illustrate. This is a simplified example of what the data looks like.

 

DocName            DocNumber       DocType              DocDate               DocID

DocJohn               54321                    6                              10/12/2008         ABC123

DocJohn               FC123                    1                              10/12/2008         ABC123

DocJohn               46581                    6                              10/12/2008         ABC123

DocDave              FC432                    1                              10/12/2008         ABC987

DocDave              87965                    6                              10/12/2008         ABC987

DocDave              57198                    6                              10/12/2008         ABC987

DocPhil                 46289                    1                              10/12/2008         ABC456

DocPhil                 17841                    6                              10/12/2008         ABC456

DocPhil                 69236                    6                              10/12/2008         ABC456

 

 

My report needs to find any “DocType” = “1“ AND “DocNumber” StartsWith “FC” and somehow save the “DocID” results. In this case we would end up only with “ABC123” and “ABC987”. I would then like to re-query the entire database and display all matching “DocID” records that have a DocType of “6”. So the resulting report would only display as follows:

 

DocName            DocNumber       DocType              DocDate               DocID

DocJohn               54321                    6                              10/12/2008         ABC123

DocJohn               46581                    6                              10/12/2008         ABC123

DocDave              87965                    6                              10/12/2008         ABC987

DocDave              57198                    6                              10/12/2008         ABC987

 

I hope this makes sense. I can only do this as 2 separate reports but don’t know how to use the Formula Editor to make this happen as one report. If anyone can help me with a starting point it would be greatly appreciated.

Edited by ctoledo - 25 Feb 2010 at 11:52am

The report cannot have parameters as it will be automatically scheduled. Yes, I want to create just one report. What's peculiar about query is that I see it as a 2 phase query. The first query will find the records that have a "DocNumber" with a StartsWith "FC" and "DocType" = "1" selection criteria and give me a list of "DocID" of the records that match. The second query will take these resulting "DocNumber" records and display all of them that have a "DocType" = "6" criteria. Dont know if I am making much sense here but the illustration I think does a good job.

"You mean you need to get the 'DocName's that meet criteria for statment 1 and then in in statment you want where statement1 docname exists AND of those the doctype=6, correct?"

EXACTLY! Unfortunately, this is all Crystal. The DB is MS SQL 2005 but dodo not have views or stored procedures associated with this report. Are you suggesting that is the way to go? I hope I can still do this using Crystal alone but if not, please enlighten me. Although I am a Crystal newbie I do a pretty good job of learning may way through, just need a starting point. Thnx much, your replies thus far are very much appreciated.

So theoretically, if I create a view that accomplishes the first filter, I should be able to run the second filter on Crystal Report?

out today but trying to help here and there.

Edited by DBlank - 01 Mar 2010 at 1:40pm