Calulating FY Totals

G

Guest

Hi.

I have a DB that was created by another user that I am trying to fix.
Ultimately, I need to produce a report showing monthly and fiscal year
totals. Currently, the report is showing the monthly totals, but I have no
idea how to define the FY. Our fiscal year is 1 April. 2005 to 31 March,
2006. The "report" is pulling from several different queries, including a
crosstab query that pushes all months (Jan, Feb, Mar, etc) across the top of
the page. Below is the SQL version of the query that was developed by
someone else that initially pulled the report. Below that is the SQL version
of the query I had to use that includes the incorporation of the crosstab
query.

SELECT Opportunities.TaskID, Opportunities.OQID, Opportunities.OppName,
Bookings.BusAreaID, Opportunities.Probability, DatePart("m",[BookDate]) AS
BookMonth, DatePart("yyyy",[BookDate]) AS BookYear, Bookings.BookAmount,
[BookAmount]*[Probability] AS [Weighted Bookings], Bookings.BookDate
FROM Opportunities INNER JOIN Bookings ON Opportunities.OppID = Bookings.OppID
WHERE (((DatePart("yyyy",[BookDate]))="2006") AND ((Opportunities.OppStatus)
Like "New" Or (Opportunities.OppStatus)="Potential" Or
(Opportunities.OppStatus)="Developing" Or (Opportunities.OppStatus)="Mature"
Or (Opportunities.OppStatus)="Contracting"));

BELOW IS THE ACTUAL QUERY THAT THE REPORT IS TIED TO:

SELECT [FY 2006 Forecast].OQID, [FY 2006 Forecast].OppName, [FY 2006
Forecast].BusAreaID, [FY 2006 Forecast].Probability, [FY 2006
Forecast_Crosstab1].Jan, [FY 2006 Forecast_Crosstab1].Feb, [FY 2006
Forecast_Crosstab1].Mar, [FY 2006 Forecast_Crosstab1].Apr, [FY 2006
Forecast_Crosstab1].May, [FY 2006 Forecast_Crosstab1].Jun, [FY 2006
Forecast_Crosstab1].Jul, [FY 2006 Forecast_Crosstab1].Aug, [FY 2006
Forecast_Crosstab1].Sep, [FY 2006 Forecast_Crosstab1].Oct, [FY 2006
Forecast_Crosstab1].Nov, [FY 2006 Forecast_Crosstab1].Dec, [FY 2006
Forecast].[Weighted Bookings], [FY 2006 Forecast].BookAmount, [FY 2006
Forecast_Crosstab1].[Total Of BookAmount]
FROM [FY 2006 Forecast] INNER JOIN [FY 2006 Forecast_Crosstab1] ON [FY 2006
Forecast].OppName = [FY 2006 Forecast_Crosstab1].OppName;

BELOW IS THE CROSSTAB QUERY:

TRANSFORM Sum([FY 2006 Forecast].BookAmount) AS SumOfBookAmount
SELECT [FY 2006 Forecast].OppName, Sum([FY 2006 Forecast].BookAmount) AS
[Total Of BookAmount]
FROM [FY 2006 Forecast]
GROUP BY [FY 2006 Forecast].OppName
PIVOT Format([BookDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Please help. I have been working with the DB for 3 weeks now, and am hardly
making any headway.

THANK YOU!!!
 
D

David Lloyd

Cyndi:

Just a few observations regarding your queries. You can change the order of
the months in the crosstab query to reflect your fiscal year by changing the
"Pivot In" line of the crosstab. Your predecessor defined them starting
with Jan, Feb, ...

From what you are saying it sounds as though the report is hard coded to
display the monthly data starting with January. The order of the months in
the query behind the report is not critical. In order to adjust how they
display on the report, you will need to change the Control Source property
of each of the textboxes in the report.

The queries are using the "BookDate" to determine the fiscal year. I do not
know whether this is correct, but that is how the queries are setup.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hi.

I have a DB that was created by another user that I am trying to fix.
Ultimately, I need to produce a report showing monthly and fiscal year
totals. Currently, the report is showing the monthly totals, but I have no
idea how to define the FY. Our fiscal year is 1 April. 2005 to 31 March,
2006. The "report" is pulling from several different queries, including a
crosstab query that pushes all months (Jan, Feb, Mar, etc) across the top of
the page. Below is the SQL version of the query that was developed by
someone else that initially pulled the report. Below that is the SQL
version
of the query I had to use that includes the incorporation of the crosstab
query.

SELECT Opportunities.TaskID, Opportunities.OQID, Opportunities.OppName,
Bookings.BusAreaID, Opportunities.Probability, DatePart("m",[BookDate]) AS
BookMonth, DatePart("yyyy",[BookDate]) AS BookYear, Bookings.BookAmount,
[BookAmount]*[Probability] AS [Weighted Bookings], Bookings.BookDate
FROM Opportunities INNER JOIN Bookings ON Opportunities.OppID =
Bookings.OppID
WHERE (((DatePart("yyyy",[BookDate]))="2006") AND ((Opportunities.OppStatus)
Like "New" Or (Opportunities.OppStatus)="Potential" Or
(Opportunities.OppStatus)="Developing" Or (Opportunities.OppStatus)="Mature"
Or (Opportunities.OppStatus)="Contracting"));

BELOW IS THE ACTUAL QUERY THAT THE REPORT IS TIED TO:

SELECT [FY 2006 Forecast].OQID, [FY 2006 Forecast].OppName, [FY 2006
Forecast].BusAreaID, [FY 2006 Forecast].Probability, [FY 2006
Forecast_Crosstab1].Jan, [FY 2006 Forecast_Crosstab1].Feb, [FY 2006
Forecast_Crosstab1].Mar, [FY 2006 Forecast_Crosstab1].Apr, [FY 2006
Forecast_Crosstab1].May, [FY 2006 Forecast_Crosstab1].Jun, [FY 2006
Forecast_Crosstab1].Jul, [FY 2006 Forecast_Crosstab1].Aug, [FY 2006
Forecast_Crosstab1].Sep, [FY 2006 Forecast_Crosstab1].Oct, [FY 2006
Forecast_Crosstab1].Nov, [FY 2006 Forecast_Crosstab1].Dec, [FY 2006
Forecast].[Weighted Bookings], [FY 2006 Forecast].BookAmount, [FY 2006
Forecast_Crosstab1].[Total Of BookAmount]
FROM [FY 2006 Forecast] INNER JOIN [FY 2006 Forecast_Crosstab1] ON [FY 2006
Forecast].OppName = [FY 2006 Forecast_Crosstab1].OppName;

BELOW IS THE CROSSTAB QUERY:

TRANSFORM Sum([FY 2006 Forecast].BookAmount) AS SumOfBookAmount
SELECT [FY 2006 Forecast].OppName, Sum([FY 2006 Forecast].BookAmount) AS
[Total Of BookAmount]
FROM [FY 2006 Forecast]
GROUP BY [FY 2006 Forecast].OppName
PIVOT Format([BookDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Please help. I have been working with the DB for 3 weeks now, and am hardly
making any headway.

THANK YOU!!!
 
G

Guest

Hi David!!

"Pivot In?" Hmm...not sure how to do that. My predecessor acutally set the
"Book Month" part, and I added the crosstab query to break it out by Month.
I just can't seem to get the total's to only show dates from 1 April, 2005
through 31 March, 2006. There is a lot of financial data in the DB, but the
"bookings" table contains most of the information that is being pulled for
the report. I don't know what formula to use or how to hard code it to
choose ONLY the dates that I need.
--
Cyndi


David Lloyd said:
Cyndi:

Just a few observations regarding your queries. You can change the order of
the months in the crosstab query to reflect your fiscal year by changing the
"Pivot In" line of the crosstab. Your predecessor defined them starting
with Jan, Feb, ...

From what you are saying it sounds as though the report is hard coded to
display the monthly data starting with January. The order of the months in
the query behind the report is not critical. In order to adjust how they
display on the report, you will need to change the Control Source property
of each of the textboxes in the report.

The queries are using the "BookDate" to determine the fiscal year. I do not
know whether this is correct, but that is how the queries are setup.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hi.

I have a DB that was created by another user that I am trying to fix.
Ultimately, I need to produce a report showing monthly and fiscal year
totals. Currently, the report is showing the monthly totals, but I have no
idea how to define the FY. Our fiscal year is 1 April. 2005 to 31 March,
2006. The "report" is pulling from several different queries, including a
crosstab query that pushes all months (Jan, Feb, Mar, etc) across the top of
the page. Below is the SQL version of the query that was developed by
someone else that initially pulled the report. Below that is the SQL
version
of the query I had to use that includes the incorporation of the crosstab
query.

SELECT Opportunities.TaskID, Opportunities.OQID, Opportunities.OppName,
Bookings.BusAreaID, Opportunities.Probability, DatePart("m",[BookDate]) AS
BookMonth, DatePart("yyyy",[BookDate]) AS BookYear, Bookings.BookAmount,
[BookAmount]*[Probability] AS [Weighted Bookings], Bookings.BookDate
FROM Opportunities INNER JOIN Bookings ON Opportunities.OppID =
Bookings.OppID
WHERE (((DatePart("yyyy",[BookDate]))="2006") AND ((Opportunities.OppStatus)
Like "New" Or (Opportunities.OppStatus)="Potential" Or
(Opportunities.OppStatus)="Developing" Or (Opportunities.OppStatus)="Mature"
Or (Opportunities.OppStatus)="Contracting"));

BELOW IS THE ACTUAL QUERY THAT THE REPORT IS TIED TO:

SELECT [FY 2006 Forecast].OQID, [FY 2006 Forecast].OppName, [FY 2006
Forecast].BusAreaID, [FY 2006 Forecast].Probability, [FY 2006
Forecast_Crosstab1].Jan, [FY 2006 Forecast_Crosstab1].Feb, [FY 2006
Forecast_Crosstab1].Mar, [FY 2006 Forecast_Crosstab1].Apr, [FY 2006
Forecast_Crosstab1].May, [FY 2006 Forecast_Crosstab1].Jun, [FY 2006
Forecast_Crosstab1].Jul, [FY 2006 Forecast_Crosstab1].Aug, [FY 2006
Forecast_Crosstab1].Sep, [FY 2006 Forecast_Crosstab1].Oct, [FY 2006
Forecast_Crosstab1].Nov, [FY 2006 Forecast_Crosstab1].Dec, [FY 2006
Forecast].[Weighted Bookings], [FY 2006 Forecast].BookAmount, [FY 2006
Forecast_Crosstab1].[Total Of BookAmount]
FROM [FY 2006 Forecast] INNER JOIN [FY 2006 Forecast_Crosstab1] ON [FY 2006
Forecast].OppName = [FY 2006 Forecast_Crosstab1].OppName;

BELOW IS THE CROSSTAB QUERY:

TRANSFORM Sum([FY 2006 Forecast].BookAmount) AS SumOfBookAmount
SELECT [FY 2006 Forecast].OppName, Sum([FY 2006 Forecast].BookAmount) AS
[Total Of BookAmount]
FROM [FY 2006 Forecast]
GROUP BY [FY 2006 Forecast].OppName
PIVOT Format([BookDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Please help. I have been working with the DB for 3 weeks now, and am hardly
making any headway.

THANK YOU!!!
Click to expand...
 
D

David Lloyd

Cyndi:

The "Pivot In" syntax I was referring to is already in your crosstab query
in the line:

PIVOT Format([BookDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

If you change the order of the months, for example Apr., May, ... then the
column order in the crosstab query will change. You can change this line in
SQL view or by changing the ColumnHeadings property of the query in the
query designer.

From what I can tell the dates are being determined by this line in the
first query.

WHERE (((DatePart("yyyy",[BookDate]))="2006") ...

If you want to constrain the query to show dates between 4/1/05 and 3/31/06,
then you could do something like:

WHERE (([BookDate] BETWEEN #4/1/05# AND #3/31/06#) ...

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hi David!!

"Pivot In?" Hmm...not sure how to do that. My predecessor acutally set the
"Book Month" part, and I added the crosstab query to break it out by Month.
I just can't seem to get the total's to only show dates from 1 April, 2005
through 31 March, 2006. There is a lot of financial data in the DB, but the
"bookings" table contains most of the information that is being pulled for
the report. I don't know what formula to use or how to hard code it to
choose ONLY the dates that I need.
--
Cyndi


David Lloyd said:
Cyndi:

Just a few observations regarding your queries. You can change the order of
the months in the crosstab query to reflect your fiscal year by changing the
"Pivot In" line of the crosstab. Your predecessor defined them starting
with Jan, Feb, ...

From what you are saying it sounds as though the report is hard coded to
display the monthly data starting with January. The order of the months in
the query behind the report is not critical. In order to adjust how they
display on the report, you will need to change the Control Source property
of each of the textboxes in the report.

The queries are using the "BookDate" to determine the fiscal year. I do not
know whether this is correct, but that is how the queries are setup.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hi.

I have a DB that was created by another user that I am trying to fix.
Ultimately, I need to produce a report showing monthly and fiscal year
totals. Currently, the report is showing the monthly totals, but I have no
idea how to define the FY. Our fiscal year is 1 April. 2005 to 31 March,
2006. The "report" is pulling from several different queries, including a
crosstab query that pushes all months (Jan, Feb, Mar, etc) across the top of
the page. Below is the SQL version of the query that was developed by
someone else that initially pulled the report. Below that is the SQL
version
of the query I had to use that includes the incorporation of the crosstab
query.

SELECT Opportunities.TaskID, Opportunities.OQID, Opportunities.OppName,
Bookings.BusAreaID, Opportunities.Probability, DatePart("m",[BookDate]) AS
BookMonth, DatePart("yyyy",[BookDate]) AS BookYear, Bookings.BookAmount,
[BookAmount]*[Probability] AS [Weighted Bookings], Bookings.BookDate
FROM Opportunities INNER JOIN Bookings ON Opportunities.OppID =
Bookings.OppID
WHERE (((DatePart("yyyy",[BookDate]))="2006") AND ((Opportunities.OppStatus)
Like "New" Or (Opportunities.OppStatus)="Potential" Or
(Opportunities.OppStatus)="Developing" Or (Opportunities.OppStatus)="Mature"
Or (Opportunities.OppStatus)="Contracting"));

BELOW IS THE ACTUAL QUERY THAT THE REPORT IS TIED TO:

SELECT [FY 2006 Forecast].OQID, [FY 2006 Forecast].OppName, [FY 2006
Forecast].BusAreaID, [FY 2006 Forecast].Probability, [FY 2006
Forecast_Crosstab1].Jan, [FY 2006 Forecast_Crosstab1].Feb, [FY 2006
Forecast_Crosstab1].Mar, [FY 2006 Forecast_Crosstab1].Apr, [FY 2006
Forecast_Crosstab1].May, [FY 2006 Forecast_Crosstab1].Jun, [FY 2006
Forecast_Crosstab1].Jul, [FY 2006 Forecast_Crosstab1].Aug, [FY 2006
Forecast_Crosstab1].Sep, [FY 2006 Forecast_Crosstab1].Oct, [FY 2006
Forecast_Crosstab1].Nov, [FY 2006 Forecast_Crosstab1].Dec, [FY 2006
Forecast].[Weighted Bookings], [FY 2006 Forecast].BookAmount, [FY 2006
Forecast_Crosstab1].[Total Of BookAmount]
FROM [FY 2006 Forecast] INNER JOIN [FY 2006 Forecast_Crosstab1] ON [FY 2006
Forecast].OppName = [FY 2006 Forecast_Crosstab1].OppName;

BELOW IS THE CROSSTAB QUERY:

TRANSFORM Sum([FY 2006 Forecast].BookAmount) AS SumOfBookAmount
SELECT [FY 2006 Forecast].OppName, Sum([FY 2006 Forecast].BookAmount) AS
[Total Of BookAmount]
FROM [FY 2006 Forecast]
GROUP BY [FY 2006 Forecast].OppName
PIVOT Format([BookDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Please help. I have been working with the DB for 3 weeks now, and am hardly
making any headway.

THANK YOU!!!
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sub-report not showing up 3
IF function 3
Problem with Update Query 5
Add FY to Report header 3
Totals in A Report 1
adding one more column to corss tab 1
Fiscal Period 2
Sorting a date field on Year and Month 8

Top