Nested case in report studio

[satyagorantla]

Hi, I need to do a dynamic grouping on a date column based on the prompt value selected by the user.The prompt values are Daily,Weekly and Monthly. If the user selects daily,the report measures should be grouped on a daily basis.And for weekly,the measures should be grouped on a weekly basis.
For daily,I need to show that day's date.For monthly,need to show the month's last date.So used _last_of_month () function.For Weekly, I need to show the week ending Sunday date. Used a case statement for this:

CASE _day_of_week([Date], 7)

  WHEN (1) THEN (_add_days([Date], 6))
  WHEN (2) THEN (_add_days([Date], 5))
  WHEN (3) THEN (_add_days([Date], 4))
  WHEN (4) THEN (_add_days([Date], 3))
  WHEN (5) THEN (_add_days([Date], 2))
  WHEN (6) THEN (_add_days([Date], 1))
  WHEN (7) THEN ([Date])
  ELSE ([Date])  --- Or whatever you want as default.
END


Now to show the dynamic grouping,I have a case statement on the prompt :

case when (?group_by_column?='daily') then '[Day]'
when (?group_by_column?='weekly') then '[Week]'
when (?group_by_column?='monthly') then '[Month]' end

I am getting an error on the report saying that nested case is not supported on the database(using Teradata as the back end DB). Since [Week] is calculated using a case statement and is used in another case stmt,it's bombing out.Any ideas as to how to overcome this problem? Thanks in advance!

[bdybldr]

Instead of using a case statement in your filter, try using a conditional block list for your report with variables defined to handle the user prompt selection.

[satyagorantla]

I created the variable with the prompt values and it works fine if I don't have a Group by on Day,Week Ending Sunday and Month Ending columns.It shows only the Day column when daily is selected as the prompt value and hides the other 2 date columns,week column when weekly is selected in the prompt.If I apply group by on the columns,the data is hidden but the column titles are still showing up. Please help!

- Thanks

[bdybldr]

I'm not understanding your requirements.Ã,  Please post a mockup report design with the fields and some sample data.Ã,  Then explain what you want to display with each prompt selection.

[bdybldr]

Ok, here's what you need to do...

Create three queries - one grouped by day, one grouped by week, one grouped by month.

Then create three list reports - one from each query.

Next, add a conditional block to your report and associate it to the variable you created earlier.

Go to the Current Block setting on your properties panel and select the value for your variable (day, or whatever).  Then move the list report that is grouped by day into the conditional block.

Do the same for the other two lists (week and month).

Hope this helps.  Keep us posted.

[satyagorantla]

Please refer to the document for the requirements and sample data and design.

[bdybldr]

ok...try setting the box type of the colum titles that you want to hide to "none" also.

[bdybldr]

See my previous post above re: creating three queries and three reports.  That will solve your issue.

[satyagorantla]

Thanks.Creating conditional block with 3 lists solved the problem. (Sorry for the late reply)

[bdybldr]

Glad your issue is solved.  Please close this thread.  See board rules.