How to access group record values 1

[JCruz063]

Hi All,
I have a Crystal Report that has a group based on a formula. The formula in charge of creating the group looks something like this:

if (
   ({myTable.x} = 10 and {myTable.y} = 20)
   or
   ({myTable.x} = 30 and {myTable.y} = 40)
   ) then [COLOR=red][b]1[/b][/color]
else if(
   ({myTable.x} = 50 and {myTable.y} = 60)
   or
   ({myTable.x} = 70 and {myTable.y} = 80)
   ) then [COLOR=red][b]2[/b][/color]
else if(
   ({myTable.x} = 90 and {myTable.y} = 100)
   ) then [COLOR=red][b]3[/b][/color]

As you can see in the code, I have a database table with two fields: x and y. What this code does is it returns a unique value, 1, 2, or 3, for diferent combinations of the x and y fields of myTable. You could see in the code that when x is 10 and y is 20 or x is 30 and y is 40, the formula returns the number 1. Also, when x is 50 and y is 60, or when x is 70 and y is 80, the formula returns the number 2. Finally, when x is 90 and y is 100, the code returns the number 3.

Like I said before, in my report I have a Crystal Reports Group based on the above formula and the Crystal Reports engine automatically organizes the records in three groups: those for which the formula returns the number 1, those for which the formula returns the number 2, and those for which the formula returns the number 3.

Ok, all good so far. Now, when the report above prints, it will print in three pages. The first page will contain, of course, the records for which the formula returns 1, the second page will contain the records for which the formula returns 2, and the third page will have the records for which the formula returns 3.

Ok, still good. Now, I need to print a header for the each page. The header should say something like this:
X = 10, Y = 20 / X = 30, Y = 40
That would be the header for page 1. The header of page 2 would say:
X = 50, Y = 60 / X = 70, Y = 80
And so on... This way, users who view my report will know what each page means, duh. Well, I have a formula that does this: This formula is in is placed in the group header and it looks like this:

numberVar myX = myTable.x
numberVar myY = myTable.y
stringVar outputValue
if (
   myX = 10 and myY = 20 
   )then [b]outputValue = "X = 50, Y = 60"[/b]
[COLOR=green]   // I don't know what do do to 
   // add the other part of the header[/color]

And here's where I have the problem. As you can see in the formula above, myX and myY are variables declared in the formula itself. They receive the values of myTable.x and myTable.y respectively. The variable outputValue is of type string and it should receive the value of the header that I want to print. The problem is that the formula can only access one value of x and y at a time so it prints only the first part of the header. For example, for page 1, the header should be
"X = 10, Y = 20 / X = 30, Y = 40"
but the formula only prints "X = 10, Y = 20". Of course, we know that for each group x and y have multiple values.

So, my question is: How can I read all the values of x and y in this formula so that the correct value of the header is printed?

Thanks

JC




We don't see things as they are; we see them as we are. - Anais Nin

 

[synapsevampire]

Since this is page based, why bother with the values, use a conditional on the pagenumber function, as in:

if pagenumber = 1 then
"blah"
else if pagenumber = 2 then
"blah blah"
else if pagenumber = 3 then
"blah blah blah"
else
"..."

-k

 

[JCruz063]

Thanks -k,
Your suggestion is good but it would work only for the report I described in my previous thread. However, I have another report in which the bulk of the data is based on a formula with multiple values like the one I described before. In fact, in this new report, each header of my previous report becomes a row, like this:

[b]Items:                              Qty:[/b]
X = 10, Y = 20 / X = 30, Y = 40     320
X = 50, Y = 60 / X = 70, Y = 80     528
X = 90, Y = 100                     156

Do you know of any other way to do what I need?

Thanks

JC

We don't see things as they are; we see them as we are. - Anais Nin

 

[lbass]

Create a second formula:

if (
({myTable.x} = 10 and {myTable.y} = 20)
or
({myTable.x} = 30 and {myTable.y} = 40)
) then
"X = 10, Y = 20 / X = 30, Y = 40"
else if(
({myTable.x} = 50 and {myTable.y} = 60)
or
({myTable.x} = 70 and {myTable.y} = 80)
) then
"X = 50, Y = 60 / X = 70, Y = 80"
else if(
({myTable.x} = 90 and {myTable.y} = 100)
) then
"X = 90, Y = 100"

You can use this in the group header or in the detail section.

-LB

 

[JCruz063]

Thanks -LB, that's actually what I had in mind.

JC

We don't see things as they are; we see them as we are. - Anais Nin

 

[JCruz063]

-LB,
bad news - it doesn't work. I will try to explain why to the best of my abilities so that you understand what's going on. Here it is:

1 - As you may have assummed, my database table does not contain fields named x and y. Instead, the fields are StartTime and EndTime, and they are used to store the times of the day of certain items in my application.

2 - The data type of StartTime and EndTime in the database is not Date, or DataTime, or the like; their data type is Integer. Thus, 5:00 AM is stored as 500, 5:30 AM is stored as 530, 1:00 PM is stored as 1300, 5:30 PM is stored as 1730, and so on. Of course, before displaying it to the user the database value must undergo some trasformation so that, in the report, users see "2:00 PM - 5:30 PM" and not "1400 - 1730".

3 - Users of my internet application have the ability to print reports based on the start time and end time of certain items in the application. They are also able combine different time periods so that more than one time range appear together in one single page of the report. For example, if the time ranges "9 - 11 AM" and "12 - 5 PM" are available for printing, users may combine them and print them on together. Thus the header of the page where these time periods would print would be "9:00 - 11:00 AM/12:00 - 5:00 PM".

4 - At design time, I don't know the different time periods that will be avaiable because they vary depending on the user. For a given user, there may be 15 time periods avaiable and he may choose to combine them in any way he likes, in groups of 2, 3 and even 4. Thus, at design time, I don't know the different combinations either. I need to create a formula at run time based on the way the user selected to print the data in his time periods. That said, you could take a look at my first thread in this post, which shows that I create a formula that returns a number for each time period combination. Then, I create a group based on that formula and the Crystal Report engine groups together the records for which the formula returns the same number.

The Problem

The problem I have is that I can't display the time periods correctly for each group. For example, if the user has combined the time periods "9:00 - 11:00 AM" and "12:00 - 5:00 PM", the header of the page should be "9:00 - 11:00 AM/12:00 - 5:00 PM". In the database, these time periods are stored as "900 - 1100" and "1200 - 1700" and so the formula must convert to their appropriate user-friendly time values. But in the formula, I can only access one time period at a time and thus I can't display both from the formula. In other words, the formula should say something like this:

stringVar formula

[i]if the first time in group is 900-1100
   formula = "9:00 - 11:00 AM"

[loop while there are time periods in group]
if the the current time period is 1200-1730
   formula = formula & "/12:00 - 5:30 PM"
else if the current time period is [bla]
   formula = formula & "/blah"
else ...[/i]
[end loop]

The above algorithm is not possible because you can only access one record at a time. Before I knew any better, I tried to implement it, but the header would only show the first part of the time each time period group.

Ok, so now you (hopefully) know what I need to do. Can you please tell me how to do it?

Thanks in advance!

JC

We don't see things as they are; we see them as we are. - Anais Nin