Dan English's BI Blog

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

Posts Tagged ‘ssrs’

Power View meet Microsoft Excel 2013

Posted by denglishbi on July 19, 2012

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

This week Microsoft released the Public Review of Office 2013 (aka Office 15).  You can download and check this out now for yourself and read some reviews of the new changes here:

One of the items that I have been patiently waiting to talk about and demonstrate is the new Power View and not only the new enhancements to it, but that it also is available in Excel 2013!  With Excel 2013 the PowerPivot and Power View COM add-ins are provided with the installation, you simply need to enable them in the Options->Add-Ins section.

Before I dive into the new Power View let me show a quick screenshot of one of the Power View reports from our Visualizing Data with Microsoft Power View book.

image

A couple of things I want you to take a look at primarily is the field list.  Also you will be able to compare this look to the enhanced one that I will now show off in Excel 2013.  To create my Excel 2013 I decided I would go against a complete Excel solution, so I created a complete Pan-Geo Hospitality & Travel PowerPivot Model.  I did this first in Excel 2010 and then upgraded this in Excel 2013 by accessing the Model in the PowerPivot Ribbon in Excel 2013 (you will notice that in this screenshot you will see two PowerPivot tabs and that is because I have both versions of Office running along with the add-ins (PowerPivot for Excel from SQL 2012 and now the one for Microsoft Office PowerPivot for Excel 2013).

image

When I opened up my Excel 2010 file with the PowerPivot model in Excel 2013 (which I also enabled the PowerPivot add-in) I then went to the 2nd PowerPivot tab in the Ribbon and clicked the Manage button in the Data Model section.

image

Could really use a line break in this warning message box:)

image

So just to reiterate the situation and give you an option to cancel.

image

Time to reboot:)

WARNING: I did notice one issue with the upgrade, the KPI that I had defined in my Excel 2010 PowerPivot model did not convert over, so I had to create the measure from scratch and setup the KPI again

Once I had the model converted over I used the new option in the Insert tab of the Ribbon for Power View (right in the middle in the Reports section)!

image

If you click on this and you do not have the add-in enabled you will be prompted and you can enable it then.

image

This creates a new worksheet in the workbook and then you can start building out your report, just like you can do with the current version of Power View, but this time you are doing it in Excel and not in your web browser as part of the Reporting Services 2012 integration with SharePoint 2010.

Here is what I ended up with recreating the Hotel Performance Dashboard report displayed above (notice if you can see a few extra items I have added and any other differences):

image

Did you see anything new on the report?  No, not the fact that I changed the Play Axis in the scatter chart or the data labels being added in the bar chart.  Look closer….okay, so you did see it, there are images included in the report and they are not part of the model definition!

The first one that I will point out is in the top left hand portion of the report and the new option I used here is the Picture option in the Insert section.

image

You can include Image files now so you can include the company logo as a report header (jpg, jpeg, and png files).

The other image you see is in the background of the report and for this I used the Set Image option in the Background Image section.

image

Here you can select the same type of image files as the picture and then you can specify the position option as Fit, Stretch, Tile, or Center. Along with that you can specify the transparency level and here I have it set to 90%.

Pretty neat, right?

Well you can do more formatting.  If you recall in the existing Power View we have Styles – Using Styles in Microsoft Power View.  In the new version here we can really tweak the report setup using the Themes section.

image

I can change the Font from Segoe UI to Times New Roman. I can select a different Theme, adjust the Font Size, and alter the Background (these changes are all at the report level, not item).  The result (not saying this is ideal) ends up looking like the following:

image

Another interesting item you can do here in Power View is in the Data section of the Ribbon.  You can actually review and modify relationships.

image

Pretty slick.

Let’s quick go back to the second screenshot above and take a look at the Field List.  Notice something different here?  What are those Globes and Spotlights doing in the list?  Yep, you guessed it, Power View now provides geo-coding capabilities to use with the new Map visualization, plus it also supports Key Performance Indicators (KPIs)!  In addition to the new Map visualization, the Pie chart has also been made available.

image

You can even include Pie charts in Maps (just a bit of a warning here, currently the Pie charts do not support data labels, similar to PerformancePoint Analytical Chart reports).

The mapping capability uses Bing Maps just like SSRS does for some of its mapping that was introduced in SQL Server 2008 R2.

image

Here is an example of a Map (including Pie charts, just to put it over the top):

image

You can see in the Field Well that there are new spots for Locations, Longitude, and Latitude.

If you are looking for the Card visualization it is now under the Table.

image

Now what about those KPIs, how do those look?

image

Well, looks like we still need some work here, but it is a start.  The indicators it is using are not correct.  I would like to see a more condensed view and options here to provide the detail information about the KPIs, but at least we can include them now.

Now there are more new items and capabilities that I haven’t even touched on yet, so stay tune for another post next week.

For more information about the new BI features in Office 2013 check out the following links:

Enjoy and kudos to all of the Microsoft teams that were involved to make all of this happen!

If you liked this post, check out the 2nd part here – Power View Meet Microsoft Excel 2013 Part 2

Advertisements

Posted in Business Intelligence, Reporting Services | Tagged: , , , | 13 Comments »

URL Actions with Reporting Services Power View (RTM)

Posted by denglishbi on June 13, 2012

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

Yesterday we took a quick peek inside the RDLX file – Inside the Power View RDLX File.  Today we will take a step back into SharePoint and explore what we can do when we access the Power View files through the URL.

WARNING: The items that are displayed and discussed here are not documented at all by Microsoft and could potentially change at any point down the road, so use at your own risk.power view

Many of you might be familiar with Reporting Services (SSRS) and creating report files (RDL).  With these files you have a lot of flexibility with your design and you can also leverage a lot of commands and syntax when accessing these files through the URL (in Report Manager or SharePoint) – URL Access Parameter Reference.

So what is available with v1 of Power View with the RTM release of SQL Server 2012?

Unfortunately not a whole lot.  But we do have a couple of things:

  1. ViewMode
  2. ReportSection

Hmmm….they don’t look like anything we had available with previous report files.  You are absolutely correct.  These are new options that are specific to Power View files (RDLX files).  With these files we can determine how the initial viewing mode is – currently only presentation (reading mode) or edit – and then which report (view) you want to look at (assuming you have multiple views in your report files).

So let’s take a quick peek at what these options look like:

ViewMode

With the ViewMode option you currently have two options you can use, either Presentation (Reading) or Edit

Presentation (Reading) mode is the initial view state of Power View files that you see when you access a RDLX file in SharePoint

image

Edit mode is what you see when you open up the canvas and have the options to be able to alter and change the layout of the report

image

You can now see that by switching the ViewMode value from Presentation to Edit we go from the Reading mode to the Edit mode where the canvas is available and we can modify the layout and design of the Power View report file.

ReportSection

What are ReportSections? In the RDLX file layout, a ReportSection is actual the equivalent of a view in a report file.  So if you have multiple views you will have multiple ReportSection tags within the ReportSections portion of the RDL file that is embedded in the RDLX file (see previous post for information on this – Inside the Power View RDLX File).   Take the file from our previous post and we can see the XML layout as follows:

image

The ReportSections area is setup so that when you export into PowerPoint and access the views in say the PowerPivot Gallery it knows which view of the RDLX file to display and output.

In looking at our example up above you can see that we have 4 views included in the Power View report.  In the XML displayed here we see 4 ReportSection tags in the RDL file and the naming convention is ReportSection, ReportSection2, ReportSection3, and ReportSection4.

So with the naming convention displayed here let’s go back to our URL expressions above and expand on them and include the ReportSection into the URL address to change from the first view in the Power View report file to the second:

image

By using the ReportSection command we have been able to navigate from the first view (report) in our file (ReportSection=ReportSection) to the second view (ReportSection=ReportSection2).  Pretty slick!

As you can image this will work for the third and fourth views in the report file as well.

So for now this is what we have available to us, just ViewMode and ReportSection.  It does provide you the means to be able to send an end-user to a specific view within a Power View report file (assuming you have more than one view defined).

