Converting decimal numbers to Words

Hi there all the Gurus,
I am trying to mave a convert a decimal number ie. 1230.30 to words in my report and i am getting result with the 30/100 at the back of the words.
Is there any way i can get a result of :
'ONE THOUSAND TWO HUNDRED THIRTY AND THIRTY'Well, I guess you could split the number into two parts (at the decimal point) and convert each separately.|||UpperCase(TOWORDS(integer({INVOICE.AMOUNT}))) + Uppercase(TOWORDS(fraction({INVOICE.AMOUNT}))) + 'CENTS ONLY'

i have tried this formula but it keeps prompting error "Missing '('

Please advice|||I was thinking something more like:

local stringvar sNumber := cstr(12345.67);
local numbervar whole := truncate(12345.67);
local numbervar pos := instrrev(sNumber, '.');
local numbervar decimal := 0;

if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Dollars and ' + ToWords(decimal, 0) + ' Cents Only');|||Cool! Many thanks for the solution.|||I was thinking something more like:

local stringvar sNumber := cstr(12345.67);
local numbervar whole := truncate(12345.67);
local numbervar pos := instrrev(sNumber, '.');
local numbervar decimal := 0;

if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Dollars and ' + ToWords(decimal, 0) + ' Cents Only');

Hi, if I want to convert a Sum of Amount into English Words? How to do this?|||I was thinking something more like:

local stringvar sNumber := cstr(12345.67);
local numbervar whole := truncate(12345.67);
local numbervar pos := instrrev(sNumber, '.');
local numbervar decimal := 0;

if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Dollars and ' + ToWords(decimal, 0) + ' Cents Only');

Hi there,

If i am not wrong, you must create a formula or using the SUM maths function to get the total amount then put it replacing the (12345.67)

or we shall hear what's the Guru says :)|||Hi there,

If i am not wrong, you must create a formula or using the SUM maths function to get the total amount then put it replacing the (12345.67)

or we shall hear what's the Guru says :)

Yap, I have already tried in this way, which replace the 12345.67 into sum(@.amount), yet it doesn't work.

local stringvar sNumber := cstr(sum({@.Amount}));
local numbervar whole := truncate(sum({@.Amount}));
local numbervar pos := instrrev(sNumber, '.');

local numbervar decimal := 0;
if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Ringgit and ' + ToWords(decimal, 0) + ' Sen Sahaja');|||What error do you get? Presumably 'This field cannot be summarised' when you run the report as you can't sum a formula {@.amount}.
Try summing something that can be summed like the underlying database value, or maybe {@.amount} is already your summed value?sqlsql

Converting Datetime to Date

I need to convert a datetime, coming from a database table, to a date. How
do I do this within my report?
--
DonIs this just for display or do you really want to convert to date for further
manipulation as a date?
If it is only for display, use =format(Fields!MyDateField.Value,
"MMM-dd-yyyy") or something like it.
If it is for further manipulation as a date, use
=CDate(Fields!MyDateField.Value)
HTH
Charles Kangai, MCT, MCDBA
"Don" wrote:
> I need to convert a datetime, coming from a database table, to a date. How
> do I do this within my report?
> --
> Don|||This is just for display. I have a related problem. In this report I have
multiple datasets. With the multiple datasets If I drag a dataset field onto
the report designer and look at the default expression for the field it looks
like this:
=First(Fields!DateSigned.Value, "AppDetailDS")
The "AppDetailDS" being the dataset name.
I tryed modifying 2 different ways as follows:
=Format(Fields!DateSigned.Value,"AppDetailDS","mm-dd-yyyy")
=Format(Fields!DateSigned.Value,"mm-dd-yyyy","AppDetailDS")
I cant seem to get the syntax correct when modifying the expression. I get
errors when I try to preview.
Also, I have tried using immediate if's(iif) and cant get that to work. I
have no problem in a report with only one dataset.
Any thoughts.
"Charles Kangai" wrote:
> Is this just for display or do you really want to convert to date for further
> manipulation as a date?
> If it is only for display, use =format(Fields!MyDateField.Value,
> "MMM-dd-yyyy") or something like it.
> If it is for further manipulation as a date, use
> =CDate(Fields!MyDateField.Value)
> HTH
> Charles Kangai, MCT, MCDBA
>
> "Don" wrote:
> > I need to convert a datetime, coming from a database table, to a date. How
> > do I do this within my report?
> >
> > --
> > Don|||Remove the "AppDetailDS" from your Format formula below, then capitalize the
"mmm" to "MMM". You only need two parameters for the Format function.
You should just have
=Format(Fields!DateSigned.Value,"MM-dd-yyyy")
HTH
Charles Kangai, MCT, MCDBA
"Don" wrote:
> This is just for display. I have a related problem. In this report I have
> multiple datasets. With the multiple datasets If I drag a dataset field onto
> the report designer and look at the default expression for the field it looks
> like this:
> =First(Fields!DateSigned.Value, "AppDetailDS")
> The "AppDetailDS" being the dataset name.
> I tryed modifying 2 different ways as follows:
> =Format(Fields!DateSigned.Value,"AppDetailDS","mm-dd-yyyy")
> =Format(Fields!DateSigned.Value,"mm-dd-yyyy","AppDetailDS")
> I cant seem to get the syntax correct when modifying the expression. I get
> errors when I try to preview.
> Also, I have tried using immediate if's(iif) and cant get that to work. I
> have no problem in a report with only one dataset.
> Any thoughts.
> "Charles Kangai" wrote:
> > Is this just for display or do you really want to convert to date for further
> > manipulation as a date?
> >
> > If it is only for display, use =format(Fields!MyDateField.Value,
> > "MMM-dd-yyyy") or something like it.
> >
> > If it is for further manipulation as a date, use
> > =CDate(Fields!MyDateField.Value)
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> >
> >
> > "Don" wrote:
> >
> > > I need to convert a datetime, coming from a database table, to a date. How
> > > do I do this within my report?
> > >
> > > --
> > > Don|||If I remove the "AppDetailDS", When I preview, I get an error: "the value
expression for the textbox 'DateSigned' uses an aggregate expression without
a scope. A scope is required for all aggregates use outside of a data region
unless the report contains exactly one data set.
"Charles Kangai" wrote:
> Remove the "AppDetailDS" from your Format formula below, then capitalize the
> "mmm" to "MMM". You only need two parameters for the Format function.
> You should just have
> =Format(Fields!DateSigned.Value,"MM-dd-yyyy")
> HTH
> Charles Kangai, MCT, MCDBA
> "Don" wrote:
> > This is just for display. I have a related problem. In this report I have
> > multiple datasets. With the multiple datasets If I drag a dataset field onto
> > the report designer and look at the default expression for the field it looks
> > like this:
> > =First(Fields!DateSigned.Value, "AppDetailDS")
> > The "AppDetailDS" being the dataset name.
> >
> > I tryed modifying 2 different ways as follows:
> > =Format(Fields!DateSigned.Value,"AppDetailDS","mm-dd-yyyy")
> > =Format(Fields!DateSigned.Value,"mm-dd-yyyy","AppDetailDS")
> >
> > I cant seem to get the syntax correct when modifying the expression. I get
> > errors when I try to preview.
> >
> > Also, I have tried using immediate if's(iif) and cant get that to work. I
> > have no problem in a report with only one dataset.
> >
> > Any thoughts.
> >
> > "Charles Kangai" wrote:
> >
> > > Is this just for display or do you really want to convert to date for further
> > > manipulation as a date?
> > >
> > > If it is only for display, use =format(Fields!MyDateField.Value,
> > > "MMM-dd-yyyy") or something like it.
> > >
> > > If it is for further manipulation as a date, use
> > > =CDate(Fields!MyDateField.Value)
> > >
> > > HTH
> > >
> > > Charles Kangai, MCT, MCDBA
> > >
> > >
> > >
> > > "Don" wrote:
> > >
> > > > I need to convert a datetime, coming from a database table, to a date. How
> > > > do I do this within my report?
> > > >
> > > > --
> > > > Don|||Try to not put textboxes outside of data regions. My suggestion is that you
use containers such as list or table data region to put your textboxes in.
The Format function does not have a scope parameter, so it should work. The
First function you are using is an aggregate function, so it may need a scope
parameter.
But the first thing you need to do is to place a list or table data region
on your screen. Bind it to a dataset using the Properties dialog, then add
textboxes inside of it.
HTH
Charles Kangai, MCT, MCDBA
"Don" wrote:
> If I remove the "AppDetailDS", When I preview, I get an error: "the value
> expression for the textbox 'DateSigned' uses an aggregate expression without
> a scope. A scope is required for all aggregates use outside of a data region
> unless the report contains exactly one data set.
> "Charles Kangai" wrote:
> > Remove the "AppDetailDS" from your Format formula below, then capitalize the
> > "mmm" to "MMM". You only need two parameters for the Format function.
> > You should just have
> > =Format(Fields!DateSigned.Value,"MM-dd-yyyy")
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "Don" wrote:
> >
> > > This is just for display. I have a related problem. In this report I have
> > > multiple datasets. With the multiple datasets If I drag a dataset field onto
> > > the report designer and look at the default expression for the field it looks
> > > like this:
> > > =First(Fields!DateSigned.Value, "AppDetailDS")
> > > The "AppDetailDS" being the dataset name.
> > >
> > > I tryed modifying 2 different ways as follows:
> > > =Format(Fields!DateSigned.Value,"AppDetailDS","mm-dd-yyyy")
> > > =Format(Fields!DateSigned.Value,"mm-dd-yyyy","AppDetailDS")
> > >
> > > I cant seem to get the syntax correct when modifying the expression. I get
> > > errors when I try to preview.
> > >
> > > Also, I have tried using immediate if's(iif) and cant get that to work. I
> > > have no problem in a report with only one dataset.
> > >
> > > Any thoughts.
> > >
> > > "Charles Kangai" wrote:
> > >
> > > > Is this just for display or do you really want to convert to date for further
> > > > manipulation as a date?
> > > >
> > > > If it is only for display, use =format(Fields!MyDateField.Value,
> > > > "MMM-dd-yyyy") or something like it.
> > > >
> > > > If it is for further manipulation as a date, use
> > > > =CDate(Fields!MyDateField.Value)
> > > >
> > > > HTH
> > > >
> > > > Charles Kangai, MCT, MCDBA
> > > >
> > > >
> > > >
> > > > "Don" wrote:
> > > >
> > > > > I need to convert a datetime, coming from a database table, to a date. How
> > > > > do I do this within my report?
> > > > >
> > > > > --
> > > > > Don

