Sort report by two columns

[RepRider]

CRXI and SQL

I have a report that shows various providers and the services they perform.
It is currently sorted ascending by the average # of days to service documentation 0 days to 14 days. there are additional columns of data one which shows the total number of services during the same time period.

I would like to sort all the O days providers by the total number of services descending and the same for each number of days.

It should look something like this:
name days total services
Prov1 0 500
prov2 0 362
prov3 0 127
prov4 1 480
prov5 1 376
prov6 2 700
prov7 2 432

the data columns are formulas.
What do I need to do to make this happen?

Thanks in advance.

 

[raja2008]

Goto Record sort expert and drag the fields you want to sort into the right side area.

 

[RepRider]

The formulas I used are not listed there but I don't know why.

 

[lbass]

You need to show us the content of the formulas.

-LB

 

[RepRider]

I misspoke. The two data columns are insert>summary fields.

the number of days is an average per provider of the following formula:
@total days//
{Progress_Note.update_date}-{Service_Time_DMC.begin_date}

the total services is an insert>summary (count) on the (Service_Time_DMC.begin_date} field.

It is sorting by the number of days now summary now (ascending). I don't know how to add a sort after that within each same number of days by total services (descending).

I hope this makes it clearer.

 

[lbass]

Go into report->group sort and you will see the first sort on "Average of @total days". Add a second group sort on "count of Service_Time_DMC.begin_date".

-LB

 

[RepRider]

Thank you lbass. In trying your suggestion I found out that I need to sort ALL and then I can choose two or more levels of grouping. I had been trying to to Bottom N and then Top N. Which of course didn't work.

Although it didnt appear at first that your idea worked I realized that my first group sort data was actually displayes as rounded off to a whole number when in fact it is loaded with hidden decimals. So, it DID sort according to the underlying decimals. Is there any way I can do the second level of sort based on the ROUNDED number?

Thanks so much for your help. My level of understanding CR increases with each read here.

 

[lbass]

Interesting problem. The group sort only becomes available based on inserted summaries, and you would be unable to round the inserted summary and then use it for a group sort. So I think you would have to create a SQL expression. How are the Progress_Note and Service_Time_DMC tables linked to each other?

-LB

 

[RepRider]

Linkage occurs as:
Service_Time_DMC Table to Progress_Note_View by Consumer_Service_ID
and then to Progress_Note Table by Progress_Note_ID

Any help is appreciated. I don't know much about SQL expressions but will learn!

 

[RepRider]

If I do this correctly here is a pic of the report and database links...

http://mytapestry.com/ScreenshotShare.html

 

[lbass]

Could you please also post the query from database->show SQL query?

-LB

 

[RepRider]

certainly!


SELECT "Service_Time_DMC"."consumer_ID", "Service_Time_DMC"."begin_date",
"Service_Time_DMC"."elapsed_time", "Service_Time_DMC"."last_name",
"Consumer_Service_View"."update_date", "Service_Time_DMC"."service_description", "Service_Time_DMC"."service_code",
"Progress_Note"."update_date", "Service_Time_DMC"."first_name"
FROM (("SC_Production"."dbo"."Service_Time_DMC" "Service_Time_DMC" INNER JOIN
"SC_Production"."dbo"."Consumer_Service_View" "Consumer_Service_View" ON "Service_Time_DMC"."consumer_service_ID"="Consumer_Service_View"."consumer_service_ID") LEFT OUTER JOIN
"SC_Production"."dbo"."Progress_Note_View_dmc" "Progress_Note_View_dmc" ON "Service_Time_DMC"."consumer_service_ID"="Progress_Note_View_dmc"."consumer_service_ID") LEFT OUTER JOIN
"SC_Production"."dbo"."Progress_Note" "Progress_Note" ON "Progress_Note_View_dmc"."progress_note_ID"="Progress_Note"."progress_note_ID"
WHERE
NOT ("Service_Time_DMC"."service_code"='120' OR "Service_Time_DMC"."service_code"='170' OR "Service_Time_DMC"."service_code"='202' OR "Service_Time_DMC"."service_code"='226' OR "Service_Time_DMC"."service_code"='238' OR "Service_Time_DMC"."service_code"='239' OR "Service_Time_DMC"."service_code"='240' OR "Service_Time_DMC"."service_code"='420' OR "Service_Time_DMC"."service_code"='475' OR "Service_Time_DMC"."service_code"='530' OR "Service_Time_DMC"."service_code"='620' OR "Service_Time_DMC"."service_code"='649' OR "Service_Time_DMC"."service_code"='651' OR "Service_Time_DMC"."service_code"='683' OR "Service_Time_DMC"."service_code"='686' OR "Service_Time_DMC"."service_code"='687' OR "Service_Time_DMC"."service_code"='688' OR "Service_Time_DMC"."service_code"='690' OR "Service_Time_DMC"."service_code"='696' OR "Service_Time_DMC"."service_code"='697' OR "Service_Time_DMC"."service_code"='699' OR "Service_Time_DMC"."service_code"='816') AND NOT ("Service_Time_DMC"."last_name"='AFC DAYS' OR "Service_Time_DMC"."last_name"='COMMUNITY LIVING SUPPORTS' OR "Service_Time_DMC"."last_name"='CONNECTIONS' OR "Service_Time_DMC"."last_name"='Contracted' OR "Service_Time_DMC"."last_name"='INGERSOLL' OR "Service_Time_DMC"."last_name"='INPATIENT DAY' OR "Service_Time_DMC"."last_name"='KUBLIN' OR "Service_Time_DMC"."last_name"='MERCER' OR "Service_Time_DMC"."last_name"='MOARC SERVICE PROVIDER' OR "Service_Time_DMC"."last_name"='NICKISSON' OR "Service_Time_DMC"."last_name"='Respite' OR "Service_Time_DMC"."last_name"='THARMAR') AND ("Service_Time_DMC"."begin_date">={ts '2008-09-01 00:00:00'} AND "Service_Time_DMC"."begin_date"<{ts '2008-10-01 00:00:00'})

