Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: financial year reports Post Reply Post New Topic
Author Message
iankerry999
Newbie
Newbie


Joined: 31 Jul 2013
Location: United Kingdom
Online Status: Offline
Posts: 6
Quote iankerry999 Replybullet Topic: financial year reports
     Posted: 31 Jul 2013 at 11:55pm
Hi
 
I noticed that a few people have asked this question, but i am afraid being a novice with CRIX I struggle to understand the answers!
 
I have reports linked to my access database (queries) going back for many years. I have lots of reports for instance on Average Audience Numbers for my 800 events a year.
 
To date I have only done reports based on calendar years, but I really need to do report based on our financial year April 1st to March 31st, to match up with our funders requirements (we are a charity).
 
Does anyone have a step by step guide to how i might achieve this? Would really appreciate it!
 
Thanks
 
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 02 Aug 2013 at 4:53am
I am guessing that your reports have some date range, maybe defaulting to jan 1 of this yr until today--or perhaps better yet, would be just the year desired.

if so, then what you need to do is to change this date range in your code. Since jan1-march31 of this year is part of LAST fiscal yr, you would want to exclude them...and the converse when you are in jan1-march31 of a year you want to include apr1-dec31, as well as the jan1-date. So...

I would think that a formula (or an addition to the record selection criteria) would be something like this, assuming you are inputting a year desired (fiscal would be nice ;) )

if YEAR({table.field}) = {?fiscalYear} AND MONTH({table.field}) >= 4 then
true //include the record...apr or later
ELSE
if YEAR({table.field}) = {?fiscalYear} + 1 AND MONTH({table.field}) < 4 then
    true //include...first 3 months of prior year
else
    false //not in the months you are looking for.

This is premised on the fiscal year being the year that you are starting...fiscal 2013 is apr2013 - march2014...otherwise you will some slight tweaking of signs.

HTH
IP IP Logged
iankerry999
Newbie
Newbie


Joined: 31 Jul 2013
Location: United Kingdom
Online Status: Offline
Posts: 6
Quote iankerry999 Replybullet Posted: 03 Aug 2013 at 12:28am
Hi
 
Thanks so much for replying.
 
I probably need to explain what i have at the moment in more detail. My report covers 11 (calendar years). So I have a bar graph at the top which shows me the average audience for each CYr.
 
What I need is for the Bar Chart (and report of ocurse) to show me the average audience for each Financial Yr.
 
So at the moment I don't have any code (haven't really used code before).
 
By using Group Expert, I have grouped the data by the [event date] field into 'for each year'.
 
I hope this helps to clarify and that you are able to assist further.  I would send a copy of the report but can't see how to attach anything to htis post.
 
Best wishes,
 
Ian
 


Edited by iankerry999 - 03 Aug 2013 at 12:29am
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 05 Aug 2013 at 4:43am
hmm...
that makes it a bit more difficult, but not much...well maybe.

If the data was being retrieved via a stored proc, it would be simple to add a column that would be the fiscal year, and then you could just group on that.

without a stored proc, we just have to create a formula that mimics the determination of the fiscal year for the event date...
local numbervar fiscalyr := year({table.eventDate}); // not a bad default, most of the months are fiscal year
if month({table.eventDate}) < 4 then //jan/feb/mar
fiscalyr := fiscalyr - 1; //in the prior yr
fiscalyr //displays the fiscal yr

now that we have the formula, we can use it to group the data, and presumably the graph...I haven't used graphs, as the reports have been all numbers.

HTH
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum