Hello,
I have a business in every state.
Each state falls in to 1 of 3 sections.
Each section has a different % to multiply by Sales for the month.
So I have created a List Report.
Business Name, State, Monthly Sales, And 3 query calculations (1 for each section)
Each Query Calculation is set up the same:
CASE
WHEN([State] in (That sections States in here))
THEN([Monthly Sales]*that sections %)
ELSE(0)
END
The problem is the ELSE. I don't want it to show a zero if they aren't in that sections group of states. I want it to stay blank if they aren't in that sections group of states and only show a zero amount if they ARE in that section and just don't have any sales. Report Builder doesn't seem to like that. If I make the ELSE('') or ELSE() it treats that as text and will not mix between numbers and text.
Any suggestions?
Have you tried the following
CASE
WHEN([State] in (That sections States in here))
THEN([Monthly Sales]*that sections %)
ELSE(NULL)
END
Perfect! :) Thank you SO much pricter!