Calculate Total Column

I need to calculate the Average by Location and then sum the Average for a Total that is displayed on a report. Following is my stored procedure so far:

SELECT tblLocn.LocnCode, tblLocn.LocnDesc,
#work.StartDate, #work.ZValue,
J1.ZAvg ............. Average by locn -- want the sum of all locns here
FROM tblLocn
LEFT JOIN #work ON tblLocn.LocnCode = #work.LocnCode
LEFT JOIN (SELECT #work.LocnCode,
ZAvg = Avg(#work.ZValue)
FROM #work
GROUP BY #work.LocnCode) J1 ON tblLocn.LocnCode = J1.LocnCode

How would I change this procedure to display either a) total average on each row, or b) location average on 1 row only so when I Sum this field it is the correct value.
I am aware that I could create a workfile with the Total value and then Join to that. Is there another alternative? ThanksJ1.ZAvg is your average by location, so when you sum it on a report you will get the sum of all averages.

Bigger question: are you sure this the operation you want? Your statistics will be skewed if your location don't have equal number of records. Consider:
Sum(Avg(1,2,3,4,5), Avg(6,7,8)) = 10

But the same values distributed across different locations yields this:
Sum(Avg(1,2,3), Avg(4,5,6,7,8)) = 8

If you just want the average per location, this value is constant across all locations since it applies to the entire enterprise, and can be calculated separately:
Sum(#work.ZValue)/Count(Locations)

blindman|||I do want the average by location as I have it coded. The problem with just summing the J1.ZAvg field is that the SELECT statement is not grouped by location but rather by DATE. This means that the J1.ZAvg field appears on each row for the location. If I sum that up, I would get an exploded value. Make sense?|||If I change the J1.ZAvg to Sum(J1.ZAvg), that would give me the total I want, except that it seems I cannot use the Sum unless I use a GROUP BY clause. In Access I used the DSUM aggregate function which is not available for Access Project. I may be missing something as I'm fairly new to SQL Server.|||So calculate the value once, store it in a variable, and include it in your output select statement:

set @.SumLocationAvgs = sum((select avg(zwork) from #Workd group by location))

Note: the above is just pseudo-code. You'll have to write for your specification.

blindman|||Makes total sense! Thanks a bunch.|||Oops .. I get the following message:

ERROR 130: Cannot perform an aggregate function on an expression containing an aggregate or a subquery

Set @.SumLocationAvgs = Sum((SELECT Avg(#work.ZValue) FROM #work GROUP BY LocnCode))|||Try this instead:

Set @.SumLocationAvgs =
(select Sum(SubQuery.LocnCodeAvg)
from (SELECT Avg(#work.ZValue) LocnCodeAvg FROM #work GROUP BY LocnCode) SubQuery)|||Exactly what I needed. Thank you.

Cached data being displayed

If I run a report, then change some data in the database. Then, navigate
away from my sql report, go back back in and re-run the report, it doesn't
show the data that I just changed in the database. I have to actually click
the 'refresh' button to get the report actually "re-run". It appears to
display cached data.
How can I tell the report to not do this?I had the same issue and found the solution was to add another rs command to
the report url...
i.e. rs:ClearSession=true
e.g.
http://server/reportserver?/YourReport&rs:Command=Render&rs:ClearSession=true
"Brian Patrick" wrote:
> If I run a report, then change some data in the database. Then, navigate
> away from my sql report, go back back in and re-run the report, it doesn't
> show the data that I just changed in the database. I have to actually click
> the 'refresh' button to get the report actually "re-run". It appears to
> display cached data.
> How can I tell the report to not do this?
>
>|||I too had the same issue. I did tried the same solution as you have given here.
I found that even a true or false has the same effect ie.. clearing the
cached data for the both the values. Only we need to specify any one of them.
And in case we don't specify rs:ClearSession='' cached data is not cleared.
Did u experiance that?
Suresh
"NH" wrote:
> I had the same issue and found the solution was to add another rs command to
> the report url...
> i.e. rs:ClearSession=true
> e.g.
> http://server/reportserver?/YourReport&rs:Command=Render&rs:ClearSession=true
> "Brian Patrick" wrote:
> > If I run a report, then change some data in the database. Then, navigate
> > away from my sql report, go back back in and re-run the report, it doesn't
> > show the data that I just changed in the database. I have to actually click
> > the 'refresh' button to get the report actually "re-run". It appears to
> > display cached data.
> >
> > How can I tell the report to not do this?
> >
> >
> >|||Well I have not tested this. It sounds like odd behaviour.
"Suresh" wrote:
> I too had the same issue. I did tried the same solution as you have given here.
> I found that even a true or false has the same effect ie.. clearing the
> cached data for the both the values. Only we need to specify any one of them.
> And in case we don't specify rs:ClearSession='' cached data is not cleared.
> Did u experiance that?
> Suresh
>
> "NH" wrote:
> > I had the same issue and found the solution was to add another rs command to
> > the report url...
> >
> > i.e. rs:ClearSession=true
> >
> > e.g.
> > http://server/reportserver?/YourReport&rs:Command=Render&rs:ClearSession=true
> >
> > "Brian Patrick" wrote:
> >
> > > If I run a report, then change some data in the database. Then, navigate
> > > away from my sql report, go back back in and re-run the report, it doesn't
> > > show the data that I just changed in the database. I have to actually click
> > > the 'refresh' button to get the report actually "re-run". It appears to
> > > display cached data.
> > >
> > > How can I tell the report to not do this?
> > >
> > >
> > >|||We had the same issue. Impmented rs:ClearSession=true and that worked ome
time ago. Now it no longer worls on a RS sp 1 Windows Server 2003 with a
APS.NET app accessing reports with url. This is causing major issues with
client and the on-line business application not reporting correctly as data
enry occur. Surley Microsoft has some solution?
"NH" wrote:
> Well I have not tested this. It sounds like odd behaviour.
> "Suresh" wrote:
> > I too had the same issue. I did tried the same solution as you have given here.
> > I found that even a true or false has the same effect ie.. clearing the
> > cached data for the both the values. Only we need to specify any one of them.
> >
> > And in case we don't specify rs:ClearSession='' cached data is not cleared.
> > Did u experiance that?
> >
> > Suresh
> >
> >
> > "NH" wrote:
> >
> > > I had the same issue and found the solution was to add another rs command to
> > > the report url...
> > >
> > > i.e. rs:ClearSession=true
> > >
> > > e.g.
> > > http://server/reportserver?/YourReport&rs:Command=Render&rs:ClearSession=true
> > >
> > > "Brian Patrick" wrote:
> > >
> > > > If I run a report, then change some data in the database. Then, navigate
> > > > away from my sql report, go back back in and re-run the report, it doesn't
> > > > show the data that I just changed in the database. I have to actually click
> > > > the 'refresh' button to get the report actually "re-run". It appears to
> > > > display cached data.
> > > >
> > > > How can I tell the report to not do this?
> > > >
> > > >
> > > >