MSBI Cheat Sheet
SSIS Expression Cheat Sheet
Problems
Expression
Expression on the Flat File or File Connection Manager:
Create a file name
with today's date
"C:\\Project\\MyExtract" + (DT_WSTR, 30)(DT_DBDATE)GETDATE() + ".csv"
Expression Output Example: C:\Project\MyExtract2009-03-20.csv
Use a 2 digit date
(ex. "03" for March
instead of "3")
RIGHT("0" + (DT_WSTR, 2)MONTH(GETDATE()),2)
Expression Output: 03 (if the month is March)
Multiple condition
if statement
In this example, the statement determines that if the ColumnName is blank
or NULL, it will be set to unknown. To make a Logical AND condition, use
"&&" instead of the "||" operator.
ISNULL(ColumnName)||TRIM(ColumnName)==""?"Unknown": ColumnName
Returns the first
five characters
from a zip code
Remove a given
character from a
string (ex. Remove
"-" from a social
security number)
Derived Column Transform in the Data Flow:
SUBSTRING(ZipCodePlus4,1,5)
Derived Column Transform in the Data Flow:
REPLACE(SocialSecurityNumber, "-","")
Derived Column Transfrom in the Data Flow:
Uppercase data
UPPER(ColumnName)
Replace NULL with
another value
Derived Column Transform in the Data flow:
ISNULL(ColumnName)?"New Value" : ColumnName
Derived Column Transform in the Data Flow:
Replace blanks with
NULL values
TRIM(ColumnName)=="" ? (DT_STR, 4, 1252)NULL(DT_STR, 4, 1252) :
ColumnName
Script Transform in the Data Flow Task with the code as follows (VB 2008):
Imports System.Text.RegularExpressions
Public Overrides Sub Input()_ProcessInputRows(ByVal Row As Input()Buffer)
Remove any nonnumeric data from
a column
If Row.ColumnName_IsNull = False Or Row.ColumnName = "" Then
Dim pattern As String = String.Empty
Dim r As Regex = Nothing
pattern = "[^0-9]"
r = New Regex(pattern, RegexOptions.Compiled)
Row.ColumnName = Regex.Replace(Row.ColumnName, pattern, "")
End If
End Sub
Convert text to
proper case
(ex. 1st letter in
each word is
uppercase)
Script Transform with the line of partial code as follows:
Row.OutputName = StrConv(Row.InputName, VBStrConv.ProperCase)
Expression on the SQLStatementSource property of Execute SQL Task:
Build dynamic SQL
statement
Round to the
nearest two
decimal mark
"SELECT Column From " + @[User::TableName] +WHERE
DateFilterColumn = '" + (DT_WSTR,4)YEAR(@
[User::DateTimeVar]) + RIGHT("0" + (DT_WSTR,2)MONTH(@
[User::DateTimeVar]),2) + RIGHT("0" + (DT_WSTR,2)DAY(@
[User::DateTimeVar]),2) + "'"
Expression on Derived Column Transform:
ROUND(YourNumber, 2)
Expression Output Example: 1.2600000
Common SSIS Problems and Solutions
Problems
Solutions
Tasks Required: Foreach Loop, Data Flow Task
Loop over a list
of files & load
each one
Solution: Configure the Foreach Loop to loop over any particular directory of
files. The loop should be configured to output to a given variable. Map the
given variable to a connection manager by using expressions.
Conditionally
executing tasks
Solution: Double-click the precedence constraint and set the Evaluation
property to Expression and Constraint. Type the condition that you want to
evaluate in the Expression box.
Pass in variables
when scheduling
or running a
package
Solution: Use the /SET command in the DTExec command line or change the
Property tab in the Package Execution Utility to have the property path like:
\Package.Variables[User::VariableName].Properties[Value]
Tasks Required: File System Task
Move and
rename the file
at the same time
Loop over an
array of data in a
table & perform
a set of tasks for
each row
Solution: Set the File System task to rename the file and point to the directory
you'd like to move the file to. This enables you to rename and move the file in
the same step.
Tasks Required: Execute SQL Task, Foreach Loop
Solution: Use an Execute SQL Task to load the array and send the data into an
object variable. Loop over the variable in a Foreach Loop by use an ADO
Enumerator.
Tasks Required: 2 Execute SQL Tasks, Data Flow Task
Perform an
incremental load
of data
Solution: Have the 1st Execute SQL Task retrieve a date from a control table of
when the target table was last loaded and place that into a variable. In the Data
Flow Task, create a date range on your query using the variable. Then, update
the control table using a 2nd Execute SQL Task to specify when the table was
last updated.
Components Required: Data Flow Task, Conditional Split, Lookup Transform or
Merge Join, OLE DB Command Transform
Perform a
conditional
update & insert
Solution: Use the lookup Transform or Merge Join to detemine if the row exists
on the destination and ignore a failed match. If the row yields blank on the key,
then you know the row should be inserted into the target (by Conditional Split).
Otherwise, the row is a duplicate or an update. Determine if the row is an
update by comparing the source value to the target value in the Conditional
Split. The update can be done by an OLE DB Command Transform or by loading
the data into a staging table.
Derived Column Transform in the Data Flow:
Replace blanks
with NULL values
TRIM(ColumnName)=="" ? (DT_STR, 4, 1252)NULL(DT_STR, 4, 1252) :
ColumnName
Script Transform in the Data Flow Task with the code as follows (VB 2008):
Imports System.Text.RegularExpressions
Public Overrides Sub Input()_ProcessInputRows(ByVal Row As Input()Buffer)
Remove any nonnumeric data
from a column
If Row.ColumnName_IsNull = False Or Row.ColumnName = "" Then
Dim pattern As String = String.Empty
Dim r As Regex = Nothing
pattern = "[^0-9]"
r = New Regex(pattern, RegexOptions.Compiled)
Row.ColumnName = Regex.Replace(Row.ColumnName, pattern, "")
End If
End Sub
Convert text to
proper case
(ex. 1st letter in
each word is
uppercase)
Script Transform with the line of partial code as follows:
Row.OutputName = StrConv(Row.InputName, VBStrConv.ProperCase)
Expression on the SQLStatementSource property of Execute SQL Task:
Build dynamic
SQL statement
"SELECT Column From " + @[User::TableName] +WHERE
DateFilterColumn = '" + (DT_WSTR,4)YEAR(@
[User::DateTimeVar]) + RIGHT("0" + (DT_WSTR,2)MONTH(@
[User::DateTimeVar]),2) + RIGHT("0" + (DT_WSTR,2)DAY(@
[User::DateTimeVar]),2) + "'"
Calculate
beginning of the
previous month
Expression on Derived Column Transform:
Round to the
nearest two
decimal mark
Expression on Derived Column Transform:
(DT_DATE) (DT_DBDATE)DATEADD("dd" , -1*(DAY(GETDATE()) -1),
DATEADD("month", -1, GETDATE()))
Expression on Derived Column Transform: ROUND(YourNumber, 2)
Expression Output Example: 1.2600000
MDX Expression Cheat Sheet
Problems
Calculation Expression
Simple Gross
Profit Calculation
[Measures].[Sales Amount] - [Measures].[Total Product Cost]
Sales in the USA
([Measures].[Sales Amount], [Customer].[Country].&[United States])
Year to Date
Sales (Works for
any level of Date
Hiearchy)
Aggregate(
PeriodsToDate( [Date].[Calendar Hierarchy].[Year],
[Date].[Calendar Hierarchy].CurrentMember ),
([Measures].[Sales]))
Alternate Year To
Date Expression
(YTD, QTD, MTD)
AGGREGATE (
YTD ([Date Order].[Calendar].CurrentMember),
Measures.[Sales Amount])
Product Ranking
IIF (Product.Product.CurrentMember IS Product.Product.[All],NULL,
IIF (Measures.[Sales Amount] = 0, NULL,
RANK(Product.Product.CurrentMember, ORDER (Product.Product.Members,
Measures.[Sales Amount]))))
Sales from 365
Days Ago
(ParallelPeriod([Invoice Date].[Date Hierarchy].[Date], 365, [Invoice Date].[Date
Hierarchy].CurrentMember), [Measures].[Sales Amount])
Sales in the
Previous Period
(Measures.[Sales Amount], [Date
Order].[Calendar].CurrentMember.PrevMember)
Top 10 Selling
Products (Named
Set)
TopCount (Product.Product.Product.Members,10,Measures.[Sales Amount])
Three Years
Average Sales
From NOW()
Avg(
{ParallelPeriod( [Date].[Date].[Year], 3,
StrToMember("[Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")) :
StrToMember("[Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")} ,
[Measures].[Sales Count])
Drillthrough
Action Caption
'Get Sales Details for'
+[Product].[Product].CurrentMember.Member_Caption
Change SSAS
Calculation Text
color
IIF([Measures].[Profit Percentage] < .40, 255 , 0)
Changing a
Calculation with
a SCOPE
statement
SCOPE ([Measures].[Scope Profit]);
THIS = ([Measures].[Sales Amount] - [Measures].[Standard Product Cost]);
END SCOPE;
Clear Ration
Value when at all
level
SCOPE ([Customer].[Customer Geography].[All], Measures.RatioOverParent);
THIS = NULL
SSAS KPI Value
Expression
[Measures].[Sales Amount] * 1.2
SSAS KPI Goal
Expression
Case
When IsEmpty
(ParallelPeriod
([Date Order].[Fiscal].[Fiscal Year],
1,[Date Order].[Fiscal].CurrentMember))
Then [Measures].[Sales Amount]
Else 1.10 *
([Measures].[Sales Amount],
ParallelPeriod([Date Order].[Fiscal].[Fiscal Year],
1,[Date Order].[Fiscal].CurrentMember))
End
SSAS KPI Status
Expression
Case
When KpiValue( "Sales Revenue YTD" ) / KpiGoal( "Sales RevenueYTD" ) > 1
Then 1
When KpiValue( "Sales Revenue YTD" ) / KpiGoal( "Sales Revenue YTD" ) <= 1
And
KpiValue( "Sales Revenue YTD" ) / KpiGoal( "Sales Revenue YTD" ) >=
.85
Then 0
Else -1
End
SSAS KPI Trend
Expression
Case
When IsEmpty
(ParallelPeriod
([Date Order].[Fiscal].[Fiscal Year],
1,[Date Order].[Fiscal].CurrentMember))
Then 0
When VBA!Abs
((KpiValue( "Sales Revenue YTD" )- (KpiValue( "Sales Revenue YTD" ),
ParallelPeriod( [Date Order].[Fiscal].[Fiscal Year],
1, [Date Order].[Fiscal].CurrentMember)))
/(KpiValue( "Sales Revenue YTD" ),
ParallelPeriod
( [Date Order].[Fiscal].[Fiscal Year],
1,[Date Order].[Fiscal].CurrentMember))) <=.02
Then 0
When (KpiValue( "Sales Revenue YTD" )- (KpiValue( "Sales Revenue YTD" ),
ParallelPeriod
( [Date Order].[Fiscal].[Fiscal Year], 1,
[Date Order].[Fiscal].CurrentMember)))
/(KpiValue( "Sales Revenue YTD" ),
ParallelPeriod( [Date Order].[Fiscal].[Fiscal Year],1,[Date
Order].[Fiscal].CurrentMember)) >.02
Then 1
Else -1
End
SSRS Expression Cheat Sheet
Problems
Return first day of current Week
(ex. Default Start Date parameter
to return WTD)
Return first day of current Month
(ex. Default Start Date parameter
to return MTD)
Expression
Expression on the parameter default value:
=DateAdd("d",-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today)
Expression Output Example: 11/7/2010 12:00:00 AM
Expression on the parameter default value:
=DateAdd("d",-(Day(today)-1), Today)
or
=DateSerial( year(today()), month(today()), 1)
Expression Output Example:11/1/2010 12:00:00 AM
Expression on the parameter default value:
Return first day of current year
ex. Default Start Date parameter
to return YTD)
=DateAdd("d",DatePart(DateInterval.DayOfYear,Today,0,0)+1,Today)
Expression Output Example: 1/1/2010 12:00:00 AM
Expression on the parameter default value:
Week over Week
=DateAdd("ww",-1, Today)
Return period over period
(ex. Default date parameters to a
rolling year)
Month over Month
=DateAdd("m",-1,Today)
Year over Year
=DateAdd("yyyy",-1, Today)
Expression Output Example:10/9/2010 12:00:00 AM
Return current month name
Expression in Text Box:
=MonthName(Month(Today()))
Expression Output Example:November
Uppercase fields
Expression in Text Box:
=UCASE(Fields!FieldName.Value)
Expression Output Example:NOVEMBER
Convert text to proper case
(ex. 1st letter in each word is
uppercase)
Expression in Text Box:
=StrConv(Fields!FieldName.Value, VbStrConv.ProperCase)
Replace NULL with another value
Expression in Text Box:
=iif(Fields!FieldName.Value = nothing, "No Value",Fields!
FieldName.Value)
Alternating row color (Banding
effect)
BackgroundColor property on Text Box:
=iif(RowNumber(Nothing) Mod 2 = 0, "Silver", "White")
Expression in Text Box:
Handling division by zero
=iif(Fields!DenominatorField.Value = 0, 0,
Fields!NumeratorField.Value/
iif(Fields!DenominatorField.Value = 0, 1, Fields!
DenominatorField.Value))
security number)
Expression in Text Box:
=Replace(Fields!EmailAddress.Value,"-","")
Common SSRS Problems and Solutions
Problems
Solutions
Parameter
with
dropdown
box
Solution: Create an additional dataset that with populate the parameter dropdown
box. Change the available values page of the parameter to get values from a query.
Select the dataset that you created to populate the dropdown box.
Solution: Cascading parameters means one parameter can populate the contents of
the next parameter. Create additional datasets to populate each of the dropdown
box parameters you want. When writing these datasets you will want to make sure
that the lower level only shows values available in the higher level. The datasets
may look like this:
Select distinct EnglishCountryRegionName From DimGeography
Select distinct StateProvinceName From DimGeography
Cascading
Parameters
Where EnglishCountryRegionName = @Country
Select distinct City From DimGeography
Where EnglishCountryRegionName = @Country and StateProvinceName = @State
The country dataset passes the country selected into the state dataset. Next, the
State dataset passes into the city dataset the country and state. This way when you
get to the City parameter you only see cities that are in the selected country and
state.
Change the available values page of the parameters to get values from a query.
Select the dataset that you created to populate the dropdown box
Solution: Copy a Report you design to one of the following folders:
Create a
report
template
64 bit location: C:\Program Files (x86)\Microsoft Visual Studio
9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
32 bit location: C:\Program Files\Microsoft Visual Studio
9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
Solution: Install and configure Reporting Services add-in for SharePoint for your
version of SharePoint. Set the Deployment properties of the Report project to the
following:
Deploying
to
SharePoint
Setting up a
TargetDatasetFolder
http://yourservername/site/library/datasets
TargetDataSourceFolder
http://yourservername/site/library/datasources
TargetReportFolder
http://yourservername/site/library/reports
TargetReportPartFolder
http://yourservername/site/library/reportparts
TargetServerURL
http://yourservername/
Solution: Right-click on the textbox you wish to allow the drillthrough action and
drillthrough
report
select Text Box Properties. Select the Action page and select which type of object
you want to drillthrough to (Go to report, Go to bookmark, or Go to URL).
Creating a
Document
Map
Solution: Document Maps provide an easy way to navigate through a large report
with a table of contents for your report. To add a Document Map to a report rightclick on a row or column grouping (this assumes you have already added a grouping
to the report) and select Group Properties. On the Advanced page you will find the
Document map property where you will select the fields to add to the table of
contents.
