locked
CRM Online, user access report for last 12 months using fetchXML RRS feed

  • Question

  • Hi,

    I am creating a user access report for last 12 month using fetchXML.  It is easy to implement in SQL in on premise CRM.  However, do not know how to do that in fetchXML in online CRM.   12 months has to be dynamic.  For instance, if fetchXML engine detects now is June, the current month will be 06/01 - 06/30, last month is 05/01 - 05/31, 2 month earlier will be 04/01 - 04/30.

    Likewise, if fetchXML engine detects now is Jan, the current month will be 01/01 - 01/31, last month is 12/01 - 12/31, 2 month earlier will be 11/01 - 11/30.

    Does anyone know how to achieve that using fetchXML?  I am using BIDs creating SSRS report.

    Thursday, June 23, 2016 9:21 PM

Answers

  • Hi Chicago,

    In your SSRS report you can create a hidden parameter called "CurrentDate" and default it to "=Now()", and a second parameter called "LastYear" defaulted to "=DateAdd(Year, -1, Now())".

    You can then use these in your FetchXML;

    <filter type="and">
    	<condition attribute = '<datefield>' operator='between'>
    		<value>@CurrentDate</value>
    		<value>@LastYear</value>
    	</condition>
    </filter>

    You may need to perform some conversions on the dates to get them in the correct format, but this should set you on the right track!

    ~ Nathan

    • Marked as answer by chicago bq Saturday, July 2, 2016 3:57 PM
    Monday, June 27, 2016 12:29 AM

All replies

  • Hi Chicago,

    In your SSRS report you can create a hidden parameter called "CurrentDate" and default it to "=Now()", and a second parameter called "LastYear" defaulted to "=DateAdd(Year, -1, Now())".

    You can then use these in your FetchXML;

    <filter type="and">
    	<condition attribute = '<datefield>' operator='between'>
    		<value>@CurrentDate</value>
    		<value>@LastYear</value>
    	</condition>
    </filter>

    You may need to perform some conversions on the dates to get them in the correct format, but this should set you on the right track!

    ~ Nathan

    • Marked as answer by chicago bq Saturday, July 2, 2016 3:57 PM
    Monday, June 27, 2016 12:29 AM
  • Hi, Nathan-

    Thanks for the very useful information.  Sorry, I actually meant each month of past 12 months like below:

    2016-07-01 ~ 2016-07-31

    2016-06-01 ~ 2016-06-30

    2016-05-31 ~ 2016-05-31

    --

    Friday, July 1, 2016 4:13 PM
  • Hi, Nathan-

    Initially, I used Visual Basic in Bids to program the beginning and end of each month.  However, CRM Online does not take Visual Basic.  Your posting does ring the bell!  Then, I tried the Expression in Bids to program the label of each month, the beginning date of each month and end date of each month.  CRM Online take Expression programming.

    The label of each month:  MonthName(Month(DateAdd("m", -1, Now())))

    The beginning date of each month:  DateSerial(Year(DateAdd("m", -1, Now())), Month(DateAdd("m", -1, Now())), "1")

    The end date of each month:  DateSerial(Year(DateAdd("m", -1, Now())), Month(DateAdd("m", -1, Now())), "1").AddMonths(1).AddDays(-1)

    Saturday, July 2, 2016 3:57 PM