1. 

   adamhaeder, Member

   I want to give my users the ability to pass a parameter to my stored procedure (which would be handled by my WHERE clause), but I also want to give them the ability pass nothing and get all the data back. Currently, I can't pass 'nothing' unless I make the parameter optional, and when I make the parameter optional, I get no data back whatever I put in.

   Is there any way to allow a user to essentially 'remove' a parameter?
2. 

   myDBR Team, Key Master

   The way to do it is to use optional parameters. You can handle the empty parameter in the report.

   Could you elaborate what do you mean by "I get no data back whatever I put in"?

   --
   myDBR Team
3. 

   adamhaeder, Member

   I believe I'm having some kind of caching issue with my browser, but I'm not sure. I have erased all of my examples and I'm starting over. Here is where I'm at:

   I have a simple query that is displaying data from 1 table. One of the fields is 'name' (it's a company name). I want to display that field to my users so they can filter by a substring, but if they leave it blank, I want to return all values. My stored procedure looks like this:

   CREATE PROCEDURE sp_DBR_companies( coname varchar(100))
   select co.name as 'Name'
   WHERE co.name LIKE CONCAT('%', coname, '%');

   If I make this 'coname' field NON optional, then when I run the report, I get the screen asking my to input a value for coname. If I put nothing there and click 'Run Report', I get an error, because the field is non optional, so I have to enter SOMETHING there.

   So I edit the report and change the field to 'optional'. Now when I run the report, I don't see the initial screen asking me to enter a value, instead I'm immediately taken to the report results, and I only see the header row, no data at all. I would expect in this situation that I would click on the little 'gear' icon in the upper-right hand corner of the page to edit the parameters, but that gear icon is not there. The gear icon only shows up when I set the field to NON optional.

   This seems like strange behavior to me: I can't seem to use any field that is marked as 'optional'. I've tried this on Chrome and Firefox on Linux and I get the same results. Am I doing something wrong?

   Incidentally, I can achieve my desired result of 'all data' by putting '%' in as the parameter, but i don't really want to have to teach my users to do that if at all possible.
4. 

   myDBR Team, Key Master

   This is actually a problem in current release. If all parameters are optional, they are not asked at all. Futhermore, the gear icon does not appear. We'll fix this.

   To make it work for now, the only way is to add extra non-optional parameter to the report and possibly hide it from the user.

   Btw, if you use optional parameters and it is left empty, a NULL value is passed to the procedure so your code should look like this:

   co.name LIKE CONCAT('%', ifnull(coname,''), '%');

   --
   myDBR Team
5. 

   myDBR Team, Key Master

   This has now been fixed. Run the updater to get the latest build.

   --
   myDBR Team

Reply

You must log in to post.