Dan English's BI Blog

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

Archive for the ‘Reporting Services’ Category

Reporting Services SharePoint Large Parameter List Issue & Fix

Posted by denglishbi on November 8, 2012

UPDATE (11/13/2012): Additional modification needed to provide ability to manage the parameters in SharePoint (see the bottom of the post for the updates)

If you are using Reporting Services (SSRS) in SharePoint Integrated Mode (I saw this issue with SSRS 2008 R2 SP2 & SSRS 2012 with SharePoint 2010 running with SP1 CU Aug 2012), then something you might want to be aware of.  If you ever have to load a parameter list with lots of records (say 35K+) then you might end up receiving a message like this:image

An error (Unable to read data from the transport connection: The connection was closed.) occurred while transmitting data over the HTTP channel.
An error (Unable to read data from the transport connection: The connection was closed.) occurred while transmitting data over the HTTP channel.
Unable to read data from the transport connection: The connection was closed.

 

If you are running the report in BIDS or SSDT or Reporting Services Native Mode you will be fine, just an issue with SharePoint Integrated Mode.  I also noticed this with a report that had approximately nine parameters (four were hidden) and two of them had approximately 10K records.  So I think it was the combination of the parameters along with some default values getting set that caused the issue.

REPRO: For a quick example of this issue you can easily replicate this with populating a parameter with the FactFinanceKey from the AdventureWorksDW2012 database.   Deploy the report to SharePoint and try and run it:)

I have filed a Microsoft Connect Bug for this here – Reporting Services (SSRS) SharePoint Large Parameter Issue

Well after a bunch of searching and digging through the forums, blogs, BOL, and additional documentation (and even tried an old ProClarity fix) I found the fix for the issue – MaxItemsInObjectGraph….yes, that was a setting that I had to incorporate into a couple of the configuration files for Reporting in SharePoint:S

The first forum posting that pointed me to the web.config and client.config files was Issue Exporting SSRS 2012 Report to CSV file in SharePoint 2010 (MaxReceivedMessageSize property exceeded) and then after doing some additional research running live log tracing on SharePoint I tracked down that it was really related to the WCF and I came across this additional MaxItemsInObjectGraph setting in quit a few posts such as this one MaxItemsInObjectGraph and keeping references when serializing in WCF.

Now I didn’t find the magic post that just told me what to do, but with some trial-and-error I came up with the following steps to fix this:

1. Edit the web.config file in the following location C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\Reporting

NOTE: prior to modifying a config file, always make a backup of the file first and use a text editor like notepad, notepad++ to modify the file, do not use a rich text editor.

2. In the web.config file find the <behaviors> section and make the following addition:

    <behaviors>
      <serviceBehaviors>
        <!–
            Notes:
             1) maxConcurrentCalls is set to 16(default value in .Net3.5)*Processor Count(assuming 32)
             2) maxConcurrentInstances is set to 512 since InstanceContextMode is PerCall
             3) These settings can be removed after upgrading to .Net 4.0
         –>
        <behavior name=”RSExecutionBehavior”>
        <serviceThrottling maxConcurrentCalls=”512″ maxConcurrentInstances=”512″ />
        <serviceMetadata httpGetEnabled=”true” />
        <serviceDebug includeExceptionDetailInFaults=”true” />
        <dataContractSerializer maxItemsInObjectGraph=”2147483647″ />

        </behavior>
      </serviceBehaviors>
    </behaviors>

3. Save the file and then go and edit the client.config file, remember to make a backup copy prior to modifying it.  This file is located in the following location C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebClients\Reporting\

4. In the client.config file add the following section below the <System.ServiceModel> tag and before the <client> tag

  <system.serviceModel>
    <behaviors>
      <endpointBehaviors>
        <behavior name=”RSExecutionBehavior”>
      <dataContractSerializer maxItemsInObjectGraph=”2147483647″ />
        </behavior>
      </endpointBehaviors>
    </behaviors>

    <client>

5. Now I also modified four of the endpoint settings in the <client> section to reference the new behavior that I added (IReportExecution and IReportStreamingAsyncClient):

