MDX - Calcoli utili

Valore nel periodo corrispondente dell'anno precedente
([Measures].[Ven], ParallelPeriod([Calendario.Calendario Castoldi].[Anno],1,[Calendario.Calendario Castoldi].CurrentMember))

Indice rispetto all'anno
[Measures].[Ven]/([Measures].[Ven], Ancestor([Calendario.Calendario Castoldi].CurrentMember, [Calendario.Calendario Castoldi].[Anno]))
 
Indice rispetto a tutti gli articoli
[Measures].[Ven]/([Measures].[Ven], [Articolo].[Tutti gli articoli])

SpagoBI - Deploy Cubo JPivot

Passi da eseguire per il deploy di un nuovo cubo:

(1) Innanzitutto è necessario impostare il valore della variabile "spagobi_resource_path" nel file \apache-tomcat\conf\server.xml con tutto il percorso

<Environment name="spagobi_resource_path" type="java.lang.String" value="C:/SpagoBI/apache-tomcat-6.0.33/resources"/>

(2) creare lo schema (esempio.xml)
(3) aggiungere lo schema esempio.xml alla cartella  \apache-tomcat\resources\Olap
(4) modificare il file \apache-tomcat\webapps\SpagoBIJPivotEngine\WEB-INF\classes\engine-config.xml aggiungendo le informazioni relative al nuovo schema

<SCHEMAS> 

<SCHEMA catalogUri="/Olap/esempio.xml" name="esempio" /> 

 </SCHEMAS>

(5) riavviare Spago e aggiungere un nuovo documento OLAP

PostgreSQL - Generare dim_calendar

Breve script per generare una semplice dimensione temporale (calendario) con posgres utile in ogni datawarehouse:

create table stg.dim_calendar as
select to_char( to_date('20000101', 'YYYYMMDD') + s, 'YYYYMMDD')::int8 as dw_id_calendar,
(to_date('20000101', 'YYYYMMDD') + s)::timestamp as timestamp,
(to_date('20000101', 'YYYYMMDD') + s) as date,
to_char( to_date('20000101', 'YYYYMMDD') + s, 'YYYYMMDD')::varchar as YYYYMMDD,
to_char( to_date('20000101', 'YYYYMMDD') + s, 'YYYYMM')::varchar as YYYYMM,
to_char( to_date('20000101', 'YYYYMMDD') + s, 'YYYY')::varchar as Year,
to_char( to_date('20000101', 'YYYYMMDD') + s, 'Q')::int4 as Quarter,
to_char( to_date('20000101', 'YYYYMMDD') + s, 'MM')::int4 as Month,
to_char( to_date('20000101', 'YYYYMMDD') + s, 'Mon') as Month_name,
to_char( to_date('20000101', 'YYYYMMDD') + s, 'W')::int4 as Week_of_month,
to_char( to_date('20000101', 'YYYYMMDD') + s, 'WW')::int4 as Week_of_year,
to_char( to_date('20000101', 'YYYYMMDD') + s, 'DD')::int4 as Day_of_month,
to_char( to_date('20000101', 'YYYYMMDD') + s, 'D')::int4 as Day_of_week,
to_char( to_date('20000101', 'YYYYMMDD') + s, 'Dy') as Day_of_week_name,
to_char( to_date('20000101', 'YYYYMMDD') + s, 'DDD')::int4 as Day_of_year
from generate_series(0, 7300) s

Japser - Gestione dei parametri: $P{}, $P!{}, $X{}

$P{} 
Using $P{} in the report query is for situations where the query is fixed at design time and you only wish to inject values into the query before it is executed. The example does not illustrate this concept, however the $X{} explained shortly works with a similar concept.
When using $P{} you do something like the following:

    
   SELECT * FROM Address WHERE city = $P{customerId}
     
 

If we changed the query in this way and turned on Debugging for the JRJdbcQueryExecuter in an application running this report, we would get an output like this (the hosting application also collected the value for the parameter and supplied it to JasperReports when it was time to Fill the report):

  
    SELECT * FROM Address WHERE city = ?
    Parameter #1 (city of type java.lang.String): New York
     

In this case the query as seen above and the parameter are passed to the database via the JDBC Driver (MySQL in this example) to be executed.
As report developers we don't have to worry about adding quotes around the String value for city in the query as that will be done for us.
This illustrates one way of injecting values into the query. 

$P!{} 
Using $P!{} allows you to modify the query syntax itself. The query in the sample uses this:

    
     SELECT * FROM Address WHERE $X{NOTIN, City, ExcludedCities} ORDER BY $P!{OrderClause}
     
 

If we run the report in an application and collect values for the parameters (OrderBy was given the value 'LastName') we will see an output like this:

    
    SELECT * FROM Address WHERE City NOT IN (?) ORDER BY LastName
    Parameter #1 (ExcludedCities[0] of type java.lang.String): New York
     
 

Here we can see the value of $P!{OrderClause} was added into the query directly by JasperReports. For this reason, when working with $P!{} you must ensure any values collected will not result in a syntax error in the query as they will be inserted directly into the query. However this does give us the power to modify the query entirely. For example we could have set the whole 'ORDER BY' clause using $P!{}, or chosen to omit it entirely. 

$X{} 

There are also cases when just using $P{} in the report query is not enough, because parts of the query need to be dynamically built, depending on one or more report parameter values, in order to construct a valid query. The most common case is the <column_name> = $P{<param_name>} equality clause. When the value of the parameter is null, the expression <column_name> = NULL becomes invalid and it has to be replaced with <column_name> IS NULL. Another case comes with IN and NOT IN query clauses that need to use a collection report parameter as a list of values, unavailable for the simple $P{} syntax. 
Such complex query clauses are introduced into the query using the $X{} syntax. The general form of a $X{} clause is $X{functionName, param1, param2,...}. 
Similar to the $P{} explanation above, $X{} results in ? being added to the query before submitting it to the DB. Also submitted are the values collected leaving it to the JDBC driver to add the values in and ensure the syntax of the query is correct.


Built-in SQL Clause Functions 

As shown above, complex queries generation might depend on parameter values. JasperReports provides built-in support for several SQL clause functions which require some additional processing:

1. The ${EQUAL, <column_name>, <parameter_name>} clause function 
   
   The function expects three mandatory clause tokens:
   • The first token represents the function ID and always takes the fixed value EQUAL.
   • The second token is the SQL column (or column combination) to be used in the clause.
   • The third token is the name of the report parameter that contains the value to compare to.
   If the parameter's value is not null, the function constructs a <column_name> = ? clause. 
   If the parameter's value is null, the function generates a <column_name> IS NULL clause. 
    
2. The ${NOTEQUAL, <column_name>, <parameter_name>} clause function 
   
   The function expects three mandatory clause tokens:
   • The first token represents the function ID and always takes the fixed value NOTEQUAL.
   • The second token is the SQL column (or column combination) to be used in the clause.
   • The third token is the name of the report parameter that contains the value to compare to.
   If the parameter's value is not null, the function constructs a <column_name> <> ? clause. 
   If the parameter's value is null, the function generates a <column_name> IS NOT NULL clause. 
    
3. The ${LESS, <column_name>, <parameter_name>} clause function 
   
   The function expects three mandatory clause tokens:
   • The first token represents the function ID and always takes the fixed value LESS.
   • The second token is the SQL column (or column combination) to be used in the clause.
   • The third token is the name of the report parameter that contains the value to compare to.
   If the parameter's value is not null, the function constructs a <column_name> < ? clause. 
   If the parameter's value is null, the comparison with null will be neglected and the function generates a SQL clause that will always evaluate to true (e.g. 0 = 0). 
   
   Note: If the comparison with null (which always return false) should not be ignored, then one can use the <column_name> < $P{<parameter_name>} instead. 
    
4. The ${LESS], <column_name>, <parameter_name>} clause function 
   
   The function expects three mandatory clause tokens:
   • The first token represents the function ID and always takes the fixed value LESS].
   • The second token is the SQL column (or column combination) to be used in the clause.
   • The third token is the name of the report parameter that contains the value to compare to.
   If the parameter's value is not null, the function constructs a <column_name> <= ? clause. 
   If the parameter's value is null, the comparison with null will be neglected and the function generates a SQL clause that will always evaluate to true (e.g. 0 = 0). 
   
   Note: If the comparison with null (which always return false) should not be ignored, then one can use the <column_name> <= $P{<parameter_name>} instead. 
    
5. The ${GREATER, <column_name>, <parameter_name>} clause function 
   
   The function expects three mandatory clause tokens:
   • The first token represents the function ID and always takes the fixed value GREATER.
   • The second token is the SQL column (or column combination) to be used in the clause.
   • The third token is the name of the report parameter that contains the value to compare to.
   If the parameter's value is not null, the function constructs a <column_name> > ? clause. 
   If the parameter's value is null, the comparison with null will be neglected and the function generates a SQL clause that will always evaluate to true (e.g. 0 = 0). 
   
   Note: If the comparison with null (which always return false) should not be ignored, then one can use the <column_name> > $P{<parameter_name>} instead. 
    
6. The ${[GREATER, <column_name>, <parameter_name>} clause function 
   
   The function expects three mandatory clause tokens:
   • The first token represents the function ID and always takes the fixed value [GREATER.
   • The second token is the SQL column (or column combination) to be used in the clause.
   • The third token is the name of the report parameter that contains the value to compare to.
   If the parameter's value is not null, the function constructs a <column_name> >= ? clause. 
   If the parameter's value is null, the comparison with null will be neglected and the function generates a SQL clause that will always evaluate to true (e.g. 0 = 0). 
   
   Note: If the comparison with null (which always return false) should not be ignored, then one can use the <column_name> >= $P{<parameter_name>} instead. 
    
7. The ${BETWEEN, <column_name>, <left_parameter_name>, <right_parameter_name>} clause function 
   
   The function expects four mandatory clause tokens:
   • The first token represents the function ID and always takes the fixed value BETWEEN.
   • The second token is the SQL column (or column combination) to be used in the clause.
   • The third token is the name of the parameter that contains the left member value.
   • The fourth token is the name of the parameter that contains the right member value.
   If both parameter values are not null, the function constructs a double comparison, similar to the BETWEEN SQL clause where both interval endpoints are excluded: (<column_name> > ? AND <column_name> < ?) 
   If the left parameter's value is null, the function constructs a <column_name> < ? clause, using the right parameter's value at fill time. 
   If the right parameter's value is null, the function constructs a <column_name> > ? clause, using the left parameter's value at fill time. 
   If both parameter values are null, the function generates a SQL clause that will always evaluate to true (e.g. 0 = 0). 
    
8. The ${[BETWEEN, <column_name>, <left_parameter_name>, <right_parameter_name>} clause function 
   
   The function expects four mandatory clause tokens:
   • The first token represents the function ID and always takes the fixed value [BETWEEN.
   • The second token is the SQL column (or column combination) to be used in the clause.
   • The third token is the name of the parameter that contains the left member value.
   • The fourth token is the name of the parameter that contains the right member value.
   If both parameter values are not null, the function constructs a double comparison, similar to the BETWEEN SQL clause where the left interval endpoint is included and the right endpoint is excluded: (<column_name> >= ? AND <column_name> < ?) 
   If the left parameter's value is null, the function constructs a <column_name> < ? clause, using the right parameter's value at fill time. 
   If the right parameter's value is null, the function constructs a <column_name> >= ? clause, using the left parameter's value at fill time. 
   If both parameter values are null, the function generates a SQL clause that will always evaluate to true (e.g. 0 = 0). 
    
9. The ${BETWEEN], <column_name>, <left_parameter_name>, <right_parameter_name>} clause function 
   
   The function expects four mandatory clause tokens:
   • The first token represents the function ID and always takes the fixed value BETWEEN].
   • The second token is the SQL column (or column combination) to be used in the clause.
   • The third token is the name of the parameter that contains the left member value.
   • The fourth token is the name of the parameter that contains the right member value.
   If both parameter values are not null, the function constructs a double comparison, similar to the BETWEEN SQL clause where the left interval endpoint is excluded and the right endpoint is included: (<column_name> > ? AND <column_name> <= ?) 
   If the left parameter's value is null, the function constructs a <column_name> <= ? clause, using the right parameter's value at fill time. 
   If the right parameter's value is null, the function constructs a <column_name> > ? clause, using the left parameter's value at fill time. 
   If both parameter values are null, the function generates a SQL clause that will always evaluate to true (e.g. 0 = 0). 
    
10. The ${[BETWEEN], <column_name>, <left_parameter_name>, <right_parameter_name>} clause function 
    
    The function expects four mandatory clause tokens:
    • The first token represents the function ID and always takes the fixed value [BETWEEN].
    • The second token is the SQL column (or column combination) to be used in the clause.
    • The third token is the name of the parameter that contains the left member value.
    • The fourth token is the name of the parameter that contains the right member value.
    If both parameter values are not null, the function constructs a double comparison, similar to the BETWEEN SQL clause where both interval endpoints are included: (<column_name> >= ? AND <column_name> <= ?) 
    If the left parameter's value is null, the function constructs a <column_name> <= ? clause, using the right parameter's value at fill time. 
    If the right parameter's value is null, the function constructs a <column_name> >= ? clause, using the left parameter's value at fill time. 
    If both parameter values are null, the function generates a SQL clause that will always evaluate to true (e.g. 0 = 0). 
     
11. The ${IN, <column_name>, <parameter_name>} clause function 
    
    The function expects three mandatory clause tokens:
    • The first token represents the function ID and always takes the fixed value IN.
    • The second token is the SQL column (or column combination) to be used in the clause.
    • The third token is the name of the report parameter that contains the value list. The value of this parameter has to be an array, a java.util.Collection or null.
    If the parameter's value is a collection of not null values, the function constructs a <column_name> IN (?, ?, .., ?) clause 
    If the parameter's value is a collection containing both null and not null values, the function constructs a (<column_name> IS NULL OR <column_name> IN (?, ?, .., ?)) clause 
    If the parameter's value is a collection containing only null values, the function constructs a <column_name> IS NULL clause 
    If the parameter's value is null, the function generates a SQL clause that will always evaluate to true (e.g. 0 = 0). 
     
12. The ${NOTIN, <column_name>, <parameter_name>} clause function 
    
    The function expects three mandatory clause tokens:
    • The first token represents the function ID and always takes the fixed value NOTIN.
    • The second token is the SQL column (or column combination) to be used in the clause.
    • The third token is the name of the report parameter that contains the value list. The value of this parameter has to be an array, a java.util.Collection or null.
    If the parameter's value is a collection of not null values, the function constructs a <column_name> NOT IN (?, ?, .., ?) clause 
    If the parameter's value is a collection containing both null and not null values, the function constructs a (<column_name> IS NOT NULL AND <column_name> NOT IN (?, ?, .., ?)) clause 
    If the parameter's value is a collection containing only null values, the function constructs a <column_name> IS NOT NULL clause 
    If the parameter's value is null, the function generates a SQL clause that will always evaluate to true (e.g. 0 = 0).

SpagoBI e Jasper: parametri multi valore

La sintassi $X{ <IN o NOTIN>, <colonna>, <parametro>} con SpagoBI NON funziona...

Per utilizzare i parametri multi valore è necessario definire un driver anlitico su sSpagoBI di tipo stringa e associare come modalità di prompt la cheklist in modo da permettere all'utente di selezionare più valori

Nel report è necessario definire il parametro di tipo stringa e usarlo nella query in questo modo:

select * from xxx

where cod IN ($P!{codici})

SpagoBI - Installazione su Oracle

http://spagobi.blogspot.com/2010/06/how-to-config-spagobi-with-oracle.html



Modificare il dialetto hibernate nel file jbpm.hibernate.cfg.xml che si trova in \webapps\SpagoBI\WEB-INF\classes 
mettendo: org.hibernate.dialect.OracleDialect

vedere anche:

http://www.spagoworld.org/jforum/posts/list/255.page



This document records the steps to install and config the Oracle database connection as reporting data source in SpagoBI. 

You can config the database connection using JNDI resource that is shared between reporting applications; or you can config JDBC data source connection, so, a new JDBC connection (non-shareable) will be created for each of reporting applications (document) execution.


Environment
============
SpagoBI: version 2.5
OS Server: Windows server 2008 R2 (x64)
App Server: Tomcat 6.0.18 (in the SpagoBI demo package)
Oracle database: 11g R2 (for windows x64)
JDBC driver: ojdbc5.jar (for JDK 1.5 or up) 
Java: JDK 1.6.0_20

1. Create JNDI Data Source Connection

1) Download and unzip the file "Demo-SpagoBI-2.5.0-apache-tomcat-6.0.18-04092010.zip" to your local folder (liketomcathome\);
2) Copy the Oracle jdbc driver file "ojdbc5.jar" to your tomcathome\lib folder;
3) Edit the file "server.xml" at the folder "tomcathome\conf" and add / edit the following items:


