Umbrellas – Leading Zeros (0’s) inside Excel or PowerPivot

Laura has a bunch of numbers sitting in column A5 of her Excel spreadsheet showing 5 digit product codes for her umbrella business.  She received the file from a vendor, and somewhere in the process Excel stripped off the leading zeros.  

Create a new column in Excel, then apply the following formula to apply leading zeros up to the required 5 digits :


 The same formula can be used inside PowerPivot.

SSRS : Open URL in a New Window

If you are using Sharepoint, you can frame your SSRS web part into a page, then link to that page using the QuickLauch links (tick the Open link in a new wind0w box). 


Or, you may want to launch the URL from within the SSRS report but there is no such obvious option in the SSRS Report Designer or Report Builder.


Cool post on how to open a link in new window in SSRS

Basically in your expression for the Select URL Action above you need to enter an expression formatted as follows :

="javascript:void('"+ Fields!YourFormattedHttpLinkFromTheDatabase.Value + "','_blank'))"

SSRS : How to Restore a Hidden Vertical Axis

You have removed the vertical axis in your Reporting Services chart and you can’t find where to restore it.   Restore a hidden vertical axis in a Reporting Services Chart as follows :

1. Select the Chart
2. Go to the Properties pane
3. Select the Ellipses (…) beside the ChartAreas Collection


4. Select the Ellipses (…) beside the VerticalAxis Collection


5. Select the axis whether it be the Primary or Secondary axis, then set the property for Visible to True.



6. Click OK

TSQL : Convert a String to Pad Leading Zeros

Uncle Julio imported some new product codes into a database for the expanding Fajita business. However the leading zeros have been removed somewhere during the import process. The point of sale machines requires a 4 character product code so he turned to his close friend and I.T. expert Jose, to pad leading zeros in front of the raw product codes. e.g. the following table shows 3 different examples demonstrating how the different lengths of data in the raw code column require more or less leading zeros (maximum required code length of 4 characters) for the Required Code column :

Raw Code Required Code Number of Leading Zeros to Pad
3 0003 3
187 0187 1
5455 5455 0

The following snippet demonstrates how Jose managed a year’s supply of free Fajitas by padding leading zeros in front of the product codes using the REPLICATE function :

REPLICATE('0', 4-LEN(a.[Raw Code])) + CAST(a.[Raw Code] as varchar(4)) as [Required Code]
FROM dbo.table;

TSQL : Convert a Year to a Datetime

Quick snippet to generate a datetime value where you only have a year to work with. Creates a datetime value set to the 1st day of the year.

DECLARE @Year int
SET @Year = 2010
SELECT CONVERT(datetime, CONVERT(varchar(8),@Year*10000+101))

This compilation of date possibilities is also really cool – though not as cool as Dan Aykroyd…