Create multiple columns from one field with out a CrossTab 1

[quinn23]

Hi
I'm currently using Crystal XIR2 against a Composite data source. I'm trying to create a report with a cross-tab look with out using a Cross-Tab.
The report has one group(Country). Within this group I have one field containing all the states(State). The third field is a date(date)
When I run the report this is how my data renders:

US
CT 1/1/2001
MA 2/2/2001
NY 1/4/2001
TX 1/5/2002

I would like to display the data in one single row.

US CT MA NY TX
1/1/2001 2/2/2001 1/4/2001 1/5/2001

I would keep the single group on Country and create individual text boxes for the State and place in the group header(State)

I have tried adding mulitiple detail sections and placing the date field on each of the detail sections with a suppress formula.
Suppress Formula(1st detail section): Table.State<>CT
Suppress Formula(2nd detail section): Table.State<>MA
Suppress Formula(3rd detail section): Table.State<>TX

Then I select the option to underlay the below sections in 1st detail and 2nd detail sections.
the dates do not line up in one row when viewed. I end of getting a staggered view.
Group US
Detail(a) 1/1/2001
Detail(a) 2/2/2001
Detail(a) 1/4/2001
Detail(a) 1/5/2002

I appreciate your help, you have helped me in the past.
Thanks

 

[IanWaterman]

Have you tried Formatiing with Multiple columns

In Deatails section expert, check the above box. A new tab appears called layout. Select that and check format groups with multiple column.

May do the trick.

Ian

 

[lbass]

I guess the question is why you wouldn't use a crosstab which would so much simpler, but to do a manual crosstab, you would group on country and then create a separate formula for each state:

//{@CAdate}:
if {table.state} = "CA" then {table.date}

Place the 50 formulas in the detail section and then insert a maximum on them at the country level. This assumes you only have one date per state. Then suppress the detail section and group header.

-LB

 

[quinn23]

Thanks Ian, unfortunately that did not work. I wasn't familar with that option, good to know for future reference

 

[lbass]

Did you notice my post?

-LB

 

[quinn23]

I agree LBass a Cross tab would be much easier. The problem is that the Cross tab needs to be drillable placed in either the report header or footer so that I get one single cross tab.

Your assumption is correct concerning the dates per states. There are multiple dates per state. I have used the suppress in the detail section to isolate the desired record so that we have one date per state. I've attempted to use variables to solve this however when I use a variable to capture the correct date it seems to ignore my suppress logic in the detail and shows me the wrong date. I'm placing my variable in the Group Footer (State)

 

[lbass]

Well, what IS the desired date, if there is only one, i.e., what criteria are you using?

Also, I don't follow why you can't use a crosstab. Do you mean you need to be able to drill into detail?

-LB

 

[quinn23]

That's correct I would need to drill into the detail.

 

[lbass]

You didn't answer my question.

-LB

 

[quinn23]

Since multiple dates are being shown I use a max count of the RunID. Each RunId has one date associated with the State.

In the detail section expert I suppress where the following is true
{table.RUN_ID}<>{#MaxRunID}

This allows me to show the correct date by state, just not sure why I can't see each expected date in it's own formula in one row

Thanks for help with this

 

[lbass]

Create a SQL expression {%statedt} like this:

(
select max(`run_ID`)
from table A
where A.`state` = table.`state`
)

Then use a record selection formula like this:

{table.date} = {%statedt}

This will return one record per state. You still need to use the 50 formulas I mentioned earlier and to follow the steps I mentioned earlier, since each row will necessarily fall on its own detail line.

-LB

 

[quinn23]

Thanks again LBass, unfortunately we are using a Composite Data Source. The SQL Expression builder is not available.

 

[lbass]

I don't really know what that is. Does that you mean you also cannot create a command?

-LB

 

[quinn23]

Lbass
I revised the Composite sql in the command script so that only one date per state is showing. I then followed your original advice to insert a max. This is working perfectly!
Thanks again