Few questions about cross tab. 1

[haneen97]

Hi, CR 8.5
I have a cross tab report that looks like this:

--------------------April 05-----February 05-----March05-----January 05
“All the nulls” -----100 ------------400------------250-----------150
Category A-----------6---------------4--------------8--------------7
Category B-----------10-------------8---------------3--------------9
Total ---------------116-----------412-------------261-----------166

The months are generated based on a calculation of a table date.

I would like to:
1- Give title to “All the nulls”
2- Group the totals like Category A + Category B in a sub total.
3- Sort the months
4- Put a filter on the “All the nulls” to skip certain records.

Sorry for number of questions, this is my first cross tab in crystal.


Mo

 

[rosemaryl]

Dear Mo,

Q 1:
If you want to handle nulls then you would create a formula or sql expression upon which the row would be based. Here is an example for Crystal formula:

If isnull({Table.MyField})
then 'Nulls or Text To Print'
else {Table.MyField}

I like SQL expressions, but that's just me.

Q 2:
I would modify the above formula to first show nulls and Category A + Catgory B then group on another field to show the breakout of Category a and b.

Like this:

If isnull({Table.MyField})
then 'Nulls or Text To Print'
else if {Table.MyField} in ['Category A','Category B']
then 'Category A + B'

Q 3:

It seems your columns are based on a text field, use an actual date field or convert your text date to a date field: CDATE({Table.MyDateString}) they should then sort in order.

q 4:
More information required.

regards,

ro





Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[synapsevampire]

1- Give title to “All the nulls”

Use a formula instead of the field which states something like:

if {table.field} = "All the nulls" then
"your text"
else
{table.field}

2- Group the totals like Category A + Category B in a sub total.

I don't think that is what a cross-tab does, but the description is very loose. Prehaps you can change the previous formula to reflect:

if {table.field} = "All the nulls" then
"your text"
else
if {table.field} in ["Category A", "Category B"] then
"Subtotal of catgories A & B"
else
{table.field}

3- Sort the months

MOnths are always sorted, you need to state technical information in posts instead of descriptions. What is the current data type? If it isn't a date, state that it is being displayed as a date, but it is not, then post what is being passed to the report. They key is to convert it to a date.

4- Put a filter on the “All the nulls” to skip certain records.

Perhaps you sahould share what the filter is intended to do?

You can limit the rows returned by using the Report->Edit Selection Formula->Record and place something like:

not(
{table.field} = "All the nulls"
and
{table.otherfield} = "ignore nulls"
)

It's good that you posted the example output, but you didn't state wher any of this data was coming from, please post example data and expected output if you need additional help.

-k

 

[haneen97]

Thanks a lot guyes. This information helped me get closer to what I want. I am still working on some minor issues. I will post if I have a question.

Mo