Confusing problem with passing Parameters

[Jason]

  I have a report which has some table controls on it. This report also has embedded subreports on it. I have the requirement of taking some info about the user(username) from my web app and passing it as a parameter to my telerik report. I have watched the video for paramterized queries (here) and am trying to model after this. It works....only sort of... 
   What is weird is that the parameters are passed just fine to my subreports . I use the NeedDataSource Event to do so. For some reason, when I try to pass the parameters to the queries (I am calling stored procs) that populate the table controls(again these controls are directly on the report),  these values get lost somehow.   Very weird, as I would think the the exact opposite would potentially be the case.
  I turned sql profiler on and I can see the queries running for the table controls with a blank string " " for the parameters that are suppose to be passed. Even weirder is if I make the paramers visible in the automatic UI, (while making absolutely certain that no value has been hardcoded for them in the properties window of Visual Studio, Value field is blank) I do in fact see the values in the automatic UI that are supposed to be passed to the report.  I tried using the different events(NeedDataSource, ItemDataBound, etc) to bind the table controls but to no avail.
   Based on what I'm experiencing I would think there is something about the report life cycle that I am currently not understanding, but I am not sure. Below are my code snippets with comments. If you can offer any direction I would be greatly appreciative. -Jason

//this from my .aspx codebehind


public partial class reporting_rep_invprodby_newlook2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
            string username = User.Identity.Name;
            MasterReport report1 = new MasterReport();
            ReportViewer1.Report = report1;
            //setting the value of Param1 in the line below
            report1.Param1 = username;
     }
 ------------------------------------------------------------------------------

//this from the telerik report codebehind


// Here is a sample query where the value for @ousername_vc
//(this.ReportParameters["ousername_vc"].Value) gets lost

SqlConnection connSomsys = new SqlConnection(@"Server=cmdivst004\Jason08;Integrated Security=false;Database=QuoteDB; Persist Security Info=True;User ID=gggg;Password=gggg");

SqlCommand selectLastYearTot;
selectLastYearTot = new SqlCommand("sprocgetOrdertotlastyear", connSomsys);

selectLastYearTot.CommandType = CommandType.StoredProcedure;

selectLastYearTot.Parameters.AddWithValue("@ousername_vc", this.ReportParameters["ousername_vc"].Value);

adapter3.Fill(dataSet3);
this.table3.DataSource = dataSet3;

//this is for one of the subreports in the NeedDataSource Event
//for the subreport, it works fine

SqlConnection connSomsys = new SqlConnection(@"Server=cmdivst004\Jason08;Integrated Security=false;Database=QuoteDB; Persist Security Info=True;User ID=cmdiapp;Password=adiadmin");
Telerik.Reporting.Processing.SubReport report =(Telerik.Reporting.Processing.SubReport)sender;

SqlCommand selectCommand;
selectCommand = new SqlCommand("sprocgetaccountingorders", connSomsys);
selectCommand.CommandType = CommandType.StoredProcedure;
//works fine right here line below
selectCommand.Parameters.AddWithValue("@ousername_vc", this.ReportParameters["ousername_vc"].Value);
SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
report.InnerReport.DataSource = dataSet;
connSomsys.Dispose();

//Here is the property I created to take in the value of the username
//much like the video
   public string Param1
        {

            get
            {
                return (string)this.ReportParameters["ousername_vc"].Value;
            }
            set
            {
                this.ReportParameters["ousername_vc"].Value = value;
            }
        }

[Steve]

Hi Jason,

Since you have reviewed the video for parameterized query already, what should be noted as conclusion from this thread is that you cannot set values of definition report objects, which is what you're trying to do. Actually the purpose of the video as the name suggests is to show you how to filter on database level and thus use your own UI for setting parameters and not the built-in filtering + report parameters.
If you need to set a value for our built-in parameter, you can easily do this directly from the viewer:

  MasterReport report1 = new MasterReport();
  report1.ReportParameters["ousername_vc"].Value = query;

Sincerely yours,
Steve
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Jason]

//from the .aspx codebehind

            string username = User.Identity.Name;
            MasterReport report1 = new MasterReport();
            ReportViewer1.Report = report1;
            report1.Param1 = username;
            report1.ReportParameters["ousername_vc"].Value = usernam



//from telerik report codebehind

            SqlCommand selectCurrentYearTot;
            selectCurrentYearTot = new SqlCommand("sprocgetOrdertotcurrent", connSomsys);
            selectCurrentYearTot.CommandType = CommandType.StoredProcedure;

//Value of @ousername_vc", this.ReportParameters["ousername_vc"].Value gets lost below

            selectCurrentYearTot.Parameters.AddWithValue("@ousername_vc", this.ReportParameters["ousername_vc"].Value);



            SqlDataAdapter adapter2 = new SqlDataAdapter(selectCurrentYearTot);
            DataSet dataSet2 = new DataSet();
            adapter2.Fill(dataSet2);
            this.table2.DataSource = dataSet2;

Dear Steve,
   I implemented your suggestion and I still get the same behavior. The parameters are populated in the automatic UI but for some reason  the values get lost when I try to pass them to the stored proc on the back end. Be that as it may, you are correct in the sense that I don't care about populating these really. I just need to pass them to my queries. It is here where these values get lost.
       In reference to the distinction you made about the use of the video, I don't understand why it would matter if I am taking the value of a variable at the UI layer as opposed to say a text box value or a drop down value at the ui layer. Thats all I am doing which is different really. See the snippet below. I am just taking for the value of the username variable which is getting a security priniciple (i.e user info) Bottom line is I need to pass these values to my queries that are called from within the telerik report. Any help would be greatly appreciated.

Jason

[Milen | Product Manager @DX]

Hello Jason,

There is only one thing that we are not sure about from the provided snippets: where (in which method) the code that executes the SQL and populates the data is located?

Here is what I suspect: the quoted code that reads the parameter value and executes the SQL is located in the constructor of the report, but at this time the report parameter is still not initialized. You initialize the parameters value two lines after the report instantiation. It is easy to make this flow mistake as the report wizard places the data pulling in the same place. But note that the report wizard creates a data adapter with parameterless select command.

If that is the case, you have several refactor options: leave the code where it is and pass the value in the reports constructor. Or leave the constructor parameterless, set the datasource to null in it, so that NeedDataSource is triggered, and make the SQL calls in NeedDataSource handler. Or make the SQL calls in the setter of the Param1 property.

If this is not the case, please open a support ticket and attach a simple report demonstrating the problematic behavior. We will be happy to review it.
 

Kind regards,
Milen
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Sebastian]

Hi,

is there a solution for this problem at the moment. We are facing exactly the same problem right now using Telerik Reporting Q3 2010.

The Silverlight client passes Report parameters as follows

XAML code

<reporting:ReportViewer Grid.Row="1" x:Name="ReportViewer" ReportServiceUri="../Services/ReportingService.svc"
                                                        Report="Reports.Examples.AtomiumReport" VerticalContentAlignment="Top"></reporting:ReportViewer>

XAML Code Behind

ReportViewerModel rvm = ((ReportViewerModel)ReportViewer.DataContext);
List<Parameter> parameterList = new List<Parameter>();
Parameter parameter = new Parameter();
parameter.Name = "ShowLegend";
parameter.Type = typeof(string).FullName;
parameter.Value = value; // The value is either "true" or "false"
parameterList.Add(parameter);
rvm.Parameters = parameterList;
rvm.ApplyReportParametersCommand.Execute(null);

The Report Parameter is definied using the Designer interface 'Right-Click, Report Parameter, Add new ...'

Accessing the Report Parameter using eg 'Conditional Formatting' the parameter is correctly used. If the Silverlight client passes 'true' the Legend Pane is visible, if 'false is passed, the Legend Pane in the Report is not visible.

If we try to access the report parameter within the CodeBehind of the Report using 

if(ReportParameters.Contains("ShowLegend"))
{
 _showLegend = Boolean.Parse(ReportParameters["ShowLegend"].Value.ToString());
}

the _showLegend value is always set to the value configured using the Value-Member in the 'Designer Interface'.

The code above is called within the constructor of the Report class.

If this is an initialization problem, which lifecycle state of the server side report instance is the best to read the parameters currently passed ?

Kind regards,
Sebastian

[Sebastian]

Sorry guys,

just understood to use NeedDataSource() right after writing the last post.

This works as expected and we'll get all parameters with the correct values there.


Kind regards
Sebastian