[b]Using Stored Procedure to Calculate Report Values[/b]

[Nassy]

Hi everybody,

I need some advice on something. I have a report that creates summaries based on the number of property transactions and the revenue generated from these transactions eg

[ul]
[li]Number of Instructions[/li]
[li]Number of Property Sales [/li]
[li]Number of Exchanges [/li]
[li]Total Revenue from Sales [/li]
[/ul]

[blue]The Underlying Data Structure[/blue]

I have a table which contains the PropertyID, Transaction Type, the Date the transaction occurred and finally Revenue.

PropertyID Type Date Revenue
-------- ----- ----- -------
1 Instruction 01/07/05 £0
1 Sale 11/07/05 £40k
2 Instruction 04/07/05 £0
2 Sale 06/07/05 £50k
2 Exchange 10/07/05 £50k
3 Instruction 01/06/05 £0
3 Sale 05/06/05 £19k
3 Exchange 14/07/05 £18k


I have created a report that takes a Date as a parameter and returns a set of summarised values for the whole week starting from the date. For example if the user enters 01/07/2005 the report will show Number of Instructions, Sales, Exchanges and Sales Revenue for the week ending 07/07/2005

[blue]The problem[/blue]

There is one additional calculation I would like to add to this report and that is Pipeline. The pipeline is the number of Sales Agreed that had not yet Exchanged on the final day of the week so in the above example that would be 07/07/2005.

For example: calculating the pipeline for 07/07/2005 would be a case of grabbing all the Properties that had a Sale Agreed before 07/07/2005 but which had not had an exchange before 07/07/2005

[blue]How I have tried to resolve this[/blue]

I have tried to write a formula to calculate the pipeline but with the current table structure I am not sure how to do this using Crystal.

I want to be able to calculate Pipeline without having to change the table structure as I want to use the same table structure to create OLAP cubes.

The only solution I can think of is to write a Stored Procedure that takes the date parameter field entered in Crystal as a parameter and then return the results.

Is this possible or is there a possible way to calculate the pipeline just using a regular formula?

Any advice much appreciated.

Nassy

 

[Madawc]

Group by property. Do a pair of running totals for the group, one accumulating Sale Agreed and one accumulating Exchanged.

Write a formula field to test if Sale Agreed is in range but 'Exchanged' is not. Be sure to test for null first (Isnull etc.). Say "Pipiline" when this is the case.

Counting 'Pipiline' cases might be more tricky: it might refuse to do counts or summaries of a field based on more than one record. Ask again if this is a problem, but also do a search here about summary functions.

Madawc Williams (East Anglia, UK)

 

[lbass]

Does the pipeline value reference only those sales made but not exchanged during that week, or do you want to include sales made before the parameter period that were not exchanged by the end of the parameter period?

-LB

 

[Nassy]

Ibass,

The pipeline should include all sales that took place before the last day of the week which had not yet exchanged at that point. For example if the user types in date range: 01-07-2005 - 07-07-2005 then the pipeline will include all properties whose Sale date is less than or equal to 07-07-2005 (meaning all past properties sold by that point) but who either have no exchange date at all or an exchange date that is greater than
07-07-2005. So there could be a property that sold in 01-05-2005 and if it didn't reach exchange until after 07-07-2005 then that property will be counted in the pipeline.

So I think your second statement

do you want to include sales made before the parameter period that were not exchanged by the end of the parameter period?

is more correct but there is no period restriction - just a cut-off point based on the final day of the date range entered by the user.

Hope that helps

Nassy

 

[lbass]

You might want to add a subreport to calculate the pipeline value. Place the subreport in the report header and link it to the main report using the date parameter. When you do this, create the same parameter in the subreport, and link {?date} from the main report to {?date} in the subreport (do not use {?pm-?date}). Then in the subreport, use a record selection formula of:

{table.date} <= {?date}+6

Insert a group on {table.propertyID} and then create a formula like {@type}:

if {table.type} = "Sales" then 1 else
if {table.type} = "Exchange" then 100 else 0

Then go to report->edit selection formula->GROUP and enter:

sum({@type},{table.propertyID}) in 1 to 99

Then insert a running total {#openpropIDs} that does a distinctcount of {table.propertyID}. Create a shared variable:

whileprintingrecords;
shared numbervar pipeline := {#openpropIDs};

Then you can suppress all sections within the subreport. In the main report, create a formula to display the pipeline:

whileprintingrecords;
shared numbervar pipeline;

Place it in any section below the report header (where the subreport is located).

-LB