Video: Building and Customizing Reports in Business Intelligence Development Studio

Presenter:Bob Tabor (from LearnVisualStudio.NET)

Building on the previous lesson, we dive into building reports from scratch and modifying the reports that were created using the wizards and designers. We also demonstrate how to embed your reports into your ASP.NET Web Form applications.


Click here to go to original video page

The following text is a software generated transcript of the video. Click on a minute link to jump to a location within the video

Minute 0
The previous video I showed you how to setup and configure reporting services and that I demonstrated how to prayer reports using report wizard that s fine if you want quick results but if you want free more complex or more detailed reports and had to learn the techniques required to customize or build reports from scratch so are we covering a wide range of topics in this video until customizing the appearance and the format of your reports that three of the video showed how to embed reports within your ASP net applications it might be the easiest thing that you do all day so I may use the upright that we created in the previous video and I wanted to his avenue reports on a demonstrate how to create a very simple report from scratch without using the report was or someone adds a new light on and select report not report wizard to
Minute 1
On and will call this a simple and and okay so the first method used to find the data set herself so I m going to put the combo box and select new dataset and can give the dataset and name call this simple will choose the data source we have a sure data source will continue to use that one and we can either select a stored procedure to retrieve our data or we can select text and I m 40 jobs of a query on the keyboard something that we use from the previous previous video changed a little bit about your hands and we need to set the timeout how long shallots to run before just times out and for now that s the only thing a simple click okay and will test our query mature presents data in now with layout tab and we are going to loan
Minute 2
Toolbox in the Darfur moment and we ll just first of all actually let s go back to our datasets window and let s pull out a simple piece of information for example let s pull outs of the total to onshore service here and with preview it and notice that it brings back up a value but unfortunately this is an will we re expecting we want to see a list of all of the total to values not just a summary of one value so although we can drag and drop values out of the body of our report layout that s not bizarre behavior in this case we do instead is drag drop table into the body and see to see here that we have a header row a detailed road of footer row and were going to expand this in a example dispute moments of high groups of this work for now
Minute 3
And you can resize these columns and then also to start dropping data into the skull so for example was correct for datasets and will drag and order dates and notice when I do that automatically gives us a header value will take these subtotal and then Deion totaled two distracting and dropping them into the the detail area of our report and was previewed now have a very simple report and we built that report from scratch let s listen more dance functionality lets go back to the data tab subs have and are parameterized queries have ever not hard coding it just for one day we ll put start dates
Minute 4
That s similar to input parameters for thoughts for procedures and then end to it as well and now whenever we attempt to run this test or query really get a defined query parameters dialog to specify the date now alternatively we can select a null or blank dates is to test those values output will want to type in will go from 81 2001 91 2001 and see what our results are up and do something to get results in a large results and see some of the duties go out to the private future so malice it with you what that does to our preview and just something
Minute 5
Notice that we have in the preview space root and type in the input parameters for our records and we can hide those or show those using this show or hide parameter area who will want to define those of 181 2001 and was coaching at 815 2001 and click view report and now we just get the report for those days for pages long and through that and at this point if we were to deploy our reports go to deploy these out in a moment here seems like it works successfully picked right up and see what happens whatever our users will do this report are a simple report
Minute 6
Is that they too will see this area where they can type in their parameters so we might go 715 2000 181 2001 in the report and within a few moments will get that report the accident very needs were able to enable this functionality sing along would take for us and we were at we had to build this using ASP net or some other type of web based technology that wants to get proficient integration of these reports and start spewing out reports of very flexible than in this case parameterized in just a few moments but is was he playing with this reports little to do with it and one of the things we could do at this point is make the report look of the more attractive the top of his previous video how we could select a given cell and changed the the background color of this case we could change from transparent to
Minute 7
Have something in the blue family colors and change the font style and size the way to things of that nature to really make the track the report look older and more attractive also will want to put a header and footer on every page of the report so the header we might want to just does say what what the report is from page 6 is still no of what the title of the report is in and at the very bottom maybe we ll give them the page numbers so to do this go to does a Manila looking caller it might be washed out the video but it got a light yellow color and select the page headers now we can see a page header area and it also does it make for page footer to now are able to see the header and footer for each of the pages and the first single do is go to the toolbox and drag outs in the text box and
Minute 8
This text box area is both something that we can type static text into so this is the name of the reports and we can also do something with the text box is a little bit more oh on a programmatic so we can take the name the reports of deaths is a verbal implicit in here through the use of expressions so in this Xbox is why click it s inside of it and select expression we get the expression editor and for example perhaps we ll go to globals and select the report came in when I double click it it appears here and so uses a VBScript I ll syntax to a reference of global objects report name property is so a step in that okay
Minute 9
Ashley previewer report lets change of heart data source who don t have the type in starting and every time so let s just paced back and on the original criteria we have for a where clause estimation networks and provides the layout and a right click inside here what s inside of a car that yellow area again and select report parameters and went into the two parameters you don t do this that will continue to get to be asked for those parameters so now we ve made that changes when previews the reports and will notice that the word simple the name of our report appears here is the dynamic text at we created expression editor and also this other text box that says the name of the reporting also see we have some formatting and says you can also see the parameter area has been that we no longer are asked for parameters as for
Minute 10
Smells good of footer section was little room for ourselves here hold us down and will do something similar will wear will allow a user text box and will print more complicated expression this time and uncertainties and expression credible than earlier that will take the page number some looking at the globals you can see that we have a verbal call page number and verbal cultural pages and so formidable as the strings and detonated it is together with some strings of pages and now whatever we run our reported to see the very bottom of every page 1 of two pages and then
Minute 11
Two pages is so using expression editor you can actually get that s a pretty complicated expressions that not only involves a global values but also feels from lower datasets and then also we can combine those with various arithmetic and strain in date math and aggregation financial functions as well and this kind of reminds me of working with excels expression editors well onto the lighter note using wine objects you can differentiate this part of my order from the rest of the page
Minute 12
You and there are other objects along those lines like the images for example was going to put an image at the very beginning of our reports and when I drag and drop the image control from the toolbox onto the designer surface we get this welcome to the image was heard it simply asks if we want to embed the image within the report or who want to store the database or put it on the web through and access it through your else single had embedded for now select new image and I happen to have in my documents and adventure which logo so I ll put okay and select next and click finish and now we have adventure Works logo here in the bar of the body of our report download the language well aesthetically correct and it will did another sexpot
Minute 13
Is a performed error handling life is and what are some full sales reports can select a larger font size and is we can make them look really nice and also lets the wind here as well drive of the data and sated and can spend all day kind of tweaking are reported seeing what look like that s pretty sick and need the other thing we wanted you on the first page of the report is suppress the header and footer so that looks correct so it s really easy to do that as well out here
Minute 14
Window and a few endless selects the page header and is a property called print on the first page of the last page are both set to true drama set different on first page defaults on the same thing with footer fundamentalists and a footer and will set print on first page of the false well now where preview is he doesn t appear on the first page of a good second patient to see both the header we ve defined as well as voter must make two small additions to this report and it will try something else back to datasets and I want to get the sum of total do the very bottom of this report so I m going to drag and drop total do into the footer of my table and noticed that smart enough to know the
Minute 15
Activation of all the values that were previous to that so some of the fields are so more of total two value and some was also to do this it s more room for ourselves here to try to move this now and get hold of it and ago and maybe I want that value also here run underneath the simple sales report so why much of that case is dragging other text box house is from a couple of ways to do this but I won t go to the expression editor again in a type in another expression so in this case I want to a queue of formats and they give us some endocytic IntelliSense and here that s pretty of fields in the field that we want is totaled 2 g value
Minute 16
Property and then only use these Visual Basic formatting strings so what do I do see zero battle concurrency with noticeable places solicit ahead and type this up correctly looks good will see what looks like whatever we attempt to preview it and you see here that I see for the day range specified via the total cells as 2 077 800 something in front of the very end of this report look at the very bottom noticed that I also get the same number here in the footer of my table got so very a baseless rest of the complexities little bit in the previous video we took a look at greater matrix reportedly did this purely from the
Minute 17
Wizard but what if we wanted to create this on our own values and report Wizard server to do that solicit boat wolves and select add new item commissions report recall this just matrix the RBL and a need to specify a new dataset and for simplicity sake of a use the same crew ensuring that I use in that other reports is because I know that it has the data that I want to view and click okay here and tested to make sure we get the data rate malice with the layout page and open the toolbox and random matrix control on my body and I would like datasets window and its splendid and accountable
Minute 18
Okay so to cut a rebel table we did on the take the order your drag it up to be in the column and then underneath it on a drag the subcategories are coming against this or I can seem or my personal place directly underneath of it in secret in the second of a role for me for that column in the city will be true for the product category of drag it into the open row fields and in the subcategory on a try and place it after that second battle after cell to play with the target just corrects and dropped a nicely excellent and then drag and drop sales because it s more again to understand that might need to summarize things are okay so this point we should have something that is reviewable and unfortunate facts matter
Minute 19
Is that the vets of the report Wizard did a lot for us it s formatted nicely for one thing it also added a little classical areas which we don t have to listen at those in ourselves back to layout and must select a subcategory because I wanted that to be collapsible or right click it and I m going to select on at its group and then grew the visibility tab for this route and when a select initial visibility should be hidden at the visibility can be toppled by another report item which report item should be able to trigger that will a product category the one that was about it since he is now saved this preview
Minute 20
And noticed this time that we have the little classical areas so great was the back and do event for our and you drag the wrong item and careless when pulled that out Dragon quarter instead or sense there ago and will right click and select a group and good visibility hidden topple over here okay save it preview it and now we get to quarters as well and we can spend the time that it would take to format this and make it look as nice as will we did previously but were not as a company that is now but could you imagine trying to do something like this using ASP net or PHP or some other technology would take quite a long time since beautiful and allows us to put together these very interactive
Minute 21
And very informative reports is taken over that is really neat okay so let s continue on now to demonstrate how to print a report that even sort on hand and soul select add new item report will call this sort great new datasets and I m going to paste in that long query that we ve been using slow to pay tested to make sure works excellent of them in little layouts and with drag and drop table onto our design surface and then you better guess that I m going to select product category each subcategory and sales as well and so what I ll want to be able to enable it is sorting by the products
Minute 22
The product categories so well do is go into this text field for the header animal right click inside here and select properties go to be interactive sort tab on the tech stuff text box properties and click add an interactive sort action to this text box and I ll want to select the sort expression on the selects the resort by the product category values that we could open up the expression editor here and it s create some very complex of sorting expression gravity that right now is very simple ourselves and say this aunt was previewed to make sure that works and now see we get these little up and down arrows and so I sort based on the product category you do the same thing for the subcategory right clicks
Minute 23
Properties of the interactive sort tab look at it at interactive sort action and right click the sub category sort on this time and preview it agencies now we can sort by the subcategory that he needed a report that is somewhere between automakers report that we saw with all its neat role of functionality but maybe not quite that complicated maybe more long lines at the simple report them together that this is a kind of very own on one level reports we might want something between insult you how to group things together so due to a right click and select add new item reports and templates for all this route to our DL actually listed as as though the report Wizard and to take a look at what you
Minute 24
RW_group to look at what the report Wizard can do for us in this regard are glossed over this whatever we were working with the previous lesson and will try to come back and created off from scratch on a row so let s just click through here let s paste in a query that we ve been working with the select tabular and on this design the table of this or we will then use the detail area was his group area so let s put some resembled the product category and subcategory bear and then place the seat held what is the year for sales in there that and assess the type of reports we want to create whether steps you need to seek out above preview what will look like here we have a product category in the leftmost column in the subcategory in the next column and that all the details
Minute 25
Low that it would also include subtotals and drill down very needs the option here would be to set it to blocked I m not as crazy about that single format but that s simply an option for useless continue on style season fault part of the group sounds great with finish and Alice preview what this looks like a pet so again summer between the tabular view and the matrix view and roll things up and then also see some group being a higher level drilling down with a coarsening Forza Italia rollout to be the sum total there looking at the sum total for all accessories across all quarters so is very neat Sahadi do this on your own with
Minute 26
Using report Wizard of vessels close a downgrading of the report and I ll select add new item reports will does call for this route so fed will have to create a dataset crew than using okay so the test and natural gas data for an so ominous is one of these little layout and we re going to drag drop table onto the design surface and include datasets window and will well and hosting fee of the sales so it is a little less By only using the sales values well as we ll had quarter and
Minute 27
Okay so were to preview this the report that we ve been seeing this point very flat and why hasn t grouping to it so in order to accomplish that going to right click on the leftmost column and select insert group referred to groups the first group will be a grouping nonprofit category so reported group bought an expression in the expression will be fields product category and will select just okay and were going to drag and drop the name of the product category into our group so they ll be displays
Minute 28
We ve seen now to give us some good work components here you can see that we did a bunch of data but still not quite right because the seeing duplicate data for a quarter for 2002 of the reasons because the shape of the query that we brought back also has this fall far of subcategories that grouping correct order right click and select to insert another group and this time the group will be on subcategories and its parent group will be product category and click okay there and will drag and drop category here and preview that you see this little better now components for mules non frames and downed more correctly continued work with this will want to do is see a role of values and for the sales
Minute 29
Data you drag and drop sales so get role of value for subcategories list preview that nothing look on the same line as forts and can see their sales value over here which is a day of a sum of all the quarters together and the same can be done with the category of the whole by dragging and dropping this field in the previewing can see now we have a total role value for all components across all quarters now wanting to we didn t have was little expand and collapse areas and 40 shown how to do this on Wednesday regular working just will quickly by right clicking select properties going to visibility selecting hidden
Minute 30
Will select that the visibility hobbled by the report item with any luck entered its product category okay and we might even do the same here as well solus right click and of visibility hidden visibility can be toppled by on the subcategory see this works okay so see that I can expand and collapse for us right I would have to do this for all the values you normally really left in expanding the dates here so that didn t quite work as well as planned by the beauty idea of how this would work we were is altogether
Minute 31
And continue on from there because while grouping is nice but everybody really want to see is charting features so let s right click and select this right click and select new item were going to print a report called shrub s and select add in personal freedom deficit into the September times and it smells the toolbox and drag and drop aid charts on doors on the surface classical reversals are endless of the right are trolled around and move its rights position even resize it here want to but ultimately he will more ask to do here is to drag and drop some fields of the data fields
Minute 32
Go there so we want with what our sales data would drag and drop our series data here so that might be something like are dates and quarters and then will drag and drop the categories there will so do our dataset is seeking if this were a drag and drop sales into this area will drag order of year are into our series fields snide concede that will have your public 2001 2000 to 2003 and then will take our product categories and drop the year of us all we don t see what we come up with a reporter from forgiveness to pitch sages and it could be that of
Minute 33
So a very simple sharks for each of our product categories gives us these sales for 2002 and 2003 struggle to find this and let s also use a quarter and let s see how that affects so there are some limitations here press we tried one more thing to something a bit that must read this you read the year and in the product categories for fun and it s a fat okay so very good as you seems very small will solve the more but it s separate by quarter ended by some
Minute 34
Are located in like a wave that s this looks we can do is right click and select chart type or get lots of different types of charts and pie charts so let s go ahead and put the said because of the different data in here and select order quarter and just at the delete key and the keyboard seem to be true subcategory of this drag and drop category this will make it a less cluttered and the preview that it was so more space to work and that seems to work better
Minute 35
And also the properties dialog for the charts and to modify the main color palettes and we want to use on the stylings of the borders and fill colors we want to get that specific we can set the data here is a drain dropping it as I demonstrated we can adjust the legend where should be displayed when one of 3 D effect or not is needs and other informational filters and so on and so on safest and will demonstrate one final one final technique that we can use to link our reports together social back to our
Minute 36
Future toward that we created and will want to do on this report is bad and text pops and typing here is view high in charts for this data right now to right click and select properties and select the navigation tab and and then will do is select hyperlink action to jump to reports and in this case were going to select a short report now with previous report and click on view pie chart to this data and it will hyperlink us to our chart will
Minute 37
Currently you can see that we can add other types of hyperlinks as well or links rather you can jump to a bookmark on a given page for jump to a URL somewhere on your you could use the bookmark IDE on a given report section to define a bookmark and then allow others to jump to it from the same report war from different reports okay so we covered a lot of roundabout creating custom reports and using a lot of tools in the toolbox and showed a lot of different techniques for for printing reports blessing never going to demonstrate this video is how to integrate the reports that we created in today s net Web form application ahead and save Irving was done in fact let s deploy everything else don t know
Minute 38
And now we were to go to report manager and go back to Bob s reports we be able see quite a few new reports here for examples look at a chart but want to want to integrate this chart into our application and not for sorry user to come through the media of the report manager slow slide show you how to do that and shut everything down and Kirsten really need to do is download the add in for a visual Web developer 2005 express edition that allow us to work with the reporting control so really go back to be MSDN Microsoft com express that will redirect us to the correct sites them and then once you re here go to visual Web developer of download if you scroll down the very bottom of that page
Minute 39
See it download at inception and there s a reporting add in for visual Web developer 2005 express so download that file to your local hard drive for 42 liberty doing that so let s go ahead and put my downloads folder and database we will of install the SQL Server 2005 report added MSI and I worry actually gone through the steps here it s very easy to do is follow and take all the faults now that I ve got set up on my local machine were going to run a visual Web developer 2005 express edition and created new websites and we ll just call it some reports viewer
Minute 40
Fantasize all that new MSI should see in the data section of your toolbox and report viewer control were so drag and drop it onto our designer surface low quick tasks of menu pops open or we can choose report was to server and then from that server we can choose the report path so here we ll put some Bob s reports charts and say this and run the application and better synergy can see that we have our default aspx page with our report viewer can
Minute 41
Oh with our shark got our deal file loaded it and we can resize things but we still had that familiar little a menu bar at the top we can adjust some of those properties on what the user would actually see by selecting this is stop this curriculum and at the very bottom of the report viewer controlled properties we can enable and disable various controls of whether I would allow exports to find controls the page navigation print button on the refresh button is in control because we can turn all that off and so the viewer can only see the report itself so that s pretty good so we accomplished quite a bit of very short per time of need look at how to build both tabular and matrix reports from scratch in regards to the tablet route
Minute 42
Or that we build we added parameters to it we add a header and footer section within the header and footer we used the the expression designer to display the report name and page numbers for a line in the footer we looked at using text boxes and use the text box and use the expression designer to display a total value with a logo on our reports and suppress the header and footer for the first page we built our own matrix reports from scratch even enabled the drill down capability looked at sorting hands gripping the data within our reports rebuilds charts in this case a pie chart showing a ticket tax bills and how to just as properties of the hyperlink of the reports and then finally we displayed a report within our report viewer control within an ASP net webpage
Minute 43
But there is a lot more to to learn we re only really scratch the surface here I did want to show you this resource at the very beginning of the previous video I showed you some reports that were actually from the adventure Works database and I installed this SQL Server samples MSI which gave me this is fine here drill down to the program files and we look at SQL Server 90 samples reporting services report samples adventure Works sample reports and the load data into Palm business intelligence development studio by double clicking the vessel and file you can see some techniques that they use of bills was really neat reports off of that database so take your time and
Minute 44
Suspect those look through each of them and see how they actually go about configuring some of these things for example how did they get this to work with selecting a given employee and then having it a display of just the that single employees yearly sales comparisons will and then here s another of a interesting report on sales detail on how to create like a receipt and so severe he needs techniques that I learn a lot from his eye went through this and I know you will too write the luck and have a lot of fun writer reports