Parameterizing an Excel Spreadsheet

I’ve had a few discussions in the past week about not only including Excel spreadsheets on PerformancePoint dashboards, but specifically about how to pass values from Filters to the spreadsheet. I did a few demonstrations to show the capability to some customers. Immediately after a few of the demonstrations I received a emails asking me to explain how I accomplished passing the filters to the spreadsheet. I would explain that I added a parameter to the spreadsheet before I saved it to SharePoint. Each time the customer would say, I did not know that was possible. As a result, I have decided to document the steps in this blog.

The first step is to build the spreadsheet. I have created a very simple sheet that is connected to an SSAS Tabular model. On the sheet is a Pivot table and a single filter, which can be seen in the following image:

Now the most important part is to name the Filter, which will act as the parameter, and to name the Pivot table. Naming the Pivot table is optional. However, if you don’t want to display the filter in the dashboard it is necessary. You can also simple hide row on which the filter resides. Completely up to you.

To name either you need to click FORMULAS from the Excel ribbon. Click in the cell that contains the values of the filter and select Define Name from the Defined Names section of the ribbon and the New Name window will appear. In the textbox labeled Name enter a value. Ensure that Workbook is selected in the Scope drop down list and click OK. The window should resemble the following:

You can repeat these steps for the Pivot table if you don’t want to display the filter. Now all you need to do is save the workbook in a document library in SharePoint. Select File | Save As. From the Save As options select SharePoint and browse to the location where the file will be saved. The Save As window will open. Click the Browser View Options button located towards the bottom of the window and a Browser View Options window will open, which can be seen below:

Select Items in the Workbook from the drop down list. If you have named the Pivot Table select it from the All Named Ranges item list. Next click on the tab labeled Parameters. Click on the button labeled Add and the Add Parameters window will appear:

Click the checkbox next to the parameter and click OK twice. Then save the workbook.

Finally, you should use PerformancePoint dashboard Designer to build a dashboard that will display the Pivot Table. Prior to creating the dashboard you should create a filter that contains the same data as the parameter (the filter that you added to the Pivot table) and you must create a report that displays the excel pivot table that you deployed to SharePoint. Once that is done, create a dashboard and add both the filter and the report to the dashboard. Once they are both there you need to create a connection on the report to the filter. To do so, click the drop down arrow located to the right of the report and select Create Connection:

Select the item that contains the filter.

Next, click the tab labeled Values. Select your parameter name from the drop down list labeled Connect to. Select Member Unique Name from the drop down list labeled Source value and click OK. Finally, right-click on the dashboard in the Workspace Browser, which is located on the left of the Dashboard Designer and select Deploy to SharePoint from the context menu that appears. When the deployment completes the dashboard will open in a web browser. Select a value from the filter and the spreadsheet values will update based on the selection.

As always, if you have any questions or concerns regarding this post, please feel free to email me at pleblanc@sqllunch.com.

Talk to you soon,

Patrick LeBlanc

SQL Server 2012

The Tale of One Tabular Model and Excel 2013 with Multiple Power View Reports

SQL Server 2012 Reporting Services introduced Power View, which was initially available only in SharePoint. This limited the use of Power View only to those individuals and organizations that had SharePoint deployed in some environment. To make Power View more widely available it is now an add-in for Excel 2013. Now anyone with Excel 2013 can create reports and dashboards using Power View. This leads me to the story.

Recently I was working with a customer and they were leveraging Excel to create Power View reports using an SSAS Tabular model as the source data. While this a good choice the problem was that they could not figure out how to connect multiple Power View reports in the same work book to a single tabular model. Now if you have ever worked with data in Excel you should be familiar with the Import Data window, which can be seen below.

When you initially connect to a tabular model using Excel 2013 the Import Data window gives you a few choices, one being Power View. After selecting Power View a new tab is added, but more importantly to the right of the screen you will see the Power View Field list, as seen below:

This is pretty simple and intuitive. Most Excel users can quickly figure this out. However, what if you want to add another sheet connected to the same tabular model? How is that accomplished? Well first click Insert in the ribbon, move the cursor to the right and you will see the Power View Reports button. If you click it a new Power View sheet is added, but you may notice that the Power View Field list resembles the following:

What the….. Where is the data? How do I connect to the existing tabular model connection? Well the first thing that I see most people do is click Data in the ribbon, but most of the items or greyed out. What next? The first step is to go to a regular sheet in the workbook. You know, one with rows and columns. Then click on Data in the ribbon. You should see most of the items enabled. Locate and click the Existing Connections button. The Existing Connections window will open, which can be seen below:

