How to transform "full width" String into "half width" String?

Hi, everyone

There is a table t1 with two fields, such as,
ID NAME
１　　　Ｔｏｋｙｏ
2 Xian
3 America

For there are full-width and half-width strings in the values of the two fields, I can not select and get the right records. So I want to transform the two fields ID and NAME, I fail to find the function in the SQL Server 2005. Please give me some advice.

Thank you very much!try trimming all the blank spaces in the fields before running any select/ use the fields with Trim() running on them.|||thank you, wash.

The function trim only trims the blank spaces in the field.However, field values with full width or half width do not mean there are blank spces in the field.|||I have no idea what you are talking about

what are full/hald width strings?|||I think I know what he wants.

If all the data consists of single words, you can eliminate all the spaces using the REPLACE function:select replace('t o k y o ', ' ', '')
...but if some of the records contain multiple words, you are out of luck.
How did your data end up looking like this in the first place?|||How about this:

select substring (name, 1, length(name)/2)

Truncates half the string, every time. May not be what you want, but...|||Do these guys even get emails when we reply?

Half?

Ever hear of RI?|||sorry, Brett Kaiser , I did not get your email.|||thanks all.

I still failed in the problems with full width and half width. If possible, please create a table, and insert some records with full-width and half-width string, you will find it is difficult to cope with the problem.|||here's a variation of what blindman suggested that will work for your funny-spaced words as long as they are separated by two spaces between words:

select replace(replace(replace('t o k y o n i g h t s', ' ', '__ACK_ACK__'),' ',''),'__ACK_ACK__',' ')

You can replace the __ACK_ACK__ with any string you like as long as it's guaranteed never to appear in the strings you are operating on. I figure __ACK_ACK__ is pretty unlikely to appear since it's Martian. :)|||no problem will have a solution untill you define your problem statement clearly. :S

how to total fields within footer

I have a report footer which includes a number of totals fields. Each
of those fields is computed based on various fields in the data set,
using some rather long expressions.
What I need now is to add a new "super total" field that displays the
sum of three of the other total fields. The only way I can reference
the other three fields in the new field's Value property (without
getting a build error) is with an expression like:
=ReportItems("txtTotal1").Value+ReportItems("txtTotal2").Value+ReportItems("txtTotal3").Value
But even then, the new field shows "#Error" anyway. Does anyone know
how to make this work?Try:
=ReportItems!txtTotal1.Value+ReportItems!txtTotal2.Value+ReportItems!txtTotal3.Value
HTH,
Magendo_man
"silverblatt@.att.net" wrote:
> I have a report footer which includes a number of totals fields. Each
> of those fields is computed based on various fields in the data set,
> using some rather long expressions.
> What I need now is to add a new "super total" field that displays the
> sum of three of the other total fields. The only way I can reference
> the other three fields in the new field's Value property (without
> getting a build error) is with an expression like:
> =ReportItems("txtTotal1").Value+ReportItems("txtTotal2").Value+ReportItems("txtTotal3").Value
> But even then, the new field shows "#Error" anyway. Does anyone know
> how to make this work?
>

How to toggle visibility of a group of columns in RS

Hi,

I need to toggle visibility of group of columns in reporting services report, just like we can do for rows. I know that individual fields can be hidden, but i need to hide/show complete column.

Thanks in anticipation.

Saeed

Hi,

This is not possible in Reporting Services. When do you need to hide/unhide the column? Maybe I can help with finding an alternative solution. For example, you can set the Visibility property to the value of a boolean parameter.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi

Thanks for your response.

I have came up with a work around. I have a summary coulmn based on few deatiled columns. Initialy when report is rendered, only summary column is visible, but has a + link associated with it to show detailed columns. Here is way to achieve it.

Get control name of heading of summary column. Then select all detail columns individualy and in visivility toggle item, type name of that heading summary column, and put visibility off. ( One caution here: Drop down list in visibility toggle item is empty. We will have to type summary heading control name here). Now, view report to test it.

Thanks

Saeed

|||

asalamo alaykom saeed ahmad

you can do that by using models in reporting services

it means that when you using a model you can let the user select within the report builder to use columns dynamicly

and you can do this in a defferent way by using sophesticated tsql using if and then that execute query with a specific columns by passing some parameter for the stored proc

http://www.123writers.com

How to toggle visibility of a group of columns in RS

Hi,

I need to toggle visibility of group of columns in reporting services report, just like we can do for rows. I know that individual fields can be hidden, but i need to hide/show complete column.

Thanks in anticipation.

Saeed

Hi,

This is not possible in Reporting Services. When do you need to hide/unhide the column? Maybe I can help with finding an alternative solution. For example, you can set the Visibility property to the value of a boolean parameter.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi

Thanks for your response.

I have came up with a work around. I have a summary coulmn based on few deatiled columns. Initialy when report is rendered, only summary column is visible, but has a + link associated with it to show detailed columns. Here is way to achieve it.

Get control name of heading of summary column. Then select all detail columns individualy and in visivility toggle item, type name of that heading summary column, and put visibility off. ( One caution here: Drop down list in visibility toggle item is empty. We will have to type summary heading control name here). Now, view report to test it.

Thanks

Saeed

|||

asalamo alaykom saeed ahmad

you can do that by using models in reporting services

it means that when you using a model you can let the user select within the report builder to use columns dynamicly

and you can do this in a defferent way by using sophesticated tsql using if and then that execute query with a specific columns by passing some parameter for the stored proc

http://www.123writers.com

how to sum records by month and year

