SSRS 2005 Reports and Stored Procedures

for the duplicate rows, can you try using DISTINCT key word, it might be helpful, also can you check the joins, it might need more conditions to eliminate duplicates


for SSRS, check the following links:
1. http://msdn.microsoft.com/en-us/magazine/cc188691.aspx
2. http://en.csharp-online.net/Building_Reports_in_SQL_Server_2005%E2%80%94Setting_Report_Parameters_with_Stored_Procedures

Thank you @Amqad.. Still a bit loss... I am using distinct

and what about joins, be sure to have them joined correctly

That would cause duplicates? I tought I haad used several (4) unique identifiers

*Thought, had

can you send me a sample of the duplicated O/P ?

ExtendedStandardCost		Order Amount_LastYear	Invoice Amount_LastYear	Order Amount_CurrentYear	Invoice Amount_CurrentYear	Qty		Order Amount_LastYear	Invoice Amount_LastYear	Order Amount_CurrentYear	Invoice Amount_CurrentYear											
10/12/2010 0:00	0	2010	0	0	-1133	0	-1	2009	0	100	0	0											
10/12/2010 0:00	0	2010	0	0	1133	0	1	2009	0	100	0	0											
10/12/2010 0:00	760.6	2010	0	0	0	3026	2	2009	0	100	0	0											
10/12/2010 0:00	0	2010	0	0	0	13	1	2009	0	100	0	0											
10/12/2010 0:00	24	2010	0	0	0	1070	2	2009	0	100	0	0											
10/12/2010 0:00	96.32	2010	0	0	0	384	2	2009	0	100	0	0											
10/12/2010 0:00	92	2010	0	0	0	144	4	2009	0	100	0	0

Select allOpen in new window

can you send the data in excel file, and highlight duplicates, sorry but the sample data is overlapping.

Used this query and the result is attached excel 2003
DECLARE @start smalldatetime
SET              @start = '10/1/2010' DECLARE @end smalldatetime
SET              @end = '10/31/2010' DECLARE @energy nvarchar(100)
SET              @energy = 'belfast'
                          SELECT     EnergyOrg, Subproduct, product, transdate, COUNT(*)
                           FROM         dbo.Order_Analysis_Select(@Start, @End, @Energy) AS currentYear
                           GROUP BY EnergyOrg, Subproduct, product, transdate Duplicates.xls Duplicates.xls Duplicates.xls

so, it might be a problem in the function "dbo.Order_Analysis_Select" getting duplicate data, can you check it or post it  here ?

The SP or ?

create procedure Order_Analysis_By_Month (
	@start smalldatetime
,	@end smalldatetime
,	@MysteriousEnergyParam nvarchar(100)) --not sure what happened with this

as

begin

/*
execute Order_Analysis_By_Month '10/1/2010', '10/31/2010', 'UNK') 

*/
--declare variables for previous year
declare @lastYearStart as smalldatetime
declare @lastYearEnd as smalldatetime

--set variables for previous year
set @lastYearStart = DATEADD(year, -1, @start)
set @lastYearEnd = DATEADD(year, -1, @end)

SELECT     CurrentYear.transdate, CurrentYear.extendedstandardCost, CurrentYear.BusinessEntity, CurrentYear.product, CurrentYear.site,
                      CurrentYear.subproduct, CurrentYear.transYear, CurrentYear.OrderAmount_LastYear, CurrentYear.InvoiceAmount_LastYear,
                      CurrentYear.OrderAmount_CurrentYear, CurrentYear.InvoiceAmount_CurrentYear, CurrentYear.ExchangeRate, CurrentYear.MonthWorkingDays,
                      CurrentYear.WorkingDays, CurrentYear.EnergyOrg, CurrentYear.QTY, CurrentYear.Energy_MS_SM, PreviousYear.transdate AS Expr1,
                      PreviousYear.extendedstandardCost AS Expr2, PreviousYear.BusinessEntity AS Expr3, PreviousYear.product AS Expr4, PreviousYear.site AS Expr5,
                      PreviousYear.subproduct AS Expr6, PreviousYear.transYear AS Expr7, PreviousYear.OrderAmount_LastYear AS Expr8,
                      PreviousYear.InvoiceAmount_LastYear AS Expr9, PreviousYear.OrderAmount_CurrentYear AS Expr10,
                      PreviousYear.InvoiceAmount_CurrentYear AS Expr11, PreviousYear.ExchangeRate AS Expr12, PreviousYear.MonthWorkingDays AS Expr13,
                      PreviousYear.WorkingDays AS Expr14, PreviousYear.EnergyOrg AS Expr15, PreviousYear.QTY AS Expr16,
                      PreviousYear.Energy_MS_SM AS Expr17
