Solved

CR 10 Reports - Different Results On Different Computers

Posted on 2007-03-30
8
331 Views
Last Modified: 2012-06-27
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
Comment
Question by:burris
  • 5
  • 2
8 Comments
 
LVL 17

Accepted Solution

by:
MIKE earned 250 total points
ID: 18827119
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 18827344
WHat are the differences?
Number of records?
Totals?

mlmcc
0
 

Author Comment

by:burris
ID: 18832611
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:burris
ID: 18832783
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
 

Author Comment

by:burris
ID: 18832889
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
 

Author Comment

by:burris
ID: 18832907
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
 

Author Comment

by:burris
ID: 18833013
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
 
LVL 17

Expert Comment

by:MIKE
ID: 18833804
Glad to be of assistance..
MikeV
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now