LightSwitch – Christopher Finlan

SQL Server Reporting Services Grab Bag, Part 2 – Mobile Reports URL Parameters, Web Application Proxy, User-based parameters, Farewell, LightSwitch and PASS

Welcome back for part two of my “grab bag” series, where I attempt to cover multiple topics around SQL Server Reporting Services without boring you to tears. Let’s dive right in, shall we?

1. A topic that often comes up is “Can I pass parameters via URL to my mobile reports?” This answer is yes, however, there is something I wanted folks to be aware of when they use this functionality.

The linked article I referenced covers how you can do this with a dataset based on a SQL Server Analysis Services MDX query, but it doesn’t currently say that it only covers that scenario right now. We’re updating the article to show the difference for how the URL needs to be formatted for SSAS and SQL based data sources, which I’ll also cover here. In the following screenshot from that article, you’ll see the parameter we want to pass is for the Category column in the TimeChartLoD dataset. So depending on what the data source is, for step 4 I’d need to format it two different ways –

mobile-report-publisher-parameter-data-view

If it’s based on SQL Server, then it requires the @ sign before the column name, and would look like this.

http://sampleserver/reports/adventureworks-reports/adventureworks-load-on-demand?TimeChartLoD.@category=Clothing

If it’s based on Analysis Services, then no @ sign is required, and would look like this.

http://sampleserver/reports/adventureworks-reports/adventureworks-load-on-demand?TimeChartLoD.category=Clothing

That (hopefully) clears up the confusion on that point, and again, you’ll see that article updated with this additional detail in the next few days.

2. While we’re on the topic of parameters, one item that tends to trip folks up is around using user-based parameters to enforce row-level security. This usually happens because unlike other parameters for mobile reports, you don’t need to use a dropdown list in your mobile report to pass this parameter, nor does it need to be called out in a URL. I covered how this works in a blogpost earlier this year that may help with your questions on that.

3. Another question I get quite a bit is around how to setup an SSRS server to allow users access to their reports when they aren’t on the corporate network. There are some blog posts out there that touch on this topic, including one done by Jaime Tarquino (who happens to sit right next to me in the office) that covers using Azure Active Directory Application Proxy to access your mobile reports and KPI’s with the native mobile apps.

Another option you should be aware of is using the Web Application Proxy for Windows Server (both 2012 and 2016) for this purpose. This enables “reverse proxy functionality for web applications inside your corporate network to allow users on any device to access your web applications from outside the corporate network.” I can confirm that this does work for SQL Server Reporting Services scenarios currently that require “browser only” access. While this isn’t currently supported by the native mobile apps (because you need to authenticate using ADFS), I think it’s fair to say it’s something we’re actively looking at supporting as well.

Image result for visual studio lightswitch

4. I wanted to take a moment to call out the recent blog post by the Visual Studio LightSwitch team where they announce that LightSwitch will no longer be in future releases of Visual Studio. For those of you following this blog, you know I loved that product and used it extensively in my day to day work before coming to Microsoft. I am genuinely sad to see it go, especially since it was responsible for me even being hired by Microsoft in the first place (that story is an entire blogpost of its own). That being said, I have complete confidence in the team working on PowerApps to create a worthy (and superior) successor to LightSwitch. In fact, I’d argue it will offer a far better option for many users who were interested in in tools like LightSwitch but were intimidated by the idea of using Visual Studio to build LOB applications (I saw this firsthand quite a bit).

5. Finally, I’ll be co-presenting at SQL PASS this week along with Riccardo Muti around what’s new and what’s coming in Reporting Services. In fact, I might even be demoing some functionality I know many of you are quite interested in. I highly recommend attending this session if you’re a fan of SQL Server Reporting Services and want to hear (and see) more about what’s coming next.

See you at PASS and have a great weekend!

Throwback Thursday Fun – Use a Visual Studio LightSwitch App to enable search/auto-complete for large selection lists in Datazen

