Solved

SSRS 2005 Reports and Stored Procedures

Posted on 2010-11-11
16
349 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now