When I try to create a custom xml datatype I get the following error:
CREATE TYPE myOwnXMLType FROM xml;
Msg 15226, Level 16, State 1, Line 1
Cannot create user defined types from an XML datatype.
Is there anything I can do ? Other types work fine (int, nvarchar(max)
etc.). I use SQL Server 2005 Standard Edition.
Alex Voit wrote:
> When I try to create a custom xml datatype I get the following error:
> CREATE TYPE myOwnXMLType FROM xml;
> Msg 15226, Level 16, State 1, Line 1
> Cannot create user defined types from an XML datatype.
> Is there anything I can do ? Other types work fine (int, nvarchar(max)
> etc.). I use SQL Server 2005 Standard Edition.
The documentation lists the following allowed base types:
bigint
binary(n)
bit
char(n)
datetime
decimal
float
image
int
money
nchar(n)
ntext
numeric
nvarchar(n | max)
real
smalldatetime
smallint
smallmoney
sql_variant
text
tinyint
uniqueidentifier
varbinary(n | max)
varchar(n | max)
The xml data type is not listed as an allowed base type for CREATE TYPE
FROM.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||We did not allow CREATE TYPE on the XML datatype. Please file a request at
http://connect.microsoft.com/sqlserver/feedback if this is a functionality
that you need.
Best regards
Michael
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:euWAnmrpHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Alex Voit wrote:
> The documentation lists the following allowed base types:
> bigint
> binary(n)
> bit
> char(n)
> datetime
> decimal
> float
> image
> int
> money
> nchar(n)
> ntext
> numeric
> nvarchar(n | max)
> real
> smalldatetime
> smallint
> smallmoney
> sql_variant
> text
> tinyint
> uniqueidentifier
> varbinary(n | max)
> varchar(n | max)
>
> The xml data type is not listed as an allowed base type for CREATE TYPE
> FROM.
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
Showing posts with label level. Show all posts
Showing posts with label level. Show all posts
Thursday, March 22, 2012
custom xml data type
When I try to create a custom xml datatype I get the following error:
CREATE TYPE myOwnXMLType FROM xml;
Msg 15226, Level 16, State 1, Line 1
Cannot create user defined types from an XML datatype.
Is there anything I can do ? Other types work fine (int, nvarchar(max)
etc.). I use SQL Server 2005 Standard Edition.Alex Voit wrote:
> When I try to create a custom xml datatype I get the following error:
> CREATE TYPE myOwnXMLType FROM xml;
> Msg 15226, Level 16, State 1, Line 1
> Cannot create user defined types from an XML datatype.
> Is there anything I can do ? Other types work fine (int, nvarchar(max)
> etc.). I use SQL Server 2005 Standard Edition.
The documentation lists the following allowed base types:
bigint
binary(n)
bit
char(n)
datetime
decimal
float
image
int
money
nchar(n)
ntext
numeric
nvarchar(n | max)
real
smalldatetime
smallint
smallmoney
sql_variant
text
tinyint
uniqueidentifier
varbinary(n | max)
varchar(n | max)
The xml data type is not listed as an allowed base type for CREATE TYPE
FROM.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||We did not allow CREATE TYPE on the XML datatype. Please file a request at
http://connect.microsoft.com/sqlserver/feedback if this is a functionality
that you need.
Best regards
Michael
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:euWAnmrpHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Alex Voit wrote:
> The documentation lists the following allowed base types:
> bigint
> binary(n)
> bit
> char(n)
> datetime
> decimal
> float
> image
> int
> money
> nchar(n)
> ntext
> numeric
> nvarchar(n | max)
> real
> smalldatetime
> smallint
> smallmoney
> sql_variant
> text
> tinyint
> uniqueidentifier
> varbinary(n | max)
> varchar(n | max)
>
> The xml data type is not listed as an allowed base type for CREATE TYPE
> FROM.
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
CREATE TYPE myOwnXMLType FROM xml;
Msg 15226, Level 16, State 1, Line 1
Cannot create user defined types from an XML datatype.
Is there anything I can do ? Other types work fine (int, nvarchar(max)
etc.). I use SQL Server 2005 Standard Edition.Alex Voit wrote:
> When I try to create a custom xml datatype I get the following error:
> CREATE TYPE myOwnXMLType FROM xml;
> Msg 15226, Level 16, State 1, Line 1
> Cannot create user defined types from an XML datatype.
> Is there anything I can do ? Other types work fine (int, nvarchar(max)
> etc.). I use SQL Server 2005 Standard Edition.
The documentation lists the following allowed base types:
bigint
binary(n)
bit
char(n)
datetime
decimal
float
image
int
money
nchar(n)
ntext
numeric
nvarchar(n | max)
real
smalldatetime
smallint
smallmoney
sql_variant
text
tinyint
uniqueidentifier
varbinary(n | max)
varchar(n | max)
The xml data type is not listed as an allowed base type for CREATE TYPE
FROM.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||We did not allow CREATE TYPE on the XML datatype. Please file a request at
http://connect.microsoft.com/sqlserver/feedback if this is a functionality
that you need.
Best regards
Michael
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:euWAnmrpHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Alex Voit wrote:
> The documentation lists the following allowed base types:
> bigint
> binary(n)
> bit
> char(n)
> datetime
> decimal
> float
> image
> int
> money
> nchar(n)
> ntext
> numeric
> nvarchar(n | max)
> real
> smalldatetime
> smallint
> smallmoney
> sql_variant
> text
> tinyint
> uniqueidentifier
> varbinary(n | max)
> varchar(n | max)
>
> The xml data type is not listed as an allowed base type for CREATE TYPE
> FROM.
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
Monday, March 19, 2012
Custom Row Level Security
Hi,
We have developed a 3-tier application and have developed our own method of
handling row level data security. Basically our application passes all
ad-hoc SQL queries through an object that replaces table names with view
names. Each user is assigned a role which has a view defined for it. For
example, issuing the statement:
SELECT FIRSTNAME FROM PEOPLE
would send the follow statement to the database:
SELECT FIRSTNAME FROM OC_LINEMANAGER_PEOPLE_READ
Where OC_LINEMANAGER_PEOPLE_READ is the name of a view in our SQL Server
database.
Obviously we want to apply the same security to users when accessing our
database using Report Services. The only way I can see of doing this is
that we parse SQL statements issued from Reporting Services and modify them
to use our view names.
Can we write a Data Processing Extension to do this? Would this apply to
the results returned in the visual query designer in the embedded report
writer in VS.NET?
Thanks
PeterI should probably hesitate to answer this, because I am not certain, but I
beleive that you can do what you wish with an extension. I suspect that it
would also apply to the generic designer, but I do not know how the DaVinci
toolkit designer would respond... Perhaps someone else will jump in .
Something which others have done is, instead of having a view for each user,
which might not scale well and is probably a management problem... Create a
mapping table with the user name and the keys the user can see, then join
the mapping table back to the orig table... this is essentially putting your
where clauses from all of your views into a table... Then the suser_sname()
or user! values can be used in the query...
--
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
"Peter Sedman" <psedman@.community.nospam> wrote in message
news:%238aGVTpEFHA.1408@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have developed a 3-tier application and have developed our own method
> of
> handling row level data security. Basically our application passes all
> ad-hoc SQL queries through an object that replaces table names with view
> names. Each user is assigned a role which has a view defined for it. For
> example, issuing the statement:
> SELECT FIRSTNAME FROM PEOPLE
> would send the follow statement to the database:
> SELECT FIRSTNAME FROM OC_LINEMANAGER_PEOPLE_READ
> Where OC_LINEMANAGER_PEOPLE_READ is the name of a view in our SQL Server
> database.
> Obviously we want to apply the same security to users when accessing our
> database using Report Services. The only way I can see of doing this is
> that we parse SQL statements issued from Reporting Services and modify
> them
> to use our view names.
> Can we write a Data Processing Extension to do this? Would this apply to
> the results returned in the visual query designer in the embedded report
> writer in VS.NET?
> Thanks
> Peter
>|||Hi Wayne,
Thanks for the reply. We are creating views for roles rather than users, so
if we have say 10,000 users there may only be 20 roles.
If you use a mapping table you would still need to use a view in order to
package the table to the user (the view would join the two tables).
I think the best thing we can do is to experiment with the DPE and see what
we come up with.
What is the DaVinci toolkit designer?
Thanks,
Peter|||You could still do the join to a table of 10,000 rows that has user names
and groups, and filter on user name. That's a relatively inexpensive join.
Another possibility is to use a custom assembly, and make all of your SQL
dynamic rather than static. For example:
=code.Parse("SELECT FIRSTNAME FROM PEOPLE")
rather than just the SQL text in the value.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Peter Sedman" <psedman@.community.nospam> wrote in message
news:u%23k3IZqEFHA.392@.TK2MSFTNGP14.phx.gbl...
> Hi Wayne,
> Thanks for the reply. We are creating views for roles rather than users,
> so
> if we have say 10,000 users there may only be 20 roles.
> If you use a mapping table you would still need to use a view in order to
> package the table to the user (the view would join the two tables).
> I think the best thing we can do is to experiment with the DPE and see
> what
> we come up with.
> What is the DaVinci toolkit designer?
> Thanks,
> Peter
>
We have developed a 3-tier application and have developed our own method of
handling row level data security. Basically our application passes all
ad-hoc SQL queries through an object that replaces table names with view
names. Each user is assigned a role which has a view defined for it. For
example, issuing the statement:
SELECT FIRSTNAME FROM PEOPLE
would send the follow statement to the database:
SELECT FIRSTNAME FROM OC_LINEMANAGER_PEOPLE_READ
Where OC_LINEMANAGER_PEOPLE_READ is the name of a view in our SQL Server
database.
Obviously we want to apply the same security to users when accessing our
database using Report Services. The only way I can see of doing this is
that we parse SQL statements issued from Reporting Services and modify them
to use our view names.
Can we write a Data Processing Extension to do this? Would this apply to
the results returned in the visual query designer in the embedded report
writer in VS.NET?
Thanks
PeterI should probably hesitate to answer this, because I am not certain, but I
beleive that you can do what you wish with an extension. I suspect that it
would also apply to the generic designer, but I do not know how the DaVinci
toolkit designer would respond... Perhaps someone else will jump in .
Something which others have done is, instead of having a view for each user,
which might not scale well and is probably a management problem... Create a
mapping table with the user name and the keys the user can see, then join
the mapping table back to the orig table... this is essentially putting your
where clauses from all of your views into a table... Then the suser_sname()
or user! values can be used in the query...
--
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
"Peter Sedman" <psedman@.community.nospam> wrote in message
news:%238aGVTpEFHA.1408@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have developed a 3-tier application and have developed our own method
> of
> handling row level data security. Basically our application passes all
> ad-hoc SQL queries through an object that replaces table names with view
> names. Each user is assigned a role which has a view defined for it. For
> example, issuing the statement:
> SELECT FIRSTNAME FROM PEOPLE
> would send the follow statement to the database:
> SELECT FIRSTNAME FROM OC_LINEMANAGER_PEOPLE_READ
> Where OC_LINEMANAGER_PEOPLE_READ is the name of a view in our SQL Server
> database.
> Obviously we want to apply the same security to users when accessing our
> database using Report Services. The only way I can see of doing this is
> that we parse SQL statements issued from Reporting Services and modify
> them
> to use our view names.
> Can we write a Data Processing Extension to do this? Would this apply to
> the results returned in the visual query designer in the embedded report
> writer in VS.NET?
> Thanks
> Peter
>|||Hi Wayne,
Thanks for the reply. We are creating views for roles rather than users, so
if we have say 10,000 users there may only be 20 roles.
If you use a mapping table you would still need to use a view in order to
package the table to the user (the view would join the two tables).
I think the best thing we can do is to experiment with the DPE and see what
we come up with.
What is the DaVinci toolkit designer?
Thanks,
Peter|||You could still do the join to a table of 10,000 rows that has user names
and groups, and filter on user name. That's a relatively inexpensive join.
Another possibility is to use a custom assembly, and make all of your SQL
dynamic rather than static. For example:
=code.Parse("SELECT FIRSTNAME FROM PEOPLE")
rather than just the SQL text in the value.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Peter Sedman" <psedman@.community.nospam> wrote in message
news:u%23k3IZqEFHA.392@.TK2MSFTNGP14.phx.gbl...
> Hi Wayne,
> Thanks for the reply. We are creating views for roles rather than users,
> so
> if we have say 10,000 users there may only be 20 roles.
> If you use a mapping table you would still need to use a view in order to
> package the table to the user (the view would join the two tables).
> I think the best thing we can do is to experiment with the DPE and see
> what
> we come up with.
> What is the DaVinci toolkit designer?
> Thanks,
> Peter
>
Custom Rollup formula and All Member Formula property
For SSAS 2000 ,to specify a custom rollup formula in any level except the All Level, the Custom Rollup Formula property of the level has to be used.
Then to specify it for the All Level the All Member Formula property of the dimension has to be used.
But at SSAS 2005, to specify it for the All Level , I dont know where is the All Member Formula property of the dimension .
Anyone know where is the All Member Formula property of the dimension at SSAS 2005.
Thanks
How about making a cube MDX script assignment to the Root() of the dimension?
SQL Server 2005 Books Online
Working with Custom Member Formulas
May I know why the CustomRollupColumn property or UnaryOperatorColumn property after configure can not be remove if I dont want it to be done this way. I tried to remove from table ID and columnid as show below, but I not see none or any other setting to remove it .I only able to change the setting , but not remove the setting.
UnaryOperatorColumn
- Sources
--TableID
--ColumnID
CustomRollupColumn
- Sources
--TableID
--ColumnID
For this moment,I just able have to delete the dimension and recreate it again because of fail to remove the above property setting.
Anyone know how to remove the setting.Thanks
|||
Anyone know about above question?
Appreciate that anyone able to provide opnion.
Thanks
Thursday, March 8, 2012
Custom Export Format List in Rendered ReportViewer Control...
I know that at the server level you can suppress the export types that
appear in the export format list control when a report is rendered. Some of
my reports need to only display a subset of the server defined export types.
An example would probably best help in my issue... I need to display one
report with options to export being Excel and PDF. With another report I
need Excel and CSV. How can I set the export list control to only display
export types required for a specific report?
Thanks in advance.
--
Bob MorvayThat is not supported in the current product, it is controlled for the
entire app.
--
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
"Bob Morvay" <adamantiumrocks@.hotmail.com> wrote in message
news:ejFlBLNeFHA.2664@.TK2MSFTNGP15.phx.gbl...
>I know that at the server level you can suppress the export types that
>appear in the export format list control when a report is rendered. Some
>of my reports need to only display a subset of the server defined export
>types. An example would probably best help in my issue... I need to
>display one report with options to export being Excel and PDF. With
>another report I need Excel and CSV. How can I set the export list control
>to only display export types required for a specific report?
> Thanks in advance.
> --
> Bob Morvay
>|||Well that sucks :) Thanks for responding.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eaA4oEleFHA.2584@.tk2msftngp13.phx.gbl...
> That is not supported in the current product, it is controlled for the
> entire app.
> --
> 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
> "Bob Morvay" <adamantiumrocks@.hotmail.com> wrote in message
> news:ejFlBLNeFHA.2664@.TK2MSFTNGP15.phx.gbl...
>>I know that at the server level you can suppress the export types that
>>appear in the export format list control when a report is rendered. Some
>>of my reports need to only display a subset of the server defined export
>>types. An example would probably best help in my issue... I need to
>>display one report with options to export being Excel and PDF. With
>>another report I need Excel and CSV. How can I set the export list
>>control to only display export types required for a specific report?
>> Thanks in advance.
>> --
>> Bob Morvay
>>
>
appear in the export format list control when a report is rendered. Some of
my reports need to only display a subset of the server defined export types.
An example would probably best help in my issue... I need to display one
report with options to export being Excel and PDF. With another report I
need Excel and CSV. How can I set the export list control to only display
export types required for a specific report?
Thanks in advance.
--
Bob MorvayThat is not supported in the current product, it is controlled for the
entire app.
--
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
"Bob Morvay" <adamantiumrocks@.hotmail.com> wrote in message
news:ejFlBLNeFHA.2664@.TK2MSFTNGP15.phx.gbl...
>I know that at the server level you can suppress the export types that
>appear in the export format list control when a report is rendered. Some
>of my reports need to only display a subset of the server defined export
>types. An example would probably best help in my issue... I need to
>display one report with options to export being Excel and PDF. With
>another report I need Excel and CSV. How can I set the export list control
>to only display export types required for a specific report?
> Thanks in advance.
> --
> Bob Morvay
>|||Well that sucks :) Thanks for responding.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eaA4oEleFHA.2584@.tk2msftngp13.phx.gbl...
> That is not supported in the current product, it is controlled for the
> entire app.
> --
> 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
> "Bob Morvay" <adamantiumrocks@.hotmail.com> wrote in message
> news:ejFlBLNeFHA.2664@.TK2MSFTNGP15.phx.gbl...
>>I know that at the server level you can suppress the export types that
>>appear in the export format list control when a report is rendered. Some
>>of my reports need to only display a subset of the server defined export
>>types. An example would probably best help in my issue... I need to
>>display one report with options to export being Excel and PDF. With
>>another report I need Excel and CSV. How can I set the export list
>>control to only display export types required for a specific report?
>> Thanks in advance.
>> --
>> Bob Morvay
>>
>
Friday, February 17, 2012
cursors problem
hi i am getting that message to update field value into table
Msg 16933, Level 16, State 1, Procedure RmDupValWSWCode, Line 39
The cursor does not include the table being modified or the table is not
updatable through the cursor.
I have two databases with same tables, in one database it work on that table
but in another database its not working on the same table with the same
data......
why cursor behave like thatHello!
Could you post your code?
Thanks!|||Since you haven't posted the code. I am guessing :)
are you using the two part name where ever you refer to the table.
I mean refer to all the table names as
dbo.table1
or whichever schema you are using.
Hope this helps.|||Also, make sure the user running the stored procedure has access to the
tables.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:2919D9C0-492D-4582-89B0-BEDABA2B0E6E@.microsoft.com...
> Since you haven't posted the code. I am guessing :)
> are you using the two part name where ever you refer to the table.
> I mean refer to all the table names as
> dbo.table1
> or whichever schema you are using.
> Hope this helps.|||Hey Jim..
long time no clashes.. was missing you :)
"Jim Underwood" wrote:
> Also, make sure the user running the stored procedure has access to the
> tables.
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:2919D9C0-492D-4582-89B0-BEDABA2B0E6E@.microsoft.com...
>
>|||Lol. Yeah, you missed an entire day last w
. All day long I was looking
for some posts from you. You usually have a few creative and thought
provoking solutions. I think today's is the negative flag * 2 +1 to get
either a positive or negative number. I had to get some caffeine before I
could figure it out.
Actually, I was hoping to see if you had a mathematical formula to solve the
matrix (Matriz) problem posted yesterday. The best I could come up with was
a mapping table. I couldn't seem to find a mathematical approach to the
problem, so I went with simple brute force.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:6FEE9DDC-CEBC-46B8-8AE1-17E1CCEE47A1@.microsoft.com...
> Hey Jim..
> long time no clashes.. was missing you :)
> "Jim Underwood" wrote:
>|||Gee...Thanks for the complments..
Well network was down yesterday.. and didn't see the post on matrix.. will
look into it now :)
"Jim Underwood" wrote:
> Lol. Yeah, you missed an entire day last w. All day long I was lookin
g
> for some posts from you. You usually have a few creative and thought
> provoking solutions. I think today's is the negative flag * 2 +1 to get
> either a positive or negative number. I had to get some caffeine before I
> could figure it out.
> Actually, I was hoping to see if you had a mathematical formula to solve t
he
> matrix (Matriz) problem posted yesterday. The best I could come up with w
as
> a mapping table. I couldn't seem to find a mathematical approach to the
> problem, so I went with simple brute force.
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:6FEE9DDC-CEBC-46B8-8AE1-17E1CCEE47A1@.microsoft.com...
>
>
Msg 16933, Level 16, State 1, Procedure RmDupValWSWCode, Line 39
The cursor does not include the table being modified or the table is not
updatable through the cursor.
I have two databases with same tables, in one database it work on that table
but in another database its not working on the same table with the same
data......
why cursor behave like thatHello!
Could you post your code?
Thanks!|||Since you haven't posted the code. I am guessing :)
are you using the two part name where ever you refer to the table.
I mean refer to all the table names as
dbo.table1
or whichever schema you are using.
Hope this helps.|||Also, make sure the user running the stored procedure has access to the
tables.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:2919D9C0-492D-4582-89B0-BEDABA2B0E6E@.microsoft.com...
> Since you haven't posted the code. I am guessing :)
> are you using the two part name where ever you refer to the table.
> I mean refer to all the table names as
> dbo.table1
> or whichever schema you are using.
> Hope this helps.|||Hey Jim..
long time no clashes.. was missing you :)
"Jim Underwood" wrote:
> Also, make sure the user running the stored procedure has access to the
> tables.
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:2919D9C0-492D-4582-89B0-BEDABA2B0E6E@.microsoft.com...
>
>|||Lol. Yeah, you missed an entire day last w
. All day long I was lookingfor some posts from you. You usually have a few creative and thought
provoking solutions. I think today's is the negative flag * 2 +1 to get
either a positive or negative number. I had to get some caffeine before I
could figure it out.
Actually, I was hoping to see if you had a mathematical formula to solve the
matrix (Matriz) problem posted yesterday. The best I could come up with was
a mapping table. I couldn't seem to find a mathematical approach to the
problem, so I went with simple brute force.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:6FEE9DDC-CEBC-46B8-8AE1-17E1CCEE47A1@.microsoft.com...
> Hey Jim..
> long time no clashes.. was missing you :)
> "Jim Underwood" wrote:
>|||Gee...Thanks for the complments..
Well network was down yesterday.. and didn't see the post on matrix.. will
look into it now :)
"Jim Underwood" wrote:
> Lol. Yeah, you missed an entire day last w
. All day long I was looking
> for some posts from you. You usually have a few creative and thought
> provoking solutions. I think today's is the negative flag * 2 +1 to get
> either a positive or negative number. I had to get some caffeine before I
> could figure it out.
> Actually, I was hoping to see if you had a mathematical formula to solve t
he
> matrix (Matriz) problem posted yesterday. The best I could come up with w
as
> a mapping table. I couldn't seem to find a mathematical approach to the
> problem, so I went with simple brute force.
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:6FEE9DDC-CEBC-46B8-8AE1-17E1CCEE47A1@.microsoft.com...
>
>
Tuesday, February 14, 2012
Custom aggregation value for all hierarchies'' levels above a specific level name?
I currently have a customer and product dimension. I'm planning on adding in a fact 'customer count', based on whether or not the customer has a specific product, and I'd also be adding a Product Status dimension. I think I'll have the default member of Product Status be 'Active' because generally, people want to see only this type of number.
The behavior that I'd want is:
If Product Status is used with Product.Product.All, then I want a behavior of "check for at least 1 Active product status for the current customer and if it is found, then the customer's status is "Active", otherwise it would be "Inactive". This is similar to what I'd want for different hierarchies in the product dimension.
I have several product hierarchies that and 'Product' is at different levels of the hierarchies. Any level above 'Product' should have a behavior as described above.
Is this definitely possible and does it have minimal impact on query times and performance?
There might be a better design - if so, please comment and set me straight. Thanks!I did a test and I see now that the default behavior takes care of this for 'active' (anything above product level is only counted once because I'm using a distinct count type measure)
But now what's needed is a custom behavior when inactive is used with a 'customer count' measure. If looking at inactive counts for a Product.Product.All, an item (customer) should only be counted if all of their products are inactive.
How would one go about overriding the cube behavior?
If there are no other ideas, I may have to do a dirty workaround and make another status - 'Entirely Inactive' and it would mean that all products for a customer are inactive, calculated in the dsv table.
|||Here is my attempt at trying to override the inactive member, but what needs to change for it to work correctly?
I've included syntax of 'currenthierarchy', but i don't know if that's really possible.
SCOPE(Measures.[Customer Count], [Product Status].[Product Status].[Inactive])
this = IIF( IsEmpty(HEAD(EXISTS(DESCENDANTS([Product].CurrentHierarchy.CurrentMember) * [Customer].[Customer].CurrentMember, [Product Status].[Product Status].[Active]))), [Product Status].[Product Status].[Inactive], [Product Status].[Product Status].[Active[)
END SCOPE
Again in English, what I want to do is override the value of [Product Status].[Product Status] to be active if any of the descendants of the current product have an active status. Some attributes of the product dimension can't determine this - ie color, but having several trees, I'm not sure how to get at the hierarchy dynamically. Also, is "this = " correct?
Custom aggregation value for all hierarchies'' levels above a specific level name?
I currently have a customer and product dimension. I'm planning on adding in a fact 'customer count', based on whether or not the customer has a specific product, and I'd also be adding a Product Status dimension. I think I'll have the default member of Product Status be 'Active' because generally, people want to see only this type of number.
The behavior that I'd want is:
If Product Status is used with Product.Product.All, then I want a behavior of "check for at least 1 Active product status for the current customer and if it is found, then the customer's status is "Active", otherwise it would be "Inactive". This is similar to what I'd want for different hierarchies in the product dimension.
I have several product hierarchies that and 'Product' is at different levels of the hierarchies. Any level above 'Product' should have a behavior as described above.
Is this definitely possible and does it have minimal impact on query times and performance?
There might be a better design - if so, please comment and set me straight. Thanks!I did a test and I see now that the default behavior takes care of this for 'active' (anything above product level is only counted once because I'm using a distinct count type measure)
But now what's needed is a custom behavior when inactive is used with a 'customer count' measure. If looking at inactive counts for a Product.Product.All, an item (customer) should only be counted if all of their products are inactive.
How would one go about overriding the cube behavior?
If there are no other ideas, I may have to do a dirty workaround and make another status - 'Entirely Inactive' and it would mean that all products for a customer are inactive, calculated in the dsv table.
|||Here is my attempt at trying to override the inactive member, but what needs to change for it to work correctly?
I've included syntax of 'currenthierarchy', but i don't know if that's really possible.
SCOPE(Measures.[Customer Count], [Product Status].[Product Status].[Inactive])
this = IIF( IsEmpty(HEAD(EXISTS(DESCENDANTS([Product].CurrentHierarchy.CurrentMember) * [Customer].[Customer].CurrentMember, [Product Status].[Product Status].[Active]))), [Product Status].[Product Status].[Inactive], [Product Status].[Product Status].[Active[)
END SCOPE
Again in English, what I want to do is override the value of [Product Status].[Product Status] to be active if any of the descendants of the current product have an active status. Some attributes of the product dimension can't determine this - ie color, but having several trees, I'm not sure how to get at the hierarchy dynamically. Also, is "this = " correct?
Custom aggregation value for all hierarchies'' levels above a specific level name?
I currently have a customer and product dimension. I'm planning on adding in a fact 'customer count', based on whether or not the customer has a specific product, and I'd also be adding a Product Status dimension. I think I'll have the default member of Product Status be 'Active' because generally, people want to see only this type of number.
The behavior that I'd want is:
If Product Status is used with Product.Product.All, then I want a behavior of "check for at least 1 Active product status for the current customer and if it is found, then the customer's status is "Active", otherwise it would be "Inactive". This is similar to what I'd want for different hierarchies in the product dimension.
I have several product hierarchies that and 'Product' is at different levels of the hierarchies. Any level above 'Product' should have a behavior as described above.
Is this definitely possible and does it have minimal impact on query times and performance?
There might be a better design - if so, please comment and set me straight. Thanks!I did a test and I see now that the default behavior takes care of this for 'active' (anything above product level is only counted once because I'm using a distinct count type measure)
But now what's needed is a custom behavior when inactive is used with a 'customer count' measure. If looking at inactive counts for a Product.Product.All, an item (customer) should only be counted if all of their products are inactive.
How would one go about overriding the cube behavior?
If there are no other ideas, I may have to do a dirty workaround and make another status - 'Entirely Inactive' and it would mean that all products for a customer are inactive, calculated in the dsv table.
|||Here is my attempt at trying to override the inactive member, but what needs to change for it to work correctly?
I've included syntax of 'currenthierarchy', but i don't know if that's really possible.
SCOPE(Measures.[Customer Count], [Product Status].[Product Status].[Inactive])
this = IIF( IsEmpty(HEAD(EXISTS(DESCENDANTS([Product].CurrentHierarchy.CurrentMember) * [Customer].[Customer].CurrentMember, [Product Status].[Product Status].[Active]))), [Product Status].[Product Status].[Inactive], [Product Status].[Product Status].[Active[)
END SCOPE
Again in English, what I want to do is override the value of [Product Status].[Product Status] to be active if any of the descendants of the current product have an active status. Some attributes of the product dimension can't determine this - ie color, but having several trees, I'm not sure how to get at the hierarchy dynamically. Also, is "this = " correct?
Subscribe to:
Posts (Atom)