Hi,

I have data like

Country has states which further have cities. The Metrics to be displayed is say Population.
But Country/State/City should all come in a "same column" and behave in a tree structure. that is, on clicking country I should get states below it, on clicking a state I get cities below it. All this while corresponding Population for that country/state/city comes as second column.
COL1 COL2
+ US 1000
+ UK 500

on clicking US

-US 1000
+CA 200
+LA 800
+UK 500

on clicking CA

-US 1000
-CA 200
-city1 50
-city2 150
+LA 800
+UK 500

i should get rolled up data as i move from city2 to US, and drill down data on vice versa.

Acordian Reports i have tried but it is not the solution because i may have other sorting fields before i reach the Population col.
And also that accordian reports do not display the metrics/measures till we reach the last sort field.

So my reqmt is
1) getting all levels in 'same column'.
2) Rolled Up/Drilled Down to get displayed on click of +/-.

Regards,
CD

I don't know what your software version is, but this is exactly what an Accordian report in WebFOCUS does. It's available in 7.x. You can also perform similar functionality using FML in WebFOCUS, but it's not very interactive. Last, but not least, there is an additional product called Active Reports that produces HTML-based reports that are highly interactive for end-users, re-sorts, expand, add columns, etc. (of course availabe for an extra fee) We are actually thinking of moving in this direction.

Step 1 - Getting all the levels in the same column

-SET &ECHO=ALL;

SET MSG        = ON
SET ASNAMES    = ON
SET HOLDLIST   = PRINTONLY
SET HOLDFORMAT = ALPHA
-RUN

TABLE FILE GGSALES
SUM
DOLLARS/D12M
COMPUTE GEO/A20 = 'US';
COMPUTE SORT/A100 = 'US';
ON TABLE HOLD AS H001
END
-RUN

TABLE FILE GGSALES
SUM
DOLLARS/D12M
COMPUTE GEO/A20 = REGION;
COMPUTE SORT/A100 = 'US' | REGION;
BY REGION NOPRINT
ON TABLE HOLD AS H002
END
-RUN

TABLE FILE GGSALES
SUM
DOLLARS/D12M
COMPUTE GEO/A20 = ST;
COMPUTE SORT/A100 = 'US' | REGION | ST;
BY REGION NOPRINT
BY ST     NOPRINT
ON TABLE HOLD AS H003
END
-RUN

TABLE FILE GGSALES
SUM
DOLLARS/D12M
COMPUTE GEO/A20 = CITY;
COMPUTE SORT/A100 = 'US' | REGION | ST | CITY;
BY REGION NOPRINT
BY ST     NOPRINT
BY CITY   NOPRINT
ON TABLE HOLD AS H004
END
-RUN

TABLE FILE H001
PRINT DOLLARS
BY SORT NOPRINT
BY GEO
MORE
FILE H002
MORE
FILE H003
MORE
FILE H004
END

... or a really simple way -

TABLE FILE GGSALES
SUM DOLLARS
BY REGION
SUM DOLLARS
BY REGION
BY ST
SUM DOLLARS
BY REGION
BY ST
BY CITY
SUM DOLLARS
BY REGION
BY ST
BY CITY
BY STCD
SUM DOLLARS
BY REGION
BY ST
BY CITY
BY STCD
BY PRODUCT
ON TABLE SET EXPANDABLE ON
END
-RUN

T

These are really great examples! I'm gonna bookmark this post.

A note on the active reports, it only allows a limited amount of data.

I have WF 5.3.2 so no EXPANDABLE for me.

ohhhh
that IS a good example. thanks T.