Fomula help with rows

[fiber0pti]

I have a cross tab report that is pulling orders from a DB with the following criterion:

"Estimated Book Date" that's between to specified dates OR
"Complete Date" that's between the same to specified dates.

The row is Estimated Book Date grouped by month. The summarized fields are “estimated revenue” for all of the orders. This field sums up all orders for that month but excludes the orders that are marked complete. The other summarized field is “actual revenue” which is a total of all orders with a complete date between the specified dates.

So, basically, I am retrieving date that would have two different dates, estimated book date and complete date, but need to have the estimated book dates be the rows but also include the information from the complete dates in a summarized field that’s within the same row. I’ve tried a formula on the row (if not {orders.complete} then {orders.estBookDate}). This doesn’t get a desired result as I have a row that doesn’t have a label but has numbers. I know this is happening because that row contains orders that have an estimated book date between the specified dates but also has a complete date which is a different month from the estimated book date. How can I prevent that blank row from showing up and put those numbers in the correct row?

Any ideas?

 

[lbass]

What version of CR are you using? One approach might be to use a union all statement to "merge" the estimated and completed dates into one field, with another field or text to identify which is which. Then you could easily limit dates to the range and get the two summaries in the same cell, I think, using conditional summary formulas.

-LB

 

[fiber0pti]

I'm using CR 9. How would I merge the fields? Could you give me a more specific example of what you're suggesting?
Thanks!

 

[lbass]

Are you using any parameters? I'm not sure whether you can use parameters in a union statement in CR 9.0 in the Add Command area.

-LB

 

[fiber0pti]

Yes, I'm using two parameters.

 

[lbass]

As I said, I'm not familiar with 9.0, so am not sure whether the parameter part will work, but you can try this.
Maybe someone else will jump in on whether you can build parameters into union statements in 9.0.

You are basically starting from scratch using "Add Command" as your datasource. Then you enter your SQL query (the specific punctuation/syntax depends upon your data source--you might want to look at the "Show SQL query" in your current report to know how to go about this--or even see if you can copy it into a new report Add Command area and then add the union all statement):

SELECT
'EstimatedBookDate', table.`estimatedbookdate`,table.`otherfield1`, table.`otherfield2`
FROM
`table` table
WHERE
table.`EstimatedBookDate` = '{?daterange}'
UNION ALL
SELECT
'CompleteDate', table.`comopletedate`,table.`otherfield1`, table.`otherfield2`
FROM
`table` table
WHERE
table.`EstimatedBookDate` = '{?daterange}'

You could try the parameters with and without the ''.

-LB

 

[fiber0pti]

It would seem as this is just gathering the rows that are needed for the query. I have no problem getting all the rows I need it's just displaying them in the correct place. Am I misunderstanding you?

 

[lbass]

Once you have this query, the {table.estimatedbookdate} field will contain both the original estimated book date field and the completed date field. The records returned will always meet your daterange criteria, whereas your original "or" statement would return other records that met one but not the other criterion. When you use this field in the row of the crosstab, you can now get the two different summaries in the same row, by summing formulas like the following:

//{@estbkdate}:
if <Expression1001> = "EstimatedBookDate" then {table.amt}

//{@completedate}:
if <Expression1001> = "CompleteDate" then {table.amt}

//where <Expression1001> is the name of the first field entered in the query (not sure how it will be named on your field list)

Actually, if you started the query with:

Select
'EstimatedBookDate' as Date
|
|
|
V
UNION all
Select
'CompleteDate' as Date

Then "Date" will appear on your field list.

-LB