Re: ACE report advice?

>From: kistler@an.hp.com (Jim Kistler) >Date: 18 Jun 1997 16:08:07 GMT >X-Informix-List-Id: <news.39369> > >Can anyone help with advice for an ACE report?.... other than to use I4GL! >Is there a way to inform the report user that nothing was found in a >particular "group"? > >e.g. when using and "order by" statement that includes a table column > named "category", I create "group" headers as in: > > before group of category begin > > if category = "one" then > print ....... > > if category = "two" then > print ....... > > etc.... > > end { before group of category } > >The report runs just fine, but if a particular category value does not >exist in the table the report is "silent". Can I determine this and tell >the report user? There are a couple of possibilities, at least. One is to revise the generating SQL so that it uses an outer join of the list of expected categories with the actual category data (probably saved in a temp table), and you then fix the pre-group printing to test whether you've got a real row or a null row. The advantage of this is that it can be made to work for any number of categories. Of course, since the report already has special per-category printing, this is not necessarily all that significant. SELECT category, ... FROM ... GROUP BY ... INTO TEMP CatData; SELECT DISTINCT category FROM SourceOfCategories INTO TEMP CatList; SELECT CatList.Category RefCat, CatData.* FROM CatList, OUTER CatData ORDER BY RefCat END FORMAT BEFORE GROUP OF category IF category IS NULL THEN PRINT "No data for category ", RefCat ELSE BEGIN PRINT ... END Another option is to have a set of variables, one per category, which are all set zero in the first page header block, individually set to 1 in the "if category" segments, and then checked for zero-ness in the 'on last row' block. FIRST PAGE HEADER LET cat_one = 0 LET cat_two = 0 ... BEFORE GROUP OF category BEGIN IF category = "one" THEN BEGIN PRINT ....... LET cat_one = 1 END IF category = "two" THEN BEGIN PRINT ....... LET cat_two = 1 END etc.... END { before group of category } ON LAST ROW IF cat_one = 0 THEN PRINT "Warning: no data for category 'one'" IF cat_two = 0 THEN PRINT "Warning: no data for category 'two'" ... You could also do the zeroing and checking in suitable BEFORE and AFTER GROUP OF blocks instead of in the F.P.H and O.L.R blocks. Note that you do not have to have a BEGIN/END pair around the code in a BEFORE GROUP OF block (or any other section of a report). It does no harm; it does no good either. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>