Solved

CR 10 Reports - Different Results On Different Computers

Posted on 2007-03-30
8
339 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal Reports 11 Ignoring Parameters 3 90
Changing Image in report header based on parameter value 12 64
Crystal Reports 13 5 73
business objects to connect to MSSQL 6 87
Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

821 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