If you want to find out more about Power View and want to learn about designing Tabular models to support Power View, don’t forget to check out our book – Visualizing Data with Microsoft Power View.

Enjoy!

Posted in Reporting Services, SharePoint | Tagged: , , | 3 Comments »

Inside the Power View RDLX File

Posted by denglishbi on June 12, 2012

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

So if you have downloaded and started playing with the new SQL Server 2012, one of the new features you can use with SharePoint 2010 and Reporting Services is Power View.  Power View is a new self-service reporting tool developed by the Reporting Services team and it currently works against Tabular BI Semantic Models (so either PowerPivot files in SharePoint or Tabular SSAS databases).power view

Now when you create a new Power View report you might realize that it doesn’t create just an RDL file anymore, they are now RDLX files.  Theses files are really zip files, so just like PowerPivot XLSX files you can simply rename these files with a ZIP extension and explore the contents.

So what is inside?  Let’s take a quick peek:

image

Here I have renamed an RDLX file called Hotel Performance Dashboard that I downloaded from SharePoint with the ZIP extension and extracted the contents.  Inside nothing too interesting yet, but I see a folder called ‘Reports’.  Let’s take a look in there…

image

Now this looks a little more interesting and what do you know, an RDL file…what is in there?  I won’t paste the whole contents here, but I will give you a little screenshot of what the contents include:

image

Now this is what we are used to seeing, XML with DataSources, DataSets, etc.  We can even see the DAX that is generated and used to retrieve our data for the report!  Really nice.

Some other things that we will see in here are ReportSections:

image

More on these in another post that will be coming up soon.

So let’s go back to that ZIP folder and see what else might have been in there tucked away…I think I saw a folder called Metadata…

image

Cool, look at that, the snapshot images of our views in our Power View report file.

Okay, so that is a quick tour of the RDLX file, up next…Power View URL commands…coming really soon!

And by the way, if you haven’t heard, the new Power View book, Visualizing Data with Microsoft Power View has been released, so check it out and let me know what you think.  Not only does it include information about Power View, but also how to build Tabular BI Semantic Models, use DAX, how to build a BI Virtual Environment, and it is loaded with ‘Learn By Doing’ exercises and videos as well!

Resources

– Visualizing Data with Microsoft Power View (book) – http://tinyurl.com/powerviewbook

– Microsoft Business Intelligence http://tinyurl.com/msftbi

– Power View Overview http://tinyurl.com/mspowerview
– Power View Samples – 6 samples to test out http://tinyurl.com/msbitryit

Posted in Reporting Services, SharePoint | Tagged: , , | 5 Comments »

Now Available: Visualizing Data with Microsoft Power View

Posted by denglishbi on June 5, 2012

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

I am very excited and proud to announce that there is a brand new book available on the market and now in print – Visualizing Data with Microsoft Power View.  When I got home after work yesterday there was a box waiting for me and the books were inside.  It is hard to believe that just over a year and a half ago that I received the email from Brian Larson asking – “Ever want to be an author?”

Just got my #powerview book http://www.mhprofessional.com/product.php?isbn=0071780823 #msbi #SharePoint #sqlserver

Here is the outline for the book:

Part I – Power View

  • Chapter 1 – Getting Started
  • Chapter 2 – The Table Visualization
  • Chapter 3 – Additional Visualizations
  • Chapter 4 – Charting
  • Chapter 5 – Bringing Your Data to Life
  • Chapter 6 – Sharing Reports

Part II – Creating a BI Semantic Model (BISM)

  • Chapter 7 – BISM: Getting Started
  • Chapter 8 – Basic BI Semantic Model Design
  • Chapter 9 – BI Semantic Model: Additional Model Features
  • Chapter 10 – Using DAX Expressions
  • Chapter 11 – Deploying Tabular BI Semantic Models

Part III – Appendixes

  • Appendix A – Installing and Configuring the Sample Data
  • Appendix B – Creating a Virtual Learning Environment

The book is loaded with Learn By Doing exercises throughout the book and included with the book is a DVD that includes the sample data, project files, Power View reports, and videos of all of the exercises.

There will be eBook versions made available as well and there is even talks of an enhanced one that would be available in the iTunes Apple Store that would have the videos embedded throughout the chapters for viewing.

So what are you waiting for, check it out now and let us know what you think – Visualizing Data with Microsoft Power View.

Here is an example of one of the videos for a Learn By Doing exercise:

Posted in Business Intelligence, SQL Server | Tagged: , , , , | 18 Comments »

Using Styles in Microsoft Power View

Posted by denglishbi on April 19, 2012

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

The other night I was working on some final chapter reviews of our upcoming book Visualizing Data with Microsoft Power View and I stumbled across something new in the RTM release of Microsoft Power View.  A new feature called Styles that provides theming capabilities with the views in the report files. Let’s take a quick look at where this new feature is located and what this can do to your views.

image

Now when you launch Power View in your browser you will see a new tab in the ribbon called Styles next to the Home tab.  When you click on this you will see that you are provided 8 different accent color themes to choose from as shown above.

So what do these look like?

Accent 1

image

Accent 2

image

Accent 3

(same as Accent 2 with exception of 1st color)

image

Accent 4

image

Accent 5

image

Accent 6

(same as Accent 5 with exception of 1st color)

image

Accent 7

(same as Accent 1 with slightly different blue color)

image

Accent 8

image

Now the one thing to note is that the Styles will change the colors for the entire view, so this includes all of the items (tables, slicers, etc.), not just one particular visualization.

image

The other thing to note here is that if you have multiple views in your report file that each view can have its own accent color setup.

image

So when designing your reports you have some options for colors to select from.  Nothing custom like you have available in Reporting Services reports, but at least you have something unlike say PerformancePoint.

So that is a quick intro and overview of the new Styles option in Power View, hope you enjoyed the quick tour.

If you are looking for more information and guidance don’t forget to check out our book that will be coming out very soon – Visualizing Data with Microsoft Power View.

Posted in Reporting Services, SharePoint | Tagged: , , | 4 Comments »

Visualizing Data with Microsoft Crescent

Posted by denglishbi on October 7, 2011

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

Yes, it is official, I am now an author.  Yesterday I noticed that the Visualizing Data with Microsoft Crescent book is now listed on Amazon.  I had been asked by Brian Larson if I wanted to collaborate on writing a book for the new reportCrescentBookCover authoring tool and I of course immediately said ‘YES’. I am also one of the technical editors of his other two books for Reporting Services and Delivering Business Intelligence for the new SQL11 release.  There are four of us from Superior Consulting Services that are working on the new Crescent book and so far it has been a very enjoyable experience.  I have been doing the blogging, tweeting, and presenting, so now it is on to something new and actually working towards a published book.

So some insight into the book, this book is going to cover the new reporting authoring tool ‘Crescent’ that is coming out in the next release of SQL11.  This is a new Silverlight browser based reporting experience that will be part of the Reporting Services Integrated Mode configuration with SharePoint 2010.  Not only will the book cover the reporting experience, but we will also go over the Business Intelligence Semantic Models (BISM) which is the new terminology being actively talked about now with SQL11.  In addition to this there will be samples and videos that will provide a very in-depth learning experience so that you will be able to get up-to-speed and familiar with both Crescent and BI Semantic Models.

Now you might know that ‘Crescent’ is the code name provided to this new feature in SQL11 and next week during the PASS 2011 Summit we should be able to have an official name to associate with this along with a few other SQL11 items (maybe even the product itself – 2011 or 2012?).  For the time being though this is the cover, but in the next week or so it should be updated to say ‘Visualizing Data with Microsoft …’.

So for the time being all I can say is thanks to Brian for the opportunity, thanks to my family for their understanding, and I hope that everyone will enjoy the book (and videos).  You can go ahead and pre-order your copy today Visualizing Data with Microsoft Crescent.

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

Using Images in Tabular BI Semantic Models with Crescent

Posted by denglishbi on September 12, 2011

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

