Loading specific dates from a given interval into one line

Oracle Database has a very simple way to aggregate multiple rows into one row. It can be done easily using a listagg function. When I started to work with Microsoft SQL, I was surprised that it has no direct alternative to Oracle listagg function. SQL Server developers recommend using FOR XML PATH expression to get similar results (like here). This work really good despite it is not so easy to use. Because this operation is not so popular among SQL Server developers, I would like to share a task which I was solving this week. I believe somebody could find it useful.

This was the task definition: I have a table with employees’ holiday terms. Each term has a holiday start and holiday end dates. I have a table with a list of all public holidays in the Czech Republic. I needed to list all public holidays during each holiday term. Because there can be multiple public holidays during each request I needed to concatenate multiple days into one row with a separator. Finally, I decided to write a function which returns all public holidays from a specific interval as a varchar. This is the code of the function.

Dynamics CRM Power Pane

Dynamics CRM Power Pane is a very useful tool for Dynamics CRM. This tool is distributed as an extension for Google Chrome and you can download it in Chrome Store. (Google Chrome is one of officially supported browsers. I have been using Google Chrome with Dynamics CRM 2016 on premise for about 7 months and in my experience, it works perfectly.) Dynamics CRM Power Pane has several interesting and useful function.

It is very easy to use. After adding the extension into your Chrome you will see a new orange lightning icon in the left up corner. After clicking on the icon, you will see a menu like on the picture.

Copy contact info to clipboard by one click in Dynamics CRM

Microsoft Dynamics CRM 2016 is a very efficient application for storing contacts and accounts data. CRM users often need to share some contact or account data with another person. Sharing with other employees of the same company is easy and can be done by Email a link command. But users need to share data also with a person outside their companies. For example, employees want sometimes send a salesman contact to a potential customer.

copy-contact-info

We have developed a workflow which sends contact data to user’s e-mail and then the user can resend this e-mail to another person. However, users demanded faster and more efficient way. So I have created a button which is situated on Contact and Account entity forms. After clicking on this button the data are copied into Windows Clipboard. The user can paste the data anywhere he wants just by pressing Ctrl+V. This is a much more straightforward way and we have received positive reviews from our customers.

Bulk generating SSRS reports by C# application

We send a specific report to our customers every month. This report is generated by SQL Server Reporting Services on SQL Server 2014. The report has a parameter called Category which determines a project for which is the report generated. This parameter is dynamically generated because we send reports only for project with some activity in last month. That’s why we cannot use SSRS standard subscription function. The only standard way how to generate the report for all projects is clicking it by hand. This is time-consuming because there are usually more than one hundred projects to report. The other problem is that user could make an error and skip some project. This error would be hard to track in further process.

I decided to solve this by C# command line application. This application loads all active projects in last month. Then it generates the report in XLS format for each project. The generated files are stored in a program’s directory. The program is quite simple as you can see from the following source code.