This is an admittedly goofy post, but maybe it points some folks in the right direction. One question that comes up is around how can you better manage a large lookup list in Datazen. Currently, the product doesn’t allow search functionality in the dropdown list. There is a way to work around this, however. Previously, I posted about how to set default selection values in Datazen dashboards viewed in a browser. The next logical step here would be to build a custom app and pass the values to that url. I did just that, and I built the app in Silverlight!

Why did I do it in Silverlight? Well, I figured for Throwback Thursday (and because it’s admittedly a hacky solution) I’d kick it old school and build a C# LightSwitch app. And since I realize most folks aren’t looking to do Silverlight development these days, I won’t bore you with the normal code review and walkthrough. Instead, I decided to post the app I threw together up in Azure so you can play around with it and see if inspires you (or makes you smile. or weep). Here’s what you do to give it a whirl –

1. Go to https://datazentbtapp.azurewebsites.net/desktopclient where you’ll be prompted to install the app locally on your machine. There’s no security on it or anything (and I didn’t bother signing the .xap file).

2. You’ll see the little DatazenTBTApp logo on your desktop if you choose that option. Double-click on it to open the desktop app.

3. The app will open and you’ll see the only screen in the app –

4. The dropdown list on the left contains over 2500 records to choose from, so it’s not something you can easily scroll through. No problem – just type the first few letters in the auto-complete combobox in the app. You’ll jump to the results that match what you’re typing. I’ll start by typing “MGS” – I quickly jump to the matching results that start with MGS, and my dashboard updates automatically to give me the proper result –

That’s basically it – feel free to try typing in some different letters/words (obviously Fabrikam and Contoso are well represented in this product list), watch your dashboard update, and relive the magical year of 2010!

(And yes, I might do one in HTML5/JavaScript in a follow-up post sometime.)

How to use Visual Studio LightSwitch oData feeds with Datazen and Azure

In the most painfully overdue blog post ever, considering my love of all things LightSwitch and Datazen, I finally sat down and consumed an oData feed I generated from a LightSwitch project I published to Microsoft Azure to use in a Datazen dashboard. It’s really pretty simple, and I’m kicking myself for not doing it before. Anyways, here’s how you do it –

1. If you don’t have the foggiest idea what Visual Studio LightSwitch is, you can read more about it here. It’s a fantastic tool for building rapid line-of-business applications for the desktop and mobile web apps. Plus, it’s available in the community edition of Visual Studio 2013, which is free for individual developers!

2. You need an app you are going to deploy and use the data feed from. Beth Massi has an excellent walkthrough (including a link to a sample app you can use) of how you can deploy a LightSwitch App to Azure. It’s very easy to do, and this is a great option to make your app available for Datazen to consume.

3. Once published, LightSwitch exposes the different data sources as easily consumable data feeds via oData, which happens to be one of the data sources that Datazen consumes out of the box with either anonymous or basic authentication. You could also create a custom data provider that used more advanced authentication options. To learn more about what oData is, you can read about that here.

So I published a simple app and put in some sample data to use with my Datazen Server –

Now, how do I setup Datazen server to use this feed as an oData source? Well, this confused me the first time I did it, so let’s go through it –

Now add a new data connection –

This is where you might get tripped up. Unlike every other data source listed, you don’t actually enter the data connection string at this point. You give your data source a name, choose the authentication method (and enter your credentials), hit next to setup permissions for the feeds, then hit finish.

If you try and put in the URL as the data connection name, it doesn’t save it, so it looks like the oData data source doesn’t actually work. Once you’ve saved the data connection details, you’ll see a new folder appear in you folder/data connections list –

Now, you click the folder name, and click the new data view button to add your first oData feed.

Here, you can now enter the url in the OData view URL area that you wish to consume.

Hit next then success, the feed is there and working!

This gives you an easy way to visualize your data from your LOB app using your Datazen on your favorite mobile device. It also opens up a number of options around filtering your data prior to bringing it into Datazen using the entity set filter options with LightSwitch.

Thanks for reading!

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: