Running SQL SSRS reports without passing a parameter value on a record

Running a report on a record without passing a parameter is possible with fetch xml. (you can use pre filter attribute for this)

Is it possible to do with a SQL report? The answer is yes, it is.

How to do this?

You can write the SQL query and for this you need to add an alias which is given below.

AS CRMAF_FilteredAccount

SELECT        accountnumber, accounted FROM FilteredAccount AS CRMAF_FilteredAccount

When you do this you are automatically will be able to run the report from the record without passing a parameter.

The below article describes this nicely.

http://www.powerobjects.com/2012/05/31/microsoft-dynamics-crm-pre-filtering-for-crm-reporting/

Sharepoint List Threshold value exceeding issue (CSOM)

With one of our projects we have been having a requirement to display from asp.net web portal all the documents attached with the contact record. This was working fine in dev, qa, uat and production. Suddenly in the production environment the documents were not listing and there were no error message.

It was very strange and we were bit worried as this couldn’t be reproducible. We started digging in to production sharepoint site.

After checking the sharepoint list it was found that the folder and files for list has exceeded that threshold value which was 5000. Most of the answers were to increase this amount, but since there were couple of other sites in this environment we were bit reluctant to do that.

We were checking all the scenarios of changing the code to get the folder itself, but was getting the error of the “threshold value” all the time. finally we were able to get this sorted out. One of my colleagues helped me sort this out and his help was great (Thanks Chaminda Bandara).

The code we used we wrote a caml query to the directory directly. Code can be seen below.

using (ClientContext clientcontext = new ClientContext(siteUrl))

            {

                NetworkCredential networkCredential = null;

                                                                networkCredential = new NetworkCredential("Username", "Password", "Domain");

                clientcontext.Credentials = networkCredential;

                try

                {

                    ListItemCollectionPosition itemPosition = null;

                    Microsoft.SharePoint.Client.List list = clientcontext.Web.Lists.GetByTitle(listTitle);

                    CamlQuery camlQuery = new CamlQuery();

                    camlQuery.ListItemCollectionPosition = itemPosition;

                    camlQuery.FolderServerRelativeUrl = "/" + "List Name" + "/" + "Folder Name" + "/";

                    ListItemCollection listItems = list.GetItems(camlQuery);

                    clientcontext.Load(listItems);

                    clientcontext.ExecuteQuery();

                    if (list != null)

                    {

                        foreach (ListItem listItem in listItems)

                        {

                            if (listItem.FieldValues != null && listItem.FieldValues.Count > 0 &&

                                listItem.FieldValues.ContainsKey("FileRef") && listItem.FieldValues.ContainsKey("FileLeafRef") && listItem.FieldValues.ContainsKey("Created"))

                            {

                                string reletiveURL = listItem.FieldValues["FileRef"].ToString();

                                string fileName = listItem.FieldValues["FileLeafRef"].ToString();

                            }

                        }

                    }

                }

                catch { }

            }

CRM Toolbox to Connect to CRM Server

You install the developer toolbox to connect to CRM server to work with plugins and workflows. Sometimes after doing changes to the solution you do not see this.

If you need to get this back you need to change the solution file. You can do this by opening the solution file in notepad or notepad++ and adding the following line.

GlobalSection(CRMSolutionProperties) = preSolution
                SolutionIsBoundToCRM = True
EndGlobalSection

Once this is added to the solution, solution file will be reloaded and you will be able to see the toolbox again.

Notes (Annotationbase) entity in CRM

In CRM all the notes are attached with the annotation base entity. This means it should be having relationships with all the entities that we say yes to Notes.

So if we take the notes table we should be seeing all the relationships that notes are associated with. But is that the case?

When you take a look at the notes table you don't seems find any relationships. Then how is this possible?

In the notes entity we have 2 fields.

• objecttype
• objectid

The first field is the ones keeping the entity which the record is associated with.

The second field keeps the primary key of the record the notes records is associated with (associated entities primary key).

That is how this miracle is happening.

CRM Delete Plugin Message - Relationship record deletion

When  you are deleting a record in CRM you might have had a requirement to delete it's relationships as well with other records. How would you do this?

Is it Pre Delete message in CRM that would work with this or Post Delete message?

The answer in none of them would work. When the plugin starts executing any of the previous messages then by that time the deleted records would have dropped all the relationships with other records. This means you are no longer able to query the related data.

So is there a way to achieve this, and the answer is Yes, there is.

You can use pre valiation before deletion to be executed and this would do the trick. Please note the message pipeline for this is 10.

You can achieve what we set to achieve from this message when a record is deleted.

Activity Feeds CRM Solution

Earlier when we wanted to get this solution we were able to go to the market place for CRM and download this. However this is no longer possible.

How can we update this to the latest version?

Many of you may know it, this is being shipped with the update rollups now.  But how to get this from the update rollup.

After downloading the update rollup, you need to change the extension of that from .exe. to .zip and extract the files. In the extracted files you would be able to see the update activity feed zip file :).

Retrieving a full record in CRM

When you try retrieve a full record from CRM using all columns = true would you be getting all the columns?

We recently checked this for one of our projects and strangely we were not getting all the custom fields with the result set. we were only getting a number of columns,

Then we tried another record from the same entity. The number of columns that retrieved was more that the previous occasion. However the columns that we received had a value assigned to each of them. 

So, it seems CRM when retrieving data only sends of the columns that have values assigned to them.