Problem with grouping not sum up correctly

[ThaoVy]

Hello,

I am using CR V 10. Below is my formulas:

LastUpdate: Maximum ({tblStatus.EnteredDate},{tblStatus.ProjectNum})

DaysLastUpdate: DateDiff ("D",{@LastUpdate} , CurrentDate)- (DateDiff ("WW",{@LastUpdate} , CurrentDate)*2 )

LastUpdate (%): If {@DaysLastUpdate} <= 3 Then "100%" Else "90%"

100% : If {@LastUpdate (%)} = "100%" Then distinctcount ({tblProjects.ProjectNum},{tblProjects.ProjectNum})

90%: If {@LastUpdate (%)} = "90%" Then distinctcount ({tblProjects.ProjectNum},{tblProjects.ProjectNum})

100%Count: If {@LastUpdate (%)} = "100%" Then distinctcount ({tblProjects.ProjectNum},{@Active/InActive})

90%Count: If {@LastUpdate (%)} = "90%" Then distinctcount ({tblProjects.ProjectNum},{@Active/InActive})


I grouped the report by Project# and by Project Status (Active or InActive)

Below is what showing on my report:

Project# 100% 90%
100AS 1
101DS 1
724GH 1
968AB 1

Active 4 0

340MQ 1
979EB 1

InActive 2


what shows under Project# Group is correct. However, the Problem is Under Project Status (Active or InActive) Group, it is showing that I have 4 projects at 100% and 0 at 90% when it should have shown 3 projects at 100% and 1 at 90%. what can I do to fix this? thanks so much for your help.

 

[lbass]

The problem is with your conditional formulas. You are saying, if the value = 100% show me all distinccount for this group--NOT the distinctcount of those with the 100% value, and when you evaluate this formula, it will read the most recent value for Active/Inactive in the group (in this case "Active" and give you the full distinctcount for that and zero for the other. Your formulas for 100% and 90% should just read:

//{@100%}:
If {@LastUpdate (%)} = "100%" Then 1

//{@90%}:
If {@LastUpdate (%)} = "90%" Then 1


...since the distinctcount of a field for the field being grouped on is always 1. You should be inserting summaries (sum not count) on these at both the projectnum and the active/inactive groups to get the correct summaries.

-LB

 

[ThaoVy]

I tried to insert a summaries earlier. However, when I clicked Insert Summary, under "Choose the field to summarize", the formula_{@LastUpdate (%)} is not there. Neither DaysLastUpdate or LastUpdate. Thanks

 

[lbass]

Okay, I just noticed that you are using a maximum in that formula. You need to return the maximum to the report directly. Try creating a command like:

select max(tblStatus.`EnteredDate`) as maxdate, tblstatus.`projectnum`
From tblStatus
Group by tblstatus.`projectnum`

Link the command to your projectstatus table on both fields. If you don't have the option of choosing "enforce both" joins in CR 10, add the following to your record selection formula:

{command.maxdate} = {tblstatus.entereddate} and
{command.projectnum} = {tblstatus.projectnum}

Then you can use the entereddate (or the command date) directly in your formulas, without using a maximum.

-LB

 

[ThaoVy]

Hi,

Normally, it takes only a minute to run the report. After I insert a Command, it's been an hr and 45 minutes. however, the report only reads at 28%. any suggessions. Thanks so much.

 

[lbass]

I should have mentioned that using a command in this way means the linking will occur locally. If you created a command that included all necessary fields as well as the summary, the report would be much faster. I think you then have to set up the summary field as a subselect within the command, like:

select tblStatus.`field11, tblStatus.`field2`,
(select max(A.`EnteredDate`) from tblStatus A where
A.`projectnum`=tblstatus.`projectnum`) as maxdate
From tblStatus
Where //etc.

There might be a better way, but this is what I ordinarily do. Then you would use this new command as your entire datasource for the report.

-LB