Grouping Data - Crosstab using the latest results

[dannyb29]

Hi all,
I am in the process of trying to create a crosstab showing tht total cost and retail values the latest transaction (this is distinguished by time)
I have grouped the information to show you what data I am working with and the results I need to capture in my crosstab

Esaxmple one:


Total Cost Total Retail
Group1 Set ID – 201 4.50 9.00
Group2 Timestamp 10/12/2011 15:00:56
Detail – product, Qty, total cost, total retail, transaction ID,
Cola, 2, 1.00 2.00 1501
Biscuit, 1 0.50 1.00 1501
Sandwich, 2 1.00 2.00 1501

Total Cost Total Retail
Group1 Set ID – 201 6.50 9.00
Group2 Timestamp 10/12/2011 21:00:56
Detail – product, Qty, total cost, total retail, transaction ID,
Cola, 2, 2.00 2.00 1502
Biscuit, 1 0.50 1.00 1502
Sandwich,2 1.00 2.00 1502

In the crosstab I would show 6.50 and 9.00 respectively as this is latest time.

Example 2

Total Cost Total Retail
Group1 Set ID – 202 4.50 9.00
Group2 Timestamp 11/12/2011 16:00:56
Detail – product, Qty, total cost, total retail, transaction ID,
Apple, 2, 1.00 2.00 1503
Biscuit, 1 0.50 1.00 1503
Sandwich, 2 1.00 2.00 1503

Total Cost Total Retail
Group1 Set ID – 202 4.50 9.00
Group2 Timestamp 11/12/2011 20:00:56
Detail – product, Qty, total cost, total retail, transaction ID,
Orange, 1, 2.00 2.00 1500
Biscuit, 1 0.50 1.00 1500
Sandwich, 2 1.00 2.00 1500

In the crosstab I would show 4.50 and 9.00 respectively as this is latest time. I cannot use the transaction ID as the sequences are not reliable.


CROSSTAB EXAMPLE
Set_ID 10/12/2011 11/12/2011 12/12/2011
T.Cost T.Retail T.Cost T.Retail T.Cost T.Retail
201 7.50 9.00 - -
202 - 4.50 9.00 -

I hope thois makes sense and I will be happy to provide any further information.
Dannyb

 

[lbass]

What version of CR are you using? What type of database?

-LB

 

[dannyb29]

Hi Ibass
I am using Crystal Reports 2008, connecting through the ODBC option. It is a SQL database

dannyb

 

[lbass]

Create a SQL expression {%maxdt} like the following:

(
select max(A."timestamp")
from "table" A
where A."Set ID"="table"."Set ID"
)

Replace "table" with your actual table name. Leave "A" as is. Then use a record selection formula like this:

{table.timestamp}={%maxdt}

-LB

 

[dannyb29]

Hi Ibass,
The SQL formula is throwing up an error. Below is a copy of the statement. Have I transposed it correctly?

(
select max(A.{ProductsSoldPd.ActionDateTime})
from ProductsSoldPd A
where A.{ApplicableTrainDates.DiagramId}=ProductsSoldPd.{ApplicableTrainDates.DiagramId}
)

dannyb

 

[lbass]

You are creating this in the wrong area. You should go into the field explorer->SQL expression and create it there.

On what field are ApplicableTrainDates and ProductsSoldPd linked? You will have to build this into the expression.

-LB

 

[dannyb29]

in Order the retrieve the DiagramID I have to use 2 tables and join 3 fields. All usng the inner join.

Please could you help me encorporate that into the sql code?

ApplicableTrainDates.Date --> ProductsSoldPd.ActionDate

ApplicableTrainDates.RetailServiceId --> ProductsSoldPd.RetailServiceID

ApplicableTrainDates.Headcode --> ProductsSoldPd.HeadCode

dannyb

 

[lbass]

(
Select max(B."ActionDateTime")
from "ApplicableTrainDates" A, "ProductsSoldPd" B
where A."Date"=B."ActionDate" and
A."RetailServiceId"=B."RetailServiceID" and
A."Headcode"=B."Headcode" and
A."DiagramId"="ApplicableTrainDates"."DiagramId"
)

Try copying this into the SQL expression {%maxdt}. Then set the record selection formula to:

{ProductsSoldPd.ActionDateTime}={%maxdt}

This assumes that you have both a field actiondate and a field actiondatetime.

-LB

 

[dannyb29]

Hi Ibass
Please see my comments next to your coding as i amy have not been clear