Dear all,
i had a table named saletable i had fields like
itemgroupid,itemgroupname,saleqty,saleamount and saledate.
i want to sum the saleqty and saleamount by every month and year.but i dont
want to eneter the date every time means no user criteria.
for eaxmple
itemgroupid itemgroupname saleqty saleamount date
23101102 HERO LINER 10.00 50467.29 2004-01-07
23101102 HERO LINER 20.00 10924.58 2005-01-07
23101102 HERO LINER 10.00 50467.29 2004-02-07
23101102 HERO LINER 20.00 10924.58 2005-02-07
23101102 HERO LINER 10.00 50467.29 2004-01-07
23101102 HERO LINER 20.00 10924.58 2005-01-07
23204101 Trading Mudgard 10.00 6000.00 2005-04-21
23204101 Trading Mudgard 10.00 50467.29 2004-01-07
23204101 Trading Mudgard 20.00 10924.58 2005-01-07
23204101 Trading Mudgard 20.00 10924.58 2005-02-07
23204101 Trading Mudgard 10.00 50467.29 2004-01-07
23204101 Trading Mudgard 20.00 10924.58 2005-01-07
I WANT like this
for every month and year
23204101 Trading Mudgard 20.00 100934.58 2004-01
23204101 Trading Mudgard 60.00 2326000.00 2005-01
23204101 Trading Mudgard 10.00 6000.00 2005-07
thanx
waiting for solution
from
Sufian
See GroupBY and DatePart in BOL. Also you may have better luck with this if
you posted in the Programming section.
"Mohd Sufian" <sufian@.aeroflex.co.th> wrote in message
news:1635C8A0-3859-4D65-8CEE-3F27B0350719@.microsoft.com...
> Dear all,
> i had a table named saletable i had fields like
> itemgroupid,itemgroupname,saleqty,saleamount and saledate.
> i want to sum the saleqty and saleamount by every month and year.but i
dont
> want to eneter the date every time means no user criteria.
> for eaxmple
> itemgroupid itemgroupname saleqty saleamount date
> ----
--
> 23101102 HERO LINER 10.00 50467.29
2004-01-07
> 23101102 HERO LINER 20.00 10924.58
2005-01-07
> 23101102 HERO LINER 10.00 50467.29
2004-02-07
> 23101102 HERO LINER 20.00 10924.58
2005-02-07
> 23101102 HERO LINER 10.00 50467.29
2004-01-07
> 23101102 HERO LINER 20.00 10924.58
2005-01-07
> 23204101 Trading Mudgard 10.00 6000.00 2005-04-21
> 23204101 Trading Mudgard 10.00 50467.29 2004-01-07
> 23204101 Trading Mudgard 20.00 10924.58 2005-01-07
> 23204101 Trading Mudgard 20.00 10924.58 2005-02-07
> 23204101 Trading Mudgard 10.00 50467.29 2004-01-07
> 23204101 Trading Mudgard 20.00 10924.58 2005-01-07
> I WANT like this
> for every month and year
> 23204101 Trading Mudgard 20.00 100934.58 2004-01
> 23204101 Trading Mudgard 60.00 2326000.00 2005-01
> 23204101 Trading Mudgard 10.00 6000.00 2005-07
> thanx
>
> --
> waiting for solution
> from
> Sufian

How to Sum certain fields in a column

Hi all,
I am new to reporting services. What I am trying to do is display some of
our accounting data in a debit and credit fields. I am using this expression
to display the correct data in the fields:
=Iif(Fields!credit.value,0,Fields!Amount.Value), however at the bottom I want
to sum only the information that was displayed. Does anyone know how to best
achieve this?Hi there Cynthias,
good old days of access ;-)
AFAIK, -- > No there is no way to aggregate the report fields, you should do
this for best practice in your query, to minimize the rendering time, though
the Database Server (if you are using one) is in most cases the smartest
solution.
HTH, Jens Süßmeyer.
"cynthia" <cynthia@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BEA9F4E0-4BCC-40DA-AD06-13F7ECB2537A@.microsoft.com...
> Hi all,
> I am new to reporting services. What I am trying to do is display some of
> our accounting data in a debit and credit fields. I am using this
> expression
> to display the correct data in the fields:
> =Iif(Fields!credit.value,0,Fields!Amount.Value), however at the bottom I
> want
> to sum only the information that was displayed. Does anyone know how to
> best
> achieve this?|||There is more than one way to do it.
One way that I think is particularly clean is to do the following. In your
field list (where you drag a field onto your form) do a right mouse click,
add field. The field can be a field that is not coming from the database but
is an expression. Put in the expression you have below and call it whatever
you want. This field is now indistinguishable from a field that came from
the database. You can sum, group, whatever you want on it.
The second way is to put in this:
=sum(iif(Fields!credit.value,0,Fields!Amount.Value)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jens Süßmeyer" <Jens@.sqlserver2005.de> wrote in message
news:%23ThjqK6PFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi there Cynthias,
> good old days of access ;-)
> AFAIK, -- > No there is no way to aggregate the report fields, you should
do
> this for best practice in your query, to minimize the rendering time,
though
> the Database Server (if you are using one) is in most cases the smartest
> solution.
> HTH, Jens Süßmeyer.
>
> "cynthia" <cynthia@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:BEA9F4E0-4BCC-40DA-AD06-13F7ECB2537A@.microsoft.com...
> > Hi all,
> > I am new to reporting services. What I am trying to do is display some
of
> > our accounting data in a debit and credit fields. I am using this
> > expression
> > to display the correct data in the fields:
> > =Iif(Fields!credit.value,0,Fields!Amount.Value), however at the bottom I
> > want
> > to sum only the information that was displayed. Does anyone know how to
> > best
> > achieve this?
>