Reporting Services has many expressions that can manipulate date fields.  These expressions can be used just about anywhere in SSRS including object properties, displayed data, and parameters. 

Recently I’ve worked on some projects that required date parameters to do things that you would typically see in an Analysis Services cube.  My client needed to see data brought back Week to Date, Month to date,  Year to date, and on with a rolling week.  My first suggestion of course was to create a cube to not only to do these calculations but get many other benefits.  Unfortunately, they did not take my advice so I had to use the SSRS expression language to return the results they were looking for.

I thought this made for a great blog topic so I could refer back to these whenever I need them.  All the reports had at least two parameters for start date and end date.  I needed to change the default values so they would default to show the above date range (YTD, MTD…etc). 

End date would always have the current date or Today() in the expression language.  If you want time included then use Now().  The default value of the start date parameter will use the DateAdd function to return back a date that is a set number of units back.  The DateAdd function is constructed like this:

DateAdd(interval, number, date)

Expression I use to change my parameter default values.  These could be written many different ways and actually use VB in the customer code are but I wrote them this way if you’re not a VB expert.

Month over month

=DateAdd("m",-1,Today)

MTD

=DateAdd("d",-(Day(today)-1), Today)

Week over week

=DateAdd("ww",-1, Today)

or

=DateAdd("d",-7, Today)

WTD

=DateAdd("d",-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today)

Year over Year

=DateAdd("yyyy",-1, Today)

YTD

=DateAdd("d",-DatePart(DateInterval.DayOfYear,Today,0,0)+1,Today)

Notice in the YTD and WTD expression I’m calling a DateInterval function that is actually a Visual Basic function but does not require you do add anything to the custom code section of your report.

The SSIS expression language is a powerful tool for a developer that gives you one method of making a package dynamic.  When writing an expression there are a few things to be careful of that could potentially cause a stumbling block while writing code. 

For example, a common reason you may write expressions is to populate a changing file name.  Say you want to have a file loaded but the name of the file should change each day the SSIS package is ran to have the current date appended to the end of the file name.  This could be accomplished several ways but one method may use an expression similar to this:

"C:\\Test\\File"+ (DT_WSTR, 10)(DT_DBDATE) GETDATE()+".txt"

When an expression like this is evaluated then the results may look like this (depending on the date):

C:\Test\File2010-01-27.txt

In this example the hardcoded string values are identified by the text that is between the quotations marks.  One unusual thing about this expression is the double backslashes, which in the evaluation represents a single backslash.  This is known as an escape sequence meaning that a single backslash really represents something else.  So the purpose of this post is to warn you about these escape sequences in string literals that you will encounter. 

The example shown here is a very common example that many people know about but there are several other escape sequences that you should know about as well and can be found on msdn.  Here’s a copy of the chart from that link showing all the escape sequences.

So if I needed to have quotation marks in my expression then I would use the \” to return back quotation marks in my hardcoded part of the expression:

"Devin Said,\"What's the deal with these escape sequences\""

This expression would evaluate to look like this:

Devin Said,"What's the deal with these escape sequences"

Several months ago I blogged about why Checkpoints are a great feature in SSIS to use.  It seems with every feature there are potential problems you can run into and it is no different with Checkpoints.  The major problem or what some people would call a feature is when Control Flow tasks are run in parallel checkpoints act a little erratically.  The obvious solution for this is to not run tasks in parallel and instead have all your tasks kick off sequentially. 

Here’s an example of the problem:

First Run

I run my package with a task I know will fail.  The package has Checkpoints configured so when the package fails it creates an xml file with the fail point.  I have these tasks running in parallel so my real goal is to have the set of tasks on the right continue even though one on the left failed but, Checkpoints prevent it from continuing. 

Second Run

I have not corrected the problem in my #2 task yet but when I rerun the package I get a little bit further on the right side to task B.  Every time I run the package now it will appear like this until I correct the error in the #2 task.  This is strange to me because from the first run to the second run I moved from task A to task B on the right, so why on a third run do I not now move to task C?  Until I finally fix task #2 I’m stuck at task B on the right.

Third Run – With corrected #2 task

Now the #2 task has been corrected and everything completes. 

So the lesson here is if you want to use Checkpoints in SSIS you should run the tasks sequentially instead of parallel because your package will not run like you would anticipate.

If you weren't able to attend tuesday for SQL Lunch you can watch the recording now mosted on the website.  This session was on Data Driven Subscription.  We had a great group of people with lots of good questions.  Thanks to Patrick LeBlanc    , founder of SQL Lunch, for letting me present.  Patrick recently wrote a blog post that answers a question that some people had about subscription names so make sure to read that. 

Make sure to watch the recorded presentation and add to your calandar some of the all-star speakers scheduled for the coming months.

Recently while at a client I was discussing the differences between Report Builder 1.0 and Report Builder 2.0.  The discussion was really around will the end user actually use Report Builder and if they will should they have access to Report Builder 1.0, 2.0 or both.  I explained that Report Builder 2.0 had a lot more features than 1.0 and that if the end user is tech savy enough they should go that route.  So it was decided to go with Report Builder 2.0 but I explained that the Report Manager automatically launches Report Builder 1.0 when you click the icon for Report Builder.

To make this less confusing we needed a way to launch Report Builder 1.0 when clicking the icon.  After a little research I found a very simple way to make the switch called ClickOnce.  There are a lot of blogs that already exist about this feature but anytime I learn something new I like to write about it!  This feature was enabled in SP1 and allows to very easily switch which application is launched.  Here are the steps to needed to make the change:

1. Start the Report Manager
2. On the Home screen click Site Settings in the top right
3. In the General tab, change the Custom Report Builder launch URL to /ReportBuilder/ReportBuilder_2_0_0_0.application
4. Click Apply

Here are the steps to do this using a SharePoint portal (I have not been able to test this one yet).

1. On the Start menu, point to Administrative Tools and then click SharePoint 3.0 Central Administration
2. On the Central Administration page, click the Application Management tab.
3. Type /_vti_bin/ReportBuilder/ReportBuilder_2_0_0_0.application in the Custom Report Builder URL property
4. Click OK

You can revert back to Report Builder 1.0 at any time.  Just keep in mind the requirements are to install Report Builder 2.0 and SQL Server SP 1.

Over the last few weeks I have focused most of my blog energy into writing a couple articles.  So I wrote about something I was really interested in learning more about myself.  The result was three articles on performance tuning Analysis Services.  These articles not only explain the benefit to of doing each but also give step by step instructions so you can build it yourself in your environment.  You can find these articles at www.bidn.com (Business Intelligence Developer Network).

Analysis Services Partitions

Analysis Services Aggregations

Analysis Services Usage-Based Optimization (UBO)

For a little more than a year now I've been a part of the PASS BI SIG and have tried to contribute whenever possible.  The SIG is split into several focus groups that meet on all BI topics.  I help in leading the SSIS group.  Today I actually had an opportunity to present to the Analysis Services focus group on the topics of Analysis Services Partitions and Aggregations which you can watch free here http://www.bidn.com/blogs/DevinKnight/ssis/306/anaylsis-services-partitions-and-aggregations-pass-session.

If you think you would like to present on any BI topic contact me and we'll get you lined up as a speaker (dknight@pragmaticworks.com).  It's a great opportunity to share your expertise with the community.

In part one of this post I showed how you can use a report to run SQL Server Agent Jobs by using a report.  In that post I only showed you how to run a single job but hardcoding in the job name.  Someone recently asked if it was possible to have a drop-down list of jobs to choose from instead of have the hardcoded job name so I thought it made for a great opportunity to write a part 2.   

Step One

Create a Data Source that points to MSDB on the server that the SQL Agent job that you want to run is located.

Step Two

Create a DataSet that runs the system stored procedure sp_start_job with a parameter called @jobname

Step Three

Create a second DataSet that runs a select statement against the system table sysjobs.

SELECT name
FROM sysjobs

Step Four

Right-click on the @jobname parameter and change the available values to Get values from a query.  The Dataset should be the one created in Step Three and in this case the Value field and Label field are both the field called name.

Step Five

Add some text!  Let the user know what’s going on.  Drag a textbox over and add the appropriate text.  I wrote an expression using the @jobname parameter so it read which job was started:

="Running this report will start the SQL Agent job called "+Parameters!jobname.Value+"."

Preview the report.  You should have a parameter drop-down list of all your jobs that you can select and run.

Select the job you would like to start then hit View Report.  I have several Reporting Services Subscriptions you can see by the GUID names listed.  When the report runs you should see your textbox appear.

This is an answer to a forum question on www.bidn.com

Running SSIS on a 64-bit machine has several caveat that developers need to be aware of.  Tasks like the ActiveX task and Execute DTS 2000 Package Task only work either on a 32-bit machine or by running in 32-bit mode on a 64-bit machine.  This is also the case for many data providers like Excel, Access, and many ODBC drivers.  If you are working on a 64-bit machine don't worry there is a work around to keep yourself productive while still using 32-bit tasks and drivers.

Executing In BIDS

• Right-click on the project file and select properties in your solution
• Under the Debugging tab you will see a property called Run64BitRuntime which you need to change to true

Executing On Server

SQL Server 2008

• Either in a SQL Agent job or by just executing the package by itself there is a tab called Execution options. 
• Select Use 32 bit runtime and the package will then run in 32-bit mode

SQL Server 2005

• Use the 32-bit version of dtexec and execute your package from the command line which can be found in the Program Files (x86) folder.
• Follow link to execute package from command line http://msdn.microsoft.com/en-us/library/ms162810.aspx.

Often when designing a report you may find a need to concatenate values in an expression.  For example, you want a column with full names but your data source has names stored separately for first name and last name.  Easy enough you can write an expression like this:

=Fields!FirstName.Value + " " + Fields!LastName.Value

Problem solved right?  Well let's complicate things a little.  Let's say you not only want the full names but all addresses as well but you don't want it on the same line.  You need a carriage return so it looks like a normal address:

Devin Knight

123 Main Street

Jacksonville, FL 12345

As many times as I've used carriage return in reporting services I always seem to forget the syntax for it and have to go search for it.  That's usually the purpose for me writing blogs! 

Using the VbCrLf function I can get my line feed.  It's a call the VB for carriage return and line feed.  Here's the syntax to get my name and address like I need:

=Fields!FirstName.Value + " " + Fields!LastName.Value + VbCrLf +

Fields!Address.Value + VbCrLf +

Fields!City.Value + ", " + Fields!StateProvinceName.Value + " " + Fields!PostalCode.Value

This is part four in the series of blog posts that will help in building a library of calculations you can use as a reference in any analysis services cube you build.

You can find the previous posts to this blog series below:

Part 1 – ParallelPeriod

Part 2 – PrevMember

Part 3 – PrevMember minus CurrentMember

All of these blog posts are formatted to give you the business problem, a general solution and then the calculation needed to finish the job. 

Problem

You need to show company sales year to date, quarter to date, and month to date

Solution

Use the PeriodsToDate function to return the sales YTD, QTD and MTD.  There are also MDX functions called YTD, QTD, MTD and WTD but I have had more success using the PeriodsToDate function

Calculations

The sales amount returned YTD then aggregated

Aggregate(

            PeriodsToDate(

                [Date].[Date Hierarchy].[Year],

                [Date].[Date Hierarchy].CurrentMember

                ),

            ([Measures].[Sales Amount])

            )

---------------------------------------------------------------

The sales amount returned QTD then aggregated

Aggregate(

            PeriodsToDate(

                [Date].[Date Hierarchy].[Quarter],

                [Date].[Date Hierarchy].CurrentMember

                ),

            ([Measures].[Sales Amount])

            )

---------------------------------------------------------------

The sales amount returned MTD then aggregated

Aggregate(

            PeriodsToDate(

                [Date].[Date Hierarchy].[Month],

                [Date].[Date Hierarchy].CurrentMember

                ),

            ([Measures].[Sales Amount])

            ) 

This is part three in a series of blog posts that will help you build an arsenal of MDX calculations that you can have ready at the drop of a dime.  The first two posts of this blog series use the ParrallelPeriod and PrevMember functions to return data at different levels.

All of these blog posts are formatted to give you the business problem, a general solution and then the calculation needed to finish the job.  The problem this time will use part of the solution we used from the second blog post.