At the very top you will see a list of connections that are in the workbook. Select the connection of choice and click the button labeled Open. When you do the Import Data window will appear. Simply choose Power View from the list of available choices and a new Power View sheet will be added to the workbook. Most importantly, you will have a field list that is fully populated with the information from tabular model.

Now you can create several Power View reports based on a single connection. As always email me at pleblanc@sqllunch.com if you have any questions or concerns regarding this post.

Talk to you soon,

Patrick LeBlanc

 

Uncategorized

SSRS 101 – Your First Report

I know that it has been a while, and I apologize. I have been traveling around the U.S. evangelizing SQL Server to public schools, colleges and universities. Now that does not excuse my time away from the blog, but that is my excuse and I am sticking to it. Anyway, back to SSRS. If you have been following along you are ready to create your first report. That is exactly what I am going to demonstrate in this blog post.

To get started open the solution from the last post in this series, SSRS 101 – Creating a Shard Dataset. If you don’t have the project click HERE to download it. SSRS allows you create very simple reports and very complex reports. In this post I am going to create a very simple report that contains a single object and displays some data. In future posts I will expand on this report by adding additional visualizations.

Watch video if you prefer:


Creating Your First Report

  1. If you haven’t open the solution that I have been using throughout this series.
  2. Open the Solution Explorer if it is not open.
  3. Right-click the folder labeled Reports, select Add | New Item…

     


     

  4. Select Report from the Add New Item window that opens.
  5. In the textbox labeled Name enter Dashboard.rdl.
  6. Click the button labeled Add.
  7. Place your cursor on the new report that appears on the Design tab and left-click.
  8. In the menu select View | Report Data, which is the last item in the list.
  9. The Report Data window will appear. There are five folder available. I will discuss each at some point in this series. For now, right-click the folder labeled Datasets and select Add Dataset….
  10. The Dataset Properties window will open. In the textbox labeled Name enter SchoolAbsencesAndTardies.
  11. Ensure that the radio button labeled Use a shared dataset is selected.
  12. In the list of available shared datasets select Get_SchoolsAbsencesAndTardies_Aggr.
  13. Click the button labeled OK.

     


     

  14. The data set will now be displayed and all the columns that are available.
  15. In the menu bar select View | Toolbox.
  16. Select and drag a Matrix from the toolbox onto the report design surface.
  17. Open the Report Data window as explained in step 8. You could also simple click the report data tab, which should be located towards the bottom of the toolbox.

     


     

  18. Click School_Name and drag it into the column labeled Rows in the Matrix.
  19. Click School_Year and drag it into the column labeled Columns in the Matrix.
  20. Click Tardy and drag it into the column labeled Data in the Matrix.
  21. Expand the School_Name column a little so that can display the values on one row without wrapping.

     


     

  22. Click the Preview tab that is located directly above the report design surface.
  23. Since the Stored Procedure that we used in the dataset has a parameter so will the report. Before previewing the report you will be required to enter a value. Enter NW and click the button labeled Preview that is located to the right. Your report will execute.

     


  24. Save all of your work.

That’s it, your first report is created. In the next post I will explain how to customize the parameter to make it a little more user-friendly. Until then, happy report writing. As always, if you have any questions or concerns regarding this posts or any of the preview posts please feel free to email me at pleblanc@sqllunch.com.

Talk to you soon,

Patrick LeBlanc, Microsoft, Technical Solutions Professional SQL Server and Business Intelligence

 

SQL Server 2012, SQL Server Reporting Services

Configuring PerformancePoint in SharePoint 2013

Last week while assisting a customer configure SharePoint BI I ran into an interesting problem when configuring PerformancePoint. After following all the steps precisely on this page:

Configure PerformancePoint Services: http://technet.microsoft.com/en-us/library/ee748644.aspx

We opened the dashboard designer. Next we started to create a Data Source to an SSAS Tabular model, but when we expanded the database list it was empty. What? I have done this a million times over, what am I missing? I stopped and paused for a bit and somewhere in the back of my memory I remember installing ADMOMD.NET from the SQL Server SQL Server 2008 R2 Feature Pack. After the installed completed we reset IIS, restarted the dashboard designer, and the database list populated.

Hope this helps someone.

Talk to you soon,

Patrick LeBlanc

