Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Would an array work here? Post Reply Post New Topic
Author Message
yggdrasil
Senior Member
Senior Member
Avatar

Joined: 19 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 150
Quote yggdrasil Replybullet Topic: Would an array work here?
    Posted: 19 Dec 2007 at 2:46am
I have a report with a complicated record selection based on dates determined in a formula. The problem is, I have to run for two different sets of dates in one report. At the moment I have a formula (called halfterm) which I set to 1 for the first part of a school term, and 2 for the second part which determines which set of dates is used. The report is running ok for either, (producing data in a group footer for a number of school terms, but only first or second half). What I need is to be able to run the report first for 1 and then for 2 in one go.
I have been looking at an array [1,2], but I am not sure how to make it work.
I am using Crystal 10, and trying to persuade my manager that we need your book  but in the meantime, any advice gratefully received.
 
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 19 Dec 2007 at 5:23am
I think you may have oversimplified the description here, to the point that I, at least, am no longer sure exactly what you are doing.

Is the user entering two different date range parameters?  Or are you simply using the formula to describe which term a record with a given date falls in?  Based on the way you are describing things, I'm guessing the latter.

I think that you are overthinking the problem.  Try grouping the report on @halfterm, possibly within your existing groups.  You will be able to generate summaries for each term.  When you go one level up, you can aggregate the two terms together.

Now, if your problem is that you have multiple groups, and want to end up summarizing the data for each term in the report footer, in addition to having aggregates by other groups, that's a slightly different kettle of fish.  There are two basic solutions to this.  The first is to use a cross-tab, which will allow you to group your data in a variety of ways, and show summaries.  The second is to use formulas.  Suppose you want to show in the report footer the sum of DataX for term1, and the sum of DataX for term2.  Create two formulas, one that looks like:
    IF {MyDate} IN (date range for term1) THEN DataX ELSE 0
The other, naturally, would look like:
    IF {MyDate} IN (date range for term2) THEN DataX ELSE 0
You can then do summaries of these in your report footer, and it will give the breakdown by term.


Hopefully something in there answered your original question.  If not, reply back with some more details, and I'll give it another shot.

IP IP Logged
yggdrasil
Senior Member
Senior Member
Avatar

Joined: 19 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 150
Quote yggdrasil Replybullet Posted: 20 Dec 2007 at 6:28am
Details, well......
The heart of this report is a table with a long text field with all twice-daily attendance marks for every student at every school for every year (up to 5 at the moment).  The central processing extracts a portion on this string depending on the given start and end dates, counts up all the 30+ different types of marks for each student, and aggregates them per school and year. Then various types of marks are added together to give various types of attendance or non-attendance either for the student or the school as a whole.
This worked ok when a start date and end date were put in as perameters. But now the users want a report that will summarise the data over each half term, without any input from them.
Now the term dates I need are kept in a table which has a record for every school for every year for every term. And the half-term start and end dates are within that record. So to get the first half of a term, I need the period to run from 'term-start' and 'ht-start', to get the second half of the term, I need 'ht-end' and 'term-end'. And it has to run the central process for each of these six periods for each year, at the click of a button.
 
Did I mention they want a chart too?   
 
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 21 Dec 2007 at 5:12am
OK, this is definitely the time when you want to take a deep breath, and a step back from the problem.

So, each school may have different dates for their half-term beginning and ending.  And, all of these dates are in a table.  So far, so good.

The long text field somewhat terrifies me.  That is a minefield of potential problems.  If I were in this situation, I would try to find a way to parse that text field into a table structure, before I ever began trying to process against it.  But, I understand that such a solution is not always viable.

So, here's what you do.  You need to use a fairly complicated join here.  I know how to do it in SQL, so that's what I'll run with.  I know that it can be done in Crystal with linking.  But, I'm highly prejudiced towards SQL, so I'd have to research the linking solution.


SELECT OrigQuery.*, TermDates.*
FROM OrigQuery
JOIN TermDates
ON OrigQuery.School = TermDates.School
AND OrigQuery.AttendDate BETWEEN TermDates.ht-start AND TermDates.ht-end


In this statement, OrigQuery is the original query you are using, with the process to extract the data from the text field.  TermDates is the table that contains all the dates for the schools.  I'm presuming some about the record structure here.  Namely, that there is a record in that table for each half-term for each school, with beginning and ending dates.  I'm also making the assumption that you can tell it's the first half-term if "term-start" equals "ht-start."

Now that you have that query, you have the ability to note for each record which half-term it falls into.  From that point, it becomes a pretty straightforward exercise to group it by term, school, and/or student.


IP IP Logged
Post Reply Post New Topic
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



This page was generated in 0.032 seconds.