Crosstab Group Sort Challenge 1

[zigstick]

Hello, I have a crosstab showing NameYear rows and Section columns; as shown in the below example:

LSS Company Overall - 2009 5.1 4.8
LSS Company Overall - 2010 5.1 4.8
LSS Company Overall - 2011 5.1 4.8
John Doe - 2009 5.1 4.8
John Doe - 2010 4.7 4.2
John Doe - 2011 5.4 5.9

The results are showing the client name and survey year with the resultant score for by section grouping.

I need this to always sort by Individual name_year first, followed by the Company name and year. The company name_year is static. I'm having no success. Naturally, if it happens that the individual respondent's name starts with anything before LSS, then ascending order is perfect, but when it doesn't I have to resort to specified order grouping ... which is fine but that I have to do this for approx. 800 reports. Any suggestions may save the last few hairs on my head. Thanks!

Oh yes, I'm using Crystal XI r2 and pulling from a sql database via odbc. While I can't alter the db, I tried pulling the data into excel to give me some flexibility, but not luck yet.

 

[lbass]

Please identify the fields or formulas you are using in the crosstab expert for the row fields. If you are using formulas, please show the contents. If you only have one row field that is returning both company and individual names, how do you distinguish companies from individuals--is there some field that does this?

-LB

 

[zigstick]

Thanks for the reply LBass. My crosstab has only one field for rows and one field for columns. The row field client_year, is concatenated at the database level to show individual client's names with the year:
ex:
Mike Davis - 2009
Mike Davis - 2010
Mike Davis - 2011

There are around 500 individual names, each with 3 years of data.
The Company name is only in the database one time ... once for each year. The crosstab needs to always show the client_year, in order for the client (if they resonded all 3 years, there would be 3 lines, 2 years two lines, etc, followed by the 3 years of the company overall, to compare the individual's yearly responses to the company overall response. Getting it to sort with client_year of the individuals first, followed by company overall is the problem.

 

[lbass]

You didn't answer my question. How do you distinguish company name from individual name (from a technical perspective). You make it sound as if company and individual populate the same field--if so there must be a way of distinguishing people from companies, e.g., a separate field, an employer ID that is null for individuals, etc.

-LB

 

[zigstick]

Sorry, I know it seems there should be something to distinguish between company and individual, but for this report, company and individual do coexist within the same field of the database. The db treats the company (which is our company) as an individual for comparative purposes. Apologies if I'm not explaining this clearly.

 

[lbass]

I am asking whether there is some other field that distinguishes a company from an individual. If there is only one company, then you could create a formula like this:

If {table.client-year} startswith "LSS Company" then 2 else 1

You can add this formula as your first row field, and then suppress the label, remove the grid lines and also suppress the subtotal in the customize style tab.

Another approach, which might be acceptable to you, is to create a formula like this:

if {table.client-year} startswith "LSS Company" then
{table.client-year} else
" "+{table.client-year}

This will force the company to be last, but there will be an indentation. You could try then selecting the label->right click->format field->display string->x+2 and enter:

trim(currentfieldvalue)

-LB

 

[zigstick]

Nice, option 2 using an indentation is brilliant. I can't go with option one because there is a chart built off the crosstab and if I insert another layer in the grouping, it mucks up the chart. I'm going to try option 2 now. THANKS!!!

 

[zigstick]

LBass ... I can't tell you what a help this is. This is going to save me many man hours. THANK YOU!!! Truly brilliant solution.