Friday, 28 October 2011

Action Go to URL

To open a mail message from a textbox you can use the following URL in the action box of the text.

To open a webpage in a new page

="javascript:void('"+ "" + "','_blank'))"

In the report viewer there is no back button which will take tyou back to your original report. What you can do is create your own textbox which you use the action function to take you back to the main report. This refreshes the original report, you can use the following URL and this will take you back to the main report whithout refereshing it.


Thursday, 29 September 2011

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":
The colors in the matrix will alternate like a normal table.


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")

Wednesday, 15 June 2011

Expressions - Following Monday


The above expression will return the following Monday from Todays Date.