Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

Setting up a select statement based on whether a date falls in a Quarter

I am trying to set up a report for an accountant.  The report is ran on a Quarterly basis because that is when sales tax is due.  The cost of the item is in the database as well as the date like 5/10/2006.  How do I set up a Parameter that just gives the accountant a choice of 1st Quarter, 2nd Quarter, 3rd Quarter, 4th Quarter.  
0
cpursley1979
Asked:
cpursley1979
  • 3
  • 3
2 Solutions
 
janmariniCommented:
Will the data for the report always be based on the current year, or will the accountant need to specify the year as well?
0
 
mlmccCommented:
When is the report run?  Will it be run at any time? or will it be run only at the start of a quarter so you need the previous quarter's data?

mlmcc

0
 
cpursley1979Author Commented:
I belive the report will be ran at the start of the next quarter, and the previous quarter can be ran anytime after that
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
mlmccCommented:
Something like this should work in the Select Expert will allow you to get the previous quarter at any time during the quarter.  If you want to be able to get at any previous quarter then something much more complex will be required.

if (CurrentDate in Calendar1stQuarter) then
   {SaleDate} in Date(Year(CurrentDate)-1,10,1) to Date(Year(CurrentDate)-1,12,31)
else if (CurrentDate in Calendar2ndQuarter) then
   {SaleDate} in Date(Year(CurrentDate),1,1) to Date(Year(CurrentDate),3,31)
else if (CurrentDate in Calendar3rdQuarter) then
   {SaleDate} in Date(Year(CurrentDate),4,1) to Date(Year(CurrentDate),6,30)
else if (CurrentDate in Calendar4thQuarter) then
   {SaleDate} in Date(Year(CurrentDate),7,1) to Date(Year(CurrentDate),9,30)

mlmcc

0
 
janmariniCommented:

- To get data for the previous quarter based on todays date, create this record selection formula (pretty much the same as mmlc's):

switch
(
CurrentDate in Calendar1stQtr, {rptCounterListing.dtsaledate} in Date(Year(CurrentDate)-1,10,1) to Date(Year(CurrentDate)-1,12,31),
CurrentDate in Calendar2ndQtr, {rptCounterListing.dtsaledate} in Calendar1stQtr,
CurrentDate in Calendar3rdQtr, {rptCounterListing.dtsaledate} in Calendar2ndQtr,
CurrentDate in Calendar4thQtr, {rptCounterListing.dtsaledate} in Calendar3rdQtr
)


- To prompt the user to select a Quarter:
1.  Create a Parameter field called "Quarter", Prompt = "Please Select a quarter", Value Type = String, Discrete Values, Set Default values - "Qaurter 1", Quarter 2", etc.
2.  create this record selection formula

switch
(
{?Quarter} = "Quarter 1", {rptCounterListing.dtsaledate} in Calendar1stQtr,
{?Quarter} = "Quarter 2", {rptCounterListing.dtsaledate} in Calendar2ndQtr,
{?Quarter} = "Quarter 3", {rptCounterListing.dtsaledate} in Calendar3rdQtr,
{?Quarter} = "Quarter 4", {rptCounterListing.dtsaledate} in Date(Year(CurrentDate)-1,10,1) to Date(Year(CurrentDate)-1,12,31)
)
0
 
janmariniCommented:
oops - replace my date field with your's, forgot to clean that up :-)
0
 
mlmccCommented:
Glad i could help

mlmcc
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now