SQL Server Reporting Services: Data Analysis Master Tutorial

hi everyone I'm Trish Connor Cato welcome to analyzing data with SQL Server reporting Services also referred to as SSRS this video course is for those who will be the administrators of SSRS and who need to create Dynamic paginated reports using the reporting tools you'll also learn the SSRS administrative tasks during the course when we begin the course there is a Word document in the video description that I will review with you before we get started into the course in this word document you'll find all the requirements software requirements necessary to get your system ready for this course as well as the links and instructions to download and configure software as necessary module 9 is geared toward extending and integrating reporting Services you'll learn how to extend the functionality of reporting services with expressions and custom code you'll also learn the methods for working with reporting services programmatically modules 10 and 11 both cover mobile reports module 10 introduces the design and publication of reports that are intended for consumption on mobile devices SSRS includes support for mobile reports although the tools that are used to design and publish them are different than the tools used for paginated reports and in module 11 you'll learn about the element types that you can add to mobile reports and you will Design publish and learn how to access them although reporting Services is a powerful tool its built-in capabilities might not always meet your needs this module covers the methods for extending the functionality of reporting services with expressions and custom code you will also learn about the methods for working with reporting services programmatically by creating custom assemblies using Visual Basic code you don't need to know Visual Basic as I will walk you through two separate short code blocks so in this module we have two lessons the first lesson is about expressions and embedded code and the second lesson will get into custom assemblies so we've already used Expressions during this course and we'll use expressions in this section as well and paginated reports expressions are used throughout the report definition to specify or calculate values for a whole host of items from parameters to bookmarks there is a link to more expression uses and paginated reports there for you in addition to Expressions SSRS allows us to add embedded customized code to a report in order to perform functions that may not be available or easily implemented in SSRS the code which is embedded must be Visual Basic and allows for code reuse which in turn May simplify report updates so embedded code is created on the code tab of the report properties dialog box and then referenced in an expression for the report object being impacted by the code embedded code can also be created in a class Library which enables it to be copied and pasted into any report and in addition you can save a report that has embedded code in it as a template which you'll see in this lesson we're going to get started by creating an expression in our drill through Target report and Report Builder let's start by running this report so if we look through the prices what we want to set up is an expression that if the price is less than or equal to 100 we want the color of it to be red if not we want it to be green so let's go back to design view and get this set up foreign we're going to right click the sum the unit price field in the detail row and we're going to go to text box properties on the left side we're going to go to font next to font color next to color we're going to click the function button and notice it just has a mirror it doesn't even have an equal sign it just has the color we're going to double click that and delete it and we're going to type in equal sign in the set expression box now we're going to use the immediate IF function here so it's i i f is the name of the function and then we need an open parenthesis now go ahead in the category Pane and expand Fields stock items so that our values list populates with the fields and it'll save us on some of this typing we're going to select unit price and then we're going to type a less than equal to symbol and 100. so if that is true type a comma and then double quotes type red if that is false type of comma and in double quotes Type green and then type your closing paren so if the unit price value is less than or equal to 100 the font will be red if not the font will be green go ahead and click OK and ok now let's run the report again so I see my unit prices that are less than a hundred dollars are showing in red and the other less than or equal to 100 are showing in red and the rest are showing in green pretty cool expression let's go back to design view and Save now we're going to create another expression and we're going to place it in the page footer for this report and we're going to be making it a concatenated expression we want it to say page one of five for example so the actual page number and then the total number of pages and we're going to use some globals Fields these are not like when we went into Fields before in the expression box we use the data set Fields so globals fields are fields that can be used on any report regardless of the data set I'll just describe it that way and so the first thing we're going to do is we're going to right click and add page footer and then we're going to right click in the page footer and insert a text box I'm going to move the text box to the upper left corner of that flitter section and expand it and then we're going to type equals an equal sign and then an open double quote and the word page type of space and then a closing double quote after we get it all in I'll go over it another space and the Ampersand for concatenation and then you're going to type the word globals plural an exclamation point page number so there is a global Field named page number and it's all mushed together another space and an ampersand and a space open double quotes space of and we're going to do another space after of close the double quote an ampersand and then we're going to type Global's exclamation point again and this time we're going to use the total Pages field so it's going to actually say the word page is in double quotes that's literal text so it's going to say page one of five something like that depending on the pages and we can click on the edge of the text box and now you'll see it just says expression in there or the abbreviation for expression and we're going to make it bold and 12 point font let's run this and take a look at it and we're going to navigate to the last page and scroll down and so at the bottom you'll see page three of three based on that concatenated expression using two Global fields and we can go back to design view and Save we're going to get started with embedded code in visual studio and I already have our sales information vs project open we're going to start with a small sample of Visual Basic code that will just display a simple message to the user so what we're going to do is we're going to right click on the reports folder in solution Explorer hover over add and choose new item we're going to name this report embedded code now we're going to right click in a blank area and go to report properties and in report properties on the left hand side we're going to click on code in the custom code box you're going to type function the word function space greeting an open and closing parentheses space as space string now when you're typing Visual Basic code in report properties code window it's not like you're going to have a code editor available so you have to kind of really know the structure and syntax of your code in order to do it this way we're going to press enter and then tab and we're going to type return space and then double quotes hope you are having a wonderful day exclamation point close the quotes enter and we're going to type end function so we're creating a function procedure which we're naming greeting and its return is going to be a string so text and then we tell it what to return that text hope you're having a wonderful day and then we have to have the end function statement or it won't work every function has to be enclosed between function and end function so go ahead and click ok so we put that code in report properties in order to reference it in the report we're going to right click on the report canvas and insert a text box and you can move the text box to the upper left and expand its width you're going to right click inside the text box and you're going to go to expression so we have our expression window and this is where we need to reference the embedded code so after your equal sign you're going to type the word code and the dot so it's letting it know to look for embedded code and we have to give it the name of the function which we called greeting so you're going to just type code dot greeting and then you're gonna and it's typical to see red underlines in here because it's not a code editor and we're going to click ok so now the text box just says expression let's go ahead and preview the report so just a simple message to the user hope you're having a wonderful day we created the code the function procedure in report properties code page and then we reference the code in a text box expression we can go back to design view it suggested that if you're going to write longer more complicated blocks of code or if you're not used to Visual Basic code language that you create a class Library project a class Library contains a code editor so it will help you build your code blocks and that's what we're going to do now so we're going to leave this open let's just save and I'm going to go to my search box and type visual studio and I'm going to launch another instance of Visual Studio when it launches on the bottom right we're going to continue without code and then we're going to go up to file hover over new and choose project so when we started we created a report server project that's what we've been working in this whole time now we're going to create a class Library project so in your search box you can search for class Library and you get several results always be aware if there's a scroll bar on the right so we're looking for one that is suitable for Visual Basic and there are a few of them that do include Visual Basic so for example the first one on my list does not have Visual Basic underneath it it's for C sharp and actually if you look at the icon it has C sharp on it the second one has Visual Basic and it has VB on the icon we want to scroll down until we see class library and then in parentheses.net framework after it and we're looking for the one that has VB on the icon so once you find it click on it and then choose next we're going to name it custom code VB that's what we're going to name this project and we're going to click create when it launches it opens up a class one dot VB tab that is a code editor window and that's where we're going to put our block of code so the first thing I'm going to do in here is I want my font to be larger so I'm going to go up to the tools tab on the menu bar and I'm going to go all the way to the bottom to options and in there I'm going to do fonts and colors on the left side under environment and I'm going to change my font size to 14 in here and click ok I'm going to click on the line so it creates a class okay and it names it class 1 by default and you'll learn more about that a little bit later but my insertion point is under that public class class one line and I want to press enter one more time for me personally when I'm coding I like to have a blank line between the name of the thing the framework and conversely when we get to the end of the Cold line I'd like to have a blank line before end class so we're going to create a function procedure and after we get it typed in I'll explain it to you so we're going to type the word function and because this is a code editor it shows up on the list I can stop typing and press tab to get that word in there and then I'm going to type a space and I'm going to give it a name and the name is going to be long date format and then we have to give it parameters so I'm going to type in open parentheses and it's going to give me the closing one and put the insertion point between the two of them and in there I'm going to type any date as the as keyword and then date and that also comes up on the list so this function the long date format any date that we apply this function to will be formatted as a date and then at the end of it we're going to give it another declaration by using the as keyword again and notice when we get to the end of it there's only two things you can use outside of it two keywords as or handles we're gonna do as again and then string press enter notice how it indents you when we did this in report properties code window I had us press tab after we type the function line to get the indentation we're going to type dim and then suffix as string and use the list as much as you can again I'll go over this when we get done press enter type twice now we're going to type select and then Space Case space day and then in parentheses any day any date after the parentheses press enter and it gives you another indentation and it gives you the case keyword again we're going to type 1 comma 21 comma 31 enter suffix equals and then in double quotes s t at the end of that line press enter and you're going to do shift tab here so you're at the same margin as that case 1 21 31 statement we're going to type case Space 2 comma space 22 enter suffix equals and then double quotes ND at the end of that line enter and shift tab we're going to type case again or select it from the list three comma 23 enter suffix equals double quote Rd enter at the end of the line and shift tab one more time and we're going to do case and then the else keyword enter suffix equals and in double quotes th press enter at the end of that line and it gave us our n select statement actually we don't need to do an enter there we can just delete and then shift tab to get that in select to the same margin as select case so when we did select case it gave us the in select like when we came in here right when we did function if you look it also gave us end function so the code editor really helps you out in building your code after you're in select statement you're going to press enter twice and then you're going to type return space format open paren any date comma double quote and notice it gives you the closing double quote you're going to type four small letter D's space and then a single small letter d and then come outside of the parentheses type in Ampersand and then suffix space ampersand and then format in parentheses any date comma and a space and this time we're going to do a set of double quotes and we're going to type a space four capital letter M's a space and then four small letter y's and honestly the casing in here doesn't matter so you didn't have to do Capital M's won't hurt anything so now come outside of that closing parentheses and press enter and then go to the end of the end function statement and press another enter okay so we have our code in here let's go ahead and Save and let me break down what's going on here okay let's review this function so we named it long date format we gave it a parameter as any date and we gave that a data type of date and then the long date format itself is in a string data type we declared a variable using the dim statement we named it suffix and we assigned it the string data type then we did a select case construct select case is similar to if then else if and if code so it does multiple checks and if the check is valid it will apply the condition so in this case it's really saying we're putting the case statement on the day of any date the day portion of the date case 121 and 31 we want to give it a suffix of St so if it's the first or the 21st or the 31st that ends in St if it's 2 or 22 it ends in ND 3 and 23 ends in Rd and if none of those are true whatever else it is would end in th and then we end that select case construct then we tell it what the function is going to return it's going to return a format so any date we want it to have a format of giving the full day of the week that's the four letter D's the full name of the day of the week Monday Tuesday Wednesday Thursday and after that we want it to have the number of the date so the number of the day so Wednesday the first for example right so the number of the day now if we did the 1D so the first through the ninth would only be one number if we did two D's for that second one it would pre-zero the single digit so zero one zero two all the way up to zero nine but we're doing 1D we don't want the pre-zeroed on the single digit days after that single digit or after the digit of the day of the month it's going to apply the suffix depending on what that number is which we outlined in the select case statement so if it's a 1 if it's the first of the month it's going to apply the St suffix if it's the 23rd of the month it's going to apply the Rd suffix if it's the fourth day of the month it will apply the th which we captured in case else statement and then the Ampersand is the concatenation so it's combining all of these things and then we're going to concatenate another format and that format is going to be for the full month the four letter M's and again they don't have to be capitalized and then the full year if we did two M's there we would get an abbreviated month if we did two y's there we would get the last two digits of the year and then we end our function now our function is contained in this class known as class one go ahead and save if you haven't we'll be doing more work in our class Library later but for right now what I'd like you to do is copy the code that we have here we're not copying public class class one or in class just from function to end function we're going to select it and then copy that code and then switch back over to your other instance of Visual Studio where we have that embedded code report set up we're going to right click in a blank area and go to report properties and go to the code tab on the left and let's select our code that we typed in earlier just delete it and then paste in that function that we just created in our class Library project now notice how it comes in all of the keywords are in blue it's pasting in the result of it being created in a code editor window watch this click ok and then right click again and go back to report properties go back to code and it dropped that code editor format so it only shows when you paste it in there but this is not a code editor so it can't retain it and we can click OK again now we're going to use the same text box that we created before we're just going to right click inside of it and go to expression and you can get rid of that expression you can keep the equal sign the code.greeting expression so now what we're going to do is we're going to do equal if you got rid of it type equal code dot long date format that's what we named the function and then we have to pass it the argument for what date to use and so that's where after we did function long date format and parentheses we said any date as date now we have to tell it which date to apply this to so we're going to do an open parenthesis and in the category pane we're going to double click built-in Fields that's where your globals Fields reside and we're going to double click execution time so it puts that up there in the appropriate syntax and all we have to do is type the closing parentheses so we're saying apply that long date format that we created through a function procedure to the execution time field and click ok so now if you go and preview it and our function code we told it to give us the full day of the week right that was the four letter D's give us the day of the month depending on the day of the month put the appropriate suffix behind it give us the full name of the month and the full year and we can go back to design view so now I'm going to show you how to save code as a template it's actually saving the report that contains the code as a template so we're going to just delete this text box here that's referencing the code because the code is stored again in report properties on the code window so the the code is stored in the report definition let's save and I'm going to use the X to close the embedded code report so now we have to use Windows Explorer to navigate to the directory where the reports are being saved to on your system so I have my window open and this is where you need to navigate to because remember all our vs stuff is in that Source repos folder so navigate to that folder and you'll see all of your reports in there as well as their RDL files so we're going to right click on embedded code report and copy it the next place you need to navigate to is this path at the top that's highlighted and I expanded it so you could see the full path so you need to navigate to that path and that is where templates are stored when you get there we're going to paste the embedded code report that we did and we're going to rename it long date format so notice in here you have data set data source report when we right click on the report folder and we hover over add and we choose new item that's the things that we're seeing in that screen that's where the templates are held for visual studio so now I'm going to go back to visual studio so I've gotten into this habit whenever I save a template when I come back over to visual studio I close and relaunch it and the reason why is a lot of times the template won't show up in the correct way it is a known glitch but so we're going to close this instance our sales information vs project of visual studio and then just relaunch the application and reopen the project from The Opening screen when it's open now we're going to right click on our reports folder in solution Explorer hover over ad choose new item and you will see your long date format now if we hadn't closed visual studio and relaunched it the icon that indicates a report wouldn't be there it's just a blank and that means it's not going to work so I don't know why that is happening but now I'm just in the habit of doing this so we're going to actually use this template so click on your template and we'll name this report order information and then add now that we created the template right we really don't need the embedded code report so I'm going to right click on embedded code in the solution Explorer and I'm going to delete it okay so now we're gonna actually build a table report here and we'll reference our function our long date format function the code is already here we can check and see if you right click in a blank area go to report properties go to the code window the code is in there because it was in the template so let's insert a table here we're going to name our data set code example and we're going to use our shared data set and click ok and let's drag the table you know what to do here and widen it and in report data pane expand your data set and we're going to right click in view our grouping pane so we're going to drag order date from our data set down to the grouping Pane and drop it above details in row groups we're going to drag order ID underneath order date and row groups and then into the table we're going to drag quantity and transaction amount now we're going to do an expression that references our code our long date format code so we're going to right click order date in the table the detail row and we're going to go to expression and after the equal sign you're going to leave that Fields order date value there after the equal sign here you're going to type code dot long date format and then enclose the fields in parentheses and we're going to click ok now we're going to right click the order date expression and we're going to hover over add total and choose after and we're going to add the word Grand in front of total then we're going to right click the order ID in the detail cell add total after and we're going to put the word daily in front of that total I'm going to format all three of my transaction amounts as currency so by right clicking text box property we've done this before so you have to do it for each one so they're all the same way let's make daily total bold and we're going to format the grand total row with a blue background of your choice white 12 point bold font so blue background of your choice make the font white 12 point and bold and I'm going to do the same formatting on a header row let's add a page header section and insert a text box into it and we are going to move that text box accordingly and widen it and we're going to right click in the text box you get it in there right and we're going to go to expression so we already have our equal sign here we're going to just do it in the expression and we're going to do code dot long date format and then open parentheses we're going to double click our built-in fields and we're going to choose execution time again and close the parentheses and now click ok we can get rid of that extra white space not necessary I'm going to get rid of the extra white space underneath the report as well go ahead and preview your report because that code was built into the report definition in the template we're able to just reference it as many times as we want in the report to apply it to various date fields via expressions like we did in the text box or attaching it to that order date field like we did if we add more date fields that we want it formatted that way we could do the same thing so you can see how you can reuse cold easily we didn't have to go back over to the class library and copy the code and paste it into the report definition we saved the embedded code report as a template you can go back to design view and Save our second lesson here is extending reporting Services by creating custom assemblies so this is another way of including custom coding your reports and it's created using the microsoft.net

