SSRS Report Tasks

Common page headers and footers

Report (reportname) generated by DOMAIN/User on 02/03/2010 4:51:03 PM

="Report (" & Globals!ReportName & ") generated by " & User!UserID & " on " & Globals.ExecutionTime"

Page X of Y

="Page " & Globals!PageNumber & " of " & Globals!TotalPages

If you have start and end date parameters, and want to display them in your header or footer, with date formatted as MMM dd, YYYY

="Report Details from " & Format(Parameters!StartDate.Value, "MMM dd, yyyy") & " to " & Format(Parameters!EndDate.Value, "MMM dd, yyyy").




How to make alternating rows colored

You have to set the expression for the background color to something like:

=IIF(RowNumber("MyDataSet") Mod 2 = 0, Nothing, "LightCyan")



How to format negative numbers

Usually I will be asked to put negative numbers in brackets, and to make these numbers red. So here’s the 2 step process:

In the Format property, use this expression. Note there’s no equal sign.

#,##0.00;(#,##0.00)

In the Color property, use this expression:

=IIF(Fields!Amount.Value < 0, "Red", "Black")




Get today's date

=Today()



How to get number of rows in your result set


=CountRows("YourDataSet")




How to join selected multi-valued parameter values in a single sring


=InStr(Join(Parameters!which.Value, ", ")




How to sort a report that has been exported to Excel

- Open the excel report

- Select all (Ctrl + A)

- Go to Format > Cells

- Uncheck "Merge Cells"

- Click OK to save changes, then retry sort.

How to format date values

Important thing, make sure your value - whether it's a field or a parameter - is a DateTime data type not a String.


=Format(Parameters!Date.Value,"dd-MM-yyyy") --- 15-03-2010
=Format(Parameters!Date.Value,"dd/MM/yyyy") --- 15/03/2010
=Format(Parameters!Date.Value,"MMM-dd-yyyy") --- Mar-15-2010
=Format(Parameters!Date.Value,"MMM-dd-yy") --- Mar-15-10





Some important SSRS Expressions :

1 - Date formatting

If you have a string parameter coming in to your report (from a stored procedure for example) and need it displayed nicely, try using the expression:

=CDate(Fields!MeetingDate.Value).ToString("dd/MM/yyyy")

You will get minimum date values being displayed if the date is null for particular search criteria. If this is a problem for you, try using:

=IIF(IsDate(Fields!MeetingDate.Value),CDate(Fields!MeetingDate.Value).ToString("dd/MM/yyyy"),"")

Obviously in both these cases you can insert whatever format you'd like in place of "dd/MM/yyyy" :)

2 - IIF statement gotcha

Beware of using the IIF statement above - it works beautifully if the string is either null or a valid date but if the string is something like "tomorrow" (or even an empty string) then you will see "#Error" in your report. This is because IIF statements execute both paths when evaluating (they're not lazy enough!) so Reporting Services tries quite hard to convert "tomorrow" to a date and falls over when it can't. If you need to get around this try writing your own function (Report/Report Properties/Code) using more standard .NET IF statements and call it from your expression. Otherwise, just be aware and be careful when using IIF.

3 - Newlines in text boxes

If you're using an expression to build up a paragraph or two of text in a text box and need a new line, use this:
VBCRLF (Visual Basic Carriage Return Line Feed for those who're interested!)

E.g. ="This is line 1" + VBCRLF + "This isn't" will produce:
This is line 1
This isn't

Gotcha:

If VBCRLF is the first or last thing in your text box, Reporting Services will ignore it (I have no idea why!). Make sure you put in one extra VBCRLF at the beginning or end if you're using them there.

4 - Checkboxes

I would have thought it would be fairly common to want to display a check box on a report which is checked based on a value in the dataset but it would appear that it's not so common as there is no control provided to handle this. If your client, like mine, really wants a checkbox displayed on a report, you'll need to use wingdings. Yes, you heard right, add a small text box to your report, set the font to wingdings and use something like the following expression:

=iif(Fields!BookingMade.Value = "1",chr(0254),"o")

Feel free to play around with different wingdings characters, but I found these two gave me a nice looking checkbox that stayed the same size whether it was checked or not!

5 - Alternating row colours

Large tables look better and are easier to read if you shade alternate rows. This is easily accomplished by adding the following to the BackgroundColour expression of your detail row (substituting your desired colours of course):

=iif(RowNumber(Nothing) Mod 2, "#eefafd", "White")

I had a situation recently where my client needed alternating row colours but, for a variety of reasons, the rows displayed on the report were all group header rows. Since the RowNumber resets at every group all my rows stayed white. Using the following expression gave me header rows with alternating colours (the IDNumber is what I was grouping on):

=iif(RunningValue(Fields!IDNumber.Value, CountDistinct, nothing) mod 2,"#eefafd","White").

u need more expressions click below link:

http://krishhdax.blogspot.in/2012/10/expressions-or-functions-used-in-ax.html