SPLady
asked on
Dates, Parameters, and Report Designer
Hi Experts
I am using this query in a SSRS 2005 report and am having a few problems
1. when the user select the date if the date is for example 10/01/2010-10/31/2010 the previous year data is zero
2. when the user uses 10/01/2009-10/31/2010 the working days calculation is based on all the business days between 2009-2100
The way I would like it to work is
1. when the user selects 10/01/2010-10/31/2010 it displays the current year data and previous.
2. When the date is entered the business days calculated is based on the current year data
Is there a way to address this in the report designer (filtering, matrix)or should it be address at the query level?
I am using this query in a SSRS 2005 report and am having a few problems
1. when the user select the date if the date is for example 10/01/2010-10/31/2010 the previous year data is zero
2. when the user uses 10/01/2009-10/31/2010 the working days calculation is based on all the business days between 2009-2100
The way I would like it to work is
1. when the user selects 10/01/2010-10/31/2010 it displays the current year data and previous.
2. When the date is entered the business days calculated is based on the current year data
Is there a way to address this in the report designer (filtering, matrix)or should it be address at the query level?
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.BusinessEntity = 'energy') and ( MONTH(vReportdataCurrentPrior.transdate) = @MONTH)
well, it would be better to handle at the Query level as you will have more flexibility
ASKER
@vdr1620 can you see what I am doing wrong or what needs to be fixed?
In the WHERE clause you are using transdate BETWEEN Start and End with other AND Conditions.. basically you will get only data between those periods..
you will need to rewrite your WHERE Clause more carefully.. It will be very time consuming for me to find the problem without looking at the data or without any sense of your result set..
If, I were you, i would start with Where Clause as it would restrict the result set
you will need to rewrite your WHERE Clause more carefully.. It will be very time consuming for me to find the problem without looking at the data or without any sense of your result set..
If, I were you, i would start with Where Clause as it would restrict the result set
ASKER
Thank you @vdr1620
what would that type of where clause look like ? Could I add another set of date parameters that are exclusive to the the workingDays clause/expression(not sure of the terminology) and if so, what changes are need in the workingDays clause/expression
WHERE (vReportdataCurrentPrior.t ransdate BETWEEN @start AND @end) AND (EnergySites.EnergyOrg = @energyorg) AND
(vReportdataCurrentPrior.B usinessEnt ity = 'energy') and ( MONTH(vReportdataCurrentPr ior.transd ate) = @MONTH)
what would that type of where clause look like ? Could I add another set of date parameters that are exclusive to the the workingDays clause/expression(not sure of the terminology) and if so, what changes are need in the workingDays clause/expression
WHERE (vReportdataCurrentPrior.t
(vReportdataCurrentPrior.B
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
just noticed this in your original post
>>>1. when the user select the date if the date is for example 10/01/2010-10/31/2010 the previous year data is zero<<<
you will need to check how you are calculating the previous year date.. It would be easier for you to tell me how you are calculating it.. so that we can find another way and test it to see if that solves the problem
>>>1. when the user select the date if the date is for example 10/01/2010-10/31/2010 the previous year data is zero<<<
you will need to check how you are calculating the previous year date.. It would be easier for you to tell me how you are calculating it.. so that we can find another way and test it to see if that solves the problem
i will not be able to easily tell you about what your where clause should be without having any sense of your data and tables
ASKER
Pretty rudimentary
SELECT *
FROM (SELECT QTY, transdate, extendedstandardCost, currencyCode, BusinessEntity, product, site, lineValue AS 'OrderAmount', Cast(0 AS NUMERIC(30,
2)) AS 'InvoiceAmount', subproduct, transYear, CASE WHEN TransYear = Year(getdate()) - 1 THEN LineValue ELSE Cast(0 AS Numeric(30, 2))
END AS 'OrderAmount_LastYear', Cast(0 AS Numeric(30, 2)) 'InvoiceAmount_LastYear', CASE WHEN TransYear = Year(getdate())
THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) END AS 'OrderAmount_CurrentYear', Cast(0 AS Numeric(30, 2))
'InvoiceAmount_CurrentYear'
FROM vReportData
WHERE (recType = 'order') AND (businessEntity = 'Energy')
UNION
SELECT QTY, transdate, extendedstandardCost, currencyCode, BusinessEntity, product, site, 0 AS 'OrderAmount', lineValue AS 'InvoiceAmount',
subproduct, transYear, Cast(0 AS Numeric(30, 2)) 'OrderAmount_LastYear', CASE WHEN TransYear = Year(getdate())
- 1 THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) END AS 'InvoiceAmount_LastYear', Cast(0 AS Numeric(30, 2))
'OrderAmount_CurrentYear', CASE WHEN TransYear = Year(getdate()) THEN LineValue ELSE Cast(0 AS Numeric(30, 2))
END AS 'InvoiceAmount_CurrentYear'
FROM vReportData AS ReportData1
WHERE (recType = 'invoice') AND (businessEntity = 'Energy')) A,
(SELECT 20 + COUNT(*) WorkDayCount
FROM (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 28) AS theDate
UNION
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 29)
UNION
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 30)) AS d
WHERE DATEPART(DAY, theDate) > 28 AND DATEDIFF(DAY, 0, theDate) % 7 < 5) B
ASKER
@modus operandi Thank you! :)
Hi Elaine,
can you post your final query????
can you post your final query????
ASKER
Hi Birchsoft
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
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.BusinessEntity = 'energy') AND (MONTH(vReportdataCurrentPrior.transDate) = @MONTH)
eliane...
thx for that.
its late night,so need 2 go for sleeping...will reply u tomorow.....
sory
tc
bye
thx for that.
its late night,so need 2 go for sleeping...will reply u tomorow.....
sory
tc
bye
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome, awesome, awesome.. Finally! Thank you @CtrlAtlDl :)