Power Query – Controlling M Query Functions with User Driven Parameters

Have you ever had a user run a query against one of your largest tables only for them to immediately filter the results in Excel to show the last years worth of data? All of that data brought across your network and then immediately filtered out. Or maybe Excel just can handle the amount of unfiltered data they’re trying to return.

In this post I’d like to show you a way to solve this problem with Power Query in a solution that can dynamically filter your data returned based on user driven parameters.

In the scenario that I will demonstrate, rather then returning an entire result of all the company’s employees, I just want to return a list that show employees with a hire date within a certain range of dates that I or a user will provide. Using Power Query’s ability to make queries into Functions I’ll give my user the ability to provide the range of their choice

Connect to the Data

  • In my example I’m going use a SQL Server table as my data source but it could be any type of table. To connect to a SQL Server table from the Power Query tab in Excel select From Database > From SQL Server Database. For my example I’ll be using the AdventureWorksDW sample database.

image

  • Next you will be prompted to provide your Server and Database names where the table is located. Type these in then click OK.
  • Once you provide the Server and Database name you will also be prompted for the credentials you will use to access the data then click Connect.
  • The Navigator pane will appear showing all the available tables.  If you’re following my example with the AdventureWorksDW sample database then choose DimEmployee and then click Edit. This returns back the table to the Power Query Editor. **Note** This could be done on any table from any database.

Making the Query a Function

  • What I’d like to have my users do is return back this list of employees but only when the HireDate column falls within a range of values that they provide. To do this we’ll start by applying a hardcoded value to filter to the HireDate column. Find the HireDate column and apply a filter by clicking the down arrow next to the column and then Date Filters > Between

image

  • The range of values you filter on will depend on your table but for the DimEmployee table in AdventureWorksDW I used the following filter then clicked OK.

image

  • This simple places a filter on the query. If we want to make the filter dynamic we need to modify the M Query that’s behind the user interface. Go to the View tab on the Query Editor ribbon and select Advanced Editor. This will open the query window where you can modify

image

  • Next modify this query to add in a start date and end date parameter with the code in red below.

