Basically do cross tabs and summaries fail if you use the select expert to exclude data like a program name or service description?

We have 63 programs that make up our non-profit. Some of my year end reports (like counting the number of consumers in the counties we serve) exclude our housing participants as they are counted in other ways.

My conundrum is my reports use cross-tabs or summaries to summarize the data and if I’m using the select expert to exclude a program or service area; for example:

{SERVICE_AREA.SERVICE_DSC} <> "Housing"

or

{V_CONSUMER_PROGRAM.PROGRAM_DSC} <> “MFIP – S”

my totals do not equal the total if the above lines were not there minus housing or MFIP – S.

Another way to put it is my count should be 2,616. My MFIP- S program had 268 people in it in 2011 so the total when I exclude that program should be 2,348 but both my cross tab and basic summary (insert summary) give me a total of 2,352. A difference of 264.

The same thing happens with the housing service_dsc.

Any suggestions you may have would be hugely appreciated.

Thanks for the tips DBlank,

We do use a Left Outer Join to a purge list on all of our reports.

Here is my total select expert statement:

{V_CONSUMER_PROGRAM.INSTANCE_START_DATE} <= {?Stop Date} and
(isNull({V_CONSUMER_PROGRAM.INSTANCE_STOP_DATE}) or  {V_CONSUMER_PROGRAM.INSTANCE_STOP_DATE} >= {?Start Date}) and
{@rise program} and
isNull({PURGE_LIST.CONSUMER_ID})

//the {@rise program} points to a formula that culls out a program that we needed to track for the first part of 2011, but doesn't count towards any year end numbers.

// the "isNull ({PURGE_LIST.CONSUMER_ID}) line is the only reference to that particular table.

I'll play with it based on your suggestions.

Thanks!

Super goofy I know, but the it's the way the business runs no matter what it does to our reports!

Update: I removed the purge list table completely and my totals changed to 2,688 vs 2,616, but when I try to exclude the one program again it changes by 264 instead of 268 like it should.

I was hoping we were on to something there with the outer join!

There are in {SERVICE_AREA.SERVICE_DSC} but not in {V_CONSUMER_PROGRAM.PROGRAM_DSC}

Thanks for the continued feedback DBlank,

My @rise program formula should be taking care of it already with the first part (in bold):
if (isNull({V_CONSUMER_PROGRAM.PROGRAM_AREA_DSC})) then
    True
else if ({V_CONSUMER_PROGRAM.PROGRAM_AREA_DSC} = "Non-Rise") then
    False
else
    True

This formula correctly subtracts the 35 records that are in a "Non-Rise" program and if I take this formula out of the select expert the totals change correctly.

I tried to create another formula written like this one for my program I'm trying to subtract, but perhaps I switched things around by mistake. I'll try swapping the fields out with Program_DSC and see what happens!

Thanks again, I really appreciate the help!