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

from mc.public.crmI 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?

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


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

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: