Set your data free.

Integrating custom, interactive reports into your applications—including dashboards, real-time data, and location-based visualization—is easier than you’d think.

Tweet this

Like this

Learning

Labs & Tools

Resources

15:00min

Introducing Microsoft BI Solution Development

by: Peter Myers, Mentor, Solid Quality Mentors, and Roger Doherty, Evangelist, Microsoft

Monday, December 20, 2010

There’s no need to buy new software or learn new technologies if you want to create end-to-end solutions that deliver rich data to your end users. Find out how you can use SQL Server, Office, and SharePoint to quickly build intelligent, integrated business solutions in this short overview presentation with Roger Doherty and Peter Myers. more less

View Transcript Introducing Microsoft BI Solution Development SQL10R2BYFBI00-TSCRIPT-03 Peter Myers Mentor Solid Quality Mentors Roger Doherty: Welcome back to the “Build Your First Microsoft BI Solution with SQL 2008 R2” training course. In the previous presentation Peter gave us a high level understanding of what Business Intelligence is about and what developers can do to deliver better reports and analytics experiences to their users. In this presentation we are going to drill down and see how they actually accomplish that on the Microsoft platform, at a high level. Peter Myers: Introducing the Microsoft BI solution development, we’re going to start by describing Microsoft’s BI strategy and vision which is all about improving organization by providing insights to everybody that is going to lead to better, faster, and more relevant decisions. That’s basically the BI definition we came to in the previous presentation. Microsoft has had a long-term commitment in BI. You’ve been with Microsoft for many years. How many? Roger Doherty: I was just looking back at my email archives, looking at the first time we talked about Analysis Services back in the 1997/1998 timeframe; OLAP Services. We’ve been involved in the field of Business Intelligence in SQL Server for a couple of decades now. Peter Myers: Formally we’ve seen the introduction of BI capabilities into SQL Server and into Office for over a decade now. SQL Server has led innovation in this space, like the first relational to database to provide data mining, for example. Widespread delivery of intelligence through Office, as we’ll see in a moment: Excel in its own right is a Business Intelligence client, already waiting to be used. These platforms are now very much considered to be enterprise-grade. When we look at Microsoft Business Intelligence we can break it into these three layers. I like to work from the bottom up because SQL Server is the data platform; it’s the very foundation that is going to start delivering from your data sources upwards. We see that you’ve got Office, including the productivity suite with tools including Excel which allows us to connect directly to these data stores and allows us to access the data and query it. From a collaboration point of view we have Sharepoint that brings us all together and allows us to share and produce team solutions. Office presents a familiar user experience, ideal as a Business Intelligence client and experience already. Sharepoint is all about integrated content and collaboration. Sharepoint is a huge topic. The fact is that Business Intelligence is just one of the pieces of pie that Sharepoint delivers. Finally, from the SQL Server point of view, it’s all about data here, whether it’s your relational system, the source system, staging system, data marts, or analytic systems like data mining models and cubes, reporting applications and experiences on top; SQL Server delivers it all end-to-end. Let’s talk about SQL Server here with SQL Server 2008 R2. It’s worth noting the discussion on editions, specifically talking about Standard, Enterprise and Data Center is that if we’ree delivering Business Intelligence, the full Business Intelligence services are delivered with these three. If you want the full suite with advanced capabilities, Enterprise edition and above will give that to you. During this training course there might be some features that are not available in Standard edition; we’ll make them clear to you. There are other editions; perhaps Reporting Services in a limited way would be available to you. You could still deliver some limited BI from this platform. Next we come to Microsoft Office. The Office System includes programs, servers, services and solutions to deliver a broad array of functionality to address business problems. They play a major role in realizing Microsoft’s BI vision; specifically our focus here is going be on Excel 2010. Most of us will be familiar with Excel as a spreadsheet application, but it’s so much more. I guess you’ll appreciate, having been with Microsoft for so long, how the functionality on the menus, and now the ribbon, has increased over time to provide much more than just worksheet functions. Embedded into it is the ability to connected data stores and to interactivity explore the data and produce reports. With the recent release of PowerPivot, the capabilities of Excel have been extended to allow end-users to produce their own analytic models and to share and report from them. Sharepoint Server is a huge product, huge capabilities, content management, search, and so on. Business Intelligence ties very neatly into this. Specifically, when delivering Business Intelligence, which Office prefers to call Business Insights, there are services like Excel Services, PerformancePoint Services, Visio Services, and Business Connectivity Services that are specifically there to deliver data in different ways to meet the different needs of your users. Let’s take a look briefly at Excel Services. It’s a server-side technology for securing and controlling the delivery of workbooks. We can now use Excel as a report writing experience. We can publish our workbooks to Sharepoint, and Excel Services can then query live data, render the workbook in HTML, and deliver that back to the browser. We explore this in much more detail in one of the presentations in this training course. PerformancePoint Services is about performance management. Ultimately, it delivers dashboards that provide the ability to define very sophisticated KPIs and embed these into interactive scorecards. That supports the monitoring process, and often what happens when you’re looking at a scorecard and something’s off track, what’s your next question? “Why?” This introduced the analysis process which is delivered through reports, and interactive reports that allow end users to discover the answers as to why KPIs are off track. We can then assemble dashboards with filters that filter across scorecards and present this combined monitoring and analytics experience all delivered through PerformancePoint Services; a service application of SharePoint. I’m going to hand this over to Roger. This is Roger’s work of art leveraging the Microsoft BI platform. Roger Doherty: AKA, the evil triangle of death, right? I use this graphic to describe the opportunity for developers for integrating into our platform and the fact that you can bite off the whole chunk or a piece of the pie and either way we are going to love you for it and it will deliver great value in your own custom solutions. If we look at the platform as a whole, from SQL to SQL Server information platform all the way out to SharePoint with the rich services like PerformancePoint Services, what we’ve got is a very great robust enterprise platform for delivering BI solutions. This is becoming a very pervasive platform. SQL Server is one of the fastest growing databases. Enterprises worldwide are standardizing on SharePoint, Office, and things like Excel are the preferred data analysis tools for most information workers in the world. This is a great environment for you to be building solutions to interoperate with these technologies, because it’s going to get instant adoption. Peter Myers: Excel is likely to be installed on everyone’s desktops. The BI client is already out there waiting for you to produce solutions that Excel can leverage. Roger Doherty: Yes. It’s an integrated platform. We do a lot of work internally to try to make all these pieces of the pie play nice and to expose a developer surface that makes sense and that can be leveraged in a custom solution development environment. It’s a very flexible platform, giving you the ability to grow and adapt to changing business requirements. It’s full featured; we have a really rich set of functionality from basic operational reporting all the way out to extremely sophisticated statistical algorithms for doing data mining and everything in between. Again, we are going to talk about a lot of things during this training course. You don’t have to bite off on all of them but you should be aware of them and you could, for example, build a completely integrated BI solution that takes advantage of all of those pieces of the stack. Interoperable. Important. The ability to build a BI solution often has to operate in a standard spaced heterogeneous kind of environment. We are doing a lot of work on the Web, on standard base protocols for information delivery like OData, the ability to reach out and interoperate with other databases within our information platform. There is just a ton of examples of good work that we’re doing to make sure that we can play nice in an overall heterogeneous environment. Once you have all of these things in place the nice thing for you as a developer is you don’t have to build it yourself; you can leverage the platform in an integrated BI solution. Extensibility. From the data platform all the way out for SQL Server, the ability to add your own custom functionality in your solutions. That extends through the Office platform all the way out to SharePoint, and building custom Web Parts for example to deliver your own custom experience within SharePoint. The whole thing is designed to make it possible for you as a developer to extend the capability to the platform to make it do exactly what you want it to do. The bottom line is that as a developer, you can leverage the full capabilities of this platform, not have to build this functionality by yourself, and really take advantage of the economies of scale that are all provided by this comprehensive collection of technologies that we call ‘Microsoft BI’, enabling you to build amazing applications in a very, very fast way from the time to market perspective and really giving you the choice of where you want to integrate; the whole stack, pieces of the stack. We want to invite you to the party and ask you to take advantage of this incredible platform. Peter Myers: That brings us to the last section. Introducing the Developer Toolkit. What do you have available to you to start producing BI solutions? Allow me to introduce SQL Server Management Studio, you may be familiar with this application. And you may not be familiar that the BI services like Integration Services, Analysis Services, Reporting Services, and that you can connect to them, configure them, and in the case of Analysis Services you can connect and query the cubes and data mining models you have defined there. SQL Server Business Intelligence Development Studio is a really Visual Studio. It’s the licensed product that comes from SQL Server. It includes the designers for producing BI solutions with Integration, Analysis and Reporting Services. We will explore them in many of the demonstrations associated with this training kit. The templates, or project types, integrate with source control systems that allow us to produce BI solutions for SQL Server and to deploy the solutions direct to the SQL Server environments. Finally, for anything you need to know about SQL Server, Books Online is one resource. Books Online was the approach of having the documentation installed on the client. Now on the Web, simply Bing-ing your keywords together with ‘MSDN’ or ‘TechEd’ should surface to the content you require. We need to mention it is the online documentation for SQL Server that might be a reference for you in coming to terms in learning what the SQL Server platform can do for you. That brings us to the end of this presentation for developing Microsoft BI solutions. The home site for Microsoft BI is always a great place to go. It’s updated frequently with updated topics, news, what’s going on with upcoming releases. www.microsft.com/bi is a great way to investigate further what’s currently going on. Roger Doherty: Great. Thanks Peter for that great introduction. We hope that through the course of the rest of the presentations, demos, and hands on labs, that you’ll take advantage of this great training course to get you up-to-speed on how to get out there and build your first Microsoft BI solution. Thanks a lot and we’ll see you in the next presentation. Peter Myers: Thank you.

