How to Send username or something to the stored procedure

(12 posts) (2 voices)
  1. Derek, Member

    hi support
    report structure listed below:
    report-1 in Customer-a call sp_DBR_report-1-a
    report-1 in Customer-b call sp_DBR-report-1-b

    There is only different with Customer between sp_DBR-report-1-a and sp_DBR-report-1-b
    ,now i want to migrate two procedure to one called sp_DBR-report-1,because the number of customer will raised up quickly,i can't image if i create a separately stored procedure for each customer, So my question is: how to get the CustomerID(something like this) from mydbr to stored procedure,if i use parameter of stored procedure,Any Customer can look others data via entering other customer's ID(or something like this),so i can't use it ,because i don't to let customer-a view customer-b's data ,It is a secure data

    Is there any way to implement my requirement?

  2. myDBR Team, Key Master

    There is no need to create a separate linked report for each customer. You can pass the CustomerID from the report list to the linked report.

    When you list the reports for each customer, please include the CustomerID into the list. If you do not want to show the ID, you can choose to hide it. Once user clicks the report row, you can pass the CustomerID from that row to the linked report.

    myDBR Team

  3. Derek, Member

    hi Support,
    Seems the link report will works, In that way, we stil need to create separately report but each Customer,so my another question is: do we have any way to pass the username(loged into the mydbr) to stored procedure (hidely),then i do not need to create separately report for each customer, i only need to create 3 reports,when a customer click one row(one report), mydbr will
    pass username(login name) to stored procedure,so the procedure can return the result depending on the username,it will be better if it is possible,Could you help to look into this ! we really need this feature.

    Thanks very much!

  4. myDBR Team, Key Master

    To avoid creating multiple reports you need to pas the CustomerID to the linked procedure.

    select 'dbr.hidecolumns', 2;
    select '', 'sp_DBR_linkedReport', 'inReportID=1', 'inCustomerID=3' select ReportName, ReportID, CustomerID
    from myreports;

    the linked report would be form of:

    create procedure sp_DBR_linkedReport( inReportID int, inCustomerID int )

    Alternatively you can pass the login name automatically simply by introducing a 'inLogin varchar(30)' variable to the report. myDBR will automatically fill in the variable with login name.

    myDBR Team

  5. Derek, Member

    The following linked report is now working, there is no arrow button on the right

    delimiter //
    create procedure sp_DBR_customer_entry(inLogin varchar(30))

    select '', 'sp_DBR_film','inFilmID=2';
    select name,group_id from mydbr_groups ;

    delimiter ;

  6. myDBR Team, Key Master

    In order the linked report (sp_DBR_film) to be shown, couple of things needs to be in place:

    1. The report (sp_DBR_film in the example) needs to be attached to the myDBR structure
    2. User needs to have permission to run the report (sp_DBR_film)

    In the example you do not seem to use the inLogin-parameter in the report. Secondly, the report would passing group_id as inFilmID to the linked sp_DBR_film report.

    myDBR Team

  7. Derek, Member

    It works now, the other quesiton is: how to diable the pop menu when you click the arrow button
    i have listed all the reports,and do not need the pop-up menu. it will make user comfortable if there is a way to disable it.

  8. myDBR Team, Key Master

    Good that you got it working.

    The popup is always there so the user sees which linked report (s)he is about to run. With the popup, the user interaction is the same whether there is one or multiple reports linked.

    myDBR Team

  9. Derek, Member

    Hi support
    Now i have 3 report for each of 50 customer
    report-a (3 parameter)
    report-b (2 parameter)
    report-c (1 parameter)

    Now i created an procedure as entry point,call proc-entry
    in this proc-entry, i listed all 3 report 1 link report
    and pass reportid and customer id to :q

    select '', 'sp_DBR_linkedReport','inReportID=2','inCustomer=3';
    select 'report-a' as Report,1 as ReportID,inLogin as Customer union
    select 'report-b' as Report,2 as ReportID,inLogin as Customer union
    select 'report-c' as Report,3 as ReportID,inLogin as Customer ;

    in procedure sp_DBR_linkedReport,i need to call separate procedure for report-a
    or report-b or report-c depend on reportID
    if reportid=1
    call sp_DBR_cust_report-a(1,5,5);
    if reportid=2
    call sp_DBR_cust_report-b('derek','wang');
    if reportid=3
    call sp_DBR_cust_report-a(1);

    My question is:
    How to pass various parameter? it is no way to enter parameter in entry point (proc-entry), because there are various parameter for different report,if i use
    the linked report to generate all data, it is possible to entering parameter,but
    how to hide customerID parameter ?i don't want customerA to see customerB's data, if the linkedReport is a proxy,then linkedReport to call really report procedure,then how to let user entering parameters ?

  10. myDBR Team, Key Master

    Couple of things:

    1. What are the parameters needed for report-a, b and c? Where do they come from?
    2. You do not need to pass inLogin as a linked parameter, you can simply introduce it in sp_DBR_linkedReport as a parameter and myDBR will take care of it.
    3. Could you not just show report-a, b, and c as direct reports since only thing proc-entry and linkedReport seem to do is to guide the customer to the reports a,b and c.

    myDBR Team

  11. Derek, Member

    1.For example:
    report-a has 3 parameter, which is 1.reportid,2 customerID, 3 duration
    report-b has 2 parameter: which is 1.firstname,2 lastname,3,customerID
    reoprt-c has 1 parameter: which is duraiton,customerID

    duration,fistname,lastname which is selected by customer
    reportid,customerID is just for identify the report and customer(which should not selected(visible) by customer.

    3. how should i do?could you give example to me ?

  12. myDBR Team, Key Master

    Could be that we missed something but it looks like this:

    - ReportID seems to be always 1 in your example so it does not need to be a parameter at all
    - CustomerID is the same as the user login id. This can be handled by myDBR using inLogin parameter.
    - Duration seems to be the only parameter user will need to select

    - firstname and lastname are user selectable (could be a combined popup parameter in myDBR)
    - customerID is the same as the user login id (-> inLogin)

    - Duration is a user selectable parameter
    - customerID is the same as the user login id (-> inLogin)

    myDBR Team


You must log in to post.