Master-detail report with single SQL query?

[Feko]

Hello,

I'm trying to achieve something that I don't even know if is possible.
In my Data Source, I have a SQL query that returns states and their cities, just like this:

SELECT STATE_NAME, CITY_NAME FROM VIEWCITIES
----------------------------------------------------------------
CALIFORNIA - San Francisco
CALIFORNIA - San Clemente
CALIFORNIA - Santa Monica
CALIFORNIA - Santa Maria
COLORADO - Colorado Springs
COLORADO - Denver
COLORADO - Fort Morgan

And I would like to create a master-detail report, being state name the master detail and city name as sub detail, but using only that query...

CALIFORNIA
- San Francisco
- San Clemente
- Santa Monica
- Santa Maria

COLORADO
- Colorado Springs
- Denver
- Fort Morgan

Is it possible? If so, how can I do it?
Thanks in advice!

[Jan]

Hello,

Yes it possible. You can use GroupHeaderBand. Place it before DataBand. Set expression of the GroupHeaderBand to following:

Code: Select all

{YourDataSource.STATE_NAME.Substring(0, YourDataSource.STATE_NAME.IndexOf(" -"))}

After then place on GroupHeaderBand text box component with same expression.

At DataBand place textbox with following expression:

Code: Select all

{YourDataSource.STATE_NAME.Substring(YourDataSource.STATE_NAME.IndexOf(" -"))}

That's all.

Thank you.

[Feko]

THAT EASY???
Damn... I feel really dumb right now...
Thanks Jan, it works!!

[Alex K.]

Hello,

Let us know if you need any additional help.

Thank you.