<endpoint name=”https” contract=”Microsoft.ReportingServices.ServiceContract.IReportExecution” binding=”customBinding” bindingConfiguration=”https” behaviorConfiguration=”RSExecutionBehavior”/>
<endpoint name=”http” contract=”Microsoft.ReportingServices.ServiceContract.IReportExecution” binding=”customBinding” bindingConfiguration=”http”  behaviorConfiguration=”RSExecutionBehavior”/>

<endpoint name=”https” contract=”Microsoft.ReportingServices.ServiceContract.IReportStreamingAsyncClient” binding=”customBinding” bindingConfiguration=”httpsStreaming”  behaviorConfiguration=”RSExecutionBehavior”/>
<endpoint name=”http” contract=”Microsoft.ReportingServices.ServiceContract.IReportStreamingAsyncClient” binding=”customBinding” bindingConfiguration=”httpStreaming”  behaviorConfiguration=”RSExecutionBehavior”/>

6. Once that was done I had to restart the server where the Reporting Services application was running.  The web.config settings seemed to work right away, but not the client.config settings.  Once it restarted I warmed up SharePoint and got SSRS running again.  I tested out my report and it started up just fine, no error message:)

image

And that is the fix, can’t believe this is the first time I have come across this issue, but now there is a fix and it is documented:D  Just a word of caution, this still does not perform as well as it does in SSRS Native Mode, but at least it does work now.  It appears that it takes maybe twice as long to load in SharePoint as it does through the Report Manger (your mileage may vary though).

I have filed a Microsoft Connect Bug for this here – Reporting Services (SSRS) SharePoint Large Parameter Issue

UPDATE (11/13/2012): Additional modification needed to provide ability to manage the parameters in SharePoint -

One quick update on this – there is one more additional service & endpoint that needs to be updated in the config files as well. The reason is so that you can manage the parameters in SharePoint if needed.

Web: <service name=”Microsoft.ReportingServices.ServiceRuntime.ReportServiceManagement” behaviorConfiguration=”RSExecutionBehavior”>

Client: <endpoint name=”https” contract=”Microsoft.ReportingServices.ServiceContract.IReportServiceManagement” binding=”customBinding” bindingConfiguration=”https” behaviorConfiguration=”RSExecutionBehavior”/>

I added the behaviorConfiguration reference. Not only did I update this in the web.config file, I also updated the endpoints in the client.config file as well. It seems like it picked up on the change without a reboot this time as well, so the web.config setting seemed to work, but wanted to keep things in synch so I updated the client.config as well. This time though I did receive a valid SharePoint error message and was able to look it up in the logs to pinpoint the additional service being referenced for this operation. There are two other items in the config file – ReportServiceBackgroundProcessing and AlertManagement – and I have not had to alter these ones yet.

NOTE: Also, in a multi-server environment I had to update the client.config file on the WFE server (web.config did not exist since it wasn’t the application server) and restart that one to get the reports to behave consistently.

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

Presenting Power View Reports to Users

Posted by denglishbi on October 17, 2012

As you begin to use the new Power View reporting feature that is available with the Reporting Services 2012 integration in SharePoint 2010 one of the things that you will need to assess is how you are going to allow the users to access the reports.  What are the different options that you have when providing access to the Power View report files (rdlx) in SharePoint?

Here is a list of options that you could use:

  • store the Power View reports in a Document Library
  • display the Power View reports in a PowerPivot Gallery
  • use a Page Viewer Web Part to display the Power View report
  • use a Silverlight Web Part to display the Power View report

Here is how each of these will look to the end-user that will be access the reports:

Document Library

image

Above we can see that the Power View report files are available and stored in a document library, nothing really fancy, just storing the rdlx files.

PowerPivot Gallery

image

The PowerPivot Gallery is a very nice option to display the Power View reports. Provides a nice visual display of the reports and users are able to see if additional views are included in the files as well.  The view I have displayed above is the default Gallery setup, but additional options are available such as just displaying the files in the document layout like the Document Library display in the first example, Theater, and Carousel.

Page Viewer Web Part

image

The Pager Viewer web part is a nice option to include a Power View report (or a particular view) on a page.  This is a nice option to include a Power View report into any existing page and to provide a nice controlled output of the report.  You can see that the ‘Preview Bar’ has been removed from the top of the report so a user is not able to access the ‘Edit Report’ option.  The users are still able to interact with the report and access the different views in the report as well.

