SQL tool for MSDE: following

Showing posts with label following. Show all posts

Tuesday, March 27, 2012

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 naming

Hi,
I want help regarding the following scenario...
My scenario is ...
I want to give description for each field somewhere in the database..
such that i should use that description as alias name in the SQL queries or Stored procedures...
For example...
Table : 'Table1'

Clumns Description

Field1 xxx
Field2 yyy

Query : Select Field1 as xxx , Field2 as yyy from Table1.

Mr requirement...
1.I want to specify tag or description for each field
2. if i change the description 'xxx' as 'x1x1x1',it should be automatically updated in the Query,Views,Storedproc.. wherever the table and the fields are referred.

can any one help me?.If you are storing alias name for columns,
you have to use dynamic sql, to get the results.

I think, your requirement is so complicated to implement. :D
What advantages you will get, if you implement it..? :rolleyes:

Regards,
Selva Balaji. B

Originally posted by durgadevi_n
Hi,
I want help regarding the following scenario...
My scenario is ...
I want to give description for each field somewhere in the database..
such that i should use that description as alias name in the SQL queries or Stored procedures...
For example...
Table : 'Table1'

Clumns Description

Field1 xxx
Field2 yyy

Query : Select Field1 as xxx , Field2 as yyy from Table1.

Mr requirement...
1.I want to specify tag or description for each field
2. if i change the description 'xxx' as 'x1x1x1',it should be automatically updated in the Query,Views,Storedproc.. wherever the table and the fields are referred.

can any one help me?. :D :D :D|||There isn't such a functionality within the database. Using the standard tools like Enterprise Manager or Query Analyzer, you have to use the physical names, and to assign the logical names every time again.

You may consider to make a view for each table assigning your logical names.

Client tools, however, can replace the physical names by logical ones in the user interface. Look, for example, the DB Explorer (http://www.DB-Explorer.com).|||Hi,
Actually in my application...
I am creating more stored procedures based on a single table...(database already used by another application).
Since i can't change the field names, i am making use of alias name for the required fields in Stored procedures and binding tha data in the front end where the alias name gets displayed.
I've more than 500 stored procedures in my database.
If I want to change a field caption ,
I cannot change the existing field's name since it is already used by other application.
Also It is very hard to find out and change the alias name in each and every stored procedure wherever it is referenced.

So i am trying to look in other chances...to reflect the change in every stored procedure with a single move.

Is it possible?...
plz help me...
bye
by
durga|||And what about using views? You may consider not to assign your alias within every stored proc, but once in a view definition. If you change aliases in a view, your stored proc will return the changed name, assuming that you are working with SELECT * statements. This is consistent for all stored proc based on a particular view.|||You may want to have a look at extended properties
(sp_addextendedproperty, sp_updateextendedproperty, sp_dropextendedproperty).

It will not let you use the alias'es directly, but you do not have to come up with tables/functions etc to utilize them.

To use them you must (by code if you can, use syscomments or sqldmo) regenerate alll ddl's wher ethey are referenced (sysdepends, sysreferences).

not a small task...

Originally posted by durgadevi_n
Hi,
I want help regarding the following scenario...
My scenario is ...
I want to give description for each field somewhere in the database..
such that i should use that description as alias name in the SQL queries or Stored procedures...
For example...
Table : 'Table1'

Clumns Description

Field1 xxx
Field2 yyy

Query : Select Field1 as xxx , Field2 as yyy from Table1.

Mr requirement...
1.I want to specify tag or description for each field
2. if i change the description 'xxx' as 'x1x1x1',it should be automatically updated in the Query,Views,Storedproc.. wherever the table and the fields are referred.

can any one help me?.

Thursday, March 22, 2012

Alert on deadlock

I have created an SQL Agent Alert which should notify me whenever
a deadlock is occured on sql server. The following are the details:

Type : Performance Condition Alert
Object : SQLServerLocks
Counter : Number of deadlocks/sec
Instance: Database
Alert : If counter value rises above 0

I got deadlock situation a couple of times on server but i have never received any notification. It seems this settings doenot work. Pls. advise!

Thanks

Is the SQL Agent definitely running ?
If so, "Test" that that the SQL Agent can create a mail session. Right click SQL Server Agent, choose Properties, click the test button.

|||yes. Nothing wrong with sql agent. as such i get other alerts but not this one.|||Is the occurrence count still zero ?
Under the Response tab try reducing the "delay between responses". I recall reading somewhere that if at the time SQL samples the counters there may well not be a deadlock. i.e. the delay between responses is sufficiently high for the condition to disappear before SQL samples the data.

Tuesday, March 20, 2012

Alert for DeadLock

I am trying to create an alert to monitor deadlocks with following
specification.
Name = "Deadlock(s) detected . . ."
Type = "SQL Server performance condition alert.
Object = "SQL Server:Locks"
Counter = "Number of Deadlocks/sec"
Instance = "Database"
Alert if counter = "becomes equal to"
Value = "1"
Once the alert is created, I execute the deadlock script. But found that the
counter in alert is not increasing.
Can someone put some light on this? Am I missing something?why dont you just use SQL servers Deadlock monitoring?
Check out trace flags 1204, 1205
Immy
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:0849692C-24D2-4424-BA4D-A49F0BF22F6C@.microsoft.com...
>I am trying to create an alert to monitor deadlocks with following
> specification.
> Name = "Deadlock(s) detected . . ."
> Type = "SQL Server performance condition alert.
> Object = "SQL Server:Locks"
> Counter = "Number of Deadlocks/sec"
> Instance = "Database"
> Alert if counter = "becomes equal to"
> Value = "1"
> Once the alert is created, I execute the deadlock script. But found that
> the
> counter in alert is not increasing.
> Can someone put some light on this? Am I missing something?|||I want a NOTIFICATION when ever dead lock happens.|||I would still use the inbuilt function from MS!
If you want to continue with your own method, you'll need to post your DDL
and SQL statements so we can offer some advice.
Immy
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:75F4E5FD-0B99-47E6-90A3-EA887A4FD9FB@.microsoft.com...
>I want a NOTIFICATION when ever dead lock happens.|||>> I would still use the inbuilt function from MS!
How this works?|||See my previous post re: trace flags! :-)
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:C5CB693A-CB0E-4E43-B743-0C51115361D8@.microsoft.com...
>> I would still use the inbuilt function from MS!
> How this works?
>
>sql

Alert doesn't start

Hi all,
I have the following problem. I need to start a tarce in a job step. I
created the trace and it works properly. I scripted it and ran it in Query
Analyzer. Although the status displayed by SELECT * FROM ::
fn_trace_getinfo(default) says the trace is started (status=1), nothing
happens. What can be the problem?
Thanks in advance.
Sincerely,
Kolos
What do you mean, when you say nothing happens? Are you trying to open the
output trace file when the trace is running? Then it may not work.
You have to stop the trace, to open the file to which trace is currently
logging.
You have to use sp_trace_setstatus to stop and close the trace, before
opening the output file.
You may find my following article useful:
http://vyaskn.tripod.com/server_side...sql_server.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Kolos" <Kolos@.discussions.microsoft.com> wrote in message
news:60E1CCF1-AF58-46FA-BB1F-B58C10C3115E@.microsoft.com...
Hi all,
I have the following problem. I need to start a tarce in a job step. I
created the trace and it works properly. I scripted it and ran it in Query
Analyzer. Although the status displayed by SELECT * FROM ::
fn_trace_getinfo(default) says the trace is started (status=1), nothing
happens. What can be the problem?
Thanks in advance.
Sincerely,
Kolos
|||Well, thanks. The size of the file was 0 KB until the trace was stopped. I
feel a bit stupid... Thanks, anyway.
"Narayana Vyas Kondreddi" wrote:

> What do you mean, when you say nothing happens? Are you trying to open the
> output trace file when the trace is running? Then it may not work.
> You have to stop the trace, to open the file to which trace is currently
> logging.
> You have to use sp_trace_setstatus to stop and close the trace, before
> opening the output file.
> You may find my following article useful:
> http://vyaskn.tripod.com/server_side...sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Kolos" <Kolos@.discussions.microsoft.com> wrote in message
> news:60E1CCF1-AF58-46FA-BB1F-B58C10C3115E@.microsoft.com...
> Hi all,
> I have the following problem. I need to start a tarce in a job step. I
> created the trace and it works properly. I scripted it and ran it in Query
> Analyzer. Although the status displayed by SELECT * FROM ::
> fn_trace_getinfo(default) says the trace is started (status=1), nothing
> happens. What can be the problem?
> Thanks in advance.
> Sincerely,
> Kolos
>
>

Alert doesn't start

Hi all,
I have the following problem. I need to start a tarce in a job step. I
created the trace and it works properly. I scripted it and ran it in Query
Analyzer. Although the status displayed by SELECT * FROM ::
fn_trace_getinfo(default) says the trace is started (status=1), nothing
happens. What can be the problem?
Thanks in advance.
Sincerely,
KolosWhat do you mean, when you say nothing happens? Are you trying to open the
output trace file when the trace is running? Then it may not work.
You have to stop the trace, to open the file to which trace is currently
logging.
You have to use sp_trace_setstatus to stop and close the trace, before
opening the output file.
You may find my following article useful:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Kolos" <Kolos@.discussions.microsoft.com> wrote in message
news:60E1CCF1-AF58-46FA-BB1F-B58C10C3115E@.microsoft.com...
Hi all,
I have the following problem. I need to start a tarce in a job step. I
created the trace and it works properly. I scripted it and ran it in Query
Analyzer. Although the status displayed by SELECT * FROM ::
fn_trace_getinfo(default) says the trace is started (status=1), nothing
happens. What can be the problem?
Thanks in advance.
Sincerely,
Kolos|||Well, thanks. The size of the file was 0 KB until the trace was stopped. I
feel a bit stupid... Thanks, anyway.
"Narayana Vyas Kondreddi" wrote:
> What do you mean, when you say nothing happens? Are you trying to open the
> output trace file when the trace is running? Then it may not work.
> You have to stop the trace, to open the file to which trace is currently
> logging.
> You have to use sp_trace_setstatus to stop and close the trace, before
> opening the output file.
> You may find my following article useful:
> http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Kolos" <Kolos@.discussions.microsoft.com> wrote in message
> news:60E1CCF1-AF58-46FA-BB1F-B58C10C3115E@.microsoft.com...
> Hi all,
> I have the following problem. I need to start a tarce in a job step. I
> created the trace and it works properly. I scripted it and ran it in Query
> Analyzer. Although the status displayed by SELECT * FROM ::
> fn_trace_getinfo(default) says the trace is started (status=1), nothing
> happens. What can be the problem?
> Thanks in advance.
> Sincerely,
> Kolos
>
>

Alert doesn't start

Hi all,
I have the following problem. I need to start a tarce in a job step. I
created the trace and it works properly. I scripted it and ran it in Query
Analyzer. Although the status displayed by SELECT * FROM ::
fn_trace_getinfo(default) says the trace is started (status=1), nothing
happens. What can be the problem?
Thanks in advance.
Sincerely,
KolosWhat do you mean, when you say nothing happens? Are you trying to open the
output trace file when the trace is running? Then it may not work.
You have to stop the trace, to open the file to which trace is currently
logging.
You have to use sp_trace_setstatus to stop and close the trace, before
opening the output file.
You may find my following article useful:
http://vyaskn.tripod.com/server_sid..._sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Kolos" <Kolos@.discussions.microsoft.com> wrote in message
news:60E1CCF1-AF58-46FA-BB1F-B58C10C3115E@.microsoft.com...
Hi all,
I have the following problem. I need to start a tarce in a job step. I
created the trace and it works properly. I scripted it and ran it in Query
Analyzer. Although the status displayed by SELECT * FROM ::
fn_trace_getinfo(default) says the trace is started (status=1), nothing
happens. What can be the problem?
Thanks in advance.
Sincerely,
Kolos|||Well, thanks. The size of the file was 0 KB until the trace was stopped. I
feel a bit stupid... Thanks, anyway.
"Narayana Vyas Kondreddi" wrote:

> What do you mean, when you say nothing happens? Are you trying to open the
> output trace file when the trace is running? Then it may not work.
> You have to stop the trace, to open the file to which trace is currently
> logging.
> You have to use sp_trace_setstatus to stop and close the trace, before
> opening the output file.
> You may find my following article useful:
> http://vyaskn.tripod.com/server_sid..._sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Kolos" <Kolos@.discussions.microsoft.com> wrote in message
> news:60E1CCF1-AF58-46FA-BB1F-B58C10C3115E@.microsoft.com...
> Hi all,
> I have the following problem. I need to start a tarce in a job step. I
> created the trace and it works properly. I scripted it and ran it in Query
> Analyzer. Although the status displayed by SELECT * FROM ::
> fn_trace_getinfo(default) says the trace is started (status=1), nothing
> happens. What can be the problem?
> Thanks in advance.
> Sincerely,
> Kolos
>
>

Monday, March 19, 2012

aggregation using lastchild

Hi there

I'm using the following MDX function and it works perfect.

iif([Ledger Date].CurrentMember.Level.Name = "Year", ", [Ledger Date].CURRENTMEMBER.LASTCHILD, ([Ledger Date].CURRENTMEMBER, [Measures].[Value]))

But now I want to add another dimension namely Accout Type to the scenario containing Asset, Liabilities, Income and Expence. The lastchild must only work for "Year", "Liabilities" and "Assets"

How do I achive this?

Thank you in advance.

Try :

iif( ([Ledger Date].CurrentMember.Level.Name = "Year" and [Account Type].CurrentMember is [Account Type].[Liabilities]) or ([Ledger Date].CurrentMember.Level.Name = "Year" and [Account Type].CurrentMember is [Account Type].[Assets]) , ", [Ledger Date].CURRENTMEMBER.LASTCHILD, ([Ledger Date].CURRENTMEMBER, [Measures].[Value]))

|||

i've adjusted your suggestion to the following:

iif(([Ledger Date].CurrentMember.Level.Name = "Year" and [Ledger Entries].CurrentMember is [Ledger Entries].[Account Type].&[Asset]) ,[Ledger Date].CURRENTMEMBER.LASTCHILD, ([Ledger Date].CURRENTMEMBER, [Measures].[Value]))

but then I get the following error:

Infinite recursion detected during execution of calculated member.

any idea why?

thanks again.

Aggregation that results two columns

Hi,
I have following data coming from prior transformations.

Id Unit Name NewFlag
_

1 JacuzziBox 1
2 Hummer H2 0
3 Waste dumper 0

and so on.

I want aggregate task to result 2 fields (without any GROUP BY-ing), so that one contains total number of rows or records, and one contain total number of records with NewFlag=1.

Just wondering if it is possible by using Aggregate and how.

Thanks for your help in advance,The Aggregate transform does not support calculations based on a conditional. However, since you are using 0 and 1 as values for the NewFlag, just sum that column and you should have the right result. You'll also need to select the count(*) aggregation in the dialog. Don't select any other columns, and you will get one row with the first column containing the total rows, and the second containing the NewFlag count.

Aggregation problem in Report Designer

Hi!!! Please help me.

I have the following table structure.

-A (name)

--B (name)

--C (name, total)

For example.

A { Tom, Sam John }

B {Mazda, Audi, Ford }

C: { (Monitor, 100), (Telephone, 230), (Mouse, 370)}

The corresponding sql select:

select * from A left join B left join C

Retrive obvios result:

And now I have desing report with the following structure:

1) Create list element (A_List) and use detail group to grouping data by A.name in it.

2) Then I use 2 another lists and placed it in A.

Now I want get the textbox = Sum(C.total) in the A_List area. It is obvious that ealier represented sql select make cartesian product (AxBxC) of A,B,C tables. And now I have multiple record for single row for each row in each table. For example I have three equals records for totals.

And I could't use aggregation function in Sum. Anobody know how this problem solves?

Hi,

I'm not sure I'll be answering exactly what you need, but I'll take the risk ;-)

I will try using a RunningTotal, checking for the condition of change, or, in the worst case creating a function in Report code to perform the custom sum.

You'll find more info in Books On Line.

HTH

Jordi Rambla

Solid Quality Learning

Sunday, March 11, 2012

Aggregation and granularity levels

I'm having problems implementing the following in reporting services 2005.
My hierarchy looks like this (just to illustrate the problem...):
University->Student->Exam
My query returns the following fields:
University, Student, StudentPayment, ExamName, ExamScore
I need to create a report that will show the hierarchy and to smartly
aggregate the StudentPayment to both the Student and the University levels.
The problem is that the StudentPayment field is being multiplied by the
number of exams in the upper level aggregation.
If only I could set the granularity level of the StudentPayment measurement...
Note that I don't have access to the query, so I can't change anything on
that front.
Thanks,
EfiHello Efi,
I would like to know this more detailed.
How you calculated the StudentPayment?
Did you mean that you use a constant like $10 to multiplied by the count
amount of the exams the student takes?
If you have used a group for each level of University and Student, you
could have a aggregation expression like this:
AVG(Fields!StudentPayment, 'Group_name')
You could refer this article.
Aggregate Functions (Reporting Services)
http://msdn2.microsoft.com/en-us/library/aa255811(SQL.80).aspx
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Efi,
I understand this issue.
It seems that the dataset you use is not a 3NF data. So we may not
calculated the Student Payment.
You may use some embbed code to sum the column. I am researching this.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Efi,
Unfortunately, I did not find the solution yet.
A workaround maybe using the custom code to sum the Payment by code.
Or you could add a second dataset to get the distinct Payment for the
Student and aggregate it.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Can you please send me an example of a custom code the aggregates a field?
Thanks,
"Wei Lu [MSFT]" wrote:
> Hello Efi,
> Unfortunately, I did not find the solution yet.
> A workaround maybe using the custom code to sum the Payment by code.
> Or you could add a second dataset to get the distinct Payment for the
> Student and aggregate it.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Efi,
Currently, I did not have any examples yet. I will try to figure out any
example for you but it will be some time consuming so I appreciate your
patience. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Efi,
My internal colleague suggest that you need to change your dataset. Which
means you need to modify the sql statement you use to get the dataset or
create a new dataset.
Could you please let me know if you could do this?
Could you contact your DBA to create a new stored procedure to get the
information you want?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Aggregating groups of rows using SQL

Dear Group,
I have a SQL coding problem, which I hope that you can help me with!
I have the following SQL view, which returns a Data Table, STOCK_TRADE, with
the following data..
CUSTOMER STOCK_CODE QUANTITY PRICE DATE
J. BLOGGS MSFT 1000 535.50 10/05/2006
J. BLOGGS MSFT 2000 536.75 11/05/2006
J. BLOGGS GOOG 500 400.00 10/05/2006
J. BLOGGS GOOG 100 300.00 12/05/2006
H. ENFIELD MSFT 300 536.75 11/05/2006
My query is, I wish to aggregate rows which are for the same customer and
stock code taking a SUM of the QUANTITY and an AVERAGE of the PRICE and the
FIRST/TOP/EARLIEST of the DATE, so that the table looks like this:
CUSTOMER STOCK_CODE QUANTITY PRICE DATE
J. BLOGGS MSFT 3000 536.13 10/05/2006
J. BLOGGS GOOG 600 350.00 10/05/2006
H. ENFIELD MSFT 300 536.75 11/05/2006
Ultimately, I am aiming to order by STOCK_CODE in a Crystal Report to GROUP
BY the STOCK_CODE and then just one line per customer for each stock that
they have.
Can anyone suggest the SQL - preferably without using cursors - to perform a
SELECT on the STOCK_TRADE table as shown in the top example to return the
aggregate Data Set in the bottom table?
I am using Visual Studio 2005 (.NET) and SQL Server 2005, although generic
SQL would be my preference to solve the programming task.
Many thanks in advance for your time and help.SELECT CUSTOMER,
STOCK_CODE,
SUM(QUANTITY),
AVG(PRICE),
MIN(DATE)
FROM STOCK_TRADE
GROUP BY CUSTOMER,STOCK_CODE|||<markc600@.hotmail.com> wrote in message
news:1147021432.247292.102430@.e56g2000cwe.googlegroups.com...
> SELECT CUSTOMER,
> STOCK_CODE,
> SUM(QUANTITY),
> AVG(PRICE),
> MIN(DATE)
> FROM STOCK_TRADE
> GROUP BY CUSTOMER,STOCK_CODE
Many thanks for that - it seems to be exactly what I'm looking for.
Apologies for the double posting too; I've cancelled the second message and
forgot that I posted this one.
Thanks again.|||SELECT [CUSTOMER], [STOCK_CODE], SUM(QUANTITY) as [QUANTITY], AVG(PRICE) as
[PRICE], MIN(DATE) as [DATE] FROM WHATEVERYOURVIEWIS GROUP BY [CUSTOMER],
[STOCK_CODE]
Although this is the wrong place to post this!
Cheers,
Greg Young
MVP - C#
"Liddle Feesh" <none> wrote in message
news:445e273b$0$9257$ed2619ec@.ptn-nntp-reader01.plus.net...
> Dear Group,
> I have a SQL coding problem, which I hope that you can help me with!
> I have the following SQL view, which returns a Data Table, STOCK_TRADE,
> with the following data..
> CUSTOMER STOCK_CODE QUANTITY PRICE DATE
> J. BLOGGS MSFT 1000 535.50 10/05/2006
> J. BLOGGS MSFT 2000 536.75 11/05/2006
> J. BLOGGS GOOG 500 400.00 10/05/2006
> J. BLOGGS GOOG 100 300.00 12/05/2006
> H. ENFIELD MSFT 300 536.75 11/05/2006
> My query is, I wish to aggregate rows which are for the same customer and
> stock code taking a SUM of the QUANTITY and an AVERAGE of the PRICE and
> the FIRST/TOP/EARLIEST of the DATE, so that the table looks like this:
> CUSTOMER STOCK_CODE QUANTITY PRICE DATE
> J. BLOGGS MSFT 3000 536.13 10/05/2006
> J. BLOGGS GOOG 600 350.00 10/05/2006
> H. ENFIELD MSFT 300 536.75 11/05/2006
> Ultimately, I am aiming to order by STOCK_CODE in a Crystal Report to
> GROUP BY the STOCK_CODE and then just one line per customer for each stock
> that they have.
> Can anyone suggest the SQL - preferably without using cursors - to perform
> a SELECT on the STOCK_TRADE table as shown in the top example to return
> the aggregate Data Set in the bottom table?
> I am using Visual Studio 2005 (.NET) and SQL Server 2005, although generic
> SQL would be my preference to solve the programming task.
> Many thanks in advance for your time and help.
>|||hmm this showed up with no replies when I first saw it :-?
"Greg Young" <DruckDruckGoose@.hotmail.com> wrote in message
news:udINwWocGHA.3388@.TK2MSFTNGP05.phx.gbl...
> SELECT [CUSTOMER], [STOCK_CODE], SUM(QUANTITY) as [QUANTITY], AVG(PRICE)
> as [PRICE], MIN(DATE) as [DATE] FROM WHATEVERYOURVIEWIS GROUP BY
> [CUSTOMER], [STOCK_CODE]
> Although this is the wrong place to post this!
> Cheers,
> Greg Young
> MVP - C#
> "Liddle Feesh" <none> wrote in message
> news:445e273b$0$9257$ed2619ec@.ptn-nntp-reader01.plus.net...
>|||Just one point...
Are you sure you want a straight average of stock price, rather than a
weighted average?
i.e. if 999 shares sell for $1000 each, and 1 share sells for $2, should
the average be $501 or should it be $999.002?
You may want something like this...
SELECT CUSTOMER,
STOCK_CODE,
SUM(QUANTITY) as TotalQuantity,
SUM(PRICE*quantity)/SUM(QUANTITY) as AvgPrice,
MIN(DATE) as FirstDate
FROM STOCK_TRADE
GROUP BY CUSTOMER,STOCK_CODE
"Liddle Feesh" <none> wrote in message
news:445e29c0$0$9265$ed2619ec@.ptn-nntp-reader01.plus.net...
> <markc600@.hotmail.com> wrote in message
> news:1147021432.247292.102430@.e56g2000cwe.googlegroups.com...
> Many thanks for that - it seems to be exactly what I'm looking for.
> Apologies for the double posting too; I've cancelled the second message
and
> forgot that I posted this one.
> Thanks again.
>

