I already check the posts http://forums.informationbuild...251058331#4251058331 and http://forums.informationbuild...351009331#4351009331.
None of this answer my question.

Because I want to hide the column if all values are null or 0.

Thanks in advanced,
Carlos Dias

This message has been edited. Last edited by: Kerry,

You can do that by using a hold file. COnsider the next example on tha car file, where the field accel is checked to see if it is zero or not. By uncommenting one of the where's you can manipulate the outcome of the check, which will lead to either displaying the accel column or not.

TABLE FILE CAR
SUM   COMPUTE YESNO/I2 = IF ACCEL GT 0 THEN 1 ELSE 0;
WHERE DCOST GT 6000;
WHERE COUNTRY CONTAINS 'GERM';
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD FORMAT ALPHA
END
-RUN
-READ HOLD &YESNO.2.
-SET &ACCEL = IF &YESNO NE 0 THEN 'ACCEL' ELSE '';

TABLE FILE CAR
PRINT BODYTYPE
      SEATS
      DEALER_COST
      RETAIL_COST
      SALES
      LENGTH
      FUEL_CAP
      BHP
      RPM
      MPG
      &ACCEL

BY    COUNTRY
BY    CAR
BY    MODEL
WHERE DCOST GT 6000;
WHERE COUNTRY CONTAINS 'GERM';
END

Hope this helps ...

Thanks for the answer GamP,
But I can find a way to make it work.
The point is that I want to hide the column where all values are null (NODATA).
I already try to compare the compute value of the column with something like:
COMPUTE YESNO/I2 = IF COL GT 0 THEN 1 ELSE 0;

Thanks,
Carlos Dias

I have try several approaches but without success.

My report definition is:

  
TABLE FILE MYTABLE
SUM 
     AVE.COL1
     AVE.COL2
     AVE.COL3
BY  DATE AS 'Data'
ACROSS LOWEST SUBSERVICE 
WHERE ( YEAR EQ &YEAR.(FIND YEAR,YEAR IN MYTABLE).YEAR. )
WHERE SERVICE EQ 'A' OR 'B';
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML

Service "A" has sub-services A1 and A2. And service "B" has sub-services B1 and B2.

When sub-services are of type "A1" or "A2" only the columns COL1 and COL2 has values. When is of type "B1" or "B2" only the column COL3 has values.

In practice what I want is to hide/show columns depending on the type of the sub-services.

Thanks,
Carlos Dias

But if both records of type A and B are in the result, you'll always have three columns (2 for type A and one for type B). What you could do in this case is to create a defined field that holds one of the values for either type A or type B and use that in the across. Something like:

DEFINE FILE MYTABLE
REPCOL/D12.2 = IF SERVICE EQ 'A' THEN COL1 ELSE COL3;
END
TABLE FILE MYTABLE
SUM 
     AVE.REPCOL
     AVE.COL2
... etc

Would this be an option for you?

yOU COULD TRY THIS:

DEFINE FILE MYTABLE
COL/I1=IF SUBSERVICE EQ 'A1' OR 'B1' THEN 1
ELSE 2;
END
TABLE FILE MYTABLE
SUM
AVE.COL1
AVE.COL2
AVE.COL3
BY DATE AS 'Data'
BY SERVICE SUBHEAD
"Service ACROSS COL NOPRINT
WHERE ( YEAR EQ &YEAR.(FIND YEAR,YEAR IN MYTABLE).YEAR. )
WHERE SERVICE EQ 'A' OR 'B';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML

Or try this

Now you in fact have two request, firts for the A value and secodn for the B value.


TABLE FILE MYTABLE
SUM
AVE.COL1
AVE.COL2
AVE.COL3
BY DATE AS 'Data'
ACROSS LOWEST SUBSERVICE
WHERE ( YEAR EQ &YEAR.(FIND YEAR,YEAR IN MYTABLE).YEAR. )
WHERE SERVICE EQ 'A' ;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END

TABLE FILE MYTABLE
SUM
AVE.COL1
AVE.COL2
AVE.COL3
BY DATE AS 'Data'
ACROSS LOWEST SUBSERVICE
WHERE ( YEAR EQ &YEAR.(FIND YEAR,YEAR IN MYTABLE).YEAR. )
WHERE SERVICE EQ 'B' ;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END


And if you have more than only the A and B, you might go for a looping through all those values and make split reports for each.

Thanks for all answers.
But unfortunately none of then answer my question.

Because I want to show in just one report the values of service 'A' and 'B'.
Like I said before some column value exist in one and not the other.
Maybe I can use the last suggestion if I can, don't know how, join the two reports.

quote:

COMPUTE YESNO/I2 = IF COL GT 0 THEN 1 ELSE 0;

Carlos, so then your values can never be negative? Otherwise your compute will yield you a false positive,
ira

I already try this approach without success.

Here is an example of what a get:


For Sub Service "A1" and "A2" I want to hide the column "COL3" in green.
For Sub Service "B1" and "B2" I want to hide the column "COL1" and "COL2" in orange.

Thanks in advanced,
Carlos Dias

My problem is similar to the one post in http://forums.informationbuild...101077422#1101077422

Carlos Dias

I think the easiest way to achieve this is to convert the ACROSS values to columns then test as specified above.

Or upgrade to the 7702 release. That has a setting that will automatically hide the acrosscolumns that have nulls in every row of data. SET HIDENULLACRS=ON will do exactly what you need, but as I said, it is available as of 77.02.

GamP,
I tried this approach but only works if I have one column to sum by across. In may case I have 3 or 2.

Bu thanks anyway.

Carlos, Try the following code. This seems to give you an example of how to do what you would like to do. The second TABLE FILE is what you need. I included the first TABLE FILE to duplicate your formatting issue.

DEFINE FILE CAR
TWOSEATS/D12=IF (SEATS EQ 2) THEN RETAIL_COST ELSE 0;
FOURSEATS/D12=IF (SEATS EQ 4) THEN RETAIL_COST ELSE 0;
FIVESEATS/D12=IF (SEATS EQ 5) THEN RETAIL_COST ELSE 0;
COLNUM/I1 =DECODE SEATS(2,1,4,1,5,2 ELSE 3);
END

TABLE FILE CAR
SUM TWOSEATS FOURSEATS FIVESEATS
BY COUNTRY
BY CAR
ACROSS BODYTYPE
ACROSS SEATS
WHERE (BODYTYPE EQ 'CONVERTIBLE' OR 'SEDAN')
END

TABLE FILE CAR
SUM RETAIL_COST
BY COUNTRY
BY CAR
ACROSS BODYTYPE
ACROSS COLNUM NOPRINT
ACROSS SEATS
WHERE (BODYTYPE EQ 'CONVERTIBLE' OR 'SEDAN')
END

Carlos, You will also need to do this on a HOLD FILE that contains the AVE values already calculated. Do a TABLE FILE on your data using the AVE prefix operator on your fields and sort with just BY phrases, no ACROSS. HOLD the results and then add the DEFINE FILE section to your HOLD FILE and run the report off the HOLD FILE. That should do it.

Carlos,

Here is another example closer to what I was trying to say you need to do.

DEFINE FILE CAR
TWOSEATS/D12=IF (SEATS EQ 2) THEN RETAIL_COST ELSE 0;
FOURSEATS/D12=IF (SEATS EQ 4) THEN RETAIL_COST ELSE 0;
FIVESEATS/D12=IF (SEATS EQ 5) THEN RETAIL_COST ELSE 0;
END

TABLE FILE CAR
SUM AVE.TWOSEATS AVE.FOURSEATS AVE.FIVESEATS
BY COUNTRY
BY CAR
BY BODYTYPE
BY SEATS
WHERE (BODYTYPE EQ 'CONVERTIBLE' OR 'SEDAN')
ON TABLE HOLD AS HOLD001
END

DEFINE FILE HOLD001
COLNUM/I1 =DECODE SEATS(2,1,4,1,5,2 ELSE 3);
COLVAL/D12 =IF (COLNUM EQ 1) AND (SEATS EQ 2) THEN TWOSEATS ELSE
IF (COLNUM EQ 1) AND (SEATS EQ 4) THEN FOURSEATS ELSE
IF (COLNUM EQ 2) AND (SEATS EQ 5) THEN FIVESEATS ELSE 0;
END

TABLE FILE HOLD001
SUM COLVAL
BY COUNTRY
BY CAR
ACROSS BODYTYPE
ACROSS COLNUM NOPRINT
ACROSS SEATS
WHERE (BODYTYPE EQ 'CONVERTIBLE' OR 'SEDAN')
END