Crystal Report Problem 1

[AKPolarBear]

I have created a CR that works just fine when I run. But when the users try it, the data is all returned as zeros. Even on of the date function for year it drops the last number so it reads 200 instead of 2004. If I increase the number of digits to 5 from the string it reads from, it will display correctly, but I know that is wrong solution.

This sounds like a permission issue but I have tried to change it with no luck. The other reports all work fine even the one I did a modification to today.

I complied the report 3 times as two different users and no change. Only other anomaly was a problem last week when a spyware program got into our server and caused some grief. But this was not on any of the servers for SQL or Macola.

Any suggestions?

We are SQL based, CR is version 7 and Macola is 7.6.100a.

 

[kscheeler]

Go into your DSN configuration and make sure you DO NOT have the box checked for the line "Use regional settings when outputing currency, numbers, dates and times".



Kevin Scheeler

 

[AKPolarBear]

It was not checked.

 

[dgillz]

Please explain what you mean by "If I increase the number of digits to 5 from the string it reads from, it will display correctly".

What string? Aren't you dealing with a date? If you have a formula, please post it.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[AKPolarBear]

I think it is some sort of permission error.
Here is the formula I used:

tonumber((Mid(ToText({IMINVTRX_SQL.trx_dt}),1,4)))

and it produces the correct result when I run the report.
If I change the 4 to a 5, then the user sees the correct date and I see a 5 digit number i.e. 20041.

The data that is desired comes from a running total function which is returning all zero's instead of data.

Oddly, the rest of the report displays the correct information like item name and number. Other reports in the same directory work normally for the users.

In looking at the trx_dt table above, when I look at it I see numbers with no comma's. But when I look as a user, the number is broken up with comma's. That explains why changing to a 5 works.

How do the users see the same data differently?

I have checked all the report options to make sure they match.

 

[AKPolarBear]

I fixed it.

I went back though the options as a user and changed them to match what I saw from my login. I then saved it as a user and it now appears to work as desired.

Never had this happen before so I'm not sure if it will again.

Thanks for the help.

 

[dgillz]

In the future, try this:

Year(NumberToDate({IMINVTRX_SQL.trx_dt}))



Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[NJAnalyst]

The number format is a user and workstation specific setting in the File/Options/Fields/Number. The default is for the thousands separator to be active. When displaying the date as a number it will add the commas and throw off mid functions. The numbertodate function that dgillz listed is the best way to convert a Macola date correctly but this setting can also affect the way invoice numbers and order numbers are displayed. I have not found a way to make it the change automatic. I hope this helps.



Jonathan Nelson
Business Systems Analyst
Horizon Group, USA

 

[dgillz]

Please explain how the NumberToDate() function can affect how other numbers are displayed. I have never seen this.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[NJAnalyst]

It is not not the numbertodate() function that affects other numbers. It is the way that the Options are setup to handle a number. If you are using the numbertodate() function, the date (as a number in Macola) is converted to a date before Crystal applies it's default formatting. If you are using a mid(), left(), or right() function, then the default formatting in Crystal kicks in and will affect your character placement counts because Crystal adds in the thousands separator by default. I have seen that this can affect reports that have parameters setup for invoice numbers or order numbers by displaying the numbers with a thousands separator as well. I was trying to explain to AK why he seemed to be getting inconsistent data and pointed him toward the the options settings that are user and workstation specific. I hope I have clarified. Sorry if I clouded the water.

Jonathan Nelson
Business Systems Analyst
Horizon Group, USA

 

[dgillz]

Thanks Jonathan, I get it now.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com