Working Around Temp Tables In Reporting Services.

I have been working for days to translate a report out of an old system and in SQL reporting services.

Getting the basic code down to get the required data was easy, however getting it to work in reporting services has turned into a nightmare.

Why, because I have been told that SQL reporting services does not allow temporary tables...HUH!

Ok, so how am I supposed to take three data queries and munge them together into a report.

Here is the query that does work, can anyone give me an idea of how to make this work given the limitations I have run up against.

I have already thought of using a store procedure, but we have ruled that out since would likely have to do it via linked servers, which would be expensive. We thought of having it just create and then link real tables and then delete them...not sure thats going to work, and again probably talking linked servers to get that to work.

Code:
select distinct al3.asg_location into ##tmp1
from dbo.probsummarym2 AL3
inner join dbo.probsummarym1 AL1 on AL3.number=AL1.number
where AL1.assignment='international client services'
AND AL1.status='Closed'
AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'}
and al3.asg_location is not null




SELECT AL3.asg_location as asg_location, Count (AL3.resolve_met) as met_sla into ##tmp2
FROM dbo.probsummarym1 AL1, dbo.probsummarym2 AL3
WHERE (AL3.number=AL1.number)
AND (AL1.severity_code<>'Scheduled'
AND AL1.status='Closed'
AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'}
AND AL3.resolve_met='t'
AND AL1.assignment='international client services'

)
GROUP BY AL3.asg_location

SELECT AL3.asg_location as asg_location, Count (AL1.status) as sch_closed into ##tmp3
FROM dbo.probsummarym1 AL1, dbo.probsummarym2 AL3
WHERE (AL3.number=AL1.number)
AND (AL1.assignment='international client services'
AND AL1.severity_code='Scheduled'
AND AL1.status='Closed'
AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'}

)
GROUP BY AL3.asg_location

SELECT AL3.asg_location as asg_location, Count (AL1.status) as unsch_closed into ##tmp4
FROM dbo.probsummarym1 AL1, dbo.probsummarym2 AL3
WHERE (AL3.number=AL1.number)
AND (AL1.assignment='international client services'
AND AL1.severity_code<>'Scheduled'
AND AL1.status='Closed'
AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'}

)
GROUP BY AL3.asg_location



select ##tmp1.asg_location, ##TMP3.sch_closed, ##tmp2.met_sla, ##tmp4.unsch_closed
from ##tmp1 left outer join ##tmp2 on ##tmp1.asg_location = ##tmp2.asg_location
left outer join ##tmp3 on ##tmp1.asg_location = ##tmp3.asg_location
left outer join ##tmp4 on ##tmp1.asg_location = ##tmp4.asg_location
group by ##tmp1.asg_location, ##TMP3.sch_closed, ##tmp2.met_sla, ##tmp4.unsch_closed
order by ##tmp1.asg_location

drop table ##tmp1, ##tmp2, ##tmp3, ##tmp4