How to evaluate sum on change of group?

I have a data set that is grouped based on 2 fields, but the value of the set
that I want to add by Group 1 is the same data that repeats for the first
group.
Example:
Value Grp1 Grp2
================= 100 1 1
100 1 2
100 1 3
200 2 1
200 2 2
200 2 3
I want to get a total of Value, but only evaluate the total when Grp1
changes. Currently, when I use the Sum function, it adds all values, giving
me a total of 900. I need a way to take the value of '100' when Grp1 = 100,
and add it to the value of '200' when Grp1 = 2 to give me a total of 300.
In Crystal Reports, there was a method to evaluate a sum only on the change
of a particular group. Is there some similar method in Reporting Services to
achieve this?It is available in SSRS as well but you need to try it and see how far you
can use this solutions. it goes like this.
= RunningValue(Fields!field1.Value, Sum, <groupname>) so it evaluates to tat
particular group or the scope.
Amarnath
"Ben Shaffer" wrote:
> I have a data set that is grouped based on 2 fields, but the value of the set
> that I want to add by Group 1 is the same data that repeats for the first
> group.
> Example:
> Value Grp1 Grp2
> =================> 100 1 1
> 100 1 2
> 100 1 3
> 200 2 1
> 200 2 2
> 200 2 3
>
> I want to get a total of Value, but only evaluate the total when Grp1
> changes. Currently, when I use the Sum function, it adds all values, giving
> me a total of 900. I need a way to take the value of '100' when Grp1 = 100,
> and add it to the value of '200' when Grp1 = 2 to give me a total of 300.
> In Crystal Reports, there was a method to evaluate a sum only on the change
> of a particular group. Is there some similar method in Reporting Services to
> achieve this?
>|||Ben,
You could also use grouping on the report. You could put a group sum in
the group header or footer, and then a grand total or a count of the
groups in the table footer.
When you use a normal sum function in a group, it sums only the group.
-Josh
Ben Shaffer wrote:
> I have a data set that is grouped based on 2 fields, but the value of the set
> that I want to add by Group 1 is the same data that repeats for the first
> group.
> Example:
> Value Grp1 Grp2
> =================> 100 1 1
> 100 1 2
> 100 1 3
> 200 2 1
> 200 2 2
> 200 2 3
>
> I want to get a total of Value, but only evaluate the total when Grp1
> changes. Currently, when I use the Sum function, it adds all values, giving
> me a total of 900. I need a way to take the value of '100' when Grp1 = 100,
> and add it to the value of '200' when Grp1 = 2 to give me a total of 300.
> In Crystal Reports, there was a method to evaluate a sum only on the change
> of a particular group. Is there some similar method in Reporting Services to
> achieve this?|||Thanks for the reply. I've tried using the scope parameter of the aggregate
function, but get report compilation errors when I do so. What you've
suggested with the "RunningValue" function is essentially the same as using
the Sum function.
I oversimplified my data set to really show my problem. Here's a slightly
different version:
field1 Grp1 Grp2
=================100 1 1
100 1 1
100 1 1
100 1 2
100 1 3
On the footer for group 2, I can just show the most recent value of field1.
i.e. when grp2 changes, I just display =Fields!field1.Value instead of a sum
to get the value that I need to display.
However, when I try to create a Sum of the 1st field on the footer for group
1, I get the total of all values of field1.
i.e. =Sum(Fields!field1.Value) on the footer for group 1 yields a value of
500. What I want to get is for the sum to evaluate only when group 2
changes, to yield a value of 300.
I have tried using the scope parameter to the sum function to do this.
i.e. =Sum(Fields!field1.Value, "Grp2")
When I try this, I get a report compile error telling me that:
"The value expression for textbox 'x' has a scope parameter that is not
valid for an aggregate function. The scope parameter must be set to a string
constant that is equal to either the name of a containing group, the name of
a containing data region, or the name of a data set."
What I understand of the error message is that I can't set the scope of the
sum function to be based on a group that group 1 contains. That it has to be
set to a group that contains group 1 instead.
Am I using scope incorrectly? If this is the way that scope functions, then
the "scope" of the aggregate function must control when the running total
resets to zero, rather than controlling when the running total gets evaluated
(which is the functionality that I *need*).
It doesn't make sense that I wouldn't have this capability with MS Reporting
Services, as lesser reporting tools (such as Crystal and R&R) all provided
this kind of functionality with running totals in reports.
Further suggestions would be hugely appreciated.
"Amarnath" wrote:
> It is available in SSRS as well but you need to try it and see how far you
> can use this solutions. it goes like this.
> = RunningValue(Fields!field1.Value, Sum, <groupname>) so it evaluates to tat
> particular group or the scope.
> Amarnath
> "Ben Shaffer" wrote:
> > I have a data set that is grouped based on 2 fields, but the value of the set
> > that I want to add by Group 1 is the same data that repeats for the first
> > group.
> > Example:
> > Value Grp1 Grp2
> > =================> > 100 1 1
> > 100 1 2
> > 100 1 3
> > 200 2 1
> > 200 2 2
> > 200 2 3
> >
> >
> > I want to get a total of Value, but only evaluate the total when Grp1
> > changes. Currently, when I use the Sum function, it adds all values, giving
> > me a total of 900. I need a way to take the value of '100' when Grp1 = 100,
> > and add it to the value of '200' when Grp1 = 2 to give me a total of 300.
> >
> > In Crystal Reports, there was a method to evaluate a sum only on the change
> > of a particular group. Is there some similar method in Reporting Services to
> > achieve this?
> >|||Thanks for your reply, but I've already done this. I've better explained my
situation in a reply to Amarnath above. Further help in regard to that post
would be greatly appreciated. Thanks :)
"Josh" wrote:
> Ben,
> You could also use grouping on the report. You could put a group sum in
> the group header or footer, and then a grand total or a count of the
> groups in the table footer.
> When you use a normal sum function in a group, it sums only the group.
> -Josh|||This might be a dumb question, but I have to ask...
You said:
However, when I try to create a Sum of the 1st field on the footer for
group
1, I get the total of all values of field1.
i.e. =Sum(Fields!field1.Value) on the footer for group 1 yields a value
of
500. What I want to get is for the sum to evaluate only when group 2
changes, to yield a value of 300.
You are trying to sum Group 2 to get a sum of 300, but you are summing
in the footer of group 1 and getting 500. Can't you just sum in the
group 2 footer? That would give you 300, and you would only get the sum
(group footer) every time group 2 changes...
-Josh
Ben Shaffer wrote:
> Thanks for the reply. I've tried using the scope parameter of the aggregate
> function, but get report compilation errors when I do so. What you've
> suggested with the "RunningValue" function is essentially the same as using
> the Sum function.
> I oversimplified my data set to really show my problem. Here's a slightly
> different version:
> field1 Grp1 Grp2
> =================> 100 1 1
> 100 1 1
> 100 1 1
> 100 1 2
> 100 1 3
> On the footer for group 2, I can just show the most recent value of field1.
> i.e. when grp2 changes, I just display =Fields!field1.Value instead of a sum
> to get the value that I need to display.
> However, when I try to create a Sum of the 1st field on the footer for group
> 1, I get the total of all values of field1.
> i.e. =Sum(Fields!field1.Value) on the footer for group 1 yields a value of
> 500. What I want to get is for the sum to evaluate only when group 2
> changes, to yield a value of 300.
> I have tried using the scope parameter to the sum function to do this.
> i.e. =Sum(Fields!field1.Value, "Grp2")
> When I try this, I get a report compile error telling me that:
> "The value expression for textbox 'x' has a scope parameter that is not
> valid for an aggregate function. The scope parameter must be set to a string
> constant that is equal to either the name of a containing group, the name of
> a containing data region, or the name of a data set."
> What I understand of the error message is that I can't set the scope of the
> sum function to be based on a group that group 1 contains. That it has to be
> set to a group that contains group 1 instead.
> Am I using scope incorrectly? If this is the way that scope functions, then
> the "scope" of the aggregate function must control when the running total
> resets to zero, rather than controlling when the running total gets evaluated
> (which is the functionality that I *need*).
> It doesn't make sense that I wouldn't have this capability with MS Reporting
> Services, as lesser reporting tools (such as Crystal and R&R) all provided
> this kind of functionality with running totals in reports.
> Further suggestions would be hugely appreciated.
> "Amarnath" wrote:
> > It is available in SSRS as well but you need to try it and see how far you
> > can use this solutions. it goes like this.
> >
> > = RunningValue(Fields!field1.Value, Sum, <groupname>) so it evaluates to tat
> > particular group or the scope.
> >
> > Amarnath
> >
> > "Ben Shaffer" wrote:
> >
> > > I have a data set that is grouped based on 2 fields, but the value of the set
> > > that I want to add by Group 1 is the same data that repeats for the first
> > > group.
> > > Example:
> > > Value Grp1 Grp2
> > > =================> > > 100 1 1
> > > 100 1 2
> > > 100 1 3
> > > 200 2 1
> > > 200 2 2
> > > 200 2 3
> > >
> > >
> > > I want to get a total of Value, but only evaluate the total when Grp1
> > > changes. Currently, when I use the Sum function, it adds all values, giving
> > > me a total of 900. I need a way to take the value of '100' when Grp1 = 100,
> > > and add it to the value of '200' when Grp1 = 2 to give me a total of 300.
> > >
> > > In Crystal Reports, there was a method to evaluate a sum only on the change
> > > of a particular group. Is there some similar method in Reporting Services to
> > > achieve this?
> > >

How to encorporate IF

How can I put an If statement saying If # RECORDS returned from the query below
IF # records returned is > 1 then
SUM(rmstranamt) AS rmstranamt10
ELSE
rmstranamt AS rmstranamt10

here's my statement

-

SELECT RMSFILENUM,
rmstranamt AS rmstranamt10 <--If statement goes here base on if the amount of records found in select is >1 or not
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM, rmstranamt
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

I want to return sum(rmstranamt) AS rmstranamt10 in cases where the same query produces > 1 records
In other words if this:
SELECT RMSFILENUM,
rmstranamt AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM, rmstranamt
produces > 1 records, then I don't want to return rmstranamt AS rmstranamt10, I want to return sum(rmstranamt) AS rmstranamt10

Wouldn't

SUM(rmstranamt)
be the same as
rmstranamt

in the case of one record? Think you should just include the sum and not worry about it.

- Brad
|||

you would think....I've spent 3 days on this...check it out more here:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21692997.html

|||I see your issue now, but the problem isn't the SUM function rather the GROUP BY clause.

Just remmove the rmstranamt from your GROUP BY to achieve the desired results, otherwise each row returned represents a SUM applied to a GROUP that has one row.

- Brad|||Brad, you mean remove rmstranamt from my last Group By right? If I do that then It errors out saying that rmstranamt needs to be in it...so what do I do?|||Brad, ok yes, that works but to a certain point. My main problem is when I plug in an account that I know has more than 1 results from my first INNER JOIN query, it throws off my end aggregation and I end up with 2 account rows rather than one for that account|||SELECT RMSFILENUM, sum(rmstranamt) rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM|||

Hi,

SELECT RMSFILENUM
into #tmp

FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM having count(rmstranamt) <= 1

