How can I Change Group Options in a Cross Tab Reports Rows to original

[essa2000]

Dears;

I have a cross tab report and It is different fields other as a columns and it is one Dynamic Columns as well. Now, I am having problem with the ordering of records. Cross Tab reports changes the order of Records as these are coming from Stored Procedures. I don't want to change the order of records b/c I am calculating Running tolals in Stored Procedure and b/c of change of order of record the running total is messed up b/c the order of records should be in the same order as it is coming from SP. I checked Cross Tab Expert and there is one Button under the Rows List and when I see that option there are three options to Order or group those records 1. Ascending 2- Descending 3- Specified but there is no Original Order which I can find in different group options of a general report.

Now, I am in deep trouble b/c of it b/c all the reports are behaving wiered b/c of changing the order. I found a way to send the record from SP as in the same order which Cross Tab changes it. But, it is still not working.

How can I stop changing order on Cross Tab records.

Thanks.
Essa

Muhammad Essa Mughal
Software Engineer
iLogic Inc.

 

[kenhamady]

Unfortunately, Crystal doesn't support 'original order' in cross-tabs. (Possibly because Cross-tabs are 2 dimensional.)

I am not sure I understand why the order would change the results. Apparently even when you matched the order you don't get what you are expecting so the order may not be the problem. It may be that a Cross-tab won't summarize your SP running totals the way you expect.

You could always try a manual cross-tab, where the rows (which are the groups of the report) can be in original order and can show your running totals. The columns are hard coded.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[essa2000]

Hi Ken Hamady ;

First of all I am really thankful to you to give me detailed answers of my questions.

Secondly , I am also very disappointed by the limitation of cross tab report.

Now, let me explain the real problem in details.

I have one Quantity Column in Cross Tab report and I need to show running total of that column in very next column. Now, when I was trying to calculate running total in report using formula and I did it but when I tried to select that formula field as a row in Cross Tab Expert then it does not show that running total formula field in the list , it means Cross Tab does not support running totals as well. So, I had to calculate my running total in Stroed Procedure and which was an interesting task but I did it using loop thru the records. So, the order or records is very important b/c I inserted all the record in a temp table then I did a loop to calculate running totals and now b/c of running total the order should be same on reports as well other wise the running total columns does not show the correct results.

Now, when I run the report then cross tab changes the order and grouped by each columns which are selected in Cross tab experts as a rows. I solved the problem by changing the order of my recored in Stored Procedure to same as it was displaying on reports. But, I am still having some problem in ordering so that I was looking for the original order in groups again and then I posted that question. Now, it is clear to me that cross tab does not support original order.

My Reports columns are like thats :

[ID] [Quantity] [Running Total of Qty] [Dynamic Columns...]


Now, I have more questions:

1- Does Cross Tab support running totals ?

2- What do you mean by Manual Cross Tab Reports ?

3- If I am getting my dynamic columns from Stored Procedure then how can I show then on report b/c we have to design reports by selecting each field and placing on report.

4- What do you mean by Hard Coded columns ?

Your help will be very appreciated.

Thanks.


Muhammad Essa Mughal
Software Engineer
iLogic Inc.

 

[kenhamady]

>>1- Does Cross Tab support running totals ?
No, sorry.

>>2- What do you mean by Manual Cross Tab Reports ?
See the formulas page on my web site for a description of how to create a manual cross-tab, Item #12.

>>3- If I am getting my dynamic columns from Stored
>> Procedure then how can I show then on report b/c we
>> have to design reports by selecting each field and
>> placing on report.

Crystal is designed to run the report against the same columns of data each time it is run.

>>4- What do you mean by Hard Coded columns ?
See answer to #2

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[beanbrain]

>>Now, I am having problem with the ordering of records.

You can create a formula that contains the names of the items in the orignal order and assign a numerical value to each of these "names." Please note that this is accomplished with a multi-layered if...then...else statement. Also, this is extremely handy

This formula can then be added in the CrossTab expert and used to establish an order only. e.g. the formula can be set such that it does not display, but still has it's effect.

WhilePrintingRecords;
EvaluateAfter({keyFieldName})
if {keyFieldName} = "Zebra" then 1 else
if {keyFieldName} = "Teddy Bear" then 2 else
if {keyFieldName} = "Brown Dodger" then 3 else
if {keyFieldName} = "Axa Daxa" then 4;

This formula can then be placed as a regular field in the CrossTab. In the Cross-Tab Expert under "Customize Style" be sure to "Supress Total" and "Supress Label" to prevent the numerical values from showing up.

Supposedly, you can also add a running total field, but I have had no luck with this myself. Then again I was using formula fields not resultSet fields so this may have been the reason.

According to the Crystal Decisions support guide you do the following to embed a running total into a Cross-Tab;

[ul square]
[li]Ceate your running total "formula" and save it.[/li]
[li]Right click on the blank left top area of the Cross-Tab.[/li]
[li]On the Cross-Tab 'tab' of the Cross Tab expert add the running total field to the <b>Summarized Fields</b> area.[/li]
[li]Click "OK" to close the Cross Tab expert.[/li]
[li]Click the <b>Record Sort Expert</b> and sort your Cross-Tab by the field you have designated as a Column in the Cross-Tab expert.[/li]
[li]Create a second sort on the field you designated as a Row in the Cross-Tab expert.[/li]
[/ul]