Date filter toggling from Day to Month

[zesty1989]

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?

[Lynn]

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.

Code Select Expand


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.

[zesty1989]

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?