One of the really neat features with the new Reporting Services authoring tool code named ‘Crescent’ is that you can reference images directly from your model.  This can look really cool when you use the images in a card visualizations, part of a table, as tiles, and as slicers to use as filters within the report.  Here is a nice report that is part of the Hello World Picnic Sample that came out with the CTP3 release of SQL Server ‘Denali’ (download from Project Crescent Samples and download images from Sample Images and you can find information on the Crescent Samples for CTP3 in the readme here).

image

So this is really cool and provides a really nice visualization perspective to the report both from what you are looking at and to add a little color and life to the report.  When you are looking at a sales report for products, wouldn’t it be nice to see the image of the product or category that you are looking at.  So what better place to store this information than in your BI Semantic Model that you can then leverage with your reporting tool like Crescent.  So how do you do this?

Well in the current CTP3 there are a couple of different ways that you can go about this.  The first option is to store your images in say a SharePoint library and the provide the Image URL references in the model.  That is how the example above is being done.  That report is actually leveraging a PowerPivot Tabular model that was built in Excel 2010 leveraging the CTP3 release of the PowerPivot add-in which you can download here.  The second option that you have right now is to store the images in a database table and reference that way use base64-encoded strings.  Getting this option to work in CTP3 is by no means straightforward either and took me quite a while to figure out.  Just using a base64-encoded string was a puzzle and luckily Robert Bruckner provided that solution as to how to format the binary data in the database to this format.  There is a third option which is simply referencing the binary data from the database, but currently the CTP3 release of Crescent will not recognize this field type in the model at all.

So lets take a look at the two working options we have that we can use with the CTP3 release of Crescent along with the Tabular BI Semantic Models using both a PowerPivot and a Tabular Analysis Services solution as examples.

Image URL

As I mentioned, the Hello World Picnic PowerPivot model uses this setup.  So I will highlight how this is done.

  • First you need to have images available for what ever you want to display.  The download link is available above for the samples and then the instructions are provided in the information readme.
  • Next you need to place the images in a location that can be referenced as a URL in the model.  In this example they are placed in a document library within a folder.

images in sharepoint

images are stored in a SharePoint document library called Shared Documents buried down a couple of levels –> /Images/HelloWorldPicnic/

  • Then within your table in the PowerPivot Tabular model you can provide your URL to the image.  In this example they store the name of the image in the table and then use a calculated column to generate the Image URL.  In order to activate the option to set this as an Image URL in the Reporting Properties (which is in the Advanced tab – might need to turn on the Advanced Mode in the file setting first) you need to define the Table Identifier column in the table.  This means that you need to specify which column makes the table unique to identify each record (in this example it is the ItemId column).

table identifier

need to specify a table identifier – a column that defines the table and makes each record unique

image url

a calculated column is used here that includes the image name column from the table along with the rest of the string to the SharePoint location and concatenates everything together

  • Then once you have deployed your file to SharePoint you can create a Crescent report from the PowerPivot file and you will now see the field in the field list to select from along you can start leveraging this in your report design.

image

the Items Name and Drawing fields are selected from the Field List which get added to a Table

So that one wasn’t too bad.  And this is a nice option because having the images in a document library location is a nice spot where end-users can maintain these as needed and swap them out.

Okay, let’s take a look at the other option that is currently available and that is storing the images in the database and using a base64-encoded string.

Base64-Encoded String

So what if the images are already stored and available in a database table? Well there is a solution that can allow you to do this, at least if you are doing the Tabular Analysis Services project type.

  • Here we already have the images stored in a database table in as a varbinary(max) data type – just a warning, I believe that the max size for the images is 25K, I don’t believe that has changed yet, so just be aware of that in case your images do not show up. In this example we will be using the AdventureWorksDWDenali CTP3 sample database leveraging the product dimension data – you can download the sample databases and projects for Denali CTP3 here.

image

table contains a varbinary(max) column called LargePhoto that contains some images of our products

  • Once the table is imported into a Tabular Analysis Services project we need to modify the Table Properties for the product dimension and change the reference from Table Preview to Query Editor to generate our base64-encoded string that we will use in our model instead of the binary data that is already available from the LargePhoto data (this is currently not supported by Crescent right now, but will be in next release).

image

now the key here is to add the logic to generate the base64-encoded string which is the following – thanks to Robert Bruckner for providing the logic:

cast(N” as xml).value(‘xs:base64Binary(xs:hexBinary(sql:column(“[dbo].[DimProduct].LargePhoto”)))’, ‘varchar(MAX)’) as LargePhoto64

  • Next we need to once again set the Table Identifier (Product Id column – in this example the ProductKey column has been renamed to Product Id) and then set the properties on the new LargePhoto64 column for Image URL to True along with Representative Image to True

image

set the Product Id as the Table Identifier (which is actually the ProductKey column)

image

set the Image URL and Representative Image properties to True for the LargePhoto64 column

  • The next step is the hack that we have to perform to the Code portion because if you try to change the Image URL property back to False it will also set the Representative Image to False and that is not what we want.  You can right-click on the bim file in the Solution Explorer and select ‘View Code’ or pick the option from the ‘View’ file menu.  Once you are in the Code behind do a search for LargePhoto64 and it will take about three times to find the right spot we are looking for which contains the Type property setting that displays ImageURL.  We need to change this to just Image.

image

find the LargePhoto64 column where the Type property is located with ImageURL

image

now change the Type so that it does not include the valuens=”ddl300” and remove URL from the value so that it is just <Type>Image</Type>

  • Now you want to Save this change and then double click on the bim file in the Solution Explorer to go back into the Design view and deploy the database to your Tabular instance of Analysis Service.
  • Setup the BISM Connection File to you model (see my previous posting on this if you are not familiar with how to setup data connections to a Tabular BI Semantic Model – Configuring SQL Server Denali Reporting Services SharePoint 2010 Integration) and then click on the connection file in your SharePoint library and you will now be able to utilize the LargePhoto64 column as an image in your reports.

image

the Product Name and LargePhoto64 columns were selected from the Field List and added to a Table

So there you go, not too bad, huh?  You have two different options right now for using Images in your models.  In the next release we should see direct support with Crescent in being able to utilize binary data directly instead of having to use the base64-encoded string method.  But for the time being this is what you can use.  My preference would be the Image URL.  This just seems not only more straightforward, but also easier to maintain and update, especially from an end-user standpoint.

Enjoy!

Posted in Reporting Services, SQL Server | Tagged: , , , , , | 2 Comments »

Configuring SQL Server Denali Reporting Services SharePoint 2010 Integration

Posted by denglishbi on July 22, 2011

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

Update (7/22/2011): Added a note about an IE Security setting for SharePoint menus at the end of the post.  I will add a PDF of this posting either tonight or over the weekend if anyone is interested in that.

Update (7/24/2011): Added PDF download of the posting for printing, offline, and eReader availability at end of posting in the File Download section.

With the recent release of the SQL Server Denali CTP3 there have been a few changes in the installation process.  One of those is with how the SharePoint integration configuration works.  There was new post yesterday in regards to this process Getting up and running with SQL Server Denali for Business Intelligence (Crescent and PowerPivot in CTP3).  In this post is goes through the steps assuming that you have installed SharePoint 2010 first.  What if you are like myself and installed all of the SQL Server components (SQL Server ‘Denali’ CTP3 Install Experience), including Reporting Services, before SharePoint 2010?  Well the process is a little different because since you didn’t have SharePoint installed first the integration process will not be completed by just installing SharePoint 2010 next.  There are a few steps you can take and I am going to go over the PowerShell option that I took through with the help of some documentation I was provided from Microsoft with previous CTP testing.

So here are the steps that I went through to get this working (this was done on a Windows 2008 R2 VM):

  1. Installed all of the SQL Server Components (installed Multidimensional SSAS and just installed the SSRS and did not configure it) – SQL Server ‘Denali’ CTP3 Install Experience
    image
  2. Next I went through the installation process again and installed an additional SSAS named instance in the Tabular mode.
  3. Then I installed SharePoint 2010:  pre-reqs and then SharePoint (but did not run the configuration wizard – unchecked the box and closed the process down after the install).  I had to actually run the pre-req install twice and had to do a reboot prior to the SharePoint install as I recall.
    image
  4. Then I installed the two SharePoint 2010 SP1 files – one for Foundation and the other for Office Server (this is going to be required to get SharePoint to work with Denali for the database engine).  I believe a reboot was required after this as well. http://support.microsoft.com/kb/2510766
  5. I then did the SQL Server install again to install PowerPivot for SharePoint creating another named instance of SSAS for PowerPivot.
  6. The next step can be done a few different ways and I picked the new PowerPivot for SharePoint Configuration Tool (need to ensure your pass phrase meets the security requirements) – this option is available in the Tools page of SQL Installation or in the Configurations folder in your SQL Server install folder in the Start Menu. This process will configure the PowerPivot integration, activate it, and create a PowerPivot SharePoint Site Collection.
  7. I then installed the ADO.NET Data Services for .NET 3.5 SP1which is required if you want to use PowerPivot against an Atom Feed in SharePoint.  A reboot is required after this install.
  8. Setup the SSRS service and application (since I installed SQL and SSRS prior to SharePoint the Integration with the add-in did not complete – because no SharePoint).
    1. Run the Microsoft SharePoint 2010 Management Shell as Administrator
    2. Type the following PowerShell command> Install-SPRSService
    3. Type the following PowerShell command> Install-SPRSServiceProxy
    4. Now to start the service type the following PowerShell Command> get-spserviceinstance -all |where {$_.TypeName -like “SQL Server Reporting*”} | Start-SPServiceInstance
      image
    5. Verify that the service is started in SharePoint Central Administration –> Application Management –> Service Applications –> Manage Services on Server and now look for the SQL Server Reporting Services Service to see that it has now been setup and is runningimage
    6. Now we are going to create a new SQL Reporting Services Application in SharePoint Central Administration –> Application Management –> Service Applications –> Manage Service Applications and create a new SQL Server Reporting Services Application
      image
    7. Specify a name for the Application, create a new application pool, and specify the database server name.  Down below you will also need to select the web application to associate this application with and I used the Windows Authentication for the database authentication
      image
    8. Now we should be all set and you should see the new application that you just created
      image
  9. Now you are off to the races and can create a Reports Library in SharePoint and add the content types including the new BISM Connection File for using to create Crescent Reports! You will need to install Silverlight to get the SharePoint Create window and this will be needed for Crescent as well since it is a Silverlight application.  If you do not see the Reports Library option you will want to make sure that the Enterprise Features are enabled not only at the Site Collection Level, but also at the Site level. I left out the Report Builder Model content type because from here on out it is all about the BI Semantic Model (BISM)
    image
    image
  10. Now you can create a new BISM Connection File and or Report Data Source to get started with Crescent (shown below in the respective order). After you create these you will not see them in the library.  In order to see them you can go to the Library tab in the Ribbon and select the ‘All reports and dashboards’ option for the view and then you will see the connection and data source files you have created (by default it simply displays just the reports so it is not cluttered)
    image
    image
    image
  11. Now you can launch Crescent from the context menu from the connection and data source files you have created.  In order to create these connection files and data sources I installed and deployed the Denali Samples from CodePlex.  There is one issue with the Tabular project that you need to be aware of when you want to deploy this that has been documented here – Problems with the SSAS Tabular Model Projects Denali CTP3 samples (according to the site the samples have been fixed, so if you haven’t downloaded them you should be okay).
    image
    image

So there you go, you are off to the races.  You know have SSRS in the new SharePoint Integrated Mode setup running as a SharePoint Application (no longer running as its own Windows Service, completely setup and managed by SharePoint now) and you are creating awesome reports with Crescent.

Enjoy!

NOTE: If you are having an issue in say Step 9 and you are not getting menu options in SharePoint you need to go into your IE Security Settings and enable the Active Scripting.  Once you do that you should be able to use the menu options in SharePoint.

image

File Download:

image

Posted in Reporting Services, SQL Server | Tagged: , , , , , | 12 Comments »

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.

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.

