I have this table in database where the output looks like

 

10/1/2010 11:30 AM     10/1/2010  11:59PM            1  

10/1/2010 11:59 PM     11/10/2010 10:38 PM          0

11/10/2010 10:38 PM   01/12/2011 09:20 AM          1

 

When its 0 it means the server was not available and viceversa. Start time wouldn't get reset until the avail changes from 1 to 0 or 0 to 1.

I need to report the server availability for last 13 months, grouped by month.This is easy when we have a start and end time (1st two rows) with consecutive months. But when we have data like row 3 where the data runs past a month or two, then how do I group by month?

I expect to see something like

 

 

and so on....

 

Any help appreciated!

 

Thanks

 

 

It looks like this is one of those situations, where the raw data in the database is beyond Crystal's ability to display as we human's would like it.

 

Crystal needs to see a row of data in order to print it as a detail, the only way that I know of to achieve this, is to have something like a stored proc create the 'missing' rows.

 

HTH

 

Can u please let me know if I can do the following as soon as possible. I am really new to crystal reports.

 

1) Can Crystal use anonymous pl/sql blocks

 

 

As to #2, where you would select a table, you can select the stored proc instead.  CR will create parameters for your user to enter that will match the data needed for the stored proc...

 

Or you can create a form in your application that will populate the parameters and then you can display the report

 

Or you can create a form that gathers the parameters from your users, retrieves the data from the database, do any post processing that you want in a middle tier and then push the resulting dataset to CR (this is what we do...we have 'wizards' that are much more user friendly and customizable than the parameter page in CR.

 

HTH

 

-Dell

 

@lockwelle: I'm not sure if I follow your options

 

 

@hilfy : I create a store procedure in Oracle database and get a desired output which looks like

 

 

-Dell

 

 

 

I created anonymous PL/SQL Block

 

CREATE GLOBAL TEMPORARY TABLE hquser.TEMP_TAB_AVAIL (v_RESOURCE_ID NUMBER, v_SW_GROUP_NAME VARCHAR2(255),
                                                  v_TIER_NAME VARCHAR2(255), v_RESOURCE_NAME VARCHAR2(255),
                                                  v_STARTIME NUMBER, v_ENDTIME NUMBER,
                                                  v_NEW_STARTIME NUMBER, v_NEW_ENDTIME NUMBER,
                                                  v_AVAILVABILITY NUMBER);
  DECLARE
    i                NUMBER :=0;
    v_FDOM           NUMBER := 0;
    v_LDOM           NUMBER := 0;
    v_RESOURCE_ID    NUMBER;
    v_SW_GROUP_NAME  VARCHAR2(255);
    v_TIER_NAME      VARCHAR2(255);
    v_RESOURCE_NAME  VARCHAR2(255);
    v_STARTIME       NUMBER;
    v_ENDTIME        NUMBER;
    v_NEW_STARTIME   NUMBER;
    v_NEW_ENDTIME    NUMBER;
    v_AVAILVABILITY  NUMBER;
    CURSOR cur_avail
    IS
      SELECT R.ID,
        R.NAME,
        G.NAME,
        P.NAME,
        AV.STARTIME,
        AV.ENDTIME,
        CASE
          WHEN AV.STARTIME > v_FDOM
          THEN AV.STARTIME
          WHEN AV.STARTIME < v_FDOM
          THEN v_FDOM
        END NEW_STARTIME,
        CASE
          WHEN AV.ENDTIME < v_LDOM
          THEN AV.ENDTIME
          WHEN AV.ENDTIME > v_LDOM
          THEN v_LDOM
        END NEW_ENDTIME,
        AV.AVAILVAL
      FROM hquser.EAM_RESOURCE R
      JOIN hquser.EAM_RES_GRP_RES_MAP RGM
      ON R.ID=RGM.RESOURCE_ID
      JOIN hquser.EAM_RESOURCE_GROUP RG
      ON RGM.RESOURCE_GROUP_ID=RG.ID
      JOIN hquser.EAM_RESOURCE G
      ON RG.RESOURCE_ID=G.ID
      JOIN hquser.EAM_RES_GRP_RES_MAP PGM
      ON G.ID=PGM.RESOURCE_ID
      JOIN hquser.EAM_RESOURCE_GROUP PG
      ON PGM.RESOURCE_GROUP_ID=PG.ID
      JOIN hquser.EAM_RESOURCE P
      ON PG.RESOURCE_ID=P.ID
      JOIN hquser.EAM_MEASUREMENT M
      ON M.RESOURCE_ID=R.ID
      JOIN hquser.HQ_AVAIL_DATA_RLE AV
      ON M.ID=AV.MEASUREMENT_ID
      JOIN hquser.EAM_MEASUREMENT_TEMPL T
      ON M.TEMPLATE_ID   =T.ID
      WHERE T.ALIAS      ='Availability'
      AND P.NAME         ='KPI:  - Availability'
      AND ( ( AV.STARTIME> v_FDOM
      AND AV.ENDTIME     < v_LDOM )
      OR ( AV.STARTIME BETWEEN v_FDOM AND v_LDOM )
      OR ( AV.ENDTIME BETWEEN v_FDOM AND v_LDOM )
      OR ( AV.STARTIME < v_FDOM
      AND AV.ENDTIME   > v_LDOM ) );
  BEGIN
    LOOP
    SELECT (TO_DATE(ADD_MONTHS (TRUNC(TRUNC(sysdate,'MM')-1,'MM'), -i)) -
                                TO_DATE('01/01/1970 00:00:00', 'MM-DD-YYYY HH24:MI SS')) * 24 * 60 * 60 * 1000
    INTO v_FDOM
    FROM DUAL;
    SELECT (TO_DATE(ADD_MONTHS(TRUNC(sysdate,'MM')-1,-i)) -
                                TO_DATE('01/01/1970 00:00:00','MM-DD-YYYY HH24:MI SS'))* 24 * 60 * 60 * 1000
    INTO v_LDOM
    FROM DUAL;
    i:= i+1;
    If  ( i >= 13 ) Then
         Exit;
    End if; 
    OPEN cur_avail;
    LOOP
      FETCH cur_avail
      INTO v_RESOURCE_ID,
        v_SW_GROUP_NAME,
        v_TIER_NAME,
        v_RESOURCE_NAME,
        v_STARTIME ,
        v_ENDTIME,
        v_NEW_STARTIME,
        v_NEW_ENDTIME,
        v_AVAILVABILITY;
      INSERT
      INTO hquser.TEMP_TAB_AVAIL VALUES
        (
          v_RESOURCE_ID,
          v_SW_GROUP_NAME,
          v_TIER_NAME,
          v_RESOURCE_NAME,
          v_STARTIME ,
          v_ENDTIME,
          v_NEW_STARTIME,
          v_NEW_ENDTIME,
          v_AVAILVABILITY
        );
      EXIT WHEN cur_avail%NOTFOUND;
    END LOOP;
    CLOSE cur_avail;
    END LOOP;
END; 

This works fine when I execute it in the Database. But I place this in the command, its throwing ORA0911 - Invalid character error. I'm thinking it doesn't like the semicolon. If I remove the semicolon, it  Oracle won't like it. How can I fix this?

 

Any suggestions?

The problem is that while your PL/SQL block creates a temp table, it doesn't actually return any data.  Crystal is looking for the data.  So, intead of using a temp table, I would return a refcursor - I'm not sure that Crystal will handle a temp table.  This mean creating a stored function in the database to handle this instead of using an anonymous PL/SQL block. 

 

-Dell