Uncategorized

Creating a Shared Dataset with Report Builder

I was recently asked by a customer if you could build a Shared Dataset using Report Builder 3.0. More specifically, can you build a Shared Dataset that combines multiple tables from the same database? To be honest, I wasn’t sure initially because I never attempted to. However, after a little testing I found out that it was absolutely possible. In this post I will demonstrate how to accomplish this. I will be using the ContosoSchoolsDW database.

NOTE:
Similar to creating a Shared Dataset using SQL Server Data Tools (SSDT), you must first create a Shared Data Source.

Watch the video if you prefer:

Step-By-Step

  1. Open Report Builder.
  2. Select New Dataset from the list of choices.
  3. Then either select a previously used data source or click the Browse other data sources… hyperlink and locate the data source of choice.
  4. After you have selected a Data Source click the button in the bottom right labeled Create.

  5. On the left of the screen you will see the Database view. You can build a dataset based on Tables, Stored Procedures or Table-values Functions. For now expand the folder labeled Tables.

    NOTE: Before I proceed understand that Foreign Keys are essential to using the Query Designer to create the query for the dataset. While you can write a query from scratch by clicking the button labeled Edit as Text, you must be proficient with corresponding data sources programming language no matter if it is TSQL, PLSQL, etc…. In addition, you can also manually add relationships by expanding the relationships section, which is located between the Selected fields and Applied filters section, but this requires an intimate understanding of the underlying Database schema. Since I know that the appropriate Foreign Keys exists in the selected data source using the Query Designer will be sufficient.

  6. Expand Dim_School and select School_Name from the list of available columns.
  7. Expand Dim_Time and select Year_Name from the list of available columns.
  8. Expand Fact_Student_Absence and select Tardy from the list of available columns.

    NOTE: If you want to see the TSQL that the designer is generate click the Edit as Text button in the ribbon. Also, if you would like to aggregate the data you can do so by clicking in the column next to the desired field and select the aggregation type from the list of available values.

  9. Click the filter icon located midway down the window on the right.
  10. Click in the Field name column and expand the Fact_Student_Absence table.
  11. Select Tardy from the list of available columns.
  12. Click in the Operate column and select is not from the list of available values.
  13. Click in the Value column and select null from the list of available values.
  14. Click the save button at the top of the window and the Save As Dataset window will open.
  15. Enter StudentAbsence.rsd in the textbox labeled Name.
  16. Browse to a location where you have been granted permissions to save datasets and click the button labeled OK.

Now if you create a new report the dataset can be used as a source for that report.

Talk to you soon,

Patrick LeBlanc

SQL Server 2012, SQL Server Reporting Services

SQL Lunch – GeoFlow: 3D Geospatial Visualization

Join me next month to learn about a new Geospatial visualization feature of Excel 2013 on the SQL Lunch.

Lunch Details

#75-GeoFlow: 3D Geospatial Visualization

Speaker: Julie Koesmarno

Add To Outlook: Add To Calendar

Date and Time: 5/10/2013 11:30:00 AM CST

BIO: Julie Koesmarno is a consultant in SQL and BI space, with a Bachelor’s degree in Information Technology specializing in software development. She has over 8 years of commercial experience in SQL Server for large-scale and multi-million-dollar OLTP and ETL projects as a SQL Server developer and system analyst/designer. Her recent project includes implementing a pilot Data Warehouse project using SQL Server 2012, with Tabular Model. She regularly blogs (www.mssqlgirl.com) on SQL Server and BI, particularly Tabular Model.


Topic: #75-GeoFlow: 3D Geospatial Visualization
Have you seen PowerView map features? Do you think it is cool because of its self-service BI aspect and ease of use? Wait until you see this 3D Geospatial Visualization tool called GeoFlow (Preview). In this session, you will learn how to use GeoFlow. You will learn some of the limitations of the Preview version of GeoFlow. Finally, you will learn how to transform location based data into, more than just a report, but a visually engaging tour!

If you would like to present on the SQL Lunch or want to see a particular topic presented please feel free to email me at pleblanc@sqllunch.com.

Join if you have time,

Patrick LeBlanc, founder SQLLunch

SQL Lunch

Introduction to SQL Server 2012 – Webcast Resources

First, I would like to thank everyone for joining my webcasts today. Very much appreciated. Secondly, I would like to apologize for not being able to show the AlwaysOn demonstration. Shortly, after the webcast I found out that we lost internet connectivity to our lab. They are currently working on it.

