Need to grab 1st record before grouping 1

[RenaG]

I am using Crystal 10.

I have a report that I need to count the race and ethnicity for all patients in our system within a date range. I got the cross-tab working just fine. But then they threw in that they want it sorted by location. The problem is that some patients may be registered in more than one location. Is there a way to just count the patient once? I don't care which location the patient is counted in.

TIA!
~RLG

 

[RenaG]

Never mind. I had thought that once I grouped the records by location then it would count the patient at each location. But I just created two cross-tabs (one for race and one for ethnicity) with a distinct count on patient-id. Then I added a group by location and the counts in the cross-tabs remained the same. That was so much easier than I thought it would be .

Thanks!
~RLG

 

[lbass]

If you used race/ethnicity as your row field, then add location as the column field. You will then have row and column totals that look at each factor separately and uniquely, assuming you are using distinct counts as your summaries.

-LB

 

[RenaG]

Hi LB,

After playing with it some more, I realized that the Grand total column is counting only unique patients but the detail counts all the patient in that location. That may not be a bad thing. I will have to check with those who want this report. However, there are so many locations that the cross-tab runs across a couple of pages (even when set to landscape). So I decided that I would put a cross-tab in the group footer of the location. What I am stuck on now, is trying to get the cross-tab in the report footer to just show the grand total column. When I click on the cross-tab expert and select Customize style tab, under the Group Options is a Suppress Subtotal check box but it is grayed out. The documentation said that once I select a field in either the rows list or the columns list, this should become active but it doesn't. How do I suppress the subtotals in the cross-tab that is in my report footer? I think once I get that done then the report will be acceptable.

TIA!
~RLG

 

[lbass]

You are forgetting I don't know what the setup of your RF crosstab is. If you really only want the column grand total, you would remove the row field.

Regarding suppressing subtotals--sometimes you have to select a different row or column field in the hierarchy and check "suppress subtotal" to suppress the desired one. Just play around with it.

-LB

 

[RenaG]

LOL!! I do forget that as good as you are, you don't read minds! But actually, you hit the nail on the head. What I ended up doing was removing the column in the cross-tab because that is where I had put location. This is in the report footer. In the group footer, I selected the Suppress Row Grand Totals in the Customize Style tab. Since it is grouping by location I don't need the totals.

Thanks again for helping out!

~RLG

 

[RenaG]

I still don't have this report right. I was extracting from the wrong table (or let's just say there was a better table to extract from than the one I was using). So now, I am looking at the patient schedule table and need to pull just the first record. This will have the location information I need. The problem is that I need just the first record in the schedule table for this patient but the report is grouped by location. How can I group it by patient to get the first schedule record but then generate a report that is grouped by location? I hope that makes sense.

~RLG

 

[lbass]

What makes a record the "first" one--is it the most recent date or the earliest date?

What type of database are you using?

-LB

 

[RenaG]

Good morning LB,

The first record is the one with the earliest date. I am connecting via odbc to a relational database on a SQL Server 2008. Is that what you are wanting to know?

Thanks!
~RLG

 

[lbass]

Create a SQL expression {%mindt} (field explorer):

(
select min("date")
from Patient_Schedule A
where A."Patient"=Patient_Schedule."Patient"
)

Not exactly sure of the punctuation for SQL server, but replace the field names with the appropriate actual field names, but leave "A" as is, since it is acting as an alias table name here.
Also, add only the field name, not the table name within the summary, as shown.

Then in the report->selection formula->record, enter:

{Patient_Schedule.Date} = {%mindt}

This should return one record per patient.

-LB

 

[RenaG]

I'm not sure I understood what you suggested but this is what I did:

in the SQL Expression Fields I selected 'New...' -

(
select min("Schedule"."App_DtTm")
from Schedule A
where A."Pat_ID1"="Schedule"."Pat_ID1"
)

When I clicked 'Save and Close' it said there was an error in the expression.

Error in compiling SQL Expression:
Query Engine Error: '42000:[Microsoft][ODBC SQL Server Driver][SQL server] Column 'Mosaiq.dbo.schedule.pat_ID1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'.

Was I way off track in what you suggested I do or did I set it up wrong?

THANKS!!
~RLG

 

[lbass]

(
select min("App_DtTm")
from "Schedule" A
where A."Pat_ID1"="Schedule"."Pat_ID1"
)

-LB

 

[RenaG]

You are soooooo totally awesome!!!!! Thank you. If I could give you more than one star I sure would.

Ever grateful!
~RLG