Can I speed up this report?

[kate8]

Hi,
I have a report which displays 27 fields, group by person and show three most current value bases on three different dates. It takes 30 mines to run for only one month data. My user requests one year data. But for one month, the report has already went through 3 million records since the report has join 14 tables with 4 left join. When I try to run one year data, the report runs forever. I have to cancel the job. I have tried to use command, but still can’t get it run through.
The report could run complete (even it took long time) until join the last table with three fields displayed in the report. I couldn’t figure out why that last table cost that much time. I checked the forum and Internet to try to find the ways to speed up the report. But there is no luck. Currently, I don’t have permission to create store procedure in this database.
Is there any ideas or suggestions that could help the report run faster? Or any suggestions to explain why join the last table make the report run forever?
Thank you so much for any ideas!!!

 

[kate8]

Hi,
I didn't have chance to work on this report for a while. I finally got it done.
After thought about all your suggestions, I started from the first beginning, wrote the main part of the query to get all the patients being seen in 2011 first:

SELECT DISTINCT
pe.person_id, MAX(pe.enc_timestamp) AS enc_timestamp
FROM MedDB.dbo.patient_encounter pe
INNER JOIN MedDB.dbo.master_im_ m ON (pe.person_id=m.person_id)
AND (pe.enc_id=m.enc_id)
AND m.dept='GMAP'
AND m.visit_type='Office Visit'
AND (pe.enc_timestamp>={ts '2011-01-01 10:31:33'} AND pe.enc_timestamp<{ts '2011-12-31 00:00:01'})
GROUP BY pe.person_id

Then use it to LEFT OUTER JOIN with other tables to get those most recent results and other information. And got it ran through in 20 seconds in SQL server. I used the script to create a command in Crystal Reports, it took less than 1 minute to run now.

Thank you so much for all your helps!!!