framework that you can reference from within your report definition files the way it works is the server calls the functions in your custom assemblies when a report is run custom assemblies can be used to retrieve specialized functions that you plan to use in multiple reports it's basically a custom function library that is available to every new report project created custom assemblies allow for consistent code reuse and simplified maintenance of standard code across multiple reports and projects once the steps are completed the dll dynamic link Library file must be deployed to the report server bin directory along with the windows assembly directory on the reports finally in the report itself a reference must be added for the assembly and then at last the assembly functions can be used and referenced within the report unlike embedded code in SSRS custom assembly code can be written in Visual Basic C sharp C plus plus and others we're going to be working in our class Library project that we created in a separate instance of visual studio and what we're going to do here is we're going to modify our function to start with so that it is available in a lot of different instances so what we're going to do is we're going to click in front of the word function and we're going to type the keyword public space and then the keyword shared the public keyword makes it available outside of the class module that it's created in now notice when we started in here when we first created this project it made the class public by default so the class itself is already public we're making the function public as well and in a shared keyword means you can access it without referencing the class so this is setting it up to be able to be utilized in a custom assembly now let's say you're going to have lots of custom code and some of it you want in Visual Basic and others you might want and I'll say C sharp for example so you would have two choices here right if you wanted to create code and C sharp you'd have to use a new class library that supports that language the one that we chose supports Visual Basic you could create another project or you could create another project within this one for your c-sharp code so that way you could have one class library with two different projects that support two different languages all in the same solution file if you will so I'm going to show you how you would set that up we'll go with the choice where you're going to create a new project within this one so we're going to right click on the top of solution Explorer right click on solution custom code VB we're going to hover over add and we're going to choose new project so we already searched for class Library so I'm going to go back to the search for class Library and so the last time we chose the classlibrary.net framework for Visual Basic right above it in my list is the class Library net.net framework for c-sharp let's select that one and choose next and we're going to call this project custom code see Sharp and it won't let us put the sharp in so we're going to spell it out yeah there's all those characters that you can't use in names here and we're going to go ahead and click create so now you have two different projects within the same solution you have your custom code C sharp and notice it's on the class one dot CS for C sharp here right we could have done CS there in the name and then you still have your tab open for class one for Visual Basic if we wanted to rename the project from Custom code C sharp to custom code CS we could right click on it in solution Explorer and go to rename and we can make that change there we can also collapse that if we're not using it so imagine you had multiple languages you could have a different project for each of them in the same solution so all of your custom code is in the same space we're not going to be using C sharp so what we're going to do is we're going to right click on custom code CS and this time we're gonna remove it and you're going to say okay the other thing I typically do in a class library is I rename my classes so in solution Explorer you can expand VB class one dot VB then you have your class one and we're going to rename our class we're going to right click on class 1vb and go to rename and we're going to name this functions date and press enter so you're renaming a file would you also like to perform a rename in this project of all references to the code element class 1. yes I do so I don't have to do it manually so now if you look it says public class functions date instead of class one dot VB we also have that functions date up here I would do a naming convention like that with the common word first so if I were going to have text functions right I would have another class and call it functions text so I can continue to add more date functions in this function State class and then if I had text functions or other functions I can have them separate in all of their classes as well and that would look like this we're going to right click on custom code VB hover over ad and we're going to choose class so we're going to name this class functions text and add it so now you can see we have our two classes there appropriately named conversely delete a class just right click on functions text and choose delete and now we'd like to save and actually let's close our functions date tab and then we can save all the next thing we have to do is generate a dll which can be referenced in our report server project so in solution Explorer we're going to right click on custom code VB and to generate a dll we have to build the project so go ahead and click on build on the list you get an output window at the bottom and it lets me know that my build succeeded I'm going to go ahead and close that output window now we're going to find the dll so we're going to right click on our custom code VB again and this time we're going to go down to properties on the left side of properties we're going to click on compile and you'll see your build output path so it's putting it in the bin file and then there's a debug folder the good news is we don't have to navigate through Windows Explorer to be able to get to this file folder so what we're going to do here is we can go ahead and close this custom code VB properties tab we're going to right click on custom code VB again and then we're going to say open folder in file explorer my version it's the third one from the bottom so we have to just double click the bin folder and then the debug folder and then we'll see our dll our Dynamic link Library file we need to copy that file and we're going to end up pasting it in two different locations the first location we're going to paste the dll file into is the one for debugging and testing and Report designer and so you can follow the path that I have in my address bar to get to the private assemblies folder and then you're going to paste our dll in there foreign there and now we have a second location that we're going to paste to and I will bring up that path for you as well so you can follow a path here in this address bar and when you get into the bins folder for the report server we're going to paste it into there so that is the setup for a custom assembly and now we're going to get ready to test it we are going to test our custom assembly in our sales information vs solution so our other instance of visual studio and what we're going to do is we're going to just add a new report so I'm going to right click on the reports folder hover over add choose new item we're going to use the default report template and we're going to name this report custom assemblies we're going to right click in a blank area and go to report properties and on the left this time we're going to go to references and this is where you can add or remove assemblies and or classes we're going to choose add and then we're going to click on the Ellipsis button to the right sometimes it takes a while to load the dot net references here once they're loaded we're going to go to the browse tab and notice for me it's still in the private assemblies folder so if you're not in the private assemblies folder you can use the drop down to navigate your way there and so you can see it C all the way down to private assemblies if you need to get your way there and once you're there you should be able to see your custom code VB dll in the list and double click it now let's say we also had a C sharp library in our other solution in the class Library solution we could add an assembly for that here as well so if you need to reference more than one assembly at a time you can and we are going to click OK at the bottom we're going to add a text box insert a text box for our report I'm going to just make it a little bit wider and we're going to right click in it and choose expression now when you're doing an expression for a custom assembly you have to use what's called a fully qualified reference it includes the namespace also known as the project name and then you're going to have to use the dot notation and have the class name minus the dot VB on it and then another dot notation and the name of your function so what I'm going to do and you don't have to do this I'm going to just switch back over to the class Library so that means our project is custom code VB our class is functions date we don't need the dot VB there and then if I expand that folder my function is long date format and we have to pass a date to it as an argument so we're gonna make reference to this assembly now so after equals you're gonna type custom code VB and then a DOT notation functions plural date Dot long date format and then an open parenthesis so we have to give it a date as an argument we'll use another built-in field so double click built-in fields and just choose execution time and then scroll to the right and do a closing parenthesis click ok and go ahead and preview your report so now you are getting the long date format via a public shared custom assembly that can be referenced using a fully qualified reference in any report that you choose to use it in so you learned that you could also save a report that has embedded code in it as a template and that's great you know but this gives you more flexibility because you can have an enormous amount of custom assemblies that can be accessed from the references tab let's go ahead and go back to design view we're going to save and close our custom assemblies report and we're going to redeploy this solution so I'm going to right click on solution and deploy solution in module 9 we covered extending reporting Services by utilizing expressions and embedded code and expressions that reference the embedded code and then extending reporting services with custom assemblies so we have been using Expressions throughout the course but then we got to do embedded code we did simple Visual Basic code and again embedded code has to be in Visual Basic code and we were able to see that we were also able to save a report with embedded code as a template so that you can reuse it over and over again and just have to create an expression to reference the embedded code we moved on to modifying our Visual Basic code so that it was public and shared and which in effect turned it into a custom assembly once we did that we had to locate the dll file and copy it to two locations and we were able to test our custom assembly and learned that in a class Library project you can have multiple projects within the same project so you could have a Visual Basic project you can have a C sharp project so on and so forth and when you add the custom symbolies into report properties you can add multiple assemblies at the same time using different languages and then you actually can just call the assemblies by using a fully qualified reference in an expression and making sure to pass any required arguments to it custom assemblies actually give you more flexibility than saving embedded code in a report template because you can use existing reports and go ahead and add the custom assemblies to it and use those functions in them as well before we get into our next module I want to spend a few moments just cleaning up a little bit in SSRS the first thing I have to do is swap out this brand package I can't take it a moment longer so I'm going to go to the gear and go to site settings branding and I'm going to remove that brand package and confirm the removal and I'll just leave it on the default one and then I'm gonna go back home to the home page now this is where it's been putting everything that we've been recently deploying out of visual studio so if I click on this sales information folder it has all of the reports on it so let's go back to home go back into SSRS video course folder and I'm going to delete this data sources folder vs I'm also going to delete data sets vs and I'm going to delete sales information vs because when I look in it it only has three of the reports so I'm going to delete that as well and then I'm going to go back home and I'm going to move data sources into SSRS video course folder do the same with data sets and with sales information vs so now we have all of our stuff in all organized for our course before we begin module 10 I just want to point out that this is where you will need to have the SQL Server mobile report publisher installed and that link to download it is in the software requirements Word document in the video description you may also want to download the power bi mobile app onto your mobile device if that's the vehicle you'll use on your mobile devices to access your mobile reports this module introduces the design and publication of reports that are intended for consumption on mobile devices such as smartphones or tablets SSRS includes support for mobile reports although as you now know the tools that are used to design and publish mobile reports are different than the tools used for paginated reports discussed in the earlier modules of this course so modules 10 and our last module module 11 both are dealing with mobile reports in this module we're going to do an overview of mobile reports in the first lesson the second lesson is all about how to prepare your data for mobile reports and then the third lesson you'll get an overview of the mobile report publisher with the mobile report publisher you can quickly create reporting Services mobile reports optimize for mobile devices in a variety of other form factors mobile reports feature an assortment of visualizations from time category and comparison charts to tree maps and custom maps you can connect your mobile reports to a range of data sources including on-premises SQL server and Analysis Services data you're going to end up laying out your mobile reports on a design surface with adjusting grid rows and columns and flexible mobile report elements that scale well to any screen size you'll then save these mobile reports to a reporting Services server and View and interact with them in a browser or in the power bi mobile app on iPads iPhones Android phones and tablets and windows devices if you like at this time you can go ahead and close out a visual studio and Report Builder as we can't use those apps to create mobile reports we're going to get started in ssms by locating and copying the mobile query now there are two mobile queries and here it is mobile query and mobile query by year you'll be accessing the one with by year shortly but for right now we want to locate mobile query and select and copy it to your clipboard and then we're going to switch over to SSRS we're going to be creating a new data set by going to the new drop down and choosing data set and go ahead and open report Builder we're going to use our regular shared sales information data source and go ahead and click create on the ribbon you're going to select the edit as text icon and paste your mobile query in and go ahead and run the query by using the red exclamation mark so you can see it has total transactions by order date and also by order year or it displays the year of the order date as well we can go ahead and save this data set we want to put it in the SSRS video course folder and we're going to call it mobile data and Save and you can close it you can go ahead and get rid of that where opening report Builder pop-up and you may have to refresh your screen in order to see the data set and when you do refresh you'll see your data set now that we've created our mobile data set I'm going to give you a challenge and have you create another one based on a different query on your own so the query that you're going to want to grab in management studio is the mobile data by year query you go ahead and copy that onto your clipboard and then you're going to come back here and use your new drop down and select data set navigate to report Builder and you're still going to be using the shared data source so go on and complete creating that data set and name it mobile data by year when you're done with it you can resume the video and when you're done we're going to click on the mobile data by year tile and up at the top we're going to select edit and Report Builder and go ahead and reopen report Builder and let's run it from within here so notice how the years are not in order we're going to add to this query underneath your group by clause you're going to type order by and then year and now run it again so the order by is the sort sorting clause and now the years are in order we're going to save it so that that sort is saved and we're going to close it and then you can go back using the breadcrumb trail to your SSRS video course folder now we're going to tour the mobile report publisher before designing our mobile reports so I'm going to access it from the search box and I'm going to just type mobile and it comes up on the list and I'm going to launch it so when it launches I'm going to give you a tour of the interface and we'll start at the top where it has a title bar and to the left of the title bar you have several new icons so it opens with like a blank mobile report just kind of like when you open word you get a blank document but if you wanted to create a new one from within here you can start by doing that first icon then you can open a mobile report save and save as you also have a server connections icon up there on the left underneath that and I'm going to be in the main body of the screen you can name your mobile report from where it says new mobile report and to the right it lets you know how many grid rows and grid columns it is showing in my case is five rows and ten columns to the right of that there's a drop down and you can see the three views we by default are in master view this is normally where you want to do your design work the next view I'm going to select is tablet notice the grid is not as wide and I'm going to go back and select phone and you can see what that phone grid looks like we're going to go back to the drop down and select master now to the right of the view drop down you have a color palette drop down that you can access to apply some coloration to your reports on the left side you have a panel that has four tabs by default you come in here on the layout tab and this is where all of your graphics are located your charts and they're in different groupings so it starts with Navigators and engages charts group comes next as I scroll down and then I'll see the maps group and data grids group so these are the types of visualizations that you can use to graphically represent your data in a mobile report the next tab is the data tab which is not really populated right now when we're building our report we'll be coming to this tab and you'll see on the upper right corner there's three icons this is where you can add data so you can connect it to your data source refresh all the data or export all the data and you'll see this what it looks like when it's populated in just a bit the settings tab you can title your report from here you can select your three letter currency code the start of your fiscal year the first day of the week and the effective date applies to time Navigators and time charts so if it's not set the current date is used so you can put in a date that's not today in order to make it the effective date for a Time Navigator or chart and finally at the bottom it has a check mark for enabling client data caching and then you have the preview tab which is where you go to view your report once it's designed so I've closed my instance of the mobile report publisher and come back to SSRS because there's another way that you can launch it just like another way you can launch report Builder so this time we're going to use our new drop down and choose mobile report and we'll let it open the publisher for us and so we're going to start by jumping into module 11 where we're going to design our mobile reports and before we do so we want to make sure that we have our right Server Connection so the last icon up here on a little toolbar server connections I'm checking and it looks like I'm going to click on edit so I can see the full thing and I am on the one that I need to be on if you not you can either add a new one or like we're doing right now edit the existing one and then you would choose connect we're going to start by adding our first visualization to this report grid and underneath charts in the layout tab the first one is a time chart I'm going to click and hold on it and drag it up to the upper left grid box and a couple of things happened when we did that first at the bottom of your screen a visual properties panel open for that time chart secondly if you look and this is really more attached to the grid the gear you'll notice when I'm on any of the grid boxes the gear is there and from that gear I can delete cut copy paste redo and undo so the first thing we're going to do is resize this visualization I'm going to use the bottom right hand corner click and hold and I want it to be three columns wide and three rows tall now if you take a look at the values and the years in that preview kind of of the chart you'll notice that they are not connected to our data set and wide world importers we go from January 1st 2013 to May 31st 2016. so this is simulated

