Hi,
I'm trying to figure out where the value of "Cost" comes from in the
Query Analyzer.
For example, when running this on Northwind:
select * from dbo.[Alphabetical list of products]
You get this 3 parts for the Query Plan. The part of
"Categories.PK_Categories" has a cost of 24% of the
total and a value of 0.0119... Where does this value
come from?
All I get is "cost for CPU" and "cost for I/O" - but adding
these together does not equal that value.
Anyone have a clue?
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
I doubt that anyone outside of Microsft, and maybe Kalen, could really
answer that question...
Not only does the optimizer estimate IO and CPU, it does some estimation
about how much physical IO is required based on some expectation of how much
of the data might be in memory... It also looks at the recent use of tables
to determine the level of locking for certain operations as well...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm trying to figure out where the value of "Cost" comes from in the
> Query Analyzer.
> For example, when running this on Northwind:
> select * from dbo.[Alphabetical list of products]
> You get this 3 parts for the Query Plan. The part of
> "Categories.PK_Categories" has a cost of 24% of the
> total and a value of 0.0119... Where does this value
> come from?
> All I get is "cost for CPU" and "cost for I/O" - but adding
> these together does not equal that value.
>
> Anyone have a clue?
> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
>
|||Hello Wayne,
> I doubt that anyone outside of Microsft, and maybe Kalen, could really
> answer that question...
> Not only does the optimizer estimate IO and CPU, it does some estimation
> about how much physical IO is required based on some expectation of how
much
> of the data might be in memory... It also looks at the recent use of
tables
> to determine the level of locking for certain operations as well...
Hmm - when extracting a plan via SHOWPLAN_ALL, I do get the estimates
of some of the info (IO/CPU), but I have no idea where the actual "cost"
comes
from.
I've been fiddling around with multiplying IO * "nr of executes" and CPU *
nr of
executes, but it doesn't really add up properly... :-/
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
[vbcol=seagreen]
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
> news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
SQL
>
Showing posts with label calculated. Show all posts
Showing posts with label calculated. Show all posts
Wednesday, March 7, 2012
Query Cost - how is it calculated?
Hi,
I'm trying to figure out where the value of "Cost" comes from in the
Query Analyzer.
For example, when running this on Northwind:
select * from dbo.[Alphabetical list of products]
You get this 3 parts for the Query Plan. The part of
"Categories.PK_Categories" has a cost of 24% of the
total and a value of 0.0119... Where does this value
come from?
All I get is "cost for CPU" and "cost for I/O" - but adding
these together does not equal that value.
Anyone have a clue?
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.comI doubt that anyone outside of Microsft, and maybe Kalen, could really
answer that question...
Not only does the optimizer estimate IO and CPU, it does some estimation
about how much physical IO is required based on some expectation of how much
of the data might be in memory... It also looks at the recent use of tables
to determine the level of locking for certain operations as well...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm trying to figure out where the value of "Cost" comes from in the
> Query Analyzer.
> For example, when running this on Northwind:
> select * from dbo.[Alphabetical list of products]
> You get this 3 parts for the Query Plan. The part of
> "Categories.PK_Categories" has a cost of 24% of the
> total and a value of 0.0119... Where does this value
> come from?
> All I get is "cost for CPU" and "cost for I/O" - but adding
> these together does not equal that value.
>
> Anyone have a clue?
> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
>|||Hello Wayne,
> I doubt that anyone outside of Microsft, and maybe Kalen, could really
> answer that question...
> Not only does the optimizer estimate IO and CPU, it does some estimation
> about how much physical IO is required based on some expectation of how
much
> of the data might be in memory... It also looks at the recent use of
tables
> to determine the level of locking for certain operations as well...
Hmm - when extracting a plan via SHOWPLAN_ALL, I do get the estimates
of some of the info (IO/CPU), but I have no idea where the actual "cost"
comes
from.
I've been fiddling around with multiplying IO * "nr of executes" and CPU *
nr of
executes, but it doesn't really add up properly... :-/
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
> news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > I'm trying to figure out where the value of "Cost" comes from in the
> > Query Analyzer.
> >
> > For example, when running this on Northwind:
> >
> > select * from dbo.[Alphabetical list of products]
> >
> > You get this 3 parts for the Query Plan. The part of
> > "Categories.PK_Categories" has a cost of 24% of the
> > total and a value of 0.0119... Where does this value
> > come from?
> >
> > All I get is "cost for CPU" and "cost for I/O" - but adding
> > these together does not equal that value.
> >
> >
> > Anyone have a clue?
> >
> > --
> > With regards,
> >
> > Martijn Tonies
> > Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS
SQL
> > Server
> > Upscene Productions
> > http://www.upscene.com
> >
> >
> >
>
I'm trying to figure out where the value of "Cost" comes from in the
Query Analyzer.
For example, when running this on Northwind:
select * from dbo.[Alphabetical list of products]
You get this 3 parts for the Query Plan. The part of
"Categories.PK_Categories" has a cost of 24% of the
total and a value of 0.0119... Where does this value
come from?
All I get is "cost for CPU" and "cost for I/O" - but adding
these together does not equal that value.
Anyone have a clue?
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.comI doubt that anyone outside of Microsft, and maybe Kalen, could really
answer that question...
Not only does the optimizer estimate IO and CPU, it does some estimation
about how much physical IO is required based on some expectation of how much
of the data might be in memory... It also looks at the recent use of tables
to determine the level of locking for certain operations as well...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm trying to figure out where the value of "Cost" comes from in the
> Query Analyzer.
> For example, when running this on Northwind:
> select * from dbo.[Alphabetical list of products]
> You get this 3 parts for the Query Plan. The part of
> "Categories.PK_Categories" has a cost of 24% of the
> total and a value of 0.0119... Where does this value
> come from?
> All I get is "cost for CPU" and "cost for I/O" - but adding
> these together does not equal that value.
>
> Anyone have a clue?
> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
>|||Hello Wayne,
> I doubt that anyone outside of Microsft, and maybe Kalen, could really
> answer that question...
> Not only does the optimizer estimate IO and CPU, it does some estimation
> about how much physical IO is required based on some expectation of how
much
> of the data might be in memory... It also looks at the recent use of
tables
> to determine the level of locking for certain operations as well...
Hmm - when extracting a plan via SHOWPLAN_ALL, I do get the estimates
of some of the info (IO/CPU), but I have no idea where the actual "cost"
comes
from.
I've been fiddling around with multiplying IO * "nr of executes" and CPU *
nr of
executes, but it doesn't really add up properly... :-/
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
> news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > I'm trying to figure out where the value of "Cost" comes from in the
> > Query Analyzer.
> >
> > For example, when running this on Northwind:
> >
> > select * from dbo.[Alphabetical list of products]
> >
> > You get this 3 parts for the Query Plan. The part of
> > "Categories.PK_Categories" has a cost of 24% of the
> > total and a value of 0.0119... Where does this value
> > come from?
> >
> > All I get is "cost for CPU" and "cost for I/O" - but adding
> > these together does not equal that value.
> >
> >
> > Anyone have a clue?
> >
> > --
> > With regards,
> >
> > Martijn Tonies
> > Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS
SQL
> > Server
> > Upscene Productions
> > http://www.upscene.com
> >
> >
> >
>
Query Cost - how is it calculated?
Hi,
I'm trying to figure out where the value of "Cost" comes from in the
Query Analyzer.
For example, when running this on Northwind:
select * from dbo.[Alphabetical list of products]
You get this 3 parts for the Query Plan. The part of
"Categories.PK_Categories" has a cost of 24% of the
total and a value of 0.0119... Where does this value
come from?
All I get is "cost for CPU" and "cost for I/O" - but adding
these together does not equal that value.
Anyone have a clue?
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.comI doubt that anyone outside of Microsft, and maybe Kalen, could really
answer that question...
Not only does the optimizer estimate IO and CPU, it does some estimation
about how much physical IO is required based on some expectation of how much
of the data might be in memory... It also looks at the recent use of tables
to determine the level of locking for certain operations as well...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm trying to figure out where the value of "Cost" comes from in the
> Query Analyzer.
> For example, when running this on Northwind:
> select * from dbo.[Alphabetical list of products]
> You get this 3 parts for the Query Plan. The part of
> "Categories.PK_Categories" has a cost of 24% of the
> total and a value of 0.0119... Where does this value
> come from?
> All I get is "cost for CPU" and "cost for I/O" - but adding
> these together does not equal that value.
>
> Anyone have a clue?
> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
>|||Hello Wayne,
> I doubt that anyone outside of Microsft, and maybe Kalen, could really
> answer that question...
> Not only does the optimizer estimate IO and CPU, it does some estimation
> about how much physical IO is required based on some expectation of how
much
> of the data might be in memory... It also looks at the recent use of
tables
> to determine the level of locking for certain operations as well...
Hmm - when extracting a plan via SHOWPLAN_ALL, I do get the estimates
of some of the info (IO/CPU), but I have no idea where the actual "cost"
comes
from.
I've been fiddling around with multiplying IO * "nr of executes" and CPU *
nr of
executes, but it doesn't really add up properly... :-/
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
> news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
>
I'm trying to figure out where the value of "Cost" comes from in the
Query Analyzer.
For example, when running this on Northwind:
select * from dbo.[Alphabetical list of products]
You get this 3 parts for the Query Plan. The part of
"Categories.PK_Categories" has a cost of 24% of the
total and a value of 0.0119... Where does this value
come from?
All I get is "cost for CPU" and "cost for I/O" - but adding
these together does not equal that value.
Anyone have a clue?
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.comI doubt that anyone outside of Microsft, and maybe Kalen, could really
answer that question...
Not only does the optimizer estimate IO and CPU, it does some estimation
about how much physical IO is required based on some expectation of how much
of the data might be in memory... It also looks at the recent use of tables
to determine the level of locking for certain operations as well...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm trying to figure out where the value of "Cost" comes from in the
> Query Analyzer.
> For example, when running this on Northwind:
> select * from dbo.[Alphabetical list of products]
> You get this 3 parts for the Query Plan. The part of
> "Categories.PK_Categories" has a cost of 24% of the
> total and a value of 0.0119... Where does this value
> come from?
> All I get is "cost for CPU" and "cost for I/O" - but adding
> these together does not equal that value.
>
> Anyone have a clue?
> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
>|||Hello Wayne,
> I doubt that anyone outside of Microsft, and maybe Kalen, could really
> answer that question...
> Not only does the optimizer estimate IO and CPU, it does some estimation
> about how much physical IO is required based on some expectation of how
much
> of the data might be in memory... It also looks at the recent use of
tables
> to determine the level of locking for certain operations as well...
Hmm - when extracting a plan via SHOWPLAN_ALL, I do get the estimates
of some of the info (IO/CPU), but I have no idea where the actual "cost"
comes
from.
I've been fiddling around with multiplying IO * "nr of executes" and CPU *
nr of
executes, but it doesn't really add up properly... :-/
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
> news:ekzghVpZFHA.3040@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
>
Query calculated OLAP members
I'm trying to create a report using an OLAP cube as my data source, but I can
retrieve only the dimensions and measures, not the calculated members.
My query is:
select * from MasterCube
Is there any way to extract them?Below is a sample MDX query (used in the sample Foodmart reporting services
report) which includes a calculated measure, Store Cost.
SELECT { [Measures].[Store Sales], [Measures].[Store Cost] } ON COLUMNS,
{ Descendants([Product].[All Products], [Product].[Brand Name], LEAVES) }
ON ROWS,
{ Time.[1997].[Q1],Time.[1997].[Q2],Time.[1997].[Q3],Time.[1997].[Q4] } ON
PAGES
FROM Sales
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Cesar Meza" <CesarMeza@.discussions.microsoft.com> wrote in message
news:0A29D2FD-F3CF-4772-A063-E585C6B1DDA3@.microsoft.com...
> I'm trying to create a report using an OLAP cube as my data source, but I
> can
> retrieve only the dimensions and measures, not the calculated members.
> My query is:
> select * from MasterCube
> Is there any way to extract them?|||The real issue this: I have a cube that is too big for Excel's Pivot table to
handle it. So I'm trying to export it as PDF report using RS.
So I have several formulas that rely on the Analysis Services functionalliy
(aggregates, ranking, etc.) that are already in the cube.
I know I can write the MDX statement to recreate them, but this will mean to
re-create all the calculated members once more in the statement.
Any suggestions?.
Thanks a lot for your response.
retrieve only the dimensions and measures, not the calculated members.
My query is:
select * from MasterCube
Is there any way to extract them?Below is a sample MDX query (used in the sample Foodmart reporting services
report) which includes a calculated measure, Store Cost.
SELECT { [Measures].[Store Sales], [Measures].[Store Cost] } ON COLUMNS,
{ Descendants([Product].[All Products], [Product].[Brand Name], LEAVES) }
ON ROWS,
{ Time.[1997].[Q1],Time.[1997].[Q2],Time.[1997].[Q3],Time.[1997].[Q4] } ON
PAGES
FROM Sales
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Cesar Meza" <CesarMeza@.discussions.microsoft.com> wrote in message
news:0A29D2FD-F3CF-4772-A063-E585C6B1DDA3@.microsoft.com...
> I'm trying to create a report using an OLAP cube as my data source, but I
> can
> retrieve only the dimensions and measures, not the calculated members.
> My query is:
> select * from MasterCube
> Is there any way to extract them?|||The real issue this: I have a cube that is too big for Excel's Pivot table to
handle it. So I'm trying to export it as PDF report using RS.
So I have several formulas that rely on the Analysis Services functionalliy
(aggregates, ranking, etc.) that are already in the cube.
I know I can write the MDX statement to recreate them, but this will mean to
re-create all the calculated members once more in the statement.
Any suggestions?.
Thanks a lot for your response.
Query Calculated Member - Member Properties
I have an urgent problem with some queries including calculated members where I want to filter by properties..
I have rewriten my query to show my issue on the Adventure Works Database.
Code Snippet
with
member [Account].[Accounts].[TEST] as 0
/* create a new query calculated member in the hierarchy */
select
{} on columns,
{
filter(
{
descendants([Account].[Accounts].&[1], 2)
,[Account].[Accounts].[TEST]
/* use the new calculated member */
/* comment out the calc member to see successfull execution */
},
[Account].[Accounts].CurrentMember.Properties("Account Type")="Assets"
/* filter by a member property which does not exists on the query calculated member - error */
)
}
dimension properties
[Account].[Accounts].[Account Type],
[Account].[Accounts].[Account Number],
[Account].[Accounts].[Accounts]
on rows
from
[Adventure Works]
I create a calcuated meber - and then I want to use this in a filter by member property. The Server always raises the error that the property does not exists.
How to solve this error?
The Documentation http://technet.microsoft.com/en-us/library/ms146017.aspx specify a possiblity to specify the property for the member - but I was not able to this - like documented It has no effect.
I need some urgent Help, HANNES
If you want filter to remove the calculated member then use stripcalculatedmembers function to remove all calculated members from the 1st argument. If you want filter to retain the calculated member then use the iserror function like below:
with
member [Account].[Accounts].[TEST] as 0
/* create a new query calculated member in the hierarchy */
select
{} on columns,
{
filter(
{
descendants([Account].[Accounts].&[1], 2)
,[Account].[Accounts].[TEST]
},
iserror([Account].[Accounts].CurrentMember.Properties("Account Type")) or [Account].[Accounts].CurrentMember.Properties("Account Type")="Assets"
/* filter by a member property which does not exists on the query calculated member - error */
)
}
dimension properties
[Account].[Accounts].[Account Type],
[Account].[Accounts].[Account Number],
[Account].[Accounts].[Accounts]
on rows
from
[Adventure Works]
|||Hi,
my question - how do I proper specify a mdx property in the query calculated member.
I do not want to catch errors - nor produce errors - howto specifiy the property value in the query.
The documentation at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2126305&SiteID=1 mentions a syntax, but I can not get it work.
If I properly read the doc I would write it like
Code Snippet
with
member [Account].[Accounts].[TEST] as 0 ([Account].[Accounts].[Account Type]="Assets")
/* create a new query calculated member in the hierarchy */
select
But it dos not work.
Any ideas howto write?
HANNES
|||Calculated members don't support custom member properties. The document you mentioned shows you how to use pre-defined member properties, such as FORMAT_STRING, for calculated members.Query Calculated Member - Member Properties
I have an urgent problem with some queries including calculated members where I want to filter by properties..
I have rewriten my query to show my issue on the Adventure Works Database.
Code Snippet
with
member [Account].[Accounts].[TEST] as 0
/* create a new query calculated member in the hierarchy */
select
{} on columns,
{
filter(
{
descendants([Account].[Accounts].&[1], 2)
,[Account].[Accounts].[TEST]
/* use the new calculated member */
/* comment out the calc member to see successfull execution */
},
[Account].[Accounts].CurrentMember.Properties("Account Type")="Assets"
/* filter by a member property which does not exists on the query calculated member - error */
)
}
dimension properties
[Account].[Accounts].[Account Type],
[Account].[Accounts].[Account Number],
[Account].[Accounts].[Accounts]
on rows
from
[Adventure Works]
I create a calcuated meber - and then I want to use this in a filter by member property. The Server always raises the error that the property does not exists.
How to solve this error?
The Documentation http://technet.microsoft.com/en-us/library/ms146017.aspx specify a possiblity to specify the property for the member - but I was not able to this - like documented It has no effect.
I need some urgent Help, HANNES
If you want filter to remove the calculated member then use stripcalculatedmembers function to remove all calculated members from the 1st argument. If you want filter to retain the calculated member then use the iserror function like below:
with
member [Account].[Accounts].[TEST] as 0
/* create a new query calculated member in the hierarchy */
select
{} on columns,
{
filter(
{
descendants([Account].[Accounts].&[1], 2)
,[Account].[Accounts].[TEST]
},
iserror([Account].[Accounts].CurrentMember.Properties("Account Type")) or [Account].[Accounts].CurrentMember.Properties("Account Type")="Assets"
/* filter by a member property which does not exists on the query calculated member - error */
)
}
dimension properties
[Account].[Accounts].[Account Type],
[Account].[Accounts].[Account Number],
[Account].[Accounts].[Accounts]
on rows
from
[Adventure Works]
|||Hi,
my question - how do I proper specify a mdx property in the query calculated member.
I do not want to catch errors - nor produce errors - howto specifiy the property value in the query.
The documentation at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2126305&SiteID=1 mentions a syntax, but I can not get it work.
If I properly read the doc I would write it like
Code Snippet
with
member [Account].[Accounts].[TEST] as 0 ([Account].[Accounts].[Account Type]="Assets")
/* create a new query calculated member in the hierarchy */
select
But it dos not work.
Any ideas howto write?
HANNES
|||Calculated members don't support custom member properties. The document you mentioned shows you how to use pre-defined member properties, such as FORMAT_STRING, for calculated members.
Subscribe to:
Posts (Atom)