Dynamic CRM SSRS Report best configuration

Hey! Can we shift the logo to the top most?

Can we put a page number in the bottom and make it center align 

Can we shift an overall footer to the very bottom of the page?

If you involve yourself with SSRS report development, then you often hear such comments from your customer. 
When trying to export report in PDF or in MS-Word there will be more surprises and when it comes to printing your might be like - Awwwwww Jesus.

After making a lot of adjustments and doing trial and error one can have a perfect report. Some reports like Quotation or Invoice, which are mailed to customers requires an out most precision.

In this blog post, I would be listing out few important properties which must not be missed when it comes to SSRS report.

Note: I am referring A4 paper size and all settings are considered based on this.

Page Setup

Right-Click outside the report body area to get to Report Properties.

• These setting works perfect for PDF / Word / Printing
• Notice bottom margin is set to 0cm, which helps in keeping footer to very bottom

Report Body Size

Report body can have three sections - Header, main body and footer. All three sections must have WIDTH of 20cm which is 1cm less than Page width size.

The HEIGHT should be 28.7cm in total of height of Header + Footer + Main Body.

If report is not having header and footer section than we can utilize full height of 28.7cm for main report body.

A typical example:

Page Header and Footer

Once Page layout is configured, report body boundary should stay within that otherwise it may result in weird behavior in PDF or MS-Word.

Header and Footer space should be kept minimum as it is going to repeat itself in all the pages.

Information that may goes on header or Footer

• Company Logo
• Customer Number
• Quotation Number
• Invoice Number
• Page Number
• Company Contact details – Address / Phone
• Company VAT Details
• Page Number  5^th page of Total 15 pages

Page Number Formula

=CStr(Globals!PageNumber) + "/" + CStr(Globals!OverallTotalPages)

Alignment and Padding

Vertical Align for all data type – Middle

Text Align

Numbers, Decimals, Money or Currency – Right Align

Text – Left Align

Padding : It specify the amount of space/padding between report item boundary

There can be more settings which could result in better report. 
Share your findings in the comments sections if you have any.  

Here are more SSRS related blog to follow

How to create SSRS Report in Dynamic 365 CRM

https://vjcity.blogspot.com/2019/12/how-to-create-ssrs-report-in-dynamic.html

How to setup Workstation to Create SSRS Reports for Dynamics 365

https://vjcity.blogspot.com/2019/12/how-to-setup-workstation-to-create-ssrs.html

Report not appearing for certain users

https://vjcity.blogspot.com/2019/07/reports-missing-for-certain-users-in.html

Dynamic CRM SSRS Report common syntax help
https://vjcity.blogspot.com/2019/05/dynamic-crm-ssrs-report-syntax-help.html


If you are looking for an expert in Dynamic CRM or in SSRS Report, please feel free to reach me.

Thanks.

Vipin Jaiswal 

vipinjaiswal12@gmail.com

How to Won, Lost or Reopen opportunity in Dynamic CRM using Web API

First here is a list of default value for Status and Status Reason for opportunity entity

State	Status Reason
0 : Open	1 : In Progress
	2 : On Hold
1 : Won	3 : Won
2 : Lost	4 : Canceled
	5 : Out-Sold

JSON Data for WinOpportunity

{

  "OpportunityClose": {

                                                // Replace Opportunity ID here

    "opportunityid@odata.bind": "/opportunities(346e5406-cea3-ea11-a812-000d3a3be645)",

    "actualend": "07/12/2020",

    "actualrevenue": 100,

    "description": "Your description here"

  },

  "Status": 3

}

JSON Data for LoseOpportunity

{

  "OpportunityClose": {

    "opportunityid@odata.bind": "/opportunities(346e5406-cea3-ea11-a812-000d3a3be645)",

    "actualend": "07/12/2020",

    "description": "Your description here"

  },

  "Status": 5

}

Re_Open 

Note the request method is Patch (Update) and not POST

