MS BI Useful Codes

SSRS: To change text in a text box based on an IF condition:

=IIF(
Fields!State.Value = "Queensland",
"QLD",
Fields!State.Value
)

=IIF(

RTRIM(ReportItems!Product.Value) = "EMPTY"

,"N/A"

,Fields!Desc.Value

)

IIF Expression for Div By Zero

=IIF

(SUM(Fields!denominator) = 0,0,

( SUM(Fields!numerator.Value) / 

IIF( SUM(Fields!denominator.Value)= 0, 1, SUM(Fields!denominator.Value) )

        ) *100

)

CASE with conditions (OR)

SELECT *,

CASE
WHEN BCode = 'E6004' OR BrandCode = 'E6001'
THEN 'East'
WHEN BCode = 'EG001'
THEN 'West'
WHEN BCode = 'E6003'
THEN 'South'
ELSE 'NA'
END AS NewField

FROM Province

SSRS: Switch Statement 

=Switch( 

        (Fields!BCode .value)= "08","Brown", 

        (Fields!BCode .value)= "06","Gold", 

        (Fields!BCode .value)= "05","DarkGreen", 

        (Fields!BCode .value)= "09","DarkRed", 

        (Fields!BCode .value)= "04","DimGray", 

        (Fields!BCode .value)= "03","Goldenrod", 

        ,"Blue"

        )

=Switch(

Fields!Week.Value=1

,FORMAT(Parameters!Week1From.Value,"dd")+ " to " +

FORMAT(Parameters!Week1To.Value,"dd-MMMM-yyyy")

,Fields!Week.Value=2

,FORMAT(Parameters!Week2From.Value,"dd")+ " to " +

FORMAT(Parameters!Week2To.Value,"dd-MMMM-yyyy")

,Fields!Week.Value=3

,FORMAT(Parameters!Week3From.Value,"dd")+ " to " +

FORMAT(Parameters!Week3To.Value,"dd-MMMM-yyyy")

,Fields!WeekNo.Value=4

,FORMAT(Parameters!Week4From.Value,"dd")+ " to " +

FORMAT(Parameters!Week4To.Value,"dd-MMMM-yyyy")


Enter below code in text box background as expression in SSRS

=IIF(
      Fields!Desc.Value = "Queensland", "OliveDrab",
       IIF(
            Fields!Desc.Value = "Victoria","#a2d985",
              IIF(
                  Fields!Desc.Value = "Canbera","#d1d100",
                    IIF(
                        Fields!Desc.Value = "Perth","IndianRed",
                          IIF(
                              Fields!Desc.Value = "Sydney","#a78f60","Silver"
                              )
                        )
                  )
           )
       )

Padding with Zero

'Prod' + RIGHT( '000000' + CAST(Product.ProductNo AS varchar), 6) AS NProdNo,


OUTPUT:
Prod000000
Prod000004

SQL Query Declaration

BEGIN

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '02/01/2012' --mm/dd/yyyy
SET @EndDate = '02/29/2012'

DECLARE @FiscalYear INT
DECLARE @FiscalPeriod INT
SET @FiscalYear = '2011'
SET @FiscalPeriod  = '11'


END