Scope Problem

I'm having trouble with the following issue under RS for SQL 2005. I've spent about 6+ hours researching it trying to find a way to handle it correctly.

I have a report that pulls data from individual line items on contracts. I'm using a table group to group the records into monthly summaries. I'd like to divide the report.total quantity/number of months to get an average monthly sales quantity.

Here is a sample of the last few rows of report data. Under each monthly group is detail data (hidden). There may be one row or 20 in a given month.

... ... ...

+ June 2005

12

$4,300.00

+ July 2005

16

$4,675.00

+ August 2005

28

$10,375.00

Grand Total:

265

$98,212.50

However, I can only seem to get a count of the individual line items. I can't figure out how to get a count of monthly summaries which would logically be something like =countrows("Monthly Summary Group").

There are currently 140 detail items so the average is calculated as (265/140=1.9) when in fact, the average should be much higher. The number of months in the report is 28. The average value should be (265/28=9.5).

If I put this aggregate statement outside of the Montly Summary Group, I'm told that it's out of scope. If I put it within the group, I get a count of contracts within each month.

1. I'm not sure how to make the SQL query group on monthly values so I was doing it in a RS group. Below is a sample of the report detail data which shows that there are multiple detail lines in a given month. I'd like to summarize these for each month/year (Sept 2003, Oct 2003...)

OrderID Date Price Qty ExtPrice
PWSOR136 9/16/2003 5:00:00 PM 425 1 425
PWSOR137 9/16/2003 5:00:00 PM 425 1 425
PWSOR164 9/27/2003 9:00:00 AM 425 1 425
PWSOR179 10/15/2003 5:00:00 PM 425 1 425
PWSOR183 10/18/2003 5:00:00 PM 425 4 1700
PWSOR201 10/24/2003 5:00:00 PM 425 2 850
PWSOR201 10/24/2003 5:00:00 PM 425 1 425

2. I've tried adding a textbox to a blank cell in the group with a value of =1 and then trying to sum that value at the end of the report. I tried using the reportitems!textbox6.value to reference the textbox, but it shows as out of scope.

3. I've tried using custom code with a global variable as shown below:
public dim LineCount as integer = 0

function LineInc(n as integer) as integer
LineCount = LineCount+n
lineinc =linecount
end function

Within the group header, I use a textbox with the value =code.lineinc(1) and this returned an incrementing line count correctly. But when I try to use a textbox at the end of the report with a value of =code.LineCount, the value is 0. I'm assuming that this is part of the same scope problem.

Questions:
1. Is there a way to group records into 'month/year' groups based on a date field using SQL GROUP BY?

2. How can I structure a report so that I can use groups that aggregate in a way that hides the individual line items and gives me access to the Months summary.

3. Can anyone point me towards a good discussion of the programming model of reports in RS, how custom code behaves in RS or a discussion of how scope operates in RS.

Thanks,

Jason

=CountRows("Monthly Summary Group") will count the number of detail rows in the current Monthly Summary Group. That's not what you want.

What you're looking for is a count of the distinct number of months in your entire data set.
So you want something like this:
=CountDistinct(Fields!Month.Value+Fields!Year.Value*100, "DataSet1")

Regarding your additional questions:
1. Yes, you can use the DatePart function to extract the month and year (and group by them) in your SQL query
2. Yes, you can delete the detail rows from your table. However, if you don't want to let the user drill down to see the details, I'd recommend doing the grouping/aggregation by month in your query so that you're not sending a bunch of data into RS that you're just going to throw away.
3. The only thing I can point you at is Books Online/MSDN.

|||Chris,

Thanks much for the pointer to CountDistinct. I hadn't considered it before.

The actual implementation that I used is:

=Sum(Fields!QUANTITY.Value)/countdistinct(format(Fields!ENDDATE.Value,"MM yyyy") )

By using the format function to format the single date value (I didn't have seperate month and year fields) I got the result that I needed.

Also, thanks for the pointer to the DatePart function within the SQL statement. I'll look into that further later as I'm sure it's going to be usefull. I wanted to avoid grouping in the SQL statement if possible because that would have affected my drilldown features.

Later,

Jason