SELECT RMSFILENUM,
sum(rmstranamt )AS rmstranamt10

FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM having count(rmstranamt) > 1

union

SELECT r.RMSFILENUM,
rmstranamt AS rmstranamt10

FROM RFINANL r inner join #tmp t on r.RMSFILENUM = t.RMSFILENUM
GROUP BY r.RMSFILENUM, r.rmstranamt

Regards

How to encorporate IF

How can I put an If statement saying If # RECORDS returned from the query below
IF # records returned is > 1 then
SUM(rmstranamt) AS rmstranamt10
ELSE
rmstranamt AS rmstranamt10

here's my statement

-

SELECT RMSFILENUM,
rmstranamt AS rmstranamt10 <--If statement goes here base on if the amount of records found in select is >1 or not
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM, rmstranamt
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

I want to return sum(rmstranamt) AS rmstranamt10 in cases where the same query produces > 1 records
In other words if this:
SELECT RMSFILENUM,
rmstranamt AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM, rmstranamt
produces > 1 records, then I don't want to return rmstranamt AS rmstranamt10, I want to return sum(rmstranamt) AS rmstranamt10

Wouldn't

SUM(rmstranamt)
be the same as
rmstranamt

in the case of one record? Think you should just include the sum and not worry about it.

- Brad
|||

you would think....I've spent 3 days on this...check it out more here:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21692997.html

|||I see your issue now, but the problem isn't the SUM function rather the GROUP BY clause.

Just remmove the rmstranamt from your GROUP BY to achieve the desired results, otherwise each row returned represents a SUM applied to a GROUP that has one row.

- Brad|||Brad, you mean remove rmstranamt from my last Group By right? If I do that then It errors out saying that rmstranamt needs to be in it...so what do I do?|||Brad, ok yes, that works but to a certain point. My main problem is when I plug in an account that I know has more than 1 results from my first INNER JOIN query, it throws off my end aggregation and I end up with 2 account rows rather than one for that account|||SELECT RMSFILENUM, sum(rmstranamt) rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM|||

Hi,

SELECT RMSFILENUM
into #tmp

FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM having count(rmstranamt) <= 1

SELECT RMSFILENUM,
sum(rmstranamt )AS rmstranamt10

FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM having count(rmstranamt) > 1

union

SELECT r.RMSFILENUM,
rmstranamt AS rmstranamt10

FROM RFINANL r inner join #tmp t on r.RMSFILENUM = t.RMSFILENUM
GROUP BY r.RMSFILENUM, r.rmstranamt

Regards

How to dynamically choose whether to SUM() column or GROU BY this column?

Hi there
I'm developping an application which would deal with costs in
organization which structure is split hierarchically to four division
levels.
eg. one can have atomic unit by setting values:
division1=A3
division2=111
division3=245
division4=1234
despite their hierarchical relation, my client wants to have reports on
all of those division combinations. so the perfect solution would be
the ultraGeneric(div1,div2,div3,div4) procedure which could do:
when called with all 4 parameters
select div1,div2,div3,div4,SUM(cost)
where div1=@.div1 and div2=@.div2 and div3=@.div3 and div4=@.div4
group by div1,div2,div3,div4
while when called with only div1 set (rest would be NULL):
select div1,'all','all,'all',SUM(cost)
where div1=@.div1
group by div1
I know how I can deal with WHERE part:
WHERE (@.DIV1 IS NULL OR @.DIV1= '' OR DIV1=@.DIV1)
but I have no idea what to do with SUM / GROUP BY part.
I can use CASE in select to choose whether to put column name or some
fake string if the column parameter is null.
but what about GROUP BY? does it behave like ORDER BY, in which I'd
have to use CASE with all combinations of parameter states?
thanks a lot
HPsorry, the post title doesn't make sense. to restate the question:
How to choose whether or not GROUP by a given column depending on
whether its value in parameter is null or not?|||You should simply GROUP BY all columns, and if there is a single value
in one of those columns (due to it being used in a condition), so be
it.
You can also use dynamic SQL. Read the following article by Erland
Sommarskog, for more approaches to the dynamic search conditions
problem:
http://www.sommarskog.se/dyn-search.html
Razvan|||Razvan Socol wrote:
> You should simply GROUP BY all columns, and if there is a single value
> in one of those columns (due to it being used in a condition), so be
> it.
hey, you're right! so case in Select will do - if I don't want to group
by a column a can just put static text instead of it.
> You can also use dynamic SQL. Read the following article by Erland
> Sommarskog, for more approaches to the dynamic search conditions
> problem:
> http://www.sommarskog.se/dyn-search.html
thanks, he saved my life once (great article about passing arrays to
procs). but thanks to your observation I won't have to use EXEC().
thanks
HP|||> > You should simply GROUP BY all columns, and if there is a single value
> > in one of those columns (due to it being used in a condition), so be
> > it.
> hey, you're right! so case in Select will do - if I don't want to group
> by a column a can just put static text instead of it.
I guess it won't do because even if I put static text into div1 column
in a select, 'group by div1' will group by original values in a
database div1 column anyway.
greetings
HP|||I read your original message again and I think I now understand what
you mean. However, the queries that you wrote there will never return
more than one row. Is this really your requirement ?
For another approach, take a look at the WITH CUBE option (see
"Sumarizing Data" in Books Online). For example:
SELECT div1,div2,div3,div4,SUM(cost)
FROM YourTable
GROUP BY div1,div2,div3,div4
WITH CUBE
I think it may return all the data you need with a single query.
Razvan

How to dynamically choose whether to SUM() column or GROU BY this column?

