Display CR from 2 joined tables

Hello,

I am not going to waste too much of your time. Thanks for your help with my last problem. However, I have another problem. I want to display in my report data from 2 different tables. I have created the typed dataset and join the tables.

In CR I have added the dataset to my report designer.

I have stored procedure that selects from 2 tables based on a incidentID number. However, my report only displays from the incidents table.

Example of my tables.

[incident] IncidentID (PK), Company, phoneno, contact, etc

[IncidentTask] taskID (PK), IncidentID (FK), details

My query

Code:

---

ALTER PROCEDURE [dbo].[printJobSheet]
@incidentID int

AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

    -- Insert statements for procedure here
        SELECT incident.incidentID, incident.company, incident.subject, incident.contact, incident.phoneNo, incident.email, incidentTask.TaskID, incidentTask.Details
        FROM incident INNER JOIN incidentTask on incident.incidentID = incidentTask.incidentID
        WHERE incident.incidentID = @incidentID
END

---

The repeating field are in the incidentTask table (TaskID, Details) These will be in the detailed section of the report. The other fields from the incident table will be in the header.

The way I am fill my dataset which is a typed dataset is as follows:

Code:

---

Try
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "printJobSheet"

            Dim pIncidentID As New SqlParameter()
            pIncidentID.ParameterName = "@IncidentID"
            pIncidentID.DbType = DbType.Int16
            pIncidentID.Direction = ParameterDirection.Input
            pIncidentID.Value = IDNumber
            cmd.Parameters.Add(pIncidentID)

            DS_JobSheet2 = New DataSet

            cnn.Open()
            cmd.Connection = cnn

            da.SelectCommand = cmd
            da.Fill(DS_JobSheet2)

            dt = DS_JobSheet2.Tables(0)

            report.Load(Application.StartupPath & "/rptJobSheet2.rpt")
            report.SetDataSource(dt)

            Me.CrystalReportViewer1.ReportSource = report

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

---

If I do a SELECT * FROM Incident
or
SELECT * FROM IncidentTask

It works, but not when I join the tables together. I have created the xsd typed datasets and have the in my report.

A example working program would be wonderful, but I don't want to take too much of your time.

Thanks in advance,

Steve

Hi Stave
First thing I want to tell you that if you are using more then one table in the CR, then the database table linking is necessary. If linking is not done by you in the CR then the CR will not show the best result.
Remember one thing also that you save to link on the same data type that you are using in the database (Integer-Integer).
After it pass the sql query for searching the data in the CR with proper Join then you will get the result.

hello Shahti

Yes i have my relationships in my xsd and my CR designer.

I have uploaded an attachment that has the screen shots of the dataset schema and the CR designer. So you can see the links.

I hope this helps.

Many thanks,

Steve

In your CR where you are passing the query related to the multiple table, make a group on the Incident ID

Hello Skahti,

I am not sure I am getting this grouping right.

This is what I did I right clicked on the CR designer and selected group expert.

The fields are: Report fields, incidents, and incidentTask. Not sure what to add and where.

I would like to have the incident details in the header (incidentID, subject, etc)

And in the report details section have all the incidentsTasks that come under that incident. For example for incident 68 there are 10 incidentTasks.

I am not sure how to group them to get this in my report designer.

Thanks for any sugguestion,

You have been a great help so far. I think I almost near to solving this. Just a little be further.


I have sent the report example, so you know what I am trying to achieve with this report.

Steve