Aggregates in Matrix Reports

I am having a similar issue in a Matrix report. The following is a message from someone else having the same problem.

I have a calculation (Aggregate) that has a scope that is for both the Row and the Column.

Example:

What is the percent of total sales and percent of total category sales?
How do I do these Percentages in a Matrix?

Q1 Q2 Total
Beverages

Lemonade
Sales $100 150 250
% of Bev 44%
Percent of Total 19%

Soda
Sales $125 125 250
% of Bev 46%
Percent of Total 22%
Total Beverages $225 275 500

Snacks
Peanuts
Sales $100 150 250
% of Snacks
% of Total

Cookies
Sales $ 200 250 450
% of Snacks
% of Total
Total Snacks 300 400 700
Total Sales 525 675 1300

Any Ideas?

Assuming you have a matrix called "Matrix" with a row grouping called "CategoryGroup", you can calculate percentages in matrix cells using aggregate functions with explicit scope names.
E.g. for the percentage of sales within the current category group:
=Sum(Fields!SalesAmount.Value) / Sum(Fields!SalesAmount.Value, "CategoryGroup")

Percentage of sales within total sales:
=Sum(Fields!SalesAmount.Value) / Sum(Fields!SalesAmount.Value, "Matrix")
Or:
=Sum(Fields!SalesAmount.Value) / Sum(Fields!SalesAmount.Value, "DataSetName")

-- Robert|||What if I am not using a matrix. I just have a report that is now counting the items in subfields and I also want to include a percentage of the total that the subfield is.
animals
cats
lions 30
tigers 20
pumas 50
bears
grizzly 10
etc.
total 200
I want to include a percentage column
animals
cats
lions 16 8%
tigers 10 5%
pumas 30 15%
etc.
|||

Not sure I understand. It sounds like you are using a table with table groups. Is the "total" calculated for the entire table, or for individual groups?

In both cases, you can just use a similar approach for calculating the percentages:

=Sum(Fields!amount.Value) / Sum(Fields!amount.Value, "table1_AnimalTypeGroup")

=Sum(Fields!amount.Value) / Sum(Fields!amount.Value, "table1")

-- Robert

|||

I have a similar question. I too am using a matrix for my report. It is an employee headcount report. I have row groups for dept and employee ID and I have column groups for the month and the headcount. I also have parameters on Dept and month. The data shows as follows:

Dept Emp ID# Emp Name Jan Feb March

HR 123 H. Potter 1 1 1

IT 231 R. Weasley 1 1 1

Sales 879 C. Digory 1 1 0

