How to print SQL queries of Webi 4.0 document using Java

Posted by & filed under Business Objects, SDK.

The current public BO BI 4.0 SDK does not include functionality that allows to print SQL queries of a Webi 4.0 document, but this is still possible.

UPDATE: BO BI 4.1 SDK contains RESTful web services for Webi. It allows you to do the below without hacks.

Disclaimer: Use it on your own risk.

The necessary libraries:

  • C:\Program Files (x86)\SAP BusinessObjects\Tomcat6\webapps\BOE\WEB-INF\eclipse\plugins\webpath.AnalyticalReporting\web\WEB-INF\lib\adv_ivcdzview.jar
  • C:\Program Files (x86)\SAP BusinessObjects\Tomcat6\webapps\BOE\WEB-INF\eclipse\plugins\webpath.AnalyticalReporting\web\WEB-INF\lib\webi_client_toolkit.jar
  • C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\java\lib\*
    (without subfolders)

The program has two classes: Program and Processor. Program is a main class that iterates through infoobjects of Webi type. Processor is printing SQL of the Webi documents. The most interesting part is Processor which uses BI 4.0 functionality for accessing data providers.

Main (Program.java)

import com.crystaldecisions.sdk.exception.SDKException;
import com.crystaldecisions.sdk.framework.CrystalEnterprise;
import com.crystaldecisions.sdk.framework.IEnterpriseSession;
import com.crystaldecisions.sdk.framework.ISessionMgr;
import com.crystaldecisions.sdk.occa.infostore.IInfoObject;
import com.crystaldecisions.sdk.occa.infostore.IInfoObjects;
import com.crystaldecisions.sdk.occa.infostore.IInfoStore;

public class Program {
   public static void main(String[] args) throws Exception {

      IEnterpriseSession enterpriseSession = null;
      try {
         // Establish connection
         System.out.println("Connecting...");
         ISessionMgr sessionMgr = CrystalEnterprise.getSessionMgr();
         enterpriseSession = sessionMgr.logon(
               "Administrator", "", "localhost", "secEnterprise");
         IInfoStore infoStore = (IInfoStore) enterpriseSession.getService("InfoStore");

         // Initialize processor (see below)
         Processor processor = new Processor(enterpriseSession);

         // Get list of Webi documents
         String cmsQuery = "select SI_NAME, SI_ID from CI_INFOOBJECTS "
               + "where SI_KIND = 'Webi' and SI_INSTANCE=0";
         IInfoObjects infoObjects = (IInfoObjects) infoStore.query(cmsQuery);

         // Process all Webi documents from a folder (root here)
         for (Object object : infoObjects) {
            IInfoObject infoObject = (IInfoObject) object;
            String path = getInfoObjectPathAndTitle(infoObject); 
            if (path.startsWith("/")) {
               System.out.println(path);
               processor.process(infoObject);
               System.out.println();
            }
         }
      } catch (SDKException ex) {
         ex.printStackTrace();
      } finally {
         if (enterpriseSession != null)
            enterpriseSession.logoff();
      }
      System.out.println("Finished!");
   }

   public static String getInfoObjectPath(IInfoObject infoObject) throws SDKException {
      String path = "";
      while (infoObject.getParentID() != 0) {
         infoObject = infoObject.getParent();
         path = "/" + infoObject.getTitle() + path;
      }
      return path;
   }

   public static String getInfoObjectPathAndTitle(IInfoObject infoObject) throws SDKException {
      return getInfoObjectPath(infoObject) + "/" + infoObject.getTitle();
   }
}

Printing (Processor.java)

import java.util.List;
import java.util.Locale;

import com.businessobjects.adv_ivcdzview.Utils;
import com.businessobjects.rebean.wi.model.engine.IDocumentInstance;
import com.businessobjects.rebean.wi.services.IDocumentInstanceManagementService;
import com.businessobjects.rebean.wi.services.IDocumentInstanceService;
import com.businessobjects.sdk.core.Core;
import com.businessobjects.sdk.core.context.IContext;
import com.crystaldecisions.sdk.exception.SDKException;
import com.crystaldecisions.sdk.framework.IEnterpriseSession;
import com.crystaldecisions.sdk.occa.infostore.IInfoObject;
import com.sap.sl.dataprovider.DataProvider;
import com.sap.sl.dataprovider.service.DataProviderService;
import com.sap.sl.queryexecutionplan.NativeQueryNode;
import com.sap.sl.queryexecutionplan.OperatorNode;
import com.sap.sl.queryexecutionplan.QueryExecutionPlan;
import com.sap.sl.queryexecutionplan.QueryExecutionPlanNode;
import com.sap.sl.sdk.workspace.service.WorkspaceContextualService;
import com.sap.sl.workspace.Workspace;
import com.sap.sl.workspace.service.WorkspaceService;
import com.sap.webi.client.toolkit.Deployment;
import com.sap.webi.client.toolkit.LoginKey;
import com.sap.webi.client.toolkit.SessionContext;
import com.sap.webi.client.toolkit.services.DSLServicesHelper;
import com.sap.webi.client.toolkit.services.ServicesHelper;

public class Processor {

   SessionContext sessionContext;
   Locale localLocale;
   IContext context;
   IDocumentInstanceManagementService dimService;
   IDocumentInstanceService diService;
   DataProviderService dpService;
   WorkspaceService wsService;

   public Processor(IEnterpriseSession enterpriseSession) throws SDKException {
      sessionContext = Utils.getSessionContextManager()
            .matchSessionContext(Deployment.DHTML, 
                  enterpriseSession.getSerializedSession(), true);

      localLocale = Locale.getDefault();
      sessionContext.getLoginInfo().set(LoginKey.LOCALE, localLocale);
      sessionContext.logonWithSerializedSession();

      context = sessionContext.getCoreContext();         
      dimService = Core.getService(IDocumentInstanceManagementService.class);
      diService = ServicesHelper.getDocumentInstanceService();

      dpService = DSLServicesHelper.getDataProviderService(context);
      wsService =  Core.getService(WorkspaceContextualService.class, context);
   }

   public void process(IInfoObject infoObject) {
      IDocumentInstance doc = dimService.openDocument(context, infoObject.getID());               
      Workspace workspace = diService.getWorkspace(context, doc);            
      List<DataProvider> listDataProvider = wsService.getDataProviders(workspace); 
      for (DataProvider provider : listDataProvider) {
         QueryExecutionPlan plan = dpService.getQueryExecutionPlan(provider, true);
         print(plan.getQueryExecPlanTree());
      }
      dimService.closeDocument(context, doc);
   }

   private static void print(QueryExecutionPlanNode planNode) {
      if (planNode instanceof NativeQueryNode) {
         NativeQueryNode queryNode = (NativeQueryNode)planNode;
         System.out.println(queryNode.getNativeQueryString());         
      } else if (planNode instanceof OperatorNode) {
         OperatorNode operatorNode = (OperatorNode)planNode;      
         for (QueryExecutionPlanNode node : operatorNode.getChildren()) {
            print(node);
         }
      }
   }
}

Java code to list the objects used in a Webi 4.0 document

Posted by & filed under Business Objects, SDK.

The current public BO BI 4.0 SDK does not include any features that allows to list objects used in a Webi 4.0 document or access variables. But this is still possible.

UPDATE: Please consider 4.1 RESTful Web Services SDK Developer Guides and API References http://scn.sap.com/docs/DOC-27465. It might be more convenient than hacking internal SDKs.

The necessary libraries:

  • C:\Program Files (x86)\SAP BusinessObjects\Tomcat6\webapps\BOE\WEB-INF\eclipse\plugins\webpath.AnalyticalReporting\web\WEB-INF\lib\adv_ivcdzview.jar
  • C:\Program Files (x86)\SAP BusinessObjects\Tomcat6\webapps\BOE\WEB-INF\eclipse\plugins\webpath.AnalyticalReporting\web\WEB-INF\lib\webi_client_toolkit.jar
  • C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\java\lib\* (without subfolders)
Disclaimer: Use it on your own risk
import java.util.List;
import java.util.Locale;

import com.businessobjects.adv_ivcdzview.Utils;
import com.businessobjects.rebean.wi.model.engine.IDocumentInstance;
import com.businessobjects.rebean.wi.services.IDocumentInstanceManagementService;
import com.businessobjects.sdk.core.Core;
import com.businessobjects.sdk.core.context.IContext;
import com.crystaldecisions.sdk.exception.SDKException;
import com.crystaldecisions.sdk.framework.CrystalEnterprise;
import com.crystaldecisions.sdk.framework.IEnterpriseSession;
import com.crystaldecisions.sdk.framework.ISessionMgr;
import com.crystaldecisions.sdk.occa.infostore.IInfoObject;
import com.crystaldecisions.sdk.occa.infostore.IInfoObjects;
import com.crystaldecisions.sdk.occa.infostore.IInfoStore;
import com.sap.sl.dictionary.DictionaryExpression;
import com.sap.sl.dictionary.Variable;
import com.sap.webi.client.toolkit.Deployment;
import com.sap.webi.client.toolkit.LoginKey;
import com.sap.webi.client.toolkit.SessionContext;
import com.sap.webi.client.toolkit.reporting.ReportDictionaryHelper;

public class Program2 {
  public static void main(String[] args) throws Exception {

    IEnterpriseSession enterpriseSession = null;
    try {
      System.out.println("Connecting...");
      ISessionMgr sessionMgr = CrystalEnterprise.getSessionMgr();
      enterpriseSession = sessionMgr.logon(
          "Administrator", "", "localhost", "secEnterprise");

      // Do some magic
      String serializedSession = enterpriseSession.getSerializedSession();
      SessionContext sessionContext = Utils.getSessionContextManager()
          .matchSessionContext(Deployment.DHTML, serializedSession, true);
      Locale localLocale = Locale.getDefault();
      sessionContext.getLoginInfo().set(LoginKey.LOCALE, localLocale);
      sessionContext.logonWithSerializedSession();
      IContext context = sessionContext.getCoreContext();
      IDocumentInstanceManagementService documentInstanceManagementService =
          Core.getService(IDocumentInstanceManagementService.class);

      // Get the list of webi documents
      IInfoStore infoStore = (IInfoStore) enterpriseSession.getService("InfoStore");
      String query = "select SI_NAME, SI_ID from CI_INFOOBJECTS "
          + "where SI_KIND = 'Webi' and SI_INSTANCE=0";
      IInfoObjects infoObjects = (IInfoObjects) infoStore.query(query);

      for (Object object : infoObjects) {
        IInfoObject infoObject = (IInfoObject) object;
        if (getInfoObjectPathAndTitle(infoObject).startsWith("/")) {
          System.out.println("REPORT: " + infoObject.getTitle());

          IDocumentInstance doc = documentInstanceManagementService
              .openDocument(context, infoObject.getID());

          List list = ReportDictionaryHelper
              .getDictionaryObjectsFlatList(context, doc);
          if (list.size() > 0) {
            System.out.println("OBJECTS:");
            for (DictionaryExpression expr:list) {
              System.out.println(expr.getName());
            }
          }

          List vars = ReportDictionaryHelper.getDocumentVariables(context,doc);
          if (vars.size() > 0) {
            System.out.println("VARIABLES:");
            for (Variable var:vars) {
              System.out.println(var.getName());
            }
          }

          documentInstanceManagementService.closeDocument(context, doc);
          System.out.println();
        }
      }
    } catch (SDKException ex) {
      ex.printStackTrace();
    } finally {
      if (enterpriseSession != null)
        enterpriseSession.logoff();
    }
    System.out.println("Finished!");
  }

  public static String getInfoObjectPath(IInfoObject infoObject)
      throws SDKException
  {
    String path = "";
    while (infoObject.getParentID() != 0) {
      infoObject = infoObject.getParent();
      path = "/" + infoObject.getTitle() + path;
    }
    return path;
  }

  public static String getInfoObjectPathAndTitle(IInfoObject infoObject)
      throws SDKException
  {
    return getInfoObjectPath(infoObject) + "/" + infoObject.getTitle();
  }
}

Hack of Webi prompts

Posted by & filed under Web Intelligence.

Prompts can be used to select a value from the list of values. What about selecting a field from a list of fields? Say you have FIELD1 and FIELD2 in SOMETABLE. You want to display FIELD1 or FIELD2 depending on the user answer. You can write a long select using CASE WHEN, but that is not interesting. Here is a dirty hack :)

Create an object with select:

@Prompt('Select Dimension ', 'N', {'FIELD1', 'FIELD2'}, mono, constrained, persistent,)

Select SOMETABLE in the property Tables.

'N' is used as prompt type. This means that the values in the LoV are supposed to be numeric, so BO will unquote the selected value generating SQL.

Let's try this in a report. Pick the object, and check the generated SQL:

When you run report, you will get a prompt:

You will see 'foo' in the report (which is the value of the field FIELD1)

The generated SQL:

Command line InfoStore Query Builder with export to Excel

Posted by & filed under Tools.

A free command line tool that runs query against CMS database and saves the result in Excel or CSV.

Options

-cms: BO server. Default is localhost
-username: BO user name. Default is Administrator
-password: User password. Default is blank
-auth: Authentification. Default is secEnterprise

-query: CMS query (the same as you run in Query Builder). See example.
-file: CMS query will be read from the file.
You should either specify -query: or -file: but not both.

-excel: Output excel file name. It will contain two spreadsheets: one for simple properties
and another for containers. See containers.
-csv: Output cvs file for simple properties. See containers.
-csv2: Output cvs file for containers properties. See containers.
If neither -excel: nor -csv: are specified, the simple properties will be printed to the standard output in CSV format.

Example

java -jar querybuilder.jar -query:"select si_name from ci_infoobjects"

Containers

There are simple properties of CMS objects e.g. SI_ID, SI_NAME, and containers – properties that have subproperties. For instance, the property SI_PROMPTS has subproperties SI_NUM_PROMPTS, SI_PROMPT1, SI_PROMPT2 etc. In turn SI_PROMPT1 has subproperties: SI_PROMPT, SI_PROMPT_TYPE, SI_OPTIONS etc. The containers are exported in the form: infoobject id, infoobject title, property path, property value.

Download

The tool has been desupported. Check Biclever CMS Query Builder.

InfoStore Query Builder to Check Relations

Posted by & filed under Tools.

There is a nice tool in CMC that allows to check relations between objects. Something similar can be done in the query builder with some non documented features.

For instance, to find all reports that use a universe Finance, you can write:

SELECT *
FROM ci_infoobjects, ci_appobjects, ci_systemobjects
WHERE PARENTS("si_name='Webi-Universe'","si_name='Finance'")

PARENTS has two arguments. First is the relation type, second is the reference object.

If you want to find opposite – all universes used by a report, you can write

SELECT *
FROM ci_infoobjects, ci_appobjects, ci_systemobjects
WHERE CHILDREN("si_name='Webi-Universe'","si_name='Balance Sheet'")

What are the possible types of relations (first argument)? You can find the list querying CMS:

SELECT si_name FROM ci_systemobjects WHERE si_relation_table_name='RELATIONS'

Now you can also build such queries and export result to to Excel in InfoStore Query Builder:

Problem with OpenDocument inside IFrame in Internet Explorer

Posted by & filed under Administration.

Symptoms

It is not possible to login to BusinessObjects using Internet Explorer when the report is embedded into another web page using IFRAME and OpenDocument link.

<iframe width=100% height=100% 
src="http://SERVERNAME:8080/OpenDocument/opendoc/openDocument.jsp?sType=wid&sDocName=Balance+Sheet">

You get the login screen but nothing happens when you try to login (you are staying on the same page). The OpenDocument link works when it is opened in the browser directly.

Cause

This happens because Internet Explorer gives lower level of trust to IFRAME pages. IE calls this "third-party" content. If the page inside the IFRAME doesn't have a Privacy Policy, its cookies are blocked (which is indicated by the eye icon in status bar, when you click on it, it shows you a list of blocked URLs). The cookies are used to store BO session. As the session cannot be saved, the user cannot login.

Resolution (Tomcat)

1. Create folder w3c in

[Business Objects]\Tomcat55\webapps\

2. Create file p3p.xml in the folder with the following code:

<META>
  <POLICY-REFERENCES>
    <POLICY-REF about="/w3c/policy.xml">
      <INCLUDE>/</INCLUDE>
      <COOKIE-INCLUDE/>
    </POLICY-REF>
  </POLICY-REFERENCES>
</META>

Fun with NULL

Posted by & filed under Oracle.

I think this is interesting:

SELECT * FROM dual WHERE (1 BETWEEN 0 AND NULL)     -- FALSE
SELECT * FROM dual WHERE (0 BETWEEN 1 AND NULL)     -- FALSE
SELECT * FROM dual WHERE NOT (1 BETWEEN 0 AND NULL) -- FALSE
SELECT * FROM dual WHERE NOT (0 BETWEEN 1 AND NULL) -- TRUE

This has a good explanation however I am still embarrassed :)

How to Replace a Table with a Derived Table

Posted by & filed under Tools, Universe Designer.

This post describes the detailed steps to replace a database table with a derived table. Also it describes how Universe Documenter can help to achieve the perfect quality even for such complex modification of a universe.

You may need to replace a table with a derived table if your universe is affected by the self-outer join issue.

We will consider the following schema:

The task is to replace BO_CUSTOMER with a derived table to avoid self join in the universe database structure.

0) Make a backup of the universe and the reports that use the universe

Also export a copy of the universe to CMS. This will be required on the step 9 for verification.

1) Create the derived table that contains SQL with the self join SQL in WHERE

Create table BO_CUSTOMER_ with the following SQL:

SELECT * FROM BO_CUSTOMER WHERE ACCESSUSERNAME=@VARIABLE('BOUSER')

2) Switch all joins from the original table to the derived table.

Edit the join expression. Do not change tables in the combo-box, this may screw up the select.

The self join BOEMPLOYEE.ACCESSUSERNAME=@VARIABLE('BOUSER') should be removed. You will be prompted that it is used in contexts. Ignore this message.

The contexts do not need to be updated.

3) Update objects expression and the expression of the predefined conditions

  • Select first class in the universe.
  • Hit Ctrl+F.
  • Enter BO_CUSTOMER in the field "Find what"
  • Enter BO_CUSTOMER_ in the field "Replace"
  • Select "SQL" in the Look Also In
Sometimes Replace All works fine, but it would be more preferable to change objects one by one.

4) If the table is a fact table and aggregate awareness is used in the universe, check if you need to update aggregate navigation.

  • Open aggregate navigation
  • Select the fact table
  • Select option "Incompatibles only"
  • Check objects and predefined conditions.
  • If the list "Associated Incompatible Objects" is empty, you do not need to do anything.
  • If the list is not empty, the same incompatibilities must be defined for the derived table.
(This is not needed in our example)

3) Check if you are lucky

Try deleting the original table. If the table was removed without prompts, you finished with the table. If you see the prompt that the table is used in object definitions, do not remove the table.

  • The table can be used in index awareness.
  • The table can also be used to force join with another table.

6) Modify the objects that use join forcing

  • Click right button on BO_CUSTOMER
  • In pop-up menu select View associated objects
  • If no objects highlighted in class structure, go to the next step
  • Go through all highlighted objects
  • The select of such objects do not contain reference to the table BO_CUSTOMER because we replaced all BO_CUSTOMER to BO_CUSTOMER_ on the step 3.
  • Click Tables and change BO_CUSTOMER to BO_CUSTOMER_ holding Ctrl button.

7) Update objects for which index awareness is defined.

There is no easy way to find the objects that has index awareness in Universe Designer. You need to go through all objects and check Keys in properties:

The easy way is to build the list of index awareness using Universe Documenter

8) Save, export

9) Verify

The modification described here is very complex. It is easy to make a mistake. Luckily it is possible to use the tool Universe Documenter to identify the mistakes.

To reduce the number of differences, import the copy of the original universe and change the name of the replaced table to BO_CUSTOMER_ and export the universe back to CMS.

Import the modified copy and the corrected universes in Universe Documenter, perform diff (How to diff universes using Universe Documenter) and check all highlighted differences. The two universes must be almost identical with an exception that the BO_CUSTOMER_ in the corrected universe is a derived table.

Problem of using self joins in combination with outer joins

Posted by & filed under Universe Designer.

Problem

(It is assumed that the parameter ANSI92 of the universe is set to Yes, the parameter SELFJOINS_IN_WHERE is either not set or is set to No.)

The table A is affected by the self/outer join issue if it has a self join and it is joined to a table B with the following outer join:

If a query use these two tables, the self join SQL will be put into the outer join SQL causing the generated SQL query to ignore the self join.

If the query contains at least one inner join of A with another table, the issue will not appear because the self join will be put into the inner join SQL, so the self join will be applied to the table A. Due to this, the issue appear rarely.

Example 1

The table structure contains two tables BOEMPLOYEE and EXVENDOR. BOEMPLOYEE has a field ACCESSUSERNAME which is used to implement access control to the table.

If the query uses these two tables, the self join will be put into the outer join SQL and therefore ignored:

SELECT
  BOEMPLOYEE.NAME,
  EXVENDOR.NAME
FROM EXVENDOR RIGHT OUTER JOIN BOEMPLOYEE
  ON (BOEMPLOYEE.VENDORNUMBER=EXVENDOR.VENDORNUMBER
      AND BOEMPLOYEE.ACCESSUSERNAME = @VARIABLE('BOUSER'))

Example 2

Here we have a table EXFINANCEENTRY that is joined with BOEMPLOYEE using inner join.

In this case, the self join the self join will be put both into the outer join SQL and inner join SQL. Because it is put in the inner join, the self join is applied to the BOEMPLOYEE and the issue will not appear:

SELECT
  EXFINANCEENTRY.TRANSACTIONNUMBER,
  BOEMPLOYEE.NAME,
  EXVENDOR.NAME
FROM
  EXVENDOR RIGHT OUTER JOIN BOEMPLOYEE
  ON (BOEMPLOYEE.VENDORNUMBER=EXVENDOR.VENDORNUMBER
      AND BOEMPLOYEE.ACCESSUSERNAME = @VARIABLE('BOUSER'))
  INNER JOIN EXFINANCEENTRY
  ON (EXFINANCEENTRY.EMPLOYEENUMBER=BOEMPLOYEE.EMPLOYEENUMBER
      AND BOEMPLOYEE.ACCESSUSERNAME = @VARIABLE('BOUSER'))

The issue in BO XI 3.1 FP3.7, FP3.8, FP4.2, FP5.2

In the mentioned FPs, the algorithm of generation of joins has changed: the self join is put only in one join SQL connected to the table. (This is error that tracked under the problem report id ADAPT01640966. SAP KB article 1743593 – Self join is ignored by SQL query generator in WebIntelligence Rich Client and Universe Designer in SAP BusinessObjects XI 3.1)

Example 3

The self join is ignored even though there is an inner join:

SELECT
  EXFINANCEENTRY.TRANSACTIONNUMBER,
  BOEMPLOYEE.NAME,
  EXVENDOR.NAME
FROM
  EXVENDOR RIGHT OUTER JOIN BOEMPLOYEE
  ON (BOEMPLOYEE.VENDORNUMBER=EXVENDOR.VENDORNUMBER
      AND BOEMPLOYEE.ACCESSUSERNAME = @VARIABLE('BOUSER'))
  INNER JOIN EXFINANCEENTRY
  ON (EXFINANCEENTRY.EMPLOYEENUMBER=BOEMPLOYEE.EMPLOYEENUMBER)

Solution

Either you using the mentioned FPs or not, the described situation is a design flaw of the universes. In such situations, the self join should be embedded into the derived table.

For the above examples, the derived table can be following

SELECT * FROM  BOEMPLOYEE WHERE ACCESSUSERNAME = @VARIABLE('BOUSER'))

The table BOEMPLOYEE should be replaced with this derived table.

See also

Use of Outer Joins in Combination with Self Restricting Joins
How to Replace a Table with a Derived Table

Example of sub-query in Webi

Posted by & filed under Web Intelligence.

The post describes an interesting example of use of subqueries in Web Intelligence.

The task is to display year-to-day revenue figures.

The universe database structure is the following:

The universe contains the following objects:

  • Revenue defined as SUM(INVOICE_LINE.DAYS * INVOICE_LINE.NB_GUESTS * SERVICE.PRICE)
  • Invoice Date objects such as Invoice Date, Month, Quarter, Year.
  • Today object defined as SYSDATE (Oracle).

The task can be solved with the following query in Web Intelligence:

The generated SQL is:

SELECT
  SUM(INVOICE_LINE.DAYS * INVOICE_LINE.NB_GUESTS * SERVICE.PRICE),
  INVOICE_DATE.THE_DATE,
  INVOICE_DATE.YEAR
FROM
  SERVICE INNER JOIN INVOICE_LINE 
  ON (INVOICE_LINE.SERVICE_ID=SERVICE.SERVICE_ID)
  INNER JOIN SALES ON (INVOICE_LINE.INV_ID=SALES.INV_ID)
  INNER JOIN CALENDARDAY  INVOICE_DATE 
  ON (SALES.INVOICE_DATE_ID=INVOICE_DATE.DATE_ID)  
WHERE
  (
   INVOICE_DATE.YEAR  =  ANY 
     (
     SELECT
       INVOICE_DATE.YEAR
     FROM
       CALENDARDAY  INVOICE_DATE
     WHERE
       INVOICE_DATE.THE_DATE  =  SYSDATE
     )
   AND
   INVOICE_DATE.THE_DATE  <=  SYSDATE
  )
GROUP BY
  INVOICE_DATE.THE_DATE, 
  INVOICE_DATE.YEAR