Hi All,
There are two columns in a Report. One is Date column which display date in DD-MM-YYY HH:MM AM/PM format and one is Items column (will display item names like Item1, Item2, Item3 and Item4).
The third column should have values based on the date.
Date |
Items |
If the Day of the week is a Week Day |
If the Day of the week is a Saturday |
If the Day of the week is a Sunday + Holiday |
DD-MM-YYY HH:MM AM |
Item1 |
V1 |
||
DD-MM-YYY HH:MM AM |
Item2 |
V2 |
V3 |
|
DD-MM-YYY HH:MM AM |
Item3 |
V4 |
||
DD-MM-YYY HH:MM AM |
Item4 |
V1 |
V1 |
V1 |
Can anyone assist?
Thanks,
Ashwitha
Answer
Hi Ashwitha,
This would be a very tedious task since all the values must be hard-coded in the column syntax itself using nested if conditions. A sample expression you can see below. That entire expression is the syntax in one column to accommodate all the scenario that you presented above. Here %w checks for the day of the week
if(date_format(sysdate(),'%w') IN ('1','2','3','4','5'),if(incidents.item IN ('Item1','Item4'),'V1',if(incidents.item = 'Item2','V2')),if(date_format(sysdate(),'%w') = '6',if(incidents.item = 'Item2','V3',if(incidents.item = 'Item4','V1')),if(date_format(sysdate(),'%w') = '0',if(incidents.item = 'Item3','V4',if(incidents.item = 'Item4','V1')))))
If you could provide the use case then maybe we could look for an alternate solution approach by maybe splitting the data into multiple columns or building a drill down to segregate data based on day and item.
Regards,
Sasi K Rao.
Average Rating:
100% (+1/-0)
|
Sign in to rate this
As Sasi states above creating this report isn't that easy.
Is it worth considering if it might be worth using a custom field (or several) and populate the value/s either manually or via business rules/Custom Process Model and that might the report easier to build? For example, there could be 3 custom fields: Weekday, Saturday, Sunday + Hol that get set with a value. Then the report would be much easier to build.
Sometimes I've found that using additional fields and getting them populated somehow can lead to more simple reports. The trick is to 'how' to get them populated.
Be the first to rate this
|
Sign in to rate this