calculation with a user entered parameter in it

Technical support

Moderators: Bob Cergol, Data Access, Cintac

calculation with a user entered parameter in it

Postby ptedeski » Wed Oct 21, 2009 4:11 pm

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?
ptedeski
 
Posts: 1
Joined: Wed Oct 21, 2009 3:55 pm
Location: Horizon Information Systems

Re: calculation with a user entered parameter in it

Postby admin » Tue Oct 27, 2009 10:13 am

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

ptedeski wrote: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?
User avatar
admin
Certified Dynamic AI Professional
 
Posts: 177
Joined: Thu Jan 01, 1970 1:00 am
Location: Copenhagen, Denmark


Return to Technical Support

cron