locked
CRM 4 Reports and Functions RRS feed

  • Question

  • I have a function that takes a GUID and then finds the entities information and returns the name and start date and end date and the day of the week for every day that is in between the start and end date. The problem I am having is that I need to pass the GUID from CRM to the function for this report and I am not sure how to accomplish this.  

    Tuesday, September 30, 2008 4:38 PM

Answers

  • Hi,

    The idea is that you query data from MSCRM. You can specify which record the report will apply to by using prefiltering, but you can't set any set of data to a particular alias and expect it to work. You can only set an alias to a view.

    The follow sql query will set the 'test' parameter to the value of the invoice against which it will be run in MS CRM. You can now use this parameter to retrieve other data in MS CRM or even call your function.


    declare @test uniqueidentifier

    select @test = invoiceid
    FROM FilteredInvoice CRMAF_FilteredInvoice

    -- I've now retrieved the invoiceid I need to use from the filteredview. I can now use this value a a parameter in another function -- or in a select query.

    select * from FilteredInvoice
    where invoiceid = @test


    Cheers,

    Karlo

    Wednesday, October 1, 2008 5:30 PM

All replies

  • I assume by function you mean SQL function? You would need to look at filtered lookups.

    Essentially the following select query will auto filter for the applicable entity, in this example an invoice.

    The 'trick' behind this is to prefix your alias with 'CRMAF_' thus

    Select * FROM FilteredInvoice CRMAF_FilteredInvoice

    will retrieve all the relevant invoice details from the invoice in question.

    You could set your parameter equal to the invoiceid retrieved above and use that in your function.

    Cheers,

    Karlo

    more info...http://blogs.msdn.com/crm/archive/2007/01/30/inside-report-prefiltering.aspx

    Tuesday, September 30, 2008 7:46 PM
  • I had tried that... When I try to edit the reports filter CRM has a messsage that says "This report can not have a default filter".

     

    GetGuestsEventDates(@EventID) AS CRMAF_GetEventDates

    Wednesday, October 1, 2008 2:17 PM
  • Hi,

    The idea is that you query data from MSCRM. You can specify which record the report will apply to by using prefiltering, but you can't set any set of data to a particular alias and expect it to work. You can only set an alias to a view.

    The follow sql query will set the 'test' parameter to the value of the invoice against which it will be run in MS CRM. You can now use this parameter to retrieve other data in MS CRM or even call your function.


    declare @test uniqueidentifier

    select @test = invoiceid
    FROM FilteredInvoice CRMAF_FilteredInvoice

    -- I've now retrieved the invoiceid I need to use from the filteredview. I can now use this value a a parameter in another function -- or in a select query.

    select * from FilteredInvoice
    where invoiceid = @test


    Cheers,

    Karlo

    Wednesday, October 1, 2008 5:30 PM