| Author |
Topic |
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
 Posted - 2009-07-06 : 11:15:51
|
| I am new to reporting services and have a database named MHC. I used this as a datasource for some reports. I am getting confused now as I used MHC as a dataset as well, and I have an idea of the difference, but not a complete understanding. Could someone explain this to me? Is a dataset really just a SQL Select statement on a larger datasource? Or what? I am using the Reporting Services part of Business Intelligence Development Studio. I complete a report and then upload it to the ReportServer website. That site has datasources, too, and I don't understand the relationship between these two, either. Does this site have to have the same dataources as the report that cam from Business Intelligence Development Studio.Duane |
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2009-07-06 : 11:47:16
|
| A dataset can have multiple datasources.So you have the dataset MHC, which can have multiple datasources for example MHC_DS1, MHC_DS2.You want to use the datasource to point your reports.Hope this helps.Regards |
 |
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2009-07-06 : 11:57:26
|
| Thanks for the reply. It still does not tell me the difference. For example, I started a new report. I had to supply a datasource, so I made it the database MHC. That way I could select any tables or views from it. (I don't know if that is correct thinking or not) What I am actually trying to do is convert some reports from another non SQL Server reporting tool. In that tool, there are SQL Statements for the query part of the report. So, what I did was use the datasource MHC as a dataset (so I thought) so the SQL query would be able to get everything. I guess I am not understanding the process.This is what I have to do:1) Select a datasource which is done - I think. This datasource is the same in the other tool.2) Then, I have to take the SQL from the other tool and use it to create a the query for these reports.3) Choosing a dataset for these SQL statements is the confusing part. They are pretty complicated and I don't know how to create a dataset for an existing SQL statement.Duane |
|
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2009-07-06 : 17:15:31
|
quote:
Originally posted by duanecwilson
Thanks for the reply. It still does not tell me the difference. For example, I started a new report. I had to supply a datasource, so I made it the database MHC. That way I could select any tables or views from it. (I don't know if that is correct thinking or not) What I am actually trying to do is convert some reports from another non SQL Server reporting tool. In that tool, there are SQL Statements for the query part of the report. So, what I did was use the datasource MHC as a dataset (so I thought) so the SQL query would be able to get everything. I guess I am not understanding the process.This is what I have to do:1) Select a datasource which is done - I think. This datasource is the same in the other tool.2) Then, I have to take the SQL from the other tool and use it to create a the query for these reports.3) Choosing a dataset for these SQL statements is the confusing part. They are pretty complicated and I don't know how to create a dataset for an existing SQL statement.Duane
Your procedure to do this is exactly correct. You have to make sure that all the tables and fields in your "other" reporting tool match the tables / fields in the MHC database. If not, it will not work (of course)Do they match? |
|
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2009-07-06 : 17:29:12
|
BTW,The dataset is where you tell it what datasource you want (trying not to confuse you here)So to create a report.1) On the data tab there is a dataset. The three dots next to it will allow you to edit the data set.2) Click on this option. From there you can see the data source. There is also three dots next to this field which will allow you to edit the datasource.3) From this screen look at the connection string. This should point you to where your datasource is located. If you click the edit button then you can go into the connection properties.4) From there you can select the server name (ask your DBA if you don't know it). Then you can drop down the "Select or enter a database name." One of the options on your list should be MHC. (make sure to drop down the menu, this will varify that the server name is correct)There you go. You now have a dataset with the correct datasource. |
|
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2009-07-06 : 18:02:32
|
| Thank you for the responses. Now, the question I have is, when I am creating a report, there is a query grid with the SQL, the results, the diagram, etc, and then there is a button to edit the dataset. This looks like it brings up the same SQL. Am I correct? And if so, I assume I can "edit" it from either place. My last question is, would this be where all the grouping and case logic goes, or does it just go in the report design screen?Duane |
|
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2009-07-07 : 11:32:06
|
quote:
Originally posted by duanecwilson
Thank you for the responses. Now, the question I have is, when I am creating a report, there is a query grid with the SQL, the results, the diagram, etc, and then there is a button to edit the dataset. This looks like it brings up the same SQL. Am I correct? And if so, I assume I can "edit" it from either place. My last question is, would this be where all the grouping and case logic goes, or does it just go in the report design screen?Duane
Yes, the Query string in the dataset is the same SQL as the data in the data tab. You can edit from either place but I would recommend editing it outside of the dataset properties. (i guess it doesn't really matter however)The grouping and case logic will go in the layout tab under the row / fields properties.The groupings logic is in the ROW properties. If you right click and select insert group below, a grouping and sorting properties dialog box comes up and then you can enter in the groupings logic.The case logic can be done in the sql code or in the "Value" field in the textbox properties.Exp: =SUM(Fields!MyFieldName.value)Hope this helps. Good luck. |
|
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2009-07-07 : 19:06:35
|
| This does help a lot. I will test it all tomorrow or the next day. Thank you.Duane |
|
|
|
an279505
Starting Member
2 Posts |
Posted - 2009-09-23 : 16:55:19
|
| I too am trying to figure out the hierarchy of the datasource vs. dataset and the first response that you give is to explain that a dataset can have multiple datasources. This confuses me a bit because I thought that a datasource can have multiple datasets in it. For example if I have a db (AdventureWorks) and I have a report (Sales by Area and Year). In this report I have a shared datasource (which pulls from AdventureWorks db) and in that datasource resides datasets (SalesByCategory, CategoryValues and SubCategoryValues) which seem to me just to be SQL statements that pull certain pieces of data from the AdventureWorks db. So how is it possible for a dataset to have mulitple datasources? I am not saying that you are wrong, I am just confused by this. Thanks! |
|
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-09-24 : 09:17:36
|
| an279505, Think of it this way.DataSource=Directions on where your data is located and how to get to it. ServerName, DatabaseName, and credentialsDataSet=SQL code/Stored Procedure directions on what data to retrieve. You can have a DataSource called HR that gives the directions on where Employee and other HR related Data may be. You may have another DataSource called Accounting that gives the directions on how to get to the Finance server and dataIn your report you would create a DataSet using the HR DataSource to retrieve a list of Employees and Departments. You could also have a DataSet using the Accounting DataSource to list Financial Transactions by Employee. Joining the two could give you Transactions by Department.I am lucky in that all my data on my current project are contained in a single database on a single server so I have a single DataSource for all my reports but many different DataSets depending on what my reporting needs are.You may also want to pop over to http://www.sqlteam.com/forums/forum.asp?FORUM_ID=35 as it is also for reporting services and has a little more activity.John |
|
|
|
an279505
Starting Member
2 Posts |
Posted - 2009-09-24 : 09:53:30
|
| Thanks John! That really clears things up for me. I now can see the correlation between the two. Amy |
|
|
|
|
What is difference between datasource and dataset?