Introducing Microsoft BI Solution Development

http://ecn.channel9.msdn.com/o9/ch9/9df7/fbaea670-c5be-472e-ac42-9e4a013f9df7/SQL10R2BYFBI00REC03_ch9.wmv
http://ecn.channel9.msdn.com/o9/ch9/9df7/fbaea670-c5be-472e-ac42-9e4a013f9df7/SQL10R2BYFBI00REC03_320_ch9.jpg
15:00min
Peter Myers, Mentor, Solid Quality Mentors, and Roger Doherty, Evangelist, Microsoft
Monday, December 20, 2010
There’s no need to buy new software or learn new technologies if you want to create end-to-end solutions that deliver rich data to your end users. Find out how you can use SQL Server, Office, and SharePoint to quickly build intelligent, integrated business solutions in this short overview presentation with Roger Doherty and Peter Myers.
Introducing Microsoft BI Solution Development SQL10R2BYFBI00-TSCRIPT-03 Peter Myers Mentor Solid Quality Mentors Roger Doherty: Welcome back to the “Build Your First Microsoft BI Solution with SQL 2008 R2” training course. In the previous presentation Peter gave us a high level understanding of what Business Intelligence is about and what developers can do to deliver better reports and analytics experiences to their users. In this presentation we are going to drill down and see how they actually accomplish that on the Microsoft platform, at a high level. Peter Myers: Introducing the Microsoft BI solution development, we’re going to start by describing Microsoft’s BI strategy and vision which is all about improving organization by providing insights to everybody that is going to lead to better, faster, and more relevant decisions. That’s basically the BI definition we came to in the previous presentation. Microsoft has had a long-term commitment in BI. You’ve been with Microsoft for many years. How many? Roger Doherty: I was just looking back at my email archives, looking at the first time we talked about Analysis Services back in the 1997/1998 timeframe; OLAP Services. We’ve been involved in the field of Business Intelligence in SQL Server for a couple of decades now. Peter Myers: Formally we’ve seen the introduction of BI capabilities into SQL Server and into Office for over a decade now. SQL Server has led innovation in this space, like the first relational to database to provide data mining, for example. Widespread delivery of intelligence through Office, as we’ll see in a moment: Excel in its own right is a Business Intelligence client, already waiting to be used. These platforms are now very much considered to be enterprise-grade. When we look at Microsoft Business Intelligence we can break it into these three layers. I like to work from the bottom up because SQL Server is the data platform; it’s the very foundation that is going to start delivering from your data sources upwards. We see that you’ve got Office, including the productivity suite with tools including Excel which allows us to connect directly to these data stores and allows us to access the data and query it. From a collaboration point of view we have Sharepoint that brings us all together and allows us to share and produce team solutions. Office presents a familiar user experience, ideal as a Business Intelligence client and experience already. Sharepoint is all about integrated content and collaboration. Sharepoint is a huge topic. The fact is that Business Intelligence is just one of the pieces of pie that Sharepoint delivers. Finally, from the SQL Server point of view, it’s all about data here, whether it’s your relational system, the source system, staging system, data marts, or analytic systems like data mining models and cubes, reporting applications and experiences on top; SQL Server delivers it all end-to-end. Let’s talk about SQL Server here with SQL Server 2008 R2. It’s worth noting the discussion on editions, specifically talking about Standard, Enterprise and Data Center is that if we’ree delivering Business Intelligence, the full Business Intelligence services are delivered with these three. If you want the full suite with advanced capabilities, Enterprise edition and above will give that to you. During this training course there might be some features that are not available in Standard edition; we’ll make them clear to you. There are other editions; perhaps Reporting Services in a limited way would be available to you. You could still deliver some limited BI from this platform. Next we come to Microsoft Office. The Office System includes programs, servers, services and solutions to deliver a broad array of functionality to address business problems. They play a major role in realizing Microsoft’s BI vision; specifically our focus here is going be on Excel 2010. Most of us will be familiar with Excel as a spreadsheet application, but it’s so much more. I guess you’ll appreciate, having been with Microsoft for so long, how the functionality on the menus, and now the ribbon, has increased over time to provide much more than just worksheet functions. Embedded into it is the ability to connected data stores and to interactivity explore the data and produce reports. With the recent release of PowerPivot, the capabilities of Excel have been extended to allow end-users to produce their own analytic models and to share and report from them. Sharepoint Server is a huge product, huge capabilities, content management, search, and so on. Business Intelligence ties very neatly into this. Specifically, when delivering Business Intelligence, which Office prefers to call Business Insights, there are services like Excel Services, PerformancePoint Services, Visio Services, and Business Connectivity Services that are specifically there to deliver data in different ways to meet the different needs of your users. Let’s take a look briefly at Excel Services. It’s a server-side technology for securing and controlling the delivery of workbooks. We can now use Excel as a report writing experience. We can publish our workbooks to Sharepoint, and Excel Services can then query live data, render the workbook in HTML, and deliver that back to the browser. We explore this in much more detail in one of the presentations in this training course. PerformancePoint Services is about performance management. Ultimately, it delivers dashboards that provide the ability to define very sophisticated KPIs and embed these into interactive scorecards. That supports the monitoring process, and often what happens when you’re looking at a scorecard and something’s off track, what’s your next question? “Why?” This introduced the analysis process which is delivered through reports, and interactive reports that allow end users to discover the answers as to why KPIs are off track. We can then assemble dashboards with filters that filter across scorecards and present this combined monitoring and analytics experience all delivered through PerformancePoint Services; a service application of SharePoint. I’m going to hand this over to Roger. This is Roger’s work of art leveraging the Microsoft BI platform. Roger Doherty: AKA, the evil triangle of death, right? I use this graphic to describe the opportunity for developers for integrating into our platform and the fact that you can bite off the whole chunk or a piece of the pie and either way we are going to love you for it and it will deliver great value in your own custom solutions. If we look at the platform as a whole, from SQL to SQL Server information platform all the way out to SharePoint with the rich services like PerformancePoint Services, what we’ve got is a very great robust enterprise platform for delivering BI solutions. This is becoming a very pervasive platform. SQL Server is one of the fastest growing databases. Enterprises worldwide are standardizing on SharePoint, Office, and things like Excel are the preferred data analysis tools for most information workers in the world. This is a great environment for you to be building solutions to interoperate with these technologies, because it’s going to get instant adoption. Peter Myers: Excel is likely to be installed on everyone’s desktops. The BI client is already out there waiting for you to produce solutions that Excel can leverage. Roger Doherty: Yes. It’s an integrated platform. We do a lot of work internally to try to make all these pieces of the pie play nice and to expose a developer surface that makes sense and that can be leveraged in a custom solution development environment. It’s a very flexible platform, giving you the ability to grow and adapt to changing business requirements. It’s full featured; we have a really rich set of functionality from basic operational reporting all the way out to extremely sophisticated statistical algorithms for doing data mining and everything in between. Again, we are going to talk about a lot of things during this training course. You don’t have to bite off on all of them but you should be aware of them and you could, for example, build a completely integrated BI solution that takes advantage of all of those pieces of the stack. Interoperable. Important. The ability to build a BI solution often has to operate in a standard spaced heterogeneous kind of environment. We are doing a lot of work on the Web, on standard base protocols for information delivery like OData, the ability to reach out and interoperate with other databases within our information platform. There is just a ton of examples of good work that we’re doing to make sure that we can play nice in an overall heterogeneous environment. Once you have all of these things in place the nice thing for you as a developer is you don’t have to build it yourself; you can leverage the platform in an integrated BI solution. Extensibility. From the data platform all the way out for SQL Server, the ability to add your own custom functionality in your solutions. That extends through the Office platform all the way out to SharePoint, and building custom Web Parts for example to deliver your own custom experience within SharePoint. The whole thing is designed to make it possible for you as a developer to extend the capability to the platform to make it do exactly what you want it to do. The bottom line is that as a developer, you can leverage the full capabilities of this platform, not have to build this functionality by yourself, and really take advantage of the economies of scale that are all provided by this comprehensive collection of technologies that we call ‘Microsoft BI’, enabling you to build amazing applications in a very, very fast way from the time to market perspective and really giving you the choice of where you want to integrate; the whole stack, pieces of the stack. We want to invite you to the party and ask you to take advantage of this incredible platform. Peter Myers: That brings us to the last section. Introducing the Developer Toolkit. What do you have available to you to start producing BI solutions? Allow me to introduce SQL Server Management Studio, you may be familiar with this application. And you may not be familiar that the BI services like Integration Services, Analysis Services, Reporting Services, and that you can connect to them, configure them, and in the case of Analysis Services you can connect and query the cubes and data mining models you have defined there. SQL Server Business Intelligence Development Studio is a really Visual Studio. It’s the licensed product that comes from SQL Server. It includes the designers for producing BI solutions with Integration, Analysis and Reporting Services. We will explore them in many of the demonstrations associated with this training kit. The templates, or project types, integrate with source control systems that allow us to produce BI solutions for SQL Server and to deploy the solutions direct to the SQL Server environments. Finally, for anything you need to know about SQL Server, Books Online is one resource. Books Online was the approach of having the documentation installed on the client. Now on the Web, simply Bing-ing your keywords together with ‘MSDN’ or ‘TechEd’ should surface to the content you require. We need to mention it is the online documentation for SQL Server that might be a reference for you in coming to terms in learning what the SQL Server platform can do for you. That brings us to the end of this presentation for developing Microsoft BI solutions. The home site for Microsoft BI is always a great place to go. It’s updated frequently with updated topics, news, what’s going on with upcoming releases. www.microsft.com/bi is a great way to investigate further what’s currently going on. Roger Doherty: Great. Thanks Peter for that great introduction. We hope that through the course of the rest of the presentations, demos, and hands on labs, that you’ll take advantage of this great training course to get you up-to-speed on how to get out there and build your first Microsoft BI solution. Thanks a lot and we’ll see you in the next presentation. Peter Myers: Thank you.
-undefined-
-undefined-
-undefined-

Adding Interactivity to SQL Server 2008 R2 Reports

http://ecn.channel9.msdn.com/o9/ch9/4ac7/7feaecef-1e9f-4ddd-adf0-9e3501334ac7/SQL10R2BYFBI03REC05_ch9.wmv
http://ecn.channel9.msdn.com/o9/ch9/4ac7/7feaecef-1e9f-4ddd-adf0-9e3501334ac7/SQL10R2BYFBI03REC05_320_ch9.jpg
5 min
Peter Myers, Mentor, Solid Quality Mentors
Monday, December 20, 2010
You may think you know what your users want, but once you deliver, they’re bound to have a whole new series of questions and needs. Take it as a compliment, and empower them to explore and investigate data on their own by adding interactivity to reports with SQL Server 2008 R2 Reporting Services. This screencast demo will take you through the steps.
Adding interactivity to a SQL Server 2008 R2 Reporting Services Report Peter Myers Mentor Solid Quality Mentors Roger Doherty: Welcome back to the Build your First BI Solution with SQL Server 2008 R2 training course. In this presentation, Peter Myers is going to drill down on how to add interactivity to SQL Server 2008 R2 Reporting Services reports. Peter Myers: In this third presentation module, we move beyond the report design basics to explore the interactivity capabilities. We look at parameters, a very important design concept in reports, and then toggling visibility, adding links to a report, the document map, and finally we’ll finish up with interactive sorting. Working with parameters. It’s important to understand that there are two types of parameters. Query parameter is what we embed into our command inside the dataset. The example we see here shows the CalendarYear column is equal to a query parameter as is the CalendarQuarter column. In SQL Server and Analysis Services we prefix with them with an ‘@’ symbol. Reporting Services also has report parameters that are typically used to prompt the end user when requesting the report for a value. Commonly there is a mapping between the report parameter value into the query parameter. If the user enters 2007, it gets passed to the query parameter and the dataset command is executed. Not all report parameters need to map to query parameters. You could collect report parameter values that request what color the user would you like to highlight exceptional data. So there are exceptions. Most report parameters will filter data but you can use them in any way you want in an expression. Configuring report parameters. We can define name, the prompt, the data type, does it support multi-value, whether it’s hidden or visible. Typically with a drill through report, because the parent report will pass parameters, you can hide the parameters. We can also configure available values. The user, instead of being presented with a text box, can have a drop down list that then presents available values either defined statically within the report design or dynamically linked to the data in a dataset, in which case we have value and label fields. The value is typically a database key. The label is the user-friendly label for that. An ‘All’ item can be added to the available values. You need to create a dataset command that will artificially introduce this row. In this case, a key of -1 that doesn’t conflict with the true EmployeeKey values. You then modify the dataset accordingly. By the way, this example is not very efficient. That could, and probably does, result in a table scan. This introduces another best practice, which is that you should consider using a stored procedure as the dataset command. That way you could use logic in the store procedure that test whether the EmployeeKey is -1; execute this query else this query with the WHERE clause. Roger Doherty: This whole concept of having to manipulate your query to accommodate your parameters is one of the arts of the report development experience. Peter Myers: Right, it’s beyond the level 100 experience, but it’s something you’re likely to come into at some point. This topic is one of my favorites. The dynamic parameters, the selection of one parameter value will govern the available values of another parameter. For example, the selection of a country will result in the next dropdown list providing the states for the chosen country. It’s so easy to do, no programming involved. Essentially, we have a dependent dataset. What you see is that from my quarter here, its dataset will use a query that relies on a query parameter that is mapped to the first report parameter. Reporting Services detects this relationship automatically. Roger Doherty: From the UI perspective, it will actually gray out the parameters, right? It makes you choose them in the correct order so that it can populate them correctly. Peter Myers: Right. This may well be a design approach that if I have a million customers in my database, I don’t want a dropdown list of a million customers. I could have some parameters that are not used to filter the data but are there to minimize, or rather help the user focus on a subset of customers, to then filter on that one customer. Next, we take a look at multi-value. Simply check this check box of the report parameter, and it becomes multi-value. The dropdown list then appears like this. You need to be aware though that what’s being sent to the query parameter will be a comma separated list of those multiple selections, in which case you need to adapt the dataset command, perhaps by using the IN operator. Expressions. The Value and Label properties of the parameters are commonly used typically to convey the selection to the header of the report. If it’s multi-valued, be aware that the Value and Label properties are arrays, so you need to access them through the array elements. The VB.NET Split and Join functions could help you work with these properties. They split into an array or join from an array. Roger Doherty: Before you move on with that, we have kind of a default experience associated with parameters in the ReportViewer control and in the default ReportViewer but as a developer, if you’re unhappy with that default UI for how we present parameters, you can always front end that with your own custom code. Peter Myers: Right, when we take a look at the ReportViewer control, it’s all built in. This is convenient, but it may not suit your form’s requirements. Also there might be complex validation that takes place. You can customize this experience, but you’re responsible for producing the form. Roger Doherty: You may want to collect those parameter values in some other part of your user interface rather than the report itself. Peter Myers: Correct. The next topic of interactivity is toggling visibility. This achieves a drill down effect. What you can do on a text box is set the initial state of visibility, whether it’s shown or hidden, and you can use another item in the report to toggle that visibility. In this case, we see that we’ve hidden the detail row of the individual order line but on the order itself, we’re configuring it to toggle the visibility. Be aware that we’re in new territory here that only certain rendering formats are going to render. Those rendering formats are HTML and Excel so if you’re going to deliver this as a PDF, you need to be very careful about this because what isn’t visible initially is never going to be visible, simply because PDF doesn’t support that behavior. This is why the tip is included here. The Reporting Services 2008 R2 enhancement allows you can at authoring time use expressions to turn these features on or off, according to the rendering choice. There is a convenient property called RenderFormat.IsInteractive. You don’t even have to request it by name; the server knows what is interactive and what is not. Next, we can add links. There are three types of links. There are drill through report links, bookmarks links, and hyperlinks. These can be configured on images or text boxes. The drill through allows you to configure a link on the textbox that will navigate them to another report and pass expressions into the report parameters of that report. It very much achieves the ability to navigate from one report to another to potentially explore more detailed data. Just be aware that there is no visual clue that that link is there, however if the user happens to hover over the text box they’ll see the cursor changed to a hand so that’s why I give you the tip to format the text box value as blue and underlined. Roger Doherty: Make it look like a link. Peter Myers: Exactly. Bookmark links allow the user to jump to another location in the report. It is only supported in HTML. If you’re familiar with HTML, you’ll appreciate that it’s just an Anchor tag that allows the jump from one location to another location in the same report. Any other hyperlink supported in HTML, Excel, or PDF allows you to jump for example to your intranet page. Adding a document map. So simple. Only supported in HTML, Excel, and PDF. This is what it looks like in the HTML viewer or the ReportViewer control. Essentially, you can map your groupings to the document map labels and it will produce a hierarchy that becomes like a table of contents, in a sense. They’re really a hierarchy of links. When the user clicks on this employee, the report will jump to that location. Next we introduce interactive sorting, which is only supported in HTML. The headers of your Tablix or the group headers can be configured to support interactive sorting; the user will see an icon next to the header that will support ascending and alternately descending sorting of that column. Roger Doherty: And of course, sorting is something that’s supported by default in the Excel rendering format so even though it’s not being done by Reporting Services you can do it there when you render to Excel. Peter Myers: Right. If you wanted to overcome this limitation, that it doesn’t work in PDF, you could have a report parameter because sorting is governed by expressions as well, so you could have a dropdown that requests what the user wants to sort by. It’s simply an expression assigned to the sorting property. That brings us to the resources for this topic that I encourage you to explore. Roger Doherty: This is an important topic that Peter just covered because the interactivity of a report can really impact its usability and the appearance and the usefulness of that report. Peter has done a great job of giving you a high level overview of that and we’ll see you in the next presentation. -
-undefined-
-undefined-
-undefined-

Develop Embedded Reporting Services Applications

http://ecn.channel9.msdn.com/o9/ch9/3d8c/8cbf50ce-af04-42b6-8007-9e3601403d8c/SQL10R2BYFBI04REC06_ch9.wmv
http://ecn.channel9.msdn.com/o9/ch9/3d8c/8cbf50ce-af04-42b6-8007-9e3601403d8c/SQL10R2BYFBI04REC06_512_ch9.jpg
5:00 min
Peter Myers, Mentor, Solid Quality Mentors.
Monday, December 20, 2010
This demo shows how to really take advantage of all the functionality in SQL Server 2008 R2 Reporting Services. Peter Myers will show you how to access report server programmatically with the web services APIs. He’ll also walk through how to embed the Visual Studio 2010 ReportViewer control to render reports in your applications.
Developing Embedded SQL Server 2008 R2 Reports Services Applications Peter Myers Mentor Solid Quality Mentors In this demo, Peter Myers will show us how to leverage all the functionality of Reporting Services from within a custom application including the ability to access the Report Server programmatically using the Web Service APIs and how to embed the Visual Studio 2010 Report Viewer control in a custom application to render Reporting Services reports. In this demonstration I’m going to develop a Windows Forms application that will deliver reporting functionality and report rendering. Before us here in Visual Studio 2010, I have the Reports form open. I commence by introducing a SplitContainer. Into Panel1 I place a ListBox and I use a Dock property to fill the available panel. This ListBox will present a list of available reports catalogued in Report Server. To achieve that I right-click the project References and I add the Service Reference. I click the Advanced button and then click Add Web Reference, then I type in the reference to my Report Server, and specifically the ReportService2010 Web Service. It locates the service, and specifically requests the Web Service document. What it will do is interpret that document to understand the methods, the types, the protocols and bindings required to communicate with the Web Service. That will allow Visual Studio to producing a series of proxy classes that will allow me as the developer to program against the Web Service as if it were a local component. Here we see documentation of the Web Service that confirms that we have the right Web Service. When I scroll down the list, of interest to me in this demonstration will be the ListChildren method that will allow me to pass in the folder path and whether I wish it to continue recursively returning children, and it will return an array of CatalogItems. Let me name this the ReportService and add that reference to the project. Then I double-click the form header to open up the code behind the window and create the form’s load event handler. What I will do first of all is to come to my Using declarations at the top, and I’ll bring into scope the ReportService namespace. It will bring into scope the proxy classes and types. For convenience I’m going to use the Edit menu, Insert File of Text, to introduce a snippet of code here. It essentially instantiates the ReportingService object into this variable. It is good practice that you should then specify what the URL to your Report Server is. You should not hard code that within your application. Typically this would come from a configuration file. Then we specify that the credentials of the Service will use the default credentials of the application; in other words, the credentials, or identity, of the user running the application. I then declare a variable here, an array of CatalogItems that will have assigned to it the returned object of the ListChildren method. I want to list all of the reports in the Sales Report folder and nothing recursively beneath that folder. I then use a Foreach statement to enumerate each CatalogItem object within that array, and I test that the Type of item is a Report and that it’s not hidden, like a drill through report would be a hidden report. Then I add the Name property to the ListBox items collection. Let’s execute and see what happens. Here we have it. In the ListBox there’s only one report available, but dynamically were are able to retrieve the catalogued report and present it as an available option in the ListBox. Part two of the demonstration, I will return to the Form Designer, and introduce from the Toolbox’s Reporting section, the ReportViewer Control into Panel2. I can configure this for either local or remote reporting. For remote I choose Server Report, specify the URL to the Report Server, and then enter the Report Path. However, in this demonstration that Report Path property will be driven by a selection in this ListBox. I dock this in the Parent Container and then I double-click the ListBox to create the SelectedIndexChanged event handler. I go ahead and insert another snippet of code. In this code I’ve commented out this line, but we should not hard code that URL to the Report Server. Ideally it’s retrieved from a configuration file. Then for the ReportViewer control and its ServerReport object, I update the report path which consists of the path to the Sales Reports folder and then the selected text in the ListBox. Finally, I call the RefreshReport method. Incidentally, you’ll note up here that this line of code was inserted into the form’s load event handler when I added the ReportViewer control. I’ll modify that method to Clear. The control will be cleared until a selection is made in the ListBox. Now when I execute the report, the Web Service is used to retrieve the available reports, I then make the report selection here, and then the ReportViewer control prompts me for a parameter value, and then we can interact with the report. The ReportViewer control provides full functionality of navigation between pages, moving back to Parent report, stopping execution, refreshing, printing, layout of printing, page setup, rendering of different formats, and zoom. Properties of the ReportViewer control allows you as a developer to enable and disable these as required.
-undefined-
-undefined-
-undefined-

Supercharge your Cubes with Calculations

http://ecn.channel9.msdn.com/o9/ch9/8fef/02e1f161-cc7b-433d-8214-9e3701378fef/SQL10R2BYFBI06REC05_ch9.wmv
http://ecn.channel9.msdn.com/o9/ch9/8fef/02e1f161-cc7b-433d-8214-9e3701378fef/SQL10R2BYFBI06REC05_512_ch9.jpg
5:00 mins
Peter Myers, Mentor, Solid Quality Mentors
Monday, December 20, 2010
Every business has different goals and different ways to measure their success. Now you can enrich a SQL Server 2008 R2 Analysis Services cube with calculated members, named sets, and key performance indicators (KPIs) to determine profitability, identify top customers, see how business is trending to achieve profit goals, and more. Peter Myers shows you some detailed examples in this short demo.
Enriching a SQL Server 2008 R2 Analysis Services Cube with Calculations Peter Myers Mentor Solid Quality Mentors In this demo, Peter Myers is going to show us how to enrich a SQL Server 2008 R2 Analysis Services cube with calculations such as KPIs, named sets, and calculated members. In this demonstration I’m going to extend the design of my Internet Sales cube to include calculations. I commence by right-clicking my cube to open it. This opens the Cube Designer, and then I select the Calculations tab. I click here to introduce a Calculated Member of which the first one will be a simple member to compute profit. It will belong to the Measures dimension. Here, within my Measures, I can drag to construct the MDX expression that will subtract Total Product Cost from the Sales Amount. I can use the Format String to specify how I want it formatted for the client. I’ll enter hash comma hash (#,#), or pound comma pound, which represents comma-separated number format. I’ll associate this calculation with the Internet Sales measure group and then I’ll go ahead and deploy the project. We can then use that as any other measure. When we take a look at the available measures now, I have Sales Amount and its Profit. Let’s add a second calculation that will be named Profit % Goal. Essentially the expression is a fixed value of 0.4, or 40 percent. I’ll format that as Percentage. This will be used in another calculation to configure a KPI. I’m going to set the visibility to False. It’s still a calculated member but it won’t be visible to the end users directly. Next I introduce a third calculated member that is going to be named Profit %. The expression for this will be to go ahead and take the Profit and divide it by the Sales Amount to come up with a ratio of profitability. We’ll format this also as a percentage and we’ll relate it to the Internet Sales measure group. Then I’m going to use a Color Expression. This is an MDX expression that will test if the Profit % is less than the Profit % Goal, which is that constant of 40 percent. If this is True then the color will be red. Note here on the right of the box that there is a Color Palette button so that I can introduce the numeric code for red, which is 255. So if that is True, Profit % is less than the goal, then I want it to be formatted red. Otherwise, I want it to be black. I can share with you that the zero is the color black. Now when I deploy, we see the addition of Profit % which I can introduce into my queries. If we take a look at this by Product and perhaps by a different Date, so we’ll bring Calendar in and filter calendar down to Quarter 1 of 2008, sure enough, Clothing for this quarter didn’t meet the target of 40 percent. Let’s try something different. I’m going to remove Category from the query, and also remove the Calculations and the Year. Let’s just end up with the Sales Amount. From my Customer, I’m interested in knowing all of the individual customers. So I drag the Customer level of this hierarchy to reveal the Sales Amount. Perhaps my inquiry here is to understand who our most valuable customers are. In other words, the top customers in accordance with sales revenue. So it takes a little while because requesting the lower level of the hierarchy is requesting a lot of data. We need to be a little patient while it brings back the result. I’m going to right-click and sort descending to reveal the top customers. If this is a common requirement, like I need to know who the top ten customers are, then it is far preferable to introduce a named set that can be conveniently queried rather than having to introduce all of this data, ranking the result, and then peeling off the members at the top. There’s the result. What we’d prefer is a named set that would give us the top ten here. In my calculations, I’m now going to introduce a named which is going to be named the Top Ten Customers by Sales. Any valid set expression can be used here. It just so happens that MDX includes a set function called TopCount. I drag it in, and it says, what’s the set that is going to be considered? I say, from my Customers hierarchy, consider all of the members on the Customer level. The way this reads is, from the Customer dimension’s Customer hierarchy, at the Customer level, all of those members of the set are in consideration. How many do we want? Give me 10. How are they going to be ranked? I drag in the Sales Amount measure. Essentially, it takes this set, orders them descending according to the Sales Amount, and then returns the top 10. When we deploy and switch to the browser, we remove Customers first and reconnect. I’ll place this set on the Filter pane so now we have an operator that says we’re only interested in customers that are in this set. I can then request the customers and very efficiently and conveniently we get to see the top ten customers. The last calculation to introduce into my cube design is configured on the KPIs tab. A KPI really is four calculations for the price of one. Let me introduce a KPI that is going to be named Sales Profitability. It will belong on the Internet Sales measure group. I’m going to introduce that Profit % as the value and for the goal expression - unfortunately because I hid it that means I can’t drag and drop it but I can copy this - will be the Profit % measure. The Status and Trend are configured here by graphic icon and then an expression. I have a snippet for this and my snippet says that when the Profit % is less than 40% return -1. If it’s less than 45%, then return zero (0), otherwise everything else is 1. What the Status requires a return a value of zero (0), -1 or 1 Let me start with -1 being “Bad”, zero (0) being “Slightly Bad”, and 1 being “Good”. Trend works in a similar way. It has its own icon set. A trend expression almost always refers to the previous time period. We can see here that we’re comparing Profit % to a tuple that returns a previous member from the Calendar hierarchy, comma, the Profit %. In other words, compare it to the previous time period for profit and, likewise, this one needs to be updated as well. This way what the Trend is going to do is if the profitability for the current period is less than the prior period, then return -1. Things are getting worse. If there is no change, it’s zero (0). Otherwise, we’ll assume that profitability has improved and we return 1. I copy and paste this into the Trend expression, and then deploy. The way that we test the KPIs is that we have the KPI Browser here. The KPI Browser then presents the KPI and the ability to filter it by the different dimension hierarchy members. Across all time we see that we’ve sort of been on track. We’re between the 40 percent and 45 percent goal. What happens if I introduce the Date dimension and say that from the Calendar hierarchy we want to filter on a period 2007 Quarter 1? We see for that time period that the profitability was 41.94%. What if we take it to a level beneath that and then request a Product level? What was the situation for the subcategory Mountain Bikes? We can see for this time period that Mountain Bikes were exceeding expectations so we’re on track for the Status. Yet it was not as good of a performance as in the prior time period which would be Quarter 4 2006. Let’s explore and see what the story is for Road Bikes, which were well off track at 38.68% and getting worse over time. The great thing about encapsulating these calculations and KPIs in your cube is that you have consistent definitions that can be maintained and used consistently across all querying experiences against the cube.
-undefined-
-undefined-
-undefined-

Designing Interactive Reports and Dashboards

http://ecn.channel9.msdn.com/o9/ch9/0711/b59ffa9d-cbcd-4a2f-9a59-9e3900090711/SQL10R2BYFBI08REC06_ch9.wmv
http://ecn.channel9.msdn.com/o9/ch9/0711/b59ffa9d-cbcd-4a2f-9a59-9e3900090711/SQL10R2BYFBI08REC06_512_ch9.jpg
10:00 mins
Peter Myers, Mentor, Solid Quality Mentors
Monday, December 20, 2010
Quite frequently, data leads to questions. “Why is web traffic down?” “Was this month better than last month?” “Where’s the hole in our inventory?” Interactive reports are a great way to let end users dig into the data to get answers – and they aren’t terribly complex to design. This demo shows you how to use filters (including using time-based formulas) and build an interactive report. From there, you can assemble a complete dashboard – all using SharePoint 2010 PerformancePoint Services.
Developing Interactive Reports and Dashboards with SharePoint 2010 PerformancePoint Services Peter Myers Mentor Solid Quality Mentors In this demo, Peter Myers will show us how to develop an interactive dashboard in SharePoint 2010 PerformancePoint Services using a filter, a scorecard, and an interactive report. In this demonstration I’m going to continue in with the development in Dashboard Designer to create a filter, a report and to assemble a dashboard. The next step is to add to my PerformancePoint content list a new filter, and I’m going to select a Time Intelligence Filter. I add in my data source that I have configured with time properties in the previous demonstration. Then I create some formulas here like Year, which means this year. You’ll see the pattern quite quickly, Year -1 means last year. Year -4 means four years ago. We can do things like Year.FirstMonth:Month, which is Month YTD. When I click Preview we can see that it resolves to certain members within our cube and I’ve intentionally included Year -4 because the sample data doesn’t include 2010 or 2009. You can appreciate from this brief demonstration of producing the filter that there is no requirement to understand complex MDX functions in order to preform time filtering. We can simply configure how time works at the data connection level and then we can use these simple expressions to come up with time-based formulas. When I click Next I get to choose what type of filter display, is it a single dropdown list, or a tree or a multi-select tree. In this case it will be a dropdown list, and then I click Finish to add that filter to the library and I’ll name it Date. Next I’m going to introduce a report, and this is the analytic requirement that at times we see the scorecard and we have questions, perhaps, about why we are off track. The analytic grid that will be based on my Internet Sales data connection, and it will support an interactive experience to start to answer questions why the monitoring is revealing unfavorable results. I’m going to name this report Sales by Customer. Then from the Details pane I can introduce the measures in this case to my columns. Let’s put Sales, Profit, and Profit %. Then on the rows I’m going to place the Customer hierarchy. Then I can configure which members to display, so I include the All member, and I also add every member at the Country level. Now we get to see everything in a breakdown by Country and the ability to drill down if we need to go to further detail to State and to individual Customer. To support passing dashboard filter values into this report, I also place the Date and the Product hierarchies into the background. These will be endpoints to which the filter can pass values to, and also the Scorecard, as you’ll see when I assemble the dashboard. Just to point out that I can convert this to become a bar chart or any other type of chart that we have here like a pie chart, if necessary. So we can do this at design time or the user has the ability to do this when exploring the report in the dashboard. The final step is to introduce the dashboard. We have these zones, of which I’ll choose the Header and Two Columns, and I’m going to name this the Internet Sales Dashboard. Dashboards can consist of multiple pages of which in this demonstration it’ll just have this one page. Into the Header zone I’m going to take from my Details pane here, the Date filter and drag it into that zone. From the Scorecards I’ll drag and drop the Scorecard in the Left Column zone, and my report in the Right Column zone. What’s left then is the wiring up of the connections. Upon the selection of a filter value, what should happen with the selected value? So hovering over my Date filter here I can drag the Member Unique Name field, this is the unique name within the cube itself, and I can pass it to the scorecard. The connection is then established and we can pass it to different parts of the scorecard. Passing it to the page says it will simply filter the scorecard by the passed value. I’m going to create the very same connection by taking that Member Unique Name and dropping it into my Sales by Customer Report. Remember the two endpoints that I added, they are now available here. I’ll pass it to Date Calendar. For the last connection I’m going to create, I’ll do this the manual way. You can select the target and then here on the ribbon create the connection. Where is it coming from? My Profitability Scorecard. On the Values tab, what is being passed? From the Member Row, the Member Unique Name. Recall on the scorecard that on the rows I have the Products hierarchy. Now we’re ready to save everything, which saves all the content back to the SharePoint libraries: the Data Connections libraries and the PerformancePoint Content list. I’m ready then to deploy the dashboard to SharePoint. In doing so, Dashboard Designer creates a Web Part page for each page in the dashboard, and then into its Web Parts, to reflect the different items I have placed in, being the filters, scorecard or reports. Now when I filter and say show me what was going on four years ago we get to see what was going on with the scorecard. We can drilldown and the selection of a scorecard row member will also update the report here. Watch what happens when I click Mountain Bikes: the report updates to show me the mountain bikes and then I can start to explore within the report from there.
-undefined-
-undefined-
-undefined-

Set your data free.

Introducing Microsoft BI Solution Development

Build richer applications.

Come to a live event