ssrs displaying fields that are not in the dataset

[DavidGraham167]

I have a query statement that joins two data sets together giving a list from one dataset and from the other dataset.

'Contacts' contains job_number, contact_surname & contact_forename
'joblist' contains job_number, job_name & status.

ReportQuery = "SELECT c.job_number,c.contact_surname,c.contact_forename,j.job_name,j.status,FROM contacts AS c" & _
" INNER JOIN joblist AS j ON c.job_number=j.job_number"

In my report want to show all these fields but I can only select a field from one dataset.

How do I get round it?
At present I and using an alias(AS) to reassign the field to one that has not being used.

[FunkyDexter]

I'm unclear on the problem here. Normally in SSRS you use a query to build a dataset and you can then use any of the fields from that dataset in your report.

SSRS doesn't care about the underlying database tables, how you joined tables together, what derived fields you created etc. It only cares about the result of your query. So if your query is returning fields from more than one table you should still be able to use any or all of them because they all end up in the same dataset.

By the way, the syntax of your query is wrong as you have an extra comma at the end of your select list. That wouldn't run so I suspect you're either not showing the whole code or you're getting an error you're not telling us about. If you can fill in that gap I can probably hazard a better guess at what's going wrong for you.

[DavidGraham167]

The error in the query satement is because I'm using a more complex statement and edited it to try give a simple example and made a mistake in the post.

In the report I get this error if I try to use the field status (which is not in the 'contacts' dataset):

"The Value expression for the text box ‘comments’ refers to the field ‘status’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope."

In the Report Data window I attach one of the existing data sets to the table. As you say I would expect to be able to use the fields from the query.
I must be missing something.

[FunkyDexter]

Hmmm...

What's the full query for that dataset?
In the ReportData tab, if you expand the fields for the dataset, do you see Status in it?


edit> also, go to the properties of the tablix in the report and check the DataSetName property. Just make sure it is pointing to the right dataset.

[DavidGraham167]

The full query is this:
reportquery
"SELECT c.job_number,c.contact_surname,c.contact_forename,c.contact_company,j.job_name AS ind_id,j.status AS comments FROM contacts AS c INNER JOIN joblist AS j ON c.job_number=j.job_number WHERE UPPER(contact_surname) Like '%GRAHAM%' ORDER BY job_number DESC"

If I click on the little box on the right of the text box only the 'contacts' fields are listed.

In the Tablex properties the DataSet is DataSet1.

If I lookin in the ReportData window DataSet1 also only contains the 'contacts' filelds - this is because this is what I selected - you can only select an existing dataset.

I also have the following code in the Report form using the query.

Dim da As SqlDataAdapter = New SqlDataAdapter(ReportQuery, SQLConnection)
        ' create a new data set
        Dim ds As DataSet = New DataSet
        ' fill the data adapter
        da.Fill(ds)
        ' clear the datasource 
        ReportViewer1.LocalReport.DataSources.Clear()
        ' set the dataource (DataSet1 is defined in the matrix in the rdlc file)
        Dim rds As New Microsoft.Reporting.WinForms.ReportDataSource("DataSet1", ds.Tables(0))
        ' add a new datasource
        ReportViewer1.LocalReport.DataSources.Add(rds)
        ReportViewer1.RefreshReport()

[FunkyDexter]

The problem is that you've aliased the column Status to be Comments in the query but, from your error message, you're still referring to it as Status in the report.

Are you saying that you're not seeing ind_id and comments in the dropdown when you click the little box to the right of the textbox. That seems odd, you should see all the fields returned by your query.

Here are a few thoughts:-

1. Right click on the dataset in the report designer and select query. What do you see in there? Is it the query you just posted?
2. In the code you posted you're passing the sql query into the routine but never actually using it. It looks like you're filling DS with whatever select query was assigned to the Data Adapter at design time. Are you sure you're running the query you think you are? Stick a breakpoint in and interrogate DA.SelectQuery.

[DavidGraham167]

If I use the following it works. The 'contacts' dataset does not contain job_name or status but it does contain ind_id and comments which (Luckily) I'm not using.
In my report I can use the ind_id & comments field which are available.
I'm lucky in this case that I have two spare fields I'm not using.

I'm reporting the jobname & status but I have to fool it into thinking it is repoerting on two other fields.

"SELECT c.job_number,c.contact_surname,c.contact_forename,c.contact_company,j.job_name AS ind_id,j.status AS comments FROM contacts AS c INNER JOIN joblist AS j ON c.job_number=j.job_number WHERE UPPER(contact_surname) Like '%GRAHAM%' ORDER BY job_number DESC"

What I would like to do is;

"SELECT c.job_number,c.contact_surname,c.contact_forename,c.contact_company,j.job_name,j.status FROM contacts AS c INNER JOIN joblist AS j ON c.job_number=j.job_number WHERE UPPER(contact_surname) Like '%GRAHAM%' ORDER BY job_number DESC"

and refer to job_name & status in the report.

Sorry - I did not understand "Right click on the dataset in the report designer and select query" - I can't find that.
Also DA doesn't have DA.SelectQuery - I get:

da.selectquery 'selectquery' is not a member of 'System.Data.SqlClient.SqlDataAdapter'.

[FunkyDexter]

da.selectquery 'selectquery' is not a member of 'System.Data.SqlClient.SqlDataAdapter'.

Sorry, that should have been SelectCommand

Sorry - I did not understand "Right click on the dataset in the report designer and select query" - I can't find that.

Open the report in design mode and open the ReportData tab. You should see a datasource listed there and under that you should see your DataSets. Right click on the dataset that's assigned to the tablix (which should be DataSet1 according to your previous post) and you should get a context sensitive menu that has "Query" as one of it's options. Select that and the query for your dataset should be shown.

