Show only last Status Entry 1

[Vi08]

Hello,

I am using CR10. In the report, I am trying to show something as follows:

Customer Name Project Num Business Name Last Status Entry
ABC 123BC Training Onsite Sent out quote to the customer
EDS 124DS Programming

For the Last Status Entry, I want to show only last entry status entered and the date if we have any. For Date, I am able to do it by a formula: Maximum ({tblStatus.EnteredDate},{tblProjects.ProjectNum})

However, for last entry status, I have no success.
If I do Maximum ({tblStatus.Status},{tblProjects.ProjectNum}) , I will receive an error that this field,{tblStatus.Status},can not be summarized.

Anything you can help to show only last status entry entered is greatly appreciated!

Vi

 

[lbass]

If by last, you mean the most recent, then go to report->selection formula->GROUP and enter:

{tblStatus.EnteredDate} = Maximum ({tblStatus.EnteredDate},{tblProjects.ProjectNum})

This would return the most recent record per Project. However, you say "if any"--not sure whether you mean the date might be missing or that the "LastStatusEntry" might be null. If the most recent entry has a null "laststatusentry", then are you looking for the last NON-null entry?

-LB

 

[Vi08]

I am so sorry for the confusion. What I am trying to get is a report to show a list of all pending projects. Within these pending projects, there are projects, which lots of STATUSES entered per project; however, I only want to show last STATUS entered and the date for the project. Something like

Customer Name Project Num Business Name Last Status Date Entered
ABC 123BC Training Onsite Sent out quote to the customer 02-03-2009


At the same time, in this report, there are also pending projects with NO STATUS Entered at all. I want to able to show these projects as well. The report should look something like this:

Customer Name Project Num Business Name Last Status Date Entered
ABC 123BC Training Onsite Sent out quote to the customer 02-03-2009
EDS 124DS Programming


There is NO Last Status for EDS because there is NO Status Entered for this project at all.

Thanks

 

[Vi08]

Hello,

It's me again.

This might help to understand my question.

in my tblProjects, I would have something like:

ProjectNum CustomerID
123BC 1
124DS 2

tblStatus:

Project Num Status Date
123BC assigned to a consultant 01-29-2009
123BC Contact requester for additional requirements 01-31-2009
123BC Sent out quote to the customer 02-03-2009

Since the last status entry for project # 123BC is 02-03-2009, on my report, I should have this project and Last Status Entry is "Sent out quote to the customer" and Date is "02-03-2009". For Project 124DS, there is no entry entered at all in tblStatus. Therefore, my pending projects report, would show this report as Pending and the columns Last Status and Date Entered would be null since there is nothing for this project in tblStatus.

By the way, how I determined if a project is pending is based on the data I have in tblProjects.

Thanks

 

[lbass]

Change the group selection formula to:

isnull({tblStatus.EnteredDate}) or
{tblStatus.EnteredDate} = Maximum ({tblStatus.EnteredDate},{tblProjects.ProjectNum})

This assumes you have a left join FROM the Projects table TO the Status table, and that you have no record selection criteria on the Status table.

-LB

 

[Vi08]

Hello,

I am able to show only the last status entered based on your suggestion below. However, I now have a different problem. In the report, I also want to show total hours entered per project. By doing the Group Select of isnull({tblStatus.EnteredDate}) or
{tblStatus.EnteredDate} = Maximum ({tblStatus.EnteredDate},{tblProjects.ProjectNum}), it only give me the 1st entry hours entered . Is there anything I can do so it can calculate all entries (hours entered) per Project? Thanks so much.

Vi

 

[Vi08]

just so you have more details about my problem report.

If I have a running total and put it to my detail section, I then will have all entries. however, if a project has 4 entries and I put it under details section, I will have it shows 4 times. If I group it by project, it will show only 1 time; however, the problem is that the running total will only calculate the 1st entries not all.

Thanks

Vi

 

[lbass]

Non-group selected records still contribute to inserted summaries, e.g., sum({table.hours},{table.project}) would show all hours for the project, including those not displayed. Running totals, however, will summarize only the group-selected records. I am unsure of whether you are saying that you have duplicate records or not; if you do, then inserted summaries (not rts) would still be incorrect. If you don't have row inflation, though, you can still use group selection and then use inserted summaries to include values that are not displayed.

-LB

 

[Vi08]

My detail section would show as follows:

Customer Name Proj# Business Name Hrs Enter
ABC 24841JH Programming 1.15
Programming 2.88
Programming 4.03
Training OnSite 2.00

Programming shows 3 times because Hrs were entered 3 times at different date for this business. Training onsite show only 1 time becasue hrs was enter 1 time.

If I put these fields under my grouping{tblProjectBusinessTypes.ProjectBusinessID}, I will have:

Customer Name Proj# Business Name Hrs Enter
ABC 24841JH Programming 1.15
Training OnSite 2.00

The problem is that Hrs Enter, which is my running total, only calculate the 1st entry.

What I am looking for a report to able to show something like this:
Customer Name Proj# Business Name Hrs Enter
ABC 24841JH Programming 4.03
Training OnSite 2.00

I can not do a summary, because it would give me wrong total due to multiple status entry for the same business name. Thanks so much.

Vi

 

[Vi08]

I am so sorry for confusions!

I actually got what I need.

For some reasons, same grouping if I have my running total under Group Header, it only calculate the 1st entry. However, under Group Footer, it gives me the correct running total. The bottom line is that I finnaly get what I am looking for. Thanks so much for your help.

Vi