Replicating a cross tab in Crystal Reports 1

[mirogak]

Hi folks,

Using:
CR 11.5
Data Source is a data warehouse universe

I am trying to create a huge report part of which looks like a cross tab report.

I pull from the universe: PROD_ID, NET_UNITS, YEAR

When I pull all three fields into the the Details section I get this:
PROD_ID YEAR NET_UNITS
PROD X 2007 50
PROD X 2008 100
PROD X 2009 200

I am aiming for this output format

PROD_ID 2007 2008 2009
PROD_X 50 100 200

I read an faq article on this site (

http://www.tek-tips.com/faqs.cfm?fid=1219)

but that didn't work out. I tried running totals and other variables, I get the right values but I just can't make it to print out in one line like shown above. Any help?

Thanks guys,
Miro

 

[lbass]

Can't you insert a crosstab? Use Prod_ID as the row field, year as the column, and sum of net_units as the summary.

-LB

 

[mirogak]

No, I can't. What I showed up is only part of the report, and the entire report is not a crosstab. I would like to create the crosstab portion manually.

 

[lbass]

If it is not the entire report you are better off inserting a crosstab, since creating a manual crosstab involves using conditional formulas that are summarized at the group level, with details suppressed. You can always insert the crosstab in a subreport.

-LB

 

[mirogak]

Thanks LB but it is not a subreport I am after.
My report will contain 15 columns, 5 of which are going are going to behave like a crosstab (Year1,2,3,4,5). So the crosstab portion is an integrated portion. I need to create a manual crosstab.

MA

 

[lbass]

Okay then, what problems are you running into? You would group on Prod_ID, and then add formulas like this:

//{@2007}:
if {table.Year} = 2007 then {table.netunits}

Repeat for other years. Then insert summaries on these formulas at the group level and suppress the details.

-LB

 

[mirogak]

Thanks lbass. It worked like a charm. Earlier my problem was that I was grouping on the Year and it didn't work but grouping on the Product worked.

Cheers,
Miro