Hi Friends,

I am working on one requirement where i have to display 'clash' or 'no clash' message based on different time table conditions for a staff.

1) If we have different rooms booked for the same period for one staff then 'Clash'

Ex data :

Room      Period     Staff 

100           1           A

101           1           A

2) There is a chance of having blank room along with other room then 'clash'.Here blank space can be considered as a room.

Ex data :

Room      Period     Staff 

100           1           A

100           1           A

                 1           A

3) There is a chance of having blank room along with other room then 'clash'.Here blank space can be considered as a room.

Ex data :

Room      Period     Staff 

100           1           A

100           1           A

                 1           A

                 1           A

'No clash' conditions

1) If we have same room for the same period then 'No Clash'

Ex data :

Room      Period     Staff 

100           1           A

100           1           A

2) If we have two blank rooms for the same period then 'No Clash'.Here blank means room.

Ex data :

Room      Period     Staff 

                1           A

                1           A

I have to fulfill all the logic's in the same report.Please help me to solve this.

sample report:

Thanks in advance,

Sam. 

• Edited by Samhith Wednesday, February 18, 2015 11:22 PM

Hi Samhith,

I have tested on my local environment that we can modify the query to get the count of the period of the same room in the dataset and add expression to contional show the message as "No Calsh" or "Calsh".

I assumed you have sample data as below in the table:

Detail information below for your reference:

1. Add below query to group by the data in the table and display the null value as "0":
   select room,count(case when room is null then '0' else room end) as CountofPeriod,period,staff from TableName
   group by room,period,staff
2. We can using the expression below :
   =iif(Fields!CountofPeriod.Value<2,"No Clash","Clash")
3. Preview as below in the report
   

If your problem still exists, please try to provide some sample data in the table and also the exprectted result of the "No Clash","Calsh" accordingly.

Regards,
Vicky Liu

Hi Vicky,

Thanks for providing some hint with the available data unfortunately my problem still exists,Please find further details and suggest me some solution.

In the below picture if you observe Day number 3 for Period Number 1 we have one blank value & one value '100' for Room then it should be a clash.

If we have two blanks  or two number values(Same) for the same period then-- no clash

If we have one blank with one number or one blank with two number values(same or different) for the same period then-- clash

Please refer to my actual question also for more clarity.

Here the report grouping are on staff surname , PeriodNumber , PeriodNumberSeq

Thanks for your efforts.Please let me know if you require any other details.

Sam.

• Edited by Samhith 6 hours 15 minutes ago

Hi Vicky,

Thanks for providing some hint with the available data unfortunately my problem still exists,Please find further details and suggest me some solution.

In the below picture if you observe Day number 3 for Period Number 1 we have one blank value & one value '100' for Room then it should be a clash.

If we have two blanks  or two number values(Same) for the same period then-- no clash

If we have one blank with one number or one blank with two number values(same or different) for the same period then-- clash

Please refer to my actual question also for more clarity.

Here the report grouping are on staff surname , PeriodNumber , PeriodNumberSeq

Thanks for your efforts.Please let me know if you require any other details.

Sam.

• Edited by Samhith Monday, February 23, 2015 5:15 AM

Hi Samhith,

I have checked your snapshot but still very clear about your report structure in design mode, pleaase reference to below information that hope it will help.

I suggest you to modify the query to group the data as below, please modify the group based on your requirement to get the correct CountofPeriod which we will use in the expression:

select Day,room,count(case when room is null then '0' else room end) as CountofPeriod,period,staff from tablename
group by room,period,staff,day

Sample data i have added to test:


When the CountofPeriod have the value>=2 based on the same group(Same room,Day number, Period Number ) then it will "No Clash" or it will "Clash".

Expression you can use in the report to conditional display the "No Clash"and "Clash":
=iif(Fields!CountofPeriod.Value<2,"Clash","NoClash")

You can also add an column to display the 'Clash' and 'NoClash' by modify the query,for example:

select Day,room,
count(case when room is null then '0' else room end) as CountofPeriod,
period,staff,(case when count(case when room is null then '0' else room end) >=2 then 'No clash' else 'Clash' end) as Status
from TableName
group by room,period,staff,day

Both method will have execution result like below:

If you still have any problem, please feel free to ask.

Regards
Vic

Hi Vicky_Liu,

Sorry for the late reply, When we take your data for Day 1 we have room values as blank & 101 for the same period then it should be clash but method specified by you is returning 'No Clash' may be i have not provided you with a clear requirement.Please find the requirement below again 

whether it's a blank with some other room or different rooms for the same period on the same day it should be clash,

if we have two blanks or same room for the same period on the same day it should not be a clash(No Clash).

1) If we have different rooms booked for the same period for one staff then 'Clash'

Ex data :

Room      Period     Staff 

100           1           A

101           1           A

2) There is a chance of having blank room along with other room then 'clash'.Here blank space can be considered as a room.

Ex data :

Room      Period     Staff 

100           1           A

100           1           A

                 1           A

3) There is a chance of having blank room along with other room then 'clash'.Here blank space can be considered as a room.

Ex data :

Room      Period     Staff 

100           1           A

100           1           A

                 1           A

                 1           A

'No clash' conditions

1) If we have same room for the same period then 'No Clash'

Ex data :

Room      Period     Staff 

100           1           A

100           1           A

2) If we have two blank rooms for the same period then 'No Clash'.Here blank means room.

Ex data :

Room      Period     Staff 

                1           A

                1           A

still looking for the solution.Please help me.Please let me know for further details.

Thanks,

Sam

• Edited by Samhith Monday, March 02, 2015 1:30 AM edited

Hi Samhith,

Per my understanding that the status "Clash" and "No Clash" should determined on the "Day","Room","Period","Staff" on the same time, right? 

I have modify the query and tested on more time, when count distinct of the room based on the same period,staff and day, the number=1 mean all the room is the same, when >1 means there are difference, please find the query as below:

select day,period,staff,
count(distinct case when room is null then '0' else room end)as countroom,
(case when count(distinct case when room is null then '0' else room end)=1 then 'No clash' else 'Clash' end) as Status

from TableName

 

Preview result as below:

If you still have any problem, please feel free to ask.

Regards,
Vic

Hi Vicky_Liu,

Thanks for your response.It is working fine now.

Thanks,

Sam