image

How do you configure the Page Viewer web part?  Pretty basic and a bit easier than the next option that will be looked at.

  1. Create a web part page
  2. Add a Page Viewer web part to the page
  3. Specify the URL path along with additional parameters for the report
  4. Specify the height and width for the web part for the appearance

Here is what the Page Viewer web part settings look like for the example displayed above:

image

Here is the URL path that I have specified along with some additional parameters that I am setting to provide the desired output:

/_layouts/ReportServer/AdHocReportDesigner.aspx?RelativeReportUrl=/Shared%20Documents/Hotel%20Performance%20Dashboard.rdlx&ViewMode=Presentation&ReportSection=ReportSection2&True&Fit=True&PreviewBar=False

In this example I am setting the View Mode, the Report Section I want to display, and hiding the Preview Bar.  These are the core settings that need to be set to get this output.  There are additional settings that you can explore and a few of these I discuss here in the following post URL Actions with Reporting Services Power View (RTM) and as one person commented there are additional parameters available and you can see these by accessing the properties of the output in PowerPoint as discussed here Can i edit my PowerView Report exported to PowerPoint in Presentation Mode? YES YOU CAN.

If we dig into the above output for the Pager View web part we can see that the output is displayed in an iframe and granted just by removing the Preview Bar doesn’t mean that the user couldn’t track down the actual location of the report and launch it from there, so this by no means is a security option.  The user still needs the proper permissions to the Power View report file (Restricted Readers – requires the ability to Open Items and this is different compared to regular Reporting Services reports because of the Silverlight application).

image

Silverlight Web Part

image

The Silverlight web part display looks identical to the Page Viewer.  This option requires a bit more of setup and I have to thank Robert Bruckner for the details on configuring this option.

    1. Create a web part page
    2. Add a Silverlight web part to the page
    3. Specify the URL path to the Silverlight application
    4. Specify the custom initialization parameters (Other Settings)
    5. Specify the height and width for the web part for the appearance

The path to the Silverlight application is the following:

/_layouts/ReportServer/ClientBin/Microsoft.Reporting.AdHoc.Shell.Bootstrapper.xap

The custom initialization parameters that I used in the example above are the following:

ItemPath=http://win-doqtev64aj4/Shared%20Documents/Hotel%20Performance%20Dashboard.rdlx,ReportServerUri=http://win-doqtev64aj4/_vti_bin/reportserver/,ViewMode=Presentation,PreviewBar=False,Fit=True,ReportSection=ReportSection2

The template Robert provided for this setup is the following for the parameters:

ItemPath=http://<YourServername>/<YourSite>/YourReport.rdlx,ReportServerUri=http://<YourServername>/_vti_bin/reportserver/,ViewMode=Presentation,PreviewBar=False,Fit=True

Here is what the Silverlight web part settings look like for my example above:

image

When you add the Silverlight web part to the page you will be prompted for the URL path for the Silverlight application. That setting and address is also available in the above screenshot in the Application settings when you click on the ‘Configure…’ button.

You can once again dig into the output like we did above with the IE Developer Tools (F12) and you will see the following:

image

A little different output here and if you dig around enough you will eventually track down the initialization parameters, so once again not an option to prevent the users from finding out the original source of the file, but it is still an option that you can use to display Power View reports in a web page.

Summary

So to wrap up this post, I just wanted to show the different options you have available in SharePoint to display and provide access to Power View reports to end-users.  The first two options allow the users to launch the reports in the web browser and access the Edit Report or go into Full Screen modes.  I would use the PowerPivot Gallery option because of the nice output and provides the users quick insight as to what is included in the report and an overview of the different views (if more than one exists).  The last two options would be something to explore if you want to include a Power View report on a web page.  This is a nice clean option where we have removed the Preview Bar, but have still allowed the user access to the report to interact and browse the different views.  This could be a nice option if say you want to include a Power View report in a PerformancePoint dashboard.

I hope you have enjoyed this quick overview and for more information on how to use Power View and creating tabular models, don’t forget to check out our book here – Visualizing Data with Microsoft Power View.

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

Using PowerPivot Model Images with Excel 2013 Power View