Hi there
I'm developping an application which would deal with costs in
organization which structure is split hierarchically to four division
levels.
eg. one can have atomic unit by setting values:
division1=A3
division2=111
division3=245
division4=1234
despite their hierarchical relation, my client wants to have reports on
all of those division combinations. so the perfect solution would be
the ultraGeneric(div1,div2,div3,div4) procedure which could do:
when called with all 4 parameters
select div1,div2,div3,div4,SUM(cost)
where div1=@.div1 and div2=@.div2 and div3=@.div3 and div4=@.div4
group by div1,div2,div3,div4
while when called with only div1 set (rest would be NULL):
select div1,'all','all,'all',SUM(cost)
where div1=@.div1
group by div1
I know how I can deal with WHERE part:
WHERE (@.DIV1 IS NULL OR @.DIV1= '' OR DIV1=@.DIV1)
but I have no idea what to do with SUM / GROUP BY part.
I can use CASE in select to choose whether to put column name or some
fake string if the column parameter is null.
but what about GROUP BY? does it behave like ORDER BY, in which I'd
have to use CASE with all combinations of parameter states?
thanks a lot
HP
sorry, the post title doesn't make sense. to restate the question:
How to choose whether or not GROUP by a given column depending on
whether its value in parameter is null or not?
|||You should simply GROUP BY all columns, and if there is a single value
in one of those columns (due to it being used in a condition), so be
it.
You can also use dynamic SQL. Read the following article by Erland
Sommarskog, for more approaches to the dynamic search conditions
problem:
http://www.sommarskog.se/dyn-search.html
Razvan
|||Razvan Socol wrote:
> You should simply GROUP BY all columns, and if there is a single value
> in one of those columns (due to it being used in a condition), so be
> it.
hey, you're right! so case in Select will do - if I don't want to group
by a column a can just put static text instead of it.

> You can also use dynamic SQL. Read the following article by Erland
> Sommarskog, for more approaches to the dynamic search conditions
> problem:
> http://www.sommarskog.se/dyn-search.html
thanks, he saved my life once (great article about passing arrays to
procs). but thanks to your observation I won't have to use EXEC().
thanks
HP
|||> > You should simply GROUP BY all columns, and if there is a single value
> hey, you're right! so case in Select will do - if I don't want to group
> by a column a can just put static text instead of it.
I guess it won't do because even if I put static text into div1 column
in a select, 'group by div1' will group by original values in a
database div1 column anyway.
greetings
HP
|||I read your original message again and I think I now understand what
you mean. However, the queries that you wrote there will never return
more than one row. Is this really your requirement ?
For another approach, take a look at the WITH CUBE option (see
"Sumarizing Data" in Books Online). For example:
SELECT div1,div2,div3,div4,SUM(cost)
FROM YourTable
GROUP BY div1,div2,div3,div4
WITH CUBE
I think it may return all the data you need with a single query.
Razvan

How to dynamically choose whether to SUM() column or GROU BY this column?

Hi there
I'm developping an application which would deal with costs in
organization which structure is split hierarchically to four division
levels.
eg. one can have atomic unit by setting values:
division1=A3
division2=111
division3=245
division4=1234
despite their hierarchical relation, my client wants to have reports on
all of those division combinations. so the perfect solution would be
the ultraGeneric(div1,div2,div3,div4) procedure which could do:
when called with all 4 parameters
select div1,div2,div3,div4,SUM(cost)
where div1=@.div1 and div2=@.div2 and div3=@.div3 and div4=@.div4
group by div1,div2,div3,div4
while when called with only div1 set (rest would be NULL):
select div1,'all','all,'all',SUM(cost)
where div1=@.div1
group by div1
I know how I can deal with WHERE part:
WHERE (@.DIV1 IS NULL OR @.DIV1= '' OR DIV1=@.DIV1)
but I have no idea what to do with SUM / GROUP BY part.
I can use CASE in select to choose whether to put column name or some
fake string if the column parameter is null.
but what about GROUP BY? does it behave like ORDER BY, in which I'd
have to use CASE with all combinations of parameter states?
thanks a lot
HPsorry, the post title doesn't make sense. to restate the question:
How to choose whether or not GROUP by a given column depending on
whether its value in parameter is null or not?|||You should simply GROUP BY all columns, and if there is a single value
in one of those columns (due to it being used in a condition), so be
it.
You can also use dynamic SQL. Read the following article by Erland
Sommarskog, for more approaches to the dynamic search conditions
problem:
http://www.sommarskog.se/dyn-search.html
Razvan|||Razvan Socol wrote:
> You should simply GROUP BY all columns, and if there is a single value
> in one of those columns (due to it being used in a condition), so be
> it.
hey, you're right! so case in Select will do - if I don't want to group
by a column a can just put static text instead of it.

> You can also use dynamic SQL. Read the following article by Erland
> Sommarskog, for more approaches to the dynamic search conditions
> problem:
> http://www.sommarskog.se/dyn-search.html
thanks, he saved my life once (great article about passing arrays to
procs). but thanks to your observation I won't have to use EXEC().
thanks
HP|||> > You should simply GROUP BY all columns, and if there is a single value
> hey, you're right! so case in Select will do - if I don't want to group
> by a column a can just put static text instead of it.
I guess it won't do because even if I put static text into div1 column
in a select, 'group by div1' will group by original values in a
database div1 column anyway.
greetings
HP|||I read your original message again and I think I now understand what
you mean. However, the queries that you wrote there will never return
more than one row. Is this really your requirement ?
For another approach, take a look at the WITH CUBE option (see
"Sumarizing Data" in Books Online). For example:
SELECT div1,div2,div3,div4,SUM(cost)
FROM YourTable
GROUP BY div1,div2,div3,div4
WITH CUBE
I think it may return all the data you need with a single query.
Razvan

how to do this "between dates" query?

Hi,

I have a query as follows:

SELECT SUM(Total) AS WeekRetailTotal, COUNT(*) AS MonthRetailOrderNo, DATEPART(wk, OrderDate) AS SalesWeek, YEAR(OrderDate) AS SalesYear
FROM dbo.Orders_Retail
WHERE (account = @.Account) AND (OrderStatus <> 'Deleted') AND (PayStatus <> 'Pending') AND (OrderStatus <> 'Refunded')
GROUP BY YEAR(OrderDate), DATEPART(wk, OrderDate)
ORDER BY YEAR(OrderDate), DATEPART(wk, OrderDate)

the results look like this

WeekRetailTotal

MonthRetailOrderNo

SalesWeek

SalesYear

￡397.55

8

3

2002

etc etc for each week in a year and then it goes onto the next year.

What I would like to do, is feed the query a variable as the start week and year and then also for the endweek and year.

I've tried to do a WHERE DATEPART(wk, OrderDate) > @.StartDate AND DATEPART(wk, OrderDate)
< @.EndDate AND YEar(OrderDate) > @.StartYear AND YEAR(OrderDate) < @.EndYear

But that's not correct, it only bring in the weeks in both years that are in between those two week range variables.

I need the startweek and year to be "one" starting point and the endweek and endyear be the ending point.

Any ideas?

Thanks

It is common for Date query questions to receive the suggestion of using a Calendar table to assist. In this case it would help as you could turn your Year/Week pairs into dates and then do a simple date range comparison.

Try this approach, using only date functions:

Where DateDiff( wk, DateAdd(yy, @.StartYear - 1900 , '1/1/1900' ), OrderDate ) >= @.StartWeek

And DateDiff(wk, DateAdd(yy, @.EndYear - 1900, '1/1/1900' ), OrderDate ) < @.EndWeek

The first comparison needs to be >= because the first day of the year is in the first week of the year -- the comparison gets off by one. The end should be okay. I used DateAdd to get the first day of each year, you could use another approach like string concatenation.

|||

There is native BETWEEN-AND syntax support in SQL Server. The bigger issue here is you need to consider day of week, i.e. you want the date range to be between the first day of the start year/week and the last day of the end year/week (inclusive on both ends):

Here is my approach (working sample against Northwind database):

DECLARE @.StartWeek INT, @.EndWeek INT, @.StartYear INT, @.EndYear INT

SET @.StartWeek = 50;

SET @.EndWeek = 2;

SET @.StartYear = 1996;

SET @.EndYear = 1997;

DECLARE @.StartDate DATETIME, @.EndDate DATETIME;

SET @.StartDate = DATEADD(week, @.StartWeek-1, '1/1/' + CAST(@.StartYear AS VARCHAR(4))) -- get into the @.StartWeek-th week

SET @.StartDate = DATEADD(day, 1-DATEPART(weekday, @.StartDate), @.StartDate) -- get the First Day (Sunday by default) of that week

SET @.EndDate = DATEADD(week, @.EndWeek-1, '1/1/' + CAST(@.EndYear AS VARCHAR(4))) -- get into the @.EndWeek-th week

SET @.EndDate = DATEADD(day, 7-DATEPART(weekday, @.EndDate), @.EndDate) -- get the Last Day (Saturday by default) of that week

--SELECT @.StartDate, @.EndDate

SELECT orderdate, DATEPART(year, orderdate) AS Year, DATEPART(week, orderdate) AS Week, DATEPART(weekday, orderdate) AS DayOfWeek FROM orders

WHERE OrderDate BETWEEN @.StartDate AND @.EndDate

|||thanks guys! - I'll give it a go!!

How to do SUM of Average in subtotal cell for the entire matrix

Hi,

I have a matrix as shown below:

Head Count Jan-07 Feb-07 Average Dept1 59.00 62.00 60.50 Dept2 21.00 21.00 21.00 Total 80.00 83.00 81.50

I am having trouble figuring out how to ADD the "Average" column to get the 81.50 (red). I tried SUM(AVG(Fields!....)) but it didn't work.

Any help is appreacited!

Thanks,

Tabbey

Don't try to SUM it, just leave the formula as is and it should give you the correct result in the total row, the average of 80 and 83 is 81.50.|||

Can you explain me what you did in report ?

How do you populate data into the Avg and Total i.e subtotal of rows and columns ?

|||

Sluggy,

I apologize I did not explain that correctly. The Dept has a nested group of Divisions as shown in the new example below. The RED highlited cells give the "WRONG" AVERAGE (using function AVG). That's the reason I think I need the AVG(SUM(...)).

Jan 07 Feb 07 Average Dept1 Div 1A 345.00 345.00 345.00 Div 1B 41.00 41.00 41.00 Div 1C 283.00 283.00 283.00 Total 669.00 669.00 223.00 Dept2 Div 2A 8.00 8.00 8.00 Div 2B 63.00 63.00 63.00 Div 2C 2.00 2.00 2.00 Total 73.00 73.00 24.33

As the result, I get the wrong average when the Divisions are collapsed as shown below (in RED highlite):

Jan 07 Feb 07 Average Dept1 669.00 669.00 223.00 Dept2 73.00 73.00 24.33

What is the best solution for situation like this?

Thanks,

Tabbey

|||What you are looking to accomplish requires nested aggregates, the average of a subtotal. Nested aggregates are not currently supported. There is a workaround, however. The workaround is to use a custom function that uses the appropriate expression, based on the current scope. In this example, there are four different scopes in which the cell is calculated. One for the cell, the subtotal, average, and the average of the subtotals. The custom function for the first two cases simply returns the aggregate value passed in. The third case will take the subtotal passed in and add it to a running total, as well as keep a count values added to the total, and then return the subtotal. The fourth case will calculate and return the average, and reset the running total and count.

