Big performance problem with reports

Hello everyone!
I have major performance problems with reports that I have deployed in
Reporting Services.
I have an Sql database with millions of rows, and I have created a cube in
Analysis Services that is run against a view in the database.
My reports get data from the cube and the dataset is created with Sql syntax.
I need to filter the report and needed to create parameters, but only
prameters that give the option of choosing "all", i.e. being optional
parameters. The only way I can do that, as far as I know, is by creating the
filter parameter datasets in Sql Syntax. That is to be able to use union as
in
"select 'All'
union
select the real dataset
group by value" syntax. And it seems that I can't use union when running the
sql statement on a cube, so I have to run the statement on the original
database view.
Now the problem I have is that it takes about half an hour or more to view
the report, after choosing values in some filter parameters!
As far as I can see, the group by, is the statement that takes such a long
time. But that can't be the only problem.
So... does anyone know what I can do to reduce the time it takes to process
a report and what performance enhancements I can do?
I also have a problem with a specific report that gets a
System.OutOfMemoryException when I try to view it in the preview pane. But
when I run the dataset generating the report data, there are no problems.
Does anybody know what the problem might be?
I'm in a tight place and would appreciate any fast responses.
Thanks a lot,
TaraDo you have a stored procedure or is the sql code inside the RDL?
If you put as much as possible on the SQL Server, you can run it
through Query Analyzer and look at query plans etc. It does sound like
you are passing a lot more data around than you need, so get all the
SQL code out of the RDL and see where that gets you.