Solution

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,”&”,”&amp;”) 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("&amp;","&").Trim()) Then
noDups.Add(SpStr(i).Replace("&amp;","&").Trim())
End If
Next

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

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

Conclusion

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.

Enjoy.

File Downloads:

imageimage

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

Reporting Services (SSRS) OR Filter Logic

Posted by denglishbi on January 28, 2011

In designing a report one of the requirements is typically to add parameters and be able to filter the results displayed in the report.  For the most part these types of requests are to filter the data so that the reports include data where the values are inclusive to what is selected. 

Scenario

Let’s say that I am working on having a sales meeting and want to include the local sales people along with some of my top performers.  I would create a report with two parameters, one to filter the report by state and the other based on the sales amount like the following:

image

In the example above I am using the ever famous Adventure Works 2008 R2 database pulling data from the Sales.vSalesPerson view.  I am referencing the same view for the additional dataset that I use to provide available values for the StateProvince parameter performing an aggregate ‘Grouped by’ on the Sales.vSalesPerson.StateProvinceName column.

I then setup the report to display the list of sales people that I would like to send an invite to for the meeting.  My criteria will be to include the sales people that live in Minnesota because that is where I am planning on having the sales meeting and the second criteria is to also include people that have had sales of at least $1 million.  I go ahead setup the parameters with the dataset, create the report, and select the criteria.

image

image

One thing to point out here is that I am placing the filtering criteria on the Filters portion of dataset and it is not embedded in the Query.  In this case I am using a database view that I could embed this logic, but there might be certain scenarios when you are referencing an item that you cannot do this like against a stored procedure or SharePoint list.

Issue

The report returned the results based on my criteria, but in this case what was returned was only a single sales person.  Not exactly what I wanted.  What I was looking for was to include the sales people that lived in Minnesota along with the people that had at least $1 million in sales.  The way that the report was setup it only returned results for people that met both criteria, not one or the other.

If I go back into the Filters section of the dataset properties I do not see an option to change the relationship of the filters from an ‘And’ to an ‘Or’ relationship.  Where is the logical operator selection?  Hmmm….  If I travel back in time a few years I believe that Report Builder 1.0 had an option to specify the logical operator between the parameters that I would use.

image

Working with a Filter Condition (Report Builder 1.0)

Logical Operators

As you add additional filter conditions to the filter area, a logical operator is displayed between each filter condition. Report Builder supports the And and Or logical operators. By default, the logical operator is And, but you can easily change it by clicking And and selecting Or.

Interesting.  Looks like this what kind of left out with the newer versions of Report Builder.  So where do we go from here?  How about we head over to the Reporting Services forums and see what we can come up with?  After a few searches we come across this thread – How to filter a table with an “OR” condition.  We find out that you would run into this same type of issue with data regions on the report if you try and add filters to them as well.  The solution for this issues is not as obvious as one would think, but there is a workaround based on the answer to the thread.

Solution

In order to resolve the fact that there is no option to select the logical operator between filters they must be combined into a single filter option.  This mean that the second filter will be removed and the OR statement will be placed into an expression in a single filter.  This same technique can be utilized on a data region as well within the report.  In the configuration of the filter we will set the expression like such:

image

And the Filters portion will look like the following now with the value being set to True and the expression type have a data type of Boolean.

image

Now when we go and run the report the results contain not only the sales person from Minnesota, but also the other sales people that had sales of at least $1 million.

image

Now we have the complete list of 14 sales people that we can send an invite to attend the sales meeting.  The one person that lives in Minnesota and the other 13 that had at least $1 million in sales.

Conclusion

Coming to solutions is not as always as straight forward as one would assume.  There is typically always a workaround to get at the desired results.  You might not necessarily have to go to the lengths that we did here if you are going against the database directly, but in some cases you might be using a stored procedure that you cannot modify or using a SharePoint data source that you might not be able to incorporate this logic against directly.

If you are interested in taking a look at the RDL file, feel free to download it from my SkyDrive account here:

image

Posted in Reporting Services | Tagged: | 16 Comments »