The custom function is called using the following expression, assuming that the column group is named "Date" and the inner row grouping is named "Division."

=Code.CalculateSumSubtotalOrAverage(Sum(Fields!FieldName.Value),AVG(Fields!FieldName.Value), InScope("Date"), InScope("Division"))

Here is the custom function.

Code Snippet

Private m_total As Double
Private m_count As Integer

Public Function CalculateSumSubtotalOrAverage(subtotal As Double, average As Double, inDateScope as Boolean, inDivisionScope As Boolean) As Double

If inDateScope And inDivisionScope Then
' Regular cell
Return subtotal

Else If Not inDateScope And inDivisionScope Then
' Average of Division

Return average

Else If inDateScope And Not inDivisionScope Then
' Subtotal of Date
m_total = m_total + subtotal
m_count = m_count + 1

Return subtotal

Else
' Average of Subtotal
Dim avg as Double
avg = m_total / m_count

m_count = 0
m_total = 0

Return avg

End If

End Function

|||

Ian, would this code also help resolve my issue here?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1515409&SiteID=1|||Yes, it should, but you will need to pass in the results of the InScope function for Year and Quarter, and the only the result of the SUM aggregate are needed.

In the function, if Year is in scope and Quarter is not, then update the variance calculation, and return the subtotal. If both Year and Quarter are not in scope, then calculate and return the variance. Otherwise, just return the sum. This should produce the variance over the year subtotals for the column grand total and regular subtotals for the Customer and Year subtotals.|||

Hi Ian,

I don't know visual basic and I don't know how to calculate the variance using a visual basic code. Could you write a visual basic code that calculates the variance in scope just like the one you wrote above?

Thanks, Susan

|||

Ian,

I have a report project in VS2005 with a matrix on the report. I have added a rowgroup on date and I want to average the values for each column at the bottom of the report. I cannot find anything within the Matrix Properties dialog that lets me specify Averaging as opposed to Summing the totals.

I see what you are doing with this function, but where do I put this code? VS will not let me add a code module or anything other than another report.

Thanks,

Russ.

|||

Hi Ian,

Could your code be adapted to give me the max value of a specific column of a table ?

For more details please check out this : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1979307&SiteID=1

If you could help me with some hints this would be great.

Greetings

Vinnie

|||Hi Vinnie,

Yes, the above can be modified to accomplish this. However, you don't really need anything this complicated.

You can use the first function below in the body of the table, and use the second one in the table footer.

Code Snippet

Private m_max As Integer = -1

Public Function StoreMaxValueForHour(maxValueForHour as Integer) As Integer

m_max = Math.Max(m_max, maxValueForHour)
Return maxValueForHour

End Function

Public Function GetMaxHourSubtotal() As Integer

Return m_max

End Function

The first one is called like this:

=Code.StoreMaxValueForHour(CInt(math.Max((sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest2.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest3.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest4.Value)/sum(Fields!CountTotalTest.Value)*100), (sum(Fields!CountTest5.Value)/sum(Fields!CountTotalTest.Value)*100)))))))

Actually, you can simplifiy this a little using ReportItem references:

=Code.StoreMaxValueForHour(math.Max(ReportItems!TableBodyCountTest1.Value, math.Max(ReportItems!TableBodyCountTest2.Value, math.Max(ReportItems!TableBodyCountTest3.Value, math.Max(ReportItems!TableBodyCountTest4.Value, ReportItems!TableBodyCountTest5.Value)))))

The second in the footer is called like:

=Code.GetMaxHourSubtotal()

I hope this helps.

Ian|||

I've been reading this discussion thread as I'm running into a similar problem wherein I have a table that performs a Count(# of support cases) per Month. The table groups on month. I'm trying to get the max(count(#support cases)).

I tried to implement the code suggested from above; the first function works fine "StoreMaxValueForHour", but the second function just returns -1. I've placed the second function in the table footer.

What's the secret to making it work?

|||The reason this is not working is that the Table headers and footers are calculated before the inner groups and details. The code discussed here is really only appropriate for Matrix report items.

Ian|||

Ian Roof - MSFT wrote:

The reason this is not working is that the Table headers and footers are calculated before the inner groups and details. The code discussed here is really only appropriate for Matrix report items.

Ian

Oooh... good to know. Well, okay then.

Thanks for the clarification, Ian.

So, I'm guessing this means that a table data region just doesn't offer much in the way of getting to a solution for an aggregate of an aggregate. Your suggestion (relevant to a matrix) seems like the closest thing to a simple solution of a nested aggregate that I've been able to find.

--Pete

|||One thing you may be able to try is to create a two Tables. One table that is hidden and uses the first function that does not have headers and footers, and your current table that only uses the second function. Make sure that the new, hidden table is above and to the left of your current table, so that it will be executed first.

Ian

How to do SUM of Average in subtotal cell for the entire matrix

Hi,

I have a matrix as shown below:

Head Count Jan-07 Feb-07 Average Dept1 59.00 62.00 60.50 Dept2 21.00 21.00 21.00 Total 80.00 83.00 81.50

I am having trouble figuring out how to ADD the "Average" column to get the 81.50 (red). I tried SUM(AVG(Fields!....)) but it didn't work.

Any help is appreacited!

Thanks,

Tabbey

Don't try to SUM it, just leave the formula as is and it should give you the correct result in the total row, the average of 80 and 83 is 81.50.|||

Can you explain me what you did in report ?

How do you populate data into the Avg and Total i.e subtotal of rows and columns ?

|||

Sluggy,

I apologize I did not explain that correctly. The Dept has a nested group of Divisions as shown in the new example below. The RED highlited cells give the "WRONG" AVERAGE (using function AVG). That's the reason I think I need the AVG(SUM(...)).

Jan 07 Feb 07 Average Dept1 Div 1A 345.00 345.00 345.00 Div 1B 41.00 41.00 41.00 Div 1C 283.00 283.00 283.00 Total 669.00 669.00 223.00 Dept2 Div 2A 8.00 8.00 8.00 Div 2B 63.00 63.00 63.00 Div 2C 2.00 2.00 2.00 Total 73.00 73.00 24.33

As the result, I get the wrong average when the Divisions are collapsed as shown below (in RED highlite):

Jan 07 Feb 07 Average Dept1 669.00 669.00 223.00 Dept2 73.00 73.00 24.33

What is the best solution for situation like this?

Thanks,

Tabbey

|||What you are looking to accomplish requires nested aggregates, the average of a subtotal. Nested aggregates are not currently supported. There is a workaround, however. The workaround is to use a custom function that uses the appropriate expression, based on the current scope. In this example, there are four different scopes in which the cell is calculated. One for the cell, the subtotal, average, and the average of the subtotals. The custom function for the first two cases simply returns the aggregate value passed in. The third case will take the subtotal passed in and add it to a running total, as well as keep a count values added to the total, and then return the subtotal. The fourth case will calculate and return the average, and reset the running total and count.

The custom function is called using the following expression, assuming that the column group is named "Date" and the inner row grouping is named "Division."

=Code.CalculateSumSubtotalOrAverage(Sum(Fields!FieldName.Value),AVG(Fields!FieldName.Value), InScope("Date"), InScope("Division"))

Here is the custom function.

Code Snippet

Private m_total As Double
Private m_count As Integer

Public Function CalculateSumSubtotalOrAverage(subtotal As Double, average As Double, inDateScope as Boolean, inDivisionScope As Boolean) As Double

If inDateScope And inDivisionScope Then
' Regular cell
Return subtotal

Else If Not inDateScope And inDivisionScope Then
' Average of Division

Return average

Else If inDateScope And Not inDivisionScope Then
' Subtotal of Date
m_total = m_total + subtotal
m_count = m_count + 1

Return subtotal

Else
' Average of Subtotal
Dim avg as Double
avg = m_total / m_count

m_count = 0
m_total = 0

Return avg

End If

End Function

|||

Ian, would this code also help resolve my issue here?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1515409&SiteID=1|||Yes, it should, but you will need to pass in the results of the InScope function for Year and Quarter, and the only the result of the SUM aggregate are needed.

In the function, if Year is in scope and Quarter is not, then update the variance calculation, and return the subtotal. If both Year and Quarter are not in scope, then calculate and return the variance. Otherwise, just return the sum. This should produce the variance over the year subtotals for the column grand total and regular subtotals for the Customer and Year subtotals.|||

Hi Ian,

I don't know visual basic and I don't know how to calculate the variance using a visual basic code. Could you write a visual basic code that calculates the variance in scope just like the one you wrote above?

Thanks, Susan

|||

Ian,

I have a report project in VS2005 with a matrix on the report. I have added a rowgroup on date and I want to average the values for each column at the bottom of the report. I cannot find anything within the Matrix Properties dialog that lets me specify Averaging as opposed to Summing the totals.

I see what you are doing with this function, but where do I put this code? VS will not let me add a code module or anything other than another report.

Thanks,

Russ.

|||

Hi Ian,

Could your code be adapted to give me the max value of a specific column of a table ?

For more details please check out this : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1979307&SiteID=1

If you could help me with some hints this would be great.

Greetings

Vinnie

|||Hi Vinnie,

Yes, the above can be modified to accomplish this. However, you don't really need anything this complicated.

You can use the first function below in the body of the table, and use the second one in the table footer.

Code Snippet

Private m_max As Integer = -1

Public Function StoreMaxValueForHour(maxValueForHour as Integer) As Integer

m_max = Math.Max(m_max, maxValueForHour)
Return maxValueForHour

End Function

Public Function GetMaxHourSubtotal() As Integer

Return m_max

End Function

The first one is called like this:

=Code.StoreMaxValueForHour(CInt(math.Max((sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest2.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest3.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest4.Value)/sum(Fields!CountTotalTest.Value)*100), (sum(Fields!CountTest5.Value)/sum(Fields!CountTotalTest.Value)*100)))))))

Actually, you can simplifiy this a little using ReportItem references:

=Code.StoreMaxValueForHour(math.Max(ReportItems!TableBodyCountTest1.Value, math.Max(ReportItems!TableBodyCountTest2.Value, math.Max(ReportItems!TableBodyCountTest3.Value, math.Max(ReportItems!TableBodyCountTest4.Value, ReportItems!TableBodyCountTest5.Value)))))

The second in the footer is called like:

=Code.GetMaxHourSubtotal()

I hope this helps.

Ian|||