4) Edit the file "context.xml" at the folder "tomcathome\webapps\SpagoBI\META-INF" as showing below:


5) Edit the file "context.xml" at the folder "tomcathome\webapps\SpagoBIJasperReportEngine\META-INF" as showing below:



6) Modificare in modo opportuno il dialetto hibernate nei file "jbpm.hibernate.cfg.xml" e "hibernate.cfg.xml" (lo trovi in \webapps\SpagoBI\WEB-INF\classes)

7) Start Tomcat server to enable the above changes;

8) Log on to SpagoBI at http://localhost:8080/SpagoBI to create a data source connection:

Click "Resources" -> "Data Source"


Click Insert button (icon) to add a new data source


Enter the settings for the new data source


Test the new connection before you save and exit.



2. Create JDBC Connection

1) Download and unzip the file "Demo-SpagoBI-2.5.0-apache-tomcat-6.0.18-04092010.zip" to your local folder (liketomcathome\);
2) Copy the Oracle jdbc driver file "ojdbc5.jar" to your tomcathome\lib folder;
3) Start Tomcat server to enable the above changes;
4) Log on to SpagoBI at http://localhost:8080/SpagoBI to create a data source connection:

Click "Resources" -> "Data Source"


Click Insert button (icon) to add a new data source


Enter the settings for the new data source


Test the new connection before you save and exit.