tovbornotovb
Jun 19th, 2008, 10:00 PM
Hi Everyone! I Need your Help.
I Have this Stored Procedure in my SQL Server 2005.
SP Name: SummaryItemBought
SQL:
ALTER PROCEDURE [dbo].[SummaryItemBought]
@DateBought nvarchar(15),
@CustomerName nvarchar(50)
AS
IF @CustomerName IS NULL
BEGIN
SET NOCOUNT ON;
SELECT INV_Transaction_OR as [Official Receipt],
CONVERT(nvarchar(12),INV_Transaction_Date,3) as
[TransactionDate] ,
CONVERT(nvarchar(8),INV_Transaction_Time,108) as
[Transaction Time] ,
INV_Item_Sold_Name as [Item Name],
COUNT (*) AS [Total Item Bought],
SUM(INV_Item_FinalPrice) As [Total Amount],
Sales.INV_Sales_Customer as [Customer Name]
FROM INventory_Sales_Details
INNER JOIN Inventory_sales_Summary as Sales
ON Inventory_Sales_details.INV_Transaction_OR =
Sales.INV_Sales_OR
WHERE INV_Transaction_Date = @DateBought
GROUP BY INV_Item_Sold_Name ,
INV_Transaction_Date,
INV_Transaction_Time,
INV_Transaction_OR,
Sales.INV_Sales_Customer;
END
ELSE
BEGIN
SET NOCOUNT ON;
SELECT INV_Transaction_OR as [Official Receipt],
CONVERT(nvarchar(12),INV_Transaction_Date,3) as
[Transaction Date] ,
CONVERT(nvarchar(8),INV_Transaction_Time,108) as
[Transaction Time] ,
INV_Item_Sold_Name as [Item Name],
COUNT (*) AS [Total Item Bought],
SUM(INV_Item_FinalPrice) As [Total Amount],
Sales.INV_Sales_Customer as [Customer Name]
FROM INventory_Sales_Details
INNER JOIN Inventory_sales_Summary as Sales
ON Inventory_Sales_details.INV_Transaction_OR =
Sales.INV_Sales_OR
WHERE INV_Transaction_Date = @DateBought AND
Sales.INV_Sales_Customer = @CustomerName
GROUP BY INV_Item_Sold_Name ,
INV_Transaction_Date,
INV_Transaction_Time,
INV_Transaction_OR,
Sales.INV_Sales_Customer;
END
In My MS SQL Reporting Services, I didnt use the wizard for the creation of Report but the manual report creation.
I did all the step.
THE PROBLEM IS, when I am previewing the data, ONLY one record is showing in the report preview tab but in the Data Tab when i try to run the stored procedure, it list all the data.
What seems to be the problem?
PLease help me...:(
I Have this Stored Procedure in my SQL Server 2005.
SP Name: SummaryItemBought
SQL:
ALTER PROCEDURE [dbo].[SummaryItemBought]
@DateBought nvarchar(15),
@CustomerName nvarchar(50)
AS
IF @CustomerName IS NULL
BEGIN
SET NOCOUNT ON;
SELECT INV_Transaction_OR as [Official Receipt],
CONVERT(nvarchar(12),INV_Transaction_Date,3) as
[TransactionDate] ,
CONVERT(nvarchar(8),INV_Transaction_Time,108) as
[Transaction Time] ,
INV_Item_Sold_Name as [Item Name],
COUNT (*) AS [Total Item Bought],
SUM(INV_Item_FinalPrice) As [Total Amount],
Sales.INV_Sales_Customer as [Customer Name]
FROM INventory_Sales_Details
INNER JOIN Inventory_sales_Summary as Sales
ON Inventory_Sales_details.INV_Transaction_OR =
Sales.INV_Sales_OR
WHERE INV_Transaction_Date = @DateBought
GROUP BY INV_Item_Sold_Name ,
INV_Transaction_Date,
INV_Transaction_Time,
INV_Transaction_OR,
Sales.INV_Sales_Customer;
END
ELSE
BEGIN
SET NOCOUNT ON;
SELECT INV_Transaction_OR as [Official Receipt],
CONVERT(nvarchar(12),INV_Transaction_Date,3) as
[Transaction Date] ,
CONVERT(nvarchar(8),INV_Transaction_Time,108) as
[Transaction Time] ,
INV_Item_Sold_Name as [Item Name],
COUNT (*) AS [Total Item Bought],
SUM(INV_Item_FinalPrice) As [Total Amount],
Sales.INV_Sales_Customer as [Customer Name]
FROM INventory_Sales_Details
INNER JOIN Inventory_sales_Summary as Sales
ON Inventory_Sales_details.INV_Transaction_OR =
Sales.INV_Sales_OR
WHERE INV_Transaction_Date = @DateBought AND
Sales.INV_Sales_Customer = @CustomerName
GROUP BY INV_Item_Sold_Name ,
INV_Transaction_Date,
INV_Transaction_Time,
INV_Transaction_OR,
Sales.INV_Sales_Customer;
END
In My MS SQL Reporting Services, I didnt use the wizard for the creation of Report but the manual report creation.
I did all the step.
THE PROBLEM IS, when I am previewing the data, ONLY one record is showing in the report preview tab but in the Data Tab when i try to run the stored procedure, it list all the data.
What seems to be the problem?
PLease help me...:(