July 23, 2008 06:57pm pdt

1. mlmcc 1,141,572
2. peter57r 200,108
3. bdreed35 160,091
4. frodoman 104,945
5. James0628 94,389
6. crgary_tx 73,196
7. wykabryan 67,844
8. CRXIuser... 57,319
9. analliah 52,698
10. emoreau 39,586
11. IdoMillet 36,542
12. dougvarga 20,560
13. ast2550 20,075
14. RickJ 19,280
15. JeffStur... 14,600

1. mlmcc 6,835,991
2. frodoman 3,127,884
3. bdreed35 1,537,542
4. peter57r 1,019,264
5. rhinok 695,834
6. GJParker 447,021
7. IdoMillet 420,398
8. CRXIuser... 408,692
9. EwaldL 329,724
10. vidru 310,397
11. crgary_tx 300,618
12. wykabryan 256,288
13. DRRYAN3 253,255
14. James0628 226,076
15. ebolek 210,829

02.14.2008 at 12:45PM PST, ID: 23164324

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

7.4

Subreport passed variable to Main but field shifts down 1 & duplicates data.

Zone: Crystal Reports Software

Tags: Crystal Reports, XI

I have a subreport that passes a variable back to my main report. The field passed is a formula. The results seem to be shifting down one leaving the first result a 0.00 when it should be 15. Along with this, it duplicates the prior line info when the Name field is blank. I have attached the report for review.

Attachments:

employee-job-cost4.pdf (23 KB) (File Type Details)

Employee Name by Job

Start your free trial to view this solution

Question Stats

Zone: Database

Question Asked By: kpwhitte

Solution Provided By: CRXIuser2005

Participating Experts: 2

Solution Grade: A

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
Automotive
New Net Users
New Users

Software
Digital Music
Gaming World
Home Security

Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Displays / Monitors
Handhelds / PDAs
Components
Peripherals
Laptops/Notebooks

Servers
Misc
Apple
Embedded Hardware
Networking Hardware

Storage
Desktops
New Users

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMware
Misc
Web Development

OS
CYGWIN
Voice Recognition
Virtualization
Message Queue
Quality Assurance
Security
Firewalls

MultiMedia Applications
Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals
Devices

Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
Web Computing
WebApplications
IDS
Vulnerabilities
Email Clients
File Sharing

Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Consulting
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMware

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel
Automation

Algorithms
Game
Signal Processing
Project Management
Open Source
Database

Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Web Services

Images
Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration

WebApplications
Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Web Computing

Broadband
Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Lounge
Business Travel
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles
Automotive

Community Support

Suggestions
New to EE
New Topics
CleanUp

Announcements
General
Feedback

Input
EE Bugs

02.14.2008 at 06:04PM PST, ID: 20898883

Rank: Wizard

CRXIuser2005:

What is the FORMULA contents that you are using for the VARIABLE..?

MikeV

02.15.2008 at 06:12AM PST, ID: 20901729

kpwhitte:

The variable for the SUB rpt is:

//@Amt
//Stores the Amt of the
//{@Rate} field
//in a number variable called 'PayRate'

WhilePrintingRecords;

Shared NumberVar PayRate:= {@Rate}

The variable for the MAIN rpt is:

//@AmtMain
//Returns the value that was stored
//in the shared number variable called
//PayRate in the subreport

WhilePrintingRecords;

Shared NumberVar PayRate;

PayRate

Thank you.

02.15.2008 at 07:57AM PST, ID: 20902875

Rank: Wizard

CRXIuser2005:

What is the content of {@Rate}...? Please post contents....

02.15.2008 at 08:03AM PST, ID: 20902932

kpwhitte:

{@Rate} is if {PR1_EmployeeMaster.PayMethod} = "S" then {PR1_EmployeeMaster.PayRate1} / {PR1_EmployeeMaster.AutopayHours} else {PR1_EmployeeMaster.PayRate1}

02.15.2008 at 08:08AM PST, ID: 20902998

Rank: Wizard

CRXIuser2005:

What is the LINKING FIELD for your report....???

02.15.2008 at 08:17AM PST, ID: 20903093

kpwhitte:

The main has a formula to create the employee department & the employee number (it all originated form a string field called 'comment'). Then I am LINKING the true field Employee Department & Employee Number in the Sub.

If I have no variable set, I am happy as a clam in mud. Once I pass the payrate over to the main, the tide comes in.

02.15.2008 at 08:24AM PST, ID: 20903177

Rank: Wizard

CRXIuser2005:

Ok....then....what is the contents of the formula that creates the employee dept and number...?

I think we are getting close.

02.15.2008 at 08:30AM PST, ID: 20903255

kpwhitte:

lol

emp dept is: {JC_H3_TransactionDetailHistory.TransactionComment} [2 to 3]
emp # is: {JC_H3_TransactionDetailHistory.TransactionComment} [4 to 10]

02.15.2008 at 08:38AM PST, ID: 20903344

Rank: Wizard

CRXIuser2005:

Are you sure ALL employees have something in this field...{PR1_EmployeeMaster.PayRate1}.. and the no nulls are present...?

Just curious...

02.15.2008 at 08:38AM PST, ID: 20903349

Rank: Wizard

CRXIuser2005:

What do you mean by this:

