?
Solved

Trying to create subreport

Posted on 2011-10-05
14
Medium Priority
?
185 Views
Last Modified: 2012-08-14
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
Comment
Question by:chtullu135
  • 8
  • 6
14 Comments
 
LVL 27

Expert Comment

by:planocz
ID: 36918745
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
 

Author Comment

by:chtullu135
ID: 36918921
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
 
LVL 27

Expert Comment

by:planocz
ID: 36918951
Are you running a query or using a Stored Procedure?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:chtullu135
ID: 36919097
I am running a query
0
 
LVL 27

Expert Comment

by:planocz
ID: 36919900
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
 

Author Comment

by:chtullu135
ID: 36919919
I don't think the query is running correctly.  There is something wrong with the where clause - in particular the ? place holder
0
 
LVL 27

Expert Comment

by:planocz
ID: 36920389
Can you post the query?
0
 

Author Comment

by:chtullu135
ID: 36920437
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
 
LVL 27

Expert Comment

by:planocz
ID: 36920844
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
 

Author Comment

by:chtullu135
ID: 36920891
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
 
LVL 27

Accepted Solution

by:
planocz earned 2000 total points
ID: 36921125
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
 

Author Comment

by:chtullu135
ID: 36921946
I'm going to give that a try.  Thanks
0
 

Author Comment

by:chtullu135
ID: 36921994
That's what it was.  There was a mistake in  the book. Thanks
0
 

Author Closing Comment

by:chtullu135
ID: 36921996
Greatly Appreciated.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Integration Management Part 2
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question