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

 

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

 

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

 

 

 

-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?