Alternate Way of creating a report...

[gyfu]

Hi. I am using CR 9 and am looking for an alternate way of displaying a part of my report. Let me explain the scenario.

I have 40 fields.. Price.TP1, Price.TP2, Price.TP3..Price.TP40. I would like to display a summary count and a summary amount for each of them.

Display will be such as.

Count Amount
TP1 12 2000.00
TP2 05 975.00
TP3 40 10572.95
and so forth till TP40..

I don't think I can use a crosstabs as the rows are multiple fields, I could be wrong. The only way I know is mannually adding each field and creating a formula field for each of them, which would mean that i need to create about 80 formula fields. I actually have 4 columns for each TP field, which brings it to 160 formula fields. Is there a way to use programming to do make Crystal display all with just a few fields. I hope this make sense. Thanks.

 

[lupins46]

I don't think you will find a simple answer to this.
You could create a set of arrays, 1 for each column you want to print. Then as you are reading the records insert the values into the same position in each array based on 'n' in the TPn value.
To print the results you would have build a formula that loops through the values in each array and concatenates them (with spaces/tabs/carriagereturns as necessary) to form a single output containing the whole table.

 

[lbass]

I think you could use a SQL command as your datasource where you use Union All statements, as in:

SELECT
Price.`TP1`, '1' as 'X'
FROM
Price
UNION ALL
(
SELECT
Price.`TP2`, '2' as 'X'
FROM
Price
UNION ALL
SELECT
Price.`TP3`, '3' as 'X'
FROM
Price
UNION ALL
SELECT
Price.`TP4`, '4' as 'X'
FROM
Price
)

//etc up to TP40

You could then group on 'X' and insert summaries (count and sum).

-LB

 

[gyfu]

Lbass,
Will this display as how I request? Cos I do not want to summarize the fields collectively but instead summarize them individually. Either way, I will try and do it and see how it works. If there are other advice, let me know, I really appreciate it, thanks.

 

[gyfu]

lupins46,
Thanks for the idea, but that is what I am expecting, just that I do not know which direction to take. to begin with, I will try lbass suggestion.

I tried, using arrays but somehow, Crystal gives me an error, saying that i cannot use an array on the fields. Don't really know where I go wrong on that, maybe i just cannot do that cos I don't see it in any of the help files, that actually uses array of database fields.

 

[lbass]

Using my suggestion, you would get summaries in the group footers for each TP value. You would then drag the group header into the footer and suppress the details and group header section (or drag the summaries into the group header, and suppress the details and group footer section). So yes, you would get the display you wished. You might test it out on three or four of the fields to begin with.

-LB

 

[gyfu]

lbass,
I am having trouble with the SQL add command. This is what I have, very similar to what you displayed.But I got a few errors.

1) Unexpected Syntax after table name. Expected 'where' or 'order by'

2) Select is the only operation supported.

The code is below. I am not very good with the SQL commands, could you please advise?

SELECT
POD.'sTier Pricing 1', '1' as 'X'
FROM POD
UNION ALL
(
SELECT
Pod.`sTier Pricing 2`, '2' as 'X'
FROM POD
UNION ALL
SELECT
POD.`sTie Pricing 3`, '3' as 'X'
FROM POD
)

 

[lbass]

I'm at a disadavantage because I don't have 9.0, but my understanding was that you could create an entire SQL query in the command area, so I think this should work. However, note that in your first select you should be using "`" around the field name, not "'". Also the punctuation can vary by datasource. Try looking in a completed report at "Show SQL Query" to see how tables and fields are punctuated. On another database I use, instead of "`" around field names, there are "", so that the phrase '1' as
'X' must be shown as '1' as "X".

The above SQL query worked when I tested it in 8.0 (although I had to use another field instead of '1' as 'X' in the first select clause, since in 8.0 you can't add "fake fields" in the first select). Maybe someone else can jump in with the appropriate syntax if you still can't get it to work after trying these suggestions.

-LB