What I would like to do is

Well, the fields you use in your report have to match up to the fields you return to the dataset from your query... otherwise SSRS cannot know what field to display where. So your choices are:-
1. Amend the query to return the fields the report expects
2. Amend the report to expect the fields the query returns

[DavidGraham167]

Thanks for your help.

1. da.SelectCommand is giving the correct query.
It is returning the correct records from the 'WHERE' section

2. When I open the Report Data window and right click on DataSet1, the three choices are refresh, delete & dataset properties - there is no query.

3. "the fields you use in your report have to match up to the fields you return to the dataset from your query"
This is what I want.

job name is in the 'joblist' dataset, ind_id is in the 'contacts' data set.
Using a JOIN in the query I want to report on items in both data sets.
The data set I have attached to the Tablix is 'comtacts'
When I use 'j.job_name AS ind_id' in the query and refer to ind_id' in the report it is OK.
But if I use 'j.job_name' in the query and refer to 'job_name' in the report I get an error.

I think I will have to go with the above workaround (or maybe this is the correct way).

[FunkyDexter]

When I open the Report Data window and right click on DataSet1, the three choices are refresh, delete & dataset properties - there is no query.

I guess you're using a different interface to me so you're probably having a lisghtly different user experience. Try looking under properties. It's probably under there somewhere.

job name is in the 'joblist' dataset, ind_id is in the 'contacts' data set.
Using a JOIN in the query I want to report on items in both data sets.
The data set I have attached to the Tablix is 'comtacts'

It is possible to use a value from a dataset your tablix isn't based on but it gets a bit awkward so probably isn't you're best solution. To do so you have to refer to the field in an aggregate function. So these are acceptable expressions for a cell:-
=First(Fields.MyField.Value, "MyOtherDataset")
=Sum(Fields.MyField.Value, "MyOtherDataset")
You need to use an agregate because there's no way of relating one dataset to another in SSRS. That means there's no way of SSRS knowing which row from datasetA to show for a given row of datasetB - so you have to tell it using an aggregate. That might help but I think you'll still find it awkward.

When I use 'j.job_name AS ind_id' in the query and refer to ind_id' in the report it is OK.
But if I use 'j.job_name' in the query and refer to 'job_name' in the report I get an error.

Assuming the name of the field in the dataset matches the name of the field you're referring to in your report expression it should just work. I suspect the problem is something to do with the fact that you're actually creating your dataset in code and then passing it in. One of the fields isn't called what you think it is. Look carefully at that code.

Alternatively, why not just design the dataset in SSRS in the first place and remove all doubt. Unless you really want to run a different query under different circumstances I can't see a good argument for creating the dataset in code when SSRS will do it for you.

Can I just clarify that we're using the word dataset in the same way? I'm referring to a dataset in the SSRS designer. You should see it in your ReportData tab. It's based on a query that returns you a set of fields that are then available for use in your report.

[DavidGraham167]

Thanks for your help.
I think my interface is a little bit different from yours.

My program is such that the user selects a number of parameters then it lists those records matching those parameters then the user can print/report them. In the example it's selecting all contacts containng the name 'GRAHAM' but it can be more complex than that.
Therefore the query is built up in the code.

The aggregate function looks just as messy as using the ALIAS.

The 'dataset' is shown in the ReportData window it is 'DataSet1' and I had to select one of the datasets from the database - not the actual query.

[FunkyDexter]

I think my interface is a little bit different from yours.

What tech are you actually using? I'm using VS 2008 with BIDS 2008. I find the Visual Studio with BIDS setup to be by far the best to develop SSRS reports in.

Also, how are you opening up the reports to design them? I notice you're using a report viewer control which I don't typically use so maybe that changes your experience. Your not using some ReportViewer wizard or similar are you? My recommendation is just to double click on the rdl file in your solution explorer tree.

The aggregate function looks just as messy as using the ALIAS

Yeah, I personally don't like it. It's an available option so it's worth knowing about but I generally avoid using it. It's usually far easier to return the apropriate values in the dataset. The argument for using it would be when you're already returned the value in another dataset for a different tablix/matrix etc. Grabbing a value from that dataset saves a little bit of network traffic but, to be honest, it's usually a trivial amount and extra complexity it brings usually isn't worth it.

My program is such that the user selects a number of parameters then it lists those records matching those parameters then the user can print/report them. In the example it's selecting all contacts containng the name 'GRAHAM' but it can be more complex than that.
Therefore the query is built up in the code.

That sounds to me like something you could do quite trivially through a single query designed in SSRS itself, no need for dynamic sql. Something like this:-

Select blah1, blah2, blah3
From MyBlahTable
Join MyBlah2Table
 on MyBlah1Table.KeyField = MyBlah2Table.KeyField
Where (blah7 = @parm7 or @parm7 is null)
and (blah8 = @parm8 or @parm8 is null)
and (blah9 = @parm9 or @parm9 is null)

If you allow the apropriate parameter in SSRS to accept NULLs then that structure will allow the user to select any or all parameters or even none (for a completely open search).

If you can I'd always recommend you design your query in SSRS first because that gives you your field list to play with. It'll also automatically create all the parameter it needs. Basically, it creates everything you want and automatically ties it all together. If, after that, you really do need to do some dynamic manipulation of the sql then, yeah, break it out to code but just make sure it's producing the same list of fields.

And just to be clear, when I say design your query in SSRS I don't neccessarily mean using the SSRS query designer (I HATE query designers), I just mean make sure the SSRS dataset has an apropriate query designed into it. Personally I usually design my queries in management studio then cut and paste it into the query property of the SSRS dataset.

[DavidGraham167]

I'm using VisualStudio 2010 so that is probably why it is looking different.

I will have a look at your suggested method of sending queries.

Thanks