1. 

   jfb, Member

   Hi I just got mydbr premium and I have a few question that I was unable to answer with the documentation.

   I'm trying to create a report with multiple parameters. All parameters are optional.

   At the moment I was able to only generate a report with a single parameter, if I add a second one the report is always empty.

   Can someone show me a few examples on how the report code should look like?

   Thank you
2. 

   myDBR Team, Key Master

   Could you show how your report looks like?

   --
   myDBR Team
3. 

   jfb, Member

   Basically this is my code right now:

   CREATE PROCEDURE sp_DBR()
   BEGIN

   select i.CodeLigne as 'Line Code',
   i.CodeItem as 'Product Code',
   i.OnHand1 as 'Qty',
   i.Magasin as 'Store'
   from pfd.inv i;

   END

   I would like to add optional parameters for i.Codeline, I.CodeItem and I.Magasin. The user needs to be able to add search values for parameters of leave them blank. Can anyone show me how it can be done?

   Thank you
4. 

   myDBR Team, Key Master

   You can add optional parameters to a report. If user does not enter any value in them, the values will be NULL in the report. You can then use ifnull-function in the report to match those parameters user has entered value for:

   CREATE PROCEDURE sp_DBR_myreport(
   
   vCodeline varchar(30),
   
   vCodeItem varchar(30),
   
   vMagasin varchar(30)
   
   )
   
   BEGIN
   
   select
   
       i.CodeLigne as 'Line Code',
   
       i.CodeItem as 'Product Code',
   
       i.OnHand1 as 'Qty',
   
       i.Magasin as 'Store'
   
   from pfd.inv i
   
   where
   
       i.CodeLigne = ifnull( vCodeline, i.CodeLigne) and
   
       i.CodeItem = ifnull( vCodeItem, i.CodeItem) and
   
       i.Magasin = ifnull( vMagasin, i.Magasin);
   
   END

   --
   myDBR Team
5. 

   jfb, Member

   Great thank you

   Another question: Where can I manage my store procedure? Can I change the stored procedure assigned to a report?
6. 

   myDBR Team, Key Master

   When you log in as admin, there is a "report code" link which will allow for you to edit the report code. If you wish to rename the report procedure, the best way is tho duplicate an existing report (all your parameter definitions will be duplicated) and you can drop the original one.

   --
   myDBR Team
7. 

   jfb, Member

   Thats great thank you

   I have a few more questions:

   When I use "dbr.avg", it displays the result like that: "avg 25.45". I would like to just display the result without the "avg".

   Is it possible to format a column displaying number so that instead of having: "1,234.00" I would get instead "1234".

   Thank you for your help
8. 

   jfb, Member

   One more thing:

   How do I change the label used for the parameters? right now its using the variable name.

   Thanks
9. 

   myDBR Team, Key Master

   Click 'edit' for the report and desired label for the "Parameter name for user" field.

   --
   myDBR Team
10. 

    jfb, Member

    thanks

    When I use "dbr.avg", it displays the result like that: "avg 25.45". I would like to just display the result without the "avg". How can I change that?
11. 

    myDBR Team, Key Master

    Preferences include options to determine the prefix used in aggregate functions. There is also separate command for this so you might want to check the manual.

    --
    myDBR Team

Reply

You must log in to post.