Monday, November 9, 2015

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.


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 { }

            }

Retrieving Calendar of a Bookable Resource in Dynamics

There are occasions where we need to retrieve working days and working times of a resource in Dynamics grammatically. This is quite possible...