Group By Problem RS2005 using Report-Parameter?

Hi all,
my Report shows not the correct values, if i choose more than one
Products from my Parameter-Object (Products). Does RS2005 is having a
problem with GROUP BY?
The SqlProfiler does not show the filtered query which the Report is
executing?
I just want to have a small Report to show the Totals of each sold
Products and i want to filter on one ore more Products.
my Report based on the following DataSet (DsSales) of AdventureWorks:
SELECT Production.Product.Name,
SUM(Sales.SalesOrderDetail.LineTotal) AS Total,
SUM(Sales.SalesOrderDetail.UnitPriceDiscount) AS Discount,
Production.Product.ProductID
FROM Sales.SalesOrderDetail INNER JOIN
Production.Product ON
Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
GROUP BY Production.Product.Name, Production.Product.ProductID
i have a additional DataSet for my Parameter Products which based on
the Query:
SELECT ProductID, Name
FROM Production.Product
ORDER BY Name
I attached a new Report-Parameter to this DataSet (Multi, Value-Member
to ProductID, Display-Member to name of the Product).
i defined a Filter in DsSales (=Fields!ProductID.Value In
=Parameters!Products.Value(0))
If i choose all from the combo-Box (Parameter is multi) i get no
result. If i choose just one Product i get the correct Result.
Plain sql against the Database gives always the correct values, so
RS2005 not filtering correct with Group by and Having?
Any ideas?
Thanks for your answers in advance...
RonnyThere is nothing wrong in the query or group by etc...
This works very much fine. One small adjustment needs to be done is that
in your filter you need to have something like this.
(=Fields!ProductID.Value In
=Parameters!Products.Value(0)) or (=Fields!ProductID.Value In
=Parameters!Products.Value)
The difference between value(0) and Value is select full oject or all option
or single from the drop down.
Amarnath
"Classgenerator" wrote:
> Hi all,
> my Report shows not the correct values, if i choose more than one
> Products from my Parameter-Object (Products). Does RS2005 is having a
> problem with GROUP BY?
> The SqlProfiler does not show the filtered query which the Report is
> executing?
> I just want to have a small Report to show the Totals of each sold
> Products and i want to filter on one ore more Products.
> my Report based on the following DataSet (DsSales) of AdventureWorks:
> SELECT Production.Product.Name,
> SUM(Sales.SalesOrderDetail.LineTotal) AS Total,
> SUM(Sales.SalesOrderDetail.UnitPriceDiscount) AS Discount,
> Production.Product.ProductID
> FROM Sales.SalesOrderDetail INNER JOIN
> Production.Product ON
> Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
> GROUP BY Production.Product.Name, Production.Product.ProductID
> i have a additional DataSet for my Parameter Products which based on
> the Query:
> SELECT ProductID, Name
> FROM Production.Product
> ORDER BY Name
> I attached a new Report-Parameter to this DataSet (Multi, Value-Member
> to ProductID, Display-Member to name of the Product).
> i defined a Filter in DsSales (=Fields!ProductID.Value In
> =Parameters!Products.Value(0))
> If i choose all from the combo-Box (Parameter is multi) i get no
> result. If i choose just one Product i get the correct Result.
> Plain sql against the Database gives always the correct values, so
> RS2005 not filtering correct with Group by and Having?
> Any ideas?
> Thanks for your answers in advance...
> Ronny
>|||Hi Amarnath,
thanx for the hint.
i also found another solution which is quicker in execution also.
Define the parameter already in Query:
SELECT Production.Product.Name, SUM(Sales.SalesOrderDetail.LineTotal)
AS Total, SUM(Sales.SalesOrderDetail.UnitPriceDiscount) AS Discount,
Production.Product.ProductID
FROM Sales.SalesOrderDetail INNER JOIN Production.Product ON
Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
GROUP BY Production.Product.Name, Production.Product.ProductID
HAVING (Production.Product.ProductID IN (@.ProductID))
Create a DataSet for the Products.
SELECT ProductID, Name FROM Production.Product ORDER BY Name
Add a Report-Parameter:
name: ProductID
type: String
prompt: products
multi: checked
available values: from query (DataSetproducts)
valuemember: ProducID
displaymember: name
Not nessecary to set a Filter in DataSet!
The Parameter is automatically added to the Parameters of the DataSet:
@.ProductID=Parameters!ProductID.Value
Everything is working fine and very quick.
Thanx Ronny