Wednesday, May 8, 2019

Dynamic CRM SSRS Report Syntax Help


Enabling CRM Auto Filters


<fetch version="1.0" mapping="logical" distinct="false">
  <entity name="cm_invoice" enableprefiltering="1" prefilterparametername="CRM_FilteredInvoice">


Date Formatting

FORMAT(Cdate(Parameters!StartDate.Value), "dd/MM/yyyy")
Format(CDate(Fields!createdon.Value), "MM/dd/yyyy")


Switch Case Statement

   =Switch(First(Fields!cm_paymentmethod.Value, "ds_main") = "1","Bill",
           First(Fields!cm_paymentmethod.Value, "ds_main") = "2","Bill (part payment)",
           First(Fields!cm_paymentmethod.Value, "ds_main") = "3","Card (Debit)",
           First(Fields!cm_paymentmethod.Value, "ds_main") = "4","Card (Credit)",
           First(Fields!cm_paymentmethod.Value, "ds_main") = "5","Cash",
           First(Fields!cm_paymentmethod.Value, "ds_main") = "6","Voucher",
           First(Fields!cm_paymentmethod.Value, "ds_main") = "7","Cheque")


The IF Statement
=IIf(Fields!cm_vatnumber.Value is nothing,true,false)


Line Break or Enter Key

Vbcrlf

 CStr("Päiväys") + Cstr(Fields!cm_issuedate.Value) + vbcrlf +
 Cstr("Asiakasnumero") + CStr(Fields!cm_customernumber.Value)


Number Formatted with Commas (,)
Format(1212.89,"$#,#")            $1,213
Format(1212.89452,"F2")             $1212.89


Beginning of Current Month
DateSerial(Year(Date.Now), Month(Date.Now), 1)


Beginning of Last Month
DateAdd(DateInterval.Month, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1))


End of Last Month
DateAdd(DateInterval.Minute, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1))


Page Number  5th page of Total 15 pages

No comments: