• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 808
  • Last Modified:

How can I pivot a table without using an aggregate function

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?
0
envirospatial
Asked:
envirospatial
  • 5
  • 4
  • 2
  • +1
1 Solution
 
MIKESoftware Solutions ConsultantCommented:
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
0
 
mlmccCommented:
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
0
 
envirospatialAuthor 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
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MIKESoftware Solutions ConsultantCommented:
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
0
 
MIKESoftware Solutions ConsultantCommented:

Minor correction in example:

select name, amount_engaged,YearField,'Engagements'
from engagements
UNION ALL
select name, amount_paid, year, 'Paid'
from Payments
0
 
mlmccCommented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
envirospatial,

" I am using Access and CR."

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

JeffCoachman
0
 
MIKESoftware Solutions ConsultantCommented:
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.....

?
0
 
envirospatialAuthor 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
0
 
MIKESoftware Solutions ConsultantCommented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
"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
0
 
envirospatialAuthor 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
0
 
envirospatialAuthor Commented:
Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now