Suppressing blank rows in a summary table (1 Viewer)

Binworkin

New member
Local time
Today, 03:23
Joined
Jun 6, 2020
Messages
9
In the group and report footer I have a table of values in a structured tabular layout. There are 5 rows - sum of cost for Type 1, 2, 3, 4 and 5. I used sum(iif([type]=1,[Type1_Cost),0)) etc and I used this formula for in each of the 5 rows for each of the columns I want to sum. It formats and looks ok however for each report section the costs are typically of only one type and so I end up with 3 or 4 zero rows in each footer section. Simple illustration below.

I would prefer if possible to provide the summary totals only for the cost types that exist in that section. Total cost is always the first row. In the footer section example below I would like to see the total Cost, Type 2 and Type 4 rows only. What are my options please.

thanks



Budget CostActual CostProjected CostVariance
Total Cost
10​
16​
20​
10​
Type 1
Type 2
5​
12​
5​
0​
Type 3
Type 4
5​
4​
8​
3​
Type 5
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 01:23
Joined
Oct 29, 2018
Messages
14,142
Hi. Try reducing the heights and set the Can Grow property to Yes.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:23
Joined
Feb 19, 2002
Messages
31,109
Don't select the data. Change your crosstab query so that it doesn't select rows with no value. If Type1 has no value for A, B, C, D, or E, no row will appear. If it has a value for one or more Projects, then the row will appear.

The problem is probably being caused because a ZLS is somehow being substituted for the Cost field. We'd need to see the crosstab and any underlying queries to point out the actual cause.
 

Binworkin

New member
Local time
Today, 03:23
Joined
Jun 6, 2020
Messages
9
Thanks for the responses. I tried the row heights and can grow suggestion and since the values are $0 and not Null, and also have row names so I don't think it will work. In considering the second option using a cross tab, I realize I that my example data was not well represented. I have replaced the column headers with something better which I think rules out using cross tabs. I have tried some more queries & sub reports and there seems to be potential but alot of effort.
 

Micron

AWF VIP
Local time
Today, 04:23
Joined
Oct 20, 2018
Messages
3,471
Looks to me like your type field ought to be just that - with values that represent a type rather than a field for each type.
Is the data based on a cross tab query, or is it based on simple select query or table with 5 fields - one for each type?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:23
Joined
Oct 29, 2018
Messages
14,142
Thanks for the responses. I tried the row heights and can grow suggestion and since the values are $0 and not Null, and also have row names so I don't think it will work. In considering the second option using a cross tab, I realize I that my example data was not well represented. I have replaced the column headers with something better which I think rules out using cross tabs. I have tried some more queries & sub reports and there seems to be potential but alot of effort.
Hi. If you're willing to post a sample db with test data, we can take a look and give it a try for you.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:23
Joined
Feb 19, 2013
Messages
12,906
perhaps you need a subform in the group and report footer to provide the totals?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:23
Joined
Feb 19, 2002
Messages
31,109
Whether you are using crosstabs or not, use criteria in the query to select only the records you want or use criteria to exclude records you don't want.

Select ..
From ...
Where BudgetCost Is Not Null or ActualCost is Not Null or ProjectCost is Not Null;
 

Users who are viewing this thread

Top Bottom