I've been reading this discussion thread as I'm running into a similar problem wherein I have a table that performs a Count(# of support cases) per Month. The table groups on month. I'm trying to get the max(count(#support cases)).

I tried to implement the code suggested from above; the first function works fine "StoreMaxValueForHour", but the second function just returns -1. I've placed the second function in the table footer.

What's the secret to making it work?

|||The reason this is not working is that the Table headers and footers are calculated before the inner groups and details. The code discussed here is really only appropriate for Matrix report items.

Ian

How to do SUM of Average in subtotal cell for the entire matrix

Hi,

I have a matrix as shown below:

Head Count Jan-07 Feb-07 Average Dept1 59.00 62.00 60.50 Dept2 21.00 21.00 21.00 Total 80.00 83.00 81.50

I am having trouble figuring out how to ADD the "Average" column to get the 81.50 (red). I tried SUM(AVG(Fields!....)) but it didn't work.

Any help is appreacited!

Thanks,

Tabbey

Don't try to SUM it, just leave the formula as is and it should give you the correct result in the total row, the average of 80 and 83 is 81.50.|||

Can you explain me what you did in report ?

How do you populate data into the Avg and Total i.e subtotal of rows and columns ?

|||

Sluggy,

I apologize I did not explain that correctly. The Dept has a nested group of Divisions as shown in the new example below. The RED highlited cells give the "WRONG" AVERAGE (using function AVG). That's the reason I think I need the AVG(SUM(...)).

Jan 07 Feb 07 Average Dept1 Div 1A 345.00 345.00 345.00 Div 1B 41.00 41.00 41.00 Div 1C 283.00 283.00 283.00 Total 669.00 669.00 223.00 Dept2 Div 2A 8.00 8.00 8.00 Div 2B 63.00 63.00 63.00 Div 2C 2.00 2.00 2.00 Total 73.00 73.00 24.33

As the result, I get the wrong average when the Divisions are collapsed as shown below (in RED highlite):

Jan 07 Feb 07 Average Dept1 669.00 669.00 223.00 Dept2 73.00 73.00 24.33

What is the best solution for situation like this?

Thanks,

Tabbey

|||What you are looking to accomplish requires nested aggregates, the average of a subtotal. Nested aggregates are not currently supported. There is a workaround, however. The workaround is to use a custom function that uses the appropriate expression, based on the current scope. In this example, there are four different scopes in which the cell is calculated. One for the cell, the subtotal, average, and the average of the subtotals. The custom function for the first two cases simply returns the aggregate value passed in. The third case will take the subtotal passed in and add it to a running total, as well as keep a count values added to the total, and then return the subtotal. The fourth case will calculate and return the average, and reset the running total and count.

The custom function is called using the following expression, assuming that the column group is named "Date" and the inner row grouping is named "Division."

=Code.CalculateSumSubtotalOrAverage(Sum(Fields!FieldName.Value),AVG(Fields!FieldName.Value), InScope("Date"), InScope("Division"))

Here is the custom function.

Code Snippet

Private m_total As Double
Private m_count As Integer

Public Function CalculateSumSubtotalOrAverage(subtotal As Double, average As Double, inDateScope as Boolean, inDivisionScope As Boolean) As Double

If inDateScope And inDivisionScope Then
' Regular cell
Return subtotal

Else If Not inDateScope And inDivisionScope Then
' Average of Division

Return average

Else If inDateScope And Not inDivisionScope Then
' Subtotal of Date
m_total = m_total + subtotal
m_count = m_count + 1

Return subtotal

Else
' Average of Subtotal
Dim avg as Double
avg = m_total / m_count

m_count = 0
m_total = 0

Return avg

End If

End Function

|||

Ian, would this code also help resolve my issue here?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1515409&SiteID=1|||Yes, it should, but you will need to pass in the results of the InScope function for Year and Quarter, and the only the result of the SUM aggregate are needed.

In the function, if Year is in scope and Quarter is not, then update the variance calculation, and return the subtotal. If both Year and Quarter are not in scope, then calculate and return the variance. Otherwise, just return the sum. This should produce the variance over the year subtotals for the column grand total and regular subtotals for the Customer and Year subtotals.|||

Hi Ian,

I don't know visual basic and I don't know how to calculate the variance using a visual basic code. Could you write a visual basic code that calculates the variance in scope just like the one you wrote above?

Thanks, Susan

|||

Ian,

I have a report project in VS2005 with a matrix on the report. I have added a rowgroup on date and I want to average the values for each column at the bottom of the report. I cannot find anything within the Matrix Properties dialog that lets me specify Averaging as opposed to Summing the totals.

I see what you are doing with this function, but where do I put this code? VS will not let me add a code module or anything other than another report.

Thanks,

Russ.

|||

Hi Ian,

Could your code be adapted to give me the max value of a specific column of a table ?

For more details please check out this : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1979307&SiteID=1

If you could help me with some hints this would be great.

Greetings

Vinnie

|||Hi Vinnie,

Yes, the above can be modified to accomplish this. However, you don't really need anything this complicated.

You can use the first function below in the body of the table, and use the second one in the table footer.

Code Snippet

Private m_max As Integer = -1

Public Function StoreMaxValueForHour(maxValueForHour as Integer) As Integer

m_max = Math.Max(m_max, maxValueForHour)
Return maxValueForHour

End Function

Public Function GetMaxHourSubtotal() As Integer

Return m_max

End Function

The first one is called like this:

=Code.StoreMaxValueForHour(CInt(math.Max((sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest2.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest3.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest4.Value)/sum(Fields!CountTotalTest.Value)*100), (sum(Fields!CountTest5.Value)/sum(Fields!CountTotalTest.Value)*100)))))))

Actually, you can simplifiy this a little using ReportItem references:

=Code.StoreMaxValueForHour(math.Max(ReportItems!TableBodyCountTest1.Value, math.Max(ReportItems!TableBodyCountTest2.Value, math.Max(ReportItems!TableBodyCountTest3.Value, math.Max(ReportItems!TableBodyCountTest4.Value, ReportItems!TableBodyCountTest5.Value)))))

The second in the footer is called like:

=Code.GetMaxHourSubtotal()

I hope this helps.

Ian|||

I've been reading this discussion thread as I'm running into a similar problem wherein I have a table that performs a Count(# of support cases) per Month. The table groups on month. I'm trying to get the max(count(#support cases)).

I tried to implement the code suggested from above; the first function works fine "StoreMaxValueForHour", but the second function just returns -1. I've placed the second function in the table footer.

What's the secret to making it work?