How to use parameter in set function and calculated field in where
-
Here is a simple database example:
What I want to do is filter the results by Fiscal Year. In my mind, to accomplish this, I need to:
(1) Make a Fiscal Year Parameter (FYP) with values 2018, 2019, 2020 and 2021.
(2) Create calculated field Fiscal Year Start Date (FSD) = FYP - 1 & "-07-01".
(3) Create calculated field Fiscal Year End Date (FED) = FYP & "06-30".
(4) Use WHERE function such that Date >= FSD and Date <= FED.
Because Date Ranges for Fiscal Years are below:
(a) 2018: From 2017-07-01 to 2018-06-30
(b) 2019: From 2018-07-01 to 2019-06-30
(c) 2020: From 2019-07-01 to 2020-06-30
(d) 2021: From 2020-07-01 to 2021-06-30
Any help you can provide would be much appreciated. I have an alternative hardcode fix for this but that might require revisiting the report editor after some years to add more years etc.
-
Here is the file just in case.
-
Why not create a fiscal calendar? It's a whole one record per day. They're super helpful if you're doing stuff like sales analysis. Then this is ridiculous easy.
SELECT C.FiscalYear, SUM(S.Amount)
FROM Sales s INNER JOIN Calendar C
ON s.SaleDate = c.TheDate
GROUP BY c.FiscalYearSo easy that way that a child could do it.
-
If I am understanding you correctly, you are suggesting I make a second table which has all dates and fiscal years (maybe also fiscal periods or fiscal months) and do a join on the two tables?
That may be doable but I am not the server administrator - the data I am pulling is from the back-end which I only have visibility to via the SSRS or Microsoft Report Builder. Someone may have already created such a table in the back and I will need to find which one it is out of the millions of tables I have access to :).
-
While a Calendar table will certainly work, a little (and I do mean very little) math can be used to solve this problem. Case in point...
--===== Create and populate the Test Table on-the-fly
-- This is NOT a part of the solution... We're just generating test data here.
DROP TABLE IF EXISTS #TestTable;
SELECT TOP 1000
Date = CONVERT(DATE,DATEADD(dd,ABS(CHECKSUM(NEWID())%DATEDIFF(dd,'2017','2022')),'2017'))
,Sales = ABS(CHECKSUM(NEWID())%100)+1
INTO #TestTable
FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2
;
--===== Let's see what we get using a formula as a solution.
SELECT *
,FY = YEAR(Date)+(MONTH(Date)-1)/6 --Simple, nasty fast formula for Fiscal Year
FROM #TestTable
ORDER BY Date
;The formula would be a good thing to use as a persisted computed column in your table so that you don't have to do much of anything special in any WHERE clause.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
While a Calendar table will certainly work, a little (and I do mean very little) math can be used to solve this problem. Case in point...
--===== Create and populate the Test Table on-the-fly
-- This is NOT a part of the solution... We're just generating test data here.
DROP TABLE IF EXISTS #TestTable;
SELECT TOP 1000
Date = CONVERT(DATE,DATEADD(dd,ABS(CHECKSUM(NEWID())%DATEDIFF(dd,'2017','2022')),'2017'))
,Sales = ABS(CHECKSUM(NEWID())%100)+1
INTO #TestTable
FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2
;
--===== Let's see what we get using a formula as a solution.
SELECT *
,FY = YEAR(Date)+(MONTH(Date)-1)/6 --Simple, nasty fast formula for Fiscal Year
FROM #TestTable
ORDER BY Date
;The formula would be a good thing to use as a persisted computed column in your table so that you don't have to do much of anything special in any WHERE clause.
Both solutions don't address the problem the OP posted - which is how to query the table for the dates in the specified fiscal year. This is actually much simpler than using a calendar table or generating the fiscal year.
Just need a parameter in SSRS for the fiscal year. In the code, we then do this (embedded SQL in report):
Select ...
From Sales
Where [Date] >= datefromparts(@Parameters!FiscalYear.Value - 1, 7, 1)
And [Date] < datefromparts(@Parameters!FiscalYear.Value, 7, 1)We don't need to calculate FY since that will be defined by the parameter and can be included in the report directly. If the code is a stored procedure then that procedure would have a parameter as @fiscalYear - change the code to use that instead.
The next question is how to define the parameter itself. For that we just need some simple code to generate the fiscal years - and that is where the nice simple formula from @JeffModen comes into play. Create a new dataset and use this code, then set the parameter available values to use that dataset.
Select FiscalYear = year(getdate()) + month(getdate()) / 6 - t.n
From (Values (0), (1), (2), (3)) As t(n)Use the above code to generate the drop-down for the parameter and the available fiscal years will be determined by the current date. The next step would be to define a default value - which is a simple expression:
=IIf(Today.Month >= 7, Today.Year + 1, Today.Year)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thank you all for the quick response, despite it being a long weekend in some countries. A blend of these has certainly addressed my issue.
You guys rock!
- fahad.kazi wrote:
Thank you all for the quick response, despite it being a long weekend in some countries. A blend of these has certainly addressed my issue.
You guys rock!
Awesome... can you post your final "blend" so we can learn what worked best for you? Thanks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeffrey Williams wrote:Jeff Moden wrote:
While a Calendar table will certainly work, a little (and I do mean very little) math can be used to solve this problem. Case in point...
--===== Create and populate the Test Table on-the-fly
-- This is NOT a part of the solution... We're just generating test data here.
DROP TABLE IF EXISTS #TestTable;
SELECT TOP 1000
Date = CONVERT(DATE,DATEADD(dd,ABS(CHECKSUM(NEWID())%DATEDIFF(dd,'2017','2022')),'2017'))
,Sales = ABS(CHECKSUM(NEWID())%100)+1
INTO #TestTable
FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2
;
--===== Let's see what we get using a formula as a solution.
SELECT *
,FY = YEAR(Date)+(MONTH(Date)-1)/6 --Simple, nasty fast formula for Fiscal Year
FROM #TestTable
ORDER BY Date
;The formula would be a good thing to use as a persisted computed column in your table so that you don't have to do much of anything special in any WHERE clause.
Both solutions don't address the problem the OP posted - which is how to query the table for the dates in the specified fiscal year.
Heh... actually, it does. Notice the computed column suggestion. That would make all code going forward a simple and exact bit of criteria.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
Heh... actually, it does. Notice the computed column suggestion. That would make all code going forward a simple and exact bit of criteria.
Yes - with a computed column added to the table, but it isn't necessary. The table should already have an index on the [Date] column - and calculating the fiscal start/end dates is trivial in this case, since we have a known start of each fiscal year.
Also note - the question was asked in the SSRS 2016 forum and I assume the OP was looking for an SSRS based solution.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Jeffrey Williams wrote:Jeff Moden wrote:
Heh... actually, it does. Notice the computed column suggestion. That would make all code going forward a simple and exact bit of criteria.
Yes - with a computed column added to the table, but it isn't necessary. The table should already have an index on the [Date] column - and calculating the fiscal start/end dates is trivial in this case, since we have a known start of each fiscal year.
Also note - the question was asked in the SSRS 2016 forum and I assume the OP was looking for an SSRS based solution.
Oh... I get that it's not necessary BUT... once you add it to the table, all of the code that others have written becomes totally unnecessary not to mention the fact then the column becomes the single source of the truth. How many places would you have to change in the code if they decided that the bounds for the fiscal year need to be changed (and don't say they won't... I've seen it happen too many times)? For the formulas in the code, the answer is "everywhere" (unless you wisely built it into a table or a properly written function used by everyone with great "religion"). For the persisted column, the answer is "just in one column of the table).
Unfortunately, there may be more than one table that needs such a change. If you want the performance. you might stick with that or use a "Calendar" table, which might be slower everywhere all the time.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Of course, please note that the example I had shared was a simplistic one. Here is the SQL'ing I had done.
With CTE AS
(
SELECT
pr_time_attendance.a_employee_number
,pr_employee_mast.a_name_first
,pr_employee_mast.a_name_last
,pr_time_attendance.a_warrant
,pr_time_attendance.d_sequence_no
,pr_time_attendance.d_unit_quantity
,pr_time_attendance.d_unit_of_measure
,pr_time_attendance.a_org
,pr_time_attendance.d_proj_seg1
,pr_time_attendance.d_proj_seg2
,pr_time_attendance.a_object
,pr_time_attendance.h_date
,pr_time_attendance.d_original_rate
,LEFT(pr_time_attendance.a_org,3) as Fund
,(pr_time_attendance.d_original_rate * pr_time_attendance.d_unit_quantity) as Amount
,CASE
WHEN (MONTH(pr_time_attendance.h_date)) > 6 THEN (YEAR(pr_time_attendance.h_date) + 1)
ELSE (YEAR(pr_time_attendance.h_date))
END as FiscalYear
,CASE
WHEN (MONTH(pr_time_attendance.h_date)) > 6 THEN (MONTH(pr_time_attendance.h_date) - 6)
ELSE (MONTH(pr_time_attendance.h_date) + 6)
END as FiscalMonth
,CASE pr_time_attendance.d_proj_seg2
WHEN 'CIPMSFP' THEN '360'
WHEN 'INSPSUR' THEN '240'
WHEN 'TRANS' THEN '260'
WHEN 'WATER' THEN '510'
WHEN 'STORM' THEN 540
WHEN 'WASTE' THEN '550'
WHEN 'CIPMSTR' THEN '360'
WHEN 'CIPMSWT' THEN '513'
WHEN 'CIPMSSW' THEN '543'
WHEN 'CIPMSWW' THEN '553'
WHEN 'CIPMSUR' THEN '390'
WHEN 'CIPMSGD' THEN '330'
WHEN 'DEV' THEN '240'
ELSE '0'
END as RevFund
,CASE
WHEN LEFT(pr_time_attendance.a_org,3) = '998' THEN '0'
WHEN pr_time_attendance.d_proj_seg2 = 'NOBILLING' THEN '0'
WHEN pr_time_attendance.d_proj_seg2 = 'CIPMSFP' AND LEFT(pr_time_attendance.a_org,3) = '360' THEN '0'
WHEN pr_time_attendance.d_proj_seg2 = 'INSPSUR' AND LEFT(pr_time_attendance.a_org,3) = '240' THEN '0'
WHEN pr_time_attendance.d_proj_seg2 = 'TRANS' AND LEFT(pr_time_attendance.a_org,3) = '260' THEN '0'
WHEN pr_time_attendance.d_proj_seg2 = 'WATER' AND LEFT(pr_time_attendance.a_org,3) = '510' THEN '0'
WHEN pr_time_attendance.d_proj_seg2 = 'STORM' AND LEFT(pr_time_attendance.a_org,3) = '540' THEN '0'
WHEN pr_time_attendance.d_proj_seg2 = 'WASTE' AND LEFT(pr_time_attendance.a_org,3) = '550' THEN '0'
WHEN pr_time_attendance.d_proj_seg2 = 'CIPMSTR' AND LEFT(pr_time_attendance.a_org,3) = '360' THEN '0'
WHEN pr_time_attendance.d_proj_seg2 = 'CIPMSWT' AND LEFT(pr_time_attendance.a_org,3) = '513' THEN '0'
WHEN pr_time_attendance.d_proj_seg2 = 'CIPMSSW' AND LEFT(pr_time_attendance.a_org,3) = '543' THEN '0'
WHEN pr_time_attendance.d_proj_seg2 = 'CIPMSWW' AND LEFT(pr_time_attendance.a_org,3) = '553' THEN '0'
WHEN pr_time_attendance.d_proj_seg2 = 'CIPMSUR' AND LEFT(pr_time_attendance.a_org,3) = '390' THEN '0'
WHEN pr_time_attendance.d_proj_seg2 = 'CIPMSGD' AND LEFT(pr_time_attendance.a_org,3) = '330' THEN '0'
WHEN pr_time_attendance.d_proj_seg2 = 'DEV' AND LEFT(pr_time_attendance.a_org,3) = '240' THEN '0'
ELSE (pr_time_attendance.d_original_rate * pr_time_attendance.d_unit_quantity)
END as FundVarianceAmt
FROM
pr_time_attendance
LEFT OUTER JOIN pr_employee_mast
ON pr_time_attendance.a_employee_number = pr_employee_mast.a_employee_number
)
Select *
From CTE
WHERE
cte.d_proj_seg1 LIKE N'CIP%'
AND cte.FiscalYear = @FY
AND
(
cte.FiscalMonth >= 1
AND cte.FiscalMonth <= @FP
)
AND
(
(@Display = 'A')
OR (@Display = 'B' AND cte.FundVarianceAmt != 0)
OR (@Display = 'C' AND cte.FundVarianceAmt = 0)
)Basically, what is relevant to the question I had asked is below.
I used the following expression to figure out the Fiscal Year and Fiscal Month (this is extra, did not ask for it).
,CASE
WHEN (MONTH(pr_time_attendance.h_date)) > 6 THEN (YEAR(pr_time_attendance.h_date) + 1)
ELSE (YEAR(pr_time_attendance.h_date))
END as FiscalYear
,CASE
WHEN (MONTH(pr_time_attendance.h_date)) > 6 THEN (MONTH(pr_time_attendance.h_date) - 6)
ELSE (MONTH(pr_time_attendance.h_date) + 6)
END as FiscalMonthI enveloped the entire SQL in CTE (Common Table Expression) so I could filter using the newly created calculated fields (FiscalYear and YTD for FiscalMonth).
With CTE AS
(
SELECT ...
FROM ...
)
Select *
From CTE
WHERE
...
AND cte.FiscalYear = @FY
AND
(
cte.FiscalMonth >= 1
AND cte.FiscalMonth <= @FP
)
... -
In the code you posted - it would be much better to use @JeffModen's calculation for FY. However - because that value is calculated and he calculation is on columns in the query, indexes may not be used to filter the data. A table scan will most likely be needed causing the query to take much longer than it should.
If you have access to modify the table, then using @JeffModen's method in a computed persisted column would probably work better.
The method I proposed does not require changing the table but does allow for index usage because the filter is on the [Date] column and the calculation is based on the passed @FY parameter.
Where ...
And pr_time_attendance.h_date >= datefromparts(@FY - 1, 7, 1)
And pr_time_attendance.h_date < datefromparts(@FY, 7, 1)You can also calculate the fiscal month:
, FiscalMonth = (iif(month(pr_time_attendance.h_date) < 7, 1, 0) * 12) + month(pr_time_attendance.h_date) - 7 + 1
The 7 in the above calculation is the first month of the defined fiscal year. If your fiscal year started in October - the calculation would be:
, FiscalMonth = (iif(month(pr_time_attendance.h_date) < 10, 1, 0) * 12) + month(pr_time_attendance.h_date) - 10 + 1
You could define a variable and use that variable in your code - or you could use cross apply to DRY the code.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Hi Jeffrey,
Sorry for asking a noob question but why should I change the code if it is working for me? Is Jeff's more resource-efficient?
Also maybe my Microsoft Report Builder is buggy but it can't do assigning with =. You have to use AS instead. So in the instance you mentioned above, I will need to rewrite it as.
, ((iif(month(pr_time_attendance.h_date) < 10, 1, 0) * 12) + month(pr_time_attendance.h_date) - 10 + 1) as Fiscal Month
I think it also did not recognize datefromparts when i tried it :(.
-
The method @JeffModen proposed is much faster to calculate the fiscal year. When combined with filtering in the where clause - it would be better than your case expression.
Since you are coming from Report Builder - it will depend on the version of SQL you are querying. It does not sound like you are querying a SQL Server system - and that would be why certain things don't work.
There are other methods for calculating the date - what is used will depend on the data type of the column. Is the data type datetime or date or something else?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply