Solved

SSRS 2005 Reports and Stored Procedures

Posted on 2010-11-11
16
359 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Copy Database Wizard 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 shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

829 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