Why do records not apear on cross-tab 1

[7007]

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.

 

[dcm]

7007: What is the problem? Can you explain in a bit more detail? David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner

 

[7007]

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

 

[kenhamady]

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)

 

[7007]

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

 

[kenhamady]

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)

 

[7007]

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

 

[kenhamady]

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)

 

[7007]

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

 

[kenhamady]

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)

 

[7007]

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

 

[kenhamady]

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)

 

[7007]

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.

 

[kenhamady]

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)

 

[7007]

Thanks Ken

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

 

[7007]

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?

 

[kenhamady]

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)

 

[7007]

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.

 

[kenhamady]

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)

 

[7007]

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?