Interesting UPDATE STATEMENT for SQL

This Following statement executes perfectly in SQL2000
but not in SQL7.0 it gives the message
Server: Msg 147, Level 16, State 2, Procedure spTEST1, Line 57
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Create Procedure "spTEST1"
As

--Update Processed Flags
UPDATE tblTransaction
SET TransProcessed = 1
FROM dbo.tblTrustGroupTrust
INNER JOIN dbo.tblTrust ON dbo.tblTrustGroupTrust.TgtTrustID = dbo.tblTrust.TrustID
INNER JOIN dbo.tblTransaction ON dbo.tblTrust.TrustID = dbo.tblTransaction.TransTrustID
INNER JOIN dbo.tblShareholder ON dbo.tblTransaction.TransShID = dbo.tblShareholder.ShID
INNER JOIN dbo.tblTransType ON dbo.tblTransType.TransTypeID = dbo.tblTransaction.TransTypeID
WHERE (dbo.tblTrustGroupTrust.TgtTrustGroupID = 3) AND (dbo.tblTransaction.TransProcessed = 0)
AND (dbo.tblShareholder.ShPaymentMethod = 1) AND (dbo.tblShareholder.ShDeceased = 0) AND tblShareholder.ShBankAccount IS NOT NULL
AND EXISTS
(
SELECT dbo.tblTransaction.TransShID
FROM dbo.tblTrustGroupTrust
INNER JOIN dbo.tblTransaction ON dbo.tblTrustGroupTrust.tgtTrustID = dbo.tblTransaction.TransTrustID
WHERE (dbo.tblTransaction.TransProcessed = 0) AND (dbo.tblTrustGroupTrust.TgtTrustGroupID = 3) AND (dbo.tblShareholder.SHID = dbo.tblTransaction.TransShID)
GROUP BY dbo.tblTransaction.TransShID
HAVING SUM(tblTransaction.TransAmt) >= 10
)

When using a straight select on this statement in SQL7.0 it works fine:
SELECT tblTransaction.TransProcessed
FROM .....

Can anyone shed some light on this?The aggregate is on the table being updated - although it is in a subquery this is correllated and so still fails the test.

If you can change the having clause to
HAVING SUM(dbo.tblTransaction.TransAmt) >= 10

so that it is using the copy of the table in the subquery then it should work.|||It might be clearer if you use an alias for the subquery table like
tblTransaction t2.|||Thanks, I very nearly tried to add the missing dbo prefix's in but I presumed that would be too trivial and the error was more complicated than something like that.

Obviously SQL2000 is a little more relaxed on this sort of thing, as it works without error.

Integration between SSRS and SSAS

Any plans to improve the integration between Reporting Services and Analysis Services?

I‘ll try to be specific as possible. Below is a state of affairs for the SSRS and SSAS integration as of today (MS SQL Server 2005):

1. When querying Analysis Services cubes, the data can be returned in one of two ways: as a cellset or as a recordset. Reporting Services uses the recordset format, and as such, the data from Analysis Services goes through a flattening process to return the data in a two-dimensional grid.
   SSRS should accommodate SSAS native output format without flattening.
   This is obviously a very serious point of contention between SSRS and SSAS. This particular issue is affecting usefulness of the SSRS as reporting software for SSAS.
2. Quite often, depending on the MDX, SSRS produces the infamous error message “The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension”.
3. The error message above pushes to switch from the native "Microsoft SQL Server Analysis Services (AdomdClient)" to MS SQL Server 2005 Analysis Services 9.0 OLE DB Provider. However, OLEDB provider goes through the flattening process which negatively affects the received data overall format on the SSRS side.
   ADOMD native provider should support any kosher MDX statement. SSRS should be able to receive ADOMD output as is.
4. Graphical MDX Query Designer doesn’t accept manual changes tothe MDX.
   It should be 2-way communication.MDX Query Designer should analyze manually tweaked MDX and if it is legit accept it.

Hopefully, Tablix region as a UI control will be able to support native SSAS data outputs in MS SQL Server 2008.

I am talking here about straight reporting. I am not asking for the interactive OLAP client.

References:

1. Integrating Analysis Services with Reporting Services
   http://technet.microsoft.com/en-us/library/aa902647(sql.80).aspx