FROM         dbo.Order_Analysis_Select(@start, @end, @MysteriousEnergyParam) AS CurrentYear LEFT OUTER JOIN
                      dbo.Order_Analysis_Select(@lastYearStart, @lastYearEnd, @MysteriousEnergyParam) AS PreviousYear ON
                      CurrentYear.EnergyOrg = PreviousYear.EnergyOrg AND CurrentYear.subproduct = PreviousYear.subproduct AND
                      CurrentYear.product = PreviousYear.product AND CurrentYear.transdate = DATEADD(year, 1, PreviousYear.transdate)

Select allOpen in new window

the function, you are using for getting data

ALTER FUNCTION [dbo].[FnOrder_Analysis_Select]
(	
	@Start smalldatetime
,	@end smalldatetime
,	@energyorg nvarchar(100)
,@subproduct nvarchar(100))		--note: i'm guessing var type of this field!
	
RETURNS TABLE 
AS
RETURN 
(
 
	SELECT     vReportdataCurrentPrior.transdate, vReportdataCurrentPrior.extendedstandardCost, vReportdataCurrentPrior.BusinessEntity,
                                      vReportdataCurrentPrior.product, vReportdataCurrentPrior.site, vReportdataCurrentPrior.subproduct, vReportdataCurrentPrior.transYear,
                                      vReportdataCurrentPrior.OrderAmount_LastYear, vReportdataCurrentPrior.InvoiceAmount_LastYear,
                                      vReportdataCurrentPrior.OrderAmount_CurrentYear, vReportdataCurrentPrior.InvoiceAmount_CurrentYear, ExchangeRates.ExchangeRate,
                                      (DATEDIFF(DD, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME),
                                      CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION))
                                      AS DATETIME)) + 1) - (DATEDIFF(DD, DATEADD(DD, 6 - (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE())
                                      AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2) % 7, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE())
                                      AS DOUBLE PRECISION)) AS DATETIME)), DATEADD(DD, - ((DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, DATEADD(MONTH, 1,
                                      GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2) % 7), CAST(FLOOR(CAST(DATEADD(DD,
                                      - DATEPART(DD, DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME))) - 1)
                                      / 7 * 2 - CASE (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME))
                                      + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD,
                                      - DATEPART(DD, DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2)
                                      % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS MonthWorkingDays, (DATEDIFF(DD, @start, @end) + 1) - (DATEDIFF(DD, DATEADD(DD,
                                      6 - (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7, @start), DATEADD(DD, - ((DATEPART(DW, @end) + @@DATEFIRST - 2) % 7), @end)) - 1)
                                      / 7 * 2 - CASE (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, @end)
                                      + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS WorkingDays, EnergySites.EnergyOrg, vReportdataCurrentPrior.QTY,
                                      EnergySites.Energy_MS_SM
      FROM         vReportdataCurrentPrior INNER JOIN
                                      ExchangeRates ON MONTH(vReportdataCurrentPrior.transdate) = ExchangeRates.XRateMonth AND
                                      vReportdataCurrentPrior.transYear = ExchangeRates.XRateYear AND vReportdataCurrentPrior.currencyCode = ExchangeRates.Currency INNER JOIN
                                      EnergySites ON vReportdataCurrentPrior.site = EnergySites.SiteNumber
      WHERE     (vReportdataCurrentPrior.transdate BETWEEN @start AND @end) AND (EnergySites.EnergyOrg = @energyorg) AND (vReportdataCurrentPrior.subproduct = @subproduct)AND
                                      (vReportdataCurrentPrior.BusinessEntity = 'energy') 
)

Select allOpen in new window

Thank you.. I am going to investigate duplicates in the table before I proceed futher... it seems that there maybe duplicates however, not as many.

your are welcome, any time.