SSRS Report using exec stored procedure - can it use filtering?

[Dave Carr]

I have a report that takes a long time to run, and I'd like to use
pre-filtering. But, the data source for the report is a SQL stored
procedure, and using CRMAF doesn't seem to work within the sproc. So, SRS
gurus, can you use a stored procedure as the data source, yet still enable
the user to pre-filter before running the report?

Many thanks,
Dave

[ChrisC]

Yes you can, please see this blog post -
http://mscrm4u.blogspot.com/2008/11/using-custom-database-for-reporting.html
Regards, Chris

[Dave]

Yes you can, please see this blog post -http://mscrm4u.blogspot.com/2008/11/using-custom-database-for-reporti...
Regards, Chris

Thanks for the link, Chris, but I don't think this addresses my
concern.

I'm ideally looking for a way to use the CRMAF filter mechanism to
allow the user to pre-filter the report, and pass their selections to
my stored procedure, so that the query will return data in less than
the 5 minutes it takes now.

I have a very complicated query that, written as a sproc, takes a very
long time to run. I rewrote it as a query, with CRMAF_ aliases, and
with a CTE to do the heavy lifting, and that still doesn't work, as
CRM does not recognize the CRMAF filter, probably because the CTE
comes first..

I spoke recently to someone who I consider to be an SSRS guru, and she
told me that's why she doesn't use stored procedures with CRM
reporting, because you can't get the pre-filtering to work... I'm
just trying to cover my bases (and hope she's wrong ;-)...

I'd be glad to hear any other opinions... thanks,
Dave

[ChrisC]

Hi Dave,
1. If you want to add any SQL objects you should do this in a custom
database to be supported. This necessarily means using dynamic SQL (I
believe).
2. If you pass in parameters to your stored procedure representing the
particular organization and your filter you can achieve want you want
- you should use explicit pre-filtering so the filter parameter is
CRM_FilteredEntity
(I use this technique in a production product to explicitly add a
customizable filter for [Last 30 Days]).
Chris