subqueries in Crystal? 1

[bmacdo]

Resubmitting this from a mistaken placement in Crystal Analysis:

Records in our client enrollment view contain a flag field to indicate whether or not the funding source fields are the primary source of funding, RDV_foo.Funding_Primary_YN.

An existing report selects records where Funding_Primary_YN = "Y" and totals enrollees within facility type and by facility name, e.g.

Outdoor Facilities
Enrolled
Facility A 10
Facility B 15
-----
25

Indoor Facilities
...etc...

In the above, enrollees with no primary funding source are ignored.

I've been asked to add into the report counts of enrollees who have no primary funding source. I'm not sure how to count enrollees who only have rows of Funding_Primary_YN = "N" without counting those who have rows of Funding_Primary_YN = "N" AND rows with Funding_Primary_YN = "Y" both for the same client.

Seems to me back in the day using other reporting software I could use something called a subselect or subquery to find those who had Funding_Primary_YN = "Y" and use the select statement in the main query to exclude client_ids found in the subselect. I am not able to find mention of similar functionality in Crystal Help. I was thinking of creating the counts for those with no primary funding via a subreport.

Hope this message is understandable and thanks in advance for any and all help. I do not have the access required to create stored procedures or to create or edit views.

Funding_Primary_YN presently may contain null values.

Brad Macdonald

 

[lbass]

Please do not crosspost. I already responded to your other post.

-LB

 

[bmacdo]

I crossposted because you responded that I posted to the wrong forum. Thought I was following your wishes! Sorry.

Brad

 

[lbass]

Okay, sorry. I guess you red-flagged your own post? And now that post has been deleted.

Can you please explain what you want the resulting report to look like? Might affect the solution.

-LB

 

[bmacdo]

I didn't realize Crystal Analysis referred to a product, but thought it referred to something more general.

My first mock-up wasn't thorough. Here's what the report is supposed to look like:

Facility-Fund Enrolled
------------- --------
Fac A-Acme 5
Fac A-Ace 2
Fac A-No Primary Source 1

** Total Fac A ** 8

(The first column above is a formula combining facility and funding source fields.)

Ungrouped rows could look something like this, just to give a picture of the data:
Client_Id Facility Funding_Source Funding_Primary_YN
1234 A Acme Y
1234 A XYZ N
4343 A Acme N

A client won't be at more than one facility during the time frame for which I am selecting. The report should ignore non-primary funding rows for those clients who have primary funding indicated to be Y in another row. As I replied earlier, I did find rows with null values of the Funding_Primary_YN flag.

Thanks for looking at this!
Brad

 

[lbass]

Okay, you can handle this with a SQL expression. First create a SQL expression {%prim}:

(
select max(`Funding_Primary_YN`)
from RDV_foo A
where A.`ClientID` = RDV_foo.`ClientID` and
A.`Date` >= cdate('2008-01-01')
)

I think you have to add in your date criteria as in the last line. The syntax/punctuation will be specific to your datasource. Then in the main report, use a record selection formula like this:

{RDV_foo.Funding_Primary_YN} = {%prim}

Then create a formula {@fundsource}:

if isnull({%prim}) or
{%prim} = "N" then
"No Primary Source" else
{RDV_foo.Funding_Source}

Then create a formula {@fac-fs}:

{RDV_foo.facility}+"-"+{@fundsource}

Insert a group on facility and then a second group on this formula. Then insert a distinctcount on {@clientID} at the {@fac-fs} group level, drag the groupname into the group footer, and then suppress the detail section and group header. Insert a distinctcount on {@clientID} at the facility group level, also.

-LB

 

[bmacdo]

Thanks very much. I will have to play with this to get an understanding of your recommendation. Sure appreciate it.

Brad

 

[bmacdo]

I'm not clear about the date inclusion in the SQL expression.

In the main report, rows are selected if the enrollee's discharge date is null or in advance of a parameter field date, and ditto for a funding date. Were you suggesting I add criteria like that into the SQL expression where you placed “A.’Date’ >= cdate(‘2008-01-01’)” to get the max row of Funding_Primary_YN in the report time period?

cdate is not a recognized function name in the SQL Expression Builder. I am looking into creating a SQL Command Parameter to accomplish the same functionality … but wanted to verify the intention in your advice. If I have to create SQL code for the command parameter, how does that interact with the SQL expression I am building? Haven’t done this before. Thanks again for your help.

Brad

 

[lbass]

SQL Expressions directly access the database and so do not take into account any selection criteria you have in the main report--unless you explicitly build them into the expression. Cdate DOES work in the SQL expression area, at least for Access as a datasource. Note that some functions work without being in the function list, so you have to try them, and the functions that work vary by datasource. You could also try to_date--depends upon your datasource.

You probably shouldn't use both a command and a SQL expression. If you are using a command, it is best to make it your sole datasource for the report. While you can link tables to a command, the linking occurs locally, and will slow your report.

I don't know what you mean by command parameter.

-LB

 

[bmacdo]

Our data is in MS-SQL Server. I will try to_date, thanks. What I am wondering is if it is possible using an SQL Expression field or an SQL Command Parameter to create some version of a parameter field where the user can easily type in a date to select by as in a regular program ... rather than having to hard code a date into the program. Thanks as always ...

Brad

 

[lbass]

You can create parameters within commands (on the right), but not within SQL expressions.

-LB