Pending/Awarded/Rejected Report

Angela Bertrand 2

I've taken both RE Crystal Reports trainings... and now I'm trying to make my first Crystal Report! I'm working with our Grants Manager who has identified dozens of reports she 'needs' to do her job... we've decided on one per month, and the first is the Pending/Awarded/Rejected report. I've sketeched it all out, figured out which fields are needed, exported it... and currently I have lists of proposals with the amounts. I'm having trouble a) filtering columns and b) showing only totals. 


Here's a rough table of what I want:

  $ Pend $ Awarded $ Rejected $ Total $$$ $$$ $$$ Sum $$$ $$$ $$$ Sum $$$ $$$ $$$ Sum $$$ $$$ $$$ $$$

Solicitor

Ash

Alm

Ker

total

The $ Pend would be a total of the amount asked on the proposal records that have a status of LOI or Submitted; $ Awarded would be a total of the amount funded on proposal records with a status of Funded or Acknowledged. $ Rejected would be the total of the amount asked on proposal records with the status of rejected. All of this would be grouped by solicitor, with totals at the end for each of the statuses. 


I've gotten to a point where I can see each of the proposals each solicitor has, and the amount funded and amount asked for each... and I haven't been able to work out filtering or getting one line per solicitor instead of one line per proposal. I tried the select expert for the filtering... and it filtered all columns instead of just the one. 


Any tips moving forward? I'm really trying to learn instead of having other do it so I can build my skills moving forward

Alan French

I've created a very similar report - I found that the cross-tab functionality didn't work very well for this, because I wanted to count the same field in different columns depending upon the proposal status. What I ended up doing was suppressing the details section so that none of the individual proposals are displayed, and grouping the report based on solicitor. I then created several formulas that would be equal to the amount asked if the status was pending, for example, but would return 0 for any other status (repeat for each of the other statuses you want to summarise in the table), and then I created summaries based on those formulas and dropped the summary fields into the group header so that they were also split by solicitor. I've attached a screenshot of both the design mode and preview.

There's probably a less clunky way of doing it, as I'm a self-taught Crystal user, but it does the job.

Melissa S Graves

It takes some finesse to figure out how to do this but it is doable.  As Alan said, suppress details and only show the footers with summaries and you are likely close to being there. 


Do you have the level of support which gives you free crystal support. If you do, use them - they are a goldmine!

Angela Bertrand 2

Melissa Graves:

It takes some finesse to figure out how to do this but it is doable.  As Alan said, suppress details and only show the footers with summaries and you are likely close to being there. 


Do you have the level of support which gives you free crystal support. If you do, use them - they are a goldmine!

Melissa- which level of support gives you free crystal support?! We have learn everything so we should... and when I contacted support they said they don't offer it. How do I contact them because that would be amazing! Thank you!

Chester Bammel

Melissa,

 

What Alan suggested earlier sounds a bit
daunting, but it really isn’t that bad.  In a former life (not
RE), we had many reports that would list production requirements
for the next 8 weeks out, with each week in a separate column.

 

In the details area (This does not have to be
fancy and formatted in any way since it will be suppressed.) create
a separate formula for each status.  (if status = “Pending”
then value else 0)  These formulas will calculate the
value for that one detail record only.  Then create a summary
for each formula.  This summary will add the values for each
of the statuses.  These summaries can be both a Group
(Solicitor) summary and a grand total summary if you like. 
These are the ones that have to be all formatted and pretty.

 

It all sounds bad, but is really quite simple
once you have done your first one.

 

Chester

 

 

Chester Bammel

Business
Analyst

IT Enterprise
Services

Division of
Information Technology

Sam
Houston State University

Box
2397 Huntsville, TX  77341

Office - (936) 294-2351

 

 

Melissa S Graves

Learn subscriptions are different from support. Contact your account manager and talk to them. They can help you figure out which support level you have and if you already have it. If you do they are their own number in the phone tree in support so you rarely have to wait on the phone to get crystal support help. 

James Andrews

Any tips moving forward? I'm really trying to learn instead of having other do it so I can build my skills moving forward

I agree with Alan and Chester.  Crosstabs are pretty limiting -- they are good at doing what they do, but you almost always run up against some kind of little customization you need to account for, and you can't do it within a crosstab.


I don't use regular summaries very often in Crystal, though: you run into the problem of duplicates being counted more than once because of the way relational data comes out of RE.  Running Totals are much cleaner and more configurable.


 

Angela Bertrand 2

Thank you all so much!!!


I've gotten the report to a place where it is by-and-large working! It's shown me some clean-up I need to do in RE, and there are a couple first-time report quirks I need to play around with... but I'm really excited this is somewhat complete (other than formatting...)


Thank you, thank you!

Angela Bertrand 2

Account rep emailed - thank you! I'll keep my fingers crossed that it's included