Alias question

The following is not working:
SELECT [Total Calls] / [Conversion Rate] AS [Customers], [Customers] *
[Customer Value] AS [Sales], [Sales] * [Profit Margin] AS Profit FROM [Table]
SQL doesn't recognize the aliased column names ([Customers] and [Sales] in
the above example) when I try to use them in later calculations. Is there a
way to do this without actually having to do all the calculations for each
successive column? I've got a lot more calculations to do than just the ones
I'm showing here, so I'd like to limit the amount of SQL code to sift throug
h
if at all possible.Your alternatives are views/derived tables or reusing the entire expression.
So you can have:
SELECT "Total Calls" / "Conversion Rate" AS "Customers",
( "Total Calls" / "Conversion Rate" )
* "Customer Value" AS "Sales",
( "Total Calls" / "Conversion Rate" )
* "Customer Value" * "Profit Margin" AS "Profit"
FROM Table ;
-- or
SELECT Customers,
Customers * Customer_value AS Sales,
Customers * Customer_value * Profit_margin AS profit
FROM (
SELECT "Total Calls" / "Conversion Rate",
"Customer Value", "Profit Margin"
FROM table
) Derived_tbl ( Customers, Customer_value, Profit_margin ) ;
Anith|||Hi,
You can not use alias for this. The approaches are:-
1. As you mentioned use the calculations for each columns
2. Declare variables and use the variables in select statement
Eg:-
Declare @.customers int,
@.Sales int,
@.profit int
SELECT @.Customers = [Total Calls] / [Conversion Rate] , @.Sales= @.Customers
*
[Customer Value] , @.Profit = @.Sales * [Profit Margin] FROM [Table]
Select @.customers,@.sales,@.Profit
Thanks
Hari
SQL Server MVP
"mike" <mike@.discussions.microsoft.com> wrote in message
news:F7718D7F-2203-44A3-B664-4FCD4E9CFACE@.microsoft.com...
> The following is not working:
> SELECT [Total Calls] / [Conversion Rate] AS [Customers], [Customers] *
> [Customer Value] AS [Sales], [Sales] * [Profit Margin] AS Profit FROM
> [Table]
> SQL doesn't recognize the aliased column names ([Customers] and [Sales] in
> the above example) when I try to use them in later calculations. Is there
> a
> way to do this without actually having to do all the calculations for each
> successive column? I've got a lot more calculations to do than just the
> ones
> I'm showing here, so I'd like to limit the amount of SQL code to sift
> through
> if at all possible.
>

alias in insert statement

I am trying to do insertion with alias
insert into TableName t (t.columnName) value (value)
it does not work, please give me any useful input on this.
Thanks in advance!
Sincerely
Why use the alias at all? It isn't needed. The INSERT INTO tablename
defines which table is impacted by the statement...I don't understand why
it would be helpful to alias the table name within the insert statement.
Keith
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:05EEA4BE-2351-49C2-923B-62DAC9391068@.microsoft.com...
> I am trying to do insertion with alias
> insert into TableName t (t.columnName) value (value)
> it does not work, please give me any useful input on this.
> Thanks in advance!
>
> --
> Sincerely
|||Since an INSERT statement can only insert to one table an alias isn't
necessary and isn't supported by this statement. If you need more help
please explain just what you are trying to achieve.
David Portas
SQL Server MVP

alias in insert statement

I am trying to do insertion with alias
insert into TableName t (t.columnName) value (value)
it does not work, please give me any useful input on this.
Thanks in advance!
SincerelyWhy use the alias at all? It isn't needed. The INSERT INTO tablename
defines which table is impacted by the statement...I don't understand why
it would be helpful to alias the table name within the insert statement.
Keith
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:05EEA4BE-2351-49C2-923B-62DAC9391068@.microsoft.com...
> I am trying to do insertion with alias
> insert into TableName t (t.columnName) value (value)
> it does not work, please give me any useful input on this.
> Thanks in advance!
>
> --
> Sincerely|||Since an INSERT statement can only insert to one table an alias isn't
necessary and isn't supported by this statement. If you need more help
please explain just what you are trying to achieve.
David Portas
SQL Server MVP
--

alias in insert statement

I am trying to do insertion with alias
insert into TableName t (t.columnName) value (value)
it does not work, please give me any useful input on this.
Thanks in advance!
--
SincerelyWhy use the alias at all? It isn't needed. The INSERT INTO tablename
defines which table is impacted by the statement...I don't understand why
it would be helpful to alias the table name within the insert statement.
--
Keith
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:05EEA4BE-2351-49C2-923B-62DAC9391068@.microsoft.com...
> I am trying to do insertion with alias
> insert into TableName t (t.columnName) value (value)
> it does not work, please give me any useful input on this.
> Thanks in advance!
>
> --
> Sincerely|||Since an INSERT statement can only insert to one table an alias isn't
necessary and isn't supported by this statement. If you need more help
please explain just what you are trying to achieve.
--
David Portas
SQL Server MVP
--

Algorithm to populate a table with finite value combinations

I need to populate a table which have 10 columns with four values. Each row should be a different combination of these four values and the columns can be null too. In other words how can I get all the different combinations for the 4 values that can be in 10 buckets. The final result column based on these values will be generated manually.

For example , I have for grades (P,F, WP, WF) and I have 8 terms and two exams. 8 terms and two exams can have any of the above four values. Based on these grades and terms and exams I need to generate a table which wil be used to determine the student final status Pass/Fail.

What will be the best way to do this and how is it possible. Is there a T-SQL or C# program for this.

If I need to submit this in another forum please let me know.

Do you just need some code which generates all the possible combinations? There are quite a few: 4 (scores) ^10 (exams) ~ 1M rows, without considering nulls. Do you plan to score them manually after?

You may want to rather develop some sort of scoring system (P =10,F=1, WP..., W...) and require a certain sum or average to pass

|||Yes. I am trying to get some code/algorithm to do this. The manual part will be assigning the Pass/Fail based on the combinations. Thanks.

Alert User of Invalid Parameter Entry

Does anyone know if there is a way to alert the user that the parameter
value is invalid prior to the report rendering?
One of the parameters is an account number '########'. The user is
accustomed to seeing it as '###-####-#' and may enter it in that
format. I've already put the usual '(With no dashes)' next to the
prompt. However, if they do put dashes, (or some other invalid format),
the report runs as normal and spits out a blank report.
I would like the user to be aware of invalid parameter entries, similar
to a web app, or windows form.
TIA.On Dec 21, 11:06 am, Michael <Mich...@.discussions.microsoft.com>
wrote:
> Does anyone know if there is a way to alert the user that the parameter
> value is invalid prior to the report rendering?
> One of the parameters is an account number '########'. The user is
> accustomed to seeing it as '###-####-#' and may enter it in that
> format. I've already put the usual '(With no dashes)' next to the
> prompt. However, if they do put dashes, (or some other invalid format),
> the report runs as normal and spits out a blank report.
> I would like the user to be aware of invalid parameter entries, similar
> to a web app, or windows form.
> TIA.
There are a couple different things you can do. Either you can add a
textbox control to the report and set it to an expression like:
=iif(InStr(Parameters!AcctNumber.Value, "-") > 0, "Invalid Account
Number", Nothing)
-or-
You can do a replace on the Acct Number parameter entered by the user
via an expression in your dataset. An expression like this might help.
=Replace(Parameters!AcctNumber.Value, "-", "")
and you can embed them in each other to catch other stray characters
(i.e., remove all hyphens and asterisks)
=Replace(Replace(Parameters!AcctNumber.Value, "-", ""), "*", "")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Ajax Toggle Within a Gridview

Ive got a table of items which holds the privacy settings for each user. The items can either be the value 1 = Yes ,or 0 = No. Is it possible to bind these two options to a checkbox? I tryed to simply bind it to the checkboxes "checked" property, But it errored. Does anyone know how to bind an int feild to a checkbox? cos in the long run I want to add ajax toggle items to the checkboxes, but i was also wondering why that errored, but i think its cos i did my binding wrong. thanks si!

I've had this problem in the past. When using a boundfield checkbox, the type conversion fails converting from bool to int, if I remember right. If you change it to a template field, it should work though. I think this has to do with the implementation of checkboxfield.

|||

ill give it a go and get back to you! *fingers crossed* thanks si!

Aggregete() function doesn't work for SQL query results!

Reporting Services translates null value into blank on SQL query results, but not on MDX query results. But Aggregate() functions is triggered only by null value.

So Aggregate() function only works for MDX query results, not for SQL query results.

MDX example:

select {[Measures].[Sales]} on columns,

{[Account].[Hierarchy].Members} on rows

FROM Cube

SQL example:

SELECT * FROM OPENQUERY(Linked_Cube, '
select {[Measures].[Sales]} on columns,

{[Account].[Hierarchy].Members} on rows

FROM Cube')

Now you build a report with a table, then add a grouping and use "=Aggregate(Fields!Sales.Value)" for the group level cell. If you bind MDX query to this table, then aggregates show up correctly. But if you bind SQL query to this table, there are no aggregates at all.

I need to use SQL query to drive my reports, because MDX query results need to be merged with results from other calculations.

How can I make Aggregate() function to work for SQL query results?

Thanks,

Bo Dong

bo_dong@.yahoo.com

How can I make it to work for SQL as well?

Please read my answer on your other thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=668524&SiteID=1

-- Robert

Aggregation problem

Hi

We have a fact table contains 5 dimension keys and one measure.The measure is having value as 1for all records and it will always be 1.Total records in this fact table are 2316.

we processed cube successfully.

Problem : While viewing data in cube browser the measure count is aggregating.We want all the records for the measure is 1. But its showing aggregate values as 1, 2 6..

Thanks

karumuru

Try connecting from Excel or some other tools you have, to the cube.

Also, If you are using the default measure " Fact Count", it will aggregate, as it is a Count.

You can specify another measure and fill it with value 1 (this is just for testing, this is not a good practice), and specify the aggregateFunction property of the measure to DistinctCount.

(I hope you are aware of the Factless fact table concept)

Regards,

Jiju

|||

Jiju

yes, Our one measure is a factless fact and had value 1. This measure aggregation property we set to distinct count and count tried all. but its aggregating and we could not get 2316 rows

Thanks

Sridhar K

Aggregation of Calculated members of form: MEASURE op ATTRIBUTE VALUE

hi there. i do not understand how to use an attribute in a calculation. In the query below, I'm trying to say:

for all members of the date.calendar hierarchy, show me the sum of (sales amount fact * dealer price attribute for the product sold)

with

member [measures].[fact times attribute]

as cdbl([Measures].[Sales Amount]) * cdbl([Product].[Dealer Price].CurrentMember.memberValue)

select [measures].[fact times attribute] oncolumns,

[Date].[Calendar].allmembersonrows

from [Adventure Works]

where [Geography].[City].&[Beaverton]&[OR]

this gives a type mismatch error complaining that "All Products" cannot be cast to double. But I don't understand how to force the calculation to occur at the lowest level and for aggregation to be done subsequently.

Product is currently at the "All Product" member. What you are actually wanting to do is get the price for each product and multiply that by the sales of that product and then aggregate (i'll assume sum) those values. That query looks like the one below. (Actually, the one below is kinduva shortcut to the solution. Instead of going to the individual product, I just went to the product price. If two products have the same price, the query kinda says that we treat them the same. Nit-picky, I know.)

Good luck.

Code Snippet

withmember [measures].[fact times attribute] as

SUM([Product].[Dealer Price].[Dealer Price].Members,

[Measures].[Sales Amount])

select [measures].[fact times attribute] oncolumns,

NONEMPTY [Date].[Calendar].allmembersonrows

from [Adventure Works]

where ([Geography].[City].&[Beaverton]&[OR])

;

|||Dear Bryan,

Thanks for your pointers. I actually work in hong kong so were ~12 hours ahead of you. I'm checking this from home and so can't test it. but i'm reviewing it now, because I'd love to get this right for work tomorrow.

Looking at your solution, one thing I find very conceptually troubling:

>>I don't see a multiplication sign anywhere!!!!<<

So at the very point where I would imagine attribute and fact really connect, the code is silent! Also when you refer to a shortcut and being nitpicky, that is all just going straight over my head - my knowledge of mdx (rather like my knowledge of cantonese just isn't evolved enough to understand the subtleties here. I can just about order lunch with a lot of gesticulation but that's it.

i will of course kick the tires on this tomorrow when I get into work. But [embarrassingly] I've been trying to figure out what the missing link is here for almost 3 days without success! Would you mind just leaving a working "best practice" solution for me? I feel like I sound like a lazy person - I'm not, I just don't grock this yet and its driving me nuts.

Yours,

John G.
|||

Sorry about that. I was pushing to get the code assembled and totally dropped that one critical part of the code. Try this one. I use the CurrentMember of the Dealer Price to get to the value.

Code Snippet

withmember [measures].[fact times attribute] as

SUM(

[Product].[Dealer Price].[Dealer Price].Members,

[Measures].[Sales Amount]*[Product].[Dealer Price].CurrentMember.MemberValue

)

select [measures].[fact times attribute] on columns,

NON EMPTY [Date].[Calendar].allmembers on rows

from [Adventure Works]

where ([Geography].[City].&[Beaverton]&[OR])

;

|||

Jo,

Try to use named calculations in the datasourceview of your factTable... it would be a better performance, because you only run the formulas once when the cube is processing.

Regards!

|||

ok it works fine - THANKS!

I also tried this which returns the same results - is it logically identical?

with

member [measures].[fact times attribute]

asSUM([Product].[Product].[Product].Members,

[Measures].[Sales Amount]*[Product].[Dealer Price].CurrentMember.MemberValue

)

select {[measures].[fact times attribute]} oncolumns,

NONEMPTY [Date].[Calendar].allmembersonrows

from [Adventure Works]

where ([Geography].[City].&[Beaverton]&[OR])

i'm not sure if you'd have the patience of a saint to wade through what follows, but I'm trying to understand the execution of the query. Would you mind critiquing the below, or if it's easier for you just describe the above query in pseudo code for dummies

For my general understanding when you refer to [Product].[Dealer Price].CurrentMember.MemberValue,

what is the context of the CurrentMember? Is it:

1. The current member in the context of traversing the set of members of the product hierarchy as defined by the set argument passed to the SUM function.

or 2. the currentmember in some wider context of the query (actually I'm not sure if that makes sense, so I'll go with answer 1).

Assuming 1. above, then I would interpret the query as follows:

1. slice by beaverton

2. iterate through all members of the date hierarchy.

3. for each sales fact within the intersection of beaverton and the current date, calculate [fact times attribute] as follows:

a) for each member of the product hierarchy ...

i) ... extract the tuple set defined by the intersection of that product within the current region [for many product members this will be a null set]

ii) for that set of tuples, sum up (sales amount * dealer price).

iii) keep running total of that sum, and the final total will be your result of the current date member.

|||

thanks - this did occur to me, since as i understand it calculated members (even cube scoped ones) are calculated at runtime not at processing time, correct?

however, in my real calculation I'm using another calculated member which doesn't exist in the underlying table so this makes it tricky.

Would you say that in general, one should strive to do as much data cleaning / preparation / precalculation as possible outside the cube, and then just leave the cube to prepare aggregations and pure analysis calculations that are hard to do outside of mdx?

|||Yeah joGo, I'm with you! If you have other calculated members that you cannot replace for named calculation, so you are right...|||

Your code is logically similar and should produce the same result. You may want to test for performance differences, but the only way I can imagine a performance difference would exist would be under a very particular situation I suspect does not exist in the cube. (In other words, they probably have the same performance so don't sweat it.)

Regarding the CURRENTMEMBER question, you have to keep in mind context at all times. In the SUM function, you generate a set. That set definition is in the context of the cube as a whole. If I want to limit that context based on my slicer (WHERE clause), I can use the EXISTING keyword in the set definition.

So, now I have a set. Then, for each member in that set, I will return and/or calculate a value. That value is determined in the context of the member from the set I am currently working with. At this point, how that set came to be is unknown to me. The set has been determined and I'm just working through it blindly. I think this is what you're saying in the section at the bottom of your email.

Sorry I am being slow to respond today. The MSDN forum email seems to be jammed up a bit and I'm not getting alerts like I should.

Thanks,
Bryan

aggregates within aggregates

Here is the code I have:

=Sum(IIF(sum( Fields!Total_Amount.Value, "Collat_Acct_Group2") < 0, 1,0))

I am trying to do a count but only if the sum of a value is greater/less than 0.

The error I get is:

The value expression for the textbox 'textbox146' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.

Please help .

Thanks

Elias

I guess you want to count the number of groups which have sum of total amount > 0.

In the code (Report -> Report Properties -> Code tab), have a shared variable called count and initialize it to 0. Then have a function called IncrementCount which when called will increment count by 1.

In your Collat_Acct_Group2 header or footer, append the following expression to the any of the textboxes:

[old expression] & IIf(Sum(Fields!Total_Amount.Value, "Collat_Acct_Group2") > 0, Code.IncrementCount(), "")

and use "Code.count" in any field expression to get the count of all groups with sum of total amount > 0

Shyam

|||

Shyam,

Thanks, for the answer, but I have never written any code in RS if it would not be too much trouble could you please show me an example.

Thanks again,

Elias

|||

Sorry for the delay in my response

Your code will look something like this in VB.Net:

Private Shared count as Integer

count=0

Public Function IncrementCount() As String

count = count +1

IncrementCount = CStr(count)

End Function

Say one of the textboxes (or a column) in your table header has the following expression:

Fields!SomeField.Value

Now change the expression to:

Fields!SomeField.Value & IIf(Sum(Fields!Total_Amount.Value, "Collat_Acct_Group2") > 0, Code.IncrementCount(), "")

Then use Code.count in any of your field expressions to get the number of groups with total amount > 0.

Shyam

aggregates and type conversion

Hello,
I have a field of type string which I know will either hold a double value or null. I wish to sum the value of this field and mulitply the result by -1 to give a negative number, but it's proving really difficult.
My first problem is that if I modify my field to use CDbl I get an error when it runs, along the lines of "Field contains a direct or indirect reference to itself..."
So I have to get around it by creating a separate field with a different name, like
myFieldDbl and setting it's formula to = Cdbl(Fields!MyField.Value).
Why is that then ?
My next problem is that when I multiply by -1
e.g. =Sum(Fields!MyFieldDbl.Value * -1),
it gives me this error :
"The value expression for the textbox â'textbox19â' uses an aggregate function on data of varying data types".
To get around this I have to multiply by -1.0 instead ! As if there's a difference !!!
Type conversion in Reporting services is diabolical. I can't even use the format property unless I explicitly declare my field to be a particluar type first, even though I could happily use format$(myField,"C") in the Value property with any data type.
Can I expect MS might have a look at thses issues in time for the next service pack?Note: It is up to the data provider (e.g. managed SQL provider) how it
translates database types into .NET datatypes. Generally, you can determine
the runtime datatype of fields by temporarily adding a textbox to the report
which shows the runtime datatype:
=First(Fields!SomeFieldName.Value, "DataSetName").GetType.ToString
I assume that your field is actually a System.Decimal rather than a
System.Double as you indicated. An expression like this should work for you:
= -1 * Sum( iif(IsNothing(Fields!MyFieldDbl.Value), 0.0,
CDbl(Fields!MyFieldDbl.Value)))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike Salway" <MikeSalway@.discussions.microsoft.com> wrote in message
news:B90D6631-A0D2-4F53-9C69-A06366ADA6C7@.microsoft.com...
> Hello,
> I have a field of type string which I know will either hold a double value
or null. I wish to sum the value of this field and mulitply the result
by -1 to give a negative number, but it's proving really difficult.
> My first problem is that if I modify my field to use CDbl I get an error
when it runs, along the lines of "Field contains a direct or indirect
reference to itself..."
> So I have to get around it by creating a separate field with a different
name, like
> myFieldDbl and setting it's formula to = Cdbl(Fields!MyField.Value).
> Why is that then ?
> My next problem is that when I multiply by -1
> e.g. =Sum(Fields!MyFieldDbl.Value * -1),
> it gives me this error :
> "The value expression for the textbox 'textbox19' uses an aggregate
function on data of varying data types".
> To get around this I have to multiply by -1.0 instead ! As if there's a
difference !!!
> Type conversion in Reporting services is diabolical. I can't even use the
format property unless I explicitly declare my field to be a particluar type
first, even though I could happily use format$(myField,"C") in the Value
property with any data type.
> Can I expect MS might have a look at thses issues in time for the next
service pack?

Aggregate where claus problem =/

Hi Guys,

Is it possible to have a where clause (or other method) where you only select the max value from this: SUM(ORDER_ITEM.ItemQuantity) and only output that 1 row... or even perhaps a range of rows... in others words... find the ItemID with the greatest combined Quantity

Heres the query so far:

SELECT ORDER_ITEM.ItemID, SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID

Thx for reading :-)

--PhilkillsTry this:
SELECT itemid, max(qsum)
FROM
(
SELECT ORDER_ITEM.ItemID, SUM(ORDER_ITEM.ItemQuantity) AS qsum
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
)
group by itemid|||Shammat, I don't think that will work. The outer query will return the same multi-record dataset as the inner query.

Try this:
SELECT TOP 1
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||Shammat, I don't think that will work. The outer query will return the same multi-record dataset as the inner query.

Try this:
SELECT TOP 1
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

ah thx man perfect ^^

Although, is it not inefficient to have 2 sums for the same thing? or will SQL realise that their the same thing and count them as such..

Also would be it possible to change the query to select all Total's that are greater than say... 10..

like:

SELECT
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
WHERE SUM(ORDER_ITEM.ItemQuantity) > 10
GROUP BY ORDER_ITEM.ItemID
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

although this doesn't work as it doesn't like that in the where clause =/|||Doing multiple aggregate is not expensive. Doing multiple data-scans is what costs query time.

To filter on an aggregate value you must use the HAVING clause:
SELECT
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
HAVING SUM(ORDER_ITEM.ItemQuantity) > 10
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||Doing multiple aggregate is not expensive. Doing multiple data-scans is what costs query time.

To filter on an aggregate value you must use the HAVING clause:
SELECT
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
HAVING SUM(ORDER_ITEM.ItemQuantity) > 10
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

