Bug?: left outer join in combination with a view

Hi,
there is a small test case (SQL Server 2000 - 8.00.760):
-- create a small table
create table test1 (nr int null)
insert into test1 values (1)
insert into test1 values (2)
-- now create a view
create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
If you perform this select, the isnull_jn col is always 'J',
although it should be 'N' in the second row:
select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn isnull_jn
-- -- --
1 J J
2 NULL J <<<=== error, should be N!
If you redefine the query to:
select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn case_jn
-- -- --
1 J J
2 NULL N <<== ok!
everything works fine.
Any suggestions?
--
markusOn Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:

>-- create a small table
>create table test1 (nr int null)
>insert into test1 values (1)
>insert into test1 values (2)
>-- now create a view
>create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
>If you perform this select, the isnull_jn col is always 'J',
>although it should be 'N' in the second row:
>select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn isnull_jn
>-- -- --
>1 J J
>2 NULL J <<<=== error, should be N!
>
>If you redefine the query to:
>select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn
'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn case_jn
>-- -- --
>1 J J
>2 NULL N <<== ok!
>everything works fine.
Hi Markus,
Nice one :-)
Looks like a bug to me. You might want to report it to MS.

>Any suggestions?
Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
COALESCE more portable. Plus, the support for ISNULL might be
discontinued in a future version of SQL Server.
2. COALESCE is more versatile. With COALESCE, you can get the first
non-NULL of as many arguments as you want. With ISNULL, the same can
only be achieved by nesting.
3. ISNULL apparently produces erroneous results when used in a left
outer join in combination with a view. A guy named Markus recently
posted about this in a newsgroup :-))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
COALESCE has bug problems of it's own:
http://support.microsoft.com/defaul...kb;en-us;317527
If you want to play safe, use CASE for any non-trivial scenarios where you
would use ISNULL or COALESCE.
Jacco Schalkwijk
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:e2sa80500of69g9q8e4dmijfcqjl9toenc@.
4ax.com...
> On Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:
>
'case_jn'[vbcol=seagreen]
> Hi Markus,
> Nice one :-)
> Looks like a bug to me. You might want to report it to MS.
>
> Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
> 1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
> COALESCE more portable. Plus, the support for ISNULL might be
> discontinued in a future version of SQL Server.
> 2. COALESCE is more versatile. With COALESCE, you can get the first
> non-NULL of as many arguments as you want. With ISNULL, the same can
> only be achieved by nesting.
> 3. ISNULL apparently produces erroneous results when used in a left
> outer join in combination with a view. A guy named Markus recently
> posted about this in a newsgroup :-))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 21 Apr 2004 15:26:17 +0100, Jacco Schalkwijk wrote:

>Hi Hugo,
>COALESCE has bug problems of it's own:
>http://support.microsoft.com/defaul...kb;en-us;317527
>If you want to play safe, use CASE for any non-trivial scenarios where you
>would use ISNULL or COALESCE.
When I executed the repro query in that article, I didn't get the
error, but the (expected) value of 1. Apparantly, this bug is fixed in
Service Pack 3a.
But thanks for the pointer, anyway - it just shows that it pays to pay
attention to the results of your query. A complicated product like SQL
Server (or any other RDBMS) can never be completely bug-free.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Bug?: left outer join in combination with a view

