When developing Reporting Services reports that use Analysis Services as a data source you may find that it is difficult to dynamically set default parameter that are passed into the dataset being used.  It turns out this is a lot simpler to do then many people think.  In this post I’ll walk you through the steps of setting a default date value in a dataset’s parameter. 

Step One

When creating a dataset that uses Analysis Services you will select Query Designer and then drag over the measures and dimension attributes that are needed for the report where it says “Drag levels or measures here to add to the query” .  To add a parameter to the dataset you need to add a filter above where you just dragged over your report fields.  In my example I am filtering by the year 2008.  If I want to make this a parameter I just check Parameter box.  Now when you hit OK and then OK again to return to your report you will have a new parameter added.  When I made 2008 the Filter Expression it automatically sets that value as my default parameter value.

Step Two

What I really want to do is setup this parameter so that it dynamically changes.  In my report I want it to always show the current year by default in the parameter.  So instead of 2008 the report should display 2009 and automatically change to 2010 on January 1st.  You can do this using the SSRS expression language in the default value of the expression.  The tricky part about it is that the default value of the parameter is using a MDX value.  If you open the report parameter and select the Default Values tab you can change the value used for this parameter to use a SSRS expression so it dynamically changes to the current year no matter what the date is.  The expression used for my example was ="[Year].[Year].&["+CSTR(Year(Today))+"]".  This is using the Today function and only returning back the year using the Year function.  Lastly it converts the value into a string. 

This is a very basic example but I could use a similar expression if I had used a date hierarchy instead of just the year attribute.  The key is to find out how the MDX that is being passed into the value looks like and then you can duplicate them using the SSRS expression language.  Now every time I run the report It will have the current year populated by default in the parameter.

Today I gave a two part session at SQL Saturday in Orlando.  When originally planning this years event Andy Warren said many people who have attended previous events were interested in sessions that were split into two parts.  So I decided to submit an Introduction to SSIS that was split into a part 1 and 2. 

I was a little unsure on how to split an Intro to SSIS session into two parts but it turned out I actually preferred it this way.  I decided to have the first session cover general understanding of the development tool, Control Flow, and Event Handlers.  The second session was on the Data Flow, Variables, and deployment.  I think this worked out well and it made me not feel as rushed on the topics. 

Andy knows how to run these events and I think it’s interesting to see how the event has transformed over the years now.  This event as a whole was stacked with talented speakers.  I think this may have been one of the most talented group of speakers I’ve seen to date at a SQL Saturday.  Not only does SQL Saturday have seasoned speakers but it is an opportunity for new and local speakers to share with others the knowledge they’ve gained from their experience.  I think a lot of people forget about how much great information they’re getting out of this free event.  Make sure you make it to the next event near you.  www.sqlsaturday.com

It’s that time again!  SQL Saturday has returned to Orlando with an all star group of speakers http://www.sqlsaturday.com/schedule.aspx.  The event will be October 17th and you can find all the important information at www.sqlsaturday.com.  If you’ve never attended one of these free events make sure you make this one.  It’s well worth attending even with a couple hours of a drive like me!  The topic I will be speaking on is introduction to SSIS in a two part session.  Hope to see everyone there!

SQL Server 2008 has a new Change Data Capture feature that allows you to track Inserts, Updates, and Deletes on a table. A lot of the features I’ve used in the past to detect changes are generally very expensive operations so I’m always looking for alternatives.  There are several caveats you need to know about before attempting to use CDC.  View this blog with screenshots at my regular blog http://blogs.pragmaticworks.com/devin_knight/.

1.  You must be using either Enterprise, Developer, or Evaluation editions of SQL Server 2008

2.  You must have a sysadmin fixed server role

3.  You must have a db_owner fixed database role

4.  CDC can only be applied to user databases

5.  SQL Server Agent must be running

All of the stored procedures used for CDC can be found on msdn http://msdn.microsoft.com/en-us/library/bb500244.aspx.

Step One

CDC must be enabled on each database you decide to check for changes.  To enable CDC on a database using the following script:

Use AdventureWorks2008
exec sys.sp_cdc_enable_db
select name, is_cdc_enabled from sys.databases

The select statement shows which databases have Change Data Capture enabled.

Step Two

Create a table to test CDC:

Create Table dbo.CDC_Test
(ID int,
Description varchar (50))

Step Three

CDC must also be enable on each individual table:

exec sys.sp_cdc_enable_table 
    @source_schema = 'dbo', 
    @source_name = 'CDC_Test',
    @role_name = 'CDCRole'
select name, is_tracked_by_cdc from sys.tables

Here I’ve created a new role called “CDCrole” that didn’t previously exist.  This stored procedure has many parameters but I’ve only used the required ones here.  View all the other available parameters here http://msdn.microsoft.com/en-us/library/bb522475.aspx.  The select statement displays all the tables that have Change Data Capture enabled.  You may have also noticed that two SQL Agent Jobs were created (cdc.AdventureWorks2008.capture – watches for changes in transaction logs, cdc.AdventureWorks2008.cleanup – automates cleanup). 

Step Four

Insert a new record into the test table:

INSERT INTO AdventureWorks2008.dbo.CDC_Test
           (ID
           ,Description)
    Values(1, 'CDC Test' )

Step Five

Update the record in the table:

UPDATE AdventureWorks2008.dbo.CDC_Test
   SET ID = 3
      ,Description = 'NewTest'
 WHERE ID = 1

Step Six

Use the function created by CDC to return which columns are Inserted, Deleted, or Updated

DECLARE @from_lsn binary(10), @to_lsn binary(10)
SET @from_lsn =
   sys.fn_cdc_get_min_lsn('dbo_CDC_Test')
SET @to_lsn   = sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_CDC_Test
  (@from_lsn, @to_lsn, N'all')

How is this useful?  The _$operation column is used to indicate the change (1 = Delete, 2 = Insert, 3 = Update with row filter option set to “all update old”, 4 = More typical Update).  So my example shows one insert and one update.

Step Seven

Use the following procedure to view all tables with CDC enabled.

exec sys.sp_cdc_help_change_data_capture

Step Eight

Disable Change Data Capture on table and database.  To disable just the individual table run the following:

exec sys.sp_cdc_disable_table 
  @source_schema = 'dbo', 
  @source_name = 'CDC_Test',
  @capture_instance='dbo_CDC_Test'

Run the following to disable CDC for the database:

exec sys.sp_cdc_disable_db

You don’t need to do both if you want to disable the table and database just run the disable database stored procedure.  You will also notice the SQL Agent Jobs are removed when the last procedure is ran.