Shared data set which is basically like creating a query to select the columns and rows you want to see will then show you how to use a shared data set in a report to display information to the.
User finally we'll talk about the difference between shared data sets and embedded data sets and how to create an embedded data set within a report so let's get started if you want to display.
Information from a database in one of your reports you'll need to create both a data source and a data set a data source is really just a pointer to a location that holds all of your.
Information while a data set is a specific collection of columns and records that are displayed in a report you can create both shared data sources and data sets.
Data set that shared embedded data source and data sets are created at the level of each individual report and those are actually created in the report data window which is invisible at the.
Moment to start with we're going to create a shared data source which points to a database stored in a Microsoft sequel server in fact I would quickly switch into sequel server management.
Studio you'll be able to see the database I'm interested in it's called movies and is stored on a server referred to as Dell Vostro to slash SQL 2012 so to create the chod data tools.
Which point to that database in the solution Explorer right-click on the shared data sources folder and choose add new data source the dialog box that appears then allows you to give the data.
Source a name which I'm going to give a straightforward symbol name using DSC to represent a data source I'm using a Microsoft sequel server data source can use things like Microsoft Access.
Posts Related:
Oracle databases and generic ODBC data
Sources as well I'm gonna stick with a Microsoft sequel server type the next box down here allows me to specify the connection string now if you don't know.How to type out the connection string you can click the edit button to build it I added my connections drink the server name this is just that showed you in sequel server management studio.
The name of the server containing the database so I can quickly type this out once I've typed out the server name I can then choose which database on that server I want to point to in the.
Drop-down this down here select movies and then click OK it's possible to test the connection if you want to reassure yourself that it's working but I'm reasonably confident that's going to.
Work anyway so let's click OK the connection string is displayed then for you and click OK again and there's your shared data source once you've created a shared data source.
You can then create a shared data set which specifies which columns and records you will display in a report to do that you can right click on the shared data sets folder and choose add.
New data set the dialog box that appears then allows you to specify a name for the data set and we'll mine DTS films I'm gonna show a list of film names and other details you then need to.
Select which data source your dataset is going to use now if you have already created one you can simply select it from this list it's actually pre-selected for me alternatively you.
Can use this new button to create another new shared data source in the query box you can if you like type out a full SQL query so if you know there the syntax of select and then the field.
Names
Princeton's film name and from TBL film you can do things this way and you might full SQL statements in there I'm including order by clauses where clauses.Etc you could also select from a list of store procedures that may be stored in your database if you select the store procedure option you can then select from a list of store procedures in the.
Underlying database alternatively if you don't want to do either those two things you can simply use the query designer which by far and away the easiest way to build a query if you're not familiar.
With SQL syntax if I click the query designer tool I'll launch a graphical user interface which allowed me to build up a query the starting point is to select a table that is going to be used.
In my query I right clicking the background here I can do to add a table and the dialog box that appears then gives me a list of all the tables stored in the underlying database I'm going to.
Select the the film table here and then click the Add button I close them this window I'll see that my table of the films appears in the top part of the window the third part part of the window.
Third panel shows me the basic SQL statement being built up as I start to check boxes in this list to include extra fields so I want to include the film name field I'll see it gets added.
To this second panel and then window and then it also gets added to the Select list in the third panel I can simply then carry on checking boxes to include all the fields that I want to include.
I'll include three at this point I think I'm also going to choose to sort my fields saw my records in order alphabetical order film name so for the film name called muse the second panel.
Of the window in the source type box select ascending or descending and once you've selected that you'll add an awed by clause of the SQL so it's actually quite a neat way to learn how SQL.
Statements get built once you're happy or your theme got you think you're happy with your app with your results you can try to execute or test the results of your query I run this query I'll see the.
Results spat out into the fourth panel at the windows I'll get some idea as to what data I'm going to see in the end results various other things in doing the query designer is relatively.
Straightforward to work with but at this point I'm pretty happy of what I've done so I'm going to choose ok I'll see my SQL statement has been written out for me in the query box choose ok again and.
Then we go i've created a simple shared data set that's ready for use in any report once you've created a shared data set you're ready to create a report which.
Will display the information stored in it so to do that right click the reports folder in the solution Explorer choose add and then choose new item choose the report item and then give the report a.
Sensible name I'm going to on line number two shared film list click the Add button and your report will be created now before you start adding a report items to the report we need to.
Make sure that this report itself points to one of our shared data sets so to do that we use the report data window at the bottom left-hand corner of the screen find the data sets folder right.
Click onto it and choose add data set now all we're going to do here is choose to use one of our shared data sets and as we only have one in this entire project is a fairly simple choice you.
Can give your data set a central name within this report as well I'm gonna leave it as data set one at this point just select my said data set DTS films and then click the ok button as soon as.
I do that I create a reference using a little shortcut icon here increase reference to my shared data set so any items contained in that dataset are available to this report the one most.
Could be an advantage or disadvantage in fact if I change anything about this shared data set then all the reports that use that shared data set will inherit the same changes to actually.
Display that bit of information all those three columns in the report all I need to do is add one of three types of items I'm going to add a table that's the simplest way to display the.
Information and I double click on the table item and then I can either use the field selector button that appears when I hover the mouse over various cells if I click on to this field select it.
Allows me to choose film name and then for the next column I can choose film release date and finally film time in minutes we'll have a separate video that explains a lot more about how to work.
With tables properly there we go at that point we can display the information in our report if each of the reports are you creating a project aren't going to be based on.
The same set of data then it might make more sense to create embedded data sets rather than shared data sets so in order to do that we're going to create a new report we're going to YouTube by.
Right-clicking reports choose add new item measure I've selected reports and I'm going to call this one number three embedded film lists will create exactly the same dataset that we created as a.
Shared today set earlier but this time we'll make it an embedded one so when I've created a new report as you need to do two things here I can't just create a way of creating an.
Embedded data set because I need a data source to point it to the data source must belong to the report that I'm working in so the first job is to right-click the data sources folder in.