AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of SPLady
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?
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)

Open in new window

Microsoft SQL Server 2005SSRSDB Reporting ToolsMicrosoft SQL Server.NET Programming
Avatar of undefined
Last Comment
SPLady
8/22/2022 - Mon
vdr1620
well, it would be better to handle at the Query level as you will have more flexibility
SPLady
ASKER
@vdr1620 can you see what I am doing wrong or what needs to be fixed?
vdr1620
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
SPLady
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.transdate BETWEEN @start AND @end) AND (EnergySites.EnergyOrg = @energyorg) AND
                      (vReportdataCurrentPrior.BusinessEntity = 'energy') and ( MONTH(vReportdataCurrentPrior.transdate) = @MONTH)
 

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

Open in new window

vdr1620
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
vdr1620
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SPLady
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

Open in new window

SPLady
ASKER
@modus operandi Thank you! :)
Bhavesh Shah
Hi Elaine,

can you post your final query????
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SPLady
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)

Open in new window

Bhavesh Shah
eliane...
thx for that.

its late night,so need 2 go for sleeping...will reply u tomorow.....

sory

tc

bye
ASKER CERTIFIED SOLUTION
CtrlAltDl
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SPLady
ASKER
Awesome, awesome, awesome.. Finally! Thank you @CtrlAtlDl :)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent