Can I extract a data item from a field that contains many data items?

Question

Can I extract a data item from a field that contains many data items?

faulknerwilliam2

SSCrazy

Points: 2299
More actions
November 24, 2014 at 6:05 am

#397680

Any help would be appreciated please.
I have set up a simple report that shows date, timeslot and reason for visit. However the timeslot displays in order that the order was processed and I get for example:
Date Timeslot Reason
24/11/14 1300-1800 Repair TV
24/11/14 0800 - 1200 Repair TV
So even though on 24/11/14 we have a morning appointment booked, it appears on the report after the afternoon one, because the office handled the call requesting a PM booking first.
So what I would like is for the timeslot to show to earlier times first: Date Timeslot Reason
24/11/14 0800 - 1200 Repair TV
24/11/14 1300 - 1800 Repair TV
This would normally be simple but data for timeslot comes from a field which contains several items of data (fields!businessnamevalue), where several pices of information are gathered, eg timeslot, customer's name, reason for visit etc. This data is organised in the report as:
=switch(fields!businessname.value = "TIME", "timeslot", fields!businessname.value = "CustName", "Customer's Name")
So my issue is, can the field (fields!businessname.value) be 'split up' in any way other than by using switch, so I can sort my report so the timeslot shows morning appointments before pm appointments?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65142 More actions · Answer 1

Add a calculated field to the dataset that parses the column you mention and converts the start of the time period to TIME datatype and use that to order the data

Far away is close at hand in the images of elsewhere.
Anon.

faulknerwilliam2 SSCrazy Points: 2299 More actions · Answer 2

Thanks for taking the time to get back.

Sorry to appear slightly dense (I am fairly new to reporting services), but how would I parse the column? I get the time bit, and sorting, and know how to add a calculated field - it's just the parsing bit that has me scratching my head.

Thanks.

David Burrows SSC Guru Points: 65142 More actions · Answer 3

It depends on the content of fields!businessnamevalue that contains timeslot (as per your description)

can you post examples of this field?

Far away is close at hand in the images of elsewhere.
Anon.

faulknerwilliam2 SSCrazy Points: 2299 More actions · Answer 4

Thank you.

Basically if the field is put straight into the report (as a column) it splits into three:

Timeslot appears first

Type of job second

Address third.

So the content of the field is timeslot - type of job - address, in that order.

David Burrows SSC Guru Points: 65142 More actions · Answer 5

Add a calculated field and set the expression to

=TimeSerial(Mid(Fields!businessnamevalue.Value,1,2),Mid(Fields!businessnamevalue.Value,3,2),0)

this will give you a date type field in the dataset (the date will be 01/01/001 but the time will be correct)

You can then order by this field

Far away is close at hand in the images of elsewhere.
Anon.

faulknerwilliam2 SSCrazy Points: 2299 More actions · Answer 6

Thanks. I've entered the expression and it's thrown up an error: 'the valueexpression used in field 'time' references a dataset field which contais an error. Conversion from string "DA" to type "Integer" is not valid'

This doesn't mean much to me - do you have an idea?

Many thanks for helping with this.

David Burrows SSC Guru Points: 65142 More actions · Answer 7

This means that either

Mid(Fields!businessnamevalue.Value,1,2) chars 1 and 2

or

Mid(Fields!businessnamevalue.Value,3,2) chars 3 and 4

has the value 'DA'

which indicates to me that the field businessnamevalue does not always start with timeslot

Far away is close at hand in the images of elsewhere.
Anon.

faulknerwilliam2 SSCrazy Points: 2299 More actions · Answer 8

Many thanks - your help and answers today have got me on the right track.

MMartin1 One Orange Chip Points: 27795 More actions · Answer 9

Be aware to first sort by the actual date field , then your derived time field in the report. Else although your time filed will be ascending, the dates will be all over the map. This assuming your report can cover more than one day.

----------------------------------------------------

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

To be honest, having a "timeslot field" is a really bad idea. You should have a StartDT and an EndDT where you have both the date and time (hence, the "DT" in both names) in each column. That will make your life a whole lot easier for calculating conflicting time slots, open time slots, total hours slotted, etc, ad infinitum.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27795 More actions · Answer 11

Hmm, he may be interested in having the time portion to report what time of day he has the most business. In this case it would be ideal/wise to just create time buckets. Such as hour of the day or (morning, afternoon, evening)... you would have these as calculated fields. But as mentioned having date and time together makes things easier when making sure there are no overlaps in scheduling, for instance. Also if you want to count appointments by day there then using date functions in sql (eg, CAST as DATE) will help immensely, (no need to separate the date and time). My thoughts.

----------------------------------------------------