2. Rant: Reporting Services and Analysis Services
   http://cwebbbi.spaces.live.com/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!163.entry
3. The state of Reporting Services UDM (Analysis Services) integration
   http://peterkol.spaces.live.com/blog/cns!68755AEAC31F9A6C!185.entry

Regards,

Yitzhak Khabinsky

Brian Welcker/ Robert Bruckner: I hope that one of you can elaborate. This is a VERY important subject matter. Thank you.a

|||

I agree....from the very beginning I thought that SSRS's ability to work with cubes was a VERY important feature because it would allow access to the amazing aggregation power that cubes have...without this you have to spend lots of time writing complicated and bad performing SQL in order to put aggregates in your reports (and the fact is that reports are mostly about aggregating data). I would have thought this to be a simple, natural solution and am AMAZED that it doesn't work.

I actually work for a company where we currently use Cognos and Reporting Services, and one of the things I've told them is to consider using Analysis Services instead because then the cubes would be accessible to SSRS. I guess I bought into the marketing hype a little too early :-).

SB

|||

Thanks for your feedback. The core scenario you seem to want to paste an arbitrary MDX statement into RS and have the RS engine format it. One question would be - where does your MDX come from? Are you crafting it by hand? We felt that most users are not MDX experts and would simply use the query builder (which will form the MDX in a conformant form).

Anyway, while supporting arbitrary MDX is certainly a valid scenario, it is simply not what RS was designed to do. In order to understand the data that is being returned from a source, SSRS must have a fixed schema. Without a schema, RS cannot compute subtotals or sort or filter the data. A cellset based tool relies fully on the back-end for all interactivity (which negates all of the RS caching layer). Tablix in 2008 does nothing to change this approach - in fact several of the Tablix functions (side by side groups, for example) are not even possible to express in MDX.

We do understand that people would like add this functionality to RS and we will consider supporting arbitrary schema and round trip the interactivity for post-2008 future version.

Integration between SSRS and SSAS

Any plans to improve the integration between Reporting Services and Analysis Services?

I‘ll try to be specific as possible. Below is a state of affairs for the SSRS and SSAS integration as of today (MS SQL Server 2005):

1. When querying Analysis Services cubes, the data can be returned in one of two ways: as a cellset or as a recordset. Reporting Services uses the recordset format, and as such, the data from Analysis Services goes through a flattening process to return the data in a two-dimensional grid.
   SSRS should accommodate SSAS native output format without flattening.
   This is obviously a very serious point of contention between SSRS and SSAS. This particular issue is affecting usefulness of the SSRS as reporting software for SSAS.
2. Quite often, depending on the MDX, SSRS produces the infamous error message “The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension”.
3. The error message above pushes to switch from the native "Microsoft SQL Server Analysis Services (AdomdClient)" to MS SQL Server 2005 Analysis Services 9.0 OLE DB Provider. However, OLEDB provider goes through the flattening process which negatively affects the received data overall format on the SSRS side.
   ADOMD native provider should support any kosher MDX statement. SSRS should be able to receive ADOMD output as is.
4. Graphical MDX Query Designer doesn’t accept manual changes tothe MDX.
   It should be 2-way communication.MDX Query Designer should analyze manually tweaked MDX and if it is legit accept it.

Hopefully, Tablix region as a UI control will be able to support native SSAS data outputs in MS SQL Server 2008.

I am talking here about straight reporting. I am not asking for the interactive OLAP client.

References:

1. Integrating Analysis Services with Reporting Services
   http://technet.microsoft.com/en-us/library/aa902647(sql.80).aspx
2. Rant: Reporting Services and Analysis Services
   http://cwebbbi.spaces.live.com/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!163.entry
3. The state of Reporting Services UDM (Analysis Services) integration
   http://peterkol.spaces.live.com/blog/cns!68755AEAC31F9A6C!185.entry

Regards,

Yitzhak Khabinsky

Brian Welcker/ Robert Bruckner: I hope that one of you can elaborate. This is a VERY important subject matter. Thank you.a

|||

I agree....from the very beginning I thought that SSRS's ability to work with cubes was a VERY important feature because it would allow access to the amazing aggregation power that cubes have...without this you have to spend lots of time writing complicated and bad performing SQL in order to put aggregates in your reports (and the fact is that reports are mostly about aggregating data). I would have thought this to be a simple, natural solution and am AMAZED that it doesn't work.

I actually work for a company where we currently use Cognos and Reporting Services, and one of the things I've told them is to consider using Analysis Services instead because then the cubes would be accessible to SSRS. I guess I bought into the marketing hype a little too early :-).

SB

|||

Thanks for your feedback. The core scenario you seem to want to paste an arbitrary MDX statement into RS and have the RS engine format it. One question would be - where does your MDX come from? Are you crafting it by hand? We felt that most users are not MDX experts and would simply use the query builder (which will form the MDX in a conformant form).

Anyway, while supporting arbitrary MDX is certainly a valid scenario, it is simply not what RS was designed to do. In order to understand the data that is being returned from a source, SSRS must have a fixed schema. Without a schema, RS cannot compute subtotals or sort or filter the data. A cellset based tool relies fully on the back-end for all interactivity (which negates all of the RS caching layer). Tablix in 2008 does nothing to change this approach - in fact several of the Tablix functions (side by side groups, for example) are not even possible to express in MDX.

We do understand that people would like add this functionality to RS and we will consider supporting arbitrary schema and round trip the interactivity for post-2008 future version.

Integration between SSRS and SSAS

Any plans to improve the integration between Reporting Services and Analysis Services?

I‘ll try to be specific as possible. Below is a state of affairs for the SSRS and SSAS integration as of today (MS SQL Server 2005):

1. When querying Analysis Services cubes, the data can be returned in one of two ways: as a cellset or as a recordset. Reporting Services uses the recordset format, and as such, the data from Analysis Services goes through a flattening process to return the data in a two-dimensional grid.
   SSRS should accommodate SSAS native output format without flattening.
   This is obviously a very serious point of contention between SSRS and SSAS. This particular issue is affecting usefulness of the SSRS as reporting software for SSAS.
2. Quite often, depending on the MDX, SSRS produces the infamous error message “The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension”.
3. The error message above pushes to switch from the native "Microsoft SQL Server Analysis Services (AdomdClient)" to MS SQL Server 2005 Analysis Services 9.0 OLE DB Provider. However, OLEDB provider goes through the flattening process which negatively affects the received data overall format on the SSRS side.
   ADOMD native provider should support any kosher MDX statement. SSRS should be able to receive ADOMD output as is.
4. Graphical MDX Query Designer doesn’t accept manual changes tothe MDX.
   It should be 2-way communication.MDX Query Designer should analyze manually tweaked MDX and if it is legit accept it.

Hopefully, Tablix region as a UI control will be able to support native SSAS data outputs in MS SQL Server 2008.

I am talking here about straight reporting. I am not asking for the interactive OLAP client.

References:

1. Integrating Analysis Services with Reporting Services
   http://technet.microsoft.com/en-us/library/aa902647(sql.80).aspx
2. Rant: Reporting Services and Analysis Services
   http://cwebbbi.spaces.live.com/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!163.entry
3. The state of Reporting Services UDM (Analysis Services) integration
   http://peterkol.spaces.live.com/blog/cns!68755AEAC31F9A6C!185.entry

Regards,

Yitzhak Khabinsky

Brian Welcker/ Robert Bruckner: I hope that one of you can elaborate. This is a VERY important subject matter. Thank you.a

|||

I agree....from the very beginning I thought that SSRS's ability to work with cubes was a VERY important feature because it would allow access to the amazing aggregation power that cubes have...without this you have to spend lots of time writing complicated and bad performing SQL in order to put aggregates in your reports (and the fact is that reports are mostly about aggregating data). I would have thought this to be a simple, natural solution and am AMAZED that it doesn't work.

I actually work for a company where we currently use Cognos and Reporting Services, and one of the things I've told them is to consider using Analysis Services instead because then the cubes would be accessible to SSRS. I guess I bought into the marketing hype a little too early :-).

SB

|||

Thanks for your feedback. The core scenario you seem to want to paste an arbitrary MDX statement into RS and have the RS engine format it. One question would be - where does your MDX come from? Are you crafting it by hand? We felt that most users are not MDX experts and would simply use the query builder (which will form the MDX in a conformant form).

Anyway, while supporting arbitrary MDX is certainly a valid scenario, it is simply not what RS was designed to do. In order to understand the data that is being returned from a source, SSRS must have a fixed schema. Without a schema, RS cannot compute subtotals or sort or filter the data. A cellset based tool relies fully on the back-end for all interactivity (which negates all of the RS caching layer). Tablix in 2008 does nothing to change this approach - in fact several of the Tablix functions (side by side groups, for example) are not even possible to express in MDX.

We do understand that people would like add this functionality to RS and we will consider supporting arbitrary schema and round trip the interactivity for post-2008 future version.

Integration between SSRS and SSAS

Any plans to improve the integration between Reporting Services and Analysis Services?

I‘ll try to be specific as possible. Below is a state of affairs for the SSRS and SSAS integration as of today (MS SQL Server 2005):

1. When querying Analysis Services cubes, the data can be returned in one of two ways: as a cellset or as a recordset. Reporting Services uses the recordset format, and as such, the data from Analysis Services goes through a flattening process to return the data in a two-dimensional grid.
   SSRS should accommodate SSAS native output format without flattening.
   This is obviously a very serious point of contention between SSRS and SSAS. This particular issue is affecting usefulness of the SSRS as reporting software for SSAS.
2. Quite often, depending on the MDX, SSRS produces the infamous error message “The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension”.
3. The error message above pushes to switch from the native "Microsoft SQL Server Analysis Services (AdomdClient)" to MS SQL Server 2005 Analysis Services 9.0 OLE DB Provider. However, OLEDB provider goes through the flattening process which negatively affects the received data overall format on the SSRS side.
   ADOMD native provider should support any kosher MDX statement. SSRS should be able to receive ADOMD output as is.
4. Graphical MDX Query Designer doesn’t accept manual changes tothe MDX.
   It should be 2-way communication.MDX Query Designer should analyze manually tweaked MDX and if it is legit accept it.

Hopefully, Tablix region as a UI control will be able to support native SSAS data outputs in MS SQL Server 2008.

I am talking here about straight reporting. I am not asking for the interactive OLAP client.

References:

1. Integrating Analysis Services with Reporting Services
   http://technet.microsoft.com/en-us/library/aa902647(sql.80).aspx
2. Rant: Reporting Services and Analysis Services
   http://cwebbbi.spaces.live.com/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!163.entry
3. The state of Reporting Services UDM (Analysis Services) integration
   http://peterkol.spaces.live.com/blog/cns!68755AEAC31F9A6C!185.entry

Regards,

Yitzhak Khabinsky

Brian Welcker/ Robert Bruckner: I hope that one of you can elaborate. This is a VERY important subject matter. Thank you.a

|||

I agree....from the very beginning I thought that SSRS's ability to work with cubes was a VERY important feature because it would allow access to the amazing aggregation power that cubes have...without this you have to spend lots of time writing complicated and bad performing SQL in order to put aggregates in your reports (and the fact is that reports are mostly about aggregating data). I would have thought this to be a simple, natural solution and am AMAZED that it doesn't work.

I actually work for a company where we currently use Cognos and Reporting Services, and one of the things I've told them is to consider using Analysis Services instead because then the cubes would be accessible to SSRS. I guess I bought into the marketing hype a little too early :-).

SB

|||

Thanks for your feedback. The core scenario you seem to want to paste an arbitrary MDX statement into RS and have the RS engine format it. One question would be - where does your MDX come from? Are you crafting it by hand? We felt that most users are not MDX experts and would simply use the query builder (which will form the MDX in a conformant form).

Anyway, while supporting arbitrary MDX is certainly a valid scenario, it is simply not what RS was designed to do. In order to understand the data that is being returned from a source, SSRS must have a fixed schema. Without a schema, RS cannot compute subtotals or sort or filter the data. A cellset based tool relies fully on the back-end for all interactivity (which negates all of the RS caching layer). Tablix in 2008 does nothing to change this approach - in fact several of the Tablix functions (side by side groups, for example) are not even possible to express in MDX.

We do understand that people would like add this functionality to RS and we will consider supporting arbitrary schema and round trip the interactivity for post-2008 future version.