Jump to content
  • 0

Search Form For Calculated Field Parameters


Go to solution Solved by kpcollier,

Question

I'm trying to use a search form solely for setting a condition in a calculated field in a tabular report.

I have a Job_Table that houses information about our Jobs (Job_Number is the ID for this table). I have a TimeEntry_Table that houses Job_ID, Date, And Hours for our employee timesheet. The datapage uses Job_Table as the source.

I have a calculated field that queries to the TimeEntry_Table and sums up the Hours for each Job. This works. However, now I am trying to add a date range to this. I've been trying to use two virtual fields in the search form to set parameters. If the user leaves these blank, it'll just sum up all of the hours matching the Job_ID. But, if they enter in dates in the two virtual fields, I am trying to use these in my Calculated Field. 

CASE WHEN [@DateFrom] IS NOT BLANK AND [@DateTo] IS NOT BLANK

SELECT Sum(Hours) FROM TimeEntry_Table WHERE Job_ID = Job_Number AND Date BETWEEN [@DateFrom] AND [@DateTo]

ELSE

SELECT Sum(Hours) FROM TimeEntry_Table WHERE Job_ID = Job_Number

END

To start off the CASE statement, I'm not sure if I should use the parameter name or cbViritualParam. It seemed neither worked. I am not sure if you can check to see if parameters or virtual fields are empty from the search form. Next, you can see the BETWEEN condition. This is where I am trying to enter in the dates from the virtual fields to filter the number of records that are summed up. 

Nothing is showing up with my workflow.

Any help is appreciated. 

Link to post
Share on other sites

2 answers to this question

Recommended Posts

  • 0
  • Solution

Adding parenthesis to the SELECT statements made this work.

CASE WHEN ([@calcfield:5] != ' ') AND ([@calcfield:6] != ' ')
THEN
(SELECT Sum(Hours) FROM TimeEntry_Table WHERE Job_ID = [@field:Job_Number] AND Date BETWEEN '[@DateFrom]' AND '[@DateTo]')
ELSE
(SELECT Sum(Hours) FROM TimeEntry_Table WHERE Job_ID = [@field:Job_Number])
END

 

Link to post
Share on other sites
  • 0

I was missing the single quotes on the parameters. 

SELECT Sum(Hours) FROM TimeEntry_Table WHERE Job_ID = Job_Number AND Date BETWEEN '[@DateFrom]' AND '[@DateTo]'

Now, both SELECT statements work. If there are no parameters, it sums up all hours related to that job. If there are date parameters, it will only sum up the hours between those dates.

The problem is now getting the CASE statement to work. I can't seem to get the formula to run when nothing is entered into the date fields. I can't figure out how to check against blank parameters. I have tried adding the parameters to separate calc fields, and then running the CASE statement if those calc fields are blank or not, and I still can't get it to run. 

CASE WHEN ([@calcfield:5] is not blank) AND ([@calcfield:6] is not blank)

SELECT Sum(Hours) FROM TimeEntry_Table WHERE Job_ID = Job_Number AND Date BETWEEN '[@DateFrom]' AND '[@DateTo]'

ELSE 

SELECT Sum(Hours) FROM TimeEntry_Table WHERE Job_ID = Job_Number

END

 

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...