Customer Statements

I am trying to develop a "customer statements" type report.
I have no issue creating "one off single customer at a time" versions.
I have an issue reporting groups of customers at a time.
The scenario is this:
Single Parameter = All Customers in Canada (for example)
3 different data sets of info to show on
each customer, 1 for general address info, 1 for
invoices and 1 for payments. It is necessary
for them to be broken into 3 seperate datasets.
How can I have 3 different table each with the
above datasets and have them group on one page,
and then the next page is the next customer...
The issue is my grouping, where do I set to
start a new group after all 3 tables/datasets based
on a customer grouping?
I am debating trying subreports to accomplish this
but was hoping I could get away with using 3 tables.
Any suggestions?You definitely want to use subreports. Have your general in the main report.
I suggest using a list and placing the data in individual text boxes (versus
using the table control). Then have two subreports using the table control.
This is exactly what subreports are designed for. Subreports are your
friend.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
news:6B1D0B5C-8DE3-4507-AF7D-E4113D10236E@.microsoft.com...
>I am trying to develop a "customer statements" type report.
> I have no issue creating "one off single customer at a time" versions.
> I have an issue reporting groups of customers at a time.
> The scenario is this:
> Single Parameter = All Customers in Canada (for example)
> 3 different data sets of info to show on
> each customer, 1 for general address info, 1 for
> invoices and 1 for payments. It is necessary
> for them to be broken into 3 seperate datasets.
> How can I have 3 different table each with the
> above datasets and have them group on one page,
> and then the next page is the next customer...
> The issue is my grouping, where do I set to
> start a new group after all 3 tables/datasets based
> on a customer grouping?
> I am debating trying subreports to accomplish this
> but was hoping I could get away with using 3 tables.
> Any suggestions?|||Thanks.
I am trying to implentment your suggestion of using a list (which I have
never used before) and dropping the fields on the body. I am not seeing the
"main area" data populate though, the fields show up blank.
"Bruce L-C [MVP]" wrote:
> You definitely want to use subreports. Have your general in the main report.
> I suggest using a list and placing the data in individual text boxes (versus
> using the table control). Then have two subreports using the table control.
> This is exactly what subreports are designed for. Subreports are your
> friend.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
> news:6B1D0B5C-8DE3-4507-AF7D-E4113D10236E@.microsoft.com...
> >I am trying to develop a "customer statements" type report.
> > I have no issue creating "one off single customer at a time" versions.
> > I have an issue reporting groups of customers at a time.
> >
> > The scenario is this:
> >
> > Single Parameter = All Customers in Canada (for example)
> > 3 different data sets of info to show on
> > each customer, 1 for general address info, 1 for
> > invoices and 1 for payments. It is necessary
> > for them to be broken into 3 seperate datasets.
> >
> > How can I have 3 different table each with the
> > above datasets and have them group on one page,
> > and then the next page is the next customer...
> > The issue is my grouping, where do I set to
> > start a new group after all 3 tables/datasets based
> > on a customer grouping?
> >
> > I am debating trying subreports to accomplish this
> > but was hoping I could get away with using 3 tables.
> >
> > Any suggestions?
>
>

Custom Y Axis Labels

Greetings.
I have data that is bit data type. I'd like the Y axis to display "Yes" for
1, "No" for Zero or Negative one.
How can this be accomplished? Thanks in advance.
--
Tim Heuer
heuert at Comcast dot netThe closest you can get is to use a bar chart (and therefore the x-axis
becomes the y-axis) with two static series - one for "Yes" and one for "No".
The aggregate functions would look similar to this:
Yes: =Sum( iif(Fields!BitField.Value = 1, 1, 0))
No: =Sum( iif(Fields!BitField.Value <> 1, 1, 0))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tim Heuer" <heuert at Comcast dot net> wrote in message
news:4BB4D6A1-693D-4292-986B-DB66E6C17669@.microsoft.com...
> Greetings.
> I have data that is bit data type. I'd like the Y axis to display "Yes"
for
> 1, "No" for Zero or Negative one.
> How can this be accomplished? Thanks in advance.
> --
> Tim Heuer
> heuert at Comcast dot net

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/

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/

Custom unique ID.