Problem

You need to show the difference in sales of a date and that date's previous member.  (Ex.  Show the sales difference between June and July.  Could likely be a negative number if there are fewer sales in July.)

Solution

You can return the sales for the CurrentMember and subtract it from the value of previous member using PrevMember.

Calculations

The sales for a year minus the sales of the previous year

([Date].[Year].CurrentMember,[Measures].[Sales Amount])-
([Date].[Year].PrevMember,[Measures].[Sales Amount])

---------------------------------------------------------------

The sales for a quarter minus the sales of the previous quarter

([Date].[Quarter].CurrentMember,[Measures].[Sales Amount])-
([Date].[Quarter].PrevMember,[Measures].[Sales Amount])

---------------------------------------------------------------

The sales for a month minus the sales of the previous month

([Date].[Month].CurrentMember,[Measures].[Sales Amount])-
([Date].[Month].PrevMember,[Measures].[Sales Amount])

This post is a part of a series of blog posts I am writing to give you a Batman-like Utility Belt of MDX calculations.  With this Utility Belt available you can have a template of commonly needed calculations you can use for just about any Analysis Services project. 

In part one of this series I showed how you could use the function ParallelPeriod to return sales from the previous year at all levels (Year, Quarter, Month).

In this series I present the business problem, a general solution, and the calculation to solve that problem.

Problem

To show the sales for the previous date member.  (Ex.  Show the sales for the last year, last quarter, or last month)

Solution

Using a function called PrevMember you can return the value of previous member.

Calculations

The sales for the previous year

([Date].[Year].PrevMember,[Measures].[Sales Amount])

---------------------------------------------------------------

The sales for the previous quarter

([Date].[Quarter].PrevMember,[Measures].[Sales Amount])

---------------------------------------------------------------

The sales for the previous month

([Date].[Month].PrevMember,[Measures].[Sales Amount]) 

Developing MDX calculations is one of the most confusing and time consuming pieces of building an Analysis Services cube.  That is why I have accumulated what I like to call a Utility Belt of calculation that I can pull out at any moment to solve a problem.  I consider this a Utility Belt because on just about every Analysis Services project that I have worked on these calculations have come in handy and I hope they will be helpful for you.

When building calculations you may find the templates that are available in Business Intelligence Development Studio (BIDS) as a helpful starting point.  Unfortunately, I have found even using these templates can make calculations more confusing than they really need to be.

In this blog series I will give you the business problem, solution, and then calculation that satisfies the business problem. 

Problem

You need to return sales for one year prior (Ex. one year ago, four quarters ago, twelve months ago).

Solution

Use the ParrallelPeriod time series function to look back a set number of values back in a date hierarchy and return the sales at that point.

Calculations

Looking back one year at the Year level

(ParallelPeriod([Date].[Date Hierarchy].[Year],
1,
[Date].[Date Hierarchy].CurrentMember),
[Measures].[Sales Amount])

---------------------------------------------------------------

Looking back four quarters at the Quarter level

(ParallelPeriod([Invoice Date].[Date Hierarchy].[Quarter],
4,
[Invoice Date].[Date Hierarchy].CurrentMember),
[Measures].[Sales Amount])

---------------------------------------------------------------

Looking back twelve months at the Month level

(ParallelPeriod([Invoice Date].[Date Hierarchy].[Month],
12,
[Invoice Date].[Date Hierarchy].CurrentMember),
[Measures].[Sales Amount])

You can find me regularly blogging at http://www.bidn.com/blogs/DevinKnight

A common question I've been asked a lot lately is how to replace the icon in the Report Manager to my company's logo. 

It is actually fairly simple to do by following these steps: 

1.  On the server reporting services is installed on open the folder C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportManager\images

2.  Rename the file 48folderopen.jpg to anything else (Ex. 48folderopenOLD.jpg)

3.  Resize your logo to be 48 x 48 pixels jpg file
4.  Rename it 48folderopen.jpg
5.  Copy your logo to the folder C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportManager\images

Open the Report Manager again to see the change.

Read my blog at my regular blog site http://www.bidevelopernetwork.com/blogs/DevinKnight