(startdate, enddate)=>
let
    Source = Sql.Database("localhost", "AdventureWorksDW2012"),
    dbo_DimEmployee = Source{[Schema="dbo",Item="DimEmployee"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_DimEmployee, each [HireDate] >= #date(2000, 1, 1) and [HireDate] <= #date(2002, 1, 1))
in
    #"Filtered Rows"

  • Once the parameters are created you can reference them in the query to replace the hardcoded value in the filter with a dynamic value from the parameters.  Modify the query with the code in red below then click Done.

(startdate, enddate)=>
let
    Source = Sql.Database("localhost", "AdventureWorksDW2012"),
    dbo_DimEmployee = Source{[Schema="dbo",Item="DimEmployee"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_DimEmployee, each [HireDate] >= #date(Date.Year(startdate), Date.Month(startdate), Date.Day(startdate)) and [HireDate] <= #date(Date.Year(enddate), Date.Month(enddate), Date.Day(enddate)))
in
    #"Filtered Rows"

  • This will convert the query into a function. You can test this function by clicking Invoke and then you will be prompted to provide date values to filter on.

image

image

  • If you did invoke the function make sure you remove the Invoke step before moving on. You can do this by clicking the delete icon in the Applied Steps pane.

image

  • This should return the query back to a function ready to be invoked. Now, go to the Home tab on the Query Editor ribbon and select Close & Load.
  • This will save the M Query function into the workbook but does not return any results yet.  Just how we want it! Our next step is to pass the values we want into the function.

image

Making it User Interactive

  • Go to a blank spreadsheet and create a simple Excel table that has a StartDate and Endate column with one row of values like this:

image

  • To make it so our users can type a value in this Excel Table and pass it into our function we need to take bring this small table into Power Query. Select one of the cells inside the table and on the Power Query tab select From Table. This will take the content of this table and bring it into the Power Query Editor.

image

  • To pass these two values into our function go to the Add Column tab and select Add Custom Column.
  • Write the following formula to connect the DimEmployee function created earlier to the dates we’ve now defined in the Excel table then click OK:

DimEmployee([StartDate],[EndDate])

image

  • If all your default settings are turned on inside Power Query you will probably get a privacy warning pop up.  This is because you’re working with two different data sources (1. SQL Server Table, 2. Excel Table) and there’s potential privacy concerns. In our scenario there are no legitimate privacy concerns so I’ll hit Continue. I also set the data sources to Organizational because the data sources should be contained within my company. Read more about Power Query privacy settings here.

image

  • Once the privacy settings are configured Power Query will add in a new column just called Custom (We could have renamed it previously). Click the Expand button next to the Custom column, uncheck Use original column name as prefix and then click OK. This will bring back all the rows that have hire dates between our date range.

image

  • Go ahead and remove the StartDate and EndDate columns from the query now but multi-selecting them and then right-click and select Remove Columns.
  • Next, rename the query in the Query Settings pane to Employee Data then click Close & Load on the Home tab.
  • You should now have two spreadsheets (It would obviously be a good idea to rename these) in your workbook.
    1. Sheet1 that has the Excel table with the date range values
    2. Sheet2 that has the results of the Power Query query. This data could have optionally been send to the Power Pivot Data Model
  • Now go back to Sheet1 and change the date range values in the StartDate and/or EndDate columns. After making this change the next time the Power Query query is refreshed it will pick up the results from the table to filter on saving your users from query a really large table when they only need a subset of the data.

Finishing Touches

If my users aren’t very familiar with Power Query and don’t know how to refresh their queries then we can build a quick little macro to provide a button that does it for them. Using a technique I learned from Ken Puls (blog | twitter) in a few short steps our macro will be done. In Ken’s post titled Refresh Power Query with VBA he shows how to refresh all Power Query queries in a workbook with a little VBA script.  I’m going to take the same principles he shows but just refresh the query I care about.

  • In your workbook press Alt + F11
  • Right-click on VBAProject(Book1), this may be named something different if you’ve saved with a new name, in the Project Explorer and select Insert > Module.

image

  • Use the following VBA script to refresh the workbook connection for our Employee query we created earlier (If you named your query something different you may need to adjust the section highlighted in red):

Public Sub UpdateEmployeeQuery()
‘ Macro to update my Power Query script(s)

Dim cn As WorkbookConnection

For Each cn In ThisWorkbook.Connections
If cn = "Power Query – Employee" Then cn.Refresh
Next cn
End Sub

  • Hit close on the VBA window.
  • To manually try the new code hit Alt + F8 and you will be prompted to run the script. Select the Macro we just created and click Run.

image

  • You should notice this kicks off the refresh of our Employee query.

image

image

  • Click somewhere in the worksheet that you want the button and then select the Macro we created to assign to the button. Click OK.

image

  • Click inside the button to rename it and then you’re all set!
  • Now all you have to do is change the values in the table and click the button to refresh the results of the query. This works if the results are rendered to an Excel spreadsheet or Power Pivot Data Model.

image 

As long as the data source and types of transforms support it Query Folding will still be utilized with this method. If you’re  curious about what Query folding is read more about it in this Matt Masson post.

I’ve made this example available if you would like to download it: User Driven Parameter Example.xlsx

Preparing a Technical Session Part 3: Writing an Abstract

So you’ve come up with a great idea for a session and now you have to translate that idea into a thorough abstract that helps both organizers and attendees understand what to expect from your session. This can make the difference between you getting a session at your favorite conference or not so don’t take this step lightly.

In this post I’ll walk you through some tips for writing a session abstract. Now there’s still no guaranteeing your session will get picked because there’s a lot of factors that go into a conference selection but I hope these tips will make your submission somewhat less stressful. Just as a reminder in this blog series on preparing a technical session I’ll cover the following steps:

  1. Picking a Topic
  2. Coming up with a Title
  3. Writing an Abstract
  4. Building the PowerPoint
  5. Building the Demos
  6. Delivering the Presentation

Writing the Abstract

Think of your session abstract as the appetizer to your meal. If you have a bad appetizer then you probably don’t have high hopes for the upcoming meal. The same is true about a session abstract. If an abstract is not put together well then it’s hard for the consumer (event organizers and attendees) to imagine a great session. Having said that let’s look at a few tips to make a great “appetizer” or abstract.

Grab my Attention

How is your session different than everyone else’s?  Why would someone reviewing hundreds of abstracts pick your session?  Do you think your topic is just that original?  In most large conferences you are bound to pick a topic that someone else has also submitted so what sets yours apart from the others?  Don’t let these questions haunt you but genuinely use them as a tool to ensure your session grabs the attention of the abstract reviewer.

Try to hook the reader in with the very first sentence.  This can be done by starting with a bold statement.  For example, you could say, “Stop struggling to tune your nightly data load when you could be running 60% faster”.  With a statement like that you’ve got my attention and I’m curious how you are going to accomplish such a feat.

Make the Audience Clear

It should be very clear who your session’s target audience is.  If you need to be straight forward and call out who the session is for in the abstract then do it!  You will likely have a large audience of unhappy people if the expectation was given that attendees would learn advanced query tuning strategies but your entire presentation is in Excel.  So help your audience understand that your session is for them.

Get to the Point

You don’t have much time to draw the reader in so don’t waste they’re valuable time explaining the details of what page latches are.  That’s what your session will do!  Instead of going through everything you’ll cover in the session do these things instead:

  1. State the problem (ideally in a way that hooks the reader in)
  2. Describe why it’s important or why the attendee should care
  3. Without getting into the details describe how you’re going to solve the problem

Be Specific

Yes, I know I just said don’t get into the details but that’s different than being specific. Giving the details would mean you write a 3 page abstract.  While, being specific means you’re clear on how you’re solving the problem. For example, don’t say you are going to create a BI solution to solve the problem.  Instead, say you’re going to create a BI solution that uses the following tools in this way.  You should also avoid acronyms that aren’t obvious to every attendee at the conference.

Explain the End Goal

Your abstract should tell the attendee what they will have learned after watching your talk.  For example, you may say “By the end of the session you will have learned how to load a Data Warehouse using SSIS”.  This gives attendees a clear idea of what they are getting by attending the session.  If I already know how to load a Data Warehouse then I know this session is not for me and I won’t be disappointed by it.

Read, Reread and Have Someone Else Read it

If you’ve decided to submit a session to a major conference I know you’re very passionate about the topic.  Unfortunately, the most common reason why sessions are not accepted is because despite your passion you don’t save time to review your abstract before submitting it.  Common spelling mistakes and grammar errors can cause what would likely be a great session unusable to a committee in charge on reviewing sessions.  So do yourself a favor and after you write an abstract read it, then reread it, and then have someone else read it because often we read a sentence how we want it to sound rather than what it actually says.

Other Good References

If you have other references please share them and I’ll add them to this list.

Preparing a Technical Session Part 2: Coming up with a Title

Earlier this month I began a blog series on preparing to do a technical talk. In that first post I discussed some strategies for coming up with a topic, which is ultimately the first step in your preparation.

In this post I’ll walk you through some tips for coming up with a title for your presentation. Just as a reminder in this blog series on preparing a technical session I’ll cover the following steps:

  1. Picking a Topic
  2. Coming up with a Title
  3. Writing an Abstract
  4. Building the PowerPoint
  5. Building the Demos
  6. Delivering the Presentation

Coming up with a Title

You may be thinking once you’ve come up with a topic you’ve got the title nailed down too but I actually see these are two different things. The topic is the general idea of what you’ll be talking about.  This gives you a guide one how you’ll work out the details. The title is one of those details. For example, my topic might be “Intro to SSIS” but I make the title “Getting Started with Integration Services”. This title is very clear on what the topic is and what depth the audience can expect.

Why is it important to have a good title? Well let’s be honest many people attending conferences probably only look at a small version of the schedule that doesn’t include the detail abstract so the title is all they have to go off. If your title is not clear on what you’ll be discussing then why would someone attend.  Let’s go a little deeper and look at some tips you may want to use when naming a session.

Don’t be too Cute

Do you consider yourself a creative person? Do you like to be unique and stand out in a pack? Good, now stop it! I’m kidding to some extent. You want your session to stand out and if the only thing people have to go by is the title than you may want it to be a little more creative or zany with your session title. There’s nothing wrong with this as long as it’s still very clear what your session is about. Here’s a couple good examples of being creative with your session title while still being clear and interesting:

  • Help! I’m a new DBA, Where do I start?!
  • DBA Mythbusters
  • Triggers: Born Evil or Misunderstood?

These sessions stand out but are still very clear what the talk will be about. On the opposite end of the spectrum here some session titles that are certainly unique but I have no idea what to expect if I were to attend.

  • SQL Server: We’re not in Kansas Anymore
  • Kill “BI”ll Quentin Tarantino Style

These sessions tried too hard to be cute and went past being unique to just being confusing.

Short and Sweet

I’ve often made the mistake of wanting to be so clear about my session that my title starts to look more like a poorly written paragraph. The intent is to eliminate confusion but what happens is without knowing it you begin talking people out of your session just by them reading the title. Here’s one I wish I could have back:

  • Using SQL Server 2014 to Build Analysis Service Multidimensional Cubes

Good topic, but a poorly written title. Way too long and complicated when it really didn’t need to be. If I could rewrite it I would simply name it “Building Analysis Services Cubes”. All of the other details that I decided to put in the title should have been saved for the abstract section. That way if someone was wondering which version of SQL Server I would be demonstrating then they could read the abstract to find out more.

Use Active Language

Using active language is a good method for making certain that your session topic is clear. This helps your potential audience know in many cases the kind of demos (if any) to expect. Words like “Building” or “Developing” tell your audience that they can expect demos. For example, if I went to a session titled “Developing Reporting Services Reports” I would expect demonstrations not PowerPoint slides that show me how to develop a reports. Here’s some examples of session using active language:

  • Building Dashboards with Your SalesForce Data
  • Overcoming Data Warehouse Design Challenges
  • Getting Started with Indexes

I don’t think it’s mandatory that you use this tip for every session you do but if you’re struggling for a title then this may help.

Getting Started with the Power BI Dashboards Public Preview

If you’re currently an Office 365 customer that is using Power BI sites then you probably found an early Christmas present this morning when you logged into your Power BI tenant.

Today Microsoft announced the Public Preview of Power BI Dashboards.  If you watched this PASS Summit Keynote (fast forward to the last 10 minutes) then you got an early glimpse of what the new tool will look like.

If you want to start using this preview today then here’s what you need:

  • Must be an Office 365 customer (Corrected)
  • Must have SharePoint online (Corrected)
  • Must have a Power BI tenant (Corrected)
  • Currently you must be a US customer

Getting Started

To get started you’ll first need to login to your Power BI tenant. Once you’ve logged in you’ll find in the top right the ability to launch the public preview by click “Try it now” as shown below.

image

Once this launches you may be prompted to sign in again. Once you’re in you will immediately see a sample dashboard called Retail Analysis Sample that gives you an idea of what you can do in the preview.

image

If you want to get started with your own dashboard you must first find the data you want to visualize.  Click Get Data on the left side of you screen to get started.

image

You can see the current data sources available to you on the left side of the screen.

image

Now if this seems like an incomplete list don’t worry. You actually have the ability to connect to any data source that you would typically pull in with Power Query by using the second option on the list called Power BI Designer File.

The Power BI Designer is a companion application for building Power BI Dashboards.  I’ll discuss more about this in a future post but for now just know that it’s a client tool that combines the capabilities of Power Query and Power View without the need for Office 2013 Professional Plus being installed.  Huge bonus for some eager to try Power BI but running non-Professional Plus versions of Excel.

Probably this simplest way for you to get started is by pointing the preview to an existing Power Pivot workbook you have. So in my example I’ll select Excel workbook and then click Connect.

I really love what you see next. My assumption would be that I would have to upload my workbook but fortunately that’s just one option.  You can also connect directly to your workbook if they are currently stored in either OneDrive or OneDrive for Business. I am a big OneDrive fan especially since Microsoft now allows Office 365 customers to have unlimited storage.

image

So I select OneDrive and after logging in to my account I see all my files available to select from. I pick the Power Pivot workbook I would like to build a dashboard on and then click Connect. Once connected you’ll be notified as shown below that you can start working on your dashboard.

image

You’ll find three groups of objects here: Datasets, Reports and Dashboards.

Datasets

Datasets are the connect to your model.  Selecting a dataset here will launch an empty Power View report with a connect to the dataset you selected.

Reports

These are basically like the new way of creating Power View reports. The development is completely done in the web browser and any visualizations you created here can be pinned as dashboard items. There’s a ton of new visualizations available here.  Again I’ll save that for a future blog post.

Dashboards

These are the completed dashboards that you’ve created.  Any Power View reporting object that you’ve pinned will appear here.

If you click on the new dataset that you just opened that will launch the new Power View reporting interface so you can explore your data. If you’ve worked in Power View before you’ll find there is not a huge learning gap for you. Here’s what the interface looks like.

image

You still select the fields you want to visualize but now the visualization options have been much improved.  Here’s a quick preview of a couple of the new types of visualizations

Funnel

image

Tree Map

image

Filled Map

image

Gauge

image

In addition to these you can also do combo charts!  Huge win with that one.  The dataset I used just didn’t have data to support showing that one to you.

If you want to pin one of these visualizations to a dashboard you simple hover over it and select pin to dashboard as shown below.

image

There are some things I really like about the new filtering capabilities but again I’ll save that for a future post. When your happy with the report click Save in the top ribbon and then you can access it again later.

Once you’ve saved you can return back to the main Power BI Dashboard pane and select the workbook that you uploaded under the Dashboard section.

When your dashboard opens you’ll notice two things are already present.  The first is Power BI Q&A is automatically available in every dashboard at the top.  The second is a connection to your workbook you uploaded is embedded into your dashboard.  This can be removed later but give you a quick way to launch Power View when you select it.

image

The way to create dashboard items is to create Power View reports and then pin the items (as shown earlier) you want to the dashboard. You can have different Power View reports all pin to a single dashboard.  Any items pinned on the dashboard can be selected to drillthrough to the Power View report it was based on. The Power View reports then serves as the detail view of the data and the dashboard is the high level overview of the data.

You can also pin the results of a Power BI Q&A natural language query.  If you use the Q&A search at the top of the dashboard you can pin the results to the dashboard as well.

image

This also allows you to pin visualizations of a simple number you want to monitor as shown below.

image

My final dashboard looks something like this. Using a combination of Power BI Q&A pinned results and Power View pinned visualizations.

image

Overall I love what I’m seeing with the new Power BI Dashboard capabilities and can’t wait to see how these reports are surfaced through things like mobile devices.

I’ll point out too I did find a few quirks here and there but just note it’s in preview still these things will be fixed before the official release I’m sure.

Preparing a Technical Session Part 1: Picking a Topic

So you’ve decided or perhaps were told to do a technical presentation. If this is something that’s new for you then you may be going through a variety of emotions. You may start excited in anticipation of the event but quickly that changes to anxiety when you realize all the work that’s ahead of you.

Delivering a presentation regardless of the subject matter can be a challenge. Even if you’re a seasoned speaker there’s several steps that lead to you completing a successful presentation.

In this blog series on preparing a technical session I’ll cover the following steps:

  1. Picking a Topic
  2. Coming up with a Title
  3. Writing an Abstract
  4. Building the PowerPoint
  5. Building the Demos
  6. Delivering the Presentation

While discussing these I’ll be sharing not only how I personally go through this process but also feedback I’ve gathered from peers. The good news is with more experience these steps will likely flow more naturally for you and with hopefully less stress.

My goal is to help guide those that are new to presenting through the process, help them understand what to expect and hopefully help grow a larger pool of speakers at events. 

Picking a Topic

When you submit to a major conference deciding the topic to focus on is your first step to getting started. This step is clearly critical because the idea that you come up with will impact the rest of your preparation. If you’re in need of a topic and have the equivalent to “writer’s block” then here are some tips to help you’re brainstorming process.

Talk About Your Passion

Have you ever been assigned a presentation topic that you’re not really passionate about? This may happen more in a corporate environment when you’re given a topic that just has to be covered with co-workers. When you’re not excited about a topic then it can often show in your preparation and delivery of the content.

If you are passionate about a topic then more likely write a more compelling abstract, be more proactive about content development and even deliver the information in a way that connects better with the audience. Now keep in mind some people may not be passionate about the same things you are so try not to be offended when others don’t share your excitement about a topic.

Present On What You Know

This seems obvious but you’d be surprised how many new speakers pick topics that are completely out of their comfort zone. If you’re a new presenter then this whole process may be foreign to you already so don’t add any extra pressure on your self to learn a completely new topic. Now, having said that I do see some experienced presenters occasionally pick topics that maybe aren’t necessarily completely new to them but are certainly going to challenge them to learn a few new skills. So in short if you’re new at this pick a topic you know well and if you’ve been doing this for a number of years then do what works for you!

Use Things You’ve Done At Work

Give yourself some credit. You’re smart and pretty good at what you do! I bet you’ve come up with some pretty inventive ways of solving problems while at work. Why not share some of the design patterns you’ve used to help others?

Don’t worry I’m not suggesting that do anything that would hurt your company and potentially cause you to lose your job. I bet any problem you experience at work are the same kind of problems that others are experiencing. Why can’t you take your solution and generalize the details, including the data, so it shouldn’t matter what your place of business is. The other benefit is these topics are often the most popular because they’re based on real world problem solving. My number one goal when I attend a session is to figure out how I can use what I just learned when I get back to the office. What better way to solve that then by showing problems you’ve actually solved at work.

Is Anyone Else Interested?

If you’re debating whether or not a topic would get much interest then ask? Take to social media with a poll of topics you’re thinking about presenting on and see what people like best. Not only are you getting valuable information back but you’re also doing a little early promoting for your session.

Journal Topic Ideas

Ideas can come at any moment. If you’re not prepared than you could have a stroke of brilliance and before you know if you’ve forgotten it. Be prepared and keep a pen and paper handy, or if you’re living in this decade sign up for Evernote or OneNote and log your topic ideas in a digital journal.

PASS Summit 2014 in Review

I’m finally completely recovered from a great week in Seattle last week for PASS Summit.  Now that it’s back to the grind of regular work I thought I’d put together some thoughts and tell you about my experiences from the week. 

You may have read others blogs about their experiences during the conference.  I always love how everyone may do very different things while at this conference but still have a great time!

My week started early as I arrived in Seattle on Saturday to prepare for MVP Summit that was going on at the same time.  There were some mixed opinions about these events going on at the same time but I liked it.  I think this helped many that are from out of the country justify coming to both events in a single trip rather then making two expensive and time consuming trips to Seattle.  MVP Summit started Sunday for SQL Server MVPs and ended a bit early for me, on Monday, because I delivered a Pre-Con on Tuesday.  Once our day in Redmond was done on Monday I went and registered for the PASS Summit in Seattle and called it an early night, knowing that I had a full day of talking during my Pre-Con coming up the next day.

Tuesday

This was a very full day as Brian Knight (Blog | Twitter) and myself taught a full day of SSIS: Problem, Design, Solution in our Pre-Con.  The Pre-Con was well attended with about 140 attendees.  I really enjoyed teaching and even attending sessions like these that focus on solving problems.  There’s many reasons why I attend PASS Summit but one of the biggest is that I want to learn things that I can immediately go use to help me solve problems when I get back to work.  I think we accomplished that with this session.  It’s was very focused on problems and different ways to solve them using SSIS.

Brian and I have done many presentations together over time but we’re always learning still.  One of the things we learned from feedback last year was to provide a PowerPoint deck with a little more substance.  PASS prints these decks for attendees to keep and take notes on.  While a single image slide with verbal discussion comes across great in a presentation it’s not effective when the slides are printed.  By the way, I still like the single image slide for presentations but not when you know the slides are printed for attendees.

Feedback on the session this year seemed to be pretty good but I’ll know for sure when the reviews are released.

Once the Pre-con was done I dropped my things off at the hotel and came back to the conference for the Welcome Reception.

Later that night I went to the Networking Party that was moved to Yard House.  I stayed and talked for a bit and meet some new people.  Unfortunately it looked like they weren’t ready to handle that volume of people at once so I ordered a meal from somewhere else to end my night.

Wednesday

On Wednesday I started my morning after breakfast by attending the Keynote feature T.K. “Ranga” Rengarajan, James Phillips, and Joseph Sirosh from Microsoft.  Definitely some interesting things announced during the this Keynote including Power BI Dashboard Preview coming soon.  You can watch the Keynote still on PASStv

Wednesday I had to deliver my session towards the end of the day so I wasn’t able to attend as many sessions as I would have liked, as I did some practice runs of my session. I did however attend Bradley Balls (Blog | Twitter) session on Using PowerShell to Manage Cloud Integrated Data Platforms. Brad always does a great job and shows his passion for the technology.  He showed many of the capabilities that PowerShell has for integrating into Azure.

Towards the end of the conference day I sat in the session that was prior to mine because I was a bit worried that there would be a lot of cross over with my topic because it was Power View related too.  Luckily there was very little that was the same.  I did my session on Creating and End to End Reporting Solution with Power View.  I think it went pretty well, even with Excel crashing on me.  I always have a back up ready :).  There was a lot of interest in Power View with SharePoint and how Multidimensional cubes interact with Power View so I saved some time towards the end for that discussion.

Immediately after my session I ran down to the Expo Hall for the Exhibitor Party where I was doing a book signing for Pragmatic Works.  We do this every year and it’s always a blast to meet so many people and give away free books!

Here’s a video Jeremy from our team who took showing the line that built up.

After the Exhibitor Party Pragmatic Works hosted our annual karaoke party at the Hard Rock with a live band.  I always have fun here and was able to catch up with friends.

B1vFDH2CAAIRg_u (1) 

Thursday

I started my day by attending the Keynote by Dr Rimma Nehme. This was possibly one of the best Keynotes I’ve attended.  Her explanation of cloud computing was done in a way that I think my sales team would be comfortable with.  You can watch the Keynote still on PASStv.

You can watch the Keynote here or read Steve Jones’ (Blog | Twitter) summary here.

In between networking with a few new folks I also attended two session: The BI Power Hour and Top Five Power Query M Functions That You Don’t Know.

The BI Power Hour is always a fun “infotainment” type session with many of the members of the Power BI product team.

I also really enjoyed the Top Five Power Query M Functions session.  I’ve always really liked Power Query but Reza Rad (Blog | Twitter) showed me a few new tricks!

I ended my day by attending the PASS Community Appreciation Party at the EMP (Experience Music Project) Museum.  This place is always fun and of course they had live band lead karaoke.

Friday

This was my day to work the Pragmatic Works booth so my morning was eaten up by that. To end the day I sat in on the Speaker Idol competition. The winner of the last round would be guaranteed a session at PASS Summit 2015. I was curious to watch this because we do a version of this locally for the user group in Jacksonville. Here’s Jason Carter (Blog | Twitter) doing his presentation

Embedded image permalink

I had a late flight home and actually didn’t end up back in Jacksonville until 11 AM Saturday morning… Long day!

If you’ve never been to a PASS Summit before I highly recommend it.  It is an event run by the community, which makes it unique.  While there are sponsorships and partnerships it’s community run so you hear from and meet a lot of people that are going through the same things you are.  Next years PASS Summit will be Oct 27-30. I hope to see you there!

Creating a Reporting Services Subscription

Subscriptions are a great feature in Reporting Services that will run a report unattended and deliver it to users either by email or Windows File Share. Subscriptions can also be scheduled so if your end users need a report monthly you can automate the delivery process. The steps given in this article will show you how to configure subscriptions using the Windows File Share delivery method.

Before you start to configure your first subscription make sure to have the following taken care of first:

Start the SQL Server Agent

When you installed Reporting Services it also created the ReportServer database. This database stores all subscription related data among other things. Ensure the SQL Server Agent is running on the database engine that has the ReportServer database.

To start the SQL Server Agent you can either login to the database engine in SQL Server Management Studio or open the SQL Server Configuration Manager then right-click and select start on the SQL Server Agent.

SQL Server Configuration Manager SQL Server Management Studio
1 2

Enabling sharing on folder delivering to

If you have decided to deliver reports via Windows File Share then you must configure the folder that will accept the reports for sharing. Navigate to the folder that you want to be used with the Subscription and right-click on it then select Properties. Go to the Sharing property tab to enable sharing.

3

Use SQL Server Authentication on data sources used in report

To enable Subscriptions on a report you must have a SQL Server Authenticated account used to access the data sources. In SQL Server Management Studio, create a new SQL login that has db_datareader access to the databases used in the report data sources.

4

After you have created this account change the report data sources to make sure it is being used in the Report Manager.

5

Now that these prerequisites have been taken care you can actually create a new Subscription. Click on the report that you want to add a Subscription to and click New Subscription in the report toolbar. If you do not see the New Subscription option you may need to have your privileges elevated.

6

There are several options you have when creating a Subscription:

Delivered by – Can either be Windows File Share or Email. If you have not setup your SMTP server to send email then your only option is Window File Share. The SMTP configuration is setup in the Reporting Services Configuration Manager.

File Name – What you want the file that is created to be called. Also, there is a checkbox for “Add a file extension when the file is created” which is checked by default. There may be some special circumstances where you do not want the file extension, like your work email does not accept attachments with an .xml extension.

Path – The file path where the file should be sent to. It must be written as the full UNC path (\\ServerName\FolderName). For example, my path would be \\Devin-PC\Reports.

Render Format – How you want the format to be rendered. Your options are XML file with report data, CSV (comma delimited), Acrobat (PDF) file, HTML 4.0, MHTML (web archive), Excel, RPL renderer, TIFF file, or Word.

Credentials used to access the file share – This must be a login that you setup to have access the file share in the eariler prerequisites.

Overwrite options – Overwrite an existing file with a newer version is set by default and will always replace a file if they are named the same. Do not overwrite the file if a previous version exists will keep the original file and cause the subscription to fail. Increment file names as newer versions are added will continue to add a new file if one already exists with the same name but will increment it (ReportName_1.pdf, ReportName_2.pdf, ReportName_3.pdf).

7

You can also click Select Schedule to design a set time for the Subsciption to run. You can also configure when you want the schedule to start and how long it should continue. Once you have set the schedule you want click OK.

8

This will return you to the previous screen where can lastly decide to pass in different values to the report parameters before you finalize the Subsciption by clicking OK.

On the Subscription tab of the report you should see listed all the Subscriptions that have been created for this report.

9

When the Subscription runs you should see the file now in your shared folder. If you open Management Studio and take a look at the SQL Agent jobs you will see there is a new one for the Subscription you created. Here you can run the Subscription manually so you do not have to wait for the scheduled time.

10

WARNING: Do not change any settings of this job. Even just changing the name could cause the Subscription to fail.

Follow

Get every new post delivered to your Inbox.

Join 39 other followers