locked
Using URL Parameter in report RRS feed

  • Question

  • User1545235275 posted

    I'm passing the parameter user=m88002 in the url below

    http://xxx/Reports/Pages/Report.aspx?ItemPath=%2fInfo_Develop%2fOP+Referrals+Monthly+Summary+%28all+referrals%29&user=m88002

    Can you please tell me how I can use this in my query, something like:

    WHERE userid = @url.userid

    Can anyone please help me get it working?

    thankyou

    Thursday, June 16, 2011 9:03 AM

Answers

  • User-830595639 posted

    Hi,

    I hope that you are calling your report by giving the URL as you explained earlier, and as per my knowledge there should not be any problem in that. If you want that the dropdown having the value which you are passing in the URL, then you need to assign a default value to the parameter while designing the Report and when you call this Report the default value will be overridden by the value from URL.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 20, 2011 2:49 AM
  • User1545235275 posted

    Thanks very much for your reply, I have it working now, I changed the text box and its working fine.

    Thanks again.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 21, 2011 11:09 AM

All replies

  • User1266497125 posted

    If you are using a stored procedure to extract the data in order to populate the data source for report then you can pass user to the procedure as a parameter.

    For example:

    CREATE PROCEDURE myProc
    (
       @user     VARCHAR(25)
    )
    AS
    SELECT col1, col2, col3, col4
    FROM myTable
    WHERE user = @user;
    GO

    On the ASP.NET side you could do something like this:

    using (SqlConnection cn = new SqlConnection())
    {
        cn.ConnectionString = "your connection string goes here";
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cn;
        cmd.CommandText = "myProc";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@user", Request.QueryString["user"]);
    
        cn.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        DataTable table = new DataTable();
    
        if (reader.HasRows)
            table.Load(reader);
    
        reader.Close();
        cn.Close();
    
        yourReport.DataSource = table; // Or some other property
        yourReport.DataBind(); // if DataBind exists and is needed
    }

    I hope this helps solve your problem,

    Huske

    Thursday, June 16, 2011 9:11 AM
  • User1545235275 posted

    Thanks for the reply, can you just reference the value in the query itself that makes the report?

    Like an asp webpage making a query?

    WHERE     (LU.Users.Username = @USER)

    Thursday, June 16, 2011 10:24 AM
  • User1266497125 posted

    Yes you can. You have to say:

    string sql = "SELECT your_list_of_columns FROM table WHERE (LU.Users.Username = '" + Request.QueryString["user"] + "'";

    Hope this helps,

    Huske

    Thursday, June 16, 2011 10:29 AM
  • User1545235275 posted

    Thanks again for your reply, I must be getting the wrong end of the stick.

    I have a basic login page in the root of my webserver when a user enters their details it passes their name as a parameter in the url to the report server. This works fine.

    However, When I look in the reportserver, i have loads of files, when I open the .rdl file, all I see is a query. I dont see any page code like you do with an asp webpage?

    I dont generate the sql from a string I just use the query builder.

    Can you please help, I'm very new to reports.

    Thursday, June 16, 2011 10:51 AM
  • User-830595639 posted

    Hi, Matthisco

    You just need to use the parameter name which you are passing in your URL. For example, if your are passing in URL like UserID="ABC" then go to your .rdl file and then go to your query part and add the following where condition there.

    Where yourcolumnname = @UserID

    I hope this will help you.

    Friday, June 17, 2011 3:34 AM
  • User1545235275 posted

    Thanks very much for your reply.

    I've tried that but the report doesnt load.

    Here is my rdl file can you please help? Am I referencing the parameter correctly?

          <Query>
            <DataSourceName>xxx</DataSourceName>
            <CommandText>SELECT     opr.Source_of_Referral_Outpatients, source.Description, gps.PBC_Cluster_Code, COUNT(opr.Year) AS Referrals, opr.Year_Month, 
                          gps.GP_Practice_Code, LU.Users.Username, gps.GP_Practice_Name
    FROM         (SELECT     Source_of_Referral_Outpatients, Year, Year_Month, Practice_Code_of_Registered_GP
                           FROM          OP.Referrals_Dataset
                           WHERE      (Provider_Code LIKE 'rxk%')) AS opr INNER JOIN
                          LU.Users ON opr.Practice_Code_of_Registered_GP = LU.Users.GP_Practice_Code LEFT OUTER JOIN
                          LU.SandwellPCT_GP_Practices AS gps ON opr.Practice_Code_of_Registered_GP = gps.GP_Practice_Code LEFT OUTER JOIN
                          LU.OP_Source_of_Referral AS source ON opr.Source_of_Referral_Outpatients = source.Code
    WHERE     (LU.Users.GP_Practice_Code = @USER)
    GROUP BY opr.Source_of_Referral_Outpatients, source.Description, gps.PBC_Cluster_Code, opr.Year_Month, gps.GP_Practice_Code, LU.Users.Username, 
                          gps.GP_Practice_Name
    ORDER BY opr.Year_Month</CommandText>
            <QueryParameters>
              <QueryParameter Name="@USER">
                <Value>=Parameters!USER.Value</Value>
              </QueryParameter>
            </QueryParameters>
          </Query>
    Friday, June 17, 2011 5:23 AM
  • User-830595639 posted

    I am not seeing any error in the code given by you. But whats happening if you are running the report from URL, is it returning any error or not returning any rows. Also can you please try to put this parameter in a text box to see what is the value coming into it.

    Friday, June 17, 2011 5:48 AM
  • User1545235275 posted

    THanks for your reply.

    Nothing happens, I just get a white screen where the report should be and a drop down box, where you select the user parameter. I would like to bypass this dro pdown and pass the value in the url instead.

    Friday, June 17, 2011 6:28 AM
  • User2105670541 posted

    just create a reportparameter in your reprotviewer page and pass the querystring values there, im assuming you're parameter name is param1 which you want to bypass, your reportviewer page code would be something like:

    reportparameter[] _reportparameter=new reportparameter{"param1",Request.QueryString["UserCode"].tostring()};
    
    reportviewer1.serverreport.setparameter(_reportparameter);
    
    now you'll bypass the thing you wants, the code is written directly here, so it may contain some case mistakes, or a syntax :P
    hope it will help
    Friday, June 17, 2011 10:30 AM
  • User-830595639 posted

    Hi,

    I hope that you are calling your report by giving the URL as you explained earlier, and as per my knowledge there should not be any problem in that. If you want that the dropdown having the value which you are passing in the URL, then you need to assign a default value to the parameter while designing the Report and when you call this Report the default value will be overridden by the value from URL.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 20, 2011 2:49 AM
  • User1545235275 posted

    Thanks very much for your reply, I have it working now, I changed the text box and its working fine.

    Thanks again.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 21, 2011 11:09 AM