SWITCH

Ok so a quick one here:

Want to change a property in SSRS based on the value of a field in your dataset/paramater?

Here's how:


=SWITCH(Fields!month_delta.Value > "SOME VALUE" , "Green",
Fields!month_delta.Value < "SOME VALUE", "Red")

This will accept all logical operators and you can add as many as you like, they resolve in order if there is any logical overlap though

Happy highlighting

:-)

SSAS 2008

Just back from a SQL 2008 SSAS course and hav to say, im impressed.

Expect some examples soon!

Its been busy here and i have plenty of material to write up, especialy around full text search :-)

T

sp_who2 or the "What the heck is going on" Post

Ahh back from my break and time for a very quick gem:

One on my only gripes about SQL Server 2008 is the new activity monitor,
its swish, it has filters but the layout and format is... less than desireable for those of us with small screens.

Run sp_who or sp_who2 on your box and be amazed by the wealth of usefull  information that is returned by your system in a nice tabular format. No EXEC is needed.

Its great for identifying what (and whome!) is causing your environment to run slowly as well as highlighting any blocks that are occuring.

If you havent used it yet, give it a shot, then next time you find yourself wondering  "what the heck is going on on the system" you will know where to turn...

Oh and Microsoft: Please sort this out in Denali!

SSRS: Conditional formatting - Using an expression to change chart colour

Dashboards and reports can get very crowded, very quickly and getting meaning full insight from the charts presented, can be a challenge.

In steps conditional formatting!...

In this example i will skip over how you can draw attention to key bits of information in your graphs/charts, but the principle can be applied almost everywhere.

Example:

Here in Pic 1 is a chart mapping the registrations to an online website.

As you can see, it's all over the place and isn't telling us much more than volumes

Pic 1 - My Chart

By dropping into the design and right clicking on the series we want to format (in this example its the registrations) we can then click through to edit the Series Properties:

Pic 2 - Chart in Design

Under the "Fill" tab select  the "Solid" radio button in the top half, then the fx  button next to colour:

Pic 3 - Series Options

In the Expression now we are going to use an IIF statement to decide what colours to use.
If you want something to be either one colour or another, the IIF statement probably suits your needs, howether if you need to put in multiple criteria or ranges, look up the switch function as this probably better suits your needs.

In my example though, I want to highlight the weekends a different colour to weed days as I belive they have different patterns of registration.
To go this I query day_name.value and where it is "Saturday" or "Sunday" then I ask it to be Orange, else I want it to be MidnightBlue.

The expressions format can be seen in pic 4 below:

Pic 4 - Expression Builder

Now this has been set, when I next render the report, I am met with a chart that has much more meaning when looking at it:

Pic 5 - Final Chart

By hilighting the weekends, it allows the viewer to build up a much clearer picture of what is going on.

I hope that was helpfull, there are much more worthy causes out there, but this meant to make you aware of what you can do, for you to then go on and adapt!

T

Cloud Bubble

A close friend of mine who just so happens to be a talented programmer has released his blog : http://www.cloudbubble.co.uk/

I am hoping to work with him on this project as we explore Microsoft's BI offerings, offering step by step tutorials and real world examples...

See you there,

T

SQL MERGE function...

All of us who have used databases have come accross scenarios that would have been made easier if the MERGE function was avaliable from the start...

It allows you to target a table with some data, and either update it if it allready exists or insert if it doesnt (or any other logic in between)!

Check it out here: http://technet.microsoft.com/en-us/library/bb510625.aspx

