Aggregate values based on the value of another field

I'm not having much fun trying to aggregate values based on the value of
another field
My query is returning the following...
DATE, VOLUME, PRODUCT
1/1/07, 22, OIL
1/1/07, 0, WATER
1/2/07, 8, OIL
1/2/07, 12, WATER
I want to sum all the VOLUME values where the product = 'OIL' and another to
sum where the product = 'GAS'. Is this type of conditional aggregation
possible. If so how?Correction: I want I want to sum all the VOLUME values where the product ='OIL' and another to sum where the product = 'WATER'.
"smithcjb" wrote:
> I'm not having much fun trying to aggregate values based on the value of
> another field
> My query is returning the following...
> DATE, VOLUME, PRODUCT
> 1/1/07, 22, OIL
> 1/1/07, 0, WATER
> 1/2/07, 8, OIL
> 1/2/07, 12, WATER
> I want to sum all the VOLUME values where the product = 'OIL' and another to
> sum where the product = 'GAS'. Is this type of conditional aggregation
> possible. If so how?|||Hey Smithcjb,
If you are trying to do this in your SQL statement, then drop the "date"
(you can leave it in if your are filtering by it, just make sure you define
it as part of the WHERE clause only). Write it like this
SELECT PRODUCT, SUM(VOLUME)
FROM MyTable
WHERE Date Between Date1 and Date2
GROUP BY PRODUCT
If your trying to do this in a table cell or matrix you could use a
conditinal that says
=SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
then again for water
=SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
and so on...
Michael C
"smithcjb" wrote:
> I'm not having much fun trying to aggregate values based on the value of
> another field
> My query is returning the following...
> DATE, VOLUME, PRODUCT
> 1/1/07, 22, OIL
> 1/1/07, 0, WATER
> 1/2/07, 8, OIL
> 1/2/07, 12, WATER
> I want to sum all the VOLUME values where the product = 'OIL' and another to
> sum where the product = 'GAS'. Is this type of conditional aggregation
> possible. If so how?|||For performance reasons I wanted to do this in the report and not in the SQL.
I've tried grouping by the following in the report with no success...
=SUM(IIf(Fields!PRODUCT.Value="OIL",Fields!LIQUID_VOL.Value,0))
"Michael C" wrote:
> Hey Smithcjb,
> If you are trying to do this in your SQL statement, then drop the "date"
> (you can leave it in if your are filtering by it, just make sure you define
> it as part of the WHERE clause only). Write it like this
> SELECT PRODUCT, SUM(VOLUME)
> FROM MyTable
> WHERE Date Between Date1 and Date2
> GROUP BY PRODUCT
> If your trying to do this in a table cell or matrix you could use a
> conditinal that says
> =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> then again for water
> =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> and so on...
> Michael C
> "smithcjb" wrote:
> > I'm not having much fun trying to aggregate values based on the value of
> > another field
> >
> > My query is returning the following...
> > DATE, VOLUME, PRODUCT
> >
> > 1/1/07, 22, OIL
> > 1/1/07, 0, WATER
> > 1/2/07, 8, OIL
> > 1/2/07, 12, WATER
> >
> > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > sum where the product = 'GAS'. Is this type of conditional aggregation
> > possible. If so how?|||Just to reiterate. I want to perform this in the report, not the query. I
tried the conditional expressions you provided but the report just returns
"Error" in the field
"Michael C" wrote:
> Hey Smithcjb,
> If you are trying to do this in your SQL statement, then drop the "date"
> (you can leave it in if your are filtering by it, just make sure you define
> it as part of the WHERE clause only). Write it like this
> SELECT PRODUCT, SUM(VOLUME)
> FROM MyTable
> WHERE Date Between Date1 and Date2
> GROUP BY PRODUCT
> If your trying to do this in a table cell or matrix you could use a
> conditinal that says
> =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> then again for water
> =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> and so on...
> Michael C
> "smithcjb" wrote:
> > I'm not having much fun trying to aggregate values based on the value of
> > another field
> >
> > My query is returning the following...
> > DATE, VOLUME, PRODUCT
> >
> > 1/1/07, 22, OIL
> > 1/1/07, 0, WATER
> > 1/2/07, 8, OIL
> > 1/2/07, 12, WATER
> >
> > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > sum where the product = 'GAS'. Is this type of conditional aggregation
> > possible. If so how?|||Is this aggregate happening in a table? Is it happening in a Group footer?
What is the grouping? What error are you getting? By all accounts this
should work.
Michael C.
"smithcjb" wrote:
> For performance reasons I wanted to do this in the report and not in the SQL.
> I've tried grouping by the following in the report with no success...
> =SUM(IIf(Fields!PRODUCT.Value="OIL",Fields!LIQUID_VOL.Value,0))
> "Michael C" wrote:
> > Hey Smithcjb,
> > If you are trying to do this in your SQL statement, then drop the "date"
> > (you can leave it in if your are filtering by it, just make sure you define
> > it as part of the WHERE clause only). Write it like this
> >
> > SELECT PRODUCT, SUM(VOLUME)
> > FROM MyTable
> > WHERE Date Between Date1 and Date2
> > GROUP BY PRODUCT
> >
> > If your trying to do this in a table cell or matrix you could use a
> > conditinal that says
> >
> > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > then again for water
> > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> >
> > and so on...
> >
> > Michael C
> >
> > "smithcjb" wrote:
> >
> > > I'm not having much fun trying to aggregate values based on the value of
> > > another field
> > >
> > > My query is returning the following...
> > > DATE, VOLUME, PRODUCT
> > >
> > > 1/1/07, 22, OIL
> > > 1/1/07, 0, WATER
> > > 1/2/07, 8, OIL
> > > 1/2/07, 12, WATER
> > >
> > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > possible. If so how?|||Tables (also called matrix) exist INSIDE reports and have nothing to do with
Datasets. I fully understand what your saying, so no need to "reiterate"
anything.
Also, the comment "for performance reasons i want to do this in the report".
Are you saying you want the report to run slower? Performance wise , as far
as I've read, would suggest you do this in the SQL. But hey...its up to you.
Michael C.
"smithcjb" wrote:
> Just to reiterate. I want to perform this in the report, not the query. I
> tried the conditional expressions you provided but the report just returns
> "Error" in the field
> "Michael C" wrote:
> > Hey Smithcjb,
> > If you are trying to do this in your SQL statement, then drop the "date"
> > (you can leave it in if your are filtering by it, just make sure you define
> > it as part of the WHERE clause only). Write it like this
> >
> > SELECT PRODUCT, SUM(VOLUME)
> > FROM MyTable
> > WHERE Date Between Date1 and Date2
> > GROUP BY PRODUCT
> >
> > If your trying to do this in a table cell or matrix you could use a
> > conditinal that says
> >
> > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > then again for water
> > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> >
> > and so on...
> >
> > Michael C
> >
> > "smithcjb" wrote:
> >
> > > I'm not having much fun trying to aggregate values based on the value of
> > > another field
> > >
> > > My query is returning the following...
> > > DATE, VOLUME, PRODUCT
> > >
> > > 1/1/07, 22, OIL
> > > 1/1/07, 0, WATER
> > > 1/2/07, 8, OIL
> > > 1/2/07, 12, WATER
> > >
> > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > possible. If so how?|||Michael,
I appreciate your comments. This is my full query. I'm returning 3 rows per
date (1 per product). I've tried to use inner joins on the view I'm pulling
from but performance is woeful for large date ranges. I'm trying to sum the
production for the specified date range for each product - easy for GAS since
it appears in it's own column! Any further help greatly appreciated. I don't
know why the expressions you provided won;t workm - they seem logical to me!
Kind regards,
Colin
SELECT c.ITEM_NAME AS COMPLETION_NAME, c.ITEM_ID AS COMPLETION_ITEM_ID,
p.START_DATETIME, p.GAS_VOL, p.LIQUID_VOL,p.PRODUCT
FROM dbo.VI_COMPLETION_en_US AS c INNER JOIN
dbo.ITEM_LINK AS il ON c.ITEM_ID = il.FROM_ITEM_ID
INNER JOIN
dbo.VT_ACT_DAY_en_US AS p ON c.ITEM_ID = p.ITEM_ID
WHERE (il.LINK_TYPE = 'NET_MEMBER') AND (p.START_DATETIME between
'1/1/04' and '1/1/07') AND (c.ITEM_ID IN
(SELECT COMPLETION_ITEM_ID
FROM dbo.REP_ORG_COMPLETION
WHERE (FIELD_ITEM_ID = @.FieldItemId)))
GROUP BY c.ITEM_NAME, c.ITEM_ID, p.START_DATETIME, p.GAS_VOL,
p.LIQUID_VOL,p.PRODUCT
"Michael C" wrote:
>
> Tables (also called matrix) exist INSIDE reports and have nothing to do with
> Datasets. I fully understand what your saying, so no need to "reiterate"
> anything.
>
> Also, the comment "for performance reasons i want to do this in the report".
> Are you saying you want the report to run slower? Performance wise , as far
> as I've read, would suggest you do this in the SQL. But hey...its up to you.
> Michael C.
> "smithcjb" wrote:
> > Just to reiterate. I want to perform this in the report, not the query. I
> > tried the conditional expressions you provided but the report just returns
> > "Error" in the field
> >
> > "Michael C" wrote:
> >
> > > Hey Smithcjb,
> > > If you are trying to do this in your SQL statement, then drop the "date"
> > > (you can leave it in if your are filtering by it, just make sure you define
> > > it as part of the WHERE clause only). Write it like this
> > >
> > > SELECT PRODUCT, SUM(VOLUME)
> > > FROM MyTable
> > > WHERE Date Between Date1 and Date2
> > > GROUP BY PRODUCT
> > >
> > > If your trying to do this in a table cell or matrix you could use a
> > > conditinal that says
> > >
> > > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > > then again for water
> > > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> > >
> > > and so on...
> > >
> > > Michael C
> > >
> > > "smithcjb" wrote:
> > >
> > > > I'm not having much fun trying to aggregate values based on the value of
> > > > another field
> > > >
> > > > My query is returning the following...
> > > > DATE, VOLUME, PRODUCT
> > > >
> > > > 1/1/07, 22, OIL
> > > > 1/1/07, 0, WATER
> > > > 1/2/07, 8, OIL
> > > > 1/2/07, 12, WATER
> > > >
> > > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > > possible. If so how?|||Okay, so now I do understand your want to do this in report. I'm a little
stumped at why the IIF won't work (Unless the report crosses pages?).
I would suggest trying a CASE statement in your SQL to give both OIL and
WATER their own column at least to benchmark what the added overhead is.
CASE WHEN p.PRODUCT = 'OIL' THEN p.LIQUID_VOL ELSE 0 END as OIL_VOL,
CASE WHEN p.PRODUCT = 'WATER' THEN p.LIQUID_VOL ELSE 0 END as WATER_VOL,
If it is minimal overhead to do this then voila, you now have columns for
both oil and water (unless of course other products like NGL's and
Condensates are being included in your products list in which case you'll
need to expand the case statements).
Michael C.
"smithcjb" wrote:
> Michael,
> I appreciate your comments. This is my full query. I'm returning 3 rows per
> date (1 per product). I've tried to use inner joins on the view I'm pulling
> from but performance is woeful for large date ranges. I'm trying to sum the
> production for the specified date range for each product - easy for GAS since
> it appears in it's own column! Any further help greatly appreciated. I don't
> know why the expressions you provided won;t workm - they seem logical to me!
> Kind regards,
> Colin
> SELECT c.ITEM_NAME AS COMPLETION_NAME, c.ITEM_ID AS COMPLETION_ITEM_ID,
> p.START_DATETIME, p.GAS_VOL, p.LIQUID_VOL,p.PRODUCT
> FROM dbo.VI_COMPLETION_en_US AS c INNER JOIN
> dbo.ITEM_LINK AS il ON c.ITEM_ID = il.FROM_ITEM_ID
> INNER JOIN
> dbo.VT_ACT_DAY_en_US AS p ON c.ITEM_ID = p.ITEM_ID
> WHERE (il.LINK_TYPE = 'NET_MEMBER') AND (p.START_DATETIME between
> '1/1/04' and '1/1/07') AND (c.ITEM_ID IN
> (SELECT COMPLETION_ITEM_ID
> FROM dbo.REP_ORG_COMPLETION
> WHERE (FIELD_ITEM_ID = @.FieldItemId)))
> GROUP BY c.ITEM_NAME, c.ITEM_ID, p.START_DATETIME, p.GAS_VOL,
> p.LIQUID_VOL,p.PRODUCT
> "Michael C" wrote:
> >
> >
> > Tables (also called matrix) exist INSIDE reports and have nothing to do with
> > Datasets. I fully understand what your saying, so no need to "reiterate"
> > anything.
> >
> >
> > Also, the comment "for performance reasons i want to do this in the report".
> > Are you saying you want the report to run slower? Performance wise , as far
> > as I've read, would suggest you do this in the SQL. But hey...its up to you.
> >
> > Michael C.
> >
> > "smithcjb" wrote:
> >
> > > Just to reiterate. I want to perform this in the report, not the query. I
> > > tried the conditional expressions you provided but the report just returns
> > > "Error" in the field
> > >
> > > "Michael C" wrote:
> > >
> > > > Hey Smithcjb,
> > > > If you are trying to do this in your SQL statement, then drop the "date"
> > > > (you can leave it in if your are filtering by it, just make sure you define
> > > > it as part of the WHERE clause only). Write it like this
> > > >
> > > > SELECT PRODUCT, SUM(VOLUME)
> > > > FROM MyTable
> > > > WHERE Date Between Date1 and Date2
> > > > GROUP BY PRODUCT
> > > >
> > > > If your trying to do this in a table cell or matrix you could use a
> > > > conditinal that says
> > > >
> > > > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > > > then again for water
> > > > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> > > >
> > > > and so on...
> > > >
> > > > Michael C
> > > >
> > > > "smithcjb" wrote:
> > > >
> > > > > I'm not having much fun trying to aggregate values based on the value of
> > > > > another field
> > > > >
> > > > > My query is returning the following...
> > > > > DATE, VOLUME, PRODUCT
> > > > >
> > > > > 1/1/07, 22, OIL
> > > > > 1/1/07, 0, WATER
> > > > > 1/2/07, 8, OIL
> > > > > 1/2/07, 12, WATER
> > > > >
> > > > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > > > possible. If so how?|||Well,I hope i'm not being too much of a pain, but you could also use custom
code ( I realize my last answer is exactly what you DIDN"T want to do).
you can create a function that fires on each detail setting static variables
over the group, then a second function that returns the answers at the end of
the group. this is actually quite easy to accomplish too.
Sorry I can't be of more help Colin.
Michael C.
"Michael C" wrote:
> Okay, so now I do understand your want to do this in report. I'm a little
> stumped at why the IIF won't work (Unless the report crosses pages?).
> I would suggest trying a CASE statement in your SQL to give both OIL and
> WATER their own column at least to benchmark what the added overhead is.
> CASE WHEN p.PRODUCT = 'OIL' THEN p.LIQUID_VOL ELSE 0 END as OIL_VOL,
> CASE WHEN p.PRODUCT = 'WATER' THEN p.LIQUID_VOL ELSE 0 END as WATER_VOL,
> If it is minimal overhead to do this then voila, you now have columns for
> both oil and water (unless of course other products like NGL's and
> Condensates are being included in your products list in which case you'll
> need to expand the case statements).
>
> Michael C.
>
>
>
> "smithcjb" wrote:
> > Michael,
> >
> > I appreciate your comments. This is my full query. I'm returning 3 rows per
> > date (1 per product). I've tried to use inner joins on the view I'm pulling
> > from but performance is woeful for large date ranges. I'm trying to sum the
> > production for the specified date range for each product - easy for GAS since
> > it appears in it's own column! Any further help greatly appreciated. I don't
> > know why the expressions you provided won;t workm - they seem logical to me!
> >
> > Kind regards,
> > Colin
> >
> > SELECT c.ITEM_NAME AS COMPLETION_NAME, c.ITEM_ID AS COMPLETION_ITEM_ID,
> > p.START_DATETIME, p.GAS_VOL, p.LIQUID_VOL,p.PRODUCT
> > FROM dbo.VI_COMPLETION_en_US AS c INNER JOIN
> > dbo.ITEM_LINK AS il ON c.ITEM_ID = il.FROM_ITEM_ID
> > INNER JOIN
> > dbo.VT_ACT_DAY_en_US AS p ON c.ITEM_ID = p.ITEM_ID
> > WHERE (il.LINK_TYPE = 'NET_MEMBER') AND (p.START_DATETIME between
> > '1/1/04' and '1/1/07') AND (c.ITEM_ID IN
> > (SELECT COMPLETION_ITEM_ID
> > FROM dbo.REP_ORG_COMPLETION
> > WHERE (FIELD_ITEM_ID = @.FieldItemId)))
> > GROUP BY c.ITEM_NAME, c.ITEM_ID, p.START_DATETIME, p.GAS_VOL,
> > p.LIQUID_VOL,p.PRODUCT
> >
> > "Michael C" wrote:
> >
> > >
> > >
> > > Tables (also called matrix) exist INSIDE reports and have nothing to do with
> > > Datasets. I fully understand what your saying, so no need to "reiterate"
> > > anything.
> > >
> > >
> > > Also, the comment "for performance reasons i want to do this in the report".
> > > Are you saying you want the report to run slower? Performance wise , as far
> > > as I've read, would suggest you do this in the SQL. But hey...its up to you.
> > >
> > > Michael C.
> > >
> > > "smithcjb" wrote:
> > >
> > > > Just to reiterate. I want to perform this in the report, not the query. I
> > > > tried the conditional expressions you provided but the report just returns
> > > > "Error" in the field
> > > >
> > > > "Michael C" wrote:
> > > >
> > > > > Hey Smithcjb,
> > > > > If you are trying to do this in your SQL statement, then drop the "date"
> > > > > (you can leave it in if your are filtering by it, just make sure you define
> > > > > it as part of the WHERE clause only). Write it like this
> > > > >
> > > > > SELECT PRODUCT, SUM(VOLUME)
> > > > > FROM MyTable
> > > > > WHERE Date Between Date1 and Date2
> > > > > GROUP BY PRODUCT
> > > > >
> > > > > If your trying to do this in a table cell or matrix you could use a
> > > > > conditinal that says
> > > > >
> > > > > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > > > > then again for water
> > > > > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> > > > >
> > > > > and so on...
> > > > >
> > > > > Michael C
> > > > >
> > > > > "smithcjb" wrote:
> > > > >
> > > > > > I'm not having much fun trying to aggregate values based on the value of
> > > > > > another field
> > > > > >
> > > > > > My query is returning the following...
> > > > > > DATE, VOLUME, PRODUCT
> > > > > >
> > > > > > 1/1/07, 22, OIL
> > > > > > 1/1/07, 0, WATER
> > > > > > 1/2/07, 8, OIL
> > > > > > 1/2/07, 12, WATER
> > > > > >
> > > > > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > > > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > > > > possible. If so how?|||Michael,
As it happens your SQL has been of great value. I've rebuilt the view and
the CASE statements (which are new to me) - and seem to be doing the trick.
Many thanks,
Colin
"Michael C" wrote:
> Well,I hope i'm not being too much of a pain, but you could also use custom
> code ( I realize my last answer is exactly what you DIDN"T want to do).
> you can create a function that fires on each detail setting static variables
> over the group, then a second function that returns the answers at the end of
> the group. this is actually quite easy to accomplish too.
> Sorry I can't be of more help Colin.
> Michael C.
> "Michael C" wrote:
> >
> > Okay, so now I do understand your want to do this in report. I'm a little
> > stumped at why the IIF won't work (Unless the report crosses pages?).
> >
> > I would suggest trying a CASE statement in your SQL to give both OIL and
> > WATER their own column at least to benchmark what the added overhead is.
> >
> > CASE WHEN p.PRODUCT = 'OIL' THEN p.LIQUID_VOL ELSE 0 END as OIL_VOL,
> > CASE WHEN p.PRODUCT = 'WATER' THEN p.LIQUID_VOL ELSE 0 END as WATER_VOL,
> >
> > If it is minimal overhead to do this then voila, you now have columns for
> > both oil and water (unless of course other products like NGL's and
> > Condensates are being included in your products list in which case you'll
> > need to expand the case statements).
> >
> >
> > Michael C.
> >
> >
> >
> >
> >
> >
> > "smithcjb" wrote:
> >
> > > Michael,
> > >
> > > I appreciate your comments. This is my full query. I'm returning 3 rows per
> > > date (1 per product). I've tried to use inner joins on the view I'm pulling
> > > from but performance is woeful for large date ranges. I'm trying to sum the
> > > production for the specified date range for each product - easy for GAS since
> > > it appears in it's own column! Any further help greatly appreciated. I don't
> > > know why the expressions you provided won;t workm - they seem logical to me!
> > >
> > > Kind regards,
> > > Colin
> > >
> > > SELECT c.ITEM_NAME AS COMPLETION_NAME, c.ITEM_ID AS COMPLETION_ITEM_ID,
> > > p.START_DATETIME, p.GAS_VOL, p.LIQUID_VOL,p.PRODUCT
> > > FROM dbo.VI_COMPLETION_en_US AS c INNER JOIN
> > > dbo.ITEM_LINK AS il ON c.ITEM_ID = il.FROM_ITEM_ID
> > > INNER JOIN
> > > dbo.VT_ACT_DAY_en_US AS p ON c.ITEM_ID = p.ITEM_ID
> > > WHERE (il.LINK_TYPE = 'NET_MEMBER') AND (p.START_DATETIME between
> > > '1/1/04' and '1/1/07') AND (c.ITEM_ID IN
> > > (SELECT COMPLETION_ITEM_ID
> > > FROM dbo.REP_ORG_COMPLETION
> > > WHERE (FIELD_ITEM_ID = @.FieldItemId)))
> > > GROUP BY c.ITEM_NAME, c.ITEM_ID, p.START_DATETIME, p.GAS_VOL,
> > > p.LIQUID_VOL,p.PRODUCT
> > >
> > > "Michael C" wrote:
> > >
> > > >
> > > >
> > > > Tables (also called matrix) exist INSIDE reports and have nothing to do with
> > > > Datasets. I fully understand what your saying, so no need to "reiterate"
> > > > anything.
> > > >
> > > >
> > > > Also, the comment "for performance reasons i want to do this in the report".
> > > > Are you saying you want the report to run slower? Performance wise , as far
> > > > as I've read, would suggest you do this in the SQL. But hey...its up to you.
> > > >
> > > > Michael C.
> > > >
> > > > "smithcjb" wrote:
> > > >
> > > > > Just to reiterate. I want to perform this in the report, not the query. I
> > > > > tried the conditional expressions you provided but the report just returns
> > > > > "Error" in the field
> > > > >
> > > > > "Michael C" wrote:
> > > > >
> > > > > > Hey Smithcjb,
> > > > > > If you are trying to do this in your SQL statement, then drop the "date"
> > > > > > (you can leave it in if your are filtering by it, just make sure you define
> > > > > > it as part of the WHERE clause only). Write it like this
> > > > > >
> > > > > > SELECT PRODUCT, SUM(VOLUME)
> > > > > > FROM MyTable
> > > > > > WHERE Date Between Date1 and Date2
> > > > > > GROUP BY PRODUCT
> > > > > >
> > > > > > If your trying to do this in a table cell or matrix you could use a
> > > > > > conditinal that says
> > > > > >
> > > > > > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > > > > > then again for water
> > > > > > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> > > > > >
> > > > > > and so on...
> > > > > >
> > > > > > Michael C
> > > > > >
> > > > > > "smithcjb" wrote:
> > > > > >
> > > > > > > I'm not having much fun trying to aggregate values based on the value of
> > > > > > > another field
> > > > > > >
> > > > > > > My query is returning the following...
> > > > > > > DATE, VOLUME, PRODUCT
> > > > > > >
> > > > > > > 1/1/07, 22, OIL
> > > > > > > 1/1/07, 0, WATER
> > > > > > > 1/2/07, 8, OIL
> > > > > > > 1/2/07, 12, WATER
> > > > > > >
> > > > > > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > > > > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > > > > > possible. If so how?|||Colin,
My pleasure, Im glad I could be of assistance.
Michael
"smithcjb" wrote:
> Michael,
> As it happens your SQL has been of great value. I've rebuilt the view and
> the CASE statements (which are new to me) - and seem to be doing the trick.
> Many thanks,
> Colin
> "Michael C" wrote:
> > Well,I hope i'm not being too much of a pain, but you could also use custom
> > code ( I realize my last answer is exactly what you DIDN"T want to do).
> >
> > you can create a function that fires on each detail setting static variables
> > over the group, then a second function that returns the answers at the end of
> > the group. this is actually quite easy to accomplish too.
> >
> > Sorry I can't be of more help Colin.
> >
> > Michael C.
> >
> > "Michael C" wrote:
> >
> > >
> > > Okay, so now I do understand your want to do this in report. I'm a little
> > > stumped at why the IIF won't work (Unless the report crosses pages?).
> > >
> > > I would suggest trying a CASE statement in your SQL to give both OIL and
> > > WATER their own column at least to benchmark what the added overhead is.
> > >
> > > CASE WHEN p.PRODUCT = 'OIL' THEN p.LIQUID_VOL ELSE 0 END as OIL_VOL,
> > > CASE WHEN p.PRODUCT = 'WATER' THEN p.LIQUID_VOL ELSE 0 END as WATER_VOL,
> > >
> > > If it is minimal overhead to do this then voila, you now have columns for
> > > both oil and water (unless of course other products like NGL's and
> > > Condensates are being included in your products list in which case you'll
> > > need to expand the case statements).
> > >
> > >
> > > Michael C.
> > >
> > >
> > >
> > >
> > >
> > >
> > > "smithcjb" wrote:
> > >
> > > > Michael,
> > > >
> > > > I appreciate your comments. This is my full query. I'm returning 3 rows per
> > > > date (1 per product). I've tried to use inner joins on the view I'm pulling
> > > > from but performance is woeful for large date ranges. I'm trying to sum the
> > > > production for the specified date range for each product - easy for GAS since
> > > > it appears in it's own column! Any further help greatly appreciated. I don't
> > > > know why the expressions you provided won;t workm - they seem logical to me!
> > > >
> > > > Kind regards,
> > > > Colin
> > > >
> > > > SELECT c.ITEM_NAME AS COMPLETION_NAME, c.ITEM_ID AS COMPLETION_ITEM_ID,
> > > > p.START_DATETIME, p.GAS_VOL, p.LIQUID_VOL,p.PRODUCT
> > > > FROM dbo.VI_COMPLETION_en_US AS c INNER JOIN
> > > > dbo.ITEM_LINK AS il ON c.ITEM_ID = il.FROM_ITEM_ID
> > > > INNER JOIN
> > > > dbo.VT_ACT_DAY_en_US AS p ON c.ITEM_ID = p.ITEM_ID
> > > > WHERE (il.LINK_TYPE = 'NET_MEMBER') AND (p.START_DATETIME between
> > > > '1/1/04' and '1/1/07') AND (c.ITEM_ID IN
> > > > (SELECT COMPLETION_ITEM_ID
> > > > FROM dbo.REP_ORG_COMPLETION
> > > > WHERE (FIELD_ITEM_ID = @.FieldItemId)))
> > > > GROUP BY c.ITEM_NAME, c.ITEM_ID, p.START_DATETIME, p.GAS_VOL,
> > > > p.LIQUID_VOL,p.PRODUCT
> > > >
> > > > "Michael C" wrote:
> > > >
> > > > >
> > > > >
> > > > > Tables (also called matrix) exist INSIDE reports and have nothing to do with
> > > > > Datasets. I fully understand what your saying, so no need to "reiterate"
> > > > > anything.
> > > > >
> > > > >
> > > > > Also, the comment "for performance reasons i want to do this in the report".
> > > > > Are you saying you want the report to run slower? Performance wise , as far
> > > > > as I've read, would suggest you do this in the SQL. But hey...its up to you.
> > > > >
> > > > > Michael C.
> > > > >
> > > > > "smithcjb" wrote:
> > > > >
> > > > > > Just to reiterate. I want to perform this in the report, not the query. I
> > > > > > tried the conditional expressions you provided but the report just returns
> > > > > > "Error" in the field
> > > > > >
> > > > > > "Michael C" wrote:
> > > > > >
> > > > > > > Hey Smithcjb,
> > > > > > > If you are trying to do this in your SQL statement, then drop the "date"
> > > > > > > (you can leave it in if your are filtering by it, just make sure you define
> > > > > > > it as part of the WHERE clause only). Write it like this
> > > > > > >
> > > > > > > SELECT PRODUCT, SUM(VOLUME)
> > > > > > > FROM MyTable
> > > > > > > WHERE Date Between Date1 and Date2
> > > > > > > GROUP BY PRODUCT
> > > > > > >
> > > > > > > If your trying to do this in a table cell or matrix you could use a
> > > > > > > conditinal that says
> > > > > > >
> > > > > > > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > > > > > > then again for water
> > > > > > > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> > > > > > >
> > > > > > > and so on...
> > > > > > >
> > > > > > > Michael C
> > > > > > >
> > > > > > > "smithcjb" wrote:
> > > > > > >
> > > > > > > > I'm not having much fun trying to aggregate values based on the value of
> > > > > > > > another field
> > > > > > > >
> > > > > > > > My query is returning the following...
> > > > > > > > DATE, VOLUME, PRODUCT
> > > > > > > >
> > > > > > > > 1/1/07, 22, OIL
> > > > > > > > 1/1/07, 0, WATER
> > > > > > > > 1/2/07, 8, OIL
> > > > > > > > 1/2/07, 12, WATER
> > > > > > > >
> > > > > > > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > > > > > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > > > > > > possible. If so how?