Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Archive for April, 2011

Free SQL Server Community May 2011 Training

Posted by denglishbi on April 29, 2011

[tweetmeme source=”denglishbi” only_single=”false”]

Head over to Pragmatic Works site and take a look at all of the training that is available in May – free training on Tuesdays and Thursdays at 11 a.m. EST.  image

  • May 3, Chris Webb – Fun with Scoped Assignments in MDX 
  • May 5, Tom Lannen – Intro to Data Mining: Your Guide to Fantasy Baseball Season 
  • May 10, Brian Knight – Introduction to PowerPivot 
  • May 12, Tom Lannen – Data Mining with Excel: Fantasy Baseball Made Easier 
  • May 17, Veronique Palmer – New Web Parts in Foundation 
  • May 19, Mark Stacey – Using PerformancePoint and Reporting Services to create an interactive dashboard 
  • May 24, Shawn Harrison – Introduction to Report Builder 3.0 
  • May 26, Mike Davis – Using Parameters in SQL Server Reporting Services

If you miss a session they record them and you can check them out later on-demand in the webinar resource area.

They already have the line up going for June as well, so if you want to plan ahead you are all set.  Just remember the disclaimer that the speakers are subject to change, so always check back to see what is going on.

So what are you waiting for, get registered for one of these spectacular webinars today – Webinar Registration.

Don’t forget, you can stay up-to-date on the training events through the PWFreeTraining twitter account as well.


Posted in SQL Server, Training | Tagged: | Leave a Comment »

Minneapolis Tableau User Group May 17, 2011

Posted by denglishbi on April 25, 2011

[tweetmeme source=”denglishbi” only_single=”false”]

The next Minneapolis Tableau user group meeting is coming up hosted by Alan Wernke.  If you are interested in Data Visualizations then this will be a user group you will want to check out. 


Date: May 17th – 3:00pm CST

Duration: 180 minutes


US Internet

12450 Wayzata Boulevard

Minnetonka, MN 55305

United States


  • 3:00 pm – Welcome / Introductions
  • 3:15 pm – Networking with MN TUG Members
  • 3:30 pm – Navigating Tableau Maps: A Compass for Beginners Vicki Siemers, Sr. Marketing Research Analyst at RBC Wealth Management
  • 4:15 pm – Tableau 6.0 features: Powerful Parameter and Table Calculation Alan Shilepsky, Wernke Consulting Services
  • 4:30 pm – Tour of US Internet Datacenter with Mark Elert
  • 4:45 pm – Break
  • 5:00 pm – Tableau in the Enterprise: Panel discussion Shawn Spott, Mike Keys, Alan Shilepsky, Rob Dewey, Eric Sanders Tableau. Please send your questions to Alan Wernke in advance of the meeting
  • 5:45 pm – Future Events

If you are interested in visualizing your data and dashboard solutions then you will want to check out this event – Register Here.

Posted in Business Intelligence, Training | Tagged: , | Leave a Comment »

PASSMN April 19, 2011 Meeting

Posted by denglishbi on April 14, 2011

The next Minnesota SQL Server User Group (PASSMN) meeting is coming up next week.  The topic will be about SQL Server DBA careers including information about the Microsoft Certification Master program for SQL Server 2008.  There will also be a bonus session after the meeting by Jason Strateabout becoming and being a SQL Server MVP.

“SQL DBA careers, including The Path to Microsoft Certified Master: SQL Server 2008


  • 2:45-3:00 : Registration / hospitality / networking
  • 3:00-3:15 : Announcements etc.
  • 3:15-4:25 : Joe Sack: The Path to Microsoft Certified Master: SQL Server 2008
  • 4:25-4:45 : Jacquie Garberg: Get the career you want…keep the one you have!
  • 4:45-5:00 : Closing comments/prize giveaways
  • 5:00+ : On Becoming and Being a SQL MVP – Jason Strate


The Path to Microsoft Certified Master: SQL Server 2008 (by Joe Sack, Microsoft) – Joe will be giving an in-depth talk on the SQL MCM Certification program. He’ll discuss how the SQL MCM Certification program format use to look and how it looks today. He’ll also describe the benefits of a SQL MCM Certification and how to prepare for it.

Get the career you want…keep the one you have! (by Jacquie Garberg, Benchmark Learning) – Do you know where you are heading with your career? Are you on the journey to somewhere or just getting by? Does your organization see the value you bring to the table? What do companies look for in a SQL DBA professional? Jacquie will focus on career topics, with an interactive discussion. She will also provide the latest tips and tricks to help you “stay on track” so that your career doesn’t get derailed.

On Becoming and Being a SQL MVP (by Jason Strate, Digineer) – For those willing to stay a little later than our usual closing time, Jason Strate will tell us what the MVP award is, what’s involved in becoming an MVP, and what it the benefits are for the MVP program.

Posted in Training | Tagged: , | Leave a Comment »

Using SharePoint List data for Reporting Services (SSRS) parameter source

Posted by denglishbi on April 13, 2011

[tweetmeme source=”denglishbi” only_single=”false”]

UPDATE (8/11/2012): updated the link for the sample download files to point to my file on SkyDrive.

UPDATE (6/18/2011): added a PDF download option of the blog post.

UPDATE (6/15/2011): added file download which includes the Excel source file used to create SharePoint list and also two RDL files, the one in the demo and additional one that was created with multi value modification for the parameter.

When creating reports with Reporting Services (SSRS) you might find yourself having to reference data that is stored in SharePoint lists.  Another task that you might need to do is to leverage that same data that is stored in the list to filter the results that are displayed to the user in the report.  Let’s take a look at a scenario for this type of a report request.  In order to get setup for this scenario we are going to do a little prep work first though to get the data created for the example.  We will do this right after we go over the scenario.


A request comes in to create a report that provides a list of the stores and to provide a way for the users to filter the stores that they see on the report based on the location of the store; in this case they would like to filter the records by country.  The request states that the information needed for the report is currently being stored and maintained in a SharePoint list.

Just a heads up, in this example we will be using SharePoint 2010 along with Reporting Services (SSRS) 2008 R2.  SSRS 2008 R2 includes a new SharePoint List Connection Type.  You will see screenshots of this down below in the Solution part.

Prep Work

Okay, first we are going to generate the list in SharePoint that is going to be the source for our report where the store information is entered and maintained so that we can do this example.  We are going to do the following task:

  • Create custom Store Information SharePoint List by importing an Excel file as a source for the structure and initial data load

The Excel file that we are going to use for this list was originally created from the AdventureWorks2008R2 database by exporting the view [Sales].[vStoreWithAddresses].  You can download the AdventureWorks sample databases from CodePlex here.

image image
From the SharePoint 2010 site select Site Actions->View All Site Content From the All Site Content page click on the Create option at the top of the page to display the popup window for creating new content. Under the All Categories section on the left-hand side select Blank & Custom, then select Import Spreadsheet, and finally click the Create button.
image image
Enter a Name for the new list to maintain the store information, click Browse to locate the Excel file that contains the store information, click Open, and then click Import A Excel window will open up so that you can specify what you want to import.  Select the range of cells and then click Import.
image image
The range of cells will be converted to a Table and you will see a progress meter on the bottom of the file stating ‘Adding Records’ Once the import process is complete you will have a new list in your site with all of the store addresses from the Adventure Works database.


We will now create the report that will provide a list of stores and also provide a way to filter the records displayed to the end-user.  All of this data will be generated from the SharePoint list that we just setup in the Prep Work section above.

image image
First step is to simply create a Report Server Project that will contain our report and to add a new Report item called Store List. Next step is to add a new Data Source to the report with the Type of Microsoft SharePoint List with the URL address of the site (in my case it is http://mybi10/) and then click OK.
image image
Now add a new Dataset to the Data Source you just created, provide a name, click Query Designer, select the desired fields from the SharePoint list you created in the Prep Work, and then click OK twice to close out the Query Designer and the new Dataset dialog boxes. Repeat the previous step, but this time only select the CountryRegionName field from the SharePoint List.  This is going to be the source for the Parameter we are going to setup next.
image image
Add a new Parameter to the report and on the General page make sure you click on the checkbox for ‘Allow multiple values’ and also click the radio button for Hidden. On the Available Values page click the radio button for the ‘Get values from a query’, select the Dataset you created that contains just the CountryRegionName field, and then select the CountryRegionName for the Value and Label field drop-downs.
image image
On the Default Values repeat the process that you just did for the Available Values with the exception that there is no Label field option.  Once you have done this click OK to add the new Parameter. Now if we go back and run the query for the CountryNames dataset we created we will see that this is not a distinct list of values of Countries.  Since this is coming from a SharePoint list we need to do a bit of extra work to generate this distinct list.Here is where the magic comes in and a little help comes in from the community and the Microsoft forums – “How to get distinct values of sharepoint column using SSRS”.
image image
In the Report Properties we need to go to the Code page and add some code that will be referenced to create an ArrayList of values that will contain our distinct list of values, that will be sorted, and will also contain an “All” option as well.  In the code above which is also provided below in the posting to reference there is some additional code to handle ampersands (&) in names as well, but in this case we do not have those, and I use a Vertical Par (Pipe) for a delimiter – need to be careful if your values had commas, so I go with the bar instead.  Once you have the code in place click Ok to exit the Report Properties window. Now we need to setup an additional Parameter.  Add a new Parameter and on the General page enter a name and prompt text.
image image
On the Available Values page click the radio button for ‘Specify values’, click on the Add button, click on the fx button to enter an expression for the value and use the following=Split(Code.RemoveDups(JOIN(Parameters!CountryNames.Value, “|”)), “|”)Now click the Ok and go to the next step. Similar to the previous step, on the Default Values page click on the ‘Specify values’ radio button, click Add, click on the fx button to enter an expression for the value and use the following=Split(Code.RemoveDups(JOIN(Parameters!CountryNames.Value, “|”)), “|”)(0)This will return the first value in the ArrayList.  Now click OK twice to close the expression window and to add the new Parameter to the report.
image image
Next step is to wire the Parameter into the main dataset for the report.  In order to do that we need to access the properties of the dataset and add a filter. Now we need to setup the Expression and we will use a formula for this so that we can handle the use of the “All” value.=IIF(Parameters!CountryName.Value=”All”, “All”, Fields!CountryRegionName.Value)
image image
Next move down to the Value and enter another expression in order to account for the modification we did in the Code to handle ampersand ( & ) logic.=Replace(Parameters!CountryName.Value,”&”,”&”) Now that part is done, just need to create the desired report layout.  In this case I use a List data region, but you could use a Table.  I also included some additional logic to alter the background color (green bar effect).Don’t forget to add a sort to the data region as well.
image image
Run the report and test out the filter.  Here is the report for Canada.One thing to note is that we would want to update each field that might contain an ampersand with the REPLACE logic. After that logic is added into the expression for the field we are in business. Another option is to modify the placeholder for the data and enable the option for HTML support.You could simply create another function in the Code to handle the ampersand logic for reusability as well.Nice job;)

Code Snippet used to remove duplicates from the dataset to present to the second Parameter defined in the report.  I also added in the Sort after the ArrayList is fully populated along with the additional All value added to the beginning of the pipe delimited string.

Public Shared Function RemoveDups(ByVal items As String) As String
Dim noDups As New System.Collections.ArrayList()

Dim SpStr as String()
SpStr = Split(items ,"|")

For i As Integer=0 To Ubound(Spstr)
If Not noDups.Contains(SpStr(i).Replace("&","&").Trim()) Then
End If

Dim uniqueItems As String() = New String(noDups.Count-1){}

Dim strRetValue AS String = String.Join("|", uniqueItems)
strRetValue = "All|" + strRetValue
Return strRetValue
End Function


The challenge with this solution was to get the parameter setup the way we wanted as a distinct list of values.  Since there can be more than one store in each country the values were duplicated.  Luckily we can handle this with some code and an additional hidden parameter.  The other thing is dealing with SharePoint data and handling additional HTML markup.

Pretty fun and interesting huh?  Little challenges like this are always fun to work with and it is great once you figure out the workaround to get this all to display the way you want.


File Downloads:


Posted in Reporting Services | Tagged: , | 32 Comments »

Video: Leveraging PowerPivot Demonstration

Posted by denglishbi on April 4, 2011

[tweetmeme source=”denglishbi” only_single=”false”]

I finally got around to recording the presentation.  I only did the demo to create the screenshot application I included in the presentation, I didn’t include the portions where I demo exploring the xlsx file by renaming it to zip file extension or browse the VertiPaq folders that get created in your %temp% directory in Windows (watch out for these because they can take up a lot of space over time – I currently have 16 VertiPaq folders consuming 1.31GB of space).image

For the follow up on this presentation you can check out my previous posting – MN Microsoft BI User Group 2011 Q1 Follow up.  You can download the PDF presentation material by clicking on the PowerPoint screenshot on the right-hand side which I have stored on my SkyDrive account.

This video file is approximately 27 minutes long.  I didn’t realize my demonstration was that long, next time I will have to time this out so I can make sure I have plenty of time to show the complete demo and have time to resolve any technical difficulties.  The file is approximately 43MB if you download it and I did make this in HD mp4 quality, so it should be good to watch.  This was my first time using Camtasia to create a screencast, so hopefully everything worked out okay (really sweet product).

Leveraging PowerPivot Demonstration from Dan English on Vimeo.

Hope you enjoy the video, please feel free to post comments and questions.  Later.

Update (4/5/2011):  I have the PowerPoint presentation on SlideShare if you don’t want to download it and just want to view the slides.

Update (4/6/2011):   In regards to the CSV file I simply used the Export wizard in SSMS to create the file.  In order to do that I create the following view in the ContosoRetailDW database to reference:

CREATE VIEW [dbo].[vw_SalesBudgetData]


SELECT DateKey, ChannelKey, StoreKey, ProductKey, SalesQuantityQuota, SalesAmountQuota,

FROM dbo.FactSalesQuota WHERE (ScenarioKey = 2)

When I exported the file I also used the Vertical Bar for the delimiter and included the column headers.

Posted in PowerPivot | Tagged: , , , | Leave a Comment »