T sql Scripts

Here are some scripts which I use now and then, it is quite useful to have these handy.

--- percentage Calculation returns zero
select (43/50) * 100

---percentage calculation where you cast the values
select cast(43 as float)/cast(50 as float)*100


---percentage calculation
---with 2 decimal places
select convert(decimal(10,2),cast(43 as float)/cast(50 as float)*100)

---Date Scripts

select datepart(d,getdate()) --returns day number
select datepart(dw,getdate()) --returns day of week, day 1 = Sunday and 7 = Saturday

select datename(month,GETDATE()) --retunrs Month Name
select left(datename(month,GETDATE()),3) --returns first 3 letters of Month Name

select datename(WEEKDAY,GETDATE()) --returns Day Name
select left(datename(WEEKDAY,GETDATE()),3) --returns first 3 letters of Day Name

Alternate Colors in a Matrix/Pivot Table

To add alternate colors in a matrix the expression should be as follows for the row group:


=IIf( RunningValue (Fields!FIELDNAME.Value, CountDistinct, Nothing) MOD 2, "Transparent", "aliceblue")


The tricky bit is when you want the same shading for the column groups. The way to do this is to insert a column outside of the row group and then use the same expression. Change the name of the textbox to "color". Then in your column group or data field in your matrix in the background color insert the expression which refers to the textbox "color":
=Reportitems!color.value
The colors in the matrix will alternate like a normal table.

Wahayy!!

Also in a anormal table where you have multiple groups you can use the following script for say 2 groups I assume you just use '&' and add in the extra fields if you have more than one group:

=IIF(RunningValue(Fields!Field1.Value & Fields!Field2.Value,CountDistinct, Nothing) MOD 2 = 1, "White", "Aliceblue")