Hi,
there is a small test case (SQL Server 2000 - 8.00.760):
-- create a small table
create table test1 (nr int null)
insert into test1 values (1)
insert into test1 values (2)
-- now create a view
create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
If you perform this select, the isnull_jn col is always 'J',
although it should be 'N' in the second row:
select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn isnull_jn
-- -- --
1 J J
2 NULL J <<<=== error, should be N!
If you redefine the query to:
select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn case_jn
-- -- --
1 J J
2 NULL N <<== ok!
everything works fine.
Any suggestions?
--
markusThat looks like a bug with ISNULL; I switched it to use COALESCE instead and
it fixed the problem...
Hopefully someone reading this knows how to submit a SQL bug report to MS?
"mark" <wimark@.smsNOSAPM.at> wrote in message
news:#EJS#RvJEHA.620@.tk2msftngp13.phx.gbl...
> Hi,
> there is a small test case (SQL Server 2000 - 8.00.760):
> -- create a small table
> create table test1 (nr int null)
> insert into test1 values (1)
> insert into test1 values (2)
> -- now create a view
> create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
> If you perform this select, the isnull_jn col is always 'J',
> although it should be 'N' in the second row:
> select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
> from test1 left outer join testview on test1.nr = testview.nr
> nr jn isnull_jn
> -- -- --
> 1 J J
> 2 NULL J <<<=== error, should be N!
>
> If you redefine the query to:
> select test1.nr, jn, case when jn is null then 'N' else jn end as
'case_jn'
> from test1 left outer join testview on test1.nr = testview.nr
> nr jn case_jn
> -- -- --
> 1 J J
> 2 NULL N <<== ok!
> everything works fine.
> Any suggestions?
> --
> markus
>|||ISNULL is kind of notoriously NOT the same thing as COALESCE and CASE, so I
was skeptical that this was really a bug. But below is a repro which uses
ISNULL with a view and an "inline view" or derived table.
Now despite any differences between INSULL and COALESCE, these 2 queries
should return the same data. They do not.
-- create a small table
create table test1 (nr int null)
insert into test1 values (1)
insert into test1 values (2)
-- now create a view
create view testview as select nr, 'J' jn from test1 where nr = 1
select
test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1
left outer join (select nr, 'J' jn from test1 where nr = 1) testview
on test1.nr = testview.nr
where test1.nr=2
select
test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1
left outer join testview
on test1.nr = testview.nr
where test1.nr=2
--
nr jn isnull_jn
2 N
(1 row(s) affected)
nr jn isnull_jn
2 J
(1 row(s) affected)
David
Should definitely return the same data
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:ebxMLqvJEHA.3120@.TK2MSFTNGP11.phx.gbl...
> That looks like a bug with ISNULL; I switched it to use COALESCE instead
and
> it fixed the problem...
> Hopefully someone reading this knows how to submit a SQL bug report to MS?
>
> "mark" <wimark@.smsNOSAPM.at> wrote in message
> news:#EJS#RvJEHA.620@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > there is a small test case (SQL Server 2000 - 8.00.760):
> >
> > -- create a small table
> > create table test1 (nr int null)
> > insert into test1 values (1)
> > insert into test1 values (2)
> > -- now create a view
> > create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
> >
> > If you perform this select, the isnull_jn col is always 'J',
> > although it should be 'N' in the second row:
> > select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
> > from test1 left outer join testview on test1.nr = testview.nr
> > nr jn isnull_jn
> > -- -- --
> > 1 J J
> > 2 NULL J <<<=== error, should be N!
> >
> >
> > If you redefine the query to:
> > select test1.nr, jn, case when jn is null then 'N' else jn end as
> 'case_jn'
> > from test1 left outer join testview on test1.nr = testview.nr
> > nr jn case_jn
> > -- -- --
> > 1 J J
> > 2 NULL N <<== ok!
> > everything works fine.
> >
> > Any suggestions?
> > --
> > markus
> >
> >
>|||On Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:
>-- create a small table
>create table test1 (nr int null)
>insert into test1 values (1)
>insert into test1 values (2)
>-- now create a view
>create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
>If you perform this select, the isnull_jn col is always 'J',
>although it should be 'N' in the second row:
>select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn isnull_jn
>-- -- --
>1 J J
>2 NULL J <<<=== error, should be N!
>
>If you redefine the query to:
>select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn case_jn
>-- -- --
>1 J J
>2 NULL N <<== ok!
>everything works fine.
Hi Markus,
Nice one :-)
Looks like a bug to me. You might want to report it to MS.
>Any suggestions?
Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
COALESCE more portable. Plus, the support for ISNULL might be
discontinued in a future version of SQL Server.
2. COALESCE is more versatile. With COALESCE, you can get the first
non-NULL of as many arguments as you want. With ISNULL, the same can
only be achieved by nesting.
3. ISNULL apparently produces erroneous results when used in a left
outer join in combination with a view. A guy named Markus recently
posted about this in a newsgroup :-))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
COALESCE has bug problems of it's own:
http://support.microsoft.com/default.aspx?scid=kb;en-us;317527
If you want to play safe, use CASE for any non-trivial scenarios where you
would use ISNULL or COALESCE.
--
Jacco Schalkwijk
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:e2sa80500of69g9q8e4dmijfcqjl9toenc@.4ax.com...
> On Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:
> >-- create a small table
> >create table test1 (nr int null)
> >insert into test1 values (1)
> >insert into test1 values (2)
> >-- now create a view
> >create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
> >
> >If you perform this select, the isnull_jn col is always 'J',
> >although it should be 'N' in the second row:
> >select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
> >from test1 left outer join testview on test1.nr = testview.nr
> >nr jn isnull_jn
> >-- -- --
> >1 J J
> >2 NULL J <<<=== error, should be N!
> >
> >
> >If you redefine the query to:
> >select test1.nr, jn, case when jn is null then 'N' else jn end as
'case_jn'
> >from test1 left outer join testview on test1.nr = testview.nr
> >nr jn case_jn
> >-- -- --
> >1 J J
> >2 NULL N <<== ok!
> >everything works fine.
> Hi Markus,
> Nice one :-)
> Looks like a bug to me. You might want to report it to MS.
> >
> >Any suggestions?
> Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
> 1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
> COALESCE more portable. Plus, the support for ISNULL might be
> discontinued in a future version of SQL Server.
> 2. COALESCE is more versatile. With COALESCE, you can get the first
> non-NULL of as many arguments as you want. With ISNULL, the same can
> only be achieved by nesting.
> 3. ISNULL apparently produces erroneous results when used in a left
> outer join in combination with a view. A guy named Markus recently
> posted about this in a newsgroup :-))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 21 Apr 2004 15:26:17 +0100, Jacco Schalkwijk wrote:
>Hi Hugo,
>COALESCE has bug problems of it's own:
>http://support.microsoft.com/default.aspx?scid=kb;en-us;317527
>If you want to play safe, use CASE for any non-trivial scenarios where you
>would use ISNULL or COALESCE.
When I executed the repro query in that article, I didn't get the
error, but the (expected) value of 1. Apparantly, this bug is fixed in
Service Pack 3a.
But thanks for the pointer, anyway - it just shows that it pays to pay
attention to the results of your query. A complicated product like SQL
Server (or any other RDBMS) can never be completely bug-free.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Bug?: left outer join in combination with a view

Hi,
there is a small test case (SQL Server 2000 - 8.00.760):
-- create a small table
create table test1 (nr int null)
insert into test1 values (1)
insert into test1 values (2)
-- now create a view
create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
If you perform this select, the isnull_jn col is always 'J',
although it should be 'N' in the second row:
select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn isnull_jn
-- -- --
1 J J
2 NULL J <<<=== error, should be N!
If you redefine the query to:
select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn case_jn
-- -- --
1 J J
2 NULL N <<== ok!
everything works fine.
Any suggestions?
markus
On Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:

>-- create a small table
>create table test1 (nr int null)
>insert into test1 values (1)
>insert into test1 values (2)
>-- now create a view
>create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
>If you perform this select, the isnull_jn col is always 'J',
>although it should be 'N' in the second row:
>select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn isnull_jn
>-- -- --
>1 J J
>2 NULL J <<<=== error, should be N!
>
>If you redefine the query to:
>select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn case_jn
>-- -- --
>1 J J
>2 NULL N <<== ok!
>everything works fine.
Hi Markus,
Nice one :-)
Looks like a bug to me. You might want to report it to MS.

>Any suggestions?
Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
COALESCE more portable. Plus, the support for ISNULL might be
discontinued in a future version of SQL Server.
2. COALESCE is more versatile. With COALESCE, you can get the first
non-NULL of as many arguments as you want. With ISNULL, the same can
only be achieved by nesting.
3. ISNULL apparently produces erroneous results when used in a left
outer join in combination with a view. A guy named Markus recently
posted about this in a newsgroup :-))
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo,
COALESCE has bug problems of it's own:
http://support.microsoft.com/default...b;en-us;317527
If you want to play safe, use CASE for any non-trivial scenarios where you
would use ISNULL or COALESCE.
Jacco Schalkwijk
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:e2sa80500of69g9q8e4dmijfcqjl9toenc@.4ax.com... [vbcol=seagreen]
> On Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:
'case_jn'
> Hi Markus,
> Nice one :-)
> Looks like a bug to me. You might want to report it to MS.
>
> Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
> 1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
> COALESCE more portable. Plus, the support for ISNULL might be
> discontinued in a future version of SQL Server.
> 2. COALESCE is more versatile. With COALESCE, you can get the first
> non-NULL of as many arguments as you want. With ISNULL, the same can
> only be achieved by nesting.
> 3. ISNULL apparently produces erroneous results when used in a left
> outer join in combination with a view. A guy named Markus recently
> posted about this in a newsgroup :-))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Wed, 21 Apr 2004 15:26:17 +0100, Jacco Schalkwijk wrote:

>Hi Hugo,
>COALESCE has bug problems of it's own:
>http://support.microsoft.com/default...b;en-us;317527
>If you want to play safe, use CASE for any non-trivial scenarios where you
>would use ISNULL or COALESCE.
When I executed the repro query in that article, I didn't get the
error, but the (expected) value of 1. Apparantly, this bug is fixed in
Service Pack 3a.
But thanks for the pointer, anyway - it just shows that it pays to pay
attention to the results of your query. A complicated product like SQL
Server (or any other RDBMS) can never be completely bug-free.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Bug? Legend values same color

I when setting the background color for a data point programatically, I want
two colors. 'Firebrick' for bad values and 'ForestGreen' for good. So I
have a simple global function to handle this. Sometimes though, for no
explainable reason, even though my bar chart has green and red series, the
legend shows red for each of the two existing series. How can this be? The
series colors on the chart should always by default correspond with the
colors rendered for the legend.
I will provide you any information required to get to the bottom of this.
For starters, I'm running Report Services SP2.Please someone help me on this one. I am fairly certain this is a bug in
reporting services but need help to confirm this.

Bug? Available Values list for boolean parameters

Hi,
I have defined a boolean parameter in my report and I wish to display a list
of possible values instead of the True/False radio button. I have defined
two possible values in the Available Values list (Report Parameters Window)
but it looks as though they are ignored, the parameter toolbar still
displaying the True/False radio buttons...
Hope someone can help.Looks like a bug to me...best chance is to programatically create your
own parameter page.
"webfrog" <webfrog@.discussions.microsoft.com> wrote in message news:<11B5246A-F6E6-4583-8945-A945851F58CD@.microsoft.com>...
> Hi,
> I have defined a boolean parameter in my report and I wish to display a list
> of possible values instead of the True/False radio button. I have defined
> two possible values in the Available Values list (Report Parameters Window)
> but it looks as though they are ignored, the parameter toolbar still
> displaying the True/False radio buttons...
> Hope someone can help.|||If you change your datatype to integer, then put in available values such
that true = 1 and false = 0, you will get a drop-down list with true & false
options.
"webfrog" wrote:
> Hi,
> I have defined a boolean parameter in my report and I wish to display a list
> of possible values instead of the True/False radio button. I have defined
> two possible values in the Available Values list (Report Parameters Window)
> but it looks as though they are ignored, the parameter toolbar still
> displaying the True/False radio buttons...
> Hope someone can help.
>

Bug Maybe: Consecutive White Spaces Disappeared.

Hi,
When the report comes from the server (ie: not from the preview in
VS.Net) the consecutive white spaces in values are lost. Only one
white space is left. They are shown correctly in the preview though.
Example:
the value of a field expression is: ="xxx yyy"
the browser shows: "xxx yyy".
Is there work around this or it's a bug?
Thanks in advance.
DanniNever mind.
It started to work but I don't know what got changed.

bug - insert miliseconds

I noticed that my tables are not storing the milisecond values that I
am inserting. Is this a bug? If not, can someone please explain what
is going or or point me to a resource so I can research it further.

create table #t (col1 int identity,col2 datetime)
insert into #t (col2)
SELECT '2006-03-21 18:59:50.985'
select * from #tHi Dave,

Thats curious, in SQL 2000 this script:

create table #t (col1 int identity,col2 datetime)
insert into #t (col2)
SELECT '2006-03-21 18:59:50.985'
select * from #t

Results in:
2006-03-21 18:59:50.987

If I look up "datetime and smalldatetime" in Books Online it says:
"Date and time data from January 1, 1753 through December 31, 9999, to an
accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds
or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007
seconds, as shown in the table. (the table below not included here)"

Maybe this helps you somehow.

--
-Dick Christoph
"Dave" <daveg.01@.gmail.com> wrote in message
news:1143152560.420148.112820@.e56g2000cwe.googlegr oups.com...
>I noticed that my tables are not storing the milisecond values that I
> am inserting. Is this a bug? If not, can someone please explain what
> is going or or point me to a resource so I can research it further.
>
> create table #t (col1 int identity,col2 datetime)
> insert into #t (col2)
> SELECT '2006-03-21 18:59:50.985'
> select * from #t|||Yes, it does. I should have looked this up. It rounds to the nearest
..003 second.

Thanks!

bug - insert miliseconds

I noticed that my tables are not storing the milisecond values that I
am inserting. Is this a bug? If not, can someone please explain what
is going or or point me to a resource so I can research it further.
create table #t (col1 int identity,col2 datetime)
insert into #t (col2)
SELECT '2006-03-21 18:59:50.985'
select * from #tHi Dave,
Thats curious, in SQL 2000 this script:
create table #t (col1 int identity,col2 datetime)
insert into #t (col2)
SELECT '2006-03-21 18:59:50.985'
select * from #t
Results in:
2006-03-21 18:59:50.987
If I look up "datetime and smalldatetime" in Books Online it says:
"Date and time data from January 1, 1753 through December 31, 9999, to an
accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds
or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007
seconds, as shown in the table. (the table below not included here)"
Maybe this helps you somehow.
--
-Dick Christoph
"Dave" <daveg.01@.gmail.com> wrote in message
news:1143152560.420148.112820@.e56g2000cwe.googlegroups.com...
>I noticed that my tables are not storing the milisecond values that I
> am inserting. Is this a bug? If not, can someone please explain what
> is going or or point me to a resource so I can research it further.
>
> create table #t (col1 int identity,col2 datetime)
> insert into #t (col2)
> SELECT '2006-03-21 18:59:50.985'
> select * from #t
>|||Yes, it does. I should have looked this up. It rounds to the nearest
.003 second.
Thanks!

buffer.SetString not writing values!

Hello all,

I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.

Here's the code:

Code Snippet

public override void ProcessInput(int inputID, PipelineBuffer buffer){

//TODO

setupTraceListener();

IDTSOutput90 outp = ComponentMetaData.OutputCollection["DecodeOutput"];

int outId = outp.ID;

int errorOut = -1;

IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);

if( ComponentMetaData.OutputCollection.Count > 1){errorOut = ComponentMetaData.OutputCollection["DecodeErrors"].ID;

}

string sourceValue;

if(!buffer.EndOfRowset){

while(buffer.NextRow()){

if(!buffer.IsNull(inputColumnBufferIndex)){

switch(inp.InputColumnCollection[0].DataType){

case DataType.DT_WSTR:

sourceValue = buffer.GetString(this.inputColumnBufferIndex);

break;

case DataType.DT_I4:

sourceValue = buffer.GetInt32(this.inputColumnBufferIndex).ToString();

break;

default: throw new Exception("Invalid Data Type!");

}

sourceValue = sourceValue.Trim();

try{

object decodedValueObj = this.mappings[sourceValue];

if(decodedValueObj == null){

throw new Exception("No mapping!");

}

string decodedValue = decodedValueObj.ToString();

switch(outp.OutputColumnCollection[0].DataType){

case DataType.DT_WSTR:

buffer.SetString(this.outputColumnBufferIndex, decodedValue);

break;

case DataType.DT_I4:

buffer.SetInt32(this.outputColumnBufferIndex, int.Parse(decodedValue));

break;

default:

throw new IOException("Invalid Data Type!");

}

buffer.DirectRow(outId);

}catch(IOException fake){

throw fake;

}catch(Exception e){

redirectOrFail(inp, errorOut, outId, buffer, e);

}

}else{

redirectOrFail(inp,errorOut,outId,buffer,new Exception("Null values cannot be mapped"));

}

}

}

}

mappings is a hash map filled in PreExecute by accessing a database, outputColumnBufferIndex is calculated like this:

Code Snippet

IDTSInput90 input = ComponentMetaData.InputCollection[0];

IDTSOutput90 output = ComponentMetaData.OutputCollection["DecodeOutput"];

IDTSInputColumn90 inputColumn = input.InputColumnCollection[0];

IDTSOutputColumn90 outputColumn = output.OutputColumnCollection[0];

this.inputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);

this.outputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, outputColumn.LineageID);

I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.

I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.

Any help would be greatly appreciated!

Have you debugged it? Do inputColumnBufferIndex and outputColumnBufferIndex values make sense?

Do you only have one input and one output column in your component?

Are you getting any errors from SetString?

Thanks,

Bob

|||

Thank you for your reply Bob,

Yes, I did debug it and the values made sense. Thats how I know that the decodedValue was being also properly initialized.

It's working now by using:

Code Snippet

buffer[this.outputColumnBufferIndex] = decodedValue;

Don't ask me why.

I'm having all sorts of problems that have very easy but completely undocumented solutions like having to override PerformUpgrade to update the UserComponentTypeName attribute and the Version, even though I'm not changing either one as the component is still in development and not in use in any "real" packages.

Maybe my installation is corrupted somehow?

|||

Hmm, you should not be required to implement PerformUpgrade unless you are really changing your component.

Could you check your ProvideComponentProperties implementation and if there is something that might be messing up the settings?

Thanks,

Bob

|||

I don't know what to look for, this is my 3rd week with C# and SSIS.

Here's the basic idea:

Code Snippet

public override void ProvideComponentProperties(){

//FIXME

base.ProvideComponentProperties();

(set the name, dispositions, etc...)

base.RemoveAllInputsOutputsAndCustomProperties();

(add synchronous inputs and outputs)

(add custom properties)

}

I suspect it could be I'm calling RemoveAllInpusOutputsAndCustomProperties(); after base.ProvideComponentProperties(), but I don't want to touch it just yet. What are your thoughts?

Thank you very much again.

|||

Yeah, that is it.

You can safely remove that line as ProvideComponentProperties is called only once in the SSIS designer.

Alternatively, you may move that line before base.ProvideComponentProperties().

Thanks,

Bob

|||Thank you very much for your help|||

Adrian, this is not related to your question, but there is a bug in how buffer.EndOfRowset is used in the ProcessInput method in the first code sample, please see my blog for details

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

|||

Thank you very much for that link! I'll be changing that right away.

buffer.SetString not writing values!

Hello all,

I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.

Here's the code:

Code Snippet

public override void ProcessInput(int inputID, PipelineBuffer buffer){

//TODO

setupTraceListener();

IDTSOutput90 outp = ComponentMetaData.OutputCollection["DecodeOutput"];

int outId = outp.ID;

int errorOut = -1;

IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);

if( ComponentMetaData.OutputCollection.Count > 1){errorOut = ComponentMetaData.OutputCollection["DecodeErrors"].ID;

}

string sourceValue;

if(!buffer.EndOfRowset){

while(buffer.NextRow()){

if(!buffer.IsNull(inputColumnBufferIndex)){

switch(inp.InputColumnCollection[0].DataType){

case DataType.DT_WSTR:

sourceValue = buffer.GetString(this.inputColumnBufferIndex);

break;

case DataType.DT_I4:

sourceValue = buffer.GetInt32(this.inputColumnBufferIndex).ToString();

break;

default: throw new Exception("Invalid Data Type!");

}

sourceValue = sourceValue.Trim();

try{

object decodedValueObj = this.mappings[sourceValue];

if(decodedValueObj == null){

throw new Exception("No mapping!");

}

string decodedValue = decodedValueObj.ToString();

switch(outp.OutputColumnCollection[0].DataType){

case DataType.DT_WSTR:

buffer.SetString(this.outputColumnBufferIndex, decodedValue);

break;

case DataType.DT_I4:

buffer.SetInt32(this.outputColumnBufferIndex, int.Parse(decodedValue));

break;

default:

throw new IOException("Invalid Data Type!");

}

buffer.DirectRow(outId);

}catch(IOException fake){

throw fake;

}catch(Exception e){

redirectOrFail(inp, errorOut, outId, buffer, e);

}

}else{

redirectOrFail(inp,errorOut,outId,buffer,new Exception("Null values cannot be mapped"));

}

}

}

}

mappings is a hash map filled in PreExecute by accessing a database, outputColumnBufferIndex is calculated like this:

Code Snippet

IDTSInput90 input = ComponentMetaData.InputCollection[0];

IDTSOutput90 output = ComponentMetaData.OutputCollection["DecodeOutput"];

IDTSInputColumn90 inputColumn = input.InputColumnCollection[0];

IDTSOutputColumn90 outputColumn = output.OutputColumnCollection[0];

this.inputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);

this.outputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, outputColumn.LineageID);

I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.

I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.

Any help would be greatly appreciated!

Have you debugged it? Do inputColumnBufferIndex and outputColumnBufferIndex values make sense?

Do you only have one input and one output column in your component?

Are you getting any errors from SetString?

Thanks,

Bob

|||

Thank you for your reply Bob,

Yes, I did debug it and the values made sense. Thats how I know that the decodedValue was being also properly initialized.

It's working now by using:

Code Snippet

buffer[this.outputColumnBufferIndex] = decodedValue;

Don't ask me why.

I'm having all sorts of problems that have very easy but completely undocumented solutions like having to override PerformUpgrade to update the UserComponentTypeName attribute and the Version, even though I'm not changing either one as the component is still in development and not in use in any "real" packages.

Maybe my installation is corrupted somehow?

|||

Hmm, you should not be required to implement PerformUpgrade unless you are really changing your component.

Could you check your ProvideComponentProperties implementation and if there is something that might be messing up the settings?

Thanks,

Bob

|||

I don't know what to look for, this is my 3rd week with C# and SSIS.

Here's the basic idea:

Code Snippet

public override void ProvideComponentProperties(){

//FIXME

base.ProvideComponentProperties();

(set the name, dispositions, etc...)

base.RemoveAllInputsOutputsAndCustomProperties();

(add synchronous inputs and outputs)

(add custom properties)

}

I suspect it could be I'm calling RemoveAllInpusOutputsAndCustomProperties(); after base.ProvideComponentProperties(), but I don't want to touch it just yet. What are your thoughts?

Thank you very much again.

|||

Yeah, that is it.

You can safely remove that line as ProvideComponentProperties is called only once in the SSIS designer.

Alternatively, you may move that line before base.ProvideComponentProperties().

Thanks,

Bob

|||Thank you very much for your help|||

Adrian, this is not related to your question, but there is a bug in how buffer.EndOfRowset is used in the ProcessInput method in the first code sample, please see my blog for details

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

|||

Thank you very much for that link! I'll be changing that right away.

buffer.SetString not writing values!

Hello all,

I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.

Here's the code:

Code Snippet

public override void ProcessInput(int inputID, PipelineBuffer buffer){

//TODO

setupTraceListener();

IDTSOutput90 outp = ComponentMetaData.OutputCollection["DecodeOutput"];

int outId = outp.ID;

int errorOut = -1;

IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);

if( ComponentMetaData.OutputCollection.Count > 1){errorOut = ComponentMetaData.OutputCollection["DecodeErrors"].ID;

}

string sourceValue;

if(!buffer.EndOfRowset){

while(buffer.NextRow()){

if(!buffer.IsNull(inputColumnBufferIndex)){

switch(inp.InputColumnCollection[0].DataType){

case DataType.DT_WSTR:

sourceValue = buffer.GetString(this.inputColumnBufferIndex);

break;

case DataType.DT_I4:

sourceValue = buffer.GetInt32(this.inputColumnBufferIndex).ToString();

break;

default: throw new Exception("Invalid Data Type!");

}

sourceValue = sourceValue.Trim();

try{

object decodedValueObj = this.mappings[sourceValue];

if(decodedValueObj == null){

throw new Exception("No mapping!");

}

string decodedValue = decodedValueObj.ToString();

switch(outp.OutputColumnCollection[0].DataType){

case DataType.DT_WSTR:

buffer.SetString(this.outputColumnBufferIndex, decodedValue);

break;

case DataType.DT_I4:

buffer.SetInt32(this.outputColumnBufferIndex, int.Parse(decodedValue));

break;

default:

throw new IOException("Invalid Data Type!");

}

buffer.DirectRow(outId);

}catch(IOException fake){

throw fake;

}catch(Exception e){

redirectOrFail(inp, errorOut, outId, buffer, e);

}

}else{

redirectOrFail(inp,errorOut,outId,buffer,new Exception("Null values cannot be mapped"));

}

}

}

}

mappings is a hash map filled in PreExecute by accessing a database, outputColumnBufferIndex is calculated like this:

Code Snippet

IDTSInput90 input = ComponentMetaData.InputCollection[0];

IDTSOutput90 output = ComponentMetaData.OutputCollection["DecodeOutput"];

IDTSInputColumn90 inputColumn = input.InputColumnCollection[0];

IDTSOutputColumn90 outputColumn = output.OutputColumnCollection[0];

this.inputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);

this.outputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, outputColumn.LineageID);

I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.

I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.

Any help would be greatly appreciated!

Have you debugged it? Do inputColumnBufferIndex and outputColumnBufferIndex values make sense?

Do you only have one input and one output column in your component?

Are you getting any errors from SetString?

Thanks,

Bob

|||

Thank you for your reply Bob,

Yes, I did debug it and the values made sense. Thats how I know that the decodedValue was being also properly initialized.

It's working now by using:

Code Snippet

buffer[this.outputColumnBufferIndex] = decodedValue;

Don't ask me why.

I'm having all sorts of problems that have very easy but completely undocumented solutions like having to override PerformUpgrade to update the UserComponentTypeName attribute and the Version, even though I'm not changing either one as the component is still in development and not in use in any "real" packages.

Maybe my installation is corrupted somehow?

|||

Hmm, you should not be required to implement PerformUpgrade unless you are really changing your component.

Could you check your ProvideComponentProperties implementation and if there is something that might be messing up the settings?

Thanks,

Bob

|||

I don't know what to look for, this is my 3rd week with C# and SSIS.

Here's the basic idea:

Code Snippet

public override void ProvideComponentProperties(){

//FIXME

base.ProvideComponentProperties();

(set the name, dispositions, etc...)

base.RemoveAllInputsOutputsAndCustomProperties();

(add synchronous inputs and outputs)

(add custom properties)

}

I suspect it could be I'm calling RemoveAllInpusOutputsAndCustomProperties(); after base.ProvideComponentProperties(), but I don't want to touch it just yet. What are your thoughts?

Thank you very much again.

|||

Yeah, that is it.

You can safely remove that line as ProvideComponentProperties is called only once in the SSIS designer.

Alternatively, you may move that line before base.ProvideComponentProperties().

Thanks,

Bob

|||Thank you very much for your help|||

Adrian, this is not related to your question, but there is a bug in how buffer.EndOfRowset is used in the ProcessInput method in the first code sample, please see my blog for details

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

|||

Thank you very much for that link! I'll be changing that right away.

buffer.SetString not writing values!

Hello all,

I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.

Here's the code:

Code Snippet

public override void ProcessInput(int inputID, PipelineBuffer buffer){

//TODO

setupTraceListener();

IDTSOutput90 outp = ComponentMetaData.OutputCollection["DecodeOutput"];

int outId = outp.ID;

int errorOut = -1;

IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);

if( ComponentMetaData.OutputCollection.Count > 1){errorOut = ComponentMetaData.OutputCollection["DecodeErrors"].ID;

}

string sourceValue;

if(!buffer.EndOfRowset){

while(buffer.NextRow()){

if(!buffer.IsNull(inputColumnBufferIndex)){

switch(inp.InputColumnCollection[0].DataType){

case DataType.DT_WSTR:

sourceValue = buffer.GetString(this.inputColumnBufferIndex);

break;

case DataType.DT_I4:

sourceValue = buffer.GetInt32(this.inputColumnBufferIndex).ToString();

break;

default: throw new Exception("Invalid Data Type!");

}

sourceValue = sourceValue.Trim();

try{

object decodedValueObj = this.mappings[sourceValue];

if(decodedValueObj == null){

throw new Exception("No mapping!");

}

string decodedValue = decodedValueObj.ToString();

switch(outp.OutputColumnCollection[0].DataType){

case DataType.DT_WSTR:

buffer.SetString(this.outputColumnBufferIndex, decodedValue);

break;

case DataType.DT_I4:

buffer.SetInt32(this.outputColumnBufferIndex, int.Parse(decodedValue));

break;

default:

throw new IOException("Invalid Data Type!");

}

buffer.DirectRow(outId);

}catch(IOException fake){

throw fake;

}catch(Exception e){

redirectOrFail(inp, errorOut, outId, buffer, e);

}

}else{

redirectOrFail(inp,errorOut,outId,buffer,new Exception("Null values cannot be mapped"));

}

}

}

}

mappings is a hash map filled in PreExecute by accessing a database, outputColumnBufferIndex is calculated like this:

Code Snippet

IDTSInput90 input = ComponentMetaData.InputCollection[0];

IDTSOutput90 output = ComponentMetaData.OutputCollection["DecodeOutput"];

IDTSInputColumn90 inputColumn = input.InputColumnCollection[0];

IDTSOutputColumn90 outputColumn = output.OutputColumnCollection[0];

this.inputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);

this.outputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, outputColumn.LineageID);

I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.

I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.

Any help would be greatly appreciated!

Have you debugged it? Do inputColumnBufferIndex and outputColumnBufferIndex values make sense?

Do you only have one input and one output column in your component?

Are you getting any errors from SetString?

Thanks,

Bob

|||

Thank you for your reply Bob,

Yes, I did debug it and the values made sense. Thats how I know that the decodedValue was being also properly initialized.

It's working now by using:

Code Snippet

buffer[this.outputColumnBufferIndex] = decodedValue;

Don't ask me why.

I'm having all sorts of problems that have very easy but completely undocumented solutions like having to override PerformUpgrade to update the UserComponentTypeName attribute and the Version, even though I'm not changing either one as the component is still in development and not in use in any "real" packages.

Maybe my installation is corrupted somehow?

|||

Hmm, you should not be required to implement PerformUpgrade unless you are really changing your component.

Could you check your ProvideComponentProperties implementation and if there is something that might be messing up the settings?

Thanks,

Bob

|||

I don't know what to look for, this is my 3rd week with C# and SSIS.

Here's the basic idea:

Code Snippet

public override void ProvideComponentProperties(){

//FIXME

base.ProvideComponentProperties();

(set the name, dispositions, etc...)

base.RemoveAllInputsOutputsAndCustomProperties();

(add synchronous inputs and outputs)

(add custom properties)

}

I suspect it could be I'm calling RemoveAllInpusOutputsAndCustomProperties(); after base.ProvideComponentProperties(), but I don't want to touch it just yet. What are your thoughts?

Thank you very much again.

|||

Yeah, that is it.

You can safely remove that line as ProvideComponentProperties is called only once in the SSIS designer.

Alternatively, you may move that line before base.ProvideComponentProperties().

Thanks,

Bob

|||Thank you very much for your help|||

Adrian, this is not related to your question, but there is a bug in how buffer.EndOfRowset is used in the ProcessInput method in the first code sample, please see my blog for details

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

|||

Thank you very much for that link! I'll be changing that right away.

buffer.SetString not writing values!

Hello all,

I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.

Here's the code:

Code Snippet

public override void ProcessInput(int inputID, PipelineBuffer buffer){

//TODO

setupTraceListener();

IDTSOutput90 outp = ComponentMetaData.OutputCollection["DecodeOutput"];

int outId = outp.ID;

int errorOut = -1;

IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);

if( ComponentMetaData.OutputCollection.Count > 1){errorOut = ComponentMetaData.OutputCollection["DecodeErrors"].ID;

}

string sourceValue;

if(!buffer.EndOfRowset){

while(buffer.NextRow()){

if(!buffer.IsNull(inputColumnBufferIndex)){

switch(inp.InputColumnCollection[0].DataType){

case DataType.DT_WSTR:

sourceValue = buffer.GetString(this.inputColumnBufferIndex);

break;

case DataType.DT_I4:

sourceValue = buffer.GetInt32(this.inputColumnBufferIndex).ToString();

break;

default: throw new Exception("Invalid Data Type!");

}

sourceValue = sourceValue.Trim();

try{

object decodedValueObj = this.mappings[sourceValue];

if(decodedValueObj == null){

throw new Exception("No mapping!");

}

string decodedValue = decodedValueObj.ToString();

switch(outp.OutputColumnCollection[0].DataType){

case DataType.DT_WSTR:

buffer.SetString(this.outputColumnBufferIndex, decodedValue);

break;

case DataType.DT_I4:

buffer.SetInt32(this.outputColumnBufferIndex, int.Parse(decodedValue));

break;

default:

throw new IOException("Invalid Data Type!");

}

buffer.DirectRow(outId);

}catch(IOException fake){

throw fake;

}catch(Exception e){

redirectOrFail(inp, errorOut, outId, buffer, e);

}

}else{

redirectOrFail(inp,errorOut,outId,buffer,new Exception("Null values cannot be mapped"));

}

}

}

}

mappings is a hash map filled in PreExecute by accessing a database, outputColumnBufferIndex is calculated like this:

Code Snippet

IDTSInput90 input = ComponentMetaData.InputCollection[0];

IDTSOutput90 output = ComponentMetaData.OutputCollection["DecodeOutput"];

IDTSInputColumn90 inputColumn = input.InputColumnCollection[0];

IDTSOutputColumn90 outputColumn = output.OutputColumnCollection[0];

this.inputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);

this.outputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, outputColumn.LineageID);

I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.

I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.

Any help would be greatly appreciated!

Have you debugged it? Do inputColumnBufferIndex and outputColumnBufferIndex values make sense?

Do you only have one input and one output column in your component?

Are you getting any errors from SetString?

Thanks,

Bob

|||

Thank you for your reply Bob,

Yes, I did debug it and the values made sense. Thats how I know that the decodedValue was being also properly initialized.

It's working now by using:

Code Snippet

buffer[this.outputColumnBufferIndex] = decodedValue;

Don't ask me why.

I'm having all sorts of problems that have very easy but completely undocumented solutions like having to override PerformUpgrade to update the UserComponentTypeName attribute and the Version, even though I'm not changing either one as the component is still in development and not in use in any "real" packages.

Maybe my installation is corrupted somehow?

|||

Hmm, you should not be required to implement PerformUpgrade unless you are really changing your component.

Could you check your ProvideComponentProperties implementation and if there is something that might be messing up the settings?

Thanks,

Bob

|||

I don't know what to look for, this is my 3rd week with C# and SSIS.

Here's the basic idea:

Code Snippet

public override void ProvideComponentProperties(){

//FIXME

base.ProvideComponentProperties();

(set the name, dispositions, etc...)

base.RemoveAllInputsOutputsAndCustomProperties();

(add synchronous inputs and outputs)

(add custom properties)

}

I suspect it could be I'm calling RemoveAllInpusOutputsAndCustomProperties(); after base.ProvideComponentProperties(), but I don't want to touch it just yet. What are your thoughts?

Thank you very much again.

|||

Yeah, that is it.

You can safely remove that line as ProvideComponentProperties is called only once in the SSIS designer.

Alternatively, you may move that line before base.ProvideComponentProperties().

Thanks,

Bob

|||Thank you very much for your help|||

Adrian, this is not related to your question, but there is a bug in how buffer.EndOfRowset is used in the ProcessInput method in the first code sample, please see my blog for details

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

|||

Thank you very much for that link! I'll be changing that right away.

Bubble Chart

I would like to show static values on the Y-Axis of a bubble chart, say High,
Medium and Low. When I select the descriptive text in the dataset the
bubbles are not plotted correctly whilst if I retrieve the numeric
equivalent the bubbles are plotted the right way, but the Y-axis will show
numbers and not text.
Any help would be greatly appreciated.I too would like to do this and have not found any help so far. Has anyone
been successful in this? Or does anyone know of good documentation and
specific examples for the bubble chart?
"Adrian Theuma" wrote:
> I would like to show static values on the Y-Axis of a bubble chart, say High,
> Medium and Low. When I select the descriptive text in the dataset the
> bubbles are not plotted correctly whilst if I retrieve the numeric
> equivalent the bubbles are plotted the right way, but the Y-axis will show
> numbers and not text.
> Any help would be greatly appreciated.
>

Breaking monthly values down into daily values

Hi all, I'm new to MDX and am getting very confused with a script.

I'm running into problems breaking down monthly measures to daily values. If I have a monthly measure of 50, I would like to divide it by the number of days in the month to come up with daily values.

I believe I have set the proper granularity for the measurement relationship against the time dimension and have added the following script to my MDX script:

[Date].[Date].Members = [Measures].CurrentMember / [Date].[Calendar].CurrentMember.Parent.Children.Count

When I submit a query like this:

SELECT [Measures].[Measurement Objective] ON 0,
MTD([Date].[Calendar].[Date].&[20070207]) ON 1
FROM [Cube]

Everything looks good. The query returns seven rows, each with a properly scaled version of the monthly measurement. However, when I write the following query to return a single MTD value:

SELECT [Measures].[Measurement Objective] ON 0
FROM [Cube]
WHERE MTD([Date].[Calendar].[Date].&[20070207])

It doesn't work. It gives me the error:

The MDX Function CURRENTMEMBER failed because the coordinate for the
'Calendar Year' attribute contains a set

I'm sure this is just a matter of me misunderstanding MDX. Any help would be appreciate.
Thanks,
Richard

This is caused by the fact that you have used the .CurrentMember function in your calculation, but then have used a set (the MTD function returns a set of date members) in the WHERE clause. This means that there is not a single current member.

By introducing the ability to have sets in the WHERE clause, Microsoft have added extra complexity to writing calculated members.

The following variation should do the trick:

Code Snippet

[Date].[Date].Members = GENERATE( EXISTING( [Date].[Date].Members), [Measures].CurrentMember / [Date].[Calendar].CurrentMember.Parent.Children.Count)

The EXISTING() function returns the set of date members in the current context (the month to date members in your example query) and the Generate() function effectively "loops" over these members calculating your original expression. In this case the [Date].[Calendar].CurrentMember is evaluated within the context of each iteration of the "loop".

|||Thanks for the reply Darren.

I tried out the script and it didn't work. I was getting a divide-by-zero error in the results. Then I tried a simpler version without the division by the number of days in the month:

[Date].[Date].Members = GENERATE( EXISTING( [Date].[Date].Members), [Measures].CurrentMember)

which, given what you outlined, should just return the original monthly value (which is 128 for the month I'm using.) However, when I run the query each day has a value of 11891189. When I use the MTD function, it returns a concatenation of 11891189 * the number of days in the MTD set. The underlying measure is an integer.

Everything you outlined seems logical, but I'm just not sure where the 11891189 is coming from. I'm also restricting the scope of the MDX script to the single measure I'm testing.

Cheers,
Richard
|||

Actually I think I am missing a [Date] and the reference needs to be [Date].[Date].[Date].members

eg

[Date].[Date].[Date].Members = GENERATE( EXISTING( [Date].[Date].[Date].Members), [Measures].CurrentMember)

because [Date] is the dimension, [Date].[Date] is the hierarchy (which includes [Date].[Date].[All] which I think where the 11891189 might be coming from) [Date].[Date].[Date] is the actual attribute level.|||Hi Darren, it still didn't work and I've been doing a lot of investigation into the problem. I see what you mean by the problems of sets within the WHERE clause.

Instead of taking my original approach, I decided to use the Time Intelligence wizard within AS2005 and the MDX script that it generated worked like a charm. The extra YTD/MTD/QTD members in the time dimension makes a lot of sense.

Thanks again for your help
Richard