Cross Tab Report Help

[wanzek1]

I have a report that uses a cross tab. I know that I can do this all with formuals and skip the cross tab part but the cross tab gives me everything I need. I just wish I could group it differently. This is the format of my cross tab:


............Regular Earnings......Overtime Earnings......Subsistance.......Taxes............Travel
............Hours Wages..........Hours Wages.............Hours Wages.....Hours Wages.....Hours Wages
John Doe
Jane Doe


So there are 2 headings. The very top row has the type of earnings/liabilities etc. and the second heading splits the first heading into Hours and Wages. What I want to be able to do is group the second heading together so that Regular hours and Overtime hours would be together.

Rows = Employee
Columns = Description (summarized horizontally)
Summarized = Actual Hours
Actual Wages

 

[CoSpringsGuy]

create a formula like this

//CTDescription
if {description} in ["Regular Earnings","Overtime Earnings"] then "Earnings" else {description}

Use that in the column of your crosstab instead of {description}

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[IanWaterman]

Creat a formula

If descriptionfield in [Regular Earnings, Overtime Earnings] then 'Earnings' else descriptionfield

Use this formula in Crosstab column instead of descriptionfield

Ian

 

[IanWaterman]

Too slow again ;-)

Ian

 

[wanzek1]

I don't see how this will work for me. I might not be following. This is the formula for my description:

if{brvJCCDDetlDesc.LiabilityType}= 1 then "Payroll Taxes" else
if{brvJCCDDetlDesc.LiabilityType}= 10 then "Fringe Benefits" else
if{brvJCCDDetlDesc.LiabilityType}= 11 then "Operator's Plan" else
if{brvJCCDDetlDesc.LiabilityType}= 12 then "Health Insurance" else
if{brvJCCDDetlDesc.LiabilityType}= 15 then "Deferred Comp Plan - Match" else
if{brvJCCDDetlDesc.LiabilityType}= 16 then "401(k) Match" else
if{brvJCCDDetlDesc.LiabilityType}= 20 then "Unemployment Insurance" else
if{brvJCCDDetlDesc.LiabilityType}= 30 then "Workers Comp Insurance" else
if{brvJCCDDetlDesc.LiabilityType}= 40 then "General Insurance" else
if{brvJCCDDetlDesc.LiabilityType}= 50 then "Indirect Cost Allocation" else
if {brvJCCDDetlDesc.EarnType} = 1 then "Regular Earnings" else
if {brvJCCDDetlDesc.EarnType} = 2 then "OT Earnings" else
if {brvJCCDDetlDesc.EarnType} = 4 then "Other (Non Bill. Cost Plus)" else
if {brvJCCDDetlDesc.EarnType} = 5 then "Subsistence" else
if {brvJCCDDetlDesc.EarnType} = 8 then "Travel" else
if {brvJCCDDetlDesc.EarnType} = 9 then "Taxable Subsistence" else
if {brvJCCDDetlDesc.EarnType} = 10 then "401(k) Employee Portion" else
if {brvJCCDDetlDesc.EarnType} = 11 then "Deferred Comp Plan" else
if {brvJCCDDetlDesc.EarnType} = 15 then "Health Insurance" else
if {brvJCCDDetlDesc.EarnType} = 16 then "Health Insurance Bank" else
if {brvJCCDDetlDesc.EarnType} = 17 then "AFLAC - Accident" else
if {brvJCCDDetlDesc.EarnType} = 18 then "AFLAC - Cancer" else
if {brvJCCDDetlDesc.EarnType} = 19 then "AFLAC - Hospital" else
if {brvJCCDDetlDesc.EarnType} = 20 then "Third Party Sick Pay" else
if {brvJCCDDetlDesc.EarnType} = 21 then "Deemed Income" else
if {brvJCCDDetlDesc.EarnType} = 25 then "Flex Benefits" else
if {brvJCCDDetlDesc.EarnType} = 30 then "Fringe Wash Account" else
if {brvJCCDDetlDesc.EarnType} = 35 then "Company Vehicle Mileage" else
if {brvJCCDDetlDesc.EarnType} = 36 then "VA Mileage Reimbursement" else
if {brvJCCDDetlDesc.EarnType} = 37 then "Operator's Plan" else
if {brvJCCDDetlDesc.EarnType} = 40 then "Taxable Wage Adj." else
if {brvJCCDDetlDesc.EarnType} = 41 then "Vehicle Allowance" else
if {brvJCCDDetlDesc.EarnType} = 45 then "Rig Welder Truck Allowance" else
if {brvJCCDDetlDesc.EarnType} = 46 then "Taxable Earnings" else
" "

What I want to do is under each of these headings there is Hours and Wages. I would like to have the hours grouped together and the wages. With a Total Hours column and a total wages column.

 

[CoSpringsGuy]

almost word for word Ian lol ... let see who gets this one first ..

try changing the following lines in your formula

if {brvJCCDDetlDesc.EarnType} = 1 then "Regular Earnings" else
if {brvJCCDDetlDesc.EarnType} = 2 then "OT Earnings" else
to
if {brvJCCDDetlDesc.EarnType} = 1 then "Reg/OT Earnings" else
if {brvJCCDDetlDesc.EarnType} = 2 then "Reg/OT Earnings" else

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[wanzek1]

I don't think I am explaining myself very well....

I don't want to change headings what I want is the information grouped differently.

Currently in the crosstab there are two rows of column headings:

Row 1 is the description
Row 2 is the under each description repeating hours and wages under each.

What I want to do is basically have the row 1 description to be with each hours and wages word and then be able to group anything that is an earning together and anything that is an hour together.

I don't know how to attached screenshots otherwise I would do that for you.

 

[IanWaterman]

What the fields /formulae for Wages and Hours

Ian

 

[wanzek1]

hours is the sum of actualhours
wages is teh sum of actualwages

Row is Employee
Column is description (formula above)
Summarized fields are actualhours and actual wages which are summarized horizontally with show labels checked. That is where the hours and wages heading comes in.

 

[IanWaterman]

Auto Cross tabs are not very flexible. Don't think its possible to do what you want. Looks like Manual Cross tab is your only option.

Or Create a command and union the wages and hours into same column

Select 'Wages' as Type, actualwages As Value, Description, employee
From yourtable
Where .. whatever filters
UNion all
Select 'Hours' as Type, actualhours As Value, Description, employee
From yourtable
Where .. whatever filters

YOu can use Parameters in commands to incorporate into filters

Then in Cross tab have two colum headers Description and Type underneath summariasing value in the employee

Ian

 

[wanzek1]

How do you do a manual crosstab?

Like just write formualas for each field you need and place them in the report or is there another way?

 

[IanWaterman]

Thats the way, long and tedious I am afraid. Also if you introduce another category then the report needs to ne updated. The more I think about it the Command solution becomes more attractive.

Ian