My Cheat Sheet for SSRS

From various websites with my try-catch-fail method :P

Thank you all who posted valuable information on the web

· Format numbers with leading zeros

=format(Fields!name.Value,"00000000")

=strdup(10-InStr(Fields!CHECK_AMT.Value, "."), "*") & Fields!name.Value & "*" è “****1111.11*”

· to int to string

=cstr(Fields!name1.Value & format(Fields!name2.Value,"00000000"))

· next line/new line

vbCrLf

· Grouping

To use 2 or more inner groups

1. Use ‘add group’ to add parent group

2. Delete the column(delete the column only)

3. Do it one more time

4. Insert a row outside group X 2 times

5. Do it all over again

6. If you can remove “[=” it would be easier so look for it

· Page break by grouping (group properties)

=Fix((RowNumber("tablename")-1) / rownumbers)

· To keep header and footer on the same page this should be used in a list

????? where did it go?

Display multi-valued parameter

=join(Parameters!processLevel.Value,",")

· Two datasets from different servers

If two tables have 1:1 relationship

=Lookup(p.key from dataset1, p.key from dataset2, column that I need, “dataset name”)

If two tables have 1:many relationship

=join(LookupsetLookup(p.key from dataset1, p.key from dataset2, column that I need, “dataset name”))

If more than one condition is needed in order to filter out or show/hide

=>break it down to one condition at a time(it is sequential)

· To Keep headers on each page

<TablixRowHierarchy>

<TablixMembers>

<TablixMember>

<RepeatOnNewPage>true</RepeatOnNewPage> ADD this Line!!

<KeepTogether>true</KeepTogether>

OR

Change row/column groups to advanced mode

open the first static property and change FixedData, KeepTogether, KeepWithGroup, RepeatOnNewPage to True as you need

· Suppressing Null or Zero Values at Run Time

=IIF(Field!B.Value=0, 0, Field!A.Value / IIF(Field!B.Value =0, 1, Field!B.Value))

*** to avoid Attempted to divide by zero Error, use =iif(A=0, 0, B / iif(A=0, 1, A))

· Multiple value parameters

Dataset Properties => Parameters => add a parameter that has multiple values

Parameter Value => =split(join(Parameters!Name.Value,","),",")

And within the where clause, add columnName in (@Name)

· First day of week(Monday)

=DateAdd("d", -(WeekDay(Today(),2))+1, Today())

So It can be changed any day

DateAdd("d", -(WeekDay(Parameters!Parameter1.Value,2))+1, Parameters!Parameter1.Value)

--next monday:

=DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())

--First day of month:

=DateAdd("d",1-DatePart("d",Today()),Today())

--First day of next month:

=dateadd("m",1,DateAdd("d",1-DatePart("d",Today()),Today()))

--First day of year:

=DateSerial(YEAR(Today()),1,1)

--First day of next year:

=DateSerial(YEAR(Today())+1,1,1)

================More===================

Set First Day of previous week (Monday)

=DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today))

Set First Day of Current Week (Monday)

=DateAdd("d", 1 - DatePart(DateInterval.WeekDay, Today(),FirstDayOfWeek.Monday), Today())

Set Last Day of previous week (Sunday)

=DateAdd(DateInterval.Day, -0,DateAdd(DateInterval.Day, 1-Weekday(today),Today))

Set First Date of last month

=DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))

*format decimals with expression

=Format(Fields!Price.Value, "C")

*to remove time from getdate()

convert(varchar(8),dateadd(dd,-1,GETDATE()),112)

*Page number by groups

*need to find a way to do it on BI

Open up with reporting builder and change the value in Group properties => Group => PageBreak

BreakLocation to Between and ResetPageNumber to True