×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Why do records not apear on cross-tab

Why do records not apear on cross-tab

Why do records not apear on cross-tab

(OP)
1)Account names are selected in the rows where the names are available on that table these names are selected in spesific order on the group option.

2)Activity for the month to date is selected on the summarised field in a different table.

These two tables are linked by account number and company and branch,on the first table only the name field is selected on table two the company ,branch, activity for the month, year and month fields are selected.Different links were tried left and right outer and = .
eg.

                  jan     feb      marc
salaries         50000   60000    50000
car allow          700     900      700
uif                 30      30       30
Total salaries   50730   60930    50730
 
On this Cross-tab workmans compansation were also selected in the spesific group order but because there were no activity up to date it do not appear,thou i need it in the report to show that it is null.

RE: Why do records not apear on cross-tab

(OP)
1)Account names are selected in the rows where the names are available on that table these names are selected in spesific order on the group option.

2)Activity for the month to date is selected on the summarised field in a different table.

These two tables are linked by account number and company and branch,on the first table only the name field is selected on table two the company ,branch, activity for the month, year and month fields are selected.Different links were tried left and right outer and = .
eg.

                  jan     feb      marc
salaries         50000   60000    50000
car allow          700     900      700
uif                 30      30       30
Total salaries   50730   60930    50730
 
On this Cross-tab workmans compansation were also selected in the spesific group order but because there were no activity up to date it do not appear,thou i need it in the report to show that it is null.

i need the cross tab like this.

                  jan     feb      marc
salaries         50000   60000    50000
car allow          700     900      700
uif                 30      30       30
workmans comp        0       0        0
Total salaries   50730   60930    50730

if still unclear leave a telephone number and i will call
thanks

willie

RE: Why do records not apear on cross-tab

a cross tab will only report groups that have data.   You can't force a group in a cross-tab if no records exist in the results for that group.

Ken Hamady
http://www.kenhamady.com/
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

RE: Why do records not apear on cross-tab

(OP)
the account name (workmans comp) do exist on the table of account names.

RE: Why do records not apear on cross-tab

But there are no records that qualify for the report criteria and make it into the report's results.  That is the 'results' I referred to.

Ken Hamady
http://www.kenhamady.com/
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

RE: Why do records not apear on cross-tab

(OP)
is there a way to show {account names} thou thy do not have movement on the other table

RE: Why do records not apear on cross-tab

Can be done, but it will be slow:

What is the current record selection formula?
What are the 2 table names?


Ken Hamady
http://www.kenhamady.com/
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

RE: Why do records not apear on cross-tab

(OP)

THE TWO TABLES:
1)GLB_BALANCES
2)GLM_CHARTOFACCOUNTS

SELECTIONS:
1.1)GLB_ACTIVITYMTD
1.2)GLB_MONTH
2.1)GLM_FULLNAME

PARAMETERS :
GLB_COMPANY
GLB_BRANCH
GLB_YEAR
GLB_MONTH


CROSS-TAB
LOOKS LIKE THIS
 (ROWS)              (COLUMS)
                    1.2)GLB_MONTHS
2.1)GLM_FULNAME     1.1)GLB_ACTIVITYMTD

RE: Why do records not apear on cross-tab

You didn't give me the record selection formula.   Paste the entire formula into your post.

Ken Hamady
http://www.kenhamady.com/
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

RE: Why do records not apear on cross-tab

(OP)
there is no formula involved only
 
the cros-tab selection
rows:             GLM_CHARTOFACCOUNTS.GLM_FULNAME
colums:           GLB_BALANCES.GLB_PERIODMM
summarised field: GLB_BALANCES.GLB_ACTIVITYMTD

and the select expert
:GLB_BALANCES.GLB_COMPANY is equal to XSOUTH AFICA
:GLB_BALANCES.GLB_BRANCH is equal to YGAUTENG
:GLB_BALANCES.GLB_PERIODYY is equal to 2001

and the links
:GLM_CHARTOFACCOUNTS.GLM_ACCOUNTTYPE  =
 GLB_BALANCES.GLM_ACCOUNTTYPE
  
:GLM_CHARTOFACCOUNTS.GLM_ACCOUNTNUMBERP1  =
 GLB_BALANCES.GLM_ACCOUNTNUMBERP1  

RE: Why do records not apear on cross-tab

Your select expert is your "record selection formula".  If you look at the menu just below the select expert the next option will open this as a formula.

You have three rules here that require a specific value in the balances table.  Since every record in COA will be partnered with balances, you can't have these rules and still get COA records without balance records.  The solution:

First, make sure the arrow in the link window goes from COA table -> Balance table.  Then, if this is a SQL table, make the join option to a Left Outer Join.

Create a formula field that says:

if GLB_BALANCES.GLB_COMPANY is equal to XSOUTH AFICA and
GLB_BALANCES.GLB_BRANCH is equal to YGAUTENG and
GLB_BALANCES.GLB_PERIODYY is equal to 2001
then GLB_BALANCES.GLB_ACTIVITYMTD
else 0

Remove all of the rules from the select expert and use this new field as your summarized field in the Xtab.   The report will return every record, but only those that meet your criteria will be in the Cross-tab.

Ken Hamady
http://www.kenhamady.com/
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

RE: Why do records not apear on cross-tab

(OP)
THANK YOU very much Ken Hamady !
one more how can i now select other branches and companys and diferent dates without creating more cros-tabs or reports?

Thanks again.

RE: Why do records not apear on cross-tab

One way is to create a parameter for each field, and use the parameter in place of the literal values in the "if-then" formula.  Each time you run it, it will ask you for your values.

This is easier than mofifying the formula field each time.

Ken Hamady
http://www.kenhamady.com/
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

RE: Why do records not apear on cross-tab

(OP)
Thanks Ken

How do I group my fields on the cros-tab selection ?
rows: GLM_CHARTOFACCOUNTS.GLM_FULNAME

RE: Why do records not apear on cross-tab

(OP)
I can only group (GLM_CHARTOFACCOUNTS.GLM_FULNAME)once in (CHANGE GROUP OPTIONS)where I would select (SPECIFIED ORDER) in which the (FULNAME) is available,but if I need to group a few (FULNAME) selection but not in alpha order,how do I do this?

RE: Why do records not apear on cross-tab

Sorry, I must be tired but I didn't understand the last two questions. Could you try again in shorter sentences?

Ken Hamady
http://www.kenhamady.com/
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

RE: Why do records not apear on cross-tab

(OP)
Hi Ken

The (GLM_CHARTOFACCOUNTS.GLM_FULNAME) at first do appear as I need them.
I still need to group GLM_CHARTOFACCOUNTS.GLM_FULNAME in spesific sequences with subtotals e.g.

                  jan     feb     march
salaries         50000   60000    50000
car allow          700     900      700
uif                 30      30       30
workmans comp        0       0        0
Total salaries   50730   60930    50730
temporary staff   3000    3000     3000
bonus payments     200     200      200
benifit fund        50      50       50
Total social secu 3250    3250     3250

I am able to group, but cannot select to put names in specific order because it stil run alphabetic.

at first I would

Group By:   
GLM_CHARTOFACCOUNTS.GLM_FULNAME
 
Sort Order:
in specified order

New named group....
Total salaries   (which is Text not .GLM_FULNAME )
 
Edit named group....
Group name : Total salaries
GLM_CHARTOFACCOUNTS.GLM_FULNAME / NEW
is one of:   salaries           
             car allow
             uif
             workmans comp

the result is that I do not get salaries car allow uif and workmans comp in the sequence that I need them it is alphabetic.



  

RE: Why do records not apear on cross-tab

You are putting them all into one category.

You need to create a separate named group for each category.  Then you define the accounts that are in each category.

What is the value in FULL NAME that goes with the category:
 SALARY
 Car Allow.

Ken Hamady
http://www.kenhamady.com/
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

RE: Why do records not apear on cross-tab

(OP)
There is no value in FULL NAME only the name is in this field.How do I create seperate groups if I can only select FULL NAME once for the group selection?

RE: Why do records not apear on cross-tab

What field do you look at to know if the record should be salary or not?  How do you determine which records should go into which groups?

Ken Hamady
http://www.kenhamady.com/
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

RE: Why do records not apear on cross-tab

7007: You CAN use FULL NAME twice in the grouping process. Select it the first time and choose your specified order. Then create a formula field (@cheat_the_order} with FULL NAME as the formula - this acts as an alias for FULL NAME and you can then select @cheat_the_order for the next level of grouping.

David C. Monks
david.monks@chase-international.com
http://www.chase-international.com
Accredited Crystal Decisions Enterprise Partner

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close