converting date time field

Hi,

I'm trying to report on the time records are created , but not to include the date. So for a month long period, I want to know how many records have been created between 8am & 9am. I can group the records and display by hour, but as the database is a time date field, it displays for each date as well.

I think I probably need to create a formula that will strip out the date information, then I can group by hour and that will return what I need, but I have no idea how create such a formula...

Any ideas?

Thanks,

Matt.What is your Database?
Write a Stored Procedure having the query group by Time and use that sp to design the report

Converting Crystal Report formula to SSRS

Converting Crystal Report formula to SSRS

I've got a formula written in Crystal Reports that I'm trying to re-do in SSRS 2005. I've just been using Crystal Reports for so long, I've got a mental road-block today.

Here is the formula in Crystal Reports:
IF {V_VIEW.FIELD1} IN ["AAA", "BBB", "CCC", "DDD",
"EEE", "FFF"]
THEN ({V_VIEW.FIELD2}&"*")
ELSE ({V_VIEW.FIELD2})

(It concatonates an asterisk to the end of FIELD2 if FIELD1 contains on of the values in the list.)

In SSRS I'd like to cause an entire row to be bold if FIELD1 contains one of the values in the list.

So in SSRS I'm putting an expression into the FontWeight properties of the TableRow and trying for something (which doesn't work yet) like:
=iif (Fields!FIELD1.Value IN ("AAA", "BBB", "CCC", "DDD", "EEE", "FFF"), "BOLD", "NORMAL")

(SSRS doesn't like the "IN" in the above statement.)

Can anyone offer a suggestion on how to write this for SSRS?

Thanks!
-ErikR

UPDATE

2007-SEPT-17

Ok. I found a workable solution. Does anyone have a better suggestion than the following? The below works but it seems it could be done more simply... Any suggestions?

=iif (Fields!FIELD1.Value = "AAA","Bold",
iif(Fields!FIELD1.Value = "BBB","Bold",
iif(Fields!FIELD1.Value = "CCC","Bold",
iif(Fields!FIELD1.Value = "DDD","Bold",
iif(Fields!FIELD1.Value = "EEE","Bold",
iif(Fields!FIELD1.Value = "FFF","Bold",
iif(Fields!FIELD1.Value = "GGG","Bold",
iif(Fields!FIELD1.Value = "HHH","Bold",
iif(Fields!FIELD1.Value = "III","Bold",
iif(Fields!FIELD1.Value = "JJJ","Bold",
iif(Fields!FIELD1.Value = "KKK","Bold",
iif(Fields!FIELD1.Value = "LLL","Bold",
iif(Fields!FIELD1.Value = "MMM","Bold",
iif(Fields!FIELD1.Value = "NNN","Bold",
iif(Fields!FIELD1.Value = "OOO","Bold",
iif(Fields!FIELD1.Value = "PPP","Bold",
"Normal"))))))))))))))))

Take a look at the VB.NET Switch function for more concise syntax. Alternatively, you can code a custom function that parses the field value any way you want.

Converting Crystal Code

I'm evaluating Reporting Services and currently converting a Crystal Report
to this format. However I've ran in to a problem and it is to do with a
function written in Crystal syntax but I cannot seem to be able to convert
this logic Heres the functio
----
StringVar name = "";
If NOT IsNull({Query.FIELD1_Company}) Then
name := {Query.FIELD1_Company};
If NOT IsNull({Query.FIELD2_COMPANY}) Then
name := {Query.FIELD2_COMPANY};
If NOT IsNull({Query.FIELD3_COMPANY}) Then
name := {Query.FIELD3_COMPANY};
If NOT IsNull({Query.FIELD4_COMPANY}) Then
name := {Query.FIELD4_COMPANY};
If NOT IsNull({Query.FIELD5_COMPANY}) Then
name := {Query.FIELD5_COMPANY};
If NOT IsNull({Query.FIELD6_COMPANY}) Then
name := {Query.FIELD6_COMPANY};
If NOT IsNull({Query.FIELD7_COMPANY}) Then
name := {Query.FIELD7_COMPANY};
If NOT IsNull({Query.FIELD8_COMPANY}) Then
name := {Query.FIELD8_COMPANY};
name
----
For a start, 'Null' cannot be used, neither can System.DBNull etc only
Nothing.
StringVar is a variable declaration in Crystal. What this all does is
prevents empty fields being returned (in Column 1) and the report format is
something similar to this
Company, Number of..., Export To..., etc
SomeCompany, 1, 1, etc
NULL, 0, 3, etc
So the code prevents record number 2 from appearing in the report and
returns the company name from one of the other columns (as code above).
NOTE All columns actually return the Company name but we are simply doing a
DISTINCTCOUNT on all the other columns(Query.FIELD2_COMPANY etc), so Column 1
will always have a company name (if the code works, as in Crystal).
Any ideas on this? and thanks in advanceIs TSql It is
ISNULL(Filed1, ISNULL(Field2, ISNULL(Field3, ISNULL(Field4,
ISNULL(Field5,'')))))
What this does is if Field1 is null it moves to Field2. A lot less
complicated.
"slk55guy" wrote:
> I'm evaluating Reporting Services and currently converting a Crystal Report
> to this format. However I've ran in to a problem and it is to do with a
> function written in Crystal syntax but I cannot seem to be able to convert
> this logic Heres the function
> ----
> StringVar name = "";
> If NOT IsNull({Query.FIELD1_Company}) Then
> name := {Query.FIELD1_Company};
> If NOT IsNull({Query.FIELD2_COMPANY}) Then
> name := {Query.FIELD2_COMPANY};
> If NOT IsNull({Query.FIELD3_COMPANY}) Then
> name := {Query.FIELD3_COMPANY};
> If NOT IsNull({Query.FIELD4_COMPANY}) Then
> name := {Query.FIELD4_COMPANY};
> If NOT IsNull({Query.FIELD5_COMPANY}) Then
> name := {Query.FIELD5_COMPANY};
> If NOT IsNull({Query.FIELD6_COMPANY}) Then
> name := {Query.FIELD6_COMPANY};
> If NOT IsNull({Query.FIELD7_COMPANY}) Then
> name := {Query.FIELD7_COMPANY};
> If NOT IsNull({Query.FIELD8_COMPANY}) Then
> name := {Query.FIELD8_COMPANY};
> name;
> ----
> For a start, 'Null' cannot be used, neither can System.DBNull etc only
> Nothing.
> StringVar is a variable declaration in Crystal. What this all does is
> prevents empty fields being returned (in Column 1) and the report format is
> something similar to this
> Company, Number of..., Export To..., etc
> SomeCompany, 1, 1, etc
> NULL, 0, 3, etc
> So the code prevents record number 2 from appearing in the report and
> returns the company name from one of the other columns (as code above).
> NOTE All columns actually return the Company name but we are simply doing a
> DISTINCTCOUNT on all the other columns(Query.FIELD2_COMPANY etc), so Column 1
> will always have a company name (if the code works, as in Crystal).
> Any ideas on this? and thanks in advance
>

Converting Blob fields to Text on a Report

On SQL Reporting Services, Blobs (type = image) do not even get exposed
to the report. Note: I am using blobs since I need virtually
unlimited text. So, to get the data out of blobs, I normally use ADO
methods of getchunk and actualsize. I tried creating a class with the
logic that I needed. I could pass it SQL and it would return the value
of the blob as a text string. Then I created the following Custom Code
in the report:
Public Function BlobText(strColumn$, strTaskID$) As String
dim t as object
t = createobject("MyReportClass.Functions")
BlobText = t.blob2text("MySQLServer","select " & strColumn & _
" from SQLDatabase..task where ID = '" & strTaskID & "'")
End Function
When I preview the report it works perfectly. When I deploy the
report, the textbox that calls the method puts "#Error" on the report.
There does not seem to be any meaningful log to help.
Anyone have any guesses?
Thanks,
SteveHi Steve,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to know how to write
embedded code in the reporting services. If I have misunderstood your
concern, please feel free to point it out.
Based on my knowledge, you are recommanded to read the article below
Writing Custom Code in SQL Server Reporting Services
http://blogs.sqlxml.org/bryantlikes/articles/824.aspx
Embedded Code In Reporting Services
http://odetocode.com/Articles/130.aspx
If this still does not resolve your issue, would you please generate a
sample rdl file with your function based on AdventureWorks database and
send it to me? my direct email address is v-mingqc@.online.microsoft.com
(remember remove "online" before you click SEND as "online" is only
prepared for SPAM), you may send the file to me directly and I will keep
secure.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner 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.|||Lost Customer, Close: Not Resolved

Converting Access report to Reporting Services

Hi,

I am trying to convert an Access report where I use expressions to calculate the number of males/females for example.

I have used the following expression, but it always returns an incorrect figure:

=Sum(IIf(Fields!gender.Value ="Female", 1, 0))

When I check the query however it has 46 rows = to 'Female', but when I previes the report it always comes out as 24.

What's causing this?

Thanks

Hi,

From your description, it seems the expression doesn't return the right number of those 'Female' field, right?

What I can see is to use a TRIM function to remove all the leading and trailing while-spaces characters from the value in each filed. Sometimes theses leading and trailing white-spaces characters may cause the expression "IIf(Fields!gender.Value = "Female", 1, 0)" returns 0. You may use TRIM to have a try, see the following code:

=Sum(IIf(TRIM(Fields!gender.Value) = "Female", 1, 0))

Thanks.

|||

Thanks, that seems to have worked.
I wasn't aware of the leading and trailing white-spaces.

Converted report too far against left side of screen

I converted an RS2000 report to RS2005 and uploaded it to a new 2005 Report Server.

When I view the report through IE 6 at the Report Server URL, the report seems jammed against the left side of the screen. This didn't happen when the report was in RS2000. When it was still an RS2000 report, there appeared to be some space (perhaps 1/8 - 1/4 inch) of space between the left edge of the screen and the left margin of the report.

Using Visual Studio 2005, I've tried moving all the objects within the report a little to the right, but that messes up the way the report prints.

Is this a common problem? Is there a way to get the space to the left of the report back when the report is rendered?

Thanks for your help!

Nancy

Also...

The border that used to be around the body of the report no longer shows up. I've checked the properties for the body of the report, and it is formatted to Border Color = Black, Border Style = Solid, and Border Width = 2pt. But the border no longer appears when the report is rendered.

sqlsql

Converted report loses border around report body

We have a (fairly large) number of reports that were created in RS 2000. We are trying to convert them to RS 2005.

All of these reports have a border around the report body. When I view the converted report in Visual Studio 2005, the border around the report body can be seen, but, when the report is rendered on the 2005 report server, the border around the report body disappears.

I've tried adding lines around the edges of the report body, but sometimes the right side line doesn't show up when the report is rendered, either.

I've seen questions about this on a few other sites, but no one seems to have an answer or workaround.

How do I get the border around the body of the converted reports (or lines around the body of converted reports) to reappear without having to completely rewrite over 50 reports?!?!?!?

Hi, I have the same problem.
Do you have solve it?
Thanks|||No one has a solution yet.

convert word doc to pdf

I have to convert word doc to pdf file use asp net.

I think maybe it possible make report in the report services, and then save them in pdf format.

but I dont know if I can make report from doc file.
and also if I can get the url of the file by query string.

Is it possible?

thanks!try again......|||

Do you have static Word documents that need 1-time conversion, or do you have Word documents that represent report styles or definitions that you want to export to PDF regularly with different data? In the first case, I'd say RS is not the right tool. In the second case, I think you'll get good results by designing the report definition from scratch using Report Designer, using the Word documents as prototypes.

convert week number to date in report designer

I have a report where I'm grouping based on week number, and I need to
translate the week number to the starting Monday of that week. This is the
format expression that I'm using
=Month(DateAdd(DateInterval.Day,((Fields!Week.Value-1)*7),DateValue("January
1, " & Year(Now()))))& "/" &
Day(DateAdd(DateInterval.Day,((Fields!Week.Value-1)*7),DateValue("January 1,
" & Year(Now()))))
It works most of the time; however, it gives me some weird results when a
month or day ends with a zero. For example, October 8 is coming back 141/8
Any suggestions on the format expression I should use to convert the week
number to a date value?I solved my problem. I was trying to go about it the hard way.
Originally I had my query set up to return week(new_date) for the week
number. I added to my query to also select new_date for the date value.
Then in report designer, I kept my group for the columns of my matrix as
week number, but for the expression for the group header, I chose to display
the date value.
"Joel Lindstrom" <joel.lindstrom@.CEI.COM> wrote in message
news:A41C4802-4422-4956-A4E1-761E5B1A395E@.microsoft.com...
>I have a report where I'm grouping based on week number, and I need to
>translate the week number to the starting Monday of that week. This is the
>format expression that I'm using
> =Month(DateAdd(DateInterval.Day,((Fields!Week.Value-1)*7),DateValue("January
> 1, " & Year(Now()))))& "/" &
> Day(DateAdd(DateInterval.Day,((Fields!Week.Value-1)*7),DateValue("January
> 1, " & Year(Now()))))
> It works most of the time; however, it gives me some weird results when a
> month or day ends with a zero. For example, October 8 is coming back
> 141/8
> Any suggestions on the format expression I should use to convert the week
> number to a date value?

convert value to 2 decimal places?

Hello, is there a way to convert the value to just 2 decimal places, I created the report in Reporting Services and it has quite a few digits to each value. I looked at the table and found that the data type is {Float}. Is there a way to convert the values to just 2 decimal places?..Thank You.

Try this...

Code Snippet

select cast(columnName as numeric(10,2)) as 'columnName' from tableName

|||That worked, Thank You...

Convert UTC time to local time

Hello,

I am new with the reporting services. I am creating a report and I need to display date/time on the report. But the servers stores those date/time in UTC. How can I convert them to the local time in my report.

Thanks for your help.

You need to use System.TimeZone.ToLocalTime(UTCTime).

See http://msdn2.microsoft.com/en-us/library/system.timezone.tolocaltime.aspx.

You should be able to use TimeZone.CurrentTimeZone if you want to convert using the server time zone.

|||

Hello,

i tried this tip with no luck.

I used the expression = System.TimeZone.ToLocalTime(!Fields.DateTime.Value) in one of my cells and got an BC30469 error.

|||

Try this instead:
=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!DateTime.Value)

-- Robert

|||

Robert,

thanks alot. Works like a charm.

|||

This works for conversion based on the time zone of the report server, but not the client. Is that correct? I tested this by using the function in a textbox on the report that I deployed to the report server. Then on my workstation PC, I changed my timezone and viewed the report. The time in the report still reflected the time on the report server (converted from the UTC time of course).

How do you change the dates in the reports dynamically based on the area of the country someone opens the report? Because the report renders as HTML first before being delivered to the client, does this mean it will always use the report server time zone?

Thanks

-Kory

|||I have the same question as KoryS. Anyone have an answer?

Thanks.

Convert UTC time to local time

Hello,

I am new with the reporting services. I am creating a report and I need to display date/time on the report. But the servers stores those date/time in UTC. How can I convert them to the local time in my report.

Thanks for your help.

You need to use System.TimeZone.ToLocalTime(UTCTime).

See http://msdn2.microsoft.com/en-us/library/system.timezone.tolocaltime.aspx.

You should be able to use TimeZone.CurrentTimeZone if you want to convert using the server time zone.

|||

Hello,

i tried this tip with no luck.

I used the expression = System.TimeZone.ToLocalTime(!Fields.DateTime.Value) in one of my cells and got an BC30469 error.

|||

Try this instead:
=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!DateTime.Value)

-- Robert

|||

Robert,

thanks alot. Works like a charm.

|||

This works for conversion based on the time zone of the report server, but not the client. Is that correct? I tested this by using the function in a textbox on the report that I deployed to the report server. Then on my workstation PC, I changed my timezone and viewed the report. The time in the report still reflected the time on the report server (converted from the UTC time of course).

How do you change the dates in the reports dynamically based on the area of the country someone opens the report? Because the report renders as HTML first before being delivered to the client, does this mean it will always use the report server time zone?

Thanks

-Kory

Convert String to Numeric without Decimal

Hi,

I am using Crystal Report 8.5 with Visual Basic 6.0 and MS Access database. Now situation is I have to display three different values for a particular condition. If data is there then number of records else zero and if even no depedend entry is there then "NA".

Variable is numeric and formula is

if {temp.var1} = 0 and {temp.var11} = 0 then 'NA'
else if {temp.var11} = 1 then '0'
else CStr ({temp.var1})

NOW problem is if I am using this report on a system having crystal report installed and set the numeric value as no decimal, it is working fine but when I try the same program on the system on which crystal is not installed it is showing decimal values upto 2 digits. I have used Report Expert Distribution also.

Any idea how to solve this problem. Thanks

VishalFormat that format field number as you want|||What does it mean. Could you please help me out by writing a example code for that.

thanks|||Roght click the field, select format section then goto number and select the required format

Convert string to numeric

I have a field which in the db is is char, and I want to sort in the report
numerically. Tried changing it in many areas but it still sorts as if alpha.
Is there an expression I can use on the report field to convert it to
numeric? Will cast or convert work in a field level experssion?Edit Field and make it like Cint(Fields!MyField.Value)
Then sort on this field.
- Suneet Mohan
"Chris Patten" wrote:
> I have a field which in the db is is char, and I want to sort in the report
> numerically. Tried changing it in many areas but it still sorts as if alpha.
> Is there an expression I can use on the report field to convert it to
> numeric? Will cast or convert work in a field level experssion?
>

convert seconds in timeformat(hh:mm:ss) in crosstab crystal report

hi,
I am displaying time in seconds in crosstab crystal report , I want to show it in time format(hh:mm:ss),Pls help me,it is very urgentI replied here
http://forums.belution.com/en/crystal/000/060/35.shtml

Convert RS2005 rdl to RS2000

Hi,

Is there any tool that can convert a report definition language file written for RS2005 to be compatible with RS2000?

Any help will be appreciated.

Thank you.

Peter

Microsoft does not provide a RDL-downgrade tool. Not sure if anyone else has built one and made it available to the public. Anyway, it would not be difficult to build a tool yourself, or just follow the manual steps described in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=414302&SiteID=1

The manual steps cover almost 100% of RDLs (I omitted custom report item and custom properties).

-- Robert

Convert rows to delimited string

Does RS have any function that would enable me to convert multiple
rows into a comma-delimited string? My report is very long due to multiple
instances of a child record that is represented by a single word. If I could
append
the column values it would significantly compress the report.
Example where the tables are Cust and Order and joined on Order.CustId:
Change from this:
CustId CustName OrderColors
-- -- --
100 Anderson Red
100 Anderson Green
100 Anderson Yellow
To this:
CustId CustName OrderColors
-- -- --
100 Anderson Red, Green, YellowRS doesn't have such a function. I would do this at the data source. If you
use SQL Server 2005, if I am not wrong, there is a custom aggregate sample
that does exactly this.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OOFXEMS9FHA.3492@.TK2MSFTNGP10.phx.gbl...
> Does RS have any function that would enable me to convert multiple
> rows into a comma-delimited string? My report is very long due to multiple
> instances of a child record that is represented by a single word. If I
> could append
> the column values it would significantly compress the report.
> Example where the tables are Cust and Order and joined on Order.CustId:
> Change from this:
> CustId CustName OrderColors
> -- -- --
> 100 Anderson Red
> 100 Anderson Green
> 100 Anderson Yellow
> To this:
> CustId CustName OrderColors
> -- -- --
> 100 Anderson Red, Green, Yellow
>
>

Convert returned values

I have a query where one of the fields returns a 0 or 1. I'd like the
zero's to show on the report as a "No" and the one's be a "Yes". Does
anyone know how to do this?
Thanks,
ColinIn Expression Editor
=IIF(Fields!ColumnName=1,"Yes","No")
--
THANKS & PLEASE RATE THE POSTING.
--RAVI--
"Colin" wrote:
> I have a query where one of the fields returns a 0 or 1. I'd like the
> zero's to show on the report as a "No" and the one's be a "Yes". Does
> anyone know how to do this?
> Thanks,
> Colin
>
>