Archive for June, 2017

I have a dataset as shown below. When the OP_CODE is null and PRIORITY = 1, I need to shift the OP_CODE of the Priority = 2 to become 1 and 3 to become 2 and 4 to become 3.

The dataset also has the history of the changes made to the records.

At first I ranked the records by using the RANK function but it didn’t work at all. It set the ranking incorrectly as shown below. It didn’t work because it ranks every record instead of every distinct record in the partition.

I then used DENSE_RANK function as it ranks the records within the partition of a result set, without any gaps in the ranking. When there is a tie in the partition, it ranks it to the same number. In this example, there are 3 records with the Priority_Shifted of 30000. These 3 records should all be ranked to 4. The DENSE_RANK function has done it! 🙂



Read Full Post »

Apparently Microsoft has redesigned the UI of the SSRS Report Manager in particular the Data Sources bit.  In SSRS 2016, you have a dropdown list to select the credential type i.e. Windows user name and password or Database user name and  password. In the previous version, you need to tick a box that says “Use as Windows credentials when connecting to the data source”.

When I first set up my data source on SSRS 2016 report manager, I ticked the box that says “Log in using these credentials, but then try to impersonate the user viewing the report”. It was a habit that as soon as I put my credential in,  I would tick the box.

Previous version:

SSRS 2016 version:


Read Full Post »

%d bloggers like this: