BusinessObjects Board

create a dynamic report based on a prompted date

building reports webintelligence
#1

Hi,

at work we have a DB with all IT devices and their respective date when they were checked.

I would like to create a report which will allow user to select a date (e.g: 31.03.2011) and lists only all IT devices which were not checked since 31.03.2010 (so IT devices which were not checked for last 12 months).

for now, i just have the standard SQL in BOXI which asks user to enter a date:


SELECT
  related_configuration_item.name,
  related_configuration_item.location_name,
  ci_hardware_other.zdatum_fyzicke_kontroly,
  related_configuration_item.zvyradene_id_zposob,
  related_configuration_item.resource_contact_last_name,
  related_configuration_item.delete_flag,
  ci_hardware_other.zuser_userid,
  ci_hardware_other.zuser_combo_name,
  ci_hardware_other.zuser_contact_num
FROM
  nr  related_configuration_item INNER JOIN har_othx  ci_hardware_other ON (related_configuration_item.id=ci_hardware_other.id)
  
WHERE
  (
   ci_hardware_other.zdatum_fyzicke_kontroly  <=  @prompt('Enter value(s) for datum fyzicke Kontroly:','D','Ci Hardware Other\datum fyzicke Kontroly',Mono,Free,Persistent,,User:0)
   AND
   related_configuration_item.delete_flag  In  ( 0  )
   AND
   (
    related_configuration_item.name  LIKE  'BEAM%'
    OR
    related_configuration_item.name  LIKE  'FOT%'
    OR
    related_configuration_item.name  LIKE  'KOP%'
    OR
    related_configuration_item.name  LIKE  'LCD%'
    OR
    related_configuration_item.name  LIKE  'MON%'
    OR
    related_configuration_item.name  LIKE  'NOTE%'
    OR
    related_configuration_item.name  LIKE  'PC%'
    OR
    related_configuration_item.name  LIKE  'PDA%'
    OR
    related_configuration_item.name  LIKE  'PORTR%'
    OR
    related_configuration_item.name  LIKE  'PLOT%'
    OR
    related_configuration_item.name  LIKE  'PSI%'
    OR
    related_configuration_item.name  LIKE  'REP%'
    OR
    related_configuration_item.name  LIKE  'SCAN%'
    OR
    related_configuration_item.name  LIKE  'TLA%'
    OR
    related_configuration_item.name  LIKE  'TLI%'
    OR
    related_configuration_item.name  LIKE  'TLAS%'
    OR
    related_configuration_item.name  LIKE  'TLL%'
    OR
    related_configuration_item.name  LIKE  'TLM%'
   )
  )

  1. for now the date format during prompting is in U.S. format so M/d/yyyy, how can i make it in slovak format dd.mm.yyyy ?

  2. how can i do to automatically make the date criteria equal to “date selected by user” minus 12 months ?
    something like dt = “date select by user” - 12 months
    where dt is the date criteria used for the SQL statement ?

thank a lot,

A.[/code]

alain.roger :slovakia: (BOB member since 2010-11-16)

#2

Try this sticky for ideas on the date math and date formats check list.

https://bobj-board.org/t/152613

Mak 1 :uk: (BOB member since 2005-01-06)