1. 

   jennifer808, Member

   I have two issues with parameters -
   1). I would like my startdate parameter default to the first day of the month 12 months ago. Here is my parameter query for YearAgo:

   select CAST(DATE_FORMAT(date_add(NOW(), interval -1 year) ,'%m-01-%Y') as DATE);

   I have set up the report like this:
   Parameter: inStartDate
   User Input: Default
   Default Value: YearAgo

   When I run the report - the inStartDate parameter is blank.

   2)
   I need to have a popup parameter that is based on inLogin. I can't see how to do that through the parameter query. Is there a way to have a pop up menu within the report that is NOT a parameter? Any other way around this?
2. 

   myDBR Team, Key Master

   1) If you run the query the answer is null.
   Just leave out the DATE_FORMAT from the query. myDBR will automatically handle the formatting for you based on user preference.

   2) Just put the inLogin into the parameter query, either have the query to be a procedure or do a direct query. See examples at documentation http://mydbr.com/doc/?start.userparam.html.

   --
   myDBR Team
3. 

   jennifer808, Member

   1 - it still is giving me a blank inStartDate when I run the report. I also think that if it were to default to something - it would be the day of the month today is and not the first of the month a year ago. Correct?

   Is there a way of forcing the parameter selection to remain at the top of the page after so you don't have to hit the 'gear' icon?
4. 

   myDBR Team, Key Master

   1 - it still is giving me a blank inStartDate when I run the report. I also think that if it were to default to something - it would be the day of the month today is and not the first of the month a year ago. Correct?

   Could you elaborate the "blank date"? Do you see the default value when report parameters are asked or is the blank date shown in the report?

   How it works is that if you define a default for report parameter, the parameter will be pre-filled with the default when the report is run first time. In subsequent executions myDBR remembers the user input making it easier for user to adjust the previous parameters.

   Is there a way of forcing the parameter selection to remain at the top of the page after so you don't have to hit the 'gear' icon?

   See the dbr.parameters.show-command from documentation:
   http://mydbr.com/doc/?cmd.appearance.html

   --
   myDBR Team
5. 

   jennifer808, Member

   It is a blank date when the report parameters are asked. I understand that in the future it will default to what I selected but the first time it is run - it is blank.

   Here is an example that give a blank when parameters are asked:
   StartLastMonth ->
   select CAST(DATE_FORMAT(date_add(NOW(), interval -1 month) ,'%m-01-%Y') as DATE);

   EndLastMonth ->
   Here is an example of one that works:
   select CAST(date_add(DATE_FORMAT(NOW() ,'%Y-%m-01'), interval -1 day) as DATE)
6. 

   myDBR Team, Key Master

   Here is an example that give a blank when parameters are asked:
   StartLastMonth ->
   select CAST(DATE_FORMAT(date_add(NOW(), interval -1 month) ,'%m-01-%Y') as DATE);

   If you run this in MySQL, the return value will be null, because date string 11-01-2011 is not valid. MySQL's default date format is YYYY-MM.DD. This is the reason why myDBR shows the default as blank. To get start of previous month just reorder formatting:

   select CAST(DATE_FORMAT(date_add(NOW(), interval -1 month) ,'%Y-%m-01') as DATE);

   --
   myDBR Team

Reply

You must log in to post.