Trying to create subreport

Hello,
I am teaching myself SSRS and am currently trying to create a subreport using Report Builder 2.0
I first created a parameter named CategoriesCategoryID with a datatype of integer and a default value of 1.
I then created a dataset named qryProducts using a previously created datasource name ProductSrc
In the dataset I created, I went to  dataset properties and entered the following in the Query tab of dataset properties

Query type: Text

Query:      Select
      Products.ProductName,
      Products.CategoryID AS ProductsCategoryID,
      Products.ProductID,
      Products.UnitPrice,
      Products.UnitsInStock,
      Categories.CategoryID AS CategoriesCategoryID
      FROM
      Categories
      INNER JOIN Products
      ON Categories.CategoryID = Products.CategoryID
      WHERE Categories.CategoryID = ?

In the Parameters tab of the dataset properties, for the parameter ? from the above select statement, I entered a [@CategoriesCategoryID] as the value

However after I do this the, the columns are not displayed for the dataset qryProducts.  I'm not sure why.  I do know when I delete the where clause or replace the question mark with a numerical value, the columns are displayes.  I've been trying to figure out what I'm doing wrong
Dataset.JPG
Juan VelasquezAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

planoczCommented:
First run the sub-report by itself. A sub-report is just a standlone report like your main report.
If the sub-report can run on it's own then place it into the main report and have the parameters the same for both reports the sub - report should run after the main report starts.
Juan VelasquezAuthor Commented:
When I attempt to run the report I get the following message.  In addition, when I go to the dataset in Report Builder 2.0, I don't see the column listed below the dataset.  For example, I would expect to see the following fields listed under the dataset qryProduct
qryProduct   'Name of dataset
ProductName
ProductsCategoryID
Products.ProductID,
Products.UnitPrice, Products.UnitsInStock,
CategoriesCategoryID

Instead no fields are being displayed.  In addition when I try to run the report I get the following error  message

An error occurred during client rendering.
An error has occurred during report processing.
Query execution failed for dataset 'qryProducts'.
Incorrect syntax near '?'. Incorrect syntax near '?'
planoczCommented:
Are you running a query or using a Stored Procedure?
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Juan VelasquezAuthor Commented:
I am running a query
planoczCommented:
If the query is working right you should see something.
You should be able the run the query on a query tab (Report Builder 2.0)
Juan VelasquezAuthor Commented:
I don't think the query is running correctly.  There is something wrong with the where clause - in particular the ? place holder
planoczCommented:
Can you post the query?
Juan VelasquezAuthor Commented:
Select
      Products.ProductName,
      Products.CategoryID AS ProductsCategoryID,
      Products.ProductID,
      Products.UnitPrice,
      Products.UnitsInStock,
      Categories.CategoryID AS CategoriesCategoryID
      FROM
      Categories
      INNER JOIN Products
      ON Categories.CategoryID = Products.CategoryID
      WHERE Categories.CategoryID = ?
planoczCommented:
Sorry i do not notice it was on the top of the page.
Try this for the last line
WHERE Categories.CategoryID like '%1%'

Or
just pick an ID that you know is in the database.
Juan VelasquezAuthor Commented:
I've did that by using a 1 and it looks like it works.  However, I need to use a place holder.  I thought ? was the placeholder for the parameter
planoczCommented:
No you need to use something like
WHERE Categories.CategoryID = @CategoryID

@CategoryID is the parameter that is coming and going to the Query and report page.
 

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Juan VelasquezAuthor Commented:
I'm going to give that a try.  Thanks
Juan VelasquezAuthor Commented:
That's what it was.  There was a mistake in  the book. Thanks
Juan VelasquezAuthor Commented:
Greatly Appreciated.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.