I am trying to streamline our reports folder. I want to create a report so that if I select all the data for one or several months, say January through June, then the Claim date column would only show the data as 6 rows labeled as 2018-0#. If I am only selecting less than one month's time then I want to have the Claim Date show individual rows for each day selected. For example: If I selected June 14th- June 24th, I would see 7 total rows of data (because there are only 7 business days in this date range), and the rows would be labeled as 2018-06-14, 2018-06-15...
Is there any type of code that I can plug in to create this change?
Quote from: zesty1989 on 13 Aug 2018 05:30:02 PM
I am trying to streamline our reports folder. I want to create a report so that if I select all the data for one or several months, say January through June, then the Claim date column would only show the data as 6 rows labeled as 2018-0#. If I am only selecting less than one month's time then I want to have the Claim Date show individual rows for each day selected. For example: If I selected June 14th- June 24th, I would see 7 total rows of data (because there are only 7 business days in this date range), and the rows would be labeled as 2018-06-14, 2018-06-15...
Is there any type of code that I can plug in to create this change?
The approach will vary depending on whether you've got a relational source or a dimensional source.
Assuming relational and also assuming you have a date prompt for the start of the range and another for the end of the range, you could create a calculation for the column.
case
when _days_between( ?pEndDate?, ?pStartDate? ) <= 30
then cast ( [Your Date Column], varchar(10) )
else [Your Month Column]
end
Note that a case statement must resolve all possible outcomes to the same data type. This means that if your date column is a true date data type and your month column is a string you'd need to cast the date to a string so all outcomes are string.
More details to clarify any incorrect assumptions should help you get a better answer.
Lynn, Thanks so much to your response! I really appreciate your help. I am so new to all of this, how do I use this code? Where do I plug it in?