Sql Reporting Services: Using Multiple Data Sources?

Hi, you can’t find much on SQL Reporting services on the net right now. I have a question, and a nice tidbit I learned which I want to share aswell.

Question:

How do I access fields from multiple data sources?
I used the Report Project Wizard to start my project and gave it my initial dataset. Then I wanted to use another dataset to bring in some different data to the report.

at this link: http://www.devx.com/dbzone/Article/21458/0/page/5
(The best info I’ve found today on SQL Reporting services)

I learned how to add datasets:

To add a dataset to your report, select the “Data” tab, then choose the “New Dataset” item in the “Dataset” drop-down list. You will be prompted for a dataset name, data source, command type and query string.

The dataset name is whatever you want to call your dataset, and the data source will be the name of the dataset you created earlier. The command type will be one of the following options:

Text: An ad-hoc SELECT statement, which you can type directly into the query string text box.
StoredProcedure: The name of a stored procedure. You have to enter the stored procedure name in the query string textbox.
TableDirect: For reading from a single table only. Enter the name of the table in the query string textbox.

So I did this, and I chose StoredProcedure. And it compiles and runs and prompts me for arguments, but the field names in the column on the right side of Visual studio come up as: ID, ID_1, ID_2 etc. But they actually are: TodaysBillings, PercentChargeable etc.

And when I try to refer to these fields with:
=Fields!ID.Value OR =Fields!TodaysBillings.Value, neither of these works!

THUS:

How do I refer to fields of new datasets that I’ve added? Do I have to make it shared (and if so, how do I do that?)? Please help! :frowning:

if you’re reading this but your not a member pls contact me: jrphilATberkeley.edu

Now for my tidbit: (don’t need to read this to answer my question)

I wanted to bring in that 2nd dataset from a stored procedure which does manipulation of the the first dataset. But then I figured out how to manipulate the data that is already in the report! This is very valuable information to anyone writing Sql Reports.

You can refer to fields already in the report by using the ReportItems! collection and using the “name” of the “textbox” you want to access.

ReportItems!TodaysChargeableHours.Value

where TodaysChargeableHours is the name of the textbox (It seems all of the boxes have names like textbox1, textbox2 …)

Furthermore, you can use this ReportItems! to pass report items to your custom code. The syntax is keyword: Code [dot] yourFunctionName ( ReportItems!..)

=Code.calculateBillings(ReportItems!TodaysChargeableHours.Value)

There is also this tricky thing about adding custom code. As far as I know, the only way to do it, is to, watch carefully,

right-click the empty space around the report “Body”. You’ll get a menu that has: “Report Parameters”, “Page header”, “Page footer” and “properties”. To create custom code, go properties > code Tab.

And write your VB.NET ONLY functions in the window (sketchy huh!)

Remember to click in the pinkish space surrounding your report body in the main window. This is the only way.

Whew, better get back to work now, that took like 30 minutes.

I’ve figured out how to add multiple datasets and use them. I don’t know why I was having the odd problem before, but it’s a simple matter of, for the table (or object I guess) that you want to refer to the fields of a dataset, you have to set the table’s DataSetName property to the dataset you want to refer to.