Is it possible to apply selection criteria on a per-field basis?

[narayanis]

I'm comparing aggregate data between a current period and a prior period. My stored proc does most of the work, so my results are like this (simplified for clarity):

showdescr - period - headcount
Gen Admission - 1 - 200
Gen Admission - 2 - 150

Can you point me to some resources that show the best approach to have Crystal display a single row per show, with columns for headcount of periods 1 and 2. For example, the end result above would be:
Gen Admission - 200 - 150

I could squish it into one row per show in SQL by creating a table that has Showdescr,Headcount1, Headcount2, Revenue1, Revenue2, etc, but I feel like Crystal has a more elegant way to handle this. Thanks for any advice!

 

[lbass]

You could insert a crosstab in the report header or report footer where you use the showdescr as the row field, the period as the column, and the headcount as the summary field.

-LB

 

[narayanis]

Thanks for the quick response! I pondered that. So if I have five pieces of data, I insert five crosstabs into the report with the relevant summary for each, then use suppression to hide the extra bits?

To complicate things, today I was given the additional requirement of a Variance column for each piece of data (headcount1 - headcount2 in my example). I apologize if this question is basic or I'm not asking correctly, my skill is in SQL and I haven't done much in Crystal beyond grouping and the occasional text concatenation formula. Is it possible to use select expert per field, so I can throw Headcount on there twice, with an If statement on each one that says to display for the relevant period?

If it's relevant, I should also mention that I have to use Crystal 8.5 for this.

 

[lbass]

Please explain whether the number of periods for the report would vary or are there always only two? What field holds the period, and what does it look like when placed on the report?

-LB

 

[narayanis]

There are always two periods (current and prior). It is an integer field containing a 1 or 2. My column headers will be 'Current' and 'Past', so the field value corresponding to period 1 is in column A and the field value for the same show corresponding to period 2 is in column B.

Since there are always two columns (even if one is zero-valued), I could code the data source to put it all on one row if it's too complex to handle elegantly in Crystal, and just let Crystal take on the task of grouping and making it look nice.

 

[lbass]

While this could be handled in an inserted crosstab, I think it would be simpler to create a manual crosstab, by inserting a group on the description field, and then adding a series of conditional formulas like this:

//{@Revenue1}:
if {table.period} = 1 then
{table.revenue}

//{@Revenue2}:
if {table.period} = 1 then
{table.revenue}

Place these in the detail section and right click on each and insert a sum at the group level. Then drag the groupname into the group footer and suppress the detail section. For the difference, create a formula like this:

sum({@Revenue1},{table.showdescr})-sum({@Revenue2},{table.showdescr})

Repeat for the other summaries (headcount, etc.).

-LB

 

[narayanis]

Thanks LB! Is it standard practice here to change a thread title to include [RESOLVED] at the beginning? I didn't see an option to edit my original post.

 

[lbass]

No, threads are closed after a certain amount of time, but there is no designation as resolved or not.

-LB