Customized color in Column chart?

I am using MS Reporting 2000 for Report where in one of the report I am
using column chart. Now I would like to give customized color to each column
in this chart but don't know how to implement it.
Please help me out from this situation!
Thanks,
SachinBasically the only way I have figured it outs is by going to the chart
properties>data tab>edit your value>appearance tab>click series style button
at bottom> fill tab. Once there you can eithere choose a different color
for all the bars or if you want a specific color for each bar showing up you
have to click on the expression button and write a iif statement stating
those colors. If you have any more questions let me know.
"Sachin Punatar" <Sachin Punatar@.discussions.microsoft.com> wrote in message
news:D685EB20-4E10-4214-B62D-7FC932E019EE@.microsoft.com...
>I am using MS Reporting 2000 for Report where in one of the report I am
> using column chart. Now I would like to give customized color to each
> column
> in this chart but don't know how to implement it.
> Please help me out from this situation!
> Thanks,
> Sachin
>|||Ben,
Thanks a tone for your reply.
This is absolutely right. But in my case i am using MS Reporting 2000 where
i am not able to have series style option in Appearance tab in data section.
To come out from this issue I have created one SetColor fuction in code
window of Report property but i am not sure how and where to call this
function so that it will create custom color for series. See Below example:
Public Function SetColor(N As String) As String
Select Case N
Case "Incurred"
Return "#fa8072"
Case "Billable"
Return "#eee8aa"
Case "Billed"
Return "#c0ffc0"
Case "Collected"
Return "#4169e1"
End Select
End Function
Now what i tried to call this function from Data-->Values-->Edit-->Value
property. And i am still not able to get appropriate output.
Please let me know is there any other work around for this.
Thanks in advance.
Sachin Punatar
"Ben Watts" wrote:
> Basically the only way I have figured it outs is by going to the chart
> properties>data tab>edit your value>appearance tab>click series style button
> at bottom> fill tab. Once there you can eithere choose a different color
> for all the bars or if you want a specific color for each bar showing up you
> have to click on the expression button and write a iif statement stating
> those colors. If you have any more questions let me know.
> "Sachin Punatar" <Sachin Punatar@.discussions.microsoft.com> wrote in message
> news:D685EB20-4E10-4214-B62D-7FC932E019EE@.microsoft.com...
> >I am using MS Reporting 2000 for Report where in one of the report I am
> > using column chart. Now I would like to give customized color to each
> > column
> > in this chart but don't know how to implement it.
> >
> > Please help me out from this situation!
> >
> > Thanks,
> > Sachin
> >
>
>|||Sachin Punatar wrote:
> > "Sachin Punatar" <Sachin Punatar@.discussions.microsoft.com> wrote in message
> > news:D685EB20-4E10-4214-B62D-7FC932E019EE@.microsoft.com...
> > >I am using MS Reporting 2000 for Report where in one of the report I am
> > > using column chart. Now I would like to give customized color to each
> > > column
> > > in this chart but don't know how to implement it.
> > >
> > > Please help me out from this situation!
> > >
> > > Thanks,
> > > Sachin
Give this a try:
http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
(Courtesy of Brian Welcker)
Sean G.|||Sean -
Thanks for your favorable reply.
I have tried out with similar way as it was mentioned in below article:
http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
But I am not able to set customized color for each column in the chart.
Please let me know if this requires SP1 or SP2 in my machine.
Because when I tried to call custom function written in code window of
Report property, it returns nothing. I have wrote "code.SetColor(values)" in
Data-->Values-->Edit--> Value tab but i dont think that it is calling custom
funciton properly. I strongly have doubts that it willl require SP1 or SP2 on
my machine.
Please verify and clear my doubts on this.
Seeking for your help.
Thanks in advance,
Sachin Punatar
"SeanGerman@.gmail.com" wrote:
> Sachin Punatar wrote:
> > > "Sachin Punatar" <Sachin Punatar@.discussions.microsoft.com> wrote in message
> > > news:D685EB20-4E10-4214-B62D-7FC932E019EE@.microsoft.com...
> > > >I am using MS Reporting 2000 for Report where in one of the report I am
> > > > using column chart. Now I would like to give customized color to each
> > > > column
> > > > in this chart but don't know how to implement it.
> > > >
> > > > Please help me out from this situation!
> > > >
> > > > Thanks,
> > > > Sachin
>
> Give this a try:
> http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
> (Courtesy of Brian Welcker)
>
> Sean G.
>sql

Customize dimension attributes in SSAS

I have a time dimension table with an integer column "Quarter". In the SSAS Dimension I would like to display this as "Q 1" etc. instead of just "1".

I could easily add a column in the root table, but would prefer to format the column in the SSAS dimension. Can't find a "Format" property for an attribute. Is there a way to do this?

The most simple way to do this is to add a named calculation(a new column) in the data source view for the dimension table.

Add this attribute to the dimension or use is as the name column for the quarter attribute/column.

Add: 'Q' + ' ' + Cast(Quarter as Char(1))

HTH

Thomas Ivarsson

|||if u have choosen "typical" time dimension in analysis services, u would get "Quarter" in the display.

customer table

I have a customer table with the column "invoice_customer" (this is the customer_acccount of account where the bill is invoiced..)

so to get the invoice_customer address for the customer account - 13301

SELECT B1.address as InvoiceAddress
from CUSTOMER AS B1 , CUSTOMER AS E1
WHERE B1.customer_account = E1.invoice_customer and E1.customer_account = '13310'

i want to add the above InvoiceAddress to the query below:

select customer_account, order_no, date_req, del_address (InvoiceAddress)
from CUSTOMERS
INNER JOIN Orders on CUSTOMERS.customer_account = Orders.account
where status 'D'

How would I put the 2 togtheer...

Thanks in advance!!!please always identify exactly which table each of your columns comes from

if you don't, you're making us guess, and do extra work trying to figure out what you're doingselect E1.customer_account
, O.order_no
, O.date_req
, B1.address as InvoiceAddress
from CUSTOMERS as B1
inner
join CUSTOMERS as E1
on E1.invoice_customer = B1.customer_account
and E1.customer_account = '13310'
INNER
JOIN Orders as O
on O.account = B1.customer_account
where B1.status = 'D'|||Im sorry I will try and be more careful next time..

Thanks for your help :)

CustomColumn

Hi All, How to define a column in sql2k with format of its value as
10,000,000
TIA"Vai2000" <nospam@.microsoft.com> wrote in message
news:eBl8SrazFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Hi All, How to define a column in sql2k with format of its value as
> 10,000,000
>
> TIA
>
Formatting is a front-end issue. You should probably create the column as a
BIGINT, or some type of Decimal.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi
create table #t
(
col1 int,
col2 as convert(varchar,cast(col1 as money),1)
)
insert into #t values (10000)
select * from #t
If it does not help you ,please lookup SUBSTRING system function in the BOL
"Vai2000" <nospam@.microsoft.com> wrote in message
news:eBl8SrazFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Hi All, How to define a column in sql2k with format of its value as
> 10,000,000
>
> TIA
>|||You don't. Formatting is controlled by your client application, not by
SQL Server.
If it's a numeric column then use one of the numeric datatypes.
David Portas
SQL Server MVP
--|||how about if you are exporting data to excel and want to populate in excel
in that format...avoiding writing format in excel...
TIA
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128957032.919975.66170@.f14g2000cwb.googlegroups.com...
> You don't. Formatting is controlled by your client application, not by
> SQL Server.
> If it's a numeric column then use one of the numeric datatypes.
> --
> David Portas
> SQL Server MVP
> --
>|||"Vai2000" <nospam@.microsoft.com> wrote in message
news:%23F4F4EbzFHA.2792@.tk2msftngp13.phx.gbl...
> how about if you are exporting data to excel and want to populate in excel
> in that format...avoiding writing format in excel...
Still a front-end issue.
Storing is not the same as displaying or migrating.
If you want to migrate it as something else, then you have multiple options.
You could create a view and convert it to just about any format that you
wish.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks, I am looking the convert format to the desired Column
type..(1,00,000)
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OZNzqIbzFHA.612@.TK2MSFTNGP10.phx.gbl...
> "Vai2000" <nospam@.microsoft.com> wrote in message
> news:%23F4F4EbzFHA.2792@.tk2msftngp13.phx.gbl...
excel
>
> Still a front-end issue.
> Storing is not the same as displaying or migrating.
> If you want to migrate it as something else, then you have multiple
options.
> You could create a view and convert it to just about any format that you
> wish.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||how to convert a view to desired format?
Thanks a lot
"Vai2000" <nospam@.microsoft.com> wrote in message
news:%23sqAnObzFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Thanks, I am looking the convert format to the desired Column
> type..(1,00,000)
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OZNzqIbzFHA.612@.TK2MSFTNGP10.phx.gbl...
> excel
> options.
>|||"Vai2000" <nospam@.microsoft.com> wrote in message
news:%23iPvPQbzFHA.3660@.TK2MSFTNGP15.phx.gbl...
> how to convert a view to desired format?
> Thanks a lot
Take a look at the CONVERT function in SQL Server.
-- Create a table variable to hold our data.
DECLARE @.Foo TABLE (
x bigint
)
-- Load a value
INSERT @.Foo VALUES (10000000)
-- Convert BigInt to money datatype, then convert the money to a character
string and specify the "1" flag
-- so that commas are added in the appropriate place.
SELECT CONVERT(varchar(20), CONVERT(money, x), 1) FROM @.Foo
Rick Sawtell
MCT, MCSD, MCDBA

Custom user field in database

Is there a SQL 2005 table containing meta data about my database where I can set a column whose
purpose is to hold custom user data?
I have an application for which I need to change the table structure from version to version. Each
time I distribute an new build of the application, the setup program lookupthe DB revision level
then issue the proper DDL calls to perform the updates.
I could always create a small user table with only one column into which I would store the DB
revision level. However, I would prefer store this INT value somewhere else if SQL 2005 offers a
capability to do so.
Gaetan.
Look in Books Online about using Database Extended Properties. Relatively
easy to code to, and retrieve from; custom name value pairs.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Gaetan" <me@.somewhere.com> wrote in message
news:q4d9m2t2o8suqoo678js6qndkk59721ji5@.4ax.com...
> Is there a SQL 2005 table containing meta data about my database where I
> can set a column whose
> purpose is to hold custom user data?
> I have an application for which I need to change the table structure from
> version to version. Each
> time I distribute an new build of the application, the setup program
> lookupthe DB revision level
> then issue the proper DDL calls to perform the updates.
> I could always create a small user table with only one column into which I
> would store the DB
> revision level. However, I would prefer store this INT value somewhere
> else if SQL 2005 offers a
> capability to do so.
> Gaetan.
|||sys.sp_addextendedproperty and sys.sp_updateextendedproperty are exactly what I needed.
Thank you.

Custom user field in database

Is there a SQL 2005 table containing meta data about my database where I can set a column whose
purpose is to hold custom user data?
I have an application for which I need to change the table structure from version to version. Each
time I distribute an new build of the application, the setup program lookupthe DB revision level
then issue the proper DDL calls to perform the updates.
I could always create a small user table with only one column into which I would store the DB
revision level. However, I would prefer store this INT value somewhere else if SQL 2005 offers a
capability to do so.
Gaetan.Look in Books Online about using Database Extended Properties. Relatively
easy to code to, and retrieve from; custom name value pairs.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Gaetan" <me@.somewhere.com> wrote in message
news:q4d9m2t2o8suqoo678js6qndkk59721ji5@.4ax.com...
> Is there a SQL 2005 table containing meta data about my database where I
> can set a column whose
> purpose is to hold custom user data?
> I have an application for which I need to change the table structure from
> version to version. Each
> time I distribute an new build of the application, the setup program
> lookupthe DB revision level
> then issue the proper DDL calls to perform the updates.
> I could always create a small user table with only one column into which I
> would store the DB
> revision level. However, I would prefer store this INT value somewhere
> else if SQL 2005 offers a
> capability to do so.
> Gaetan.|||sys.sp_addextendedproperty and sys.sp_updateextendedproperty are exactly what I needed.
Thank you.

Custom user field in database

Is there a SQL 2005 table containing meta data about my database where I can
set a column whose
purpose is to hold custom user data?
I have an application for which I need to change the table structure from ve
rsion to version. Each
time I distribute an new build of the application, the setup program lookupt
he DB revision level
then issue the proper DDL calls to perform the updates.
I could always create a small user table with only one column into which I w
ould store the DB
revision level. However, I would prefer store this INT value somewhere else
if SQL 2005 offers a
capability to do so.
Gaetan.Look in Books Online about using Database Extended Properties. Relatively
easy to code to, and retrieve from; custom name value pairs.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Gaetan" <me@.somewhere.com> wrote in message
news:q4d9m2t2o8suqoo678js6qndkk59721ji5@.
4ax.com...
> Is there a SQL 2005 table containing meta data about my database where I
> can set a column whose
> purpose is to hold custom user data?
> I have an application for which I need to change the table structure from
> version to version. Each
> time I distribute an new build of the application, the setup program
> lookupthe DB revision level
> then issue the proper DDL calls to perform the updates.
> I could always create a small user table with only one column into which I
> would store the DB
> revision level. However, I would prefer store this INT value somewhere
> else if SQL 2005 offers a
> capability to do so.
> Gaetan.|||sys.sp_addextendedproperty and sys.sp_updateextendedproperty are exactly w
hat I needed.
Thank you.sql

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

I have a parameter in my report called SortType and depending on the value of the parameter I want to sort by a particular column. I have tried doing this by using a custom function as follows:

Function SortTypeValue() As String
If Report.Parameters!SortType.Value = "1" Then
Return "=Fields!StockDesc.Value"
else Return "=Fields!StockCode.Value"
End If
End Function

Then in the sorting tab I have entered in the expression field: =Code.SortTypeValue().

However this is not doing anything. The data is just displayed in the order it is taken from the database. Can this be done? If so can anybody please tell me what I am doing wrong?

Thanks in advance.
Dynamic use of parameters for grouping can be achieved using the following syntax:

=Fields(Parameters!ParamName.Value).Value

Maybe this will also work for sorting

--

Your code is going to return the same value regardless of sort type anyway.
=Fields!StockDesc.Value
You don't need it if the above method works.

--

You can set interactive sorting so the user can sort on any column they wish:

right click on the column header
interactive sort
tick tickbox
set sort expression to the value held in that particular column (i.e. if column is sales amt then =fields!salesamt.value)

done

if you need to do this for a whole raft of columns, it is quicker to do it in the (F4) properties window:
expand the 'user sort' propery and set the 'sort expression' drop down as per the column name
NB keep sort expression selected otherwise when you click on the next column you wish to add a sort to, the properties collapse|||You say my code will return the same value regardless of sort type anyway. Why? Each condition returns a different string.

If I was to use =Fields(Parameters!ParamName.Value).Value I would still need a way to determine which column the user wants to sort on so I know which ParamName to insert.|||To paraphrase the jerky boys "my eyes is going crazy"
but you still don't need the code anyway.

With regards to using my technique for sorting.....

It works.

I just created a very simple report.
I added one parameter called 'sort by'

I just set the values of this to the names of the db columns returned. Spelling exactly as per db cols!

In the table, sorting tab, i just added this

=Fields(Parameters!sortby.Value).Value

go to the preview, Pick your column name, run

simple|||

One step further. . .

Create the parameter as described by "adolf garlic" and also create a parameter named "SortDirection" and give the parameter values "Ascending" and "Descending"

In the table/sort "Expression" field(s) enter:

=Iif(Parameters!SortDirection.Value = "Ascending", Fields(Parameters!SortBy.Value).Value, "")

=Iif(Parameters!SortDirection.Value = "Descending", Fields(Parameters!SortBy.Value).Value, "")

and set the proper direction for both expressions.|||Thanks adolf garlic. Works like a treat. Exactly what I was after.
sql

Custom Property for Remove Duplicates transform Input Row

Im working through the MS example of "removeDuplicates". I cant seem to figure out how to add custom property for input column.

I added the helper method:
private static void AddIsKeyCustomPropertyToInput(IDTSInput90 input, object value)
{
IDTSCustomProperty90 isKey = input.CustomPropertyCollection.New();
isKey.Name = "IsKey";
isKey.Value = value;
}
I call it from:
public override void ProvideComponentProperties()
{
//...
AddIsKeyCustomPropertyToInput(input, false);
//...
}
public override void ReinitializeMetaData()
{
IDTSInput90 input = ComponentMetaData.InputCollection[0];
if (input.CustomPropertyCollection.Count == 0)
{
AddIsKeyCustomPropertyToInput(input, false);
}
// ...
}

However when I deployed it and added the component to SSIS package - I cant see the Custom Column "IsKey" in the input column properties window.
What am I missing - please help As stated by some fore-sightful guy in a blog -

the way to SSIS enlightment is treacherous

. it seems i was missing something very basic - i was trying to set custom property for the whole input instead of just input column.

So instead of 2 above mentioned calls (in ProvideComponentProperties and ReinitializeMetaData ) i needed to set it in following:

public override IDTSInputColumn90 SetUsageType(int inputID, IDTSVirtualInput90 virtualInput, int lineageID, DTSUsageType usageType)
{
if (virtualInput == null)
{
throw new ArgumentNullException("virtualInput");
}

IDTSVirtualInputColumn90 vCol = virtualInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(lineageID);
IDTSInputColumn90 col = null;

/// No support for BLOB image columns.
if (vCol.DataType == DataType.DT_IMAGE)
throw new Exception("Binary data types not supported.");

/// If the usageType is UT_IGNORED, then the column is being removed.
/// So remove it from the outputs also.
if (usageType == DTSUsageType.UT_IGNORED)
{
/// ...
}
else
{
/// Let the base class add the input column.
col = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
// Store the lineageID of the input column in a custom property of the output column.
IDTSCustomProperty90 inputColIsKey = col.CustomPropertyCollection.New();
inputColIsKey.Name = "IsKey";
inputColIsKey.Value = false;

/// Add an output column to the distinct and duplicate outputs.
AddOutputColumn(ComponentMetaData.OutputCollection[0].ID, col);
AddOutputColumn(ComponentMetaData.OutputCollection[1].ID, col);
}
return col;
}

Hopefully - when i have created this transform to filter duplicates based on (Primery) Key column(s) i can post it in my blog (to be created)|||Now if only I could figure out how to pass this custom "IsKey" property to class "Row" Equals operator and to class "Buffer" sort method

Custom Page Size

Hai,

Is it possible to setup custom page size in crystal reports? I do have about 40 column and it is necessary to display and printing in report viewing. What I do now is I used A3 paper size & landscape. In report design, the page ruler at the top shows 23(inc / cm..not sure about this). Anyhow..can I adjust it to be more bigger than this? Currently the reports can generate the data as per requirement..but it look quite messy and bit cram. Kindly adviseYou need to install a printer driver for larger sized paper.
Bit of a pain when Crystal supports extract to Excel but won't let you design a report bigger than an installed printer driver size.|||Hi JaganEllis

Thanks for the response.
I did install the printer software. Adobe PDF creator..which is the software allowed me the option to choose A1 to A3 size. When I used A2 page size..it expand till 26 (page ruler) ..it look better than used a3 or A4 size. Unfortunatelly still quite messy.. so, I've changed to A1 size..and now I can resize the column and the data printing little bit nice. However..when tested it in report viewing..the error message come out 'Page too large' .. ayyo! Another problems come out.. hmmm what should I do? anybody can suggest me another printer software that can printing for larger size.

Out of idea already..kindly advise.
Crystal 2402

Custom 'Order By' Function?

Hi I have a column varchar(4). Users enter values in one of 5 formats -
1) 1,2,3,4...10,11
2) 01,02,03,04...10,11
3) A1,A2,A3,B1,B2,B3...B10,B11
4) 1.1,2.1,3.1.....10.1,11.1
5) 1.1, 1.1A, 1.1B, 2.1, 2.1A, 2.1B....10.1,10.1A
The queruies that select from this table will only select records with
one of the formats at any one time. Is it possible to ensure the order
is always logically correct based on numerical and alphabetical
ordering, as above?
So far its seems ok except formats 4 & 5 where I get the folowoing
output-
1.3 1.3A 1.3P 11.3 16.3 2.3 2.3P 2.3S
Thanks
hals_leftHi
your query will work fine if ur 4 and 5 looks similar to 2.
the results in 4 & 5 are considered and sorted as per the char value.
prefix 0 ans see the results.
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"hals_left" wrote:

> Hi I have a column varchar(4). Users enter values in one of 5 formats -
>
> 1) 1,2,3,4...10,11
> 2) 01,02,03,04...10,11
> 3) A1,A2,A3,B1,B2,B3...B10,B11
> 4) 1.1,2.1,3.1.....10.1,11.1
> 5) 1.1, 1.1A, 1.1B, 2.1, 2.1A, 2.1B....10.1,10.1A
> The queruies that select from this table will only select records with
> one of the formats at any one time. Is it possible to ensure the order
> is always logically correct based on numerical and alphabetical
> ordering, as above?
> So far its seems ok except formats 4 & 5 where I get the folowoing
> output-
> 1.3 1.3A 1.3P 11.3 16.3 2.3 2.3P 2.3S
> Thanks
> hals_left
>|||On 28 Jul 2005 08:00:54 -0700, hals_left wrote:

>Hi I have a column varchar(4). Users enter values in one of 5 formats -
>
>1) 1,2,3,4...10,11
>2) 01,02,03,04...10,11
>3) A1,A2,A3,B1,B2,B3...B10,B11
>4) 1.1,2.1,3.1.....10.1,11.1
>5) 1.1, 1.1A, 1.1B, 2.1, 2.1A, 2.1B....10.1,10.1A
Hi hals_left,
How does one store 10.1A in a varchar(4) column?

> is thgere now way to write a different
>ordering function?
Try the following. It's not pretty, but it might work:
ORDER BY
CASE
WHEN my_column LIKE '[A-Z]%'
THEN LEFT (my_column, 1)
END,
CASE
WHEN my_column LIKE '[A-Z]%'
THEN CAST (SUBSTRING (my_column, 2, 3) AS int)
WHEN my_column LIKE '%.%'
THEN CAST (LEFT (my_column, CHARINDEX ('.', my_column) - 1) AS int)
ELSE CAST (my_column AS int)
END,
CASE
WHEN my_column LIKE '%.%'
THEN SUBSTRING (my_column, CHARINDEX ('.', my_column) + 1, 4)
END
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Custom names for summary fields

Using Crystal Reports 8.5 I have created a report which displays a pie chart of the column totals in a report. Unfortunately the names of the summary fields are used as labels for the pie chart slices and they are ugly - things like "sum qryOrders.OrderValue". Is there a way to replace these labels with my own choice of text?From the deafening silence, the answer appears to be 'No'. In case anyone else has the same issue, here is a workaround. Suppress the display of pie slice labels. Suppress the display of the chart legend. Create your own legend box using the basic drawing tools - filled rectangles - and add your own text labels.|||For the sake of future Googlers; I too was having this problem, and I had given up when I realized that if you sort "in specified order", you can then set up your "Named Groups" such that the "Group Name" is the name that you wish to have displayed.

For example, the formula for one of my named groups is "@.Allocation" "is equal to" "International Equity", and the name of the group is "Int'l Equity".

Works like a charm, sorry I'm too late to help you.|||Well, within a couple of minutes of posting, I realized that this only works if you have only one field to show a value for. So, it's not going to work where I really need it to, but still, it's useful elsewhere.

custom identity field

I have what I thought would be a simple task but I keep hitting dead ends.
My database has many tables, each with an identity column as the primary
key. This worked fine until we had a requirement to import rows from other
sites/database installs (same tables, different servers). We won't have
very many databases, but the table keys must be unique to the database and
the sites. Since our users refer to the rows by ID, GUIDs are far too
awkward and will not work. The key needs to work much like the identity
field, easy to query the last key added and the key is generated
automagically.
The easy solution would be to just add the site code (3 letter alpha) to the
keys. We had hoped to just create a simple function that would return the
key and we could set the default value of the key to point to the function.
For example, the table User would have a primary key of UserID with a
default value of getKey('user') which would return 'AAA1' for the first user
entered in site 'AAA'. If the first user from site 'BBB' was imported, it
would be simple to synchronize and determine at a glance as we would have a
user 'BBB1'.
After playing around with functions, procs, default values, creating system
functions, formulas, triggers, we have not found a way to do what we want.
Anybody have any advice on this? Is there a better way?
Don't use IDENTITY keys to maintain integrity between databases - it's a
waste of time. Use alternate keys for that. The only sensible use of an
IDENTITY key is as a SURROGATE so just assign new IDENTITY keys (parent and
foreign keys) when you import the data.
David Portas
SQL Server MVP
|||>> "Don't use IDENTITY keys...Use alternate keys..."
That is exactly my question. I stated that I cannot use IDENTITY keys and
want to create my own alternate key. How can I generate an alternate key
that can, as seemlessly as possible, replace the IDENTITY key? I've tried
to create system functions, triggers, and procs but cannot seem to find a
mechanism whereby I can autogenerate a default value for the row based on a
procedure call.
|||I don't see a problem. Your tables on the source systems should already
have alternate keys because IDENTITY should never be the only key of a
table. So if you need to preserve potential duplicates between the two
systems just add another column to make up a compound key. The
additional column identifies the source as "A", "B", "C" or whatever.
Why go to the trouble of putting it into one single column? You can
always concatenate the key as one in a view if you need to.
David Portas
SQL Server MVP
|||An example is worth a thousand words so here's how I would do it.
The two sites, A and B:
CREATE TABLE A_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE A_bar (x INTEGER NOT NULL REFERENCES A_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
CREATE TABLE B_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE B_bar (x INTEGER NOT NULL REFERENCES B_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
Generate some sample data:
INSERT INTO A_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO A_bar (x,k)
SELECT 1,'XXX' UNION ALL
SELECT 1,'YYY' UNION ALL
SELECT 2,'XXX' UNION ALL
SELECT 2,'ZZZ'
INSERT INTO B_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO B_bar (x,k)
SELECT 1,'111' UNION ALL
SELECT 1,'222' UNION ALL
SELECT 2,'111' UNION ALL
SELECT 2,'333'
These are the two tables for the merged data:
CREATE TABLE foo (x INTEGER IDENTITY PRIMARY KEY, source CHAR(1) NOT
NULL, z CHAR(10) NOT NULL, UNIQUE (source,z))
CREATE TABLE bar (x INTEGER NOT NULL REFERENCES foo (x), k CHAR(10) NOT
NULL, PRIMARY KEY (x,k))
Now do the merge:
INSERT INTO foo (source, z)
SELECT 'A', z
FROM A_foo
UNION ALL
SELECT 'B', z
FROM B_foo
INSERT INTO bar (x,k)
SELECT foo.x, A_bar.k
FROM A_bar
JOIN A_foo
ON A_foo.x = A_bar.x
JOIN foo
ON A_foo.z = foo.z
AND foo.source = 'A'
UNION ALL
SELECT foo.x, B_bar.k
FROM B_bar
JOIN B_foo
ON B_foo.x = B_bar.x
JOIN foo
ON B_foo.z = foo.z
AND foo.source = 'B'
You'll probably want to add a WHERE NOT EXISTS condition to the INSERTs
to ensure that only new data gets loaded.
David Portas
SQL Server MVP

custom identity field

I have what I thought would be a simple task but I keep hitting dead ends.
My database has many tables, each with an identity column as the primary
key. This worked fine until we had a requirement to import rows from other
sites/database installs (same tables, different servers). We won't have
very many databases, but the table keys must be unique to the database and
the sites. Since our users refer to the rows by ID, GUIDs are far too
awkward and will not work. The key needs to work much like the identity
field, easy to query the last key added and the key is generated
automagically.
The easy solution would be to just add the site code (3 letter alpha) to the
keys. We had hoped to just create a simple function that would return the
key and we could set the default value of the key to point to the function.
For example, the table User would have a primary key of UserID with a
default value of getKey('user') which would return 'AAA1' for the first user
entered in site 'AAA'. If the first user from site 'BBB' was imported, it
would be simple to synchronize and determine at a glance as we would have a
user 'BBB1'.
After playing around with functions, procs, default values, creating system
functions, formulas, triggers, we have not found a way to do what we want.
Anybody have any advice on this? Is there a better way?Don't use IDENTITY keys to maintain integrity between databases - it's a
waste of time. Use alternate keys for that. The only sensible use of an
IDENTITY key is as a SURROGATE so just assign new IDENTITY keys (parent and
foreign keys) when you import the data.
David Portas
SQL Server MVP
--|||>> "Don't use IDENTITY keys...Use alternate keys..."
That is exactly my question. I stated that I cannot use IDENTITY keys and
want to create my own alternate key. How can I generate an alternate key
that can, as seemlessly as possible, replace the IDENTITY key? I've tried
to create system functions, triggers, and procs but cannot seem to find a
mechanism whereby I can autogenerate a default value for the row based on a
procedure call.|||I don't see a problem. Your tables on the source systems should already
have alternate keys because IDENTITY should never be the only key of a
table. So if you need to preserve potential duplicates between the two
systems just add another column to make up a compound key. The
additional column identifies the source as "A", "B", "C" or whatever.
Why go to the trouble of putting it into one single column? You can
always concatenate the key as one in a view if you need to.
David Portas
SQL Server MVP
--|||An example is worth a thousand words so here's how I would do it.
The two sites, A and B:
CREATE TABLE A_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE A_bar (x INTEGER NOT NULL REFERENCES A_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
CREATE TABLE B_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE B_bar (x INTEGER NOT NULL REFERENCES B_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
Generate some sample data:
INSERT INTO A_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO A_bar (x,k)
SELECT 1,'XXX' UNION ALL
SELECT 1,'YYY' UNION ALL
SELECT 2,'XXX' UNION ALL
SELECT 2,'ZZZ'
INSERT INTO B_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO B_bar (x,k)
SELECT 1,'111' UNION ALL
SELECT 1,'222' UNION ALL
SELECT 2,'111' UNION ALL
SELECT 2,'333'
These are the two tables for the merged data:
CREATE TABLE foo (x INTEGER IDENTITY PRIMARY KEY, source CHAR(1) NOT
NULL, z CHAR(10) NOT NULL, UNIQUE (source,z))
CREATE TABLE bar (x INTEGER NOT NULL REFERENCES foo (x), k CHAR(10) NOT
NULL, PRIMARY KEY (x,k))
Now do the merge:
INSERT INTO foo (source, z)
SELECT 'A', z
FROM A_foo
UNION ALL
SELECT 'B', z
FROM B_foo
INSERT INTO bar (x,k)
SELECT foo.x, A_bar.k
FROM A_bar
JOIN A_foo
ON A_foo.x = A_bar.x
JOIN foo
ON A_foo.z = foo.z
AND foo.source = 'A'
UNION ALL
SELECT foo.x, B_bar.k
FROM B_bar
JOIN B_foo
ON B_foo.x = B_bar.x
JOIN foo
ON B_foo.z = foo.z
AND foo.source = 'B'
You'll probably want to add a WHERE NOT EXISTS condition to the INSERTs
to ensure that only new data gets loaded.
David Portas
SQL Server MVP
--

custom identity field

I have what I thought would be a simple task but I keep hitting dead ends.
My database has many tables, each with an identity column as the primary
key. This worked fine until we had a requirement to import rows from other
sites/database installs (same tables, different servers). We won't have
very many databases, but the table keys must be unique to the database and
the sites. Since our users refer to the rows by ID, GUIDs are far too
awkward and will not work. The key needs to work much like the identity
field, easy to query the last key added and the key is generated
automagically.
The easy solution would be to just add the site code (3 letter alpha) to the
keys. We had hoped to just create a simple function that would return the
key and we could set the default value of the key to point to the function.
For example, the table User would have a primary key of UserID with a
default value of getKey('user') which would return 'AAA1' for the first user
entered in site 'AAA'. If the first user from site 'BBB' was imported, it
would be simple to synchronize and determine at a glance as we would have a
user 'BBB1'.
After playing around with functions, procs, default values, creating system
functions, formulas, triggers, we have not found a way to do what we want.
Anybody have any advice on this? Is there a better way?Don't use IDENTITY keys to maintain integrity between databases - it's a
waste of time. Use alternate keys for that. The only sensible use of an
IDENTITY key is as a SURROGATE so just assign new IDENTITY keys (parent and
foreign keys) when you import the data.
--
David Portas
SQL Server MVP
--|||>> "Don't use IDENTITY keys...Use alternate keys..."
That is exactly my question. I stated that I cannot use IDENTITY keys and
want to create my own alternate key. How can I generate an alternate key
that can, as seemlessly as possible, replace the IDENTITY key? I've tried
to create system functions, triggers, and procs but cannot seem to find a
mechanism whereby I can autogenerate a default value for the row based on a
procedure call.|||I don't see a problem. Your tables on the source systems should already
have alternate keys because IDENTITY should never be the only key of a
table. So if you need to preserve potential duplicates between the two
systems just add another column to make up a compound key. The
additional column identifies the source as "A", "B", "C" or whatever.
Why go to the trouble of putting it into one single column? You can
always concatenate the key as one in a view if you need to.
--
David Portas
SQL Server MVP
--|||An example is worth a thousand words so here's how I would do it.
The two sites, A and B:
CREATE TABLE A_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE A_bar (x INTEGER NOT NULL REFERENCES A_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
CREATE TABLE B_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE B_bar (x INTEGER NOT NULL REFERENCES B_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
Generate some sample data:
INSERT INTO A_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO A_bar (x,k)
SELECT 1,'XXX' UNION ALL
SELECT 1,'YYY' UNION ALL
SELECT 2,'XXX' UNION ALL
SELECT 2,'ZZZ'
INSERT INTO B_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO B_bar (x,k)
SELECT 1,'111' UNION ALL
SELECT 1,'222' UNION ALL
SELECT 2,'111' UNION ALL
SELECT 2,'333'
These are the two tables for the merged data:
CREATE TABLE foo (x INTEGER IDENTITY PRIMARY KEY, source CHAR(1) NOT
NULL, z CHAR(10) NOT NULL, UNIQUE (source,z))
CREATE TABLE bar (x INTEGER NOT NULL REFERENCES foo (x), k CHAR(10) NOT
NULL, PRIMARY KEY (x,k))
Now do the merge:
INSERT INTO foo (source, z)
SELECT 'A', z
FROM A_foo
UNION ALL
SELECT 'B', z
FROM B_foo
INSERT INTO bar (x,k)
SELECT foo.x, A_bar.k
FROM A_bar
JOIN A_foo
ON A_foo.x = A_bar.x
JOIN foo
ON A_foo.z = foo.z
AND foo.source = 'A'
UNION ALL
SELECT foo.x, B_bar.k
FROM B_bar
JOIN B_foo
ON B_foo.x = B_bar.x
JOIN foo
ON B_foo.z = foo.z
AND foo.source = 'B'
You'll probably want to add a WHERE NOT EXISTS condition to the INSERTs
to ensure that only new data gets loaded.
--
David Portas
SQL Server MVP
--

Custom Data Flow Component Column Mapping Question

Hi,

I'm having my first go at developing a destination adapter which will send data to an update Web Service.

I've got some rather big gaps in my understanding. I've been following the various samples I've found on the net and have validated my mapping and picked up all the available column names and datatypes which are appearing in the Input and Output Properties tab of the Advanced Editor but I only have a tab for "Input Columns" and not "Column Mappings".

Which method defines the availble columns for the user to map?

Let me know if I haven't given enough information.

cheers

sorry - I had searched already but somehow missed this post which answered my question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=239126&SiteID=1

I was having the same confusion about input and output, thinking that because i was creating a *destination* adapter, that I should be creating outputs not inputs [if that makes sense].

Custom Conflict Resolution Question

Hello,
I have to setup custom conflict resolution so that the
most recent update wins using a column 'lasteditdate' in a
a merge replication scenario and have about 150 tables to
do this on. Do I have to configure each table/article
individually or can I do all necessary tables in one shot?
Any help would be greatly appreciated!
Thanks in advance
Gene,
each one has to be done separately. However, you could create the
publication in SQLDMO then loop round the tables if you want a more
efficient way of doing it.
HTH,
Paul Ibison

Custom code using case statement

My report contains a field which shows a '# of days on Hand' column. This
column is the result of a datediff calc. I am not familar with vb.net but I
need to add custom code logic so that if the value in '# of days on hand" is
null that it shows a hardcoded text like "inv" or "onhand". The rest of the
value are >= 0 and I would just want to show there values as-is. Can anyone
show me what the a sample of code would look like to do this and how I call
this in my report?
ANY HELP IS MUCH APPRECIATEDAn expression would work well in this situation
=iif(Fields!column1.Value < 0,"inv",Fields!column1.Value)
I haven't tested this but it should work. If the expression evaluates to
true, i.e. If the value of the field is less than zero, then the string
'inv' is returned, if false, the field's value is returned as-is.
Put this in your column and replace Fields!column1 with whatever your field
is called.
HTH
"stacey" wrote:
> My report contains a field which shows a '# of days on Hand' column. This
> column is the result of a datediff calc. I am not familar with vb.net but I
> need to add custom code logic so that if the value in '# of days on hand" is
> null that it shows a hardcoded text like "inv" or "onhand". The rest of the
> value are >= 0 and I would just want to show there values as-is. Can anyone
> show me what the a sample of code would look like to do this and how I call
> this in my report?
> ANY HELP IS MUCH APPRECIATED

Custom Aggregation Functions like SUM, AVERAGE etc.

Hi,

I want to write a custom aggregation function called PRODUCT (as it exists in Excel) to be used with [Measures].[Monthly Return] column. I could not see such function in AggregateFunction attribute of the mesaure in the Cube explorer.

What are the different ways I can write my own Aggregation functions? OR Achieve similar functionality with any alternative approach?

Appreciate your response.. let me know if further information is required.

-Ashish

You can't write your own Aggregation functions but you can certainly achieve the same result using MDX Script assignments to control how measures roll up. Regarding your specific problem, there is a function in the Analysis Services Stored Procedure project which does exactly what you're looking for:

http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures&title=Multiplication

...although my understanding is that using a sproc in your MDX Script could have an adverse effect on caching, so you might want to test out using the sproc and the technique using logs which is described by Darren Gosbell here:

http://geekswithblogs.net/darrengosbell/archive/2006/07/18/85539.aspx

HTH,

Chris