Crosstab Report

[Ligaya]

Hello,

I have just created my first crosstab report. In it I have different types of activities done by an employee as the row headings, the column heading are the months of the year. Per month, I get a sum of the particular activity done (e.g - for month of May, answering the mail = 15, doing the audit journal = 6. etc. In June, answering mail was done 10 times and audt journals, 10 times).

In my report, I would like the sum of all activities per month to go on the page footer. I am stumped on how to go about it.

Could someone please steer me in the right direction?

Many thanks,
Ligaya

 

[lark_tilly]

I don't think I got your table setup the same in this example but it
should be close enough. What you can do is add some vb code to the
report detail and page footer sections. The attached db has a sample
table and report. If you view the code in the report you'll see how it works.
It's fairly straight forward.
You declare a public variable for each month (i.e.maysum) and then you add the value of the month's (i.e. may) activities to it each time a detail record is printed. When the page footer prints the public variable (maysum) is assigned to the appropriate text box (txtmay). This is assuming you're only ging to have the one page. Otherwise you'd use the report footer, not the page footer.

Hope this helps.

 

[Ligaya]

Hello lark tilly,

Thank you for your help and the attached sample. I shall certainly try it out and apply to my own report.

Regards,
Ligaya

 

[Ligaya]

Hello lark tilly,

It's me again. I copied the code you had on the sample crosstab report and nothing happened. My report did not show the sum in the page footer. I cannot fathom where I went wrong. Any ideas?

Regards,
Ligaya

 

[lark_tilly]

Did you just copy the code or did you adjust it to fit your field names?
If you just copied it I'm sure it didn't work. Did you get any errors when
you previewed the report?

 

[Ligaya]

Crosstab report

I changed the fields to suit my name fields and no, there was no error message. When I ran the report, there was nothing in the page footer. I really don't know where I'd gone wrong.

 

[lark_tilly]

You added the fields to display the totals in the page footer, correct?
I'm sure you did, otherwise you should have gotten an error.
I'm a little stumped if you didn't get an error.
Let's try this. In the following section of code in your report, click the margin to the left of the first line-in the example below that would be the
txtmay=maysum line. A red dot should appear next to the line. Preview your
report. The code module should open and the line be highlighted in yellow. Hover your mouse pointer over the right variable(i.e. maysum).
It should show a value if that month had any qty in it. If it's zero then hit the F8 key which will move you to the next line. Check the variable there. Continue until you reach a variable that you know had a value. At any point you can click the dot to remove the breakpoint and hit F5 to finish running the code. One of two things should happen here. If the code page never comes up, then we have one problem. If it doesn't ever show a value, then we have another.

Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
txtmay = maysum
txtjune = junesum
End Sub

 

[Ligaya]

crosstab report

Hello lark Tilly,

No joy! I have no experience with VB; have followed your instructions diligently. The red dot did appear, but no line was highlighted in yellow. No value showed up showed up either for the months that I know for sure have numbers in them.

I am very appreciative of your help, but if I cannot get this going, I'll just give up and will definitely keep your suggestions and instructions filed for future use.

Many thanks for your help.

Regards,
Ligaya

 

[lark_tilly]

So when you ran the report it didn't bring up the code page at all?
Is your db anything you can post so I can look at it. I'm sure all we
have is some small item keeping this from working. If you do post it,
I'll need it in version 2000 or 97.

 

[Ligaya]

crosstab report

Helll lark Tilly,

Thank you for not giving up on me.
Unfortunately, the db is on the system at work and cannot be copied.
Again, thank you for taking time to help.

Regards,
Ligaya