Crystal Reports - select current quarter

Have report  that is linked to a table called "fiscalperiods" that has all the start and end date for each fiscal month and quarter of the year it belongs.  I need this report to also pull data for the current quarter in the select expert so that i dont have to update each quarter.

Fiscalperiods table fields:
Begindate  (begin date of the fiscal month)
Enddate (End date of the fiscal month)
Fiscalmonth  (fiscal month number)
fiscalyear  (Fiscal year)
qtr  (quarter the fiscal month is in)
kelsanitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
DO the quarters correspond to the normal calendar quarters?

Try this

Formula
Name - Start of Quarter
if Month(CurrentDate) in [1,2,3] then
   Date(Year(CurrentDate),1,1)
Else if Month(CurrentDate) in [4,5,6] then
   Date(Year(CurrentDate),4,1)
Else if Month(CurrentDate) in [7,8,9] then
   Date(Year(CurrentDate),7,1)
Else 
   Date(Year(CurrentDate),10,1)

Open in new window


Similarly for the end of quarter
if Month(CurrentDate) in [1,2,3] then
   Date(Year(CurrentDate),3,31)
Else if Month(CurrentDate) in [4,5,6] then
   Date(Year(CurrentDate),6,30)
Else if Month(CurrentDate) in [7,8,9] then
   Date(Year(CurrentDate),9,30)
Else 
   Date(Year(CurrentDate),12,31)

Open in new window


mlmcc
0
kelsanitAuthor Commented:
No, the dates are fiscal dates which is why I have to link to that table to lookup the date ranges.  

I think if i just had a formula that would identify the current quarter by using the current date and see which fiscal period it falls you coudl just return the quater and I coudl use that in the select expert.

Just not sure how to write that...
0
mlmccCommented:
IS the date table linked into the report?

How did you link it in?

mlmcc
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

James0628Commented:
Does your main data include FiscalYear and Qtr fields, or does it just have a date field?

 If it just has a date field, I guess the basic idea would be to find the record in FiscalPeriods where the current date is >= BeginDate and <= EndDate.  That would give you the FiscalYear and Qtr.  Then you could read the FiscalPeriods records for that FiscalYear and Qtr, and the minimum BeginDate and maximum EndDate would give you the starting and ending dates for the fiscal quarter.

 Does that sound correct?

 If so, this might be easier if you wrote your own query, rather than trying to link the tables in CR and let it create the query for you.  It could be something like a view or stored procedure in the db, or a Command in CR.

 Another option might be to use SQL Expressions.  I never really used them, so I'm not sure about that.  What db are you using?  SQL Expressions may not be an option for some db's.

 James
0
kelsanitAuthor Commented:
It is linked to the "Fiscal Periods" table on fymonth & fyyear from the sales table. (see attached image)

I am just looking to put somethign in the select expert to automatically know which quarter we are in and run data for vs. having to manually updated this field in the select expert each month.
screenshot.jpg
0
mlmccCommented:
Try this

CurrentDate >= {FIscalPeriods.BeginDate} AND CurrentDate <={FiscalPeriods.EndDate}

mlmcc
0
kelsanitAuthor Commented:
that just returns a false value
0
mlmccCommented:
Put it in the report filter.

mlmcc
0
kelsanitAuthor Commented:
that works to retrun the current fiscal month but not the entire date range for the quarter which includes three fiscal periods:

example:
fiscal month =1
begindate = 3/28/2015
Enddate = 4/26/2015
qtr = 1

fiscal month =2
begindate = 4/27/2015
Enddate = 05/24/2015
qtr = 1

fiscal month =3
begindate = 5/25/2015
Enddate = 6/28/2015
qtr = 1
0
mlmccCommented:
Ok.  I misinterpreted the data in the table.

Are the quarter dates fixed year to year? or do you have 4/4/5 quarter?

mlmcc
0
mlmccCommented:
How are you building the SQL?  Do you have a command or is it just tables?

mlmcc
0
kelsanitAuthor Commented:
They are not fixed  we are on 4/4/5 fiscal calandar

using two tables -  salesdata table and Fiscal periods
0
James0628Commented:
I guess it might be possible to calculate the quarter dates, using a known date as the starting point.  For example, if 06/28/15 is the end of the 1st quarter that year, you might be able to determine the current quarter, and the date range for that quarter, by looking at how many weeks it has been since 06/28/15.

 Otherwise, I guess it's basically the situation I described earlier.  The FiscalPeriods record for the current date gives you the Qtr and Year.  The FiscalPeriods records for that Qtr and Year give you the starting and ending FiscalMonth and FiscalYear.

 I did think of another way that you might be able to handle that (rather than write your own query, or use a SQL Expression).

 Add FiscalPeriods to the report a second time.  CR will let you include the same table twice.  It will append something like "_1" to the end of the name for the second copy.

 The first copy of FiscalPeriods is linked to your sales table by FiscalMonth and FiscalYear.

 The second copy of FiscalPeriods would be linked to the first copy of FiscalPeriods by Qtr and Year.

 Your record selection formula would be the one that mlmcc posted earlier, but it needs to check the BeginDate and EndDate in the second copy of FiscalPeriods (FiscalPeriods_1, or whatever CR Calls it):

CurrentDate >= {FIscalPeriods_1.BeginDate} AND CurrentDate <={FiscalPeriods_1.EndDate}

 The idea is that CR looks up the current date in the second copy of FiscalPeriods.  That gives it the quarter and year.  Then the Qtr and Year link from the second copy of FiscalPeriods to the first finds all of the records in FiscalPeriods for that quarter.  Then the link from the first copy of FiscalPeriods to your sales table will pull in the sales records for any of the dates in those FiscalPeriods records (ie. in the quarter).

 It seems like that would work.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kelsanitAuthor Commented:
GREAT- That worked.
0
James0628Commented:
Glad to hear it.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.