calculation with a user entered parameter in it

[ptedeski]

we have a calculation
CASE
WHEN ( DateName(m,[Transaction Date]) = (Period Ending([Transaction Date]))) THEN [Amount]
ELSE 0
END

(Period Ending([Transaction Date]) is a filter that the user will enter a value. We get an error, every way we try to enter the parameter. If we use
CASE
WHEN ( DateName(m,[Transaction Date]) = 'June')
THEN [Amount]
ELSE 0
END
the report runs. What is correct statement to include the user input?

[admin]

Hi

I'm not sure where you try to enter the formula - it could be either as a SQL extra column or in a Dynamic SQL view.

What you want to accomplish - if I understand you correctly, is only possible in the Dynamic ai SQL view, and you need to use the @Par@ notation.

Using @Par@ allows you to replace part of the SQL with user input, and works pretty much anywhere in the SQL.

To match your problem as close as possible, I made a small table, and a dynamic view:

Code: Select all

SELECT *,
DateName(m,[TransDate] )MName,
CASE
WHEN ( DateName(m,[TransDate]) = 'July')
THEN [Amount]
ELSE 0
END NewAmt
FROM mytable

I added a column name to the CASE - thhe MName column is just for test reasons and is unnecessary.

Now, I want to add my parameter
I click the @PAR@ button and can enter the Caption and Default for up to 10 parameters - I enter TDate as Caption and 'July' as default (for test) for @PAR1@

Now I need to modify the view to use the parameter:
I change 'July' to '@PAR1@':

Code: Select all

SELECT *,
DateName(m,[TransDate] )MName,
CASE
WHEN ( DateName(m,[TransDate]) = '@PAR1@')
THEN [Amount]
ELSE 0
END NewAmt
FROM mytable

Now when I build a report on this view, I automatically get a filter box for @PAR1@.

I hope that helps -otherwise please ask again.

Bo Andersen

we have a calculation
CASE
WHEN ( DateName(m,[Transaction Date]) = (Period Ending([Transaction Date]))) THEN [Amount]
ELSE 0
END

(Period Ending([Transaction Date]) is a filter that the user will enter a value. We get an error, every way we try to enter the parameter. If we use
CASE
WHEN ( DateName(m,[Transaction Date]) = 'June')
THEN [Amount]
ELSE 0
END
the report runs. What is correct statement to include the user input?