Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sis-sql_2015
Starting Member
4 Posts |
 Posted - 2014-12-05 : 17:48:55
|
Hello. I’m building a report for a school district, in which it will display students who have been no-shows to any particular class. I have the view and query done so that it pulls the exact data I need. The problem, however, is that I want to make things as easy for the users as possible, and simply have a drop-down box for the school term (1,2,F,S,etc) that will set the date ranges ( @TermStart & @TermEnd). So, let’s say a truancy officer wants to look at which students have been no-shows for term 2, they would select 2 from the drop-down menu, which would then set the query parameters to 10/13/2014 (@TermStart) and 12/19/2014 (@TermEnd).D1 and D2 are the term start and end dates, respectively. SC is the school and TM is the term. I won’t have an option for school at this time, as I’m building this for a single school site, so it’ll be set explicitly in my query. quote:
SELECT SC, D1, D2, TMFROM TRMWHERE (SC = 35) AND (TM = @term)
@TermStart and @TermEnd are both hidden, as I’ll only be using those in the query. I would like the drop-down box to display the available terms (from GetTermDates), which I can hard-code for now, if need be. When the user selects a term, I want the remaining parameters to be set and the report to run. I just have no idea how to get this done. My @term parameter's Available Values is set to...quote:
Dataset: GetTermDatesValue Field: TMLabel Field: TM
I currently have the two parameters (@TermStart & @TermEnd) set in DataSet1 in the following manner:quote:
Paramters >Parameter Name: @TermStartParameter Value <<Expr>> (=First(Fields!D1.Value, "GetTermDates")Parameter Name: @TermEndParameter Value <<Expr>> (=First(Fields!D2.Value, "GetTermDates")
I get the following error, which doesn’t surprise me as I’m quite new to all of this and primarily learning as I go.quote:
An error occurred during local report processing.The definition of the report ‘/No_Shows_By_Class’ is invalid.The expression used for the parameter ‘@TermStart’ in the dataset ‘@TermStart’ refers to a field. Fields cannot be used in query parameter expressions.
I know I’m probably way off here. Any suggestions? I’m willing to research the topic more if someone can point me in the right direction. I’ve created reprots with cascading parameters before, but not ones that will populate hard-coded parameter values into queries based on another parameter. This isn’t mission-critical, by any means. I have a bit of free time as we’re nearing the end of the term, so I thought I’d work on some projects. Thanks in advance!! |
|
|
sis-sql_2015
Starting Member
4 Posts |
Posted - 2014-12-05 : 18:39:29
|
| Never mind :) Thinking through the problem, and writing about it, cleared my head. I was trying to populate the @term parameter with a query that was dependent upon the @term parameter...circular dependency, ugh!I've got it working properly now.Thanks for anyone that took time to read through this! |
 |
|
|
|
|
|
|
|
Visual Studio SQL Report Parameter Problems