How Microsoft Forms, Flow and Power BI can reshape the classroom

image

If you read my last blogpost, you know I have two kids.  One is in middle school, while the other is in elementary school.  While certain things have gotten more modern for them compared to my time in school, many parts of their day to day activities have not.  For example, it always surprises me that my daughter is issued a laptop for the school year, but a large part of her testing is still done on paper.  The teacher then puts the scores in online, and if we want to see them, we have to remember to go and check a different website.  Sometimes the original test paper is never even returned.  It makes it hard to take action and help her focus her studies for the next test.

This is a major reason I was so happy to see Microsoft Forms released recently.  It allows teachers to make quizzes where students can answer questions and see right away what they got right/wrong and their score.   It’s incredible simple to get started –

1. Click “New Quiz”

image

2. Enter a Title (or optionally, a picture) and click “Add question” and which type of question

image

3. Enter the question details, marking things like which is the correct answer, whether it is required, etc.  Repeat until you’ve done creating questions.

image

4. Share it out to the students, or make a public link and share it with anyone!

image

If you’re thinking Google Forms does something similar, yes, I know.  But Google Forms doesn’t integrate with Microsoft Flow, while Microsoft Forms now does.  Now it’s possible to set up an e-mail alert to the parents/tutor/whomever and send the results when their son/daughter has finished the test.  Now before you scream “helicopter parent”, it’s important to keep this in context.  I wouldn’t expect to have the teacher do this for every test, or for every student.  But there are times where it could be quite valuable and a great way to drive action, vs. hoping that you go check/your kid tells you/etc.

To setup a new flow with Microsoft Flow, it’s really easy.  You open the Flow app from your Office 365 account and click “Create From Blank” –

image

Next, type “Forms” to see all the different items for Microsoft Forms you can trigger an action for –

image

After selecting that, I choose the form from the dropdown to create the Flow for –

image

I want to make it conditional based on the e-mail address entered for the first question.  I select “Add a condition” and the question I want to trigger the flow based on the student response –

image

Then I entered the condition parameter and value to match, and what to do if it matches –

image

I save the flow, and now I’ll get an e-mail when she finishes this quiz, along with her answers (if that is setup to be included).

image

This also allows me to do things like setup a Flow for inserting a new row in an Excel document when a student answers a question, etc.  That makes it easy to quickly analyze the results in Power BI and see the total points she got vs. total available points!

image

It took me less than a half an hour to create a quiz, create a flow, and view the results in Power BI.  I’m excited to see the potential there for students and teachers to take advantage of this.  Personally, I’m already thinking of some great new ways to I could see everyone use these tools and others from Microsoft to engage throughout the entire school year.

Thanks for reading!

Tiggee, Batman and Power BI Premium

WP_20130922_114

On the eve of the Microsoft Data Insights Summit, I thought I’d finally write the story behind the picture I’ve included with this post.  How does this story relate to the recent announcement around Power BI Premium?  Well, I’ll leave that up to you to decide if it does, or if it’s simply a cute little story that involves my son Matthew (who has been bugging me to write about him on here).

A couple years ago, my son asked his sister Caitlin and I to play Monopoly with him.   We happily agreed, and went downstairs to the family room.  But we didn’t see the usual setup.  Instead, we were greeted with what you see in the picture.

“What the heck is this?” his sister asked.  “This isn’t Monopoly!”

“I know – now it’s AWESOME Monopoly!” he replied.

“Awesome Monopoly?  That sounds stupid.  I don’t want to play that.”

“C’mon, please – it’ll be fun.”

“No it won’t – it looks dumb.  I’m going back upstairs.”  With that, she turned and stomped out of the room, taking Happy Bear with her.

I winced.  This had played out many times before, and the ending had always been the same.  I glanced towards the kitchen, seeing if the tissue box was still sitting on the counter from an earlier incident.  But it wasn’t needed – he just smiled, sat down and started re-arranging Batman to make room for me.

“She’ll be back,” he said.  “You’ll still play with me, won’t you dad?”

“Um, sure.  Awesome Monopoly sounds, um, awesome,” I said, not quite sure what I was in for.

He tried to explain the rules , and I’ll admit, they seemed pretty confusing the first time he explained them to me.  I don’t remember everything he laid out, but one rule he mentioned during this initial explanation was if you rolled a 12 and landed on “Chance”, Batman got put in jail for a turn OR you had to draw a playing card.

“Buddy, I gotta be honest – I don’t fully understand some of these rules you’ve added.” I told him.  “Do you think we could make it a little less confusing?”

“What do you mean?  We haven’t even started yet.”

“Yeah, I know, but some of these rules . . .”

I didn’t want to push too hard, but at the same time, I couldn’t imagine the game going well if the Elf on the Shelf remained the banker for the entire game.

“Okay, new rule!  We can always change the rules if we decide they’re dumb.”

“Really?” I asked.

“Yep – that’s why it’s Awesome Monopoly.  We can keep making it more and more awesome together!”

I shook my head and laughed.  “Sure, pal, that sounds fair.  I’ll let you go first.”

We went a few rounds, me laughing and him asking me each time someone went how he could make it even more “awesome”.

“I’ll admit, pal, this is a lot of fun.”

“Yay – I knew you’d like it!  Can you go tell Caitlin how much fun it is?”

“Sure.”

I nodded, left the room, and came back with her having agreed to play after hearing how it great it was (and Happy Bear even came back too).

“Caitlin’s back – woohoo!”  He proceeded to run around the room and make sounds like a choo-choo train.  “Yayyyy!”

The game played out, and any time someone thought of a way to make the game even more awesome, we talked about it, tweaked the rules and kept making the game more and more awesome!  (My kids used to like to say awesome A LOT).

So what’s the moral of the story?  It’s either –

1. Batman cheats

or

2. Power BI Premium = Awesome Monopoly.

or

3. Chris needs better blog topics

If you’re coming to Seattle for the conference, I’ll see you next week!

How to use SQL Server on Linux to host your Reporting Services catalog

image

Let me get this out of the way upfront before we get to the good stuff.

**This is not yet officially supported by Microsoft.  We will do an official post when it is**

There we go.

While you can’t run the front end of SQL Server Reporting Services on Linux, many folks would potentially like to host the backend catalogs on SQL Server on Linux.  I was wondering over the weekend if this worked quite yet, considering that SQL Server on Linux had just introduced SQL Server Agent support as of CTP 1.4.  So, thanks to Microsoft Azure and some spare time, I decided to give it a go.

First, I used the SQL Server vNext on Red Hat Enterprise Linux 7.2 image in Azure to setup my Linux VM.  This is the easiest way to get started by far, and there is a complete walkthrough how to set this up end to end here – https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-azure-virtual-machine

Search filter for SQL Server vNext VM images

I used PuTTY to connect to my Linux instance via the IP address, and I didn’t install the SQL Server Tools on the Linux box.

You’ll need to install the SQL Server Agent on the Linux box as well, which I did by following the steps here under the part titled “Install on RHEL” – https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-sql-agent.  Go ahead and do this right after you get SQL Server up and running and are still logged into the machine with PuTTY.

To confirm everything was up and running properly, I connected to the server using SQL Server Management Studio on my local PC.  Everything looked good so far.

image

Now, I had to setup my new SSRS instance on a separate machine.  I did this using an Windows Server 2016 Azure VM and simply installing the latest Technical Preview from January on it.  Next up, you need to set the database catalog location in the Reporting Services Configuration Manager.  Keep in mind, you’re limited to using SQL Server authentication to connect to the Report Server database in this scenario (and SQL Server on Linux in general right now).  Everything looked good until I got an error at the “Generating rights scripts” part of the config process for the new database.

I figured I was stuck here until I found this really old blog post from Adam Saxton about the error message I was getting.  The blog post itself wasn’t relevant (sorry Adam), but the very last comment in the thread WAS helpful from Carlos Shepardos.

“If you use a SQL alias to connect to the SQL Server server you have to ensure that the local computer is also able to resolve the SQL alias name via a DNS resolution request. If the local computer is not able to do this you get the error message shown above.

The easiest way to ensure the SQL alias name is resolvable to the IP address of the SQL Server is to create an A record entry in DNS or add a line to the local hosts file.”

So with that in mind, I went to my HOSTS file on the server and added an entry for my SQL Linux instance.  You can navigate the the HOSTS file on your RS server here – C:\Windows\System32\drivers\etc

image

I then used that name instead of my IP address for my SQL Server instance entry for Reporting Services, and the wizard finished without issue.

image

I navigated to my report portal, and it loaded just like you’d expect.

image

To test the SQL Server Agent, I created a simple report and dataset while also setting up some subscriptions and cache refresh plans.  Sure enough, they ran successfully and the jobs showed up as expected when I looked in SSMS as well!

image

As I mentioned earlier, this still isn’t officially supported quite yet, but I was able to use it without any issues in my (admittedly limited) testing.  Would love to hear about your experiences trying this scenario out as well.  Thanks for reading!

Use Analyze in Excel + Excel Camera to create PowerPoint magic

image

So this blog post falls into the “I just think this is cool” bucket.  Did you know Excel had camera functionality?  Well, I didn’t.  And this despite the functionality being there since 2003(!).  This isn’t there by default, but if you turn it on in your Quick Access Toolbar, you can take advantage of it.  What’s so special about this functionality in particular?

Basically, it allows you to take a picture of a collection of cells in your workbook.  I know, amazing, right?  Stay with me here – it’s a picture, but it’s a LIVE picture.  Here’s a simple example using Excel 2016:

My table in my Excel workbook looks like this –
image

If I highlight those cells and use the camera tool, I can do the following in my workbook (I normally wouldn’t make the picture this big, but wanted to emphasize it was a picture and not just a linked table) –

image

Now I’ll change the numbers to text.  When I do that, the picture updates automatically as well –

image

And since it is a picture, I can do all the normal things I could do to a picture in terms of formatting –

image

So could I do something like this with a Pivot Table?  You bet, including one using “Analyze in Excel” in Power BI for the data!  I tried this myself against a sample report I loaded in PowerBI.com.  I chose “Analyze in Excel” from the ellipsis –

image

Then created my Pivot Table against the data and used the camera tool as I did in my earlier example.

image

Any change I made in the Pivot Table is reflected in the picture –

image

This is nice, but what I really want is this live picture in a PowerPoint deck that gets updated when my data is updated in Excel.  Let’s give that a try.

After I’ve selected the range in Excel with my camera tool, in PowerPoint I can choose “Paste Special” and select “Paste Link” so I can paste it as a “Microsoft Excel Worksheet Object”.  This will allow the data to updated dynamically whenever I have new data in my workbook. (You can also add Pivot Charts to your PowerPoint presentations via ‘Paste Link’, and the data will also update dynamically for those as well!)

image

For example, if I change the “AccountCount” to say “SSRS Rules!”, it changes dynamically.

image

I can also add a hyperlink to the picture back to the original report in Power BI if I wanted to jump there quickly during a presentation to do additional analysis on the data.

image

Something to keep in mind – if you share the PowerPoint deck with a user who doesn’t have access to the original Excel workbook, they can still open and use the presentation with the static images reflecting the last time the data was updated.  I think this is valuable, since I know often the workflow at some companies is basically “Hey so and so, I need that slide deck for the meeting tomorrow.  Can you update the slides from the previous meeting and send it to me?”

Thanks as always for reading  – maybe you already knew this trick, but if not, hopefully it’ll save you some time in the future!

Using URL parameters with Mobile Reports in Reporting Services

image

One area I’ve found folks looking for additional information recently has been how to use URL parameters with Mobile Reports in SQL Server 2016 Reporting Services.  This was a fairly popular feature in Datazen, so naturally people want to use it in SQL Server 2016 as well.

Quick background for folks who don’t know what URL parameters are in the context of mobile reports.  You can create a mobile report with parameters in SSRS 2016 so you and your report readers can filter your reports.  This is helpful when you have a large dataset, for example, so not all the records are loaded at once (If you aren’t familiar with how to write a shared dataset query with parameters in Reporting Services, you can learn more about that at this link – https://msdn.microsoft.com/en-us/library/dd239345.aspx).  Sometimes, you’ll want to provide those parameters as part of the URL for the report.  This can be useful if the report is a drill-through target from another report you’re viewing, for example.

First off, there is support for this feature.  You can find documentation on MSDN for it here – https://msdn.microsoft.com/en-us/library/mt772301.aspx

The article, however, only covers the basic scenario around using a dataset parameter.  Dataset parameters are passed on to the shared dataset that they were specified for when you created the mobile report. The parameter name must be specified as:

<Dataset Name>.<Parameter Name>

Below is a sample URL for supplying a value for the @Reqtype parameter of for an ExecutionLogs dataset:

http://rsserver/Reports/mobilereport/ExecutionLogs%20with%20Filter?ExecutionLogs.@Reqtype=Subscription

There are also parameters that are tied to the selection state of the control in the mobile report it was specified for.  These can be thought of as selection control parameters.  An easy way to find the name of the selection control to use in your URL is on the “Set drillthrough URL” screen in Mobile Report Publisher.

image

Like dataset parameters, the parameter name must specified in the following manner:

<Control Name>.SelectedItem
<Control Name>.SelectedItems

Below are sample URLs for specifying these:

http://rserver/Reports/mobilereport/ExecutionLogs%20with%20Filter?SelectionList.SelectedItem=Subscription

http://rsserver/Reports/mobilereport/ExecutionLogs%20Multi?SelectionList.SelectedItems=Subscription,Interactive

A special type of selection control in Mobile Reports is the Time Navigator control.  The time navigator control supports the following properties you can set in your URL –

– SelectedStartTime
– SelectedEndTime
– ViewportStartTime
– ViewportEndTime

The values for these properties are date time values formatted like this – “2017-02-15T00:00:00”.

Please Note: the “:” character needs to be URL encoded as “%3A” (browsers will usually do this automatically for you).

Now please read carefully the following item, because here’s where folks often run into trouble – if a user specifies a dataset parameter value via the URL and there is also a selection control wired up to supply values for the very same dataset parameter, a conflict occurs. The mobile report resolves this conflict by applying the dataset parameter value supplied via the URL (and discarding the initial selection control value). Note that this is true even as the user starts interacting with the report and chooses other selection values!

You can easily avoid this by supplying a selection control parameter via the URL instead of a dataset parameter.  The MSDN article is being updated to reflect this information as well, but keep this in mind when you are designing your mobile reports.

Finally, there is an item you can apply to your entire report via URL, and that’s to enable/disable showing the report title.

To disable the title, simply add “?title=false” to your URL, so it looks like the following –

http://rserver/reports/mobilereport/Claims%20Trends?title=false

If you want to show the title, just don’t add this to your URL.  Simple enough, right?

Hope this has been helpful for you and helped clear up a few things on this topic.  I want to give a special thanks to Andre M. from the engineering team for helping me with this blogpost.

How to insert a live Power BI report from SQL Server Reporting Services into a PowerPoint slide

image

Yay – the long awaited installable version of the Technical Preview of SQL Server Reporting Services is finally here.

Though very similar in functionality to what was first released on an Azure VM back in October, one item now officially supported that wasn’t previously is using https on your report server.  With this change, you can now use the Web Viewer add-in for PowerPoint 2013/2016 to surface live Power BI Reports from your server.  This is the same functionality already available for both paginated and mobile reports that I’ve walked through previously.  Here’s how you can try this functionality out as well with the Technical Preview –

1.  Go to the Insert Tab in PowerPoint.  You should see the Store Add-In in the ribbon.  Click it to open the App Store –
OfficeSnip
2.  A pop-up will open with the apps available in the store.  You can now run a search for the app you wish to use
image

Type Web Viewer into the search bar and hit Enter.  You see the Web Viewer app created by Microsoft, which is what we’ll use.

image

From here, you’ll need to enter the address for your Power BI report (and make sure you have https configured for your server!)  You need to use the “rs:embed=true” url pattern in the address, which we first introduced in SQL Server 2016.  Otherwise, you will see the following error –

When I use that address, it works just as one would expect in the preview –

image

You can also take advantage of row-level security in your report, which will flow through to the PowerPoint slide, by either using Kerberos or EffectiveUserName for your Analysis Services data source.

image

One suggestion is that you don’t use self-signed certificates with this feature – I have found the web viewer control doesn’t always work well with these (as anyone who saw my attempt to demo this at PASS can attest to.)

Thanks for reading and give this a try – it’s something we’ve heard from a lot of people wanting to take advantage of!

Why Mobile Reports and Power BI Reports aren’t a zero sum decision in SQL Server Reporting Services

There are two questions I’ve gotten for months.  And with the first public preview of Power BI reports in SQL Server Reporting Services, they’ve grown louder in the past week or so.

“Will we be able to view Power BI reports hosted in SSRS in the Power BI mobile app?” (Yes, we’re planning to support that scenario)

“If so, why should I still use mobile reports?” (Sigh)

I’ll admit, I understand one of the biggest driving factors around this question is not wanting to “bet on the wrong horse”.  And yes, a major reason people are concerned about doing that is because of, well, Microsoft and some decisions made at various points in the company’s history around certain products.  But I can only offer my opinion on this particular question and what I’d tell a customer if they asked me question two today.

Recently, Power BI added the ability to create mobile optimized layouts for reports.  But no one has been asking if they should still create mobile optimized dashboards now that they have this new functionality.  Why not?  Because they aren’t really an either/or proposition.  You have a dashboard to give you an overview of your business metrics, and then can dig into more detail by clicking a tile and jumping into a report.  In the context of SQL Server Reporting Services, it’s probably better to compare the mobile report use case to Power BI dashboards rather than Power BI Reports.

By doing so, it helps customers avoid the single biggest issue people run into today with mobile reports (and previously Datazen).  What’s the issue exactly?  Well, they’re trying to use it to build reports the same way they do with Power BI Desktop.  They want interactive reports that allow drag and drop, ad-hoc analysis and can handle hundreds of thousands of records in a data model they build on the fly.  They did this previously because they didn’t have an option to run Power BI reports on-prem.  Now that they will, that shouldn’t be an issue any longer and they can use the best tool for that purpose, which is Power BI Desktop.

Similarly, there are certain use cases today where it may be advantageous to build a mobile report as my “dashboard”, instead of just building it in Power BI Desktop directly.  Today, a few of these include –

– I need my mobile report to be available for my users offline and still be fully interactive.  Power BI reports are viewable offline, but have certain limitations.

– I like having the thumbnail view of my mobile reports on devices for my users to see a quick preview of what the report looks like, especially in combination with KPI’s I’ve created.

– I can add URL’s that link to other content from items on my mobile report, or add url parameters to pass with that as well.

– Some users have specifically told me they like the fact they can add content from different data sources to a mobile report without building another data model.  Why?  They can then filter across all of those data sources in a mobile report, which I know some folks have wanted to see in the dashboards in Power BI.

– I need to view the reports in a mobile phone browser vs. the app.  For on-prem customers, there are often additional challenges having users leverage native apps to view the content vs. a web browser on the device.  The Mobile Reports will look the same in a phone browser as they will in the mobile app, and for some customers, that’s pretty important.

Sure, there are other benefits I get from mobile reports as well, including the whole “design first” option.  But doing a “feature shootout” as of today misses the point – I see mobile reports as a complimentary item to Power BI Reports, just as they are a complimentary item to paginated reports.  They give users an additional way to meet their customer’s needs, and could play a huge part in a customer’s solution, or no role at all.  And since we’ve already stated that adding Power BI dashboards is not on the short term roadmap, I feel very comfortable telling users there is a lot of business value you can derive out of using both options with SQL Server Reporting Services for the foreseeable future.

Now, could that change down the road?  Could Power BI Desktop becomes the single tool used to create mobile and paginated reports in addition to what it does today?  After all, we stated in our blogpost last year that “We intend to standardize reporting content types across Microsoft on-premises, cloud and hybrid systems.”  Does that mean we could consolidate everything into one tool as well?  I guess, but there’s a pretty healthy debate you can have on whether its better to have one tool to do everything, or specialized tools for each report type.  Trust me, I’ve been in this debate many times already.  And it’ll really depend on what it always does – what do our customers think is the best option moving forward?

There you have it – one man’s opinion on the subject.  And I’m sure there are people who will read this and disagree completely.  Great – I wouldn’t have it any other way.  🙂

Feel free to let me know what you think in the blog comments, and have a great week!