Hi,
I'm trying to create a report with two tables in which I've created a inner join in the dataset desiner. I run the query and it works fine. When I am in the report designer I create a table and then pull the fields over from the data source into the details area. I have to select them from the two tables in the data source window. When I run the report the secondary fields do not synchronize. This is where I am confused, shouldn't those secondary fields I added to the SQL Select statement with a inner join, be available to add to my report Shouldn't I only need one ObjectDataSource for my report What am I missing
ReportViewer with inner join
Chris Treadaway MSFT
X-Cubed
It is probably not a good idea to have multiple datasets in one project.
If you start adding a dataset for each query you run that the report uses, you will end up with many datasets.
I suggest to not use the tableadapter. Especially if you use oracle and would like in the future to do a cross database join.
Here are the steps:
1. Create a dataset, create a datatable(s)
2. Create a report from the datatable(s) that is(are) in your dataset.
3. Fill your datatable(s).
4. Use this code to update your report at run time:
This is the tricky part, the datatable name you declare here, has to be datasetname underscore datatablename.
Dim dtReport As New DataTable("DataSet1_dtSMA222", "DataSet1_dtSMA222")
With rptViewer
With .LocalReport
.ReportPath = "rpt_SMA222.rdlc"
With .DataSources
.Add(New Microsoft.Reporting.WebForms.ReportDataSource(dtReport.TableName, dtReport))
End With
.Refresh()
End With
End With
End Using
Hope this helps,
-Edward
reneclip
Juggernaut.
Hi Edward
After I wrote that I realized what you said. Although I got it working within the Dataset, I will try this approach just to see how it works.
Thanks,
Jason
Mammoth
Hey William,
Thanks for the response. Below is the select statement I created in the Dataset Designer. Could it be that the Data sources window in the report designer only list the primary Query defined for the Table My query is an additional one I added.
From GetDetails select query.
SELECT Orders.MemberID, Orders.OrderID, Orders.OrgOrderID, Orders.Address1, Orders.Address2, Orders.City, Orders.State, Orders.Zip, Orders.Phone, Client.FirstName, Client.LastName, Client.Phone, Client.Work_Phone
FROM Orders INNER JOIN
Client ON Orders.MemberID = Client.MemberID
Here is the ObjectDataSource in my web page.
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="Delete" InsertMethod="Insert" OldValuesParameterFormatString="original_{0}" SelectMethod="GetDetails" TypeName="DataSetTraTableAdapters.OrdersTableAdapter" UpdateMethod="Update">Manu Goel
Ok I just tried putting the inner join into the main query and the fields do show up. But I really didn't want to have to do it this way. I guess what I need to do is create a new Dataset. I have been keeping all my tables in one Dataset but I guess there's no reason why I can't use two. Your telling me that the fields should show up in the Datasource put me on the right track to understand what was happening.
Thanks.