How do I approach this? (Grouping by time ranges) 1

[uczmeg]

Hi,

I need a little help getting started on a report as I'm not sure how to approach it.

I have a table of sales, something a bit like this:

sale_id sale_datetime sale_total

For any given period I'd like to produce a report with info like this in it:

Time period, Num of sales, % of total sales, total value, % value
08:00 - 09:00
09:00 - 10:00
.
.
16:00 - 17:00

 

[uczmeg]

Sorry, managed to post that without finishing.

I'm using crystal 8.5.

The results would look a little like this:

Time qty qty% value value%
8am-9am 8 5 £300 7
9am-10am 11 7 £450 10

etc

I'm not sure how to group by a range of times from my sql datetime field.

 

[uczmeg]

I'm getting there, now that I have discovered I can have a by hour break down on the dates on the rows.

I have got a count of the sale_ids by time, and a sum of the sales by time now. Buy can't work out how to get a relation to a percentage of the total qty and sales.

I also can't seem to get the fields out of one column, so far I have:

Time

09:00 5 (this is the total sales for the hour)
£300 (this is the total amount of the sales for the hour)
10:00 7
£450
11:00 4
£275

Is it possible to break these into two columns?

And how about the relation to the total as a percentage for each, also in seperate columns.

Thanks
Marc

 

[lbass]

Since these are summaries of separate fields, you just have to move them so that they are side by side in a group header or group footer section. Then create these formulas:

//{@percentsales}:
sum({table.sales},{table.datetime},"hourly") %
Sum({table.sales})

//{@percentsalescnt}:
count({table.salesId},{table.datetime},"hourly") % count({table.salesID})

Place these in the group header or group footer section and then click on the % icon in the toolbar.

-LB

 

[uczmeg]

Thanks for the help LB.

Sadly I don't have a clue what you mean, in the cross tab I can't see any reference to group headers or footers.

Can you be a little more 'Crosstabs for dummies' please!

 

[lbass]

This is the first you've mentioned a crosstab, but I don't really think it's called for here. I would just set this up as a regular report with a group on the datetime field, and then just follow the steps I mentioned earlier.

-LB

 

[uczmeg]

Thanks LB.

I was sure I'd put crosstab in my third post! Clearly not.

I had no idea Crystal could group by time in the way it allows, and only noticed it when messing with a crosstab and assumed it must have been related to that, I didn't think of trying it to a normal report.

As you can apply that to a normal report, all is simple!

Thank you for your time.

Cheers
Marc

 

[uczmeg]

Hi

Having a bit of a proplem putting this into operation.

When trying to create either formula field I get the error:

"The summary / running total field could not be created"

Googling on this has found when other have reported it a problem it is because the group for 'condfld' in the sum statement doesn't exist. Which in my case it does.

Syntax: sum(fld,condfld)
My code: sum({transactions.cash},{transactions.trans_time},"hourly")

Any suggestions?

Thanks
Marc

 

[uczmeg]

And once more I reply to myself!

Found the problem. It's not:

sum({table.sales},{table.datetime},"hourly")

but:

sum({table.sales},{table.datetime},"by hour")

All works fine now.

Thanks.

 

[lbass]

Sorry, I should have double checked to determine the correct condition.

-LB

 

[rds80]

I have a set of ControlIDs and NoteIDs that have to have alternative colors.

For example, in the GroupHeader section I have a set of ControlIDs and NoteIDs (format ControlID-NoteID) 01-001, 02-001 and 02-002, 03-001 and 03-002, 04-001 and 04-002. If the control ids have a set of noteids (02-001 and 02-002) then these group of controlids have to be the same color. But two consecutive control ids that have multiple noteids cannot be the same color.

So 02-001 and 02-002 will have the same color because they are grouped together, but 03-001 03-002 should have a different color because it is right after 02-001 and 02-002.

What I would like to do is set 02-001 and 02-002 set to beige and 03-001 and 03-002 to transparent, 04-001 and 04-002 back to beige.

01-001 is transparent

02-001
02-002 are beige

03-001 is transparent

04-001
04-002 are beige

05-001
05-002 are transparent