Hello all,
I baddly need to create my own datatype, that will be able to generate
itself as unique id.
1. my user defined data type is based on char(14).
2. structure of this type is
YYYYMMDDSCIDCONT
YYYY = year of record creation
MM = month of record creation
DD = day of record creation
SCID = value of SCID column in the table
CONT = counter (like identity seed) next unique value in the table
I can to do it by my .NET application, or i can make unique keys with all
values in the table, but I would like to create my own SQL type, that will
be able to do what i described. Is it possible?
Thanks.Maybe I should better explain this or ask for some questions.
My task Im going to do:
1) create my own User Defined Data Type based on char(16) named for example
MyIDType
2) create rule that will check if the value is in right format.
3) create function, that will able to generate new id with parameters
datetime, scid. This fuction will parse the datetime, scid and newly created
counter into char(16) and into MyIDType.
Questions:
1) can I create rule that contain more complex check than one rule row ?
2) How to ask for source table in T-SQL function. I mean for the table which
initiated running of my function. Im going to put this function onto
defaultValue of the column that will be type of MyIDType. The function will
suppose, that in this table will exist columns CreationDateTime and SCID.
I know, how to do it in C#. The workflow of this function should be (it is
an example, dont check any syntax):
FUNCTION GetNewMyID (@.scid char(4))
DECLARE @.datetimenow char(8)
DECLARE @.lastid char(4)
DECLARE @.partOfMyID char(12)
@.datetimenow = FORMAT( GETDATE()) // in format YYYYMMDD
for(int i = 1; i < 10000; i++)
{
@.partOfMyID = @.datetimenow + @.scid + i.ToString("0000")
SELECT MyID FROM [executingTable?] WHERE MyID LIKE @.partOfMyID
if(there is no row)
return @.partOfMyID;
}
I hope, the good programer will understand this weird construction. :)))
Thanks.
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
> Hello all,
> I baddly need to create my own datatype, that will be able to generate
> itself as unique id.
> 1. my user defined data type is based on char(16).
> 2. structure of this type is
> YYYYMMDDSCIDCONT
> YYYY = year of record creation
> MM = month of record creation
> DD = day of record creation
> SCID = value of SCID column in the table
> CONT = counter (like identity seed) next unique value in the table
> I can to do it by my .NET application, or i can make unique keys with all
> values in the table, but I would like to create my own SQL type, that will
> be able to do what i described. Is it possible?
> Thanks.
>|||hi Mirek,
Sure.
I understand that function but what does 'scid' mean? Let me know.
Current location: Alicante (ES)
"Mirek Endys" wrote:

> Maybe I should better explain this or ask for some questions.
> My task Im going to do:
> 1) create my own User Defined Data Type based on char(16) named for exampl
e
> MyIDType
> 2) create rule that will check if the value is in right format.
> 3) create function, that will able to generate new id with parameters
> datetime, scid. This fuction will parse the datetime, scid and newly creat
ed
> counter into char(16) and into MyIDType.
> Questions:
> 1) can I create rule that contain more complex check than one rule row ?
> 2) How to ask for source table in T-SQL function. I mean for the table whi
ch
> initiated running of my function. Im going to put this function onto
> defaultValue of the column that will be type of MyIDType. The function wil
l
> suppose, that in this table will exist columns CreationDateTime and SCID.
> I know, how to do it in C#. The workflow of this function should be (it is
> an example, dont check any syntax):
> FUNCTION GetNewMyID (@.scid char(4))
> DECLARE @.datetimenow char(8)
> DECLARE @.lastid char(4)
> DECLARE @.partOfMyID char(12)
> @.datetimenow = FORMAT( GETDATE()) // in format YYYYMMDD
>
> for(int i = 1; i < 10000; i++)
> {
> @.partOfMyID = @.datetimenow + @.scid + i.ToString("0000")
> SELECT MyID FROM [executingTable?] WHERE MyID LIKE @.partOfMyID
> if(there is no row)
> return @.partOfMyID;
> }
> I hope, the good programer will understand this weird construction. :)))
> Thanks.
>
> "Mirek Endys" <MirekE@.community.nospam> wrote in message
> news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
>
>|||Hi Enric
the SCID is variable - value that is contained in inserted record... or
passed by function parameter.
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:6DE5E6D0-9B7A-421C-A6BF-14E33D967B06@.microsoft.com...
> hi Mirek,
> Sure.
> I understand that function but what does 'scid' mean? Let me know.
> Current location: Alicante (ES)
>
> "Mirek Endys" wrote:
>|||Hi Mirek,
As for the SQL 2005 clr UDT, it is still quiet limited, e.g, if the data
type used in the UDT is not in the following basic types:
bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double,
SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime, SqlSingle, SqlDouble,
SqlMoney, SqlBoolean
we have to implenent our userdefined serialization. For type validation, we
can define the validation rules in a certain method and mark it with the
"ValidationMethodName" attribute.
And for the constructing a new UDT instance based on some parameters or
other info in the current database, this is hard to be done through the UDT
itself since UDT just provide a "parse" function which help use create UDT
instance from a string representation(also a "ToString" method help return
string representation from a UDT instance). For your scenario, maybe we
have to define an additional user defined funciton(CLR based) to the work.
#User-Defined Type Requirements
http://msdn2.microsoft.com/en-us/library/f33b06h1.aspx
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may
learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||You can create static method(s) in your UDT to create. Kinda like a static
constructor. Something like:
public static MyID Create(SqlDateTime dt, SqlInt id, SqlInt counter)
{
// ...
return new MyID(dt, id, counter);
}
Use the sql types that make sense for your needs.
Then you can create an instance something like:
declare @.id MyID
set @.id = MyID.Create(getdate(), 1, 1)
William Stacey [MVP]
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
| Hello all,
|
| I baddly need to create my own datatype, that will be able to generate
| itself as unique id.
|
| 1. my user defined data type is based on char(14).
| 2. structure of this type is
| YYYYMMDDSCIDCONT
|
| YYYY = year of record creation
| MM = month of record creation
| DD = day of record creation
| SCID = value of SCID column in the table
| CONT = counter (like identity seed) next unique value in the table
|
| I can to do it by my .NET application, or i can make unique keys with all
| values in the table, but I would like to create my own SQL type, that will
| be able to do what i described. Is it possible?
|
| Thanks.
|
||||Thank for Williams inputs.
This is a good suggestion that directly use a static method on the type.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may
learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks guys,
i thought so, that this will be solution.
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:wQfSEekTGHA.5536@.TK2MSFTNGXA03.phx.gbl...
> Thank for Williams inputs.
> This is a good suggestion that directly use a static method on the type.
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may
> learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Just a note after looking at it again. You need the double "::" to refer to
the static method on a type from tsql. Such as:
set @.id = MyID::Create(getdate(), 1, 1)
William Stacey [MVP]
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:uYBv3FmTGHA.4792@.TK2MSFTNGP14.phx.gbl...
| Thanks guys,
|
| i thought so, that this will be solution.
|
|
| "Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
| news:wQfSEekTGHA.5536@.TK2MSFTNGXA03.phx.gbl...
| > Thank for Williams inputs.
| >
| > This is a good suggestion that directly use a static method on the type.
| >
| > Regards,
| >
| > Steven Cheng
| > Microsoft Online Community Support
| >
| >
| > ========================================
==========
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may
| >
| > learn and benefit from your issue.
| >
| > ========================================
==========
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
|
|