Now to business, during the webcast I promised to provide you all with the scripts that was used during the presentation and some other documents and links. Here you go:

Webcast Downloads

Scripts

PowerPoint

AlwaysOn Project and Backup

Documents and Links

AlwaysOn Virtual Step-By-Step

New SQL Server 2012 XML – Scroll down about half way and you will see the documentation on XML updates.

New SQL Server 2012 Reporting Services

Extended Events

www.sqllunch.com

http://msbiacademy.com/

Again thanks for joining and I hope you all find this information helpful.

Talk to you soon,

Patrick LeBlanc

SQL Server 2012 Step-By-Step

AlwaysOn, Speaking

SQL Lunch – Power View Against Multidimensional Models

It has been some time since I have hosted or presented on the SQL Lunch. Work and home life is a balancing act where home typically wins. In the case of the SQL Lunch home is winning more and more every day. However, I am committed to delivering or hosting a single SQL Lunch each month. This month’s topic is Power View Against Multidimensional Models.

Lunch Details

Speaker: Patrick LeBlanc

Add To Outlook: Add To Calendar

Date and Time: 3/20/2013 11:30:00 AM CST

Topic: #74-Power View reports against Multidimensional models
Learn how to build reports using Power View against your Multidimensional models. In this session, you will learn how SQL Server Analysis Services exposes the multidimensional model in a tabular view so that Power View and other clients can send DAX queries against multidimensional models. You will also learn new features in Power View that help you to gain insight into your complex multidimensional models.

 

If you would like to present on the SQL Lunch or want to see a particular topic presented please feel free to email me at pleblanc@sqllunch.com.

Join if you have time,

Patrick LeBlanc, founder SQLLunch

SQL Server 2012, SSAS

Successful BI for Educational Institutions

Do you work for US Education? Are you interested in Learning about Microsoft BI? If so, please join me as I discuss and demonstrate how the MS BI Suite can be used in your environment to discover and uncover hidden GEMS within your existing data structures.

Everyone likes a pretty picture, but the key to successful BI projects is the culture change required to adopt a data-driven process.  Culture means everyone, and getting people to “do BI” has proven less successful than putting BI into what they already do.�This session will focus on the familiar tools that can be used to deploy secure, trusted, personalized data to users across your institution, tools that most already own.  Featuring many customer examples and lessons learned.

Register today! 

For the most up to date listing of webcasts or to view others on-demand visit our site
Thank you and we look forward to having you at this upcoming webcast! 


Talk to you soon Patrick LeBlanc

US Education SQL Server and BI Technical Specialist

Learn More at
www.microsoft.com/education

SQL Server 2012

SSRS – Using a List Item to Display Details

How many times have you been asked to develop a report that displays the details for a specified group? For example, maybe you need to produce something like the following report:

The above image shows a report that contains three groupings: School, Location and Event Type. Two values, # of Behaviors and Last Behavior Date, is associated to the Event Type. Directly below each Event Type listed in the report the details can be seen by toggling the # of Behaviors. I have to admit my first try at this worked, but the user experience was absolutely terrible. As a result, a searched Books Online a little and I ran across Lists (Report Builder and SSRS), which provided me with all the information that I needed to effectively solve the problem.

The following short video demonstrates how I solved the problem. I will say one thing, the report is compose of embedded Lists and on table.

Watch the Video

Downloads

Contoso Schools DW: Click Here

SQL Server Data Tools SSRS Project: Click Here

