Derived types and backend.

I want the server side (sql and business logic) to know about one type. And
the client app(s) could derive there own types as needed but also store
their derivations so they can deserialize them. Is there a pattern for
this? What I am thinking now is simple example like:
public interface IVehicle
{
string Name
{
get;
set;
}
string Type
{
get;
set;
}
string Guid
{
get;
set;
}
public class Vehicle : IVehicle
{
private string name;
private string type;
private string data;
public string Name { get/set imp }
public string Type { get/set imp } // Derived type name. Used by
client to know how to deserilize Data.
public string Data { get/set imp} // Derived types xml.
}
So server knows about the Vehicle type and that is all. It can store three
columns: Name, Type, and Data.
If a client just wants to use Vehicle(s) then it is all set. However, it
may want to derive a Corvette or some other vehicle from base like so.
public class Corvette : IVehicle
{
private string name;
private string type;
private string data;
// Derived fields.
private string color;
public string Name { get/set imp }
public string Type { get/set imp }
public string Data { get/set imp}
public string Color { get/set imp}
public Corvette() { }
public Corvette(Vehicle vehicle) { //create a corvette from a vehicle. }
}
So I can get Vehicles from the server and create Corvettes on the client
side. However I need to store back a Corvette on the server, but the server
only knows about Vehicle type. So I am thinking serialize the Corvette type
into xml string, create a new Vehicle using same Name. Set vehicle.Type to
"Corvette" and store xml string in vehicle.Data. Now send the Vehicle type
to server for storage in SQL using the 3 columns in Vehicle. Now if the
client needs Corvette type, it gets the Vehicle, checks the Type and
deserializes the Data string into Corvette type and uses it. So that is the
round trip. Not pretty, but only way I can figure so far to do it. Any
ideas? TIA
William Stacey [MVP]
William Stacey [MVP]
There was an MSDN article written by Andrew Conrad that covers a scenario
very close to what you want to do. He uses an xml overflow column to store
the additional properties of the subclass.
"Death, Taxes, and Relational Databases, Part 1"
http://msdn.microsoft.com/library/de...ml04212003.asp
Specifically the section entitled: "Extending the Business Objects"
"William Stacey [MVP]" wrote:

> I want the server side (sql and business logic) to know about one type. And
> the client app(s) could derive there own types as needed but also store
> their derivations so they can deserialize them. Is there a pattern for
> this? What I am thinking now is simple example like:
> public interface IVehicle
> {
> string Name
> {
> get;
> set;
> }
> string Type
> {
> get;
> set;
> }
> string Guid
> {
> get;
> set;
> }
> public class Vehicle : IVehicle
> {
> private string name;
> private string type;
> private string data;
> public string Name { get/set imp }
> public string Type { get/set imp } // Derived type name. Used by
> client to know how to deserilize Data.
> public string Data { get/set imp} // Derived types xml.
> }
> So server knows about the Vehicle type and that is all. It can store three
> columns: Name, Type, and Data.
> If a client just wants to use Vehicle(s) then it is all set. However, it
> may want to derive a Corvette or some other vehicle from base like so.
> public class Corvette : IVehicle
> {
> private string name;
> private string type;
> private string data;
> // Derived fields.
> private string color;
> public string Name { get/set imp }
> public string Type { get/set imp }
> public string Data { get/set imp}
> public string Color { get/set imp}
> public Corvette() { }
> public Corvette(Vehicle vehicle) { //create a corvette from a vehicle. }
> }
> So I can get Vehicles from the server and create Corvettes on the client
> side. However I need to store back a Corvette on the server, but the server
> only knows about Vehicle type. So I am thinking serialize the Corvette type
> into xml string, create a new Vehicle using same Name. Set vehicle.Type to
> "Corvette" and store xml string in vehicle.Data. Now send the Vehicle type
> to server for storage in SQL using the 3 columns in Vehicle. Now if the
> client needs Corvette type, it gets the Vehicle, checks the Type and
> deserializes the Data string into Corvette type and uses it. So that is the
> round trip. Not pretty, but only way I can figure so far to do it. Any
> ideas? TIA
> --
> William Stacey [MVP]
> --
> William Stacey [MVP]
>
>
|||Thanks Todd. :-)
William Stacey [MVP]
"Todd Pfleiger [MSFT]" <ToddPfleigerMSFT@.discussions.microsoft.com> wrote in
message news:3B2852EA-495E-4D03-8CD7-10646EEF42E4@.microsoft.com...[vbcol=seagreen]
> There was an MSDN article written by Andrew Conrad that covers a scenario
> very close to what you want to do. He uses an xml overflow column to store
> the additional properties of the subclass.
> "Death, Taxes, and Relational Databases, Part 1"
> http://msdn.microsoft.com/library/de...ml04212003.asp
> Specifically the section entitled: "Extending the Business Objects"
>
> "William Stacey [MVP]" wrote:

Derived types and backend.

I want the server side (sql and business logic) to know about one type. And
the client app(s) could derive there own types as needed but also store
their derivations so they can deserialize them. Is there a pattern for
this? What I am thinking now is simple example like:
public interface IVehicle
{
string Name
{
get;
set;
}
string Type
{
get;
set;
}
string Guid
{
get;
set;
}
public class Vehicle : IVehicle
{
private string name;
private string type;
private string data;
public string Name { get/set imp }
public string Type { get/set imp } // Derived type name. Used by
client to know how to deserilize Data.
public string Data { get/set imp} // Derived types xml.
}
So server knows about the Vehicle type and that is all. It can store three
columns: Name, Type, and Data.
If a client just wants to use Vehicle(s) then it is all set. However, it
may want to derive a Corvette or some other vehicle from base like so.
public class Corvette : IVehicle
{
private string name;
private string type;
private string data;
// Derived fields.
private string color;
public string Name { get/set imp }
public string Type { get/set imp }
public string Data { get/set imp}
public string Color { get/set imp}
public Corvette() { }
public Corvette(Vehicle vehicle) { //create a corvette from a vehicle. }
}
So I can get Vehicles from the server and create Corvettes on the client
side. However I need to store back a Corvette on the server, but the server
only knows about Vehicle type. So I am thinking serialize the Corvette type
into xml string, create a new Vehicle using same Name. Set vehicle.Type to
"Corvette" and store xml string in vehicle.Data. Now send the Vehicle type
to server for storage in SQL using the 3 columns in Vehicle. Now if the
client needs Corvette type, it gets the Vehicle, checks the Type and
deserializes the Data string into Corvette type and uses it. So that is the
round trip. Not pretty, but only way I can figure so far to do it. Any
ideas? TIA
--
William Stacey [MVP]
William Stacey [MVP]There was an MSDN article written by Andrew Conrad that covers a scenario
very close to what you want to do. He uses an xml overflow column to store
the additional properties of the subclass.
"Death, Taxes, and Relational Databases, Part 1"
http://msdn.microsoft.com/library/d.../>
4212003.asp
Specifically the section entitled: "Extending the Business Objects"
"William Stacey [MVP]" wrote:

> I want the server side (sql and business logic) to know about one type. A
nd
> the client app(s) could derive there own types as needed but also store
> their derivations so they can deserialize them. Is there a pattern for
> this? What I am thinking now is simple example like:
> public interface IVehicle
> {
> string Name
> {
> get;
> set;
> }
> string Type
> {
> get;
> set;
> }
> string Guid
> {
> get;
> set;
> }
> public class Vehicle : IVehicle
> {
> private string name;
> private string type;
> private string data;
> public string Name { get/set imp }
> public string Type { get/set imp } // Derived type name. Used by
> client to know how to deserilize Data.
> public string Data { get/set imp} // Derived types xml.
> }
> So server knows about the Vehicle type and that is all. It can store thre
e
> columns: Name, Type, and Data.
> If a client just wants to use Vehicle(s) then it is all set. However, it
> may want to derive a Corvette or some other vehicle from base like so.
> public class Corvette : IVehicle
> {
> private string name;
> private string type;
> private string data;
> // Derived fields.
> private string color;
> public string Name { get/set imp }
> public string Type { get/set imp }
> public string Data { get/set imp}
> public string Color { get/set imp}
> public Corvette() { }
> public Corvette(Vehicle vehicle) { //create a corvette from a vehicle.
}
> }
> So I can get Vehicles from the server and create Corvettes on the client
> side. However I need to store back a Corvette on the server, but the serv
er
> only knows about Vehicle type. So I am thinking serialize the Corvette ty
pe
> into xml string, create a new Vehicle using same Name. Set vehicle.Type t
o
> "Corvette" and store xml string in vehicle.Data. Now send the Vehicle typ
e
> to server for storage in SQL using the 3 columns in Vehicle. Now if the
> client needs Corvette type, it gets the Vehicle, checks the Type and
> deserializes the Data string into Corvette type and uses it. So that is t
he
> round trip. Not pretty, but only way I can figure so far to do it. Any
> ideas? TIA
> --
> William Stacey [MVP]
> --
> William Stacey [MVP]
>
>|||Thanks Todd. :-)
William Stacey [MVP]
"Todd Pfleiger [MSFT]" <ToddPfleigerMSFT@.discussions.microsoft.com> wrote in
message news:3B2852EA-495E-4D03-8CD7-10646EEF42E4@.microsoft.com...
> There was an MSDN article written by Andrew Conrad that covers a scenario
> very close to what you want to do. He uses an xml overflow column to store
> the additional properties of the subclass.
> "Death, Taxes, and Relational Databases, Part 1"
> http://msdn.microsoft.com/library/d...
l04212003.asp
> Specifically the section entitled: "Extending the Business Objects"
>
> "William Stacey [MVP]" wrote:
>

Derive ReportDesigner !2005! Textbox

Hi there,
I want to derive the ReportDesigner TextBox to add some special functionality.
So we had to know which assembly provides this class.
using:
- Microsoft SQL Server 2005 CTP (april)
- Visual Studio 2005 Teamsuite Beta 2 (april)
Thx!On 22 Apr 2005 04:18:32 -0700, moritzfechtner@.web.de (the_mo) wrote:
>Hi there,
>I want to derive the ReportDesigner TextBox to add some special functionality.
>So we had to know which assembly provides this class.
>using:
>- Microsoft SQL Server 2005 CTP (april)
>- Visual Studio 2005 Teamsuite Beta 2 (april)
>Thx!
What additional functionality are you looking to provide?
By the way SQL Server 2005 questions are best directed to the
microsoft.private.sqlserver2005.reportingsvcs newsgroup.
See
http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005&slcid=us
foir information as to how to access the newsgroups in that stack.
Andrew Watt
MVP - InfoPath

derive listing of values NOT in table

I have 3 tables..
- unique listing of all employees...
- unique listing of all dates where transactions took place
- detail listing of all transactions
The detail listing of all transactions has a reference to the employee to
whom the transaction belongs as well as the date of the transaction.
I need to come up with a list of employees who did NOT have a transaction
for each date.
My initial thought was to create joins where I would get a listing of each
date along with all employees... then those employees who did not have a
transaction on a given date would showup as a null... for example:
Employees:
pete
david
joe
Dates:
10/5
10/6
10/7
Transactions
10/5 pete shoes
10/6 pete belt
10/6 david shoes
10/7 pete hat
10/7 david pants
I want to join those 3 tables so that I get this outcome:
Date Employee Transaction
10/5 pete shoes
10/5 david <null>
10/5 joe <null>
10/6 pete belt
10/6 david shoes
10/6 joe <null>
10/7 pete hat
10/7 david pants
10/7 joe <null>
I've done this type of thing before... but for some reason it's not working
for me. The outcome is not displaying the <null> rows.. only those rows
which contain a transaction.
Thanks.First Cross join the Employee table and the Dates table to get the full rang
e
of possible values. Then outer join to the Transactions table to get
yourtransactions.
SELECT d.Dates, e.EmpName, t.Trans
FROM Dates d
CROSS JOIN Emp e
LEFT JOIN Trans t ON d.Dates = t.Dates AND e.EmpName = t.EmpName
(Obviously I made up column names and table names :P )
HTH,
John Scragg
"David Sampson" wrote:

> I have 3 tables..
> - unique listing of all employees...
> - unique listing of all dates where transactions took place
> - detail listing of all transactions
> The detail listing of all transactions has a reference to the employee to
> whom the transaction belongs as well as the date of the transaction.
>
> I need to come up with a list of employees who did NOT have a transaction
> for each date.
>
> My initial thought was to create joins where I would get a listing of each
> date along with all employees... then those employees who did not have a
> transaction on a given date would showup as a null... for example:
>
> Employees:
> pete
> david
> joe
> Dates:
> 10/5
> 10/6
> 10/7
> Transactions
> 10/5 pete shoes
> 10/6 pete belt
> 10/6 david shoes
> 10/7 pete hat
> 10/7 david pants
>
> I want to join those 3 tables so that I get this outcome:
> Date Employee Transaction
> 10/5 pete shoes
> 10/5 david <null>
> 10/5 joe <null>
> 10/6 pete belt
> 10/6 david shoes
> 10/6 joe <null>
> 10/7 pete hat
> 10/7 david pants
> 10/7 joe <null>
> I've done this type of thing before... but for some reason it's not workin
g
> for me. The outcome is not displaying the <null> rows.. only those rows
> which contain a transaction.
> Thanks.
>
>|||Please post the offending query. I bet there's something in it tha diminishe
s
the effects of outer joins - that is if you use them at all. Can't tell unti
l
we see the query. :)
ML|||I've never heard of a CROSS join... I'll use that and see what it does for
me.
thanks
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:AFDD7347-E6D1-4C85-99FA-470F0772B468@.microsoft.com...
> First Cross join the Employee table and the Dates table to get the full
> range
> of possible values. Then outer join to the Transactions table to get
> yourtransactions.
> SELECT d.Dates, e.EmpName, t.Trans
> FROM Dates d
> CROSS JOIN Emp e
> LEFT JOIN Trans t ON d.Dates = t.Dates AND e.EmpName = t.EmpName
> (Obviously I made up column names and table names :P )
> HTH,
> John Scragg
> "David Sampson" wrote:
>|||That is EXACTLY what I needed!!!!!
Thanks alot!!!!
David
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:AFDD7347-E6D1-4C85-99FA-470F0772B468@.microsoft.com...
> First Cross join the Employee table and the Dates table to get the full
> range
> of possible values. Then outer join to the Transactions table to get
> yourtransactions.
> SELECT d.Dates, e.EmpName, t.Trans
> FROM Dates d
> CROSS JOIN Emp e
> LEFT JOIN Trans t ON d.Dates = t.Dates AND e.EmpName = t.EmpName
> (Obviously I made up column names and table names :P )
> HTH,
> John Scragg
> "David Sampson" wrote:
>|||I'm not making it up :P
A CROSS join is the cartesian product of two tables. So if you have 3 rows
in each table. A CROSS join will give you 9 rows. Every possible
combination.
It is the same as saying
SELECT d.Dates, e.EmpName
FROM Dates d, Employees e
Notice that there is no where clause. Same as there is no "ON" clause in my
example posted earlier. I just prefer the clarity of describing my join
types over the method show above.
Best of luck,
John
"David Sampson" wrote:

> I've never heard of a CROSS join... I'll use that and see what it does for
> me.
> thanks
>
> "John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
> news:AFDD7347-E6D1-4C85-99FA-470F0772B468@.microsoft.com...
>
>sql

Derive columns from cube member?

I have a cube that I read in a data flow section using OLE DB. To get this to work I had to do an ad hoc query through a sql server database ( this is problem that is described in post 219068).

So I now have the data and I want to derive new columns based on that data using an if statement:

if ( ISNULL(" [Facility].[REGION].[NATCODE].[MEMBER_CAPTION] " ) comptype = "NAT" else comptype = "REG"

comptype is the new derived column I am creating.

But when I try to save this I get an error: The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a requred element such as parenthesis

I selected the cube element from the columns list in derived element, when it initially put it in it looked like:

[[Facility].[REGION].[NATCODE].[MEMBER_CAPTION] ]

and gave error:

The token "[" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

Below is the statement I use to get data back from AS

select * from openrowset('MSOLAP', 'DATASOURCE=local; Initial Catalog=Patient Demographics 2005;',
'with member [Measures].[TimeDisplayName] as [Calendar].CurrentMember.Name
SELECT NON EMPTY { [Measures].[TimeDisplayName],[Measures].[Case Count],
[Measures].[A100 Bathing],[Measures].[A100 Bed Chair Wheelchair],
[Measures].[A100 Bladder],[Measures].[A100 Bowel],
[Measures].[A100 Dressing Lower],[Measures].[A100 Dressing Upper],
[Measures].[A100 Eating],[Measures].[A100 Grooming],
[Measures].[A100 Stairs],[Measures].[A100 Toilet],
[Measures].[A100 Toileting],[Measures].[A100 Tub Shower],
[Measures].[A100 Walk Wheelchair],[Measures].[A200 Comprehension],
[Measures].[A200 Expression],[Measures].[A200 Interaction],
[Measures].[A200 Memory],[Measures].[A200 Problem Solving],
[Measures].[D100 Bathing],[Measures].[D100 Bed Chair Wheelchair],
[Measures].[D100 Bladder],[Measures].[D100 Bowel],
[Measures].[D100 Dressing Lower],[Measures].[D100 Dressing Upper],
[Measures].[D100 Eating],[Measures].[D100 Grooming],
[Measures].[D100 Stairs],[Measures].[D100 Toilet],
[Measures].[D100 Toileting],[Measures].[D100 Tub Shower],
[Measures].[D100 Walk Wheelchair],[Measures].[D200 Comprehension],
[Measures].[D200 Expression],[Measures].[D200 Interaction],
[Measures].[D200 Memory],[Measures].[D200 Problem Solving] } ON COLUMNS,
({[Facility].[REGION].[NATCODE], [Facility].[REGION].[REGCODE]} *
[RIC].[CMGGRPCD].ALLMEMBERS ) ON ROWS FROM [CMG Demographics]
WHERE [Calendar].[Quarter 2 (2007)]')

So can some one help me on how I can do this derived colum.

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

To get <fieldname> expand the 'columns' folder in the derived column transformation editor.

|||Use a derived column to create new columns.

New Column Name: comptype
Expression: ISNULL([InputColumn]) ? "NAT" : "REG"|||

Anthony Martin wrote:

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

Anthony, just simplify that statement. ISNULL() returns a boolean value, so there's no need to doubly check for a true status or not. Just drop the "== true" part.|||

Thanks, good point. Just a habit I guess.

|||

I have figured out what I had to do, but I do not know why I had to do this way, but first while I saw after I posted that used used bad assignment of derived column comptype = <value>. that was one of my many different attempts around the question I posted about.

1st) I could never get the if else statment to work in derived field, I had to use conditional if (? logic for my ifs

2nd) I could not referenct the the MDX columns in my conditional if. It did not like all of the '[' and ']', so I had to convert all of the columns from MDX column name to a more standard sql column name, basically renamed column to not have '[', ']' in them

Does any one know why this does not work neatly, MDX is supposed to have been integrated with SSIS, but I had to do multiple tricks to get the data, right from start where I had to use ad hoc query though a sql database to execute the MDX.

This also results in fact that during selection of MDX it did not maintain any of the data types and converted everything to chararacter field of size 4000, which gave me trunctaion warnings in the selection of MDX into SSIS

Derive columns from cube member?

I have a cube that I read in a data flow section using OLE DB. To get this to work I had to do an ad hoc query through a sql server database ( this is problem that is described in post 219068).

So I now have the data and I want to derive new columns based on that data using an if statement:

if ( ISNULL(" [Facility].[REGION].[NATCODE].[MEMBER_CAPTION] " ) comptype = "NAT" else comptype = "REG"

comptype is the new derived column I am creating.

But when I try to save this I get an error: The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a requred element such as parenthesis

I selected the cube element from the columns list in derived element, when it initially put it in it looked like:

[[Facility].[REGION].[NATCODE].[MEMBER_CAPTION] ]

and gave error:

The token "[" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

Below is the statement I use to get data back from AS

select * from openrowset('MSOLAP', 'DATASOURCE=local; Initial Catalog=Patient Demographics 2005;',
'with member [Measures].[TimeDisplayName] as [Calendar].CurrentMember.Name
SELECT NON EMPTY { [Measures].[TimeDisplayName],[Measures].[Case Count],
[Measures].[A100 Bathing],[Measures].[A100 Bed Chair Wheelchair],
[Measures].[A100 Bladder],[Measures].[A100 Bowel],
[Measures].[A100 Dressing Lower],[Measures].[A100 Dressing Upper],
[Measures].[A100 Eating],[Measures].[A100 Grooming],
[Measures].[A100 Stairs],[Measures].[A100 Toilet],
[Measures].[A100 Toileting],[Measures].[A100 Tub Shower],
[Measures].[A100 Walk Wheelchair],[Measures].[A200 Comprehension],
[Measures].[A200 Expression],[Measures].[A200 Interaction],
[Measures].[A200 Memory],[Measures].[A200 Problem Solving],
[Measures].[D100 Bathing],[Measures].[D100 Bed Chair Wheelchair],
[Measures].[D100 Bladder],[Measures].[D100 Bowel],
[Measures].[D100 Dressing Lower],[Measures].[D100 Dressing Upper],
[Measures].[D100 Eating],[Measures].[D100 Grooming],
[Measures].[D100 Stairs],[Measures].[D100 Toilet],
[Measures].[D100 Toileting],[Measures].[D100 Tub Shower],
[Measures].[D100 Walk Wheelchair],[Measures].[D200 Comprehension],
[Measures].[D200 Expression],[Measures].[D200 Interaction],
[Measures].[D200 Memory],[Measures].[D200 Problem Solving] } ON COLUMNS,
({[Facility].[REGION].[NATCODE], [Facility].[REGION].[REGCODE]} *
[RIC].[CMGGRPCD].ALLMEMBERS ) ON ROWS FROM [CMG Demographics]
WHERE [Calendar].[Quarter 2 (2007)]')

So can some one help me on how I can do this derived colum.

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

To get <fieldname> expand the 'columns' folder in the derived column transformation editor.

|||Use a derived column to create new columns.

New Column Name: comptype
Expression: ISNULL([InputColumn]) ? "NAT" : "REG"|||

Anthony Martin wrote:

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

Anthony, just simplify that statement. ISNULL() returns a boolean value, so there's no need to doubly check for a true status or not. Just drop the "== true" part.|||

Thanks, good point. Just a habit I guess.

|||

I have figured out what I had to do, but I do not know why I had to do this way, but first while I saw after I posted that used used bad assignment of derived column comptype = <value>. that was one of my many different attempts around the question I posted about.

1st) I could never get the if else statment to work in derived field, I had to use conditional if (? logic for my ifs

2nd) I could not referenct the the MDX columns in my conditional if. It did not like all of the '[' and ']', so I had to convert all of the columns from MDX column name to a more standard sql column name, basically renamed column to not have '[', ']' in them

Does any one know why this does not work neatly, MDX is supposed to have been integrated with SSIS, but I had to do multiple tricks to get the data, right from start where I had to use ad hoc query though a sql database to execute the MDX.

This also results in fact that during selection of MDX it did not maintain any of the data types and converted everything to chararacter field of size 4000, which gave me trunctaion warnings in the selection of MDX into SSIS