Dataset parameters question.

I have a report, which contains student details and the courses to
which the student is registered.
I have 2 datasets, one which retrieves the student data, and another
paramaterized report which retrieves all courses for this student.
What I need to do is set this parameter to be the student_id of the
current student.
e.g. I want the report to be displayed as:
Student Details
Student ID: S1000
Name: AN Other Address: 1 22nd Street
Courses:
Math
Chemistry
Physics
Spanish
I can select the correct dataset and field in the textbox for course
name, but the report keeps asking me to enter a student id. I want it
to use the student id of the current student.
Does anyone know how I go about this?
Appreciate any help.Donâ't you use a student id parameter to filter the student? This one should
be used to filter courses.
If youâ're not filtering the students, you may have more than one student. In
this case you can use a single dataset that joins student and courses, and
group by students.
"DJ" wrote:
> I have a report, which contains student details and the courses to
> which the student is registered.
> I have 2 datasets, one which retrieves the student data, and another
> paramaterized report which retrieves all courses for this student.
> What I need to do is set this parameter to be the student_id of the
> current student.
> e.g. I want the report to be displayed as:
> Student Details
> Student ID: S1000
> Name: AN Other Address: 1 22nd Street
> Courses:
> Math
> Chemistry
> Physics
> Spanish
> I can select the correct dataset and field in the textbox for course
> name, but the report keeps asking me to enter a student id. I want it
> to use the student id of the current student.
> Does anyone know how I go about this?
> Appreciate any help.
>|||IF USING A SUBREPORT
Your Student data is in your main report, and your Course data (including
StudentID) is in your subreport. Your subreport is placed inside the list
(or table or whatever) that iterates on the student data.
In the main report, right-click on the subreport and select Properties.
Then go to the Parameters tab and map the subreport parameters to the
appropriate fields.
IF NOT USING A SUBREPORT
You might be able to do this without a subreport. Just join the course
table to the student table in your dataset. Put everything inside a table
(or nested lists if it's a freeform report). You can put rectangles and
lists inside table cells, which is cool. Map everything to the same dataset.
Then set outer grouping levels at the student level, and inner grouping or
details at the course level.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"DJ" <superdj@.hotmail.com> wrote in message
news:72abb98.0501070456.d13dd9d@.posting.google.com...
>I have a report, which contains student details and the courses to
> which the student is registered.
> I have 2 datasets, one which retrieves the student data, and another
> paramaterized report which retrieves all courses for this student.
> What I need to do is set this parameter to be the student_id of the
> current student.
> e.g. I want the report to be displayed as:
> Student Details
> Student ID: S1000
> Name: AN Other Address: 1 22nd Street
> Courses:
> Math
> Chemistry
> Physics
> Spanish
> I can select the correct dataset and field in the textbox for course
> name, but the report keeps asking me to enter a student id. I want it
> to use the student id of the current student.
> Does anyone know how I go about this?
> Appreciate any help.|||I have tried this using a parameterized sub report, and set the
parameter to be the student id. The dataset that retrieves the student
data is simply 'select * from students' so this should bring back all
the students and the sub report should bring back all the courses for
these students.
But still it asks me to enter a student id!
DJ wrote:
> I have a report, which contains student details and the courses to
> which the student is registered.
> I have 2 datasets, one which retrieves the student data, and another
> paramaterized report which retrieves all courses for this student.
> What I need to do is set this parameter to be the student_id of the
> current student.
> e.g. I want the report to be displayed as:
> Student Details
> Student ID: S1000
> Name: AN Other Address: 1 22nd Street
> Courses:
> Math
> Chemistry
> Physics
> Spanish
> I can select the correct dataset and field in the textbox for course
> name, but the report keeps asking me to enter a student id. I want it
> to use the student id of the current student.
> Does anyone know how I go about this?
> Appreciate any help.|||In your parent report parameters, check for a student_id field. You might
have specified one earlier that needs to be deleted. The data source for
your subreport should be something like "select * from courses where
student_id = @.student_id". Then you can link the parameters in the parent
and child reports.
On the other hand, you could do the whole thing in a single report, with a
data source like "select * from students inner join courses on
students.student_id = courses.student_id". Then you put the fields in a
table with student info in the header rows, and group info in the detail
rows.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
<superdj@.hotmail.com> wrote in message
news:1105349201.298466.198340@.c13g2000cwb.googlegroups.com...
>I have tried this using a parameterized sub report, and set the
> parameter to be the student id. The dataset that retrieves the student
> data is simply 'select * from students' so this should bring back all
> the students and the sub report should bring back all the courses for
> these students.
> But still it asks me to enter a student id!
>
> DJ wrote:
>> I have a report, which contains student details and the courses to
>> which the student is registered.
>> I have 2 datasets, one which retrieves the student data, and another
>> paramaterized report which retrieves all courses for this student.
>> What I need to do is set this parameter to be the student_id of the
>> current student.
>> e.g. I want the report to be displayed as:
>> Student Details
>> Student ID: S1000
>> Name: AN Other Address: 1 22nd Street
>> Courses:
>> Math
>> Chemistry
>> Physics
>> Spanish
>> I can select the correct dataset and field in the textbox for course
>> name, but the report keeps asking me to enter a student id. I want it
>> to use the student id of the current student.
>> Does anyone know how I go about this?
>> Appreciate any help.
>

Dataset error when deployed to Report Manager

Hi
I've got a custom assembly which performs validation on parameters
prior to submitting the query to the database. My dataset which
retrieves the report data is in Text format and calls an appropriate
validation method on free-text parameters, such as dates and numbers.
When validation passes, the code returns a string formatted for use in
the query, and when it fails it returns a string value that the stored
procedure recognises as a dummy value and returns without SELECTing any
data. My custom assembly sets a user-friendly error message as a
property, which is displayed on the report in place of any data.
I've designed a number of reports using this method and it works well
in the Designer environment. However, I've deployed a few of them to
Report Manager and am getting the following error when pressing "View
Report":
An error has occurred during report processing. (rsProcessingAborted)
Cannot set the command text for data set 'Report'.
(rsErrorSettingCommandText)
Error during processing of the CommandText expression of dataset
'Report'. (rsQueryCommandTextProcessingError)
An example of one of the simple Datasets is:
="EXEC spAgentSettlementsDetailedRpt " &
Code.Validator.ResetValidator &
"@.p_StartDate_IN = '" +
Code.Validator.ValidateStartDate(Parameters!p_StartDate_IN.Value) + "',
" &
"@.p_EndDate_IN= '" +
Code.Validator.ValidateEndDate(Parameters!p_StartDate_IN.Value,
Parameters!p_EndDate_IN.Value) + "', " &
"@.p_AgentID_IN = " + Parameters!p_AgentID_IN.Value.ToString() + ", "
&
"@.p_WS_Code_IN = " +
Code.Validator.ValidateWSCode(Parameters!p_WS_Code_IN.Value,
Parameters!p_AgentID_IN.Value, false)
I'm happy to provide the code within the assembly if it would be of any
help in debugging this problem.
In deploying to the server, I have copied the assembly (dll) to the
\bin directory on the server and uploaded the rdl file as per normal.
Any help would be greatly appreciated. Thank you.
AshleyI've discovered what was causing the error... it turned out to be a
security/permissions problem. The validate date methods attempted to
set the culture of the current thread to "en-AU" with the following
statement:
Thread.CurrentThread.CurrentCulture = New CultureInfo("en-AU")
This was to get around the fact that the result of the VB IsDate()
function appears to be dependent upon the culture settings of the
server. It was always returning false for dd/mm/yyyy dates such as
13/1/2005.
The above line of code was causing an exception to be raised in the
assembly and hence was returning garbage to the dataset.
Does anyone know the precise reason that setting the culture of the
thread, once the report is deployed, results in an exception?
Ashley.Manos@.gmail.com wrote:
> Hi
> I've got a custom assembly which performs validation on parameters
> prior to submitting the query to the database. My dataset which
> retrieves the report data is in Text format and calls an appropriate
> validation method on free-text parameters, such as dates and numbers.
> When validation passes, the code returns a string formatted for use in
> the query, and when it fails it returns a string value that the stored
> procedure recognises as a dummy value and returns without SELECTing any
> data. My custom assembly sets a user-friendly error message as a
> property, which is displayed on the report in place of any data.
> I've designed a number of reports using this method and it works well
> in the Designer environment. However, I've deployed a few of them to
> Report Manager and am getting the following error when pressing "View
> Report":
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot set the command text for data set 'Report'.
> (rsErrorSettingCommandText)
> Error during processing of the CommandText expression of dataset
> 'Report'. (rsQueryCommandTextProcessingError)
> An example of one of the simple Datasets is:
> ="EXEC spAgentSettlementsDetailedRpt " &
> Code.Validator.ResetValidator &
> "@.p_StartDate_IN = '" +
> Code.Validator.ValidateStartDate(Parameters!p_StartDate_IN.Value) + "',
> " &
> "@.p_EndDate_IN= '" +
> Code.Validator.ValidateEndDate(Parameters!p_StartDate_IN.Value,
> Parameters!p_EndDate_IN.Value) + "', " &
> "@.p_AgentID_IN = " + Parameters!p_AgentID_IN.Value.ToString() + ", "
> &
> "@.p_WS_Code_IN = " +
> Code.Validator.ValidateWSCode(Parameters!p_WS_Code_IN.Value,
> Parameters!p_AgentID_IN.Value, false)
> I'm happy to provide the code within the assembly if it would be of any
> help in debugging this problem.
> In deploying to the server, I have copied the assembly (dll) to the
> \bin directory on the server and uploaded the rdl file as per normal.
> Any help would be greatly appreciated. Thank you.
> Ashley|||I've discovered what was causing the error... it turned out to be a
security/permissions problem. The validate date methods attempted to
set the culture of the current thread to "en-AU" with the following
statement:
Thread.CurrentThread.CurrentCulture = New CultureInfo("en-AU")
This was to get around the fact that the result of the VB IsDate()
function appears to be dependent upon the culture settings of the
server. It was always returning false for dd/mm/yyyy dates such as
13/1/2005.
The above line of code was causing an exception to be raised in the
assembly and hence was returning garbage to the dataset.
Does anyone know the precise reason that setting the culture of the
thread, once the report is deployed, results in an exception?
> Hi
> I've got a custom assembly which performs validation on parameters
> prior to submitting the query to the database. My dataset which
> retrieves the report data is in Text format and calls an appropriate
> validation method on free-text parameters, such as dates and numbers.
> When validation passes, the code returns a string formatted for use in
> the query, and when it fails it returns a string value that the stored
> procedure recognises as a dummy value and returns without SELECTing any
> data. My custom assembly sets a user-friendly error message as a
> property, which is displayed on the report in place of any data.
> I've designed a number of reports using this method and it works well
> in the Designer environment. However, I've deployed a few of them to
> Report Manager and am getting the following error when pressing "View
> Report":
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot set the command text for data set 'Report'.
> (rsErrorSettingCommandText)
> Error during processing of the CommandText expression of dataset
> 'Report'. (rsQueryCommandTextProcessingError)
> An example of one of the simple Datasets is:
> ="EXEC spAgentSettlementsDetailedRpt " &
> Code.Validator.ResetValidator &
> "@.p_StartDate_IN = '" +
> Code.Validator.ValidateStartDate(Parameters!p_StartDate_IN.Value) + "',
> " &
> "@.p_EndDate_IN= '" +
> Code.Validator.ValidateEndDate(Parameters!p_StartDate_IN.Value,
> Parameters!p_EndDate_IN.Value) + "', " &
> "@.p_AgentID_IN = " + Parameters!p_AgentID_IN.Value.ToString() + ", "
> &
> "@.p_WS_Code_IN = " +
> Code.Validator.ValidateWSCode(Parameters!p_WS_Code_IN.Value,
> Parameters!p_AgentID_IN.Value, false)
> I'm happy to provide the code within the assembly if it would be of any
> help in debugging this problem.
> In deploying to the server, I have copied the assembly (dll) to the
> \bin directory on the server and uploaded the rdl file as per normal.
> Any help would be greatly appreciated. Thank you.
> Ashley