ORDER BY "Service_Time_DMC"."last_name", "Service_Time_DMC"."begin_date"

I hope this helps!
Thanks

 

[lbass]

I am not positive about how to use the datasource references in your case (you might have to add the owner before the actual tables names in the from clause, but try something like this {%avg}:

round((
select avg("update_date" - "begin_date")
from "Service_Time_DMC" A, "Progress_Note_View_dmc" B,
"Progress_Note" C
where A."consumer_service_ID" = B."consumer_service_ID" and
B."progress_note_ID" = C."progress_note_ID" and
A."last_name" = "Service_Time_DMC"."last_name"
),0)

Then you would place this in the detail section and insert a maximum on it (so it becomes available for a group sort). Then go to group sort and select "Maximum of %avg".

-LB

 

[RepRider]

Thank you so much for your help. I think we are almost there.
I did have to adjust the formula abit. It also wanted average instead of avg. However it stops on FROM and gives an error message that a right parens is needed but if I put it in it still comes up. Any thoughts?

Here is how it is now:
round((
select average("{Consumer_Service_View.update_date}" - "{Service_Time_DMC.begin_date}")
from "Service_Time_DMC" A, "Progress_Note_View_dmc" B,
"Progress_Note" C
where A."{Consumer_Service_View.consumer_service_ID}" = B."{Progress_Note_View_dmc.consumer_service_ID}" and
B."{Progress_Note_View_dmc.progress_note_ID}" = C."{Progress_Note.progress_note_ID}" and
A."{Service_Time_DMC.last_name}" = "{Service_Time_DMC.last_name}"
),0)

 

[lbass]

You should not be using curly brackets. Try:

round((
select average("update_date" - "begin_date")
from "Service_Time_DMC" A, "Progress_Note_View_dmc" B,
"Progress_Note" C
where A."consumer_service_ID" = B."consumer_service_ID" and
B."progress_note_ID" = C."progress_note_ID" and
A."last_name" = "Service_Time_DMC"."last_name"
),0)

Please try this exactly as is.

-LB

 

[RepRider]

OOOps, I think it should be this:

round((
select average("{Progress_Note.update_date}}" - "{Service_Time_DMC.begin_date}")
from "Service_Time_DMC" A, "Progress_Note_View_dmc" B,
"Progress_Note" C
where A."{Service_Time_DMC.consumer_service_ID}" = B."{Progress_Note_View_dmc.consumer_service_ID}" and
B."{Progress_Note_View_dmc.progress_note_ID}" = C."{Progress_Note.progress_note_ID}" and
A."{Service_Time_DMC.last_name}" = "{Service_Time_DMC.last_name}"
),0)

I still get the parens error.

 

[lbass]

No. Please see my last response.

-LB

 

[RepRider]

I think we posted at the same time 'cause I swear your previous response wasn't there !

I did insert your correction but it still highlights from and tells me there is a missing parens.
This is to be a formula right? and I should insert it into details once it working?

 

[lbass]

You should be creating this in the field explorer->SQL expression->new. If you continue getting an error message, please post what you are entering there. There is one other alternative that you could try.

-LB

 

[RepRider]

My apologies for misunderstanding and thank you for continuing to help me!

I did put your formula into SQL expression and I did get an error. Screenshot can be viewed at the following link.

I am not sure what to do next.

Thank you in advance!!