CRM Forum » Microsoft Dynamics CRM

1. UG Bot
   

   admin
   Joined: Jul '08
   Posts: 2,034

   Newsgroup subject: Report - How to add pre-filter
   Archived from: microsoft.public.crm
   Author: Louanne

   Hi All

   I am writing my first report for MS CRM (version 4).
   I have written the report in SQL Server v5 BI Development and uploaded it into CRM. The report runs correctly for all records.

   The users would like to have the "Edit Filter" button.
   I think that pre-filtering needs to be turned on to do this but I'm not sure how to do this. I used Sales Pipeline as a model to do a similar report for a custom entity.
   The SQL uses FROM (' + @CRM_FilteredQ2_Job + ') as job I have tried replacing job with CRMAF_Filtered_Job but this made no difference.

   If anyone could provide me with guidance on how to enable filtering by the user it would be much appreciated.

   Thanks Louanne

   Posted 1 year ago #

2. Claudiu
   

   CRM master
   Joined: Jul '08
   Posts: 73

   Newsgroup subject: Re: Report - How to add pre-filter
   Archived from: microsoft.public.crm
   Author: SS

   You will need to add CRMAF_ to the alias name of the filtered view in your query. After adding the CRMAF_ you will have to delete the previous report that you had uploaded to the server and then upload the new report.

   SS

   Posted 1 year ago #

3. UG Bot
   

   admin
   Joined: Jul '08
   Posts: 2,034

   Newsgroup subject: Re: Report - How to add pre-filter
   Archived from: microsoft.public.crm
   Author: Louanne

   I have deleted and reloaded the report and also restarted to clear any caches. Still no luck. Does my CRMFA name need to be changed? Do I need to add CRMFA to the final temp table read?
   I'm using the Sales Pipeline report as a model and it has a default filter but does not use CRMFA in the sql.

   This is the SQL I'm using. I've removed the fields to shorten it.
   This SQL runs file over all records but the report does not allow a default filter to be applied and the Edit Filter button is missing.:

   declare @sql as nVarchar(max)
   set @sql = '
   DECLARE @Temp1 table .....
   INSERT into @Temp1
   SELECT .....
   FROM (' + @CRM_FilteredQ2_Job + ') as CRMAF_FilteredQ2_Job
   WHERE (q2_jobstate = 1 or q2_jobstate is null) and q2_joborproject = 0
   GROUP By ' + @GroupBy + ', ' + @Series + '

   UPDATE @Temp1
   set category = ''' + @CRM_Other + ''', catseq = 10000
   where category not in (select top 15 category from @Temp1 group by category order by sum(measure)desc, category)

   UPDATE @Temp1
   set catseq = 9999
   where category = ''' + @CRM_NotSpecified + '''

   SELECT * from @Temp1
   ORDER BY catseq, category
   '
   exec(@sql)

   Posted 1 year ago #

RSS feed for this topic

Reply

You must log in to post.