(
Select max(B."ActionDateTime")//this field does not exist in the ApplicaableTrainDates tbl. Even though the data is the same in both fields, the actual field is called Date. I think this is why an error is still being generated.

from "ApplicableTrainDates" A, "ProductsSoldPd" B
where A."Date"=B."ActionDate" and
A."RetailServiceId"=B."RetailServiceID" and
A."Headcode"=B."Headcode" and
A."DiagramId"="ApplicableTrainDates"."DiagramId"
)


dannyb

 

[lbass]

You said the field you wanted to show the maximum was the {ProductsSoldPd.ActionDateTime} so that is what I'm showing in the max(). What error message are you getting?

Also please try adding a field from the field list in the SQL expression editor and then show me what it looks like in the thread.

-LB

 

[dannyb29]

The error message i am getting is this, and i apologise if i have misinterpreted the error.

Database connector Error: '42000[Microsoft][ODBC SQL Server Driver][Sql Server] The multi-part identifier
"ApplicableTrainDates.DiagramID" counld not be bound. [Database vendor code 4104]'

When i add a field into the SQL expression editor, it appears like this:
"ApplicableTrainDates"."Date"

-dannyb

 

[lbass]

Please copy the expression into the post that is creating this error message.

-LB

 

[dannyb29]

(
Select max(B."ActionDateTime")
from "ApplicableTrainDates" A, "ProductsSoldPd" B
where A."Date"=B."ActionDate" and
A."RetailServiceId"=B."RetailServiceID" and
A."Headcode"=B."Headcode" and
A."DiagramId"="ApplicableTrainDates"."DiagramId"
)

 

[lbass]

That looks correct to me. Can you also post the current sql query (database->show SQL query)? It might be that you have to specify an owner.

-LB

 

[dannyb29]

Hi Ibass,
The SQL query is as follows:

SELECT "AVBRecordTypes"."Description", "ProductsSoldPd"."ActionDateTime", "ProductsSoldPd"."ProductCode", "ProductsSoldPd"."ShortDescription", "ProductsSoldPd"."Quantity", "ApplicableTrainDates"."DiagramId", "ProductsSoldPd"."CostPrice", "ProductsSoldPd"."RetailPrice", "ProductsSoldPd"."TxnId"
FROM "OCMPhase2_Report"."dbo"."AVBRecordTypes" "AVBRecordTypes" INNER JOIN ("OCMPhase2_Report"."dbo"."ApplicableTrainDates" "ApplicableTrainDates" INNER JOIN "OCMPhase2_Report"."dbo"."ProductsSoldPd" "ProductsSoldPd" ON (("ApplicableTrainDates"."RetailServiceId"="ProductsSoldPd"."RetailServiceID") AND ("ApplicableTrainDates"."Headcode"="ProductsSoldPd"."HeadCode")) AND ("ApplicableTrainDates"."Date"="ProductsSoldPd"."ActionDate")) ON "AVBRecordTypes"."RecordTypeId"="ProductsSoldPd"."RecordType"
WHERE ("ProductsSoldPd"."ActionDateTime">={ts '2011-10-05 00:00:00'} AND "ProductsSoldPd"."ActionDateTime"<{ts '2011-10-06 00:00:00'}) AND "AVBRecordTypes"."Description"=N'Train Waste'
ORDER BY "ApplicableTrainDates"."DiagramId", "ProductsSoldPd"."ActionDateTime" DESC

dannyb

 

[lbass]

I think you should try a different approach. Can you explain why you are using a crosstab? It doesn't look like you need one to achieve the results you are looking for. I can see the row field would be set Id and timestamp, but what would your column field be?

-LB

 

[dannyb29]

Hi,
The column would be date, and the summarised fields would be Total Cost and Total Retail Price. This total would be dependant on the latest/maximum time.

Dannyb

 

[lbass]

Please identify the row fields then, as now I'm unsure. Are you using two row fields, one for setId and one for time?

-LB

 

[lbass]

You should use conditional summaries for your summary fields, like this:

if {table.datetime}=maximum({table.datetime},{table.datetime}) then
{table.quantity}

Repeat for other summaries and use sums as the summary type. If you are using time({table.date}) as one of your row fields, then you should also make that conditional, using a formula like:

if {table.datetime}=maximum({table.datetime},{table.datetime}) then
time({table.datetime})

In the customize style tab, check "suppress empty rows" and "suppress empty columns".

-LB