I want to format the report so that if there is a change in the headcount number for an employee, the row is highlighted in a different color. So in the example provided above, the last column would be highlighted in red.

I think the logic would be to add up the total of the row sum(fields!headcount.value) and divide it by the number of months (parameter!month.count) and then compare the value in the first column (January) to the result.

However I am not sure of the exact syntax for this formula nor am I sure where to place this formula within the layout of the report. Should I add another column grouping? Or would this formula go somewhere in the group properties for the employee?

Any help would be greatly appreciated

Linda

|||Robert, you are great. This example helped me to get the results I wanted.

Aggregates in Matrix Reports

I am having a similar issue in a Matrix report. The following is a message from someone else having the same problem.

I have a calculation (Aggregate) that has a scope that is for both the Row and the Column.

Example:

What is the percent of total sales and percent of total category sales?
How do I do these Percentages in a Matrix?

Q1 Q2 Total
Beverages

Lemonade
Sales $100 150 250
% of Bev 44%
Percent of Total 19%

Soda
Sales $125 125 250
% of Bev 46%
Percent of Total 22%
Total Beverages $225 275 500

Snacks
Peanuts
Sales $100 150 250
% of Snacks
% of Total

Cookies
Sales $ 200 250 450
% of Snacks
% of Total
Total Snacks 300 400 700
Total Sales 525 675 1300

Any Ideas?

Percentage of sales within total sales:
=Sum(Fields!SalesAmount.Value) / Sum(Fields!SalesAmount.Value, "Matrix")
Or:
=Sum(Fields!SalesAmount.Value) / Sum(Fields!SalesAmount.Value, "DataSetName")

Not sure I understand. It sounds like you are using a table with table groups. Is the "total" calculated for the entire table, or for individual groups?

In both cases, you can just use a similar approach for calculating the percentages:

=Sum(Fields!amount.Value) / Sum(Fields!amount.Value, "table1_AnimalTypeGroup")

=Sum(Fields!amount.Value) / Sum(Fields!amount.Value, "table1")

-- Robert

|||

Dept Emp ID# Emp Name Jan Feb March

HR 123 H. Potter 1 1 1

IT 231 R. Weasley 1 1 1

Sales 879 C. Digory 1 1 0

Any help would be greatly appreciated

Linda

|||Robert, you are great. This example helped me to get the results I wanted.

Aggregates in Matrix Reports

I am having a similar issue in a Matrix report. The following is a message from someone else having the same problem.

I have a calculation (Aggregate) that has a scope that is for both the Row and the Column.

Example:

What is the percent of total sales and percent of total category sales?
How do I do these Percentages in a Matrix?

Q1 Q2 Total
Beverages

Lemonade
Sales $100 150 250
% of Bev 44%
Percent of Total 19%

Soda
Sales $125 125 250
% of Bev 46%
Percent of Total 22%
Total Beverages $225 275 500

Snacks
Peanuts
Sales $100 150 250
% of Snacks
% of Total

Cookies
Sales $ 200 250 450
% of Snacks
% of Total
Total Snacks 300 400 700
Total Sales 525 675 1300

Any Ideas?

Percentage of sales within total sales:
=Sum(Fields!SalesAmount.Value) / Sum(Fields!SalesAmount.Value, "Matrix")
Or:
=Sum(Fields!SalesAmount.Value) / Sum(Fields!SalesAmount.Value, "DataSetName")

Not sure I understand. It sounds like you are using a table with table groups. Is the "total" calculated for the entire table, or for individual groups?

In both cases, you can just use a similar approach for calculating the percentages:

=Sum(Fields!amount.Value) / Sum(Fields!amount.Value, "table1_AnimalTypeGroup")

=Sum(Fields!amount.Value) / Sum(Fields!amount.Value, "table1")

-- Robert

|||

Dept Emp ID# Emp Name Jan Feb March

HR 123 H. Potter 1 1 1

IT 231 R. Weasley 1 1 1

Sales 879 C. Digory 1 1 0

Any help would be greatly appreciated

Linda

|||Robert, you are great. This example helped me to get the results I wanted.

Aggregates in Matrix Reports

I am having a similar issue in a Matrix report. The following is a message from someone else having the same problem.

I have a calculation (Aggregate) that has a scope that is for both the Row and the Column.

Example:

What is the percent of total sales and percent of total category sales?
How do I do these Percentages in a Matrix?

Q1 Q2 Total
Beverages

Lemonade
Sales $100 150 250
% of Bev 44%
Percent of Total 19%

Soda
Sales $125 125 250
% of Bev 46%
Percent of Total 22%
Total Beverages $225 275 500

Snacks
Peanuts
Sales $100 150 250
% of Snacks
% of Total

Cookies
Sales $ 200 250 450
% of Snacks
% of Total
Total Snacks 300 400 700
Total Sales 525 675 1300

Any Ideas?

Percentage of sales within total sales:
=Sum(Fields!SalesAmount.Value) / Sum(Fields!SalesAmount.Value, "Matrix")
Or:
=Sum(Fields!SalesAmount.Value) / Sum(Fields!SalesAmount.Value, "DataSetName")

Not sure I understand. It sounds like you are using a table with table groups. Is the "total" calculated for the entire table, or for individual groups?

In both cases, you can just use a similar approach for calculating the percentages:

=Sum(Fields!amount.Value) / Sum(Fields!amount.Value, "table1_AnimalTypeGroup")

=Sum(Fields!amount.Value) / Sum(Fields!amount.Value, "table1")

