How to Format the Number for Total

Hai Friends,

I am using Crystal Report 9.2.
In my project, i am in need of finding the Subtotal, totals.

While doing so, its assigning the value as 2,234,456.00

i am in need of that display to be 22,34,456.00.

i tried for Formula Field & also Selection Formula's Group in Crystal Report.
In the Format Field, i am not able to customize for thousand.

Can any one help me to solve my problem for finding the total.Interesting question. I don't think you can do this easily. You might have to create a new formula field and use string manipulation.|||Hai Babu - Moderator,

Are u there? Why no reply from u?

Regards
Geetha|||hai Moderator,

What happened to u?
why u r not responding.
in crystal report as well as in access, we have this type of display for the totals.

rectify this.

Regards
geetha|||Convert the money to text and seperate the digits according to the format you want and combine them by ","

How to format numbers in a query

Hey - I have a quick question and know that it is probably pretty simple, but I am stumped. I have a query where I need to make a colum a number that looks like a percent with 2 significant digits:

i.e.,
SELECT tblNumericCovert.number1, tblNumericCovert.number2, [number1]/[number2] AS testDiv
FROM tblNumericCovert

where testDiv needs to spit out results like this ###.##

I am totally lost, if anyone can help, I would appreciate it.select to_char(number1/number2, '99.99') As testDiv from table;|||Originally posted by r123456
select to_char(number1/number2, '99.99') As testDiv from table;

When I do this, I get the error message that "to_char is not a vaild function name'|||What db are you using? to_char works for Oracle, but not SQL server.

Try:

select convert(money, (number1/number2)) As testDiv from tablesql

How to format in SQL

Hi All,

I have a serial number field in table. Field type is integer. It is just stored as 1,2,3,12,13, etc.

It is showing as 00001,00002,00003,00012,00013 in interface. C# string format is very easy to changed the format.

But when i export to excel there is a problem. Let me know how to format string in SQL and export to excel.

Thanks

Aung

Hi Aung,

You may want to try this

select right('00000'+cast(serialno as varchar(5)),5) from table

|||

select right('0000'+cast(serialno as varchar(5)),5) from table

should work.

Or you can use:

SELECTRIGHT('0000'+CONVERT(VARCHAR(5),serialno),5) FROM yourTable

|||

Thanks BRO...

This is what I want.

How to format a textbox?

I'm using RS 2003. Have a textbox with a number in it, but somehow the "Format" filed doesn't work or do anything?! I tried "#,#", (#,#), "#,##0", etc.

So, how do I set the format on an individual cell? Thanks.

YOu hjave to do that without quotes or use a named format like n2.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I did try that one too! Didn't work! I even used Standard Format and didn't work!|||Could it be that your .Value was recognized as text ? Text Values / references are automatically aligned to the left side, numeric ones to the right side.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||You were right. I had to use Val() function in order to use the Format. Thanks.sql

How to format a number field..

i have a number field but i can't seem to make a format out of it..
e.g.
when i have a value of 1, i want to make it appear in the report like 0000000001 or if i have 11, i want to make it appear like 0000000011
Hope someone could help!
thanksHello DarylAps,

You have two options :

1. While selecting records from table itself use replicate function (assuming you are using mssql server ) and return formatted number.
or
else

2. Create a formula as shown below : assuming your numeric field name is col :

ReplicateString('0', 10-Length ({SqlCommand.col} ) )+{SqlCommand.col}

Here are the details of ReplicateString functions of CR :

ReplicateString (str, #copies)
Basic and Crystal syntax.

Arguments
str is the text string to be replicated.
#copies is a whole number indicating the number of times str is to be replicated.

I hope you like this resolution.

Thanks
Dilemma

how to find which line has error

I am running a script which inserts large number of rows thru
INSERT INTO VALUES statement.
One of them is giving some error. While running it in Query Analyzer I am not able to know
which line is giving the problem. How do I make QA show me the offending line.
The script has lot of GO statements, usuall one after every 200 lines.
TIAData Cruncher wrote:
> I am running a script which inserts large number of rows thru
> INSERT INTO VALUES statement.
> One of them is giving some error. While running it in Query Analyzer
> I am not able to know which line is giving the problem. How do I make
> QA show me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA
Try running them as separate batches; one at a time until you find the
batch with the error.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If you put each insert into its own batch, on error, you can just double
click on the error message in the result pane which QA should bring you to
the line that fails.
--
-oj
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3g6ttvFb1038U1@.individual.net...
>I am running a script which inserts large number of rows thru INSERT INTO
>VALUES statement.
> One of them is giving some error. While running it in Query Analyzer I am
> not able to know which line is giving the problem. How do I make QA show
> me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA

how to find which line has error

I am running a script which inserts large number of rows thru
INSERT INTO VALUES statement.
One of them is giving some error. While running it in Query Analyzer I am not able to know
which line is giving the problem. How do I make QA show me the offending line.
The script has lot of GO statements, usuall one after every 200 lines.
TIA
Data Cruncher wrote:
> I am running a script which inserts large number of rows thru
> INSERT INTO VALUES statement.
> One of them is giving some error. While running it in Query Analyzer
> I am not able to know which line is giving the problem. How do I make
> QA show me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA
Try running them as separate batches; one at a time until you find the
batch with the error.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||If you put each insert into its own batch, on error, you can just double
click on the error message in the result pane which QA should bring you to
the line that fails.
-oj
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3g6ttvFb1038U1@.individual.net...
>I am running a script which inserts large number of rows thru INSERT INTO
>VALUES statement.
> One of them is giving some error. While running it in Query Analyzer I am
> not able to know which line is giving the problem. How do I make QA show
> me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA

how to find which line has error

I am running a script which inserts large number of rows thru
INSERT INTO VALUES statement.
One of them is giving some error. While running it in Query Analyzer I am no
t able to know
which line is giving the problem. How do I make QA show me the offending lin
e.
The script has lot of GO statements, usuall one after every 200 lines.
TIAData Cruncher wrote:
> I am running a script which inserts large number of rows thru
> INSERT INTO VALUES statement.
> One of them is giving some error. While running it in Query Analyzer
> I am not able to know which line is giving the problem. How do I make
> QA show me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA
Try running them as separate batches; one at a time until you find the
batch with the error.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If you put each insert into its own batch, on error, you can just double
click on the error message in the result pane which QA should bring you to
the line that fails.
-oj
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3g6ttvFb1038U1@.individual.net...
>I am running a script which inserts large number of rows thru INSERT INTO
>VALUES statement.
> One of them is giving some error. While running it in Query Analyzer I am
> not able to know which line is giving the problem. How do I make QA show
> me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIAsql

How to find the Row Number Transformation

I need help on how to find the Row Number Transformation,

I have followed this link http://www.sqlis.com/default.aspx?93

but I cannot find the Trash Destination. Could someone please tell me where I can download it?

Thanks

thanks|||

http://www.sqlis.com/default.aspx?56

|||You have posted the link to the Trash Destination page, just scroll down for the link to the download, Version 1.0.1.1.

How to find the Product of 4 Numbers in a Column?

Hi I have a table with this data

Description Number

-
Something1 2
Something2 3
Something3 4
Something4 6

I would like to find the product of 4 numbers (2*3*4*6) as output (144)

Please advice

Thanks

hi try this

SELECT *
INTO #Number
FROM (
SELECT 1 as Something,2 as Numbers UNION ALL
SELECT 1 as Something,3 as Numbers UNION ALL
SELECT 1 as Something,4 as Numbers UNION ALL
SELECT 1 as Something,6
) Number

DECLARE @.Product int

SET @.Product = 1

SELECT @.Product = @.Product * Numbers
FROM #Number

SELECT @.Product

DROP TABLE #Number|||

There was a request here to find a generic method of finding aggregate products of column entries:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1435635&SiteID=1

Umachandar posted a reference to a site here that has a generic solution that can be used:

http://www.umachandar.com/technical/SQL6x70Scripts/Main21.htm

|||

Nice answer

But I am expecting answer without using those values in the select statement

Some times I have only 3 rows sometimes I have 6 or 7 rows

But I want to find the product of those numbers in a Column

Please advice

Thanks

|||hi, those were only sample data to test if the query works. you can then filter the values from the other columns.

umandars' aggregate product were great! two thumbs up umandar..|||

Here's little way to do what you ask.

Create a string that is the formula. Evaluate the string:

Code Snippet

declare @.Formula varchar(8000)

select @.Formula = isnull(@.Formula + ' * ' + convert(varchar,[Number]),convert(varchar,[Number])) from <DataTable>

select @.Formula

exec ('select ' + @.Formula)

|||

Simply superb

I got my answer from your query

Thanks a lot

With Regards

How to find the number of rows in a table

I try to find the number of rows in a table with this commands:

CountRec =

New SqlParameter
CountRec.ParameterName ="@.countrec"
CountRec.SqlDbType = SqlDbType.Int
CountRec.Value = 0

MyCommand =New Data.SqlClient.SqlCommand()
MyCommand.CommandText ="select count(*) as @.countrec from Customer;"
MyCommand.CommandType = Data.CommandType.Text
MyCommand.Connection = MyConnection
MyCommand.Parameters.Add(CountRec)
MyCommand.Connection.Open()
MyReader = MyCommand.ExecuteReader

iRecordCount = CountRec.Value

This is the result:

Incorrect syntax near '@.countrec'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near '@.countrec'.

Source Error:

Line 39: Line 40: MyCommand.Connection.Open()Line 41: MyReader = MyCommand.ExecuteReaderLine 42: iRecordCount = CountRec.ValueLine 43:

Source File:E:\Develop\Web\ASPweb\AccessTimberSales\UserEntry.aspx.vb Line:41

What to do? I need a complete example to see how it works.

Thanks...

The cause of your error is that your SQL statement should look like this:
select @.countrec = count(*) from Customer;

But you will next run into the problem that your parameter was not declared as an output parameter so its value will always contain the 0 that you assigned it, and then the further problem that output parameter values are not accessible until a data reader is closed.

There are a few different ways to achieve what you are trying to accompish. Since you only need one value, you should not be using an ExecuteReader. Try an ExecuteNonQuery instead:

CountRec =New SqlParameter

CountRec.ParameterName ="@.countrec"
CountRec.Direction = ParameterDirection.Output
CountRec.SqlDbType = SqlDbType.Int
CountRec.Value = 0

MyCommand =New Data.SqlClient.SqlCommand()
MyCommand.CommandText ="select @.countrec = count(*) from Customer;"
MyCommand.CommandType = Data.CommandType.Text
MyCommand.Connection = MyConnection
MyCommand.Parameters.Add(CountRec)
MyCommand.Connection.Open()
MyCommand.ExecuteNonQuery()

iRecordCount = CountRec.Value

Alternately, you could forget about the parameter and do an ExecuteScalar:

MyCommand =New Data.SqlClient.SqlCommand()
MyCommand.CommandText ="select count(*) from Customer;"
MyCommand.CommandType = Data.CommandType.Text
MyCommand.Connection = MyConnection
MyCommand.Connection.Open()
iRecordCount =MyCommand.ExecuteScalar()

|||

Change your query to

SELECT COUNT(*) FROM Customer

Then use an execute Scalar from your command, don't bother with the parameter.

The way you originally wrote your query doesn't return anything to the data reader...

If you've got your heart set on using the parameter make sure you set the direction to Out , then alter your query to something like:

SET @.countrec = SELECT COUNT(*) FROM Customer

|||Thanks a lot, I can use this several places.

How to find SQL SERVER ?

How to find total number of SQL Server which is running in local
network m/cs, through C# or from one of SQL Server? - Thanks in adv.Hi

ISQL -L or SQL DMO.

http://groups.google.com.au/group/m...b87e4f264795ce0

Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"murali.trichy" <murali.trichy@.gmail.com> wrote in message
news:1124524045.477179.313090@.o13g2000cwo.googlegr oups.com...
> How to find total number of SQL Server which is running in local
> network m/cs, through C# or from one of SQL Server? - Thanks in adv.|||Hi

If you wanted a tool that does this then SQLRecon should fit!
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26

John

"murali.trichy" <murali.trichy@.gmail.com> wrote in message
news:1124524045.477179.313090@.o13g2000cwo.googlegr oups.com...
> How to find total number of SQL Server which is running in local
> network m/cs, through C# or from one of SQL Server? - Thanks in adv.

How to find out the total number of commited transactions on SQL2k?

Dear all,

Previously I posted this issue on SSIS newsgroups and I did not obtain any response so that I'll do here.

Using this system function (::fn_log(null,null)) you can find out how many transactions have been confirmed in your .LDF. Although by means of another system function you can see further information, such as statitical.

Any help would be very appreciated.

Thanks in advance and regards,

sp_monitor among them

How to find out number of SQL Server seats purchased?

How to find out number of SQL Server seats purchased?

The only way is to look at the invoice. Even if you license it per seat and register 50 seats it doesn't mean you didn't buy 100.

Jeff

|||

That sucks. What if I happen to go a new installation where they have no knowledge of this and they can't find the invoice.

How to find odd numbers?

Hi
What is the best/easiest way to find records where a certain number is odd?
I need to search for some records in one of our databases and one of the
criterias is that a number is odd.
Regards
SteenSELECT ...
FROM ...
WHERE number %1 = 1
--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%231LOEH$FFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Hi
> What is the best/easiest way to find records where a certain number is
> odd?
> I need to search for some records in one of our databases and one of the
> criterias is that a number is odd.
> Regards
> Steen
>|||On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:
>SELECT ...
>FROM ...
>WHERE number %1 = 1
Hi Roji,
I think you meant to write
WHERE number % 2 = 1
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yep, thanks Hugo..
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:d1ej11te1mgli39rt6hcg4e17tvm06qofr@.4ax.com...
> On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:
>>SELECT ...
>>FROM ...
>>WHERE number %1 = 1
> Hi Roji,
> I think you meant to write
> WHERE number % 2 = 1
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||...thanks guys...that was the function I was looking for. I just couldn't
find it in BOL.
Regards
Steen
Roji. P. Thomas wrote:
> Yep, thanks Hugo..
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:d1ej11te1mgli39rt6hcg4e17tvm06qofr@.4ax.com...
>> On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:
>> SELECT ...
>> FROM ...
>> WHERE number %1 = 1
>> Hi Roji,
>> I think you meant to write
>> WHERE number % 2 = 1
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)

How to find odd numbers?

Hi
What is the best/easiest way to find records where a certain number is odd?
I need to search for some records in one of our databases and one of the
criterias is that a number is odd.
Regards
Steen
SELECT ...
FROM ...
WHERE number %1 = 1
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%231LOEH$FFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Hi
> What is the best/easiest way to find records where a certain number is
> odd?
> I need to search for some records in one of our databases and one of the
> criterias is that a number is odd.
> Regards
> Steen
>
|||On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:

>SELECT ...
>FROM ...
>WHERE number %1 = 1
Hi Roji,
I think you meant to write
WHERE number % 2 = 1
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Yep, thanks Hugo..
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:d1ej11te1mgli39rt6hcg4e17tvm06qofr@.4ax.com...
> On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:
>
> Hi Roji,
> I think you meant to write
> WHERE number % 2 = 1
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||...thanks guys...that was the function I was looking for. I just couldn't
find it in BOL.
Regards
Steen
Roji. P. Thomas wrote:[vbcol=seagreen]
> Yep, thanks Hugo..
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:d1ej11te1mgli39rt6hcg4e17tvm06qofr@.4ax.com...

How to find odd numbers?

Hi
What is the best/easiest way to find records where a certain number is odd?
I need to search for some records in one of our databases and one of the
criterias is that a number is odd.
Regards
SteenSELECT ...
FROM ...
WHERE number %1 = 1
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%231LOEH$FFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Hi
> What is the best/easiest way to find records where a certain number is
> odd?
> I need to search for some records in one of our databases and one of the
> criterias is that a number is odd.
> Regards
> Steen
>|||On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:

>SELECT ...
>FROM ...
>WHERE number %1 = 1
Hi Roji,
I think you meant to write
WHERE number % 2 = 1
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yep, thanks Hugo..
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:d1ej11te1mgli39rt6hcg4e17tvm06qofr@.
4ax.com...
> On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:
>
> Hi Roji,
> I think you meant to write
> WHERE number % 2 = 1
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||...thanks guys...that was the function I was looking for. I just couldn't
find it in BOL.
Regards
Steen
Roji. P. Thomas wrote:[vbcol=seagreen]
> Yep, thanks Hugo..
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:d1ej11te1mgli39rt6hcg4e17tvm06qofr@.
4ax.com...

How to find last record on page?

How can i find number of last record on page?Do you want to find the value or count?|||i want to find count|||Use three formula

Formula1 @.Reset having the code
Numbervar count;
WhilePrintingRecords;
count:=0;

Formula2 @.count having the code
Numbervar count;
WhilePrintingRecords;
count:=count+1;

Formula3 @.Display having the code
Numbervar count;
WhilePrintingRecords;
count;

Place Formula1 at PageHeader and suppress
Place Formula2 at Details Section and suppress
Place Formula3 at PageFooter and dont suppress|||can i pass this max(count) parameter into Details Section? I want to suppress details if record is not last on page|||Not sure what you are asking. Do you want to show only the last record?|||i have detais a, details b sections in report and want to show details b section only for last record on page|||Goto section export
Select Details b
Nect to suppress option, there is button labelled x-2
clcik that button and write this code

not Nextisnull({FieldofDetailsB})|||not Nextisnull({FieldofDetailsB})

this code define last record in report, not in page

How to find a record by number

What would be the best way in SQL to query a table to return a single record by number?
Let's say I want record number 49 and my primary key is a text field so a numeric key is out of the question.I think I figured it out unless anyone has a better way.

SELECT TOP 1 * FROM (SELECT TOP 49 * FROM [Table] ORDER BY [Key] ASC) AS Whatever ORDER BY [Key] DESC|||That looks like a bad idea. If you pass any number bigger than the max number of records in the table, you always get the last record.

any ideas?|||which row did you want, the 49th? by which column?

and yes, if there are fewer than 49 rows, it would seem appropriate (at least, it seems so to me) to choose the last one

what database are you using? this forum is for non-specific (i.e. standard) sql

you may want to consider the Access or SQL Server forums, if TOP works in your database