Just a short post today letting folks know I'll be attending MSFT TechEd in New Orleans from June 2nd - June 6th on behalf of SAP. If you'd like to chat at all about SAP/Microsoft interoperability in general or have any questions around potential scenarios with SAP and LightSwitch, feel free to drop me a note at cfinlan AT outlook dot com or just grab the bald guy with the SAP badge while he's wandering around. I'll be happy to chat with anyone and answer any questions you might have around these topics. Look forward to seeing you there!
06/01/2013
05/24/2013
Le Queue or How to automatically create parent-child relationships
So for a change, I thought I’d share a solution to a question I got around whether it was possible to create an org chart automatically from an Excel upload. It is, but it’s tricky since you can’t create a parent/child relationship during the upload process, since the parent records don’t actually exist until the record is saved. So here’s what I ended up putting together -
First, I put together a staging table where the OrgID and ParentID fields are string fields (you could use integer fields as well). The other fields were simply unique to the spreadsheet I was using as a baseline, so that information can be whatever number of additional fields you’d like to use
This staging table is what you upload the Excel file into, so I needed to do this project as a desktop app. I also tried to do it as a web app and upload a .csv file, but even though I could import the file, the next piece didn’t work for some reason.
Next, I created a table the Org Chart Data actually will live in going forward
I created a self-referencing relationship, since that is how I have to setup the parent child relationship.
Next up was creating the screen to upload the data into and create the org chart. I used an editable grid screen and added both entities -
I added an Import From Excel button which leverages the Office Integration Pack extension for LightSwitch. The code there is pretty simple -
partial void ImportFromExcel_Execute()
{ OfficeIntegration.Excel.Import(this.Staging_Tables);
}
Once the data is uploaded and saved to the database (and you may need to refresh the screen before proceeding), the moment of truth comes where you need to actually load the data into the Org Chart table and have the relationships get setup. To accomplish this, you’ll need to use the Queue functionality via code to allow the records to be processed one by one. This way, they can each be committed to the database as they are being pulled into the entity, since they need to exist as parent records before they can have child records assigned to them.
So for our Update Org Chart button code, we have the following -
partial void UpdateOrgChartData_Execute()
{
var orgChartData = this.DataWorkspace.ApplicationData.OrgChartDatas;
OrgChartData current;
Queue<Staging_Table> toBeProcessed = new Queue<Staging_Table>();
foreach (Staging_Table stage in Staging_Tables.Where
(x => x.ParentID == null))
{ toBeProcessed.Enqueue(stage);
}
while (toBeProcessed.Count > 0)
{ Staging_Table stage = toBeProcessed.Dequeue();
current = orgChartData.AddNew();
current.ParentID = stage.ParentID;
Current.OrgName = stage.OrgName;
current.OrgDesc = stage.OrgDescription;
current.OrgID = stage.OrgID;
current.Parent = orgChartData.Where
(x => x.OrgID == stage.ParentID).OrderBy(x => x.Id).FirstOrDefault();
//This is where we don't see the parent
this.DataWorkspace.ApplicationData.SaveChanges();
foreach (Staging_Table child in Staging_Tables.Where
(c => c.ParentID == stage.OrgID).OrderByDescending(c => c.OrgID))
{ toBeProcessed.Enqueue(child);
}
}
This queues up the records, pulls them in (starting with the top parent record), and moves through
the queue until all records have been pulled in, assigned a parent, and saved to the database.
When you’re finished, you can create another screen to view and edit the org chart in a much snazzier
view by using something like the Ultimate TreeView Control!
A special thank you goes to Karol Z. from the LightSwitch team for pointing me in the right direction. Sorry this article was less pithy than normal, but hey, it’s a long weekend and I have a chance to be more pithy next time. Thanks for reading!
05/02/2013
Using SAP Netweaver Gateway, SQL Azure and LightSwitch to create “actionable BI” Apps for Sharepoint on Office 365, an end-to-end walkthrough–Part 2
Alright, let’s skip the normal chit-chat and jump right in where we left off – remember, we wanted to accomplish the following items in this application. For the users, it was:
- Be able to monitor the sales pipeline from the SAP CRM system used by the sales team for their particular partner (SAP Data Source)
- See if the companies in these opportunities they’ve identified are scheduled for any campaigns or events they have scheduled, or create a new item based on current deals (SQL Azure Data source)
- Cross-reference this information against a list of potential existing customer references who already own the product identified in the sale (Sharepoint List Data)
- Add status updates on an ongoing basis to keep the manager informed around what’s happening with upcoming events and deals they relate to. (Application Data)
And for the manager, it was:
- See all the status updates entered by his team on a daily basis
- See the current sales pipeline for each partner and take an on-demand “snapshots” of this information each week after his weekly call (SAP Data Source and Application Data)
- Get an e-mail alert any time a new event has been added that is looking for funding (SQL Azure Data)
- See the closed deal information for each partner and what events or campaigns they did (if any) that helped close the most deals.
If you haven’t already done so, download the sample project here and follow the tutorial documentation available here. This will go through almost all of the items I am building in this application, so walking you through how to build them screen by screen again seems fairly silly. Let’s do this a little differently.
I will show you a very basic home screen to get you started. Let’s choose a Browse Screen, but not select any data source
Once you’ve done that, click the Add dropdown under the Rows Layout and Select “Add Button”
Change the name of the button to “Browse Partners” and click Okay. Then change the Width in Properties to “Stretch to Container”
Remember to actually set the screen as the Home screen by right-clicking on it in Solution Explorer and select “Set as Home Screen”
I’m going to run it now just to see what it looks like (obviously it won’t do anything because I haven’t built any screens or told it what to do when I tap the item)
Meh – like I said, it’s basic. If you’d like to take it to the next level, you could check out Jan Van der Haegen’s blog here and see a really cool looking home screen and some ideas on how to get there.
Now I’m going to do something a little unusual – I’m going to build a mediocre app on purpose, but still show the screens and the process. I’m doing this to hopefully highlight suggestions from the LightSwitch community in the comments on ways to improve the application. If people provide suggestions, I’ll redo it in the next couple weeks where I incorporate those ideas. I’m doing this because I think it’s important for folks who aren’t familiar with the LightSwitch community -
a. To see how helpful this community is if you ask for suggestions/help
b. To see if anyone is even reading this
So let’s run through the screens and see where you can find ways to improve the application -
Start at the Home Screen and go to “Browse Products” on top - one way to improve this screen is to secure the buttons that only the manager should have access to by using Beth Massi’s blogpost here.
We select the product we wish to look at additional information on by tapping on the partner name to bring up the pop-up selector
This brings us to the details screen where we can do each of the items we listed earlier for the team by using the buttons on the screen taskbar
One item was to be able to monitor the sales pipeline from the SAP CRM system used by the sales team for their particular partner (SAP Data Source) by clicking the View Current Pipeline button
See if the companies in these opportunities they’ve identified are scheduled for any campaigns or events they have scheduled by clicking the “View Upcoming Events” button
You’ll notice I’m using the grid from the beta version of the new Wijmo controls mentioned in Michael Washington’s blogpost from earlier this week. If I tap on a row in the grid, the list of participants shows in a pop-up window
I can see that Tom Thomas from AmazingPie is scheduled to attend. I’m also going to see if there are any customer references I can use to help close this deal by clicking on the “View Customer References” button
Cool, Mayor McCheese Restaurants are in the same industry and apparently a great reference (which is ironic, because he was a terrible mayor). I’ll provide my manager an update using the “New Status Update” button.
I also need to setup a new event to help close the other deal in the CRM Pipeline for Buddy Lee Pants. I can do that by clicking the Add New Events button I added under the View Upcoming Events tab
Once saved, an e-mail is kicked off the the manager with the information (used principles from Beth Massi’s blogpost on sending email in Office365)
So we’ve taken care of all the requirements for the users, and one of the items for the manager. If we jump back to the home page, we know the manager also wants to see the status updates on a daily basis, so we click the “Current Status Updates” button
Which shows me all the status updates entered today
Which I can tap on to see the full update details
I also needed a screen to allow the manager to view the current pipeline and take snapshots as needed of the information. I’ll use the snazzy Wijmo chart to view this -
I also used Michael Washington’s blogpost on “Full Control LightSwitch” to add the total amount at the top of the screen
I can take a snapshot using the information from the Matt Evans blogpost by adding a button to the screen where I can take the snapshot by simply calling a webform, looping through the data and then closing the page immediately (I really went quick and dirty here - there has to be about 10 different ways to do this more elegantly . . .)
To accomplish this, I added a new table to my Application Data to host these snapshots -
Then I added a couple buttons to the screen -
If you’re interested, the code to call a webform and close it immediately is the following for the Take Snapshot button code -
myapp.CurrentPipelineAmounts.TakeSnapshot_execute = function (screen) { window.open("/TakeSnapshot.aspx", false);
};
And the following code for the webform to close it immediately when completes the process Matt outlines -
ClientScript.RegisterStartupScript(typeof(Page),
"closePage", "<script type='text/JavaScript'>window.close();</script>"); }
We can then click the View Previous Snapshots button and see earlier snapshots of the information -
Then finally, we can view the closed deal information by using the fabulous OLAP control in the C1 suite -
As a grid and a chart (and it takes care of all the grouping and summing for me)
We can even change the parameters on the fly -
To get a more in-depth view -
and snappier chart
And since we didn’t build this system until recently, we don’t have any additional information to show around events that influenced the deals (but obviously will going forward).
Whew! That was a lot to run through in a short period of time. And since there was a blogpost literally three days ago on how to publish your LightSwitch app to Sharepoint, I think I can safely skip going over that in here for now.
So, to summarize the last two posts into a couple bullet points -
1. This app can be improved (like I said earlier), so please let me know what you’d do to make it better, and I’ll bring those suggestions in. Or quickly realize no one is reading these posts . . . (I mean, I didn’t even provide a screen where the manager can approve the budget amounts for the events from!).
2. If you don’t buy the ComponentOne controls when they become generally available in June, you’re crazy. I mean it. These are a must-have suite going forward for anyone using LightSwitch.
3. We built an app, from scratch, in a couple days that allowed us to gather our group’s information more effectively, improve how the team works, and allow folks to “take action” on what they’re seeing in their reports.
4. This app barely scratches the surface of what is possible going forward with SAP Netweaver Gateway and LightSwitch/Office 365, and I’m excited to be talking to customers and developers about this in more detail over the next few months.
Thanks for sticking with this through two LONG posts around this topic – my next post will be focused on how to create a parent/child relationship “on the fly”. Until then, toodles!
04/29/2013
Action Jackson or Using SAP Netweaver Gateway, SQL Azure and LightSwitch to create “actionable BI” Apps for Sharepoint on Office 365, an end-to-end walkthrough, part deux minus one
Good news, everyone. Not only is my basement finally clean (see my previous post if you are wondering why you would care), but LightSwitch star Michael Washington begged reluctantly agreed to cross-post all of my new items on his blog as well! So for everyone who is viewing this on Michael’s website and is used to his detailed and well-structured posts around LightSwitch, I apologize in advance.
This post, however, is one that I’ve been waiting for almost a year to write – this is the first post where I demonstrate pulling your SAP data into a LightSwitch app using Netweaver Gateway!
Back when I first started this series, I mentioned a few common scenarios I would address. Two of those scenarios were:
- I want to be able to run analysis and then take measurable action on it (commonly referred to as “Actionable BI”)
- I want to use my SAP data in conjunction with my group’s local data so I can better run my business
Since I’ve written that post, I’ve had the opportunity to speak with several different customers around what their current business needs are and how they could be addressed by Microsoft and SAP. To be honest, I thought the scenarios they’d most be interested in would be around Excel, and that’s why I started this series of posts focused on those topics. And while they found the new capabilities in Excel 2013 quite compelling, more folks seemed interested in a simple app I built in LightSwitch. This app did nothing more than take an SAP data source using Gateway and a Sharepoint List in Office 365, create a relationship between them, and allowed them to update their Sharepoint data in the context of that relationship. It didn’t require any updating of their SAP information at all.
Now for those of us who have been using LightSwitch for the last couple years, you’re probably chuckling because you realize that scenario is a snap to build in LightSwitch (it took me under thirty minutes to do the whole thing - from creation to loading the app into Office 365) assuming, of course, you can actually GET the SAP data you need. And let’s be honest – until Gateway came along, unless you company had (wisely) invested in Duet Enterprise or a third party solution (and there are some really nice ones out there), this would have been far more difficult to achieve, and certainly wouldn’t be something you can roll out in thirty minutes.
So how does that simple scenario tie into the “Actionable BI” scenario I’ve long teased I would get into? First of all, what exactly is “Actionable BI”? If you do a search on Google or Bing, you’ll see a number of different descriptions/definitions/interpretations. For me, I can tell you it boils down to something a Global VP said to me a couple years ago when I presented him with a fancy new dashboard I had toiled over for about a week and was sure he was going to love. Instead, I got the following response -
“Meh. So what?”
I couldn’t believe it. But I’d worked so hard on it! It was so pretty and had the latest numbers and –
“It doesn’t show me what we’re specifically doing to on this team around the numbers. I need a report that does that, or better yet, an application that ties all this crap together with the stuff the team does to run the business day in and day out instead of everyone keeping their own spreadsheets.”
Did he realize what a pain that would have been to try and do that back then? That information was scattered across god knows how many corporate systems, databases and spreadsheets – trying to pull that together to give him that report would have been a pain in the ass, let alone building an actual app that did all that.
But that conversation happened BEFORE LightSwitch was launched – now that I not only have “The Power of LightSwitch” (not to be confused with “The Power of Greyskull”), but also Office 365 and SAP Netweaver Gateway, so this is barely a challenge at this point. But just for fun, let’s go ahead and tackle this project in this blog post as our demo scenario for a new company I made up right this instant called, um, TBQ Software.
This new fake company is an SAP and Microsoft customer, and we just setup Office365 in the cloud. I have the latest version of Visual Studio installed on my Surface Pro PC with Windows 8 installed. And we only know the basics about LightSwitch – I’m doing it this way because almost no one I spoke to at these events or meetings had even heard of LightSwitch, so I’m trying to do this like someone who has been tasked with doing this at one of those locations and has never done this before.
Here’s the data we need to pull into our LightSwitch project -
- SAP CRM and Financial Data
- A master list of partner information we’re currently using in Sharepoint that we share with other teams
- A database hosted in SQL Azure that houses data from a small LOB system around events our team hosts we’re replacing with this new application
- an oData feed from the Azure data market
- a new database that gets deployed to an once we create the application to house some new LOB-specific information for this team
Once we’ve pulled all that in, this app is going to be used by a team supporting the sale of partner software solutions we resell. I’ve identified what they need to accomplish and report against, and which data source I believe the information comes from. The team needs to -
- Be able to monitor the sales pipeline from the SAP CRM system used by the sales team for their particular partner (SAP Data Source)
- See if the companies in these opportunities they’ve identified are scheduled for any campaigns or events they have scheduled, or create a new item based on current deals (SQL Azure Data source)
- Cross-reference this information against a list of potential existing customer references who already own the product identified in the sale (Sharepoint List Data)
- Add status updates on an ongoing basis to keep the manager informed around what’s happening with upcoming events and deals they relate to. (Application Data)
Their manager wants to –
- See all the status updates entered by his team on a daily basis
- See the current sales pipeline for each partner and take an on-demand “snapshots” of this information each week after his weekly call (SAP Data Source and Application Data)
- Get an e-mail alert any time a new event has been added that is looking for funding (SQL Azure Data)
- See the closed deal information for each partner and what events or campaigns they did (if any) that helped close the most deals.
We’ll use the HTML5 client, and we’ll need to deploy this to Office 365 as a Sharepoint App. Everything is setup with IT in terms of security and potential firewall issues, which is a major advantage of using Office 365, since that is far easier to achieve than it otherwise could be. Oh, and don’t forget, we’ll need a number of reports.
So let’s start with the data we need from the central SAP systems – we need to get the data out, but we DO NOT need to write anything back to the central systems. This is a read-only scenario – if this the case, consuming an SAP Netweaver Gateway feed inside of LightSwitch is very straightforward. Once the feed is setup on the SAP side (please refer to this post for more information around that), there is no special setup work in LightSwitch to consume that feed.
I can right-click on the Server folder and choose “Add a new data source”
Select “OData Service” from the options, and hit next
Enter the connection string information and check the “read-only” checkbox and your security information (FYI - security in general will be covered as its own topic in-depth at some point to talk about various scenarios, including SSO)
I can choose the entities I wish to bring down into my project and hit “Finish”. Now depending on what entities you’re trying to pull into the project, you may get some warning messages from LightSwitch at this point -
Let’s go through each of these – the first item lets you know that currently Gateway doesn’t enable paging inside of LightSwitch. Gateway DOES support paging – however, this screenshot is against a slightly earlier version of Gateway we use for testing that doesn’t support it. You shouldn’t get this error going forward.
The second item is around a specific SAP data type that isn’t supported. There are a couple SAP data types that aren’t currently supported in LightSwitch, including “Address” and “Media Element” (in fact LightSwitch doesn’t support Media Element from any oData source currently). These are simply ignored by LightSwitch if they are included currently.
The third item talks about a one-to-one relationship that’s being ignored. This could potentially be worked around on either the LightSwitch side or the SAP side, depending on what your business scenario demands. I don’t actually need to use those entities for now in this new application, so I can safely hit “Continue” at this point to finish bringing in the SAP data.
(NOTE: I’d like to suggest something – if all you’re doing is bringing in SAP data that you will
NEVER want to update back into the core SAP systems from this app you’re building, it might make a lot of sense to setup a Gateway feed from your SAP BW system (which you can do). That would generally eliminate some of these issues, and it should have the data already structured in a manner that would be the path of least resistance when bringing it into LightSwitch. You need a BW system (duh), and keep in mind it won’t allow you to perform full CRUD operations against the data source ever. But in this example, it’s certainly would meet my needs.)
Now that I’ve tied in my SAP data source – this includes pipeline information and closed deals.
This information will help us “take action” later. Think about what we’ve just done for a moment
– we’ve tied our SAP data into an app we’ll be hosting in Sharepoint without using Business Connectivity Services, special coding, third party tools, etc. IN A THREE-STEP WIZARD. For anyone who’s tried doing this in a pre-LightSwitch world, you appreciate just how incredible this ability can and will be going forward in scenarios like this one.
Next up is tying in the Sharepoint list we have sitting in our Office365 platform – unlike pulling in the data from Gateway, this is a pain. Why? First off, you have to have Sharepoint enabled in the LightSwitch project, like they do in this image I am shamelessly re-using from the blogpost I reference below
Once you do that, you are locked into deploying it as a Sharepoint App. Full stop. This is a big change from being able to consume an on-premise Sharepoint list with any LightSwitch app. Also, you can only debug in a developer site you setup – not the end of the world, but I’m guessing any list you’re pointing at isn’t sitting on that developer site, so it’s possible to run into a scenario where you want to debug the app but not actually SEE the data you’re pointing at while debugging because it sits in a different site collection and you run into permission issues (or you do what I did, which is debug it in my personal dev site, which has a different userid/password than where I want to deploy it. Ugh, painful). I used this nice walkthrough on how to attach to Office365 based Sharepoint lists from the LightSwitch team to help me work through any issues I had -
And while Sharepoint lists on Office365 are frustrating to work with for now using LightSwitch, I can still easily use Microsoft Access to do a number of things on the desktop I can’t easily do anymore with a LightSwitch project deployed as a Sharepoint app (interacting easily with the rest of the Office suite on a desktop, for example). I emphasize “I” because it’s easier for ME to do it this way – you could be better at using javascript to create a new Office App to bring the data in, or you just hate Access, or who knows.
Next up, we need to pull in a couple things from Azure. First off, pulling in an existing SQL Azure database is child’s play – you just connect to it like any other SQL data source.
See how simple that was? Easy peasy lemon squeasy! (Quick note – I know we mentioned earlier we have no issues with firewalls, but your company firewall around TCP/IP traffic could make this scenario significantly more challenging. If that’s the case, the easiest workaround would be to pull that into a seperate LightSwitch project and turn it into an oData service that you deploy to Azure. But I digress.)
The other item we’re going to pull in an Azure data feed we get from the Windows Azure Marketplace. We’re going to use the free datamarket app called “DateStream” to bring in a number of date/time entities via oData that will assist us with some reports we’re going to use later on. You’ll need to sign up for an account on the Azure Marketplace at no cost to take advantage of this. Once you’re registered, you’ll need to make you have your customer ID and Primary Account Key to use as your credentials from the LightSwitch Attach Data Source Wizard. You can enter the credentials in like you see below:
Alright – we’ve brought all four external data sources into our project.
Okay, so everything’s based off a partner and the products each has. There’s a master list of all the partners and their products that makes sense to add as a new table in the Application Database. Unfortunately, it only exists as an Excel right now that includes the partner name, product ID, and product name of each product the partner sells. Let’s clean that up a bit when we create the tables – we’ll create a partner_master and partner_product table and create a one-to-many relationship between them.
Now we’ll create relationships between the new partner_product table and the different data sources –
- We use the Product ID to link in the SAP datasource
- We use the Product Name to link in the campaign/event information from the SQL Azure data source
- We use the Product Name to link in the Sharepoint list around customer references
Now if I had my druthers, I’d re-do the structure of the Sharepoint list/SQL Azure piece to have the product ID in there, since using a string field (and a name no less!) is, shall we say, less than ideal. But often times the real world can be messy, and we’re going to pretend we don’t have any way to redo those old data sources. So we work with what we have, which again illustrates how valuable LightSwitch is.
We also wanted to capture status updates from folks around deals they are supporting. So let’s add a table called status updates that has the following fields –
I’m also going to create a relationship based on the Update_Date based on the Azure Datamarket stream and the Pipeline and Closed Deal information based on the Opportunity_ID field.
So we’ve gotten halfway through our project, and it’s a good time to take a break because according to SkyDrive I’ve spent almost 900 minutes on this so far (I’ll admit, that seem high). And while what we just did seemed simple, it only seemed that way because of LightSwitch. This would have been considerably more time-consuming without it.
When we resume this later this week, I’ll finish this up and perhaps even do it in a little video Beth Massi style. Until then!
04/12/2013
The Van der Haegen Conundrum or Using Power View, LightSwitch and oData to make yourself look smarter than you really are (Part 1 of Part 3 of the – oh, never mind)
Hey, today’s my birthday (um, well it was my birthday when I started this blog post a week ago. You tend to move slower when you get older, it seems). This was a wonderful birthday week where I learned that bald men are a higher risk of heart disease (which stinks for those of us who don’t have Michael Washington’s Fabio-esque hair) and the newest version of Visual Studio LightSwitch was released. Okay, only one of those qualifies as good news (unless you suffer from a severe case of Peladophobia or are Michael Washington). But who cares about the snazzy new HTML5 client? Let’s talk Silverli– wait, come back!
Alright look – I know no one likes to talk about the, um, OTHER client in Visual Studio LightSwitch anymore. But much like the 1986 Philadelphia Eagles, I still need the old veteran to lead the team while the young, exciting backup gets up to speed and just plays on 3rd down for now. And yes, someday the HTML5 client, much like Randall Cunningham, will provide a lot of exciting moments on its way to three straight playoff losses. Then, it’ll blow out its knee , destroying any chance the Eagles have of winning the Super Bowl in 1991 and leading to a lifetime of what-if’s and flinching whenever I hear the name Bryce Paup.
Now that I’ve shoehorned that reference to the Eagles teams of my youth, we can get down to talking about the Van Der Haegen conundrum, named of course for Jan Van der Haegen, a superstar LightSwitch developer and blogger. So what exactly is the Van Der Haegen conundrum? It’s what you face the sad reality when you can’t build cool dashboards in LightSwitch like Jan yourself! I mean, just look at this stuff – this is fantastic! And he’s giving away free Syncfusion software licenses? What’s a person to do if they aren’t blessed with these blogging and development skillsets but they want to build a cool dashboard? (And no, it is not my wife’s answer, which is to quit typing this up and go clean the basement).
I know I’ve been promising that actionable BI post almost as long as I’ve been promising to clean the basement. But like the basement project, it’s going to have to wait a couple days. I’ll instead lead into it with a poor man’s solution to build something similar to what Jan has built by leveraging the brand new Power View capabilities in Excel 2013, LightSwitch, and the same Northwind Data feed that Jan is using, which can be here – http://services.odata.org/Northwind/Northwind.svc/.
I was going to use an SAP oData feed again from Netweaver Gateway, but since I’m already in the wayback machine with the Eagles, why not use sales data from Access courses in 1997 like Jan did?
Because it is an oData feed we’re using as a data source, we don’t even need to pull this data into LightSwitch for this particular solution, since I’ve covered previously how you can pull it directly into Excel. So let’s pull all the items into Excel using PowerPivot first.
There were three items we needed to add to the dashboard –
Top 5 Sellers
Top 10 Products
Top 15 Regions
Now, Jan showed a cool way to have LightSwitch group these and show the count for the dashboard. Since we aren’t Jan and we’re doing it the poor man’s way, I’m going to
do this in my PowerPivot data model. The first piece is to add the count of ShipRegion as a new field – I can do this in DAX with the following statement –
Count of ShipRegion:=COUNTA([ShipRegion])
Or simply choose the AutoSum function from the cell under the ShipRegion column and select Count.
That gets me part of the way – but how do I limit it to the
top 15 results? Again, a very simple DAX
expression allows me to add rank -
Region
Rank:=RANKX(all(Orders[ShipRegion]), [Count of ShipRegion])
This adds a column which ranks each of the items from the highest count to the lowest. That way, when I go back to my Power View, I can simply set a filter on the rank field to always show the top 15, even if the underlying data changes.
Now that we have all the proper fields and filters for the Top 15, let’s pull them together in Power View
Select ShipRegion and Count of ShipRegion (You can pretty this field name up a bit in PowerPivot if you want) –
By default, this will put it into a table format. But I like pretty colors! So I’m going to change my table to a column chart by simply going up to the toolbar and selecting Stacked Column under the Column Chart dropdown (please forgive the sloppy circle – this Microsoft Wedge Mouse isn’t designed for someone with feet for hands)
I’m going to sort it by Count of ShipRegion by selecting that from the sort options in the upper left corner of the chart
I’m then going to add the rank filter to the filter area by simply dragging the Region Rank field I created over and setting the slider accordingly -
That gives me the following chart –
We’re
close to Jan’s chart, but still a couple more items to add. The first is to add data labels to show the
count numbers overlaid onto the chart by going to the layout tab and turning on
the data labels
Then I’m going to add the ShipRegion as a legend for the chart by dragging that field into the legend item like so
Then let’s hide the legend from view because I just want pretty colors for each item, not some stupid chart legend hogging up valuable screen real estate. Ta-da, we’ve matched the chart Jan had, but with cooler colors (okay fine, they aren’t cooler, more like they are the default option in Power View).
We’re
still missing the proper title, which we remedy by adding a text box for the
title -
And we’re done!
The other two charts are derived from the Order_Details_Extendeds entity (for the Top 10 Products count) and, uh, well actually, I assume the pie chart is based on the Invoices entity, but couldn’t figure out exactly how Jan was calculating who was considered top 5 (I’m guessing it’s something like total deal value divided by number of distinct deals for each sales rep, but that didn’t work, so I gave up). I’ll just use sum of total sales since I am out of coffee and figure you get the gist at this point. My final dashboard then looks like this –
This turned out pretty nicely! But you’ll see I couldn’t make it look EXACTLY like Jan’s (there’s a reason this is called the poor man’s solution) – I couldn’t use checkboxes to select the salespeople above the chart, but I could use Power View’s filter area to accomplish the same thing
I can see all of you also pointing out this doesn’t get it into a LightSwitch project either. Well, that’s where you can take advantage of your Sharepoint 2013 or Sharepoint Online/Office 365 instance. If I create a new LightSwitch project and use Tim Leung’s blogpost from back in the day on how to embed a webpage inside of LightSwitch, I can upload my Excel to Sharepoint, grab the URL of the document, and then display it like so –
Hey, that’s pretty cool, right? Yes and no.
I’d normally load the PowerPivot data model into Sharepoint and THEN build a Power View report on top of it instead of doing it in Excel. I can then do automated data refresh and not have the user forced to open the file in Excel to refresh the data. Unfortunately, this isn’t an option right now with Office 365/Sharepoint Online, and it’s not an option at all if you create the Power View report in Excel as opposed to Sharepoint – what you can and can’t do is outlined here. It also could create additional overhead around security and if you don’t have Sharepoint 2010/2013 or Office 365 as an option, then this blog post was fairly useless to you and you are cursing me right now for having wasted your time.
On the other hand, my experience has generally found any dashboard I create has people wanting variations thereof almost immediately upon rolling it out, and this obviously gives users enormous flexibility to potentially download a local copy of this file and roll their own dashboards in Power View against the data model I have setup. It also requires basically no code and would even work with the previous version of LightSwitch while showing off Sharepoint Online/Office 365, which hopefully helps keep me in Visual Studio Overlord benevolent Microsoft collaborator Beth Massi’s good graces.
So that’s my solution to the Van der Haagen conundrum – a much better one would be simply hire Jan, but that would be a fairly lame blog post (Hire Jan and um, see you next week!). Coming next week will be one of these topics – How you can tie this Power View report into an actionable BI scenario OR How not cleaning your basement after being reminded to do so for several weeks leads you to have to sleep in said basement since you aren’t allowed to sleep in your normal bed because your wife says you should have done it the first time she asked back in February. I’ll work on shortening the title if I need to go with option B. Have a nice weekend!
03/26/2013
Quick and Dirty – Compare your SAP CRM Pipeline to a static target value by leveraging SAP Netweaver Gateway, Visual Studio LightSwitch and Excel 2013 (Part 2: Electric Boogaloo in Post 2 of a series)
So I wasn’t really planning on doing a post on this next topic, but it’s probably better than my original plan to sit around all night buying stuff from my childhood on EBay. For those of you who’ve spent any time building reports/dashboards for folks in sales, you’ve probably heard the following –
“I need to see how our pipeline looks against our target.”
Then they hit you with the good old –
“By the way, my targets are by industry for the year” or “My targets are by partner by quarter” or “My targets are in my head” or “Target is a great place to shop.”
If you’re a BI professional for a living, this request isn’t a big deal for most of you, since you’re used to using SQL Server Analysis Services or Business Objects and probably already have a nice cube with this information at everyone’s fingertips.
OR
You’re not a BI professional for a living, just found out there are new target numbers in place and the bigwigs want to see updated numbers from your group first thing in the morning. Would your advice to folks be this?
Not a terrible idea, but perhaps there is a better way to handle it than everyone entering Thunderdome. This post will cover a quick and dirty method I’ve shown to a couple folks in the past. We’ll use PowerPivot in Excel 2013, an SAP CRM BW oData feed (using SAP Netweaver Gateway), and a simple LightSwitch App and the oData feed from that (you could use also use a SharePoint list, but you’ll see why I recommend using LightSwitch in the next paragraph).
The first thing we’ll do is create a simple LightSwitch App so various users can type in/upload their target values – for the purposes of this example, we’ll assume that there are three basic items they are looking to compare to the current CRM information: a category, a time period and the target amount in dollars. I’ll also add a field for the user ID or user name so I can add an entity filter and let users see the data they are adding for now. The table you create in the app will look something like this then –
The (C#) code for the entity filter (if you choose to use one) would be –
partial void Targets_Filter(ref Expression<Func<Target, bool>> filter)
{
filter = e => e.UserName == this.Application.User.Name;
}
You also should add code to have the user’s name inserted into each record added to make sure the filter is, you know, useful -partial void Targets_Inserting(Target entity)
{
entity.UserName = this.Application.User.Name;
}
Okay, once that’s done, add an Editable Grid Screen to the project and maybe a button to allow folks to upload their data from Excel if they want to (best to have the data centralized in case we need to re-use it for other reports in the future). We’ll set targets for this quarter by industry in LightSwitch -
Now that I have my LightSwitch project done and my target numbers entered, I can bring my oData feeds into PowerPivot from SAP CRM and LightSwitch. My PowerPivot file now has two tabs each representing a different data feed, one from LightSwitch and one from SAP. (We don’t need to bring them into Excel first like we did in the previous post – we can add them directly into PowerPivot by going to Get External Data -> From Data Service -> From OData Data Feed)
Which leaves me with the following view -
Okay, so we have our two data sources – how do we link the two? Well, we know the comparison will be made on Industry (Category) and Quarter/Year, and we know that you can create relationships in PowerPivot (or if you don’t, there is a great tutorial here that will walk you through the process), so I just need to create a relationship between the two on those common fields and I’m all done! Wow – this was so easy. Vintage GI Joe Hovercraft on EBay, here I come!
Well, not quite. When I choose the Design Tab and click Create Relationship, this happens –
Uh oh – I can’t create a relationship on those fields because neither is UNIQUE to that table. That’s the key. So how can I accomplish what I need to do? I need to create some unique lookup “sheets” that I can link into PowerPivot and then create relationships to those from each data source. This is pretty easy to accomplish.
Jump back to your Excel sheet and create two sheets. One we’ll call “Industries” and the other we’ll call “Quarters”. Each tab should then contain the unique data for each –
and
To add them to your PowerPivot data model, make sure you are on the PowerPivot tab and select “Add to Data Model”
There will be a new tab in PowerPivot that looks like this –
Make sure you do this for each table you wish to add. This will allow you to change data without changing the data model in PowerPivot at any time by simply hitting “Update All”.
Ah, that’s better. No errors when I go to create my relationships!
I’ll link CRM to the Quarter lookup table on the Cal_Yr_Qtr
field in the CRM table
I’ll link CRM to the Industry lookup table on the Industry field in the CRM
table
I’ll link Targets to the Quarter lookup table on the TimePeriod field in the
Targets table
I’ll link Targets to the Industry lookup table on the Category field in the
Targets table
My PowerPivot “cube” should look like this (click this
button to see
the relationships) –
I’m guessing at this point you’re curious to see if this worked. Let’s create a PivotTable based on this PowerPivot cube by clicking the PivotTable button -
Add the Industry field from the Industry lookup table you created and add it as a row field, and the Quarter field from that lookup table you created and make that a column field –
I always create calculated fields to bring into my Values section because then it allows me to do some nifty calculations with them later on without retyping a lot of stuff. I am going to go to the PowerPivot tab in Excel and select “New Calculated Field” from the Calculated Fields dropdown.
For Target, I am going to add a “Target Total” field by using the formula =SUM(Targets[TargetAmount])
Targets is the Table Name I am using, and TargetAmount is the column I wish to create a total for. I’ll do something similar for the CRM amount as well
Let’s add each of those to the PivotTable as Values and see what happens for the first quarter
Ta da! My target totals look correct, and my CRM totals appear to be rolling up properly. Let’s do a quick double check of the data back in PowerPivot by summing up the Amount column and filtering based on 1/2013 –
Well what do you know? I’m not as dumb as I look (according to my father, no one could be that dumb – that was a rough fourth birthday).
Next post I’ll get back to what I promised to cover in this post - showing an “actionable BI” scenario and revisiting Power View. Until then!
(Oh and I was just kidding about my dad - he didn't say that until I was at least 6 or 7)
03/22/2013
Beyond the Data Dump or How to leverage Netweaver Gateway and LightSwitch oData functionality in Excel 2013 (Part 1 in Post 2 of a series – confused yet?).
Disclaimer - The opinions expressed in this blog are my own views and not those of SAP, Microsoft, or the guy in the white Honda who cut me off today. Trust me.
So the first bit of feedback I received on my blog post yesterday was the following – “You just complained about people taking data out of the system and dumping it into Excel, then you go on to say you’ll show them just how to do that in your next blog post? That’s a little odd.” And I think that’s a good place to start what is sure to be the most “controversial” of this series of blog posts.
I tried dancing around this first point when I started this post, then I tried couching it, and then I tried not saying it at all. But here goes –
I love Excel 2013. Seriously, it’s a fantastic BI tool, especially since PowerPivot and Power View are now baked into it. As long as Microsoft doesn’t screw it up, it has the potential – oh, what’s that you say? Those features are only available in the Pro Plus version, which is in many ways a step back from the ability in Office 2010 to install PowerPivot for free, regardless of the SKU?
Sigh.
Alright, well, let’s assume the vast majority of the companies out there have Pro Plus versions of the Office suite. As soon as they upgrade to Office 2013/Office 365, every user will suddenly find that a product they already know and use every day has transformed into one of the best BI products out there. And look how easy it is to – why are you looking at me like that? Oh, right – SAP sells quite a few BI products and Excel 2013 could be considered a threat to that business so why am I, a loyal SAP employee, talking about Excel 2013 as a BI tool?
Well, the two main reasons in my mind are –
- Unlike other BI products we might be competing against, no one is going to replace Excel entirely with SAP BI tools, and frankly, I doubt we’d ever expect them to. If we did, I hardly think we’d put in an “Export to Excel” button.
- To that end, I’m sure many of SAP’s customers have the aforementioned “data dump” scenario playing out on a daily basis. This has potential licensing violations associated with it, and rather than spending time doing a costly audit and going down the adversarial path with our customers, I’d rather highlight a scenario that has:
- numerous improvements around the customer experience, in terms of security, ease of use, automation, etc.
- makes the licensing questions a lot more transparent then trying to figure out who emailed what spreadsheet to whom
- it allows SAP to make a far more compelling case of why these folks at the customer should pay for a license, since they are getting a lot more features and functionality than they’d get from a simple data dump.
I could also go into showcasing some of our mobile BI options compared to using Excel – regardless, the point is I think it’s something we should consider complimentary, embrace the functionality it offers and leverage that to our benefit whenever possible.
Okay, now that I finished that high-wire act, let’s get into the meat of it – how can the current users who “just want it in Excel” leverage Netweaver Gateway to accomplish that task?
Well, the first thing you need to do is install Gateway (duh) – this document walks you through how to do that - http://scn.sap.com/docs/DOC-16328. (For those of you don’t have SAP installed where you work, you could do all this with an oData feed from a LightSwitch app, or any oData feed for that matter.)
You also need Excel 2013 Pro Plus – more information on how to get that is available here
Once you’ve gotten Excel 2013 installed and listen to that chirpy video they make you watch once it is installed, open up Excel and create a new workbook –
There’s actually several ways to now bring in an oData feed at this point –
- Under the Data tab
- Under the PowerPivot tab
- Under the Data Explorer tab (if you have the preview installed)
Let’s focus on the first two, since Data Explorer is still in preview (but definitely worth checking out). Every version of Excel 2013 has the first option available to users, which is bringing in the oData feed in the Data tab options. This is the best option if you are an Excel novice or just want to look at your data formatted as a simple table. So how do we create the connection and bring in our data? Easy!
Go to the Data tab, and then choose From Other Sources, then select From oData Data Feed
The Data Connection Wizard will pop-up and ask you to enter the datafeed URL and the security information. Let’s use the public feed and credentials Holger pointed out in his blogpost a few weeks back.
URL - https://gw.esworkplace.sap.com/sap/opu/odata/sap/ZCD204_EPM_DEMO_SRV/
user name : GW@ESW
password: ESW4GW
Once that’s entered, you’ll see the entities that are exposed in the feed.
Let’s just check the Products entity for now and hit Next. I’ll be prompted to save the information for the new Data Connection file I’ve created in case I want to use it in the future.
Then I can hit Finish. At that point I’ll get a few options on how I want to view this data.
Let’s first view it as a table in a New worksheet. I make my selections and hit OK.
Ta Da! Check it out –
All my records were imported and I’ve saved the data connection in the file. Now any time I want to see the latest information, I simply hit the refresh button and I get the latest information. And any subsequent pivot tables, charts, etc. that I build on top of this information will update any time the information updates. Plus, the data is now has security around it, so someone with improper credentials would be out of luck (Ideally, you are using WinAD in combination with SSO, but I'll leave that "fun" topic to another post).
Another option in the list was to expose the data as a Power View Report –
I would recommend you NOT create a data feed and push it right into a Power View sheet. Instead, I would recommend you pull it in via PowerPivot so you have many additional options on how you’d like to work with the data.
To enable PowerPivot in Excel 2013, go to File -> Add-Ins -> Manage COM Add-Ins
Hit Go, and you’ll see the following –
Check the box marked “Microsoft Office PowerPivot for Excel 2013” and hit OK. You’ll then see the PowerPivot menu in the menu bar.
Hit “Manage Data Model” to open up PowerPivot. You’ll see the data you brought in earlier through an Excel tab is already added to the PowerPivot data model.
Now, you can shape your data to best work with it in a pivot table, Power View report, etc. See how I highlighted the price column? It’s currently being treated as a text column – I’m going to change that to using the formatting options –
Let’s change it to currency
This will give me the ability to use the field like a number and handle things like sum, average and other mathematical functions properly. Now if I go back to my Excel spreadsheet and choose Insert - > Power View, I see both two options –
Products is the data from the PowerPivot, while Table_Products is the original data I pulled into an Excel table. There’s one major difference now in how the price field is being treated.
In the Products option, it looks like this and the Sigma sign lets you know it is being treated as a numeric field -In
the Table_Products option, it’s still being treated as a text field -
So if I had brought the feed in directly to a Power View Report, it wouldn’t have been all that useful. I’d strongly suggest you create an explicit calculated field and not use the field directly from the table to do your calculations with, but there are much better PowerPivot blogs to walk you through the hows/whys of things like that.
So now in Power View, I can simply drag and drop fields from my table to make some really cool interactive dashboards/reports
Let’s do a quick table of the different suppliers and their products/prices. Simply check those boxes in the field list and you’ll see the following
What if I wanted to make it a chart instead? No problem, I just choose my chart type from the toolbar and it changes automatically –
This hardly does Power View justice in terms of the potential it has (including interactive mapping, multiple data sources on a single view, etc.), and frankly, this is a blog and not a novel, so I’ll hold some back and split this post into two parts so I don’t bore anyone to tears with my “beloved” dry walkthroughs. In the next part, I’ll briefly talk about some additional Power View and PowerPivot functionality and then get into some of “Actionable BI” scenarios as well.
03/20/2013
02/20/2013
Adding calculated fields to your HTML5 LightSwitch project
So this one I found really helpful, and a special thanks to Heinrich Wendel from the Microsoft LightSwitch team for helping me out on this. I had been really struggling to make calculated fields work in the beta of the new HTML5 client for LightSwitch since I don't know JavaScript nearly as well as I know C#. I had tried any number of ways to do this, but in the end, it ended up being a few simple lines of JavaScript code that anyone can easily add and use for their project.
First I add a simple table to the project with two values, Value1 and Value2 -
Then I create an add/edit screen plus a local property to show the total and add this code to the postRender Method
myapp.Table1ItemDetail.Property1_postRender = function (element, contentItem) { // Write code here. contentItem.dataBind("screen.Table1Item.ValueOne", function (newValue) { contentItem.screen.Property1 =
contentItem.screen.Table1Item.ValueOne + contentItem.screen.Table1Item.ValueTwo;
});
contentItem.dataBind("screen.Table1Item.ValueTwo", function (newValue) {
contentItem.screen.Property1 = contentItem.screen.Table1Item.ValueOne + contentItem.screen.Table1Item.ValueTwo;
});
};
And ta-da! It updates the totals as I change the numbers! No postback needed!
This was too easy, so thanks again Heinrich for all your help! Maybe that #SaveMeJaySchmelzer hashtag worked after all. :)
02/09/2013
Creating Mobile Dashboards using LightSwitch and the ReportPlus app from Infragistics
One of the things I'd heard again and again on the message boards and in articles around LightSwitch was that its lack of reporting options out of the box was a big drawback. While this had some validity in LightSwitch 2011 (although I always felt it was overstated, but whatever), with the introduction of oData in v2 of the product, this should have put to rest the idea that you couldn't do offer a number of reporting options to end-users in conjunction with a LightSwitch application. Because now you could leverage PowerPivot in Excel, which is a free download for Excel 2010 and is baked into Excel 2013 by simply checking a box. I'm not even going to go into all the different options for reporting you have with PowerPivot, because they are too numerous to mention (a GREAT resource for PowerPivot is available here, by the way - http://www.powerpivotpro.com/). Also, Microsoft just (wisely) added Power View into its Excel 2013 product, which adds drag and drop dashboarding capabilities into Excel with no programming required. It has some limitations, sure, but it's a fantastic complement to PowerPivot and really helps round out Excel as a BI powerhouse that almost every business user is very familiar and comfortable with and already has on their PC.
"But what about for the iPad?" you ask. Well, I won't get into the fact that Sharepoint 2013 allows you to run PowerPivot reports on the iPad, and I'll not get into all of the cool new SAP mobile solutions for business intelligence (in this post I won't anyway). Instead, I want to bring your attention to an app that is a bit of a "hidden gem" currently sitting on the iTunes store for anyone to purchase for $29.99. It's called ReportPlus, and it's made by Infragistics, who may be known to many developers out there for their strong set of development tools (except for their VERY mediocre LightSwitch suite, which pales in comparison to the far superior Studio for LightSwitch control suite from ComponentOne). ReportPlus allows anyone to pick a dashboard template, take one or multiple data sources (including oData from a LightSwitch app of course), and then create your dashboard directly on the iPad by simply dragging and dropping your data. So how easy is it? Let me walk you through it -
1. First, start the app and select a template by tapping on it
2. Add a data source to use for your dashboard - you can see how many options you have to do this from the menu of data sources it supports
We're going to use the Northwind oData Feed for the demo. Once selected, you'll see the different tables/queries that have been exposed in the feed. You can then drag and drop your selection(s) into one of the boxes in the dashboard template
Let's do a sample with the Category_Sales_for_1997 data by dragging it into the first window and bring up our dashboard creator -
Look at the number of charting and table layouts available to use! How about I do a simple pie chart with this dataset - wonder how long this will take. Oh wait, the answer is two seconds since I can just tap the pie chart symbol to do that.
So I'm going to do a little cleanup here by changing the title, getting rid of the grid, etc. So after a minute, I have the following -
I then proceeded to complete a quick sample dashboard in under 5 minutes that looked like this
This was almost too easy to put together, and I can set how often I want it to go back and fetch live data (among a myriad of other options I have to shape my data). Obviously I want to show it off to my co-workers now. So how can I do that? Simple, I can click on the arrow if I wanted to quickly send it via e-mail as a snapshot -
But, you also have the ability to send the dashboard you created either as a template someone else with ReportPlus can reuse/modify, or even export it right to a PowerPoint file that you can open in any number of other programs -
Here's how it looks in Quickoffice (which is so annoying to have to use as someone who loves Office 2013 like I do)
And before you ask, yes, you can get rid of all the annoying ReportPlus logos in the presentation (no offense Infragistics, but you should really have the option to export it without the logo in the paid app).
This is a VERY simplistic example of what you can do with this app - the first time I saw this app demonstrated to me I was blown away by the potential it represented. It has more features then you can shake a stick at, is simple to use, and gives a whole new group of users the power to create really slick mobile dashboards with no coding or PC required. And Infragistics also has an Enterprise Solution available if you're looking to have even more control on what features you allow your users to leverage, branding, etc.
ReportPlus is a great companion to LightSwitch in Visual Studio 2012 - it showcases how LightSwitch and its ability to expose data via oData can make mobile reporting a snap. Highly recommended.
.jpg){kind=link}