Custom transform component, change type or add output column

Would anyone happen to have any pointers or know of any good code examples to either programmatically change the type of an input column when it is passed through the component, or add a new column to the output? I am extracting data from an Oracle database which is in Julian date format (represented within SSIS as a DT_NUMERIC column) and I need to to either transform the input column holding it into a date column, or to dynamically add a new output column holding the transformed data.

Many thanks

You cannot change the type of a buffer column. You will have to add a new column. If you only expect users to select valid columns, and for each one selected you could handle this by overriding SetUSageType on the component class.

Whenever people select a column (UsageType read-only, deny read-write as not required), you could then add a new output column. This would work and would be quite clean.

I would also use a custom property on the output column to store the lineage ID of the "source" inpurt column.

The better way would be to handle adding the column outside of SetUsageType. So with the Advanced UI you would need to select the input column (SetUsageType), then add the output column (InsertOutputColumnAt) and add the custom property (SetOutputColumnProperty) all in three steps. Method names used by the UI are shown in braclets so you know what to do if it was your own UI. With a proper custom UI this of course would become one step for the end user, but more importantly could all be done through the managed wrapper interface, CManagedComponentWrapperClass.SetUsageType, InsertOutputColumnAt, SetOutputColumnProperty methods. This would be good for the user experience it will give around real-time validation and OK/Cancel behaviour of the UI.

Custom sorting

Hi,

I am having a cube with 6 dimensions. One of the columns is of

leave type which has the following:

Business
Vacation
Sick
Casual
Paid
Unpaid
Miscellaneous
Time Off

When I display this on the cube it displays in alphabetically sorted

format. Is there any way I can display it in a customized manner? For e.g. as

displayed above?

Would appreciate if anybody can guide me on how it can be

done.

Thanks and regards

PMNJYou could add an Annotation into Dimension.Annotations property for your dimension display ordinal. Use this property for sorting when you display the dimensions.

Custom number format

Hello everybody,
I have a float type field in my sql server table and I want to select the
data in the format $XX,XXX.XX.
Any ideas '
Thanks.usamaalam wrote:

> Hello everybody,
> I have a float type field in my sql server table and I want to select the
> data in the format $XX,XXX.XX.
> Any ideas '
> Thanks.
Your client application controls how data is formatted for display, not
SQL Server. If you want to do that in SQL you'll have to return a
string rather than a number. I don't recommend it, but you can take a
look at the CONVERT function in Books Online.
Why do you use FLOATs for monetary amounts? FLOAT is an inexact numeric
so is an unusual and probably unwise choice for accounting. More
typically, I would suggest you should use NUMERIC/DECIMAL for this.
Hope this helps.
David Portas
SQL Server MVP
--|||I need select float in the specified format and return the formatted string.
Thanks.
"David Portas" wrote:

> usamaalam wrote:
>
> Your client application controls how data is formatted for display, not
> SQL Server. If you want to do that in SQL you'll have to return a
> string rather than a number. I don't recommend it, but you can take a
> look at the CONVERT function in Books Online.
> Why do you use FLOATs for monetary amounts? FLOAT is an inexact numeric
> so is an unusual and probably unwise choice for accounting. More
> typically, I would suggest you should use NUMERIC/DECIMAL for this.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||As David says, you should do it in the application tier and not in the
database. If you are using .NET in your application tier, there are powerful
formatting capabilities.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"usamaalam" <usamaalam@.discussions.microsoft.com> wrote in message
news:8207FDF2-C83E-491A-AE06-FF91FD965E14@.microsoft.com...
>I need select float in the specified format and return the formatted
>string.
> Thanks.
> "David Portas" wrote:
>|||Basically the application mechanism has been developed by someone else. I
need to export data from some tables in CSV format. The mechanism executes
an stored procedure, get data in a data table and a method gets the data
table as a parameter and generates CSV whatever present in the data table.
This will be more helpful for me to do it in sql, if possible.
Thanks a lot.
"SriSamp" wrote:

> As David says, you should do it in the application tier and not in the
> database. If you are using .NET in your application tier, there are powerf
ul
> formatting capabilities.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "usamaalam" <usamaalam@.discussions.microsoft.com> wrote in message
> news:8207FDF2-C83E-491A-AE06-FF91FD965E14@.microsoft.com...
>
>|||One method:
SELECT
'$' + CONVERT(varchar(20), CAST(MyFloatColumn AS money), 1)
FROM MyTable
I agree with the others that this is a kludge. This is a deficiency in the
application that ought to be addressed in the app code rather than
Transact-SQL.
Happy Holidays
Dan Guzman
SQL Server MVP
"usamaalam" <usamaalam@.discussions.microsoft.com> wrote in message
news:874B2D06-CA54-4852-AB97-4EB07762DA84@.microsoft.com...
> Basically the application mechanism has been developed by someone else. I
> need to export data from some tables in CSV format. The mechanism
> executes
> an stored procedure, get data in a data table and a method gets the data
> table as a parameter and generates CSV whatever present in the data table.
> This will be more helpful for me to do it in sql, if possible.
> Thanks a lot.
> "SriSamp" wrote:
>|||i've needed to do this myself a number of times, format the output on the
SELECT because i'm using BCP to plonk the data into an output file ready for
FTP'ing off to another server somewhere for another department/company to
use.
I'm not going to go to the effort of building a .NET application just to do
this; far simpler and quicker to format on the SELECT and output using BCP -
less components, less complexity.
There are times in reporting too where we need to do this because it would
be more difficult to do it in the reporting tool, for instance reporting
services; consider a trading report where trade amounts might be in
different currency, two ways of doing it - either concatenate in the front
end which might be more difficult or just simply do it easily on the SELECT.
You need to consider what you are doing before definitively saying do it in
the front end or in the db.
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1135337208.989446.250540@.g43g2000cwa.googlegroups.com...
> usamaalam wrote:
>
> Your client application controls how data is formatted for display, not
> SQL Server. If you want to do that in SQL you'll have to return a
> string rather than a number. I don't recommend it, but you can take a
> look at the CONVERT function in Books Online.
> Why do you use FLOATs for monetary amounts? FLOAT is an inexact numeric
> so is an unusual and probably unwise choice for accounting. More
> typically, I would suggest you should use NUMERIC/DECIMAL for this.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||Maybe you should consider using XML as a transport medium, instead of CSV.
After all, this is the 21st century. :)
If the data is being used in an application at the other end then I really
see no point in formatting it, unless the actual data type is ignored at the
other end, which I doubt to be true.
ML
http://milambda.blogspot.com/|||i just want to reiterate what David said: if at all possible, change the
float to another type - you *will* get bad data at some point, and this
being money, that will be a problem.
if not possible for you to do it, hound the person who can do it :)
usamaalam wrote:
> Hello everybody,
> I have a float type field in my sql server table and I want to select the
> data in the format $XX,XXX.XX.
> Any ideas '
> Thanks.|||On Fri, 23 Dec 2005 08:17:02 -0800, "ML" <ML@.discussions.microsoft.com> wrot
e:
in <61588BC5-7F5E-4BD4-A95E-7B6B24984F3A@.microsoft.com>

>Maybe you should consider using XML as a transport medium, instead of CSV.
>After all, this is the 21st century. :)
I don't understand that sort of reasoning. What makes a proprietary schema
and
bloated data better than the CSV format?
Stefan Berglund

custom log provider type not recognized as a valid log provider

I am new to SSIS but I am learning fast. Any help is greatly appreciated.

I have written a custom log provider derived from LogProviderBase. I want to programmtically add the custom log provider to my package, but when I try this:

myPackage.LogProviders.Add("ACustomLogProvider");

I get the following exception:

"The log provider type "ACustomLogProvider" specified for log provider "{..GUID..}" is not recognized as a valid log provider type. This error occurs when an attempt is made to create a log provider for unknown log provider type. Verify the spelling in the log provider type name".

I have followed the instructions in BOL and as suggested in this forum, adding the DtsLogProvider attribute:

[DtsLogProvider(DisplayName="ACustomLogProvider", LogProviderType="ACustomLogProvider", Description="a description")]

Another way I tried is that I added a GUID attribute to my custom log provider class and then tried this:

Type aType = typeof(ACustomLogProvider);

Guid aGUID = (Guid)aType.GUID;

myPackage.LogProviders.Add("{"+ aGUID.ToString() +"}");

This also gives me the same exception "The log provider type ""{..the actual GUID for ACustomLogProvider..}" specified for log provider "{..a different GUID..}" is not recognized as a valid log provider type....

My questions are: Is it possible to add a custom log provider programmtically or must it be done through the SSIS Designer?

Must I deploy my custom log provider class for it to work programmatically? (I had perhaps wrongly assumed I would not have to deploy if I don't want it to show up in the SSIS Designer).

I would prefer to use a custom log provider instead of just logging within the SSIS event handlers because I can override the OnPostExecute, etc. but I cannot (can I?) override the specific event handlers like PackageStart, PackageEnd, etc because they are not exposed through DTSEvents.

Any help I would be very grateful for. Thanks.

I think you need to pass fully qualified type name to Add method:

myPackage.LogProviders.Add(aType.AssemblyQualifiedName);

|||

Thank you very much, Michael. That is working great.

I appreciate your help, especially since this seems like it was a trivial question. I should have been able to see that!

Thanks,

Amy

|||

Hi,

I've trying to create a custom log provider following msdn directions from:

http://technet.microsoft.com/en-us/library/ms365184.aspx

However, the example shown there for HtmlLogProvider does not work for me. I created the dll put it in every single folder it is indicated and also used gacutil and nothing. When I go to the SQL Server Business Intelligence Development Studio and I select "Logging" from the control flow, the only available log proivders that I see are the ones that came by default with the the IDE but not the one I created (HtmlLogProvider). I would like to know if you tried it and also had the same problem and if you could give me some advice of what to do.

Thanks so much

|||Try restarting the SSIS service.|||

I finally made it work for the HtmlLog Provider but sometimes it works and sometimes it does not. By some reason sometimes it does fill data inside the html and sometimes it does nothing with it. I have wondering what may be the reason.

The steps I followed once I had the Log Provider installed in the GAC and copied into the log provider folder was to do:

Logging

Add Custom log provider

and choose the package level and som of the events (On error, etc). I also tried selecting everything.

I also tried clickng on "Log Events" but what it does is only show a window with the logged events.

Please tell me if there is a bug with the log providers in SSIS or if I am not following the right steps.

Thanks a lot

|||

I'm not aware of any bug with the log providers. The stock log providers work using the same extensibility mechanism as you should be using, so if stock log providers work OK, you need to debug why yours is not working.

It is unclear what do you mean by "sometime it does nothing with it" - have you debugged whether the log provider receives the events you expect it to receive?

|||

I am sorry I was not too specific with what I meant. Let me try to make myself more clear. First I downloaded an example of an HtmlLogProvider from MS and tried it and worked. Now I was looking at the methods that are overwritten and I wanted to create my own log provider. So I tried to do it as simple as possible. So in my dummy example I just wanted to see the errors of my dtsx pacakge thru my cutom log provider. The code is as follows:

namespace HtmlLogProviderCS

{

using System;

using Microsoft.SqlServer.Dts.Runtime;

using System.IO;

using System.Web.UI;

using System.Web;

// The LogProviderType property is required but not used.

// The custom log provider will not appear in the list

// if a LogProviderType is not provided.

[DtsLogProvider(DisplayName = "MyLogProviderTest", Description = "A simple log provider.", LogProviderType = "Custom")]

public class HtmlLogProviderCS : LogProviderBase

{

#region Variables and constants

// Constants.

private const string SUBCOMPONENT = "HtmlLogProviderCS";

private const string PACKAGE_END_EVENT = "PackageEnd";

// Variables.

private Microsoft.SqlServer.Dts.Runtime.Connections _connections;

private Microsoft.SqlServer.Dts.Runtime.IDTSInfoEvents _events;

private string _configString;

private string _logFile;

private HtmlLogWriterCS _htmlLogWriter;

private MemoryStream _logStream;

private StreamWriter _logStreamWriter;

// Status flags.

private bool _fireEventsAgain=true;

private bool _loggingAlreadyStarted;

private bool _packageHasEnded;

#endregion

public override void InitializeLogProvider(Microsoft.SqlServer.Dts.Runtime.Connections connections, Microsoft.SqlServer.Dts.Runtime.IDTSInfoEvents events, Microsoft.SqlServer.Dts.Runtime.ObjectReferenceTracker refTracker)

{

_events = events;

System.Windows.Forms.MessageBox.Show("Init");

}

public override Microsoft.SqlServer.Dts.Runtime.DTSExecResult Validate(Microsoft.SqlServer.Dts.Runtime.IDTSInfoEvents events)

{

System.Windows.Forms.MessageBox.Show("Validate");

return DTSExecResult.Success;

}

public override void OpenLog()

{

System.Windows.Forms.MessageBox.Show("OpenLog");

}

public override void CloseLog()

{

System.Windows.Forms.MessageBox.Show("CloseLog");

}

public override void Log(string logEntryName, string computerName, string operatorName, string sourceName, string sourceID, string executionID, string messageText, DateTime startTime, DateTime endTime, int dataCode, byte[] dataBytes)

{

System.Windows.Forms.MessageBox.Show("Logging...");

System.Windows.Forms.MessageBox.Show("Message is: " + logEntryName + "--" + computerName + "--" + operatorName + "--" + sourceName + "--" + sourceID + "--" + executionID + "--" + messageText);

}

}

}

Many of the variables declared in there are just there cuz they were in the example I took form the HtmlLogProvider but other than that there's no reason for them to be there. So I really do not know why this Log is not working. The weird thing is that the message that am putting for every method does pop op correctly in every method EXCEPT for the LOG method which is not even invoked.

Custom Component - Change Data Type

I am developing a custom pipeline component (my first real one) which will handle input columns in a DT_NUMERIC format and convert them to a DT_DATE format. Sort of like the included Data Conversion component, but with some built in business logic. The Data Conversion component puts the converted data in a new column in the output buffer. I would like to keep the data in the same column. So, in my input buffer the column would be of type DT_NUMERIC, but in the output buffer I want to make it DT_DATE.

What is the best approach to do this?

-DarrellUnless you want you component to have to copy data from the input to the ouput you can not do this. There are 2 types of outputs, synchronous and asynchronous. The synchronous one is simple and requires no data copying or column creation (except of course for the new column you would produce of the DT_DATE type). However, because of this you can't change column types and that is why the derived column doesn't allow this. The asynchronous one is complex. You have to create all the output columns and keep them in sync with the input columns and know how to map the input columns to the output columns so that you can copy the data to the correct place. My advise is use a sync output and live with the additional column unless it is absolutely necessary to use and async output.

HTH,
Matt

Custom code problem

I am trying to lear how to use custom coding in a report. I go to the
report properties and type in my code but when I try to access it
suing "Code.MyTestCode", I do not see my function but only a slection
list of "Equals", "GetHashCode","GetType", "Reference Equals" and "To
String".
I have tried code as simple as
Public Const MyName = "John"
and then tried to assign it to a text box.
What am I doing wrong or have setup wrong on my system?
TIA,
JohnI have the same question. But when you just invoke the functions inside the
code, should be working fine.
"JL" <john@.marymonte.com> wrote in message
news:mgu3g2pdrl328dlupcoqq0cagdmpn9mfl9@.4ax.com...
>I am trying to lear how to use custom coding in a report. I go to the
> report properties and type in my code but when I try to access it
> suing "Code.MyTestCode", I do not see my function but only a slection
> list of "Equals", "GetHashCode","GetType", "Reference Equals" and "To
> String".
> I have tried code as simple as
> Public Const MyName = "John"
> and then tried to assign it to a text box.
> What am I doing wrong or have setup wrong on my system?
> TIA,
> John
>|||Hi Patrick,
What do you mean by "invoke the functions inside the
code"?
John
On Fri, 8 Sep 2006 22:44:58 -0400, "Patrick" <feng_online@.hotmail.com>
wrote:
>I have the same question. But when you just invoke the functions inside the
>code, should be working fine.
>"JL" <john@.marymonte.com> wrote in message
>news:mgu3g2pdrl328dlupcoqq0cagdmpn9mfl9@.4ax.com...
>>I am trying to lear how to use custom coding in a report. I go to the
>> report properties and type in my code but when I try to access it
>> suing "Code.MyTestCode", I do not see my function but only a slection
>> list of "Equals", "GetHashCode","GetType", "Reference Equals" and "To
>> String".
>> I have tried code as simple as
>> Public Const MyName = "John"
>> and then tried to assign it to a text box.
>> What am I doing wrong or have setup wrong on my system?
>> TIA,
>> John
>>
>|||I think the function name isn't showing up in intellisense because it hasn't
been copied to the Private Assemblies folder of VS 2005. Here's a link to
the article that got me going.
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
http://www.codeproject.com/dotnet/CustomAssemblies.asp
Steve MunLeeuw
"JL" <john@.marymonte.com> wrote in message
news:mgu3g2pdrl328dlupcoqq0cagdmpn9mfl9@.4ax.com...
>I am trying to lear how to use custom coding in a report. I go to the
> report properties and type in my code but when I try to access it
> suing "Code.MyTestCode", I do not see my function but only a slection
> list of "Equals", "GetHashCode","GetType", "Reference Equals" and "To
> String".
> I have tried code as simple as
> Public Const MyName = "John"
> and then tried to assign it to a text box.
> What am I doing wrong or have setup wrong on my system?
> TIA,
> John
>|||Hi Steve,
Thanks for the reply. Let me clarify, I am not tryint to use a custom
assembly. I am typing my code into the report properties Code window.
All the samples indicate that it should then show up when I use the
"Code.xxxx" command. I appreciate any suggestions as to what I am
missing or misunderstanding.
Thanks,
John
On Sat, 9 Sep 2006 09:23:35 -0600, "Steve MunLeeuw"
<smunson@.clearwire.net> wrote:
>I think the function name isn't showing up in intellisense because it hasn't
>been copied to the Private Assemblies folder of VS 2005. Here's a link to
>the article that got me going.
>C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
>http://www.codeproject.com/dotnet/CustomAssemblies.asp
>Steve MunLeeuw
>
>"JL" <john@.marymonte.com> wrote in message
>news:mgu3g2pdrl328dlupcoqq0cagdmpn9mfl9@.4ax.com...
>>I am trying to lear how to use custom coding in a report. I go to the
>> report properties and type in my code but when I try to access it
>> suing "Code.MyTestCode", I do not see my function but only a slection
>> list of "Equals", "GetHashCode","GetType", "Reference Equals" and "To
>> String".
>> I have tried code as simple as
>> Public Const MyName = "John"
>> and then tried to assign it to a text box.
>> What am I doing wrong or have setup wrong on my system?
>> TIA,
>> John
>>
>

Custom Code

I have been reading a little about custom code - Where do I type this code at in the report? Does anyone have a sample report or info they could pass along? Sorry about the dumb question , but i am a newbie and I am not so bright...you click on Report - Properties and click on Code tab. there you can write your vb.net functions..
sample CC :

Public Shared Function RetCardHolderName(lastname as string, middle as string,firstname as string) as String
 If ( firstname is system.dbnull.value OR len(trim(firstname))=0 ) Then
 return ( trim(lastname))
 else
 if ( middle is system.dbnull.value OR len(trim(middle))=0 ) Then
 return (trim(firstname) & " " & trim(lastname))
 else
 return (trim(firstname) & " " & trim(middle) & " " & trim(lastname) )
 end if
 end if
End Function

I wrote this CC to return the first, middle and last names concatenated as one string.

from the report I pass the parameters as :
in the Expression window for the field :

=Code.RetCardHolderName(Fields!LAST_NAME.Value, Fields!MID_NAME.Value, Fields!FIRST_NAME.Value)

|||Thank You! The example was also a big help!

cursors question

I'll greatly appreciate any help with the error message bellow. What I am
trying to do is first I am getting a customer type and nr of members of that
type. Next I list first 5 customers and if there are more than 5 I want a
message to appear indicating how many more records are remaining and the las
t
record. Here is what I get:
3 J, Total nr. of records 5
1 R, M
2 H, S
3 S, S
4 W, G
5 W, P
4 O, Total nr. of records 1
1 V, D
5 R, Total nr. of records 17
1 M, M
2 V, J
3 R, R
4 D, K
5 S, M
Server: Msg 16911, Level 16, State 1, Line 39
fetch: The fetch type last cannot be used with forward only cursors.
.. There are 12 more records
-->> The last record being S, S
My code looks something like this:
declare @.MemID varchar(10)
declare @.total int
declare @.RowNum int
declare @.NamesNum int
declare @.RowNum1 int
declare @.fname varchar(20)
declare @.lname varchar(20)
declare MemList cursor for
select benefit, count(*) total from cust where branch='xxx' group by benefit
OPEN MemList
FETCH NEXT FROM MemList
INTO @.MemID, @.total
set @.RowNum = 0
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.RowNum = @.RowNum + 1
print cast(@.RowNum as varchar) + ' ' + LEFT(@.MemID, 1) + ', Total nr. of
records ' + cast(@.total as varchar)
---
declare namesList cursor
FOR select surname, name from cust where branch='xxx' and benefit=@.MemID
OPEN namesList
FETCH NEXT FROM namesList
INTO @.lname, @.fname
set @.RowNum1 = 0
WHILE @.@.FETCH_STATUS = 0 AND @.RowNum1 < 5
BEGIN
set @.RowNum1 = @.RowNum1 + 1
print ' ' + cast(@.RowNum1 as varchar) + ' ' + LEFT(@.lname,1) + ', ' +
@.fname
FETCH NEXT FROM namesList
INTO @.lname, @.fname
END
IF @.total >= 6
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH LAST FROM namesList
INTO @.lname, @.fname
PRINT ' ... There are ' + ' ' + cast((@.total - 5) as varchar) + ' more
records'
PRINT ' -->> The last record being ' + LEFT(@.lname, 1) + ', ' + @.fname
END
CLOSE namesList
DEALLOCATE namesList
---
FETCH NEXT FROM MemList
INTO @.MemID, @.total
END
CLOSE MemList
DEALLOCATE MemList
Thanks for your help!Hi,
In Order to Use the LAST function you have to declare the Cursor as a
Scroll Cursor.
Please see code below - take note in a slight modification to stop the
Cursor repeating itself.
HTH
Barry
SQL CODE:
declare @.MemID varchar(10)
declare @.total int
declare @.RowNum int
declare @.NamesNum int
declare @.RowNum1 int
declare @.fname varchar(20)
declare @.lname varchar(20)
declare MemList cursor for
select benefit, count(*) total from cust where branch='xxx' group by
benefit
OPEN MemList
FETCH NEXT FROM MemList
INTO @.MemID, @.total
set @.RowNum = 0
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.RowNum = @.RowNum + 1
print cast(@.RowNum as varchar) + ' ' + LEFT(@.MemID, 1) + ', Total
nr. of records ' + cast(@.total as varchar)
---
declare namesList scroll cursor -- Declare the Cursor as a Scroll
Cursor
FOR select surname, name from cust where branch='xxx' and
benefit=@.MemID
OPEN namesList
FETCH NEXT FROM namesList
INTO @.lname, @.fname
set @.RowNum1 = 0
WHILE @.@.FETCH_STATUS = 0 AND @.RowNum1 < 5
BEGIN
set @.RowNum1 = @.RowNum1 + 1
print ' ' + cast(@.RowNum1 as varchar) + ' ' + LEFT(@.lname,1) +
', ' +
@.fname
FETCH NEXT FROM namesList
INTO @.lname, @.fname
END
--IF @.total >= 6 -- Remove this from here...
WHILE @.@.FETCH_STATUS = 0 And @.Total >= 6 ... and put it here...
BEGIN
FETCH LAST FROM namesList
INTO @.lname, @.fname
PRINT ' ... There are ' + ' ' + cast((@.total - 5) as varchar) + ' more
records'
PRINT ' -->> The last record being ' + LEFT(@.lname, 1) + ', ' +
@.fname
Set @.total = 0 --Set The Total to Zero so this loop finishes
END
CLOSE namesList
DEALLOCATE namesList
---
FETCH NEXT FROM MemList
INTO @.MemID, @.total
END
CLOSE MemList
DEALLOCATE MemList|||Why use a cursor to do all that formatting rather than just output a
query? Have you looked up the CUBE / ROLLUP operator in Books Online?
It would help you with the total counts.
If you want help to write a query instead of a cursor then please post
DDL and sample data. Cursors are rarely a good idea and surely not for
this - there really are much better ways to write reports.
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||hehe!
I was going to maybe post an example to use rather than a cursor -- but
I haven't really got the time!
And without any DDL - I definitely don't have time... ;-)
Barry|||This could be close to what you want but it is untested. It should give
you the first 5 customers per benefit. I've no way of knowing what you
mean by "first" and "last" so I've assumed alphabetical order by name.
You didn't specify ORDER BY so the order you will get from your cursor
is undefined and may be unreliable.
SELECT C.benefit, C.surname, C.name, B.row_cnt, B.last_cust
FROM cust AS C,
(SELECT benefit, COUNT(*) AS row_cnt,
MAX(surname+','+name) AS last_cust
FROM cust
GROUP BY benefit) AS B
WHERE branch='xxx'
AND 5 <=
(SELECT COUNT(*)
FROM cust
WHERE surname+','+name <
C.surname+','+C.name)
AND B.benefit = C.benefit
ORDER BY C.benefit, C.surname, C.name ;
The pretty formatting you can do at the front end or in a reporting
tool.
Hope this helps.
David Portas
SQL Server MVP
--|||Correction. Change "<=" to ">="
David Portas
SQL Server MVP
--