cross-tab effect with summary fields 1

[scottaherbst]

Going to try and explain this clearly. I'm working with CR10, SQL server db

I am working with a client database. I'm pulling from 2 tables. The first is service_records and that gives me the client_id and service_number. I've got it joined (left-inner) to the assessment_table on client_id and service_number so it returns the date_of_assessment.

What this returns is any instance where there is a service number and the date_of_assessment (if there is any). If there wasn't an assessment given at all, it returns the service_number with a Null value for date_of_assessment. If more than one assessment was given for that service_number, it returns one line for each assessment given. I've also got a formula field that calculates the days since the assessment and another that takes that result and categorizes that record into "0-90 days", "91-180", "181 days +", and "no assessment". Here's an example minus the formula fields:

Client_ID Service_Number Date_of_assessment Location

1310 12 Detroit
1311 6 4/1/2007 Hell
1311 6 1/2/2007 Hell
1311 6 8/15/2006 Hell

I then group by client_id and have the most recent assessment given, the days since that assessment, and the category in the header. Ultimately, all I'm really interested in is the last assessment.

What I want to do is have a cross-tab (or something that looks like one) that counts patients that fall into each category for each location. How I went about this was to Put the location in rows and the categories in columns and then do a distinct count on client_id. The trouble I am running into is that I only want to count records that show up in the group header. What I am getting is, if there is a client who was given an assessment 80 days ago and one 120 days ago, it counts that client in each category. How do I construct a tab (or formula, or whatever) that will summarize this data but count only the records that show up in my group header. Any ideas are greatly appreciated. Thanks

 

[lbass]

You need to set up a command that will return either a null or the most recent date of assessment. Start a new blank report->datasource->add command and enter something like this:

SELECT `Service_Records`.`Client_ID`, `Service_Records`.`Service_Number`,
`Assessment_Table`.`Location`,`Assessment_Table`.`Client_ID`,
`Assessment_Table`.`Date_of_Assessment`
FROM `Service_Records` `Service_Records`
LEFT OUTER JOIN `Assessment_Table` `Assessment_Table` ON
`Service_Records`.`Client_ID`=`Assessment_Table`.`Client_ID` and
`Service_Records`.`Service_Number` = `Assessment_Table`.`Service_Number`
WHERE
(
`Assessment_Table`.`Date_of_Assessment` is null or
`Assessment_Table`.`Date_of_Assessment` = (select max(A.`Date_of_Assessment`)
from Assessment_Table A, Service_Records B where
B.`Client_ID` = Service_Records.`Client_ID` and
A.`Client_ID` = B.`Client_ID`
)
)
ORDER BY `Service_Records`.`Client_ID`

This will return one row per Client ID. You will then be able to insert the crosstab and get the desired results.

-LB