Solved

CR 10 Reports - Different Results On Different Computers

Posted on 2007-03-30
8
349 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

737 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