January 17, 2008 at 8:43 am
Hello all. I have minimal SQL experience - essentially only what I have taught myself in order to crank out some reports at work.
Our ERP program creates a table that assigns a sequential number to inventory transactions. I can filter ALL the transactions by order number and DATE to generate the report I need. From time to time, however, the stockroom issues material more than once against the same order on the same day ... maybe once in the morning and twice in the afternoon. And I need to generate separate reports - hard copy is filed away due to QA requirements.
In these instances, I can view the HistoryPickKey field by date and then set the criteria to something like BETWEEN 582626 AND 582635 and it will return all the transactions I need for my report. And that works just fine, but isn't so good for the user in the stockroom.
What I would like to do is:
a. generate a quick report that shows all the HistoryPickKey values for the day - this is already in place and I know how to crank this one out
b. then generate a 2nd report where the user enters some sort of parameter(?) that specifies a FROM and TO value that gets dumped into that BETWEEN x AND y query.
Does that make sense?
Field is HistoryPickKey
Table is FS_HistoryPick
If someone could guide me through this it would be a great help. Again, SQL experience is limited - not sure how this task fell on ME. And feel free to ask anything you need to know about 16th c. poetry!
Thanks in advance.
Geoff
January 17, 2008 at 9:10 am
Sure - go into the report you want to parameterize, and click on the data view.
You should have a SQL statement that looks something like:
select * from mytable
Modify the query to accept parameters, as in
Select *
from mytable
where HistoryPickKey between @startingPickKey and @endingPickKey
Once you've added those - go into Report, Report Parameters, and adjust the data type and prompt (data type should be int from the looks of it; prompt would be whatever would be helpful to them to know what to put in).
Deploy the report, and give it a whirl - should prompt you for starting and ending values and then generate the report.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 17, 2008 at 10:21 am
Matt,
You're a genius - it works like a charm. I was kinda stumbling through the parameters yesterday, but wasn't sure how to put it all together.
Thank you for your quick and most helpful response.
Cheers.
Geoff
January 17, 2008 at 10:24 am
Not a genius - just another hammerer who's been playing with the hammer a little while longer:).
Glad it worked out for ya!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply