Seeking formula/views advise 1

[CRilliterate]

If it is not too much to ask, please, can you help me to understand this formula
1.
shared currencyVar CreditReviewAmountForCustomer;
SUM({ORDER_DETAIL.ACTUAL_AMOUNT},{ORDER_DETAIL.CUSTOMER_ID})

Result of this formula is a dollar amount owed by Customer.
What {ORDER_DETAIL.CUSTOMER_ID}is for?

2.
Also my existing Reports based on SQL Server tables that are about 200 column each. Report uses only 1-2 fields from the table. Would this benefit performance to write a view and use it in place of those tables? I think because views are executed on Database level it will improve performance and also hundreds of dead fields from huge tables will not hang in Crystal doing nothing. Am I wrong? Thanks

 

[BobSuruncle]

Question 1 - the formula
shared currencyVar CreditReviewAmountForCustomer;
This line retrieves the value of a variable called "CreditReviewAmountForCustomer"
It doesn't look as though this value is being used anywhere in this formula (assuming that you gave us everything)

SUM({ORDER_DETAIL.ACTUAL_AMOUNT},{ORDER_DETAIL.CUSTOMER_ID})
This line calculates the subtotal (SUM) of the ({ORDER_DETAIL.ACTUAL_AMOUNT}database field for the group based on the {ORDER_DETAIL.CUSTOMER_ID} field.
In other words, what is the subtotal of the Actual Amount for each Customer.
There is obviously a group on your report based on customer.

Question 2
In a perfect world, you would always use a database view or stored procedure to report against. This allows the database server to do all the work, and generally makes maintaining the report a lot easier.
In the real world, however, we aren't always so lucky.
We don't always have the rights to create said views and procs. DBA's aren't always co-operative. Back-end databases are sometimes proprietary and don't allow for creativity.

Basically, if you've got the ability to create a view and use it for your reporting needs, go for it.



Bob Suruncle

 

[damonkimble]

As far as question 2 goes, depending on your version of crystal, you can create a query with or without parameters that runs on the db server and returns the results in a table form. Check out the 'Add Command to Report dialog box' and 'Database Expert' in help (this is what they are labeled in cr10).

damon

 

[CRilliterate]

To a question 1
I have few more groups that are hierarchically (omg spelling) superiour to Customer_ID group, this is why I questioned use of Customer_ID in formula, I thought it does something else I am anaware of...lol

You right about views. I am not sure I have rights to create but IF I can PROVE IT that performance will be much better I can get those rights and I can write those views and test it because we have test version of software we are playing with, so now it is time to do it and make them to believe but how can I PROVE it?

Thanks a lot for your time

 

[BobSuruncle]

If you've got MS SQL Server (as I understand from your first post) then you could copy the SQL Statement from Crystal and paste it into Query Analyzer (there may be some syntax issues).

You get the SQL statement in Crystal from the Database Menu-> Show SQL Query.

Run the query from Query Analyzer and note the time for execution.
Then query for just the fields that you require for the report (the 2 or 3 you say you need) and compare the execution time.
This will give you a rough estimate.

Bob Suruncle

 

[CRilliterate]

Thanks so much!!!