thx mate, lol seems kinda pointless "having" an extra keyword just to perform those extra operations ;p (that is instead of where)

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?

Aggregate string concatenation

I have the following data:

create table TempTable(name varchar(50), value varchar(50))
insert into temptable values ('A', 'one')
insert into temptable values ('A', 'two')
insert into temptable values ('A', 'three')
insert into temptable values ('B', 'four')
insert into temptable values ('B', 'five')

and i would like the following output:

'A', 'one, two, three'
'B', 'four, five'

any ideas on how to accomplish this in Sql Server 2000?

thx in advance..Never mind, i figured it out:

CREATE FUNCTION dbo.TempFunction (@.Name as varchar(50))
RETURNS varchar(1000)
AS
BEGIN
DECLARE @.RetVal varchar(1000)
SELECT @.RetVal = ''
SELECT @.RetVal=@.RetVal + value + ', '
FROM temptable
WHERE name=@.name
select @.RetVal = left(@.RetVal, len(@.RetVal)-1)
RETURN (@.RetVal)
END

SELECT name, dbo.tempfunction(name) as [values]
FROM temptable
GROUP BY name;

Aggregate returns null - sub value

I have something like the following:
select count(mycolumn) from mytable where...
This works great. The problem is that sometimes, count(mycolumn) returns
null because mycolumn contains no values. In this case, I'd really rather
show the user a '0' rather than the word null.
Any suggestions on how to show the user a 0 (zero) when count returns a null
?
Thanks
--
RandyTry:
select isnull (count(mycolumn), 0)
from mytable where...
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"randy1200" <randy1200@.newsgroups.nospam> wrote in message
news:E73AE804-52AF-4C79-ACEE-59385838204D@.microsoft.com...
I have something like the following:
select count(mycolumn) from mytable where...
This works great. The problem is that sometimes, count(mycolumn) returns
null because mycolumn contains no values. In this case, I'd really rather
show the user a '0' rather than the word null.
Any suggestions on how to show the user a 0 (zero) when count returns a
null?
Thanks
--
Randy|||Try,
select isnull(count(mycolumn), 0) from mytable where ...
go
AMB
"randy1200" wrote:

> I have something like the following:
> select count(mycolumn) from mytable where...
> This works great. The problem is that sometimes, count(mycolumn) returns
> null because mycolumn contains no values. In this case, I'd really rather
> show the user a '0' rather than the word null.
> Any suggestions on how to show the user a 0 (zero) when count returns a nu
ll?
> Thanks
> --
> Randy|||Exactly what I needed. Many thanks
--
Randy
"Tom Moreau" wrote:

> Try:
> select isnull (count(mycolumn), 0)
> from mytable where...
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "randy1200" <randy1200@.newsgroups.nospam> wrote in message
> news:E73AE804-52AF-4C79-ACEE-59385838204D@.microsoft.com...
> I have something like the following:
> select count(mycolumn) from mytable where...
> This works great. The problem is that sometimes, count(mycolumn) returns
> null because mycolumn contains no values. In this case, I'd really rather
> show the user a '0' rather than the word null.
> Any suggestions on how to show the user a 0 (zero) when count returns a
> null?
> Thanks
> --
> Randy
>|||select count(mycolumn) from mytable where mycolumn is not null
?
"randy1200" <randy1200@.newsgroups.nospam> wrote in message
news:E73AE804-52AF-4C79-ACEE-59385838204D@.microsoft.com...
> I have something like the following:
> select count(mycolumn) from mytable where...
> This works great. The problem is that sometimes, count(mycolumn) returns
> null because mycolumn contains no values. In this case, I'd really rather
> show the user a '0' rather than the word null.
> Any suggestions on how to show the user a 0 (zero) when count returns a
null?
> Thanks
> --
> Randy

aggregate functions vs. non-numeric data

RS doesn't like this expression that's supposed to total up all the debit values in my ledger:

=RunningValue( iif(Fields!Amount.Value < 1, 0, Fields!Amount.Value), Sum, Nothing)

I'm getting this error :

The value expression for the textbox ‘APTotal’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

I take issue with its saying my data is not numeric. The matching field in the database is of type money.

Any thoughts on what i'm doing wrong?

Ian Pert
CMS Software, Business Integenct UnitOh, got it. Problem wasn't my Fields!Amount.Value, but simply that 0 wasn't the same datatype. Replaced 0 with Nothing, like so

=RunningValue( iif(Fields!Amount.Value < 1, Nothing, Fields!Amount.Value), Sum, Nothing)

And it worked like a charm.

Aggregate functions not allowed in the dataset filter

Hi,

I was trying to filter a dataset based on a condition like this: Fields!SalesAmt.value <= Sum(Fields!SalesAmt.Value)*0.05. This is nothing but it filters those SalesAmt that are less than 5% of the total sales amount. However SSRS doesn't allow to use aggregate functions in the dataset and data region filter.

Is there another way to do this?

Sincerely,

--Amde

You're trying to hide the rows correct? if so then try this

=IIF(Fields!SalesAmt.value

<= Sum(Fields!SalesAmt.Value)*0.05, true,false)
true meaning that if it is true then hid the row.

Hook this into a parameter and you have a dynamically filterable

report.

-Fap

|||Sorrt about the font size.

Here it is again.

You're trying to hide the rows correct? if so then try this

=IIF(Fields!SalesAmt.value

<= Sum(Fields!SalesAmt.Value)*0.05, true,false)
true meaning that if it is true then hid the row.

Hook this into a parameter and you have a dynamically filterable

report.

-Fap

|||

Dear Fap,

That was not my question. I just want to filter the data based on the condition I specified earlier. Please read my question carefully and let me know if you have any idea.

Sincerely,

--Amde

|||Aggregates are not supported in filter expressions (as you have seen), but there are few ways that you can work around this. Here are the first two that come to mind.

1. Filter the data returned directly in the SQL query. This can make the query more complicated. See http://msdn2.microsoft.com/en-us/library/ms179270.aspx for more information on filtering rows.

2. Use a hidden report parameter that has its default value populated by a second dataset whose query just computes and returns the result of the Aggregate. Then use the hidden report parameter in the filter expression where you were using the aggregate.

Sample Query:
SELECT Sum(SalesAmt)*.05 AS AggregateResult FROM SalesTable

Filter Expression:
Fields!SalesAmt.Value <= Parameters!AggregateResult.Value

Ian|||

Hi,

What you have said make sense, but, the thing is I am using MDX query instead of T-Sql. The link that you sent to me is helpful. It would be more helpful if you can send me similar link that uses mdx query.

Sincerely,

--Amde

|||I'm not an expert using MDX, but one way to filter the data would be to create and add another calculated member to the query, and then filter based on the result of that member. For example, create another member that will calculate whether or not the the row should be included.

The new member calculation would look something like

WITH MEMBER [Measures].[Include] AS ([Measures].[SalesAmt] <= Sum([SalesData].[SetOfAllRow], [Measures].[SalesAmt])*0.05)

Make sure to add [Measures].[Include] to the SELECT set. Then you can use this field in the data region's filter expressions. The filter expression would look something like

Fields!Include.Value = True

Or, you can use the Filter function in the query to retrive only a subset of the data. Here is more information on filtering data using MDX queries.

Filter Function:
http://msdn2.microsoft.com/en-us/library/ms146037.aspx

Slicer Axes:
http://msdn2.microsoft.com/en-us/library/ms146047.aspx

Aggregate Function on TextBoxes - a big "NO-NO"...

Ok...so we can't do an aggregate function on numerical values stored in
textboxes...but is there some other way to access the value(s) in the
textbox and use them in a calculation?
=Sum(ReportItems!textbox1.Value) is an invalid expression - but is there
another workaround?
-KB"Kevin B" <No-SPAM@.misnet.info> wrote in message news:<uoXYuR8gEHA.4092@.TK2MSFTNGP10.phx.gbl>...
> Ok...so we can't do an aggregate function on numerical values stored in
> textboxes...but is there some other way to access the value(s) in the
> textbox and use them in a calculation?
> =Sum(ReportItems!textbox1.Value) is an invalid expression - but is there
> another workaround?
> -KB
KB
I've been using Aggregate fuctions quite a bit in the last several
months. Just be sure to specify a scope in your function (i.e. a
dataset or group, etc.)
=Sum(ReportItems!textbox1.Value, "Group1")
Hope this helps,
MN

age into age band

Hi,

I have a table of members including date of birth value.

I am not finding any easy way to count the number of members per age band.
0-10, 10-20,20-30 etc.

It seems that the decode function works only for equality tests not for other complex conditions.

Until now, I am using as many count queries as age bands and look whether the date of birth of the member falls between two dates

Any thoughts?

Thanks

Edwardwhat i use is

select dob, floor(months_between(sysdate,dob)/120) ag from person;

Bill