Grouping using Unbound Fields in Report

[fredka]

I have some code that I have used for a long time now for a report. The report totals opportunities by a category field and then uses hardcoded "months"that go accross the top of the report. The results look like this:

Category January February March etc,etc
Lost 1 10 3
Declined 4 7 10
Won 2 3 4

CloseRatio 33.0 10.7 6.1
(this is in footer)

The report contains unbound fields and is populated when the report opens. The code in the format event of the detail is below. I got this code from this site a few years back and it has worked great.

The problem is that now I need to add two more groupings on the report.... I have a "Region" field and a "MarketSegment" field. I am having a problem understanding how to get the unbound boxes to populate with the correct Region and MarketSegment. The report is mixing up the groupings. I have been playing around with this all day and no matter what I have tried, I get incorrect data - for example, I am getting

region MarketSegment Category January
dental jumbo lost 10
dental jumbo declined 7

close ratio 2.8

dental jumbo won
dental mid lost
dental mid declined

I think the problem is that there are 5 categories and not each region/marketsegment has data in all of the categories.

I hope that is enough information to at least get a feeling of what I am doing - I understand that I may need to post more, just let me know what else I would need to provide - thanks!!!!

Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)
Dim intx As Integer

' Verify that not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, place values from recordset into text boxes
' in detail section.

If Me.FormatCount = 1 Then

For intx = 1 To intcolumncount
' Convert Null values to 0.
Me("Col" + Format(intx)) = xtabCnulls(rstReport(intx - 1))
' MsgBox Me("Col" + Format(intx))
Next intx

' Hide unused text boxes in detail section.
For intx = intcolumncount + 2 To conTotalColumns
Me("Col" + Format(intx)).Visible = False
Next intx

' Move to next record in recordset.
rstReport.MoveNext
End If
End If

 

[lameid]

Why not use a crosstab query for your recordsource and use a report without special code?

Then it will be easy to add grouping. I would recommend grouping by the year when ever doing a crosstab by months.

In your query, just be sure to specify the list of the 12 months as column heading for the row heading field.

I'm not sure what CloseRatio is to help with the math.

 

[fredka]

Thanks lameid - After working on this for a while, I started to think the same thing ... this format was originally uses because the report would not always be run for 12 months.... the unbound route hid the non-used columns. However, for my purposes on this report, I don't need to use that functionality.

Thanks for the input!!!