"...all originated form a string field called 'comment'..."

02.15.2008 at 08:39AM PST, ID: 20903373

Rank: Wizard

CRXIuser2005:

Also,...why are some employee names BLANK on your report...but have data to report..???

02.15.2008 at 08:54AM PST, ID: 20903529

kpwhitte:

1. Are you sure ALL employees have something in this field...{PR1_EmployeeMaster.PayRate1}.. and the no nulls are present...? I am sure. This is in the sub and ALL do have a payrate.

2. "...all originated form a string field called 'comment'..." The Main rpt shows the employee dept and # but it is a field called Transaction Comment - that is why I had to create that formula for Emp Dept and Emp # in the Main.

3. Also,...why are some employee names BLANK on your report...but have data to report..??? Problem #2 - This file I am pulling from is Job Cost Transaction History. It shows all transaction for a job regardless if there is an employee still active in the company (btw - when an employee is terminated, it is purged from the system at year end). *remember I had to create that formula for emp dept and empl #*

Saying that - I do not know how to suppress (yet) the ones that are blank. I do not need these to show on the report. Those records are technically NULL.

I hope I made sense.

02.15.2008 at 09:03AM PST, ID: 20903630

Rank: Wizard

CRXIuser2005:

Please explain your "reasoning" for having to use a SUBREPORT....?

02.15.2008 at 09:12AM PST, ID: 20903724

kpwhitte:

I can not link via formula field without the use of a subreport.

The report needs to show by job the amount of hours worked for an employee by job for a labor cost code. The report needs to show the employee number, name, payrate, amount by job and then hours.

This report needs to pull the employee's name and the payrate. The employee name and payrate is stored in PR1EmployeeMasterfile. The Main report pulls from JC_H3 Transaction History. It contains a comment field. In that comment field, there are several more 'subfields' (so to speak) lumped together - an identifier code, dept, emp#, check or directdeposit #. I have to create a formula that extracts the employee number. Hence, I can not link via formula field without the use of a subreport.

02.15.2008 at 09:21AM PST, ID: 20903820

Rank: Wizard

CRXIuser2005:

Try using a SQL Command to create a "virtual table" that you can LINK ON...in your MAIN report and forget the sub....(I personally hate dealing with subs..and don't use them,unless entirely necessary)

Your SQL Script can extract and create the EMPDEPT and EMP# and whatever else you need,...then...just enter the SQL SCRIPT at the DATASOURCE ADD COMMAND ....and a "virtual table" will ber created that you can simply LINK like any other table to your other Main report table(s).

I would recommend...dumping the SubReport Idea..and going this route...not only that...your report will be faster...

MikeV

02.15.2008 at 10:00AM PST, ID: 20904194

kpwhitte:

MikeV - don't tell me that! lol

02.15.2008 at 11:28AM PST, ID: 20905079

Rank: Wizard

CRXIuser2005:

Why do you say that...LOL...?

Do you need help writing it...? IF so,...just give me the TABLE.FIELDNAMES...and I'll write it for you...Do you know SQL..?

MikeV

Accepted Solution

02.15.2008 at 11:29AM PST, ID: 20905085

Rank: Wizard

CRXIuser2005:

It is VERY easy...to do...which is why I recommend it....

02.15.2008 at 11:46AM PST, ID: 20905237

Rank: Genius

mlmcc:

If you want to persist in the subreport route please so state. I tend to agree with MikeV that there are other ways t accomplixh what yu want and they may indeed be faster.

mlmcc

kpwhitte

02.21.2008 at 05:07AM PST, ID: 20947268

the table names are JC_H3_JobtransactionDetailHistory; PR1_EmployeeMaster

I do know sql, if you can give me the commands to create a virtual table please.

thanks

CRXIuser2005

02.21.2008 at 08:06AM PST, ID: 20949033

No "commands" needing in Crystal....what you need to do is go to where you assign your datasource IN CRYSTAL....near your database or datasource you should see the words ADD COMMAND. Double click there...and a window will open with a blank box for you to add your SQL. The main limitation here is that you really need to make sure your SQL works correctly before entering it...because there are no real functions here to parse the SQL or anything.....

Once you copy/paste your SQL in.and click OK...then Crystal will attempt to build the table...may take a few seconds......then you will see a TABLE source..that will look like any other table source...and you can then use the table or even link the table to other tables in your database......It looks and acts like a table...but in reality is a VIEW>...or...a DERIVED TABLE.....whatever you want to call it....

Hope it helps...let me know..

MikeV

kpwhitte

02.21.2008 at 08:10AM PST, ID: 20949065

can you give me the sql syntax? I do know sql but have never built anyting that works like you are describing. I will use Enterprise to debug the syntax, then move it to Crystal.

thanks for your help.

CRXIuser2005

02.21.2008 at 08:15AM PST, ID: 20949124

Just use a simple SQL script...that creates whatever table you want:

EXAMPLE:

select col1, col2, col3
from yourtable
order by col1

I mean this is the basic format....I'm not sure what you are asking...IF you give me the COLUMN and TABLE NAMES...then I can create the SQL Script for you...

THen just copy and paste the SQL into the ADD COMMAND window....and you're off........

20080236-EE-VQP-29 / EE_QW_2_20070628