Colours for SQL Management Studio Status Bar

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 …

http://blog.sqlauthority.com/2009/01/04/sql-server-2008-change-color-of-status-bar-of-ssms-query-editor/

SSRS – Add a Page Break

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 🙂

Using Over() to handle totals

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

(

SaleId int,
SaleLineId int,
SaleDateTime datetime,
SoldQty int
) 

INSERT #tmpTable
VALUES
(1,100,'2012-05-01 08:00:00.000', 2)
GO
INSERT #tmpTable
VALUES
(1,200,'2012-05-01 08:00:00.000', 5)
GO
INSERT #tmpTable
VALUES
(1,300,'2012-05-01 08:00:00.000', 1)
GO
INSERT #tmpTable
VALUES
(2,400,'2012-05-01 09:00:00.000', 9)
GO
INSERT #tmpTable
VALUES
(2,500,'2012-05-01 09:00:00.000', 7)
GO


select SaleId,  SaleLineId, SaleDateTime, SoldQty, 
      sum(SoldQty) OVER (Partition by SaleId) as SaleTotal
from #tmpTable

drop table #tmpTable

SSRS – Can’t see Images on Report Server

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.