Posted by denglishbi on September 13, 2012

As you have heard and possibly seen in my other Excel 2013 posts, Power View is coming to the Office suite.  This is great news to everyone that uses Excel.  The one thing you might be curious about is how to include and reference images within your data model.  This post will cover the options that you have and provide a few screenshots of the process.

Database Images

If you are working with data that resides in a database and the images are stored in the database table you will be able to work with them once you load the data into your data model within PowerPivot.  This hasn’t changed at all, the only thing that has changed is that now you don’t have to upload your PowerPivot file into SharePoint to create the Power View report.  Instead you can simply insert a new Power View report and create your reports.

Here is a quick look at this type of a setup.

First the database tables have to be in place.  In this example I am going to reference two different tables that will have images in them, one for authors and the other for books.

image

Once the records have been added to the tables we just need to add the images and one way to do this is with some T-SQL and OPENROWSET command as displayed in this blog post here – Insert binary data like images into SQL Server without front-end application.

image

Once the data is in place we need to create our model, so we will open up Excel 2013, go into the PowerPivot window, load the data into our model, define relationships, and do some advanced setup on our tables.

Let’s take a quick walkthrough of this process if you are not familiar with this yet.

image

Once you have activated the PowerPivot add-in in Excel 2013 you will be able to select that tab in the ribbon and manage your data model.

image

Once you are in the PowerPivot window you will be able to get external data and import data into your model.

image

Select the tables you want to work with in your model.

image

Import the data.

image

Switch over to Diagram View and drag-and-drop the fields to establish relationships between the tables.

image

Select the Author table and define the Table Behavior so you can define the default label and image for each record.  Do the same for the Book table.  Now that you have that setup one last step you can do before you start to use the model is to perform some cleanup.

image

Here we have hidden the two row identifier columns in each of the two main table and also hid the reference table that relates the two tables.  You can do this by simply right-clicking on the items and selecting ‘Hide from Client Tools’ from the context menu.

Now you can switch back to Excel and from the Insert tab in the ribbon select Power View.

image

Now we will see the two tables in our model and the item that we are particularly interested with is that the Photo column is available and the key step was the configuration step in the Table Behavior.

image

Now we can select the items in the field list and start to put together a report such as this –

image

Web Server Images

Another option you have for referencing images in your Excel PowerPivot model is to store the image files on a web server.  The one thing that you need to make sure is that the web site is setup with Anonymous authentication and users have permissions to the file location.  Once that is setup you can establish a URL reference to these images files in your table within your model.  After the URL is configured you will need to review the advanced settings just like the database image setup so that you can reference the images in your Power View report.

Let’s take a quick look at the difference here.  First we have the anonymous web site.

image

Then we place the files on the web server to reference in the location we defined for the web site.

image

Make sure that users have access to the location.

image

Now we go through the similar steps as we did in our first example, but after we define our relationships we need to go back into our tables and define the URL paths for our images.

In both tables we will create a calculated column called PhotoURL and with some DAX setup the paths to the images.

image

image

The secret here is to make it data driven, if you are not able to do this then you would need to store the URL values in the source table and import them.  Luckily with a little DAX we could setup a formula that will work for each record.  Now if someone wants to replace one of the images on the web server they can do so and it would be reflected in the report with a refresh.

The next step is to go into the Table Behavior like we did in the previous walkthrough and make reference to this new column for our default image.

image

Once this is done we can go ahead and switch back to Excel and create a Power View report, just like we did previously.

The one thing to note here is that when you switch over to the PhotoURL reference you will be prompted with a security warning about accessing external pictures.  Go ahead and click ‘Enable Content’.

image

Now we can look at our same report –

image

Only this time we are using the PhotoURL versus the Photo field.

image

Conclusion

And there you go, two different options for referencing and using images within your Excel 2013 Power View reports.  Using the web server setup is nice because then you can swap out the images a bit more easily, but you still have the option to store the images in the database if that is how you where you want to keep them. 

Now go ahead and try this on your own and really spice up your reports with some nice images:)

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

Visualizing Data with Power View: Pan-Geo goes PowerPivot

Posted by denglishbi on August 9, 2012

Back in June we released our book on Power View, Visualizing Data with Microsoft Power View.  Not only does the book contain great information about creating reports with Power View, but it also contains content on building tabular models that can be utilized with Power View.  The current release only works with tabular models, so either PowerPivot or Tabular Analysis Services (SSAS).  With our book we primarily focused on building out a Tabular SSAS solution that gets used for the majority of the Learn By Doing exercises.  Sure there are a few times where a PowerPivot file is referenced or we do some initial modeling in PowerPivot, but most of the work gets done with the tabular solution that gets deployed to Analysis Services.image

Well just a few weeks ago when Office 2013 Preview was released I decided to build out the entire Pan-Geo Hospitality & Travel model in PowerPivot.  I went ahead and replicated all of the exercises that we did in the tabular project into PowerPivot (using Excel 2010 and the SQL 2012 PowerPivot add-in).  I then took that same file and upgraded it up to Excel 2013 as well – Power View Meet Excel 2013.

image

So since I took the time to build this out I decided why not go ahead and share:)

If you are interested in Power View and especially if you have already purchased the book (hint, hint) I have gone ahead and uploaded the two Excel files to my SkyDrive here – Power View Book.

  • PGHT Tourism PowerPivot Model 2010.xlsx (Excel 2010 uses the SQL 2012 PowerPivot add-in)
  • PGHT Tourism PowerPivot Model 2013.xlsx (Excel 2013 uses the PowerPivot for Excel 2013 add-in)

Now in order to get a good download of these don’t open the Excel files up in the browser in SkyDrive, simply check the box next to the file and chose the download option on the right-hand side of the screen.  I would do them separately.  Then simply open them up and have at it.  Now if you want to use Power View in Excel you will need to download and install the Office 2013 Preview.  If you want to build out map reports with that you will need to make sure you have an Internet connection so that it can connect to the Bing Map service.

I would be very interested in some feedback and seeing what people build out with the PGHT dataset.  So feel free to leave a comment here or send me a tweet (@denglishbi).

Just an FYI, the Power View book is available on McGraw-Hill Professional, Amazon, Barnes & Noble, Google Books, as well as Kindle, Nook, and other eBook formats and sites.  Please check it out and leave us your feedback and comments:)

Enjoy!

Posted in PowerPivot, Reporting Services | Tagged: , , , | 2 Comments »

SQL Saturday Event Analysis using Power View and Office 2013

Posted by denglishbi on August 6, 2012

Updated (8/7/2012): Added screenshot at end of post of which includes data broken out by fiscal calendar (starts in July) and link to that file as well.

Updated (8/9/2012): Added some information at the bottom of the post in regards to downloading issues and more information available in the comments section.

Last week I saw a post by Sam Vanga called SQL Saturday US Map.  Needless to say I found it interesting and a great idea.  That made me think a bit and I was like hmm… why not take that same data and use it with the new Excel 2013 with PowerPivot, Power View, and maybe even a little Office 365!  I had to leverage the Microsoft stack of course:)

So I went out to the SQL Saturday site and copied down all of the event details. Needless to say a little scrubbing was required because the data was a bit incomplete to get at some of the location information.  After a little while I felt fairly good with the data and loaded the information into PowerPivot for Excel 2013.  I added a few more columns around the date information to generate a calendar and to do some sorting.  Once that was done I went ahead and started to put together a little dashboard page.  I ended up with the following:

image

We can see that the SQL Saturday events over the past couple of years have really grown in popularity and after 2010 they went global as well.

image

We can zoom in on the map and get a close up as well.  Last year we had the SQL Saturday #99 in Eden Prairie, MN.

image

I even added the ability to drill down from the cities to the zip code level and also some drill down on the column chart for the counts as well.

image

image

Some pretty cool stuff.

We can even upload this into the Office 365 preview and share this information as well which support PowerPivot and Power View.

image

You might notice that the colors for the themes don’t match up quite yet, but other than that it works out well and you can interact with the Power View visualizations and reports!  In the above screenshot in Office 365 I have the tile where the map resides filtered to 2012.  You can see that the map really opens up now for the entire globe compared to the 2010 and 2011 views.  I can only assume that next year it will grow even more:)

The data that I grabbed for this was from the SQL Saturday events page here (just copy paste and a bit of cleanup).

If you would like to download the Excel 2013 file I created you can grab it from here – SQLData.xlsx

Fiscal Year Layout

I received a request from Karla Landrum the PASS Community Evangelist to see the data setup using the PASS fiscal calendar which starts in July versus the regular calendar.  Well I decided to add in a full date table and refresh the output.  Here is the result and the new file can be downloaded here – SQLDataFiscal.xlsx

image

If you are experiencing any issues with the downloads take a look at the comments section of this post.  I did some testing and found some issues if you try to directly download the file from the browser and a workaround if the SQLSat Dash does not load for you.  You can download the file(s) from my Public|Blog SkyDrive folder.

Enjoy!

Posted in PowerPivot, Reporting Services | Tagged: , , , | 15 Comments »

Power View meet Microsoft Excel 2013 Part 2

Posted by denglishbi on July 25, 2012

Last week I did my initial post of going over using Power View inside of Excel 2013 – Power View meet Microsoft Excel 2013.  In that post I went over how I converted the PowerPivot model of the Pan-Geo Hospitality & Travel that was used in our Visualizing Data with Microsoft Power View book (well at least a Tabular SSAS version was used, I recreated the entire model in PowerPivot).  I upgraded that model to use the Excel 2013 version of PowerPivot and then showcased some of the new features such as:

  • Background Images
  • Pictures
  • Themes
  • Relationships
  • Pie Charts
  • Map Visualizations (Bing Maps)
  • Key Performance Indicators

So what other key features are that that might be of interest?  Hmmm, good question.  And yes, there are more features.  Have you been wanting support for hierarchies?  What about drill up and down capabilities?  Well guess what, you get them both:)

You might have noticed in the previous post that you saw some hierarchies listed in the field list:

image

Let’s take a quick look at using the Country Hierarchy and see how this looks in a table, matrix, and a chart.

Table

image

Matrix

image

Chart

image

So in the table and matrix no change other than the fact that we can see our hierarchies in the field list and select them.  This allows us to quickly add multiple fields to the canvas.  What about the chart though?  I picked the country hierarchy, but I am only seeing the first level of the hierarchy which are the continents.  Where are the countries?

Well if you double click on the columns in the chart it will actually drill down into that continent and display the data for the countries!

image

You will also see that the sort by and title text changed to display country and there is a new icon available when you hover over the chart, an arrow that provides drill up functionality.  So the drill up will bring you back up to continent. 

Now the neat thing about the drill up and down functionality is that you can expand on this.  The fields do not necessarily need to be part of a hierarchy, you can actually build out the drill up and down capabilities by adding fields into the field well.  So in this example I will add more fields into the chart below the country:

image

So let’s double click a few times to see what we get:

image

image

Now the one thing is that we really are not sure what we are looking at, meaning that what continent, country, and chain type did we select previously?  I can see based on the sort the trail that I have navigated, but what were the values associated with them.  That would be a nice feature.

The drill down also works for the map reports as well.

image

So if we double click on the China pie chart we will go down to the city level based on the navigation defined in the field well:

image

And you can use the zooming capabilities of the map as well.

image

Pretty cool and you can do more with the map with the layout settings in the Ribbon:

image

In addition to hierarchy, drill up & down, and cool map capabilities there is another minor addition that was added and that was support for hyperlinks in a text box.

image

In this example I have a text box that includes the words ‘Microsoft BI’ and below that a hyperlink to the web site.  If I hold the CTRL key and click on the hyperlink it goes to the web site:

image

They hyperlink simply gets added after you type in the URL and press a space, hit enter, or click out of the text box.

Well that covers the majority of the new enhancements to Power View, for the complete list checkout the links at the bottom of my previous posting – Power View meet Microsoft Excel 2013.

I will continue to work with the new features and post any additional findings I might come across like using images from the data models and the issues you might encounter along with using these files in SharePoint.

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

Power View meet Microsoft Excel 2013

Posted by denglishbi on July 19, 2012

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

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

URL Actions with Reporting Services Power View (RTM)

Posted by denglishbi on June 13, 2012

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

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 »

Using Styles in Microsoft Power View

Posted by denglishbi on April 19, 2012

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 »

 
Follow

Get every new post delivered to your Inbox.

Join 93 other followers