Count of Cancelled Orders

[mwake]

I'm using CR2011 with a SQL Server db as my data source. I'm working for a startup that's developed an app to hail cabs and limos. Because of some messy data, I'm having problems creating a report with an accurate count of cancelled orders. If the source of the order is dispatch, a distinct count of order id will work. However, if the source is a mobile phone, there can be multiple cancelled order id's for a single customer session. There is no session id in the database, but the user id is associated to each cancelled order entry. Here is a sample of the data:

Order Id Date/Time Address User ID
1171 5/1/2012 5:00:01PM 1861 Hyde St mw@yahoo.com
1172 5/1/2012 5:01:36PM 1861 Hyde St mw@yahoo.com
1173 5/1/2012 5:02:45PM 1895 Hyde St mw@yahoo.com
1174 5/1/2012 5:03:12PM 650 Mission St rider1@aol.com
1175 5/1/2012 5:04:15PM 1925 Hyde St mw@yahoo.com
1252 5/3/2012 9:45:16PM 425 1st St mw@yahoo.com
1253 5/3/2012 9:48:49PM 652 Polk rider1@aol.com
1254 5/3/2012 9:48:52PM 498 1st St mw@yahoo.com

Because the same user can order a cab multiple times per day, I've grouped the data by City, Fleet, Order Date(by day), Order Date(by hour), then by user id. Grouping by user id and hour creates a customer "session" and the total number of user groups by hour represent the number of actual customer sessions or orders. Once I have this grouping, I can only do a running total using distinct counts, but the counts get screwed up when I look at a month's worth of data. Is there any way to use a formula to create a unique user session id that I can use for counts?? Given the data limitations, what's the best way to get an accurate count of cancelled orders??

 

[hilfy]

I would create a formula from the date, time, and UserID to get an ID to group on. It would look something like this (I'll call this {@SessionID}):

ToText({table.datetime field}, 'yyyyMMddHH') + {table.UserID}

Because you have to format the date as a string that will sort in the correct order, it needs to be in year/month/day format with leading 0's on the month and day when they're only one digit. The "HH" part of the format gets you the hour as 01 through 24 with a leading 0 if it's earlier than 10 am.

If I've correctly understood what you're looking to count, to get the cancellation count, I would create a formula something like the following:

NumberVar IsCancelled; 
If PreviousIsNull({@SessionID}) or {@SessionID} <> previous({@SessionID}) then IsCancelled := 0;
If <order is cancelled> then IsCancelled := 1;
IsCancelled

Then sum this formula to get your count.

-Dello

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[mwake]

I created the SessionID field, then created the formula you suggested, but got a message saying a number, currency amount, boolean, date, time, date-time, or string is expected where you have <order is cancelled>. Do I populate this with a value??

 

[kray4660]

I do not believe Dell knows what constitutes a cancelled order so in place <order is cancelled> you would put in the criteria for a cancelled order.

 

[hilfy]

That is correct - replace "<order is cancelled>" with whatever criteria means that the order is cancelled.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.