Solved

CR 10 Reports - Different Results On Different Computers

Posted on 2007-03-30
8
315 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
 

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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. …
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

747 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

13 Experts available now in Live!

Get 1:1 Help Now