Only one field to reopen again

{

  "statecode": 0

}

**Appointment in Dynamic CRM using Web API

How to get security role of a logged-in user in D365 using JavaScript

We can get user security roles from Dynamic CRM Global context in two ways

var userRoles= Xrm.Utility.getGlobalContext().userSettings.roles;

Or

var userRoles= executionContext.getContext().userSettings.roles;

Sample code that to work on both classic UI and new UCI is below 

// JavaScript source code

function CheckUserSecurityRole(executionContext) {

       debugger;

       var roles = Xrm.Utility.getGlobalContext().userSettings.roles;

       if (roles === null) return false;

 

       if (roles == undefined) {

             roles = RetrieveLoggedInD365UserSecurityRoles();

       }

 

       var hasRole = false;

       roles.forEach(function (item) {

             if (item.name.toLowerCase() === "sales person") {

                    LockFormOnLoad(executionContext);

             }

       });

}

 

// Lock all the field on the form if Owner of the entity record is not the logged-in user

function LockFormOnLoad(executionContext) {

       var formContext = executionContext.getFormContext();

       if (formContext.getAttribute("ownerid") != null) {

             var owner = formContext.getAttribute("ownerid").getValue()[0];

             if (owner.id != Xrm.Utility.getGlobalContext().userSettings.userId) {

                    var controls = formContext.getControl();

                    controls.forEach(function (item) {

                           if (item.getName() != "" && item.getName() != null) {

                                 if (item.getDisabled && item.setDisabled && !item.getDisabled()) {

                                        item.setDisabled(true);

                                 }

                           }

                    });

                    formContext.ui.setFormNotification("Sales Person cannot edit the Quote owned by other person", "INFO");

             }

       }

}

 

function RetrieveLoggedInD365UserSecurityRoles() {

       var resultset = "";

       var fetchXMLCondition = "";

       var userSettings = Xrm.Utility.getGlobalContext().userSettings;

       if (userSettings.securityRoles.length > 0) {

             var i;

             for (i = 0; i < userSettings.securityRoles.length; i++) {

                    fetchXMLCondition += "<condition attribute='roleid' operator='eq' value='" + userSettings.securityRoles[i] + "'/>";

             }

             var fetchXML = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" +

                    "<entity name='role'>" +

                    "<attribute name='roleid' />" +

                    "<attribute name='name' />" +

                    "<order attribute='name' descending='false' />" +

                    "<filter type='or'>" + fetchXMLCondition +

                    "</filter>" +

                    "</entity>" +

                    "</fetch>";

             resultset = FetchXML_GetRecords(fetchXML, "roles");

       }

       return resultset;

}

 

function FetchXML_GetRecords(originalFetch, entityname) {

       var records;

       var fetch = encodeURI(originalFetch);

       var serverURL = Xrm.Page.context.getClientUrl();

       var Query = entityname + "?fetchXml=" + fetch;

       var req = new XMLHttpRequest();

       req.open("GET", serverURL + "/api/data/v9.0/" + Query, false);

       req.setRequestHeader("OData-MaxVersion", "4.0");

       req.setRequestHeader("OData-Version", "4.0");

       req.setRequestHeader("Accept", "application/json");

       req.setRequestHeader("Content-Type", "application/json; charset=utf-8");

       req.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");

       req.onreadystatechange = function () {

             if (this.readyState === 4) {

                    req.onreadystatechange = null;

                    if (this.status === 200) {

                           var results = JSON.parse(this.response);

                           if (results != null) {

                                 records = results.value;

                           }

                    } else {

                           Xrm.Utility.alertDialog(this.statusText);

                    }

             }

       };

       req.send();

       return records;

}

 

 

Refer more articles here

Generic way to Refresh Rollup field in Dynamic CRM

Specific page number in pagination of web api

Guidelines to write good JavaScript code in Dynamic CRM

Generic Web API methods for Dynamic CRM in C#