Hierarchy within a column

building reports crystal reports
1

Have a sample US data in a form of consolidated SQL table like -

New York 40
Washington 21
USA 3210
Denver 13
San Francisco 29
…

There is no hierarchy defined.
In the above example, Is there a way to make USA as top group, followed by cities as child group?

Abhi719 :us: (BOB member since 2010-12-10)

2

I can think of one way.
Create a SQL query using Add Command.

Selct ‘USA’ country, column, numeric_fact from table where column<>‘USA’

Then create a group on static column country.
Hide the details section and see if drill down works when you drill on static field ‘USA’.

Thanks,
Raghvendra

Raghvendra Deshpande :india: (BOB member since 2008-05-26)

3

Thanks for your suggestion. The report is on SAP BW cube, so no option of freehand SQL here. I am trying to do the similar solution by creating an additional column in the cube

Abhi719 :us: (BOB member since 2010-12-10)