Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SSRS 2005 Reports and Stored Procedures

Posted on 2010-11-11
16
Medium Priority
?
373 Views
Last Modified: 2012-08-14
Hi Experts!

I am up against a hard deadline and need help and an extra pair of eyes on this SP. I an a newbie to complex ssrs reports. The SP is returning duplicate rows in the sever explorer. In addition I am not sure if I can delete columns because it seems to be functioning as 2 distinct functions( not sure of the terminology).

 I also dont know how to set this up as a ssrs report.I have tried several matrix and simple  reports configurations but, the columns only return 2010 data in the solution explorer.
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)

Open in new window

0
Comment
Question by:SPLady
  • 8
  • 8
16 Comments
 
LVL 4

Expert Comment

by:Amgad_Consulting_Co
ID: 34115867
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
0
 
LVL 1

Author Comment

by:SPLady
ID: 34115959
Thank you @Amqad.. Still a bit loss... I am using distinct
0
 
LVL 4

Expert Comment

by:Amgad_Consulting_Co
ID: 34116063
and what about joins, be sure to have them joined correctly
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:SPLady
ID: 34116507
That would cause duplicates? I tought I haad used several (4) unique identifiers
0
 
LVL 1

Author Comment

by:SPLady
ID: 34116525
*Thought, had
0
 
LVL 4

Expert Comment

by:Amgad_Consulting_Co
ID: 34118097
can you send me a sample of the duplicated O/P ?
0
 
LVL 1

Author Comment

by:SPLady
ID: 34124000

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

Open in new window

0
 
LVL 4

Expert Comment

by:Amgad_Consulting_Co
ID: 34124645
can you send the data in excel file, and highlight duplicates, sorry but the sample data is overlapping.
0
 
LVL 1

Author Comment

by:SPLady
ID: 34124714
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
0
 
LVL 4

Expert Comment

by:Amgad_Consulting_Co
ID: 34124772
so, it might be a problem in the function "dbo.Order_Analysis_Select" getting duplicate data, can you check it or post it  here ?
0
 
LVL 1

Author Comment

by:SPLady
ID: 34124843
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)

Open in new window

0
 
LVL 4

Expert Comment

by:Amgad_Consulting_Co
ID: 34124890
the function, you are using for getting data
0
 
LVL 1

Author Comment

by:SPLady
ID: 34124922
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') 
)

Open in new window

0
 
LVL 4

Accepted Solution

by:
Amgad_Consulting_Co earned 2000 total points
ID: 34124963
the joins seems to be correct, although I'm not quite sure, can you double check on them, also have you check the tables itself, it might be some duplicates there!!
0
 
LVL 1

Author Closing Comment

by:SPLady
ID: 34125159
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.
0
 
LVL 4

Expert Comment

by:Amgad_Consulting_Co
ID: 34125174
your are welcome, any time.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

564 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