Forumulas in Report

Hi Everyone,

I have a report with 6 sub reports and am needing to do a forumula with two numbers from sub report 1 and sub report 2.

The problem is I can't figure out how to do a calculation with one figure in sub report 1 and the other in sub report 2 and show the result in sub report 3.

Does anyone know how I could do this?

Thanks very much :cool:

Hi
I don't have a quick answer but I may be able to steer you in the right direction.
Here's some code I have used to manipulate sub reports when using a Crystal control.

VB Code:

CrystalReport1.SubreportToChange = strSubReport
    CrystalReport1.DataFiles(0) = wholeDBNAME
    CrystalReport1.SubreportToChange = ""

The first line sets the sub report you want to manipulate. You can then treat the subreport as if it were the main report. For example, you may create a variable in a subreport1 and get that value back into VB. Do the same for subreport 2, do you calculations and set values in subreport 3.

This is a lot short of what you need but this is probably the way to do what you want.

FW

Thanks for the response mate but I'm still really stuck.

Anyone else out there who can assist?

Hi James,
You need to use shared variables.
Whether you have them or not probably depends upon your version of Crystal. I have v8.5.
Look for "Variable scopes" in the Crystal Reports Online Help.

Bonker :cool:

Hi Bonker,

Thanks for the response but I'm actually trying to do this in Microsoft Access 2002 and not Crystal Reports

Any ideas mate?

I MIGHT be able to help, but I need a little more information. First of all, let's make sure I understand your problem. When you say, "sub report" what are you referring to? Are you talking about subtotals, or what?

I've attached two screen prints in two posts below.

I'm trying to do "123" from sub report 2 divided by 135 (sub report 1) * 100 to get a percentage to stick into the YTD Achieved textbox sitting in sub report 2.

Does that give you enough information to be of assistance?

image 1

image 2

I am still not sure I understand your problem, but I will just keep asking questions until I do.

At first thought, it would seem like you could write the results of the computations for "123" and 135 into variables that you could refer to for your YTD Achieved text box. Where are the "123" and 135 calculated?

Thanks :)

They are calculated in a query that is then brought into two seperate reports that sit on the main report.

The problem I have is that I don't know how to include the figure (135) from sub report 1 to have it included in a calculation for sub report 2.

I may be stumped, due just as much to my relative rustiness with Access as to my lack of understanding of your problem. It still seems to me that in order to have the "123" and 135 show up on the report, you have to refer to them, by name, on the report someplace. It would therefore seem like you could simply refer to that name in your subsequent report, given that they are all active at the same time.

Alternatively, if I were doing this in data reporter for VB6, admittedly much more limited than the report writer for Access, I would have to create a grouped recordset where all the calculations took place, and simply refer to the appropriate fields, no matter which of the linked datasets they came from.

I'm terribly sorry I can't help more! :(

I have quite a bit of experience in Access and Access reports, but I'm not quite sure about all the details involved.

If I'm right, you have 2 queries that feed two different reports that are both subreports on the same main report.

What you must keep in mind about Access and subreports is that there is no way for the subreports to communicate even if they are on the same report. All Access reports are are visual representations of collected data, they dont have all the functionality of, say, an Access Form - only meer translation functionos (math, formatting, etc.)

In order to communicate data from one report to another you need to build the data before the report. Whether you can do this with a simple query or maybe you need to use a module to compute some numbers and populate a table that just stores data to run this report (not a recommended solution, but works in a pinch)

I could be off the mark with my assumptions on your problem, but hope it helps at any rate.

Thanks putts - yes you're correct with what you said re my question.

Doing a module sounds as though it's my only option but I wouldn't have a clue how to do that :confused: :(