[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

CR 10 Reports - Different Results On Different Computers

I have a few reports that were developed in Crystal Reports 10, the data resides on a SQL Server 2005 database. The reports are hosted on an asp.net web site and are exported to pdf when the user requests them. My problem is, the numbers I am getting on the web server are different than the numbers I get when I run the exact same .rpt file on my workstation.  I have crystal reports installed on the web server so I opened it there and ran it, I got the same results as I do on the web page. I mapped a drive to the web server and opened the EXACT same file and ran it on my local machine and got the correct numbers. I have restarted IIS, then the whole web server, looked for any temporary files (with no success), I cannot figure out what is going on. Any ideas?
0
burris
Asked:
burris
  • 5
  • 2
2 Solutions
 
MIKESoftware Solutions ConsultantCommented:
Please POST the SQL Query from your Crystal Report here.....goto... DATABASE >> SHOW SQL QUERY....cut/paste here.


Also,...have you tried to run the SQL Querhy in MS SQL Query Analyzer? If so, how many rows are returned...is the data matching your SERVER or your workstation numbers.

lemeno
Mike V
0
 
mlmccCommented:
WHat are the differences?
Number of records?
Totals?

mlmcc
0
 
burrisAuthor Commented:
I get the same number of records at both locations. The difference is in the totals. These reports show our departments overtime usage in the different divisions. When I run the report on my machine I get the correct total, approx. 8 million fiscal YTD. When I run it on the webserver I get a total of approx. 18 million.  When I go through the report looking at the details the numbers are off for almost every division. These show the totals for the selected pay period and then also show the ytd values at the time of the pay period that was selected. The report for pay period 06-26 was fine but 07-01 through the most recent pay period are way off. I looked at reports for pay periods 05-26 and 06-01 and they were fine.
0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
burrisAuthor Commented:
ok, I've kind of figured out where my problem is. It is in my running total fields for my YTD values. I am using a formula for the "Evaluate" section. For some reason it does not seem to be working the same on the webserver as it works on my machine. The formula is:

if ToNumber(right({?PAYPERIOD},2)) >= 13

Then
    TONUMBER(LEFT({SUMMARY_MASTER_DIV;1.PERIOD},2)+RIGHT({SUMMARY_MASTER_DIV;1.PERIOD},2)) IN TONUMBER(LEFT({?PAYPERIOD},2)+'13') TO TONUMBER(LEFT({?PAYPERIOD},2)+right({?PAYPERIOD},2))

ELSE
    TONUMBER(LEFT({SUMMARY_MASTER_DIV;1.PERIOD},2)+RIGHT({SUMMARY_MASTER_DIV;1.PERIOD},2)) IN TONUMBER(TOTEXT(TONUMBER(LEFT({?PAYPERIOD},2))-1)+'13') TO TONUMBER(LEFT({?PAYPERIOD},2)+right({?PAYPERIOD},2))

in our database the payperiods are formatted 07.01 which is why I am parsing out the left and right characters to get rid of the ".". Our fiscal years run from pay periods 13  through 12 (for example 05.13 through 06.12), the first two difits represent the calendar year. I also realized that the reports were correct for last fiscal year because that is when the new system that these reports run against was developed, previous data was not imported.
0
 
burrisAuthor Commented:
I figured out my problem. It is when I convert from text to number to text again I am loosing the leading 0, so my pay period range is wrong and it is adding all of the previous fiscal year values to my totals. Thank you for your help.
0
 
burrisAuthor Commented:
I think I just needed to communicate with someone so I could get some ideas flowing. Unfortunately around my office I don't really have anyone to bounce ideas off of for Crystal Reports or SQL. Thanks again for the help.
0
 
burrisAuthor Commented:
Sorry, I just need to correct this in case anyone else ever refers to it. The problem was in my TOTEXT conversion, not the leading zero. I was getting decimal places when I was exporting the report to pdf through my asp.net application. I needed to specify the format for totext. When I put in the "##"  to this part of my formula
     TOTEXT(TONUMBER(LEFT({?PAYPERIOD},2))-1,"##")
 I got the desired results. Apparently the number formats are saved to the crystal reports program, not the report itself.
0
 
MIKESoftware Solutions ConsultantCommented:
Glad to be of assistance..
MikeV
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now