(Now is probably a good time to look at Common Table Expressions too! (CTE's))

Enjoy,

T

TechEd America 2011

Just a quick post to point out the great online resource avaliable at TechEd 2011 America

This has some presentations on the latest outputs and thoughts from Microsoft and has some great downloadable content.

Looking forward to going to TechEd 2012 Europe in Amsterdam next year (Subject to getting it signed up!)

Useful Postcode Sector Code Snippet

Thanks to a work mate for this one:

In the UK our postcodes are formatted in quite a neat way.

We often have the need to aggregate up data to different geographic area as the following code makes this simple!

Snippet:UPDATE <Table>
SET postcode_sector = (LEFT(Postcode, 1) -- First character will always be a char
                                       + CASE ISNUMERIC(SUBSTRING(Postcode, 2,1)) -- Check 2nd character to see if its numeric
                                                      WHEN 1 THEN '' -- If its numeric add nothing
                                                       ELSE SUBSTRING(Postcode, 2,1) -- Otherwise add the second character
                                           END)

This then allows you to aggregate data to the following levels:

Region	Description	Postcode Sector
EAST MIDLANDS	Cambridge	CB
EAST MIDLANDS	Colchester	CO
EAST MIDLANDS	Derby	DE
EAST MIDLANDS	Doncaster	DN
EAST MIDLANDS	Ipswich	IP
EAST MIDLANDS	Leicester	LE
EAST MIDLANDS	Lincoln	LN
EAST MIDLANDS	Norwich	NR
EAST MIDLANDS	Nottingham	NG
EAST MIDLANDS	Peterborough	PE
EAST MIDLANDS	Sheffield	S
GREATER LONDON	Bromley	BR
GREATER LONDON	Croydon	CR
GREATER LONDON	Dartford	DA
GREATER LONDON	Enfield	EN
GREATER LONDON	Harrow	HA
GREATER LONDON	Ilford	IG
GREATER LONDON	Kingston upon Thames	KT
GREATER LONDON	London E	E
GREATER LONDON	London EC	EC
GREATER LONDON	London N	N
GREATER LONDON	London NW	NW
GREATER LONDON	London SE	SE
GREATER LONDON	London SW	SW
GREATER LONDON	London W	W
GREATER LONDON	London WC	WC
GREATER LONDON	Romford	RM
GREATER LONDON	Southall	UB
GREATER LONDON	Sutton	SM
GREATER LONDON	Twickenham	TW
GREATER LONDON	Watford	WD
NORTH EAST	Cleveland	TS
NORTH EAST	Darlington	DL
NORTH EAST	Durham	DH
NORTH EAST	Harrogate	HG
NORTH EAST	Hull	HU
NORTH EAST	Leeds	LS
NORTH EAST	Newcastle	NE
NORTH EAST	Sunderland	SR
NORTH EAST	Wakefield	WF
NORTH EAST	York	YO
NORTH WEST	Blackburn	BB
NORTH WEST	Blackpool	FY
NORTH WEST	Bolton	BL
NORTH WEST	Bradford	BD
NORTH WEST	Carlisle	CA
NORTH WEST	Chester	CH
NORTH WEST	Crewe	CW
NORTH WEST	Halifax	HX
NORTH WEST	Huddersfield	HD
NORTH WEST	Lancaster	LA
NORTH WEST	Liverpool	L
NORTH WEST	Manchester	M
NORTH WEST	Oldham	OL
NORTH WEST	Preston	PR
NORTH WEST	Stockport	SK
NORTH WEST	Warrington	WA
NORTH WEST	Wigan	WN
NORTHERN IRELAND	Belfast	BT
SCOTLAND	Aberdeen	AB
SCOTLAND	Dumfries	DG
SCOTLAND	Dundee	DD
SCOTLAND	Edinburgh	EH
SCOTLAND	Falkirk	FK
SCOTLAND	Galashiels	TD
SCOTLAND	Glasgow	G
SCOTLAND	Inverness	IV
SCOTLAND	Kilmarnock	KA
SCOTLAND	Kirkaldy	KY
SCOTLAND	Kirkwall	KW
SCOTLAND	Motherwell	ML
SCOTLAND	Paisley	PA
SCOTLAND	Perth	PH
SOUTH EAST	Brighton	BN
SOUTH EAST	Canterbury	CT
SOUTH EAST	Chelmsford	CM
SOUTH EAST	Guilford	GU
SOUTH EAST	Hemel Hempstead	HP
SOUTH EAST	Luton	LU
SOUTH EAST	Medway	ME
SOUTH EAST	Milton Keynes	MK
SOUTH EAST	Oxford	OX
SOUTH EAST	Portsmouth	PO
SOUTH EAST	Reading	RG
SOUTH EAST	Redhill	RH
SOUTH EAST	Slough	SL
SOUTH EAST	Southampton	SO
SOUTH EAST	Southend on Sea	SS
SOUTH EAST	St. Albans	AL
SOUTH EAST	Stevenage	SG
SOUTH EAST	Tonbridge	TN
SOUTH WEST	Bath	BA
SOUTH WEST	Bournemouth	BH
SOUTH WEST	Bristol	BS
SOUTH WEST	Dorchester	DT
SOUTH WEST	Exeter	EX
SOUTH WEST	Gloucester	GL
SOUTH WEST	Plymouth	PL
SOUTH WEST	Salisbury	SP
SOUTH WEST	Swindon	SN
SOUTH WEST	Taunton	TA
SOUTH WEST	Torquay	TQ
SOUTH WEST	Truro	TR
UNKNOWN	UNKNOWN	NULL
WALES	Cardiff	CF
WALES	Llandrindod	LD
WALES	Llandudno	LL
WALES	Newport	NP
WALES	Shrewsbury	SY
WALES	Swansea	SA
WEST MIDLANDS	Birmingham	B
WEST MIDLANDS	Coventry	CV
WEST MIDLANDS	Dudley	DY
WEST MIDLANDS	Hereford	HR
WEST MIDLANDS	Northampton	NN
WEST MIDLANDS	Stoke on Trent	ST
WEST MIDLANDS	Telford	TF
WEST MIDLANDS	Walsall	WS
WEST MIDLANDS	Wolverhampton	WV
WEST MIDLANDS	Worcester	WR

Hope its of some use!