SSRS REPORT DATASET EOF RECORDS

Briancslnet
Briancslnet used Ask the Experts™
on
Heres a good one for everybody;

I have a report which uses an SQL database.  I have 4 tables which are linked in the report.
The first table references the second table on a field called "Part_id".  What I want to select is those records in the first table which DO NOT exist in the second table.
I have tried "select...from..... WHERE Part_ID in secondtable IS NULL AND  blah.blah.blah...

the program just hangs in preview mode with reporting services.  Cannot seem to figure this out.
Any help or booting in the correct direction would be appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
You query does run if you execute it directly in your Sql Server?

Have you tried to restart the service SQL Server Reporting Services?
First check result set size,if it's more records,May be memory constraint in your pc.

Soln: Result set stored in Separate Table and use Table in SSRS.

Query:
seelct  PARTID from table1  
where not exists(select 'x' from table2 where  table1.partid=table2.partid)

Author

Commented:
Not sure we are on the same page, I will try to explain with more depth.

I have a table of Part numbers. I use this table as my primary table for an SQL query (i.e.  SELECT * FROM PARTTABLE)

I have a second table of existing orders.  We call these REQUIREMENTS

I am creating a report of PARTS which have NO requirements.

In addition I then need to extract specific data from 2 additional tables (i.e. a CUSTOMER table and a PARTDESCRIPTION table)

I have a very limited knowledge of SQL formats and hope somebody can give me  the syntax for a select statement, or at least send me in the right direction.
Commented:
How will you be joining the Customer table with the PARTDESCRIPTION one?

in this query I joined the PARTTABLE and PARTDESCRIPTION tables and it will show only part numbers that doesnt have any record on the REQUIREMENTS table... you just need to change the description field for the real one of the table PARTDESCRIPTION

SELECT PT.Part_ID, PD.[YourDescriptionField]
FROM PARTTABLE PT  INNER JOIN PARTDESCRIPTION PD ON PT.Part_ID=PD.Part_ID
		   LEFT JOIN REQUIREMENTS R ON PT.Part_ID=R.Part_ID
WHERE R.Part_ID IS NULL

Open in new window

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial