Solved

SSRS 2005 Reports and Stored Procedures

Posted on 2010-11-11
16
362 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

Suggested Solutions

Title # Comments Views Activity
how many extra RAM for SQL server is needed 23 49
SQL Trigger or Function that updates table with old values 5 56
ms sql + top 1 for each customer 3 70
SQL- GROUP BY 4 51
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 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