table data because we haven't connected it to our data set yet we're going to do that now let's go over to the data tab now on the data tab you'll see the report elements we only have one our time chart and we'll see what's known as a simulated table of data and if you look down at the bottom in the data properties pane you're only seeing one Tab and that's for the simulated table because we haven't yet connected it to our data set so what we're going to do in the upper right hand corner is we're going to add data and you can add data for a mobile report from Excel directly or from report server in our case we're going to choose report server so now you're going to select the server and you're going to select your folder and we're going to grab our mobile data data set so now if you look at the bottom of your screen in the data properties section first you'll notice you have two tabs one for simulated data and one for your mobile data and that's the one that it's on and it's showing data from our data set to the right of the mobile data tab you have that same gear so you can remove it so on and so forth you even have the ability to export it to excel from there if you want it to now let's go back to the layout tab for a moment and you'll notice that the values and the years have not changed even though we've just connected to our data set so let's head back to the data tab to address that and we're going to address it in the data properties pane so where it says main series it's still on the simulated table we're going to select the drop down and choose mobile data and now we can go back to layout View and now you'll see that the time chart has updated using the data from our data set so now we're going to change the chart title from time chart 1 and just so you know it's not really a text box placeholder that you can get into and you could literally click until the cows come home down here is where you can change the title in the visual properties so we're going to call it all sales now we're going to select the number gauge visualization and we're going to place it in the grid underneath our time chart and we're going to expand it so it's three columns wide and two rows tall we're going to change the title to all sales and we're going to go over to the number format visual property and change it to currency with decimals now we're going to scroll to the top in our layout Pane and we're going to grab the time Navigator and drop it to the right of the time chart we're going to expand it to the right by two rows now we're going to add a chart a data grid so I'm going to scroll to the bottom in the layout pane to the data grid section and I'm going to grab the chart data grid and drag it underneath the time Navigator and I'm expanding it to be three columns wide and four rows tall and now we're going to go down and change the title of the chart data Grid in visual properties and it's going to be sales by order date now over to the right you'll see a row numbers property and we're going to do the drop down and select hide so if you want you can do the drop down and select show and it adds row numbers we don't want them to show at all so we're going to set it to hide and we're going to go to the data set via the data tab and we want to make sure that we select both so we have data for the grid View and reference data for the chart visualizations we want both of those to be mobile data if you look to the right you'll see a data grid column section and from here you can change the order of the columns in the data grid you can add another column add a chart column so on and so forth we want the order year to be in the first position so in front of it that gray box I'm going to put my mouse on it and just drag order year up above order date and then we'll have order year order date and total transaction amount in that order now at this point let's go ahead and go to preview and see where we are with our report foreign so the cool thing about the time Navigator is this in the time Navigator if I click on 2013 the other charts updated to just show me 2013. if I go back to the left of the Navigator and click on all everything goes back to all pretty cool so time Navigators are really cool and it doesn't seem to be working on the order year column here maybe because it's how that is formatted but let's do the back arrow to the left of new mobile report to get out of preview so back on the bottom of the data tab in the data Properties Group we're going to go over to the right where it has the data grid columns and we're going to choose the options button next to order year and you'll notice it has a string format of General we're going to do the drop down there and at the very bottom we're going to select none and we're going to choose done now if we go back to layout view we'll see that the order year is now in the appropriate format now that we've fixed the format on the order year I can tell you that that is still not going to resolve the issue with it not responding to the time Navigator let's go back to preview and in your time Navigator click on 2015. now you'll notice that all of the reports kind of update it right but the problem with the grid is this let's go back and we need to go back to the data tab for that grid and I'm going to go back to layout View and I'm going to name this new mobile report I'm just going to call it all sales report let's apply a theme to this so in the upper right hand corner the last button choose a theme that you can live with I think I'll go with the spa theme and you see how it impacts your report now we're going to create two more views a phone and a tablet view so we're going to go to the view drop down and select tablet now on the left you'll see order report elements and you have to place them on the tablet grid this is what my phone view would look like and now I'm going to go back to master View and it's time for us to save this report to the report server so go ahead and choose your save icon at the top so you can save mobile reports locally or to the server we're going to choose save to server so it's going to save it to the root to the home of my server and under location I'm going to choose browse and choose SSRS video course and we're just going to leave it out there in that folder so we're going to select choose folder and then save so now that we've published our mobile report to the report server you'll see there's a category mobile reports and there's your report now it can be accessed by clicking on it from the browser but typically it's going to be accessed from a mobile device a phone or a tablet so let's go back to the SSRS video course folder using the breadcrumb trail and you'll see what it would look like in your power bi mobile app in just a little while but for right now we're going to add another component when you View mobile reports in the power bi mobile app on your mobile device you can view mobile reports as well as kpis so we're going to create a kpi we're going to start in the management Studio where we're going to construct a very short query to extract data from a table that we're going to use as our data set for our kpi so if you're not familiar with SQL queries I'll walk you through this an

2023-02-28 19:05

Other news