Good Evening,

For the report I am building there are a series of metrics that each print a row of data. In this row there are 4 fields, each field being a count of some sort of the data that the metric is analyzing. So, for example, the report ends up looking like:

   
		A      B    C    D
Metric-1	100    4    5    27
Metric-2	200    4    8    1
Metric-3	300    42   5    7

My question lies in how to handle a case where there are no results so all 0's should be displayed. I'l explain what I'm facing:

For each of my metrics I perform a series of business logic rules which reduce the initial data set to a smaller subset of guids. The last step of each of these metrics performs calculations on this subset which produce the fields you see above. I have found that if the subset contains rows of data, but additional where clauses in that final calculation reduce the result set to nothing, 0's will appear properly. However, if that subset is already an empty set, the result is just an empty string, no aggregations occur, I don't get my 0's. This makes sense I guess but what is the best way to handle this so that I get 0's instead?

Do a check to see if the subset is empty and if so, write a bunch of zeros to my hold file? Or, instead of writing a hold like I'm doing ("ON TABLE HOLD AS 'foccache/metric1' FORMAT HTMTABLE"), is there a way to copy a pre-canned file in place of the one I was going to write?

Thanks for any thoughts.

This message has been edited. Last edited by: FP Mod Chuck,

Hi,
Did you try SET NODATA = 0

Is Missing set to ON for these fields? You should remove that and try.

The SET NODATA will not replace a missing data set but missing data from a data set. But it may resolve your issue depending on how your data is created.

You may have to take a look at the HOLDMISS option if some of your result set will already be empty.

As you stated, another option is to create a canned file but this file will need to be the exact same format as the "real" one.
Something such as this

-* CREATE A DUMMY FILE WHERE EACH DIMENSION (BY FIELDS) ARE EXTRACTED BUT WITH 0 IN MEASURE
TABLE FILE CAR
SUM COMPUTE SEATS /I3 = 0;
BY COUNTRY
BY CAR
ON TABLE HOLD AS DUMMYDATA
END
-RUN

-* EXTRACT REAL DATA SET WHICH IS IN THE SAME FORMAT AS THE DUMMY FILE
-* IN THIS CASE EXTRACT ONLY ENGLAND TO SHOW THE USAGE OF THE DUMMYDATA
TABLE FILE CAR
SUM SEATS
BY COUNTRY
BY CAR
WHERE COUNTRY EQ 'ENGLAND';
ON TABLE HOLD AS EXTDATA
END
-RUN

-* MERGE BOTH DUMMY AND REAL DATA TOGETHER
TABLE FILE DUMMYDATA
SUM SEATS
BY COUNTRY
BY CAR
MORE
FILE EXTDATA
END
-RUN

Unfortunately the NODATA didn't work. I started going down the other route but in hopes that I'm just missing something here is what I'm doing as the final operation in each metric. Maybe you guys might see where I'm going astray with the NODATA...

 
-SET NODATA=0
TABLE FILE EXAMPLE
SUM
     CNT.DST.ID1 AS ''
     COMPUTE APercent/F5.1p = CNT.DST.ID1 / &&ID1COUNT; AS ''
     CNT.DST.ID2 AS ''
     COMPUTE BPercent/F5.1p = CNT.DST.ID2 / &&ID2COUNT; AS ''
WHERE ( ID2 IN FILE TMP_RESULTS )
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS 'foccache/metric1' FORMAT HTMTABLE
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
TYPE=DATA,
     COLUMN=N4,
     BORDER-TOP=OFF,
     BORDER-BOTTOM=OFF,
$
TYPE=DATA,
     COLUMN=N3,
     BORDER-TOP=OFF,
     BORDER-BOTTOM=OFF,
     JAVASCRIPT=window.parent.displayDrillDownReport( \
     'IBFS:/WFC/Repository/...' \
     ),
$
TYPE=DATA,
     COLUMN=N2,
     BORDER-TOP=OFF,
     BORDER-BOTTOM=OFF,
$
TYPE=DATA,
     COLUMN=N1,
     BORDER-TOP=OFF,
     BORDER-BOTTOM=OFF,
     JAVASCRIPT=window.parent.displayDrillDownReport( \
     'IBFS:/WFC/Repository/...' \
     ),
$
TYPE=REPORT,
     COLUMN=N4,
     WRAP=0.500000,
$
TYPE=REPORT,
     COLUMN=N3,
     WRAP=0.500000,
$
TYPE=REPORT,
     COLUMN=N2,
     WRAP=0.500000,
$
TYPE=REPORT,
     COLUMN=N1,
     WRAP=0.500000,
$
ENDSTYLE
END

Just to follow up - so when I see my blanks it seems to be when TMP_RESULTS is completely empty. If it had data, and in that where clause I further reduced the available data to nothing, I would get the 0's I want.

Sean,
As I suggest I think that your EXAMPLE file should contain the data you want to be displayed in your report.
Meaning that you shouldn't apply any filters at that step.
In other words, perform data extraction, add "dummy" one, merge them and then your the merged data to produce your report

Sean,
On thing I Noticed is that you have used -SET NODATA instead of SET NODATA=0

Thanks Addy - I tried removing the dash but same results.

Martin - I'll go down your suggestion some more but should the NODATA=0 do the trick? If it should, what things should I be looking at to see why it's not working?

Sean,

Using the last technic that I gave you shouldn't have to us the SET NODATA = 0 since you are creating valid data bucket for all your possible dimension.

If it's not working it can be for others reasons such as a JOIN that doesn't return all possible values.

Pay attention that the SET NODATA = 0 it's only a "display" feature that will not (or may not depending on the WF & Excel version) allow you to perform formula in Excel.
See my other post [CASE-CLOSED] Applying SET NODATA = "something", result in blank in Excel formula bar

But without having your code it's difficult to give you the proper answer.

If I store a .htm file in baseapp is there a way to copy and rename it into foccache?

quote:

Originally posted by Sean Sweeney:
If I store a .htm file in baseapp is there a way to copy and rename it into foccache?

What are you trying to accomplish ?
What this htm file is for ?
What this htm file contains ?

You not giving enough information to have us giving a proper answer

Please clarify and detail your need
And it doesn't seems to be related with the original post question...

I have a prepared htm document in baseapp that is essentially a tr html element. I want to take a copy of this and place it into foccache with a custom name so that it can be included in my "-HTMLFORM" tags using the following: !IBI.FIL.foccache/temp1;

The point that I don't understand is if it's to include it in something else, why do you need to make a copy of the "template" file (in this case a htm file) with a different name ?

AFAIK you just need to reference it and you can do it as many time as you whish. Each session will have its own "copy" of the "template".

It's similar as if you have made a -INCLUDE in a fex

I have a series of the following:

!IBI.FIL.foccache/temp1
!IBI.FIL.foccache/temp2
!IBI.FIL.foccache/temp3

etc. in my HTMLFORM.

In one procedure I create foccache/temp1. Based on business logic in that procedure I may not want the result achieved through the report but rather what exists in the htm file. Is there any way to copy the content from that htm file into the foccache with a specific name so that the HTMLFORM includes it as "foccache/temp1".

AFAIK to "add" something in foccache it needs to be the result of a TABLE FILE... ON TABLE HOLD AS foccache/abc END

You cannot "copy" to that folder since it does "not really exist".

quote:

Based on business logic in that procedure I may not want the result achieved through the report but rather what exists in the htm file

Generates your report using the htm file content instead of the report result
If you have a business logic that tell you to use the report result or not, test this prior to generates the report and decide which path to take : regular report or default (htm) report

Is that make any sense ?

Yep, thanks for your help.