Hi,
I am doing a report with a main report and sub reports; there is two drop down parameters box: one parameter shows 4 Fiscal Quarters and another parameter shows 6 Fiscal Years. Is there any good idea to convert date consistently and how to SQL programing in the command for parameters.
Fiscal Quarter
Select one….
1 – 10/31 – 12/31
2 – 01/01 – 03/31
3 – 04/01 – 06/30
4 – 07/01 – 09/30
Fiscal Year
Select one….
2010
2011
2012
2013
2014
2015
Requirements:
1. There is a main Report need to show the data back to 6 Fiscal Year and 24 Quarters:
Example - If I select “quarter 1” parameter from Fiscal Quarter drop down box, and select Fiscal Year “2009” from Fiscal Year drop down box
Then: the report should be show data from FY 2009/Q1 to FY 2014/Q4
2. There is a sub report only need to show one Fiscal Year, the total cumulate for Quarterly:
Example - If I select “quarter 2” from FQ drop down box, and select “2009” from Fiscal Year box
Then the report should show data from October 1, 2008 to March 31, 2009 (parameter something like Date >=200810 and Date <200904)
The problems are:
1. There are different date type in different databases, they are all calendar date need to format date type
Example 1: 2014-03-31 23:59:59.997 (data type - datetime)
Example 2: 201303 (data type - Int)
2. After select the parameter the main report and two sub report need to show 6 Fiscal Years, and two sub report need to show cumulated Fiscal quarters
Example tables:
Table 1 - for main report
D_Code Event_Name Year_Month
AAL DCAV 201308
CFB MDEV 201410
….. …… ……
HUM VASW 201307
Table 2 – For sub report
C_Code C_Name Year_Month
A001 Item 1 2012-03-31 23:59:59.997
B098 Item 2 2013-06-30 23:59:59.997
D890 Item 3 2014-09-30 23:59:59.997
…… ……. ………
V090 Item 4 2014-11-30 23:59:59.997
Thanks for your help!!
I am doing a report with a main report and sub reports; there is two drop down parameters box: one parameter shows 4 Fiscal Quarters and another parameter shows 6 Fiscal Years. Is there any good idea to convert date consistently and how to SQL programing in the command for parameters.
Fiscal Quarter
Select one….
1 – 10/31 – 12/31
2 – 01/01 – 03/31
3 – 04/01 – 06/30
4 – 07/01 – 09/30
Fiscal Year
Select one….
2010
2011
2012
2013
2014
2015
Requirements:
1. There is a main Report need to show the data back to 6 Fiscal Year and 24 Quarters:
Example - If I select “quarter 1” parameter from Fiscal Quarter drop down box, and select Fiscal Year “2009” from Fiscal Year drop down box
Then: the report should be show data from FY 2009/Q1 to FY 2014/Q4
2. There is a sub report only need to show one Fiscal Year, the total cumulate for Quarterly:
Example - If I select “quarter 2” from FQ drop down box, and select “2009” from Fiscal Year box
Then the report should show data from October 1, 2008 to March 31, 2009 (parameter something like Date >=200810 and Date <200904)
The problems are:
1. There are different date type in different databases, they are all calendar date need to format date type
Example 1: 2014-03-31 23:59:59.997 (data type - datetime)
Example 2: 201303 (data type - Int)
2. After select the parameter the main report and two sub report need to show 6 Fiscal Years, and two sub report need to show cumulated Fiscal quarters
Example tables:
Table 1 - for main report
D_Code Event_Name Year_Month
AAL DCAV 201308
CFB MDEV 201410
….. …… ……
HUM VASW 201307
Table 2 – For sub report
C_Code C_Name Year_Month
A001 Item 1 2012-03-31 23:59:59.997
B098 Item 2 2013-06-30 23:59:59.997
D890 Item 3 2014-09-30 23:59:59.997
…… ……. ………
V090 Item 4 2014-11-30 23:59:59.997
Thanks for your help!!