Step-By-Step

  1. Prior to following these steps, please restore the Contoso Schools DW database. See the above section labeled Downloads.
  2. Once the database is restored you will create a new SQL Server Date Tools SSRS project.
  3. Add a Shared Data Source that connects to the Contoso Schools DW.
  4. Add a Shared Dataset named StudentDetails that uses the data source from step 4, and use the following query as the source:

    SELECT

        s.School_Id,

        dblt.Behavior_Location_Id,

        bet.Behavior_Event_Id,

        s.School_Name School,

        bet.Behavior_Event_Desc EventType,

        dblt.Behavior_Location_Desc Location,

        st.First_Name +’ ‘ +st.Last_Name Student,

        t.Date,

        1 Behavior

    FROM dbo.Fact_Student_Behavior sb

    INNER JOIN dbo.Dim_Behavior_Event_Type bet

        ON sb.Behavior_Event_Id = bet.Behavior_Event_Id

    INNER JOIN dbo.Dim_Behavior_Location_Type dblt

        ON sb.Behavior_Location_Id = dblt.Behavior_Location_Id

    INNER JOIN dbo.Dim_School s

        ON sb.School_Id = s.School_Id

    INNER JOIN dbo.Dim_Student st

        ON sb.Student_Id = st.StudentId

    INNER JOIN dbo.Dim_Time t

        ON sb.Time_ID = t.Time_ID

  5. Right-click on the folder labeled Reports in the Solution Explorer. Select Add | New Item from the menu that appears.

     

     

  6. Select Report from the list of available items on the Add New Item window.
  7. Enter Student Behavior Details.rdl in the textbox labeled Name that is located at the bottom of the window.
  8. Click the button labeled Add.
  9. In the Report Data section, which should be located to the left of the report, right-click the folder labeled Datasets and select Add Dataset… from the menu that appears.
  10. The Dataset Properties window will open.
  11. Enter StudentBehaviorDetails in the Name textbox.
  12. Select the Use a shared dataset radio button.
  13. Select the StudentDetails shared dataset.

     

     

  14. Click the button labeled OK.
  15. Open the Report Toolbox if it is not open, but going to View | Toolbox.
  16. Locate the List object and drag it onto the report design surface.

     

     

  17. Press F4 and the Properties window will open.
  18. In the drop down list located directly at the top of the Properties window, below the words property select Tablix1.
  19. Scroll down the list of properties and locate DataSetName, click in the cell to the right, and select StudentBehaviorDetails from the drop down list.

     

     

  20. Directly below the report design surface you will see to sections, Row Groups and Column Groups.
  21. Right-click the item labeled Details and select Group Properties from the menu. The Group Properties window will open.
  22. Enter Schools in the Name textbox.
  23. Click the button labeled Add and a Group On drop down list will appear.
  24. Select [School] from the drop down list.
  25. Click the button labeled OK.

     

     

  26. Drag a Textbox object from the Toolbox into the List object.
  27. Click in the textbox and a small field list icon will appear.
  28. Click the icon and select [School] from the list of available columns. Don’t worry about the inclusion of the First function.

     

     

  29. Drag a List object from the Toolbox into the existing List object directly below the textbox that was added in Step 27.
  30. With the newly added List object selected repeat steps 21 – 25, replacing the Schools with Locations in step 22 and [School] with [Location] in step 24.
  31. Drag a Textbox object into the new List object.
  32. Click in the textbox and a small field list icon will appear.
  33. Click the icon and select [Location] from the list of available columns.

     

     

    I have added a little formatting to my School textbox, but your design surface should resemble the above image.

     

  34. You may need to increase the length of the two existing List objects before proceeding.
  35. Drag a List object into the List object that was created in step 29. It is the same List object that contains the textbox that contains [First(Location)].
  36. With the newly added List object selected repeat steps 21 – 25, replacing the Schools with Event_Types in step 22 and [School] with [EventType] in step 24.
  37. Drag a Textbox object into the new List object.
  38. Click in the textbox and a small field list icon will appear.
  39. Click the icon and select [EventType] from the list of available columns.
  40. Drag another Textbox directly to the right of the textbox created in step 37.
  41. Click in the textbox and a small field list icon will appear.
  42. Click the icon and select [Behavior] from the list of available columns.

     

     

  43. Drag a Matrix object directly below the two textboxes.
  44. With the matrix selected Select View | Report Data from the menu. The Report Data option is the very last item in the list.
  45. Drag Student from the dataset into the column in the matrix labeled Rows.
  46. Drag Date from the dataset and drop it to the right of the word [Student] in the matrix.
  47. Drag Behavior from the dataset and drop it into the column in the matrix labeled Data.

     

     

  48. With the matrix selected press F4.
  49. In the properties window locate the Visibility section.
  50. Select True for the Hidden property.
  51. Click in the cell to the left of the ToggleItem property.
  52. Select Behavior from the drop down list.

     

     

  53. Preview the Report by selecting the tab at the top of the design surface labeled Preview. This report may take 20-30 seconds to run.
  54. If you click the (+) plus symbol located to the left of each number you will display the details for a giving Event Type.

     

That was a lot of steps, but now you have a report that can show aggregated data a different levels and show details on a single report. You may want to format the report a bit to make a little more visually appealing to your end-users.

Talk to you soon,

Patrick LeBlanc

SQL Server 2012, SQL Server Reporting Services ,