-- Robert

|||

Dept Emp ID# Emp Name Jan Feb March

HR 123 H. Potter 1 1 1

IT 231 R. Weasley 1 1 1

Sales 879 C. Digory 1 1 0

Any help would be greatly appreciated

Linda

|||Robert, you are great. This example helped me to get the results I wanted.

Aggregates in Matrix Reports

I am having a similar issue in a Matrix report. The following is a message from someone else having the same problem.

I have a calculation (Aggregate) that has a scope that is for both the Row and the Column.

Example:

What is the percent of total sales and percent of total category sales?
How do I do these Percentages in a Matrix?

Q1 Q2 Total
Beverages

Lemonade
Sales $100 150 250
% of Bev 44%
Percent of Total 19%

Soda
Sales $125 125 250
% of Bev 46%
Percent of Total 22%
Total Beverages $225 275 500

Snacks
Peanuts
Sales $100 150 250
% of Snacks
% of Total

Cookies
Sales $ 200 250 450
% of Snacks
% of Total
Total Snacks 300 400 700
Total Sales 525 675 1300

Any Ideas?

Percentage of sales within total sales:
=Sum(Fields!SalesAmount.Value) / Sum(Fields!SalesAmount.Value, "Matrix")
Or:
=Sum(Fields!SalesAmount.Value) / Sum(Fields!SalesAmount.Value, "DataSetName")

Not sure I understand. It sounds like you are using a table with table groups. Is the "total" calculated for the entire table, or for individual groups?

In both cases, you can just use a similar approach for calculating the percentages:

=Sum(Fields!amount.Value) / Sum(Fields!amount.Value, "table1_AnimalTypeGroup")

=Sum(Fields!amount.Value) / Sum(Fields!amount.Value, "table1")

-- Robert

|||

Dept Emp ID# Emp Name Jan Feb March

HR 123 H. Potter 1 1 1

IT 231 R. Weasley 1 1 1

Sales 879 C. Digory 1 1 0

Any help would be greatly appreciated

Linda

|||Robert, you are great. This example helped me to get the results I wanted.

Thursday, March 8, 2012

Aggregate() doesn't aggregate over an expression

We use CTP3 of SQL Server 2005 SP2. We have the following scope expression

Scope
(
[Date].[Calendar Year].[Calendar Year].Members,
[Date].[Month].Members
);

(
{
[Measures].[Profit YTD]
}
) =

Aggregate (
PeriodsToDate([Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember)
, StrToMember(Extensibility.Replace([Measures].CurrentMember.UniqueName, " YTD", "")) -- remove " YTD" suffix to aggregate over the corresponding standard measure

);

Format_String ( This ) = "Currency";
End Scope;

However, Aggregate() essentially bypasses the call to the SSAS stored procedure (Extensibiliy.Replace is a custom SSAS stored procedure). Instead Aggregate() uses the [Profit YTD] measure instead. If I replace Aggregate() with SUM() it works correctly. Is this a bug? A workaround?

Is [Profit YTD] a calculated measure itself ? If this is a case, Aggregate would switch solve orders with it in order to try to determine the correct aggregation function. If Sum is what you want to do, then using Sum directly is probably a right thing to do here.

I also want to note, that the expression inside Aggregate (or Sum) is very inefficient. If you have plenty of calc measures with YTD suffix, and you want to compute year to date over corresponding measure without YTD suffix, there are much more efficient ways of doing it.

|||

Mosha,

Thank you so much for looking into this. I really appreciate your help. Yes, Profit YTD is a calculated member.

Here are our requirements. We have a cube with rather large dimensions, e.g. Customer dimension (some 150,000 customers) and Account dimension (represents a customer bank account) with some 1.5 million members. We need to support additive and semi-additive aggregations (e.g. rolling twelve, weighted averages, etc). Considering the fact that the Report Builder doesn't support dimension-level calculated members (time intelligence), we have no other option but to create measure-level calculated members for each calculation, e.g.: Profit R12 for rolling 12 aggregation of the Profit additive measure, Average Balance R12 for rolling 12 aggregation of the Average Balance weighted average, etc. In addition, we have a requirement to provide YTD and QTD calculations.

The idea behind the the script above was to work universally, i.e. to use the corresponding measure underlying function since additional measures will be added to the scope. For example, if we calculate Profit YTD, we need to base our calculation on the Profit measure (hence, we need to remove the YTD suffix).

Here is an example of our script for R12 aggregations:

Scope
(
[Date].[Month].[Month].Members

);

(
{
[Measures].[Interest Paid R12],
[Measures].[Interest Accrued R12],
...
}
) =
Sum (
{
ParallelPeriod(
[Date].[Calendar].[Month],
11,
[Date].[Calendar].CurrentMember
) : [Date].[Calendar].CurrentMember
}
// current measure
, StrToMember(Extensibility.Replace([Measures].CurrentMember.UniqueName, " R12", "")) -- remove R12 suffix to get to the the corresponding regular measure
);

Format_String ( This ) = "Currency";
End Scope;

What will be the recommended approach from a performance standpoint? Should we introduce multiple scopes (for additive and semi-additive measures) that use the respective aggregation function directly? Should we copy the measure before we start aggregating to avoid StrToMember(), e.g.:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Profit YTD] AS [MEASURES].[Profit] , FORMAT_STRING = "Currency";

|||

Considering the fact that the Report Builder doesn't support dimension-level calculated members (time intelligence)

So the root of your problem is that you cannot use utility attribute in Time dimension or separate utility dimension because of Report Builder limitation ? My advice is to do it right way with utility dimension, but instead of creating calculated members, make R12, YTD etc as real members in this dimension, they just won't be associated with any data. Then, you can put the formulas on them either using custom member formulas or inside MDX script.

|||

Thank you.

1. I'v e read your post about this and and David's article but I need to wrap my head about the utility dimension concept. Would mind eleborating a bit more on the utility dimension approach? Are you saying to add the utility dimension to the cube but don't link this dimension to the measure group at all? What changes need to be made to the Time Intelligence script (if any) to re-purpose it to use the utility dimension?

2. In case the end users find the utility dimension approach confusing from an usability standpoint, can we explore a more efficient approach with standalone calculated members?

|||

Are you saying to add the utility dimension to the cube but don't link this dimension to the measure group at all?

Or you can link the "Normal" or "Current" member of this dimension to the fact table by creating a new calculated column in DSV with constant|||

After a few hours of experimenting and brain crunching I am getting nowhere:

1. Introducing an utility dimension that is not linked to the measure group seems to work in the cube browser/Excel but it doesn't work in the Report Builder because the Report Builder model expects a dmension relationship. Otherwise, there is no navigational path in the model and once you drag and drop the dimension, there is nowhere to go to.

2. The utility dimension approach is causing much grief. Here is what I do.

a) I created a named query DimDateCalculations as follows:

SELECT - 1 AS MemberKey, - 1 AS CurrentPeriodID, 'Current Period' AS CalendarDateCalculations, 'Current Period' AS FiscalDateCalculations
UNION
SELECT 1 AS MemberKey, - 1 AS CurrentPeriodID, 'Rolling 12' AS CalendarDateCalculations, 'Rolling 12' AS FiscalDateCalculations
UNION
SELECT 2 AS MemberKey, - 1 AS CurrentPeriodID, 'YTD' AS CalendarDateCalculations, 'YTD' AS FiscalDateCalculations
UNION
SELECT 3 AS MemberKey, - 1 AS CurrentPeriodID, 'QTD' AS CalendarDateCalculations, 'QTD' AS FiscalDateCalculations

b) I added a calculated column CurrentPeriodID to the fact table and defaulted it to -1. In DSV, I joined DimDateCalculations to the fact table on CurrentPeriodID

c) I created a new dimension (Date Calculations) on top of the DimDateCalculations named query. The dimension key is set to MemberKey and it has two attribute hierarchies (CalendarDateCalculations and FiscalDateCalculations) whose default members are set to the Current Period member.

d) I added the following script to the cube (only changed the dimension name in the script that the Time Intelligence Wizard generates):

Scope(
{
[Measures].[Tax],
[Measures].[Profit],
}
);

(
[Date Calculations].[Calendar Date Calculations].[YTD],
[Date].[Calendar Year].[Calendar Year].Members,
[Date].Month.Members
) =

Aggregate(
{[Date].[Calendar Date Calculations].DefaultMember} *
PeriodsToDate([Date].[Calendar].[Calendar Year],
[Date].[Calendar].CurrentMember)

);
End Scope;

e) In the cube browser, I created a report which filters the Calendar Date dimension to a given month. I dropped the Calendar Date Calculations on columns, another dimension on rows, and Profit as data.

However, the YTD column is emtpy (after Show Empty Cells is enabled). What am I missing? Again, our requirement is that the utility dimension cannot have calculated members.

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 Total Acreage and Group By for Mail Merge

I was helped on an earlier question to complete my mail merge with the following code:
select YourTable.*
from YourTable
inner join --DistinctNames
(select Max(PrimaryKey) as PrimaryKey
from YourTable
group by FirstName,
LastName) DistinctNames
on YourTable.PrimaryKey = DistinctNames.PrimaryKey
Basically this code queries my mailing list and ensures that i do not send mutiple letters to one person at the same address who might be in the batabase more than once. However, the reason they are in there more than once is that they might own additional properties. Anyway, I have a column that includes their acreage for each property in each record and I would like to add those up for each person during my query. Thought anyone? Thanks!select YourTable.*, DistinctNames.total_acreage
from YourTable
inner join --DistinctNames
(select Max(PrimaryKey) as PrimaryKey, sum(acreage) total_acreage
from YourTable
group by FirstName,
LastName) DistinctNames
on YourTable.PrimaryKey = DistinctNames.PrimaryKey|||Thanks, the query seems to run withour error, however, the "total acreage" field is simply populated with one of their acreage values, not their total. It looks like the sumation is occurring after the new table is created, which would provide an incorrect result. Any other thoughts?|||Nevermind, it worked flawlessly. Thanks a lot!

Tuesday, March 27, 2012

Alias question

alias naming

Thursday, March 22, 2012

Alert on deadlock

Tuesday, March 20, 2012

Alert for DeadLock

Alert doesn't start

Alert doesn't start

Alert doesn't start

Monday, March 19, 2012

aggregation using lastchild

Aggregation that results two columns

Aggregation problem in Report Designer

Sunday, March 11, 2012

Aggregation and granularity levels

Aggregating groups of rows using SQL

Aggregates in Matrix Reports

Aggregates in Matrix Reports

Aggregates in Matrix Reports

Aggregates in Matrix Reports

Aggregates in Matrix Reports

Thursday, March 8, 2012

Aggregate() doesn't aggregate over an expression

Aggregate values based on the value of another field

Aggregate Total Acreage and Group By for Mail Merge

SQL tool for MSDE

Blog Archive

About Me