paramaterized subquery?

[bobhagan]

Is there any way to do something like this in Crystal? This is a Visual Foxpro view where I need to find new clients in a contract by comparing activity enrollments this month to enrollments from the start of the contract up to this month:

SELECT Famserv.contr_id, (client demog fields, famserv.contract activity and group _ids)...
FROM famserv
INNER JOIN client ON Famserv.client_id = Client.client_id
WHERE Famserv.contr_id LIKE ?vp_ccontract_id
AND Famserv.start_dt >= ?vp_dstartmonth
AND Famserv.start_dt <= ?vp_dendmonth
AND Famserv.client_id
NOT IN (SELECT Famserv.client_id
FROM Famserv
WHERE Famserv.contr_id LIKE ?vp_ccontract_id
AND Famserv.start_dt < ?vp_dstartmonth
AND Famserv.start_dt >= ?vp_dstartcontract) )

I initially thought I could run Crystal on the results from this, but a VFP parameterized view doesn't show as a Crystal data source. Is there any way around that?

I thought then that I could use Crystal's parameter setup, but there is no way I've found to pass the values to the subquery. Searching the Crystal web site, it says that subqueries can only be done by using a stored procedure. How would you do that, or is there any way around that?

Thanks

Bob Hagan

 

[HowardHammerman]

Wow.

Why not just use VFP to select the records and fields that you need and save the results as a temopary table. Then
run your Crystal report from that table.

Howard Hammerman,

Crystal Reports training, consulting, books, training material, software, and support. Scheduled training in 8 cities.
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[bobhagan]

I'm using a framework (Visual Fox Express) that has a view parameter object that makes it easy to bind a dropdown lookup to the parameter (say for the contract id). The parameter set displays in their report interface. I ran into trouble when instead of the contract_id, I had to break the contract down and instead allow selection of a long list of activity or group ids. I couldn't figure out how to make a pick list for that.

We're eventually moving to SQL Server and want to standardize our reporting on Crystal, so I've been playing with that. I did a similar report (cumulative enrollments, so without a subquery) that used the default Crystal parameter dialog to select the list of ids. That solved a problem that would have made me write quite a bit of customization in VFE. My users liked it fine as well, so I tried a &quot;new enrollments in the contract&quot; report. Now a different set of issues.

I may have to go the temp table route. There's also a VFE guy doing a VFE-Crystal interface that uses ADO. He's not sure about subqueries though.

It never occurred to me that Crystal wouldn't recognize a VFP paramterized view, or that it wouldn't take a sub-query. For now, I'm trying to figure out the limits of VFE and Crystal, and work-arounds. Did I miss something?


Thanks
Bob Hagan

 

[HowardHammerman]

No you did not. That is just a limitation of Crystal.

Go the temp table route. Howard Hammerman,

Crystal Reports training, consulting, books, training material, software, and support. Scheduled training in 8 cities.
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[Ngolem]

You could do this report directly in Crystal with a main report and supreport setup

the main report would be based on the first part of the query and the subreport is based on the second &quot;NOT IN&quot; section.

If you have less than 1000 old clients the subreport could be run in the report header and the old clientID's stored in a shared array (that way the report is run only once)

Then the main report would run and you would suppress the details where

shared String array OldClients;
Famserv.client_id in OldClients;

Should be simple enough...the parameters would be common to both the Subreport and mainreport.

Hope this helps
Jim

 

[bobhagan]

Limitation - ok. I wanted to make sure I haven't missed something.

subreport - Guess I'll have to figure out how to d oa subreport.

Thanks to you both.

Bob Hagan

 

[synapsevampire]

When you eventually go the SQL Server route, you'll in essence be using the equivalent of the *temp table* approach, as your data will be returned from a stored procedure (I hope).

So carefully consider using the temp table approach, making sure you have reasonable column and parameter names (prefix them with an @ symbol as your SQL Server parameters will use this), then once you migrate you'll just have to repoint your report to the SQL Server data source and it will function. Probably...

Let the database do as much of the work as is possible, Crystal is feature rich, but it's riddled with legacy workarounds which punishes it's performance and flexibility.

 

[bobhagan]

That works!

The sub report finds the cumulative enrollments for the contract, while the main report uses the same view to find this month's enrollments. The sub-report is joined by cumulative. client_id <> thismonth.client_id. Right now each report needs entry of a separate set of parameters for the group_id list as well as the different date ranges.

Is there a way to enter this list only once? I played around with using the parameter as part of the join, but didn't get it right.

I'm afraid I don't understand stored procedures at all well. The only thing I use them for in VFP is to enforce RI. How would this work?

Bob Hagan

 

[Ngolem]

when you right-click on the subreport when in the main report...select a menu item ...called &quot;change subreport links&quot; or something like that....can you see the parameter for the main report? link that to the corresponding field in the subreport.
Jim

JimBroadbent@Hotmail.com

 

[bobhagan]

Back to square one.

<If you have less than 1000 old clients the subreport could be run in the report header and the old clientID's stored in a shared array (that way the report is run only once)

Then the main report would run and you would suppress the details where

shared String array OldClients;
Famserv.client_id in OldClients; >

<The sub-report is joined by cumulative. client_id <> thismonth.client_id. >

I got the paramters to pass to the sub-report.
I didn't quite figure out the shared array part and
set up the cume part as main and the thismonth as the subreport in the main footer. I linked in the sub
thismonth.client_id <> ?pm-cume.client_id.

Looked ok until I started testing against a NOT IN query I know works properly. The CR report is returning all enrollments in this month, without removing those that had a previous enrollment in some other activity from the contract.

1) Some of my cumulative sets have over 1000 clients, so I presume the array method won't work at all. (But how does this work anyway?)
2) Is there something inherently wrong with the not in method I used?
3) Stored procedures - looks to me in Foxpro like a view with parameters - when I tested that before, CR didn't recognize it as a data source. Am I missing something here?
4) Temp tables - The only difficulty here is that I then need to modify my Foxpro report interface to sometimes run run views, and sometimes temp tables.

Thanks again

Bob Hagan

 

[HowardHammerman]

Bob,
You have beat this one to death. You should modify your fox programs to use the temp tables. Otherwise you will be endlessly fixing problems. Howard Hammerman,

Crystal Reports training, consulting, books, training material, software, and support. Scheduled training in 8 cities.
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[bobhagan]

<You have beat this one to death. You should modify your fox programs to use the temp tables.>

Time for a different approach.

Thanks


Bob Hagan

 

[csm2]

How about creating a record set in SQL Designer, using the main query. Bring in the resulting recordset in to Report Designer and create a report using the sub-query.

 

[bobhagan]

I think that's in effect what I'm doing. I'm running the same non-param foxpro view in both report sections.

On one for a particular contract, in Crystal, I'm selecting the cumuative enrollments - start_dt 7/1/2001 to < start_dt of current month.

On the other I'm doing new enrollments - with Start_dts in the current month.

To eliminate anybody who had a previous enrollment in any contract activity, I'm looking for the client_ids of new NOT = those in the Cume. Linking the subreport &quot;new&quot; to &quot;cume&quot;, using new.client_id <> cume.client_id just doesn't seem to work. I get all start_dts in the current month, without the comparison to the cume.

This sort of thing seems to work only if the processing is done in the query, view, stored proc, etc., before running the report. Are you suggesting something different is possible in Crystal?

Bob Hagan

 

[bobhagan]

Howard

I did finally run a NOT IN query as a Fopro view, copy that to a temp table and pass that to Crystal.

Thanks