• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

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
0
Juan Velasquez
Asked:
Juan Velasquez
  • 8
  • 6
1 Solution
 
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.
0
 
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 '?'
0
 
planoczCommented:
Are you running a query or using a Stored Procedure?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Juan VelasquezAuthor Commented:
I am running a query
0
 
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)
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
0
 
planoczCommented:
Can you post the query?
0
 
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 = ?
0
 
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.
0
 
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
0
 
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.
 
0
 
Juan VelasquezAuthor Commented:
I'm going to give that a try.  Thanks
0
 
Juan VelasquezAuthor Commented:
That's what it was.  There was a mistake in  the book. Thanks
0
 
Juan VelasquezAuthor Commented:
Greatly Appreciated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now