1. 

   gjsmith66, Member

   We have a report that takes three parameters. When called for the main report, we want to prompt the user for the first two parameters. The third parameter would only be used if the report was linked to from another report. We want the suppress prompting for the third parameter. I know we can make it optional, but how can we make it disabled?

   Thanks!
2. 

   myDBR Team, Key Master

   Hi,
   easiest way to do this is to use a simple wrapper report which would just contain the wanted two parameters and the only thing it would do is to call the actual report with all three parameters so that the third parameter uses a null value.

   create procedure sp_DBR_wrapper_report ( a int, b int )
   
   begin
   
   call sp_DBR_actual_report a, b, null;
   
   end

   --
   myDBR Team
3. 

   gjsmith66, Member

   Ok, we can try that I guess, although a bit inelegant. Is this something you can address in a future version please?

   Thanks,
   Gary
4. 

   myDBR Team, Key Master

   We'll put this in as a feature request.

   --
   myDBR Team
5. 

   hpcalaf, Member

   When I try this with MS SQL as the database, The parameter selection does not come up for the missing parameter.

   From the stored procedure I issue

   EXEC sp_DBR_procedure @v1, @v2, @v3, null

   instead of asking for a parameter the report is displayed with the paramerter value as null.
6. 

   myDBR Team, Key Master

   Hi,
   when you do exec inside procedure SQL Server simply executes the procedure with given parameters. This is the normal behavior. The procedure call is not visible outside the calling procedure.

   If you wish to link another report, use the 'dbr.report'-command instead.

   --
   myDBR Team
7. 

   hpcalaf, Member

   I need exactly the same use case that is described in this post, but for SQL Server.

   Basically, based on the choice of a parameter from one report, I would like to branch in the stored procedure to the next report to launch.

   A new link is not desirable in this situation.

   My question is if anyone knows whether the functionality that is described as an answer to the original posters problem in MySQL, can work in some form for SQL Server. Or is there another solution that would work to achieve this functionality in that database framework.

   If the answer is 'No', I'll move on.

   Thanks
8. 

   myDBR Team, Key Master

   myDBR works exactly same way with SQL Server as it does with MySQL (minus the different SQL dialect).

   If you have a procedure you want to branch out to inside the report and that procedure requires parameters (four in your example), you need to get those parameters from somewhere. You can ask the parameters in the original report or derive the parameter data from database. You already had first three parameters sorted out.

   In the original example above the question was quite opposite (how to call same procedure without asking optional parameters).

   --
   myDBR Team
9. 

   hpcalaf, Member

   Is it possible to implement some variation of the solution below in SQL Server?
   SQL Server to my understanding uses "exec" not "call"

   We have a report that takes three parameters. When called for the main report, we want to prompt the user for the first two parameters. The third parameter would only be used if the report was linked to from another report. We want the suppress prompting for the third parameter. I know we can make it optional, but how can we make it disabled?

   Thanks!

   myDBR Team, Key Master

   Hi,
   easiest way to do this is to use a simple wrapper report which would just contain the wanted two parameters and the only thing it would do is to call the actual report with all three parameters so that the third parameter uses a null value.

   view sourceprint?1.create procedure sp_DBR_wrapper_report ( a int, b int )
   2.begin
   3.call sp_DBR_actual_report a, b, null;
   4.end

   --
   myDBR Team
10. 

    myDBR Team, Key Master

    Yes, SQL Server has bit different syntax than MySQL. Other than that the logic is the same.

    Could you explain what you are trying to do? What is the report you have, what is the input and how do you want report to behave? It would be bit easier to give you the example.

    From what we understood your goal was different from the original post's target.

    --
    myDBR Team
11. 

    hpcalaf, Member

    Based on a parameter that is given by the user I want to use the IF or CASE logic within a stored procedure to select a new report. This would work fine except that all the reports have the same number of parameters except for one. I was trying to submit that parameter for that report via 'exec sp_DBR_procedure @v1, @v2, null', hoping that a parameter selection screen would appear for that single missing parameter; which to my reading was the same use case as the original posters question and the solution you provided.

    I've developed a work around using linked reports. However, it would still be nice to have this available as a technique for other direct calls. In my opinion, this would be a very useful feature.
12. 

    myDBR Team, Key Master

    OK,
    as said, when the exec happens inside the procedure, it all happens inside the database server, without any interaction with myDBR. So myDBR cannot have any knowledge about exec ever happening, or that an user parameter would be required. This is simply how stored procedures work, in MySQL, MS SQL Server or in Sybase.

    Then regarding your use case. As said, the original post was quite opposite (how not to ask all parameters). You basically have two options. Either ask the fourth parameter in original report and make it optional, or use linked report where user can then give the missing parameter if user selects the report where the exra parameter is required. Alternatively, just create two reports to start from, one with three parameters and the second with four.

    Without knowing the actual use case it is bit hard to make more precise answer.

    HTH,
    --
    myDBR Team
13. 

    maron, Member

    Hello.

    Let me explain a typical use case a bit further - as I'm having this come up frequently.

    We would probably have to create 30-40 wrapper reports to clean this up in our case, which easily introduces errors or issues.

    We have many reports relying on temporary tables - when initially called they show initial results, but then we want to drill down further to analyse (making sure we are using the exact same data).

    In these cases we call the same report again with an added parameter inAction.

    Hiding the inAction would reduce usability issues that are coming from our staff (where they are trying to understand what to put in inAction).

    A typical pseudocode report looks like this.

    create temp table...
    
    if(inAction = 'showOrders') then
    
      use (also hidden optional parameter 3 and 4 here)
    
    else if (inAction = 'somethingElse' ) then
    
      where inAction is set we usually have simple sql queries.
    
    else -- inAction is null - we show the default overview.
    
      linked reports with inAction parameter are here
    
      usually this part is 80% of the report.
    
    end if,
    
    

    
    drop temp table

    Best regards,
    Maron
14. 

    myDBR Team, Key Master

    You could hide the report yourself via JavaScript already, but it would be better if we provided a way to mark parameter not to be exposed to user at all. We'll take a look at it.

    --
    myDBR Team
15. 

    myDBR Team, Key Master

    Maron,
    the latest build allows for you to introduce parameters that are not shown to user and which you can use in linked reports.

    --
    myDBR Team
16. 

    maron, Member

    Thanks for the great support as always.

Reply

You must log in to post.