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.

mailto:user@gmail.com?subject=This%20is%20an%20email%20test

To open a webpage in a new page

="javascript:void(window.open('"+ "http://www.bbc.co.uk" + "','_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.

javascript:history.back(1)

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

Wednesday 15 June 2011

Expressions - Following Monday

=DateAdd("d",(8-Weekday(today(),0)),today())

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

Keep SSRS (SSRS2016) report manager awake

When running a report for the first time in report manager it takes a while to run, after this initial run reports run fine.  There are a ...