An old colleague got me hooked on Management Studio status bar colours which are cool visual clues to remind you which instance you are working in. Despite their attractiveness and usefulness, I have rarely seen this feature used. Go to your Object explorer and select Connect, then on the Connection window select the Options button to get started. Sadly only works for database connections, not SSAS etc, however still a good feature.
An excellent write up here …
A useful script for killing all processes in a database i.e. for the purposes of refreshing a training database before restore.
ALTER DATABASE [Training]
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [Training]
Then apply your restore script.
Another good link from Pinal Dave.
You can add a hard page break to a Reporting Services report by creating a small thin rectangle immediately before where the break should take place on the current page. Right click on the rectangle, and under “General” tick the box beside “Add a Page Break After”. Don’t bother hiding the rectangle as it won’t work when the Visiblity (Hidden) property is set to True.
There you go…not elegant, but functional 🙂
OVER() is fantastic for Business Intelligence and possibly under-rated, useful when you want to create a total for a particular grouping in your query but still want to see the granular transactions. The following example highlights how to sum the quantity sold for the total sale but still show the item level saleline quantity sold values.
CREATE TABLE #tmpTable
(1,100,'2012-05-01 08:00:00.000', 2)
(1,200,'2012-05-01 08:00:00.000', 5)
(1,300,'2012-05-01 08:00:00.000', 1)
(2,400,'2012-05-01 09:00:00.000', 9)
(2,500,'2012-05-01 09:00:00.000', 7)
select SaleId, SaleLineId, SaleDateTime, SoldQty,
sum(SoldQty) OVER (Partition by SaleId) as SaleTotal
drop table #tmpTable
Replace NULL or Empty values in one go with the following code snippet somewhere in your SELECT list…
COALESCE(NULLIF( a1.YourColumnName,''), 'No Name')
A good explanation of how it works at JeffsHouseOfNullIf.
You will need to rename your image file extensions to lower case otherwise you won’t be able to see the images from the Reporting Services image selector. To link images to your Reporting Services report, you can select the External property for the Image control then browse to the location of your logos in a folder your report server. If you used the Windows 7 snipping tool to save your logos, by default this tool saves images with an upper case file extension e.g. .PNG or .JPEG.
Convert those RGB Corporate colours to SSRS Hex colour codes with this handy tool.