February 28, 2008 at 5:20 am
Hello All
I have a report which lists client\matters details whereby my report have 4 date columns (date 1, date 2, date 3, date 4) I have created a new column called Latest Stage.
How do I check if Date 1 is not blank then put the value in the column latest Stage called Stage 1
If Date 1 date is missing, check date 2. If date 2 is not blank then put a value in Latest Stage called Stage 2
Check in Date 3 if it is not blank then enter Stage 3 in Latest Stage
and Stage 4 in Latest Stage column if date 4 has column.
I cannot seem to check if the date is not null as the report gave me an error.
Any ideas would be welcom
Thank you
Will
March 4, 2008 at 3:04 am
Hi I've found an answer to my question. I've used a nested IIF statement to populated the answer based on the dates.
I have another question because I use a select clause to select the dates (date1,date2,date3,date4)
Is there anyway I can use the nested IIF statement to check the four dates if the four dates is omitted from the select clause.
If so how can I perform this in my report
Thanks
Will
March 6, 2008 at 10:56 am
The most obvious question would be how would the dates optionally appear in the select clause?
I'll assume for a moment, that you're using a parameter to specify which dates to add to the result set, so you could simply test the parameter value, to see if the date field is included and then perform the subsequent steps.
March 7, 2008 at 2:58 am
Thank you for your reply.
I am not using any parameter of any sort as I need to populate fields for the report from 5 different tables.
What I've done is to select date1,date2,date3,date4 attribute from the select clause and then in my report, I create a column called Stage to store the stages based on the dates that contains value and use the nested IIF statement to find out which dates (1 to 4) contains a date.
Once done, I then hide the Date1,date2, date3,date 4 from being viewed by the user.
My select clause would be something like this
Select field1,field2, field3,field4, date1,date2,date3,date4
from ......
where....
That is fine but I just wanted to know if I omitted the date fields i.e
Select field1,field2, field3,field4
from ......
where....
How would I check if any of the dates include a value or not so that I dont need to hide the dates as I am currently doing.
My SQL knowledge is a bit rusty as I have not been doing SQL for a number of years.
Thanks
March 7, 2008 at 7:28 am
How about a coalesce statement? This would provide the first non-null entry amountst the 4 date values
Select COALESCE(date1,date2,date3,date4) as Stage
from ......
where....
You could also rever the pattern to check date4 first. So if something was in date 4, you would never check any other date. If 4 was empty, date3 would be checked...
Select COALESCE(date4,date3,date2,date1) as Stage
from ......
where....
You could even use a calculated date if date values 1-4 were all empty.
Select COALESCE(date1,date2,date3,date4,getdate()) as Stage
from ......
where....
March 7, 2008 at 9:28 am
Robert's suggestion is of course the right one. You can't omit all of the dates from the SQL statement, and then test it in MSRS.
His suggestion brings up an important point (for other posters who happen to see this thread): Don't always assume that the right way to solve the problem is in one layer or the other. The more info provided on the problem, the better answer you'll get on how to solve it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply