How can I pivot a table without using an aggregate function

envirospatial
envirospatial used Ask the Experts™
on
I have two similar tables - Engagements and Payments.   Engagements has: name, amount engaged, year fields - Payments has: name, amount paid, year.  I would like to have a report that is grouped by name, pivots the year values and shows both the amount engaged and amount paid ( and the difference) in each "cell".  I am using Access and CR.

Any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
MIKESoftware Solutions Consultant
Top Expert 2006

Commented:
Sounds to me like you need to create a MANUAL CROSSTAB in Crystal, ...which means manually creating your crosstab within the report sections of the main report.

You would have to use formulas to align the report to look like a crosstab/pivot.

I would recommend using a standard Crosstab.. but they are for summary only..and will not allow for the DIFFERENCE of the Engaged and Paid amounts. Which is why this needs to built manually.

You can simply SUPPRESS the reporting sections that you do not need so as to create the appears of a crosstab / pivot.

Hope it helps.

M
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Assuming you link the tables on the name and year fields you will get a recordset with

Name  Year   Engaged  Paid  

You can then create a formula like below and use it in the cross tab.
Engaged - Paid

mlmcc

Author

Commented:
Thanks for the comments CrXIUser2005 and mimcc.  but I am not sure how to proceed.
mimcc:
I tried to link tables as suggested.  The problem is that I have engagements for future years where I do not have payments.  The outcome is that when linked I only get the years that have payment and not all years.

CrXIUser2005:

I am not sure how to create the manual crosstab in Crystal. How would I get each Year as a column header.

Thx
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

MIKESoftware Solutions Consultant
Top Expert 2006

Commented:
Try a UNION ALL sql statement:

select name, amount_engaged,YearField,'Engagements'
from engagements
UNION ALL
select name, amount_paid, year, 'Paid'

Or something like this...you data will then be placed into a SINGLE DATA TABLE...you can then use formula VARIABLES to calculate the data anyway you choose, grouping on the name as you desire.

M
MIKESoftware Solutions Consultant
Top Expert 2006

Commented:

Minor correction in example:

select name, amount_engaged,YearField,'Engagements'
from engagements
UNION ALL
select name, amount_paid, year, 'Paid'
from Payments
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
You can LEFT OUTER JOIN the table

EngagedTable LEFT OUTER JOIN PaymentTable

With a left join you get all the records in the left table and the matching records from the right table

mlmcc
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
envirospatial,

" I am using Access and CR."

OK great, but can you be specific on what program you want a soultion in?

JeffCoachman
MIKESoftware Solutions Consultant
Top Expert 2006

Commented:
Just FYI...my recommendations are from the stand-point of using CRYSTAL as the main reporting tool....to query an ACCESS DB.

My syntax is SQL Server...but I don't think it changes all that much in Access.....

?

Author

Commented:
Thanks for the comments.  
Not yet had a chance to try the latest suggestions.  At first glance-  mimcc:  I have tried the left join .. when I look at the pivot, only the dates that are in both tables show up.   CRUserXI2005:  That seems like the approach needed, how the key is setting up the formula..  not sure yet, will try.

To  clarify, Yes Using Access as DB and CR for reporting.. Will post soon.
Thx
Software Solutions Consultant
Top Expert 2006
Commented:
Again you'll need to GROUP BY NAME and then build some VARIABLE FORMULAS that will calculate and accumulate the totals you need....then display them in the appropriate section, probably a GROUP FOOTER....

Hope it all works out.

M
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
"Yes Using Access as DB and CR for reporting"
OK, but again,...
Which platform do you want the soultion in:Access or Crystal.
Pick ONE please.

Or should I presuume that based on the other Experts posts, that you want a Crystal solution?
;-)

JeffCoachman

Author

Commented:
Thanks for the help.

I have set up the report as CRUserXI2005 has suggested.  After some trial and error with variable formulas, I can format as I wanted.

Cheers

boaq2000:  Sorry for not being clearer.

Thx

Author

Commented:
Thanks for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial