Eclipse Community Forums: BIRT » Dynamic Sql query in birt

Home » Archived » BIRT » Dynamic Sql query in birt(how to create dynamic sql query)

Dynamic Sql query in birt [message #548764]

Thu, 22 July 2010 18:20

Eclipse User

I have a scenario where I need to create sql query dynamically based on table columns.

That is, I am passing in a 'report id' as parameter to query.....then query fetches all 'report parameters' for that report id and gets 'report details' for each of the report parameter. The problem is report details table should be picked dynamically based on the 'report level' column in the report param table.
for example:
if value for 'report level'= ABC in reportparam table, then details table is ABC_report_detals and so on.

is there any way i can do it with property binder?

Any help much appreciated!

Re: Dynamic Sql query in birt [message #548935 is a reply to message #548764]

Fri, 23 July 2010 09:41

Eclipse User

Do all the report parameter detail tables have the same name and number
of columns?

On 7/22/2010 6:20 PM, Smitha wrote:
> I have a scenario where I need to create sql query dynamically based on
> table columns.
> That is, I am passing in a 'report id' as parameter to query.....then
> query fetches all 'report parameters' for that report id and gets
> 'report details' for each of the report parameter. The problem is report
> details table should be picked dynamically based on the 'report level'
> column in the report param table. for example:
> if value for 'report level'= ABC in reportparam table, then details
> table is ABC_report_detals and so on.
>
> is there any way i can do it with property binder?
>
> Any help much appreciated!

Re: Dynamic Sql query in birt [message #548960 is a reply to message #548935]

Fri, 23 July 2010 10:36

Eclipse User

yes, all param details tables have exactly same column names (atleast for the columns i need information from)

Re: Dynamic Sql query in birt [message #549003 is a reply to message #548960]

Fri, 23 July 2010 11:54

Eclipse User

You should be able to do this with a beforeOpen script on the dataset.
Something like:
this.queryText = "Select * from " +
params["parmtable"].value+"_report_details";

Jason

On 7/23/2010 10:36 AM, Smitha wrote:
> yes, all param details tables have exactly same column names (atleast
> for the columns i need information from)

Re: Dynamic Sql query in birt [message #549007 is a reply to message #549003]

Fri, 23 July 2010 12:27

Eclipse User

yes i could...but whats confusing is that params["parmtable"].value is determined by another column in report_params table..

report_params table has a column named 'report_level' ....so on a high-level the query has to be something like:

A 'report' has many report-params and for each report-param do the following:
if report_level= 'ABC' then select from table 'ABC_details'
if report_level ='XYZ' then select from table 'XYZ_details' and so on

so how can i set that condition for a parameter?

Re: Dynamic Sql query in birt [message #549029 is a reply to message #549007]

Fri, 23 July 2010 13:29

Eclipse User

You could do this with nested tables. Take a look at the attached
example. It does not change the table but that should be doable with
the same scheme.

Jason

<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.21"
id="1">
<property name="createdBy">Eclipse BIRT Designer Version
2.5.2.v20100208 Build <2.5.2.v20100210-0630></property>
<property name="units">in</property>
<property name="iconFile">/templates/blank_report.gif</property>
<property name="bidiLayoutOrientation">ltr</property>
<property name="imageDPI">96</property>
<data-sources>
<oda-data-source
extensionID="org.eclipse.birt.report.data.oda.jdbc" name="Data Source"
id="7">
<text-property name="displayName"></text-property>
<property
name="odaDriverClass">org.eclipse.birt.report.data.oda.sampledb.Driver </property>
<property name="odaURL">jdbc:classicmodels:sampledb</property>
<property name="odaUser">ClassicModels</property>
<property name="OdaConnProfileName"></property>
</oda-data-source>
</data-sources>
<data-sets>
<oda-data-set
extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet "
name="outer" id="8">
<list-property name="columnHints">
<structure>
<property name="columnName">PRODUCTLINE</property>
<text-property
name="displayName">PRODUCTLINE</text-property>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">PRODUCTLINE</property>
<property name="dataType">string</property>
</structure>
</list-property>
</structure>
<property name="dataSource">Data Source</property>
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">PRODUCTLINE</property>
<property name="nativeName">PRODUCTLINE</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
</list-property>
<xml-property name="queryText"><![CDATA[select
CLASSICMODELS.PRODUCTLINES.PRODUCTLINE
from CLASSICMODELS.PRODUCTLINES]]></xml-property>
<xml-property name="designerValues"><![CDATA[<?xml
version="1.0" encoding="UTF-8"?>
<model:DesignValues
xmlns:design="http://www.eclipse.org/datatools/connectivity/oda/design"
xmlns:model="http://www.eclipse.org/birt/report/model/adapter/odaModel">
<Version>1.0</Version>
<design:ResultSets derivedMetaData="true">
<design:resultSetDefinitions>
<design:resultSetColumns>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>PRODUCTLINE</design:name>
<design:position>1</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>50</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>PRODUCTLINE</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>PRODUCTLINE</design:label>
<design:formattingHints>
<design:displaySize>50</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
</design:resultSetColumns>
<design:criteria/>
</design:resultSetDefinitions>
</design:ResultSets>
</model:DesignValues>
]]></xml-property>
</oda-data-set>
<oda-data-set
extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet "
name="inner" id="9">
<list-property name="columnHints">
<structure>
<property name="columnName">PRODUCTCODE</property>
<text-property
name="displayName">PRODUCTCODE</text-property>
</structure>
<structure>
<property name="columnName">PRODUCTNAME</property>
<text-property
name="displayName">PRODUCTNAME</text-property>
</structure>
<structure>
<property name="columnName">PRODUCTLINE</property>
<text-property
name="displayName">PRODUCTLINE</text-property>
</structure>
<structure>
<property name="columnName">PRODUCTSCALE</property>
<text-property
name="displayName">PRODUCTSCALE</text-property>
</structure>
<structure>
<property name="columnName">PRODUCTVENDOR</property>
<text-property
name="displayName">PRODUCTVENDOR</text-property>
</structure>
<structure>
<property
name="columnName">PRODUCTDESCRIPTION</property>
<text-property
name="displayName">PRODUCTDESCRIPTION</text-property>
</structure>
<structure>
<property name="columnName">QUANTITYINSTOCK</property>
<text-property
name="displayName">QUANTITYINSTOCK</text-property>
</structure>
<structure>
<property name="columnName">BUYPRICE</property>
<text-property
name="displayName">BUYPRICE</text-property>
</structure>
<structure>
<property name="columnName">MSRP</property>
<text-property name="displayName">MSRP</text-property>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">PRODUCTCODE</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">PRODUCTNAME</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">PRODUCTLINE</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">4</property>
<property name="name">PRODUCTSCALE</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">5</property>
<property name="name">PRODUCTVENDOR</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">6</property>
<property name="name">PRODUCTDESCRIPTION</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">7</property>
<property name="name">QUANTITYINSTOCK</property>
<property name="dataType">integer</property>
</structure>
<structure>
<property name="position">8</property>
<property name="name">BUYPRICE</property>
<property name="dataType">float</property>
</structure>
<structure>
<property name="position">9</property>
<property name="name">MSRP</property>
<property name="dataType">float</property>
</structure>
</list-property>
</structure>
<method name="beforeOpen"><![CDATA[var pl =
reportContext.getGlobalVariable("cp");
this.queryText = "Select * from products where productline = '"+pl+"'";
]]></method>
<property name="dataSource">Data Source</property>
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">PRODUCTCODE</property>
<property name="nativeName">PRODUCTCODE</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">PRODUCTNAME</property>
<property name="nativeName">PRODUCTNAME</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">PRODUCTLINE</property>
<property name="nativeName">PRODUCTLINE</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">4</property>
<property name="name">PRODUCTSCALE</property>
<property name="nativeName">PRODUCTSCALE</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">5</property>
<property name="name">PRODUCTVENDOR</property>
<property name="nativeName">PRODUCTVENDOR</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">6</property>
<property name="name">PRODUCTDESCRIPTION</property>
<property
name="nativeName">PRODUCTDESCRIPTION</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">7</property>
<property name="name">QUANTITYINSTOCK</property>
<property name="nativeName">QUANTITYINSTOCK</property>
<property name="dataType">integer</property>
<property name="nativeDataType">4</property>
</structure>
<structure>
<property name="position">8</property>
<property name="name">BUYPRICE</property>
<property name="nativeName">BUYPRICE</property>
<property name="dataType">float</property>
<property name="nativeDataType">8</property>
</structure>
<structure>
<property name="position">9</property>
<property name="name">MSRP</property>
<property name="nativeName">MSRP</property>
<property name="dataType">float</property>
<property name="nativeDataType">8</property>
</structure>
</list-property>
<xml-property name="queryText"><![CDATA[select *
from CLASSICMODELS.PRODUCTS
]]></xml-property>
<xml-property name="designerValues"><![CDATA[<?xml
version="1.0" encoding="UTF-8"?>
<model:DesignValues
xmlns:design="http://www.eclipse.org/datatools/connectivity/oda/design"
xmlns:model="http://www.eclipse.org/birt/report/model/adapter/odaModel">
<Version>1.0</Version>
<design:ResultSets derivedMetaData="true">
<design:resultSetDefinitions>
<design:resultSetColumns>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>PRODUCTCODE</design:name>
<design:position>1</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>15</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>PRODUCTCODE</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>PRODUCTCODE</design:label>
<design:formattingHints>
<design:displaySize>15</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>PRODUCTNAME</design:name>
<design:position>2</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>70</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>PRODUCTNAME</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>PRODUCTNAME</design:label>
<design:formattingHints>
<design:displaySize>70</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>PRODUCTLINE</design:name>
<design:position>3</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>50</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>PRODUCTLINE</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>PRODUCTLINE</design:label>
<design:formattingHints>
<design:displaySize>50</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>PRODUCTSCALE</design:name>
<design:position>4</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>10</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>PRODUCTSCALE</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>PRODUCTSCALE</design:label>
<design:formattingHints>
<design:displaySize>10</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>PRODUCTVENDOR</design:name>
<design:position>5</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>50</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>PRODUCTVENDOR</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>PRODUCTVENDOR</design:label>
<design:formattingHints>
<design:displaySize>50</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>PRODUCTDESCRIPTION</design:name>
<design:position>6</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>32700</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>PRODUCTDESCRIPTION</design:displayName >
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>PRODUCTDESCRIPTION</design:label>
<design:formattingHints>
<design:displaySize>32700</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>QUANTITYINSTOCK</design:name>
<design:position>7</design:position>
<design:nativeDataTypeCode>4</design:nativeDataTypeCode>
<design:precision>10</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>QUANTITYINSTOCK</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>QUANTITYINSTOCK</design:label>
<design:formattingHints>
<design:displaySize>11</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>BUYPRICE</design:name>
<design:position>8</design:position>
<design:nativeDataTypeCode>8</design:nativeDataTypeCode>
<design:precision>15</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>BUYPRICE</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>BUYPRICE</design:label>
<design:formattingHints>
<design:displaySize>22</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>MSRP</design:name>
<design:position>9</design:position>
<design:nativeDataTypeCode>8</design:nativeDataTypeCode>
<design:precision>15</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>MSRP</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>MSRP</design:label>
<design:formattingHints>
<design:displaySize>22</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
</design:resultSetColumns>
<design:criteria/>
</design:resultSetDefinitions>
</design:ResultSets>
</model:DesignValues>]]></xml-property>
</oda-data-set>
</data-sets>
<styles>
<style name="report" id="4">
<property name="fontFamily">sans-serif</property>
<property name="fontSize">10pt</property>
</style>
<style name="crosstab-cell" id="5">
<property name="borderBottomColor">#CCCCCC</property>
<property name="borderBottomStyle">solid</property>
<property name="borderBottomWidth">1pt</property>
<property name="borderLeftColor">#CCCCCC</property>
<property name="borderLeftStyle">solid</property>
<property name="borderLeftWidth">1pt</property>
<property name="borderRightColor">#CCCCCC</property>
<property name="borderRightStyle">solid</property>
<property name="borderRightWidth">1pt</property>
<property name="borderTopColor">#CCCCCC</property>
<property name="borderTopStyle">solid</property>
<property name="borderTopWidth">1pt</property>
</style>
<style name="crosstab" id="6">
<property name="borderBottomColor">#CCCCCC</property>
<property name="borderBottomStyle">solid</property>
<property name="borderBottomWidth">1pt</property>
<property name="borderLeftColor">#CCCCCC</property>
<property name="borderLeftStyle">solid</property>
<property name="borderLeftWidth">1pt</property>
<property name="borderRightColor">#CCCCCC</property>
<property name="borderRightStyle">solid</property>
<property name="borderRightWidth">1pt</property>
<property name="borderTopColor">#CCCCCC</property>
<property name="borderTopStyle">solid</property>
<property name="borderTopWidth">1pt</property>
</style>
</styles>
<page-setup>
<simple-master-page name="Simple MasterPage" id="2">
<page-footer>
<text id="3">
<property name="contentType">html</property>
<text-property
name="content"><![CDATA[<value-of>new Date()</value-of>]]></text-property>
</text>
</page-footer>
</simple-master-page>
</page-setup>
<body>
<label id="82">
<text-property name="text">Look at the expression for
product line in red table. look at the beforeOpen script on the inner
dataset.</text-property>
</label>
<table id="10">
<property name="backgroundColor">#FF8080</property>
<property name="dataSet">outer</property>
<list-property name="boundDataColumns">
<structure>
<property name="name">PRODUCTLINE</property>
<text-property
name="displayName">PRODUCTLINE</text-property>
<expression name="expression"
type="javascript">reportContext.setGlobalVariable( "cp",
dataSetRow["PRODUCTLINE"]);
dataSetRow["PRODUCTLINE"];</expression>
<property name="dataType">string</property>
</structure>
</list-property>
<column id="19"/>
<column id="23"/>
<header>
<row id="11">
<cell id="12">
<label id="13">
<text-property
name="text">PRODUCTLINE</text-property>
</label>
</cell>
<cell id="20"/>
</row>
</header>
<detail>
<row id="14">
<cell id="15">
<data id="16">
<property
name="resultSetColumn">PRODUCTLINE</property>
</data>
</cell>
<cell id="21">
<table id="24">
<property
name="backgroundColor">#80FF80</property>
<property name="width">3.90625in</property>
<property name="dataSet">inner</property>
<list-property name="boundDataColumns">
<structure>
<property
name="name">PRODUCTCODE</property>
<text-property
name="displayName">PRODUCTCODE</text-property>
<expression name="expression"
type="javascript">dataSetRow["PRODUCTCODE"]</expression >
<property
name="dataType">string</property>
</structure>
<structure>
<property
name="name">PRODUCTNAME</property>
<text-property
name="displayName">PRODUCTNAME</text-property>
<expression name="expression"
type="javascript">dataSetRow["PRODUCTNAME"]</expression >
<property
name="dataType">string</property>
</structure>
<structure>
<property
name="name">PRODUCTLINE</property>
<text-property
name="displayName">PRODUCTLINE</text-property>
<expression name="expression"
type="javascript">dataSetRow["PRODUCTLINE"]</expression >
<property
name="dataType">string</property>
</structure>
<structure>
<property
name="name">PRODUCTSCALE</property>
<text-property
name="displayName">PRODUCTSCALE</text-property>
<expression name="expression"
type="javascript">dataSetRow["PRODUCTSCALE"]</expression >
<property
name="dataType">string</property>
</structure>
<structure>
<property
name="name">PRODUCTVENDOR</property>
<text-property
name="displayName">PRODUCTVENDOR</text-property>
<expression name="expression"
type="javascript">dataSetRow["PRODUCTVENDOR"]</expression >
<property
name="dataType">string</property>
</structure>
<structure>
<property
name="name">PRODUCTDESCRIPTION</property>
<text-property
name="displayName">PRODUCTDESCRIPTION</text-property>
<expression name="expression"
type="javascript">dataSetRow["PRODUCTDESCRIPTION"]</expression >
<property
name="dataType">string</property>
</structure>
<structure>
<property
name="name">QUANTITYINSTOCK</property>
<text-property
name="displayName">QUANTITYINSTOCK</text-property>
<expression name="expression"
type="javascript">dataSetRow["QUANTITYINSTOCK"]</expression >
<property
name="dataType">integer</property>
</structure>
<structure>
<property
name="name">BUYPRICE</property>
<text-property
name="displayName">BUYPRICE</text-property>
<expression name="expression"
type="javascript">dataSetRow["BUYPRICE"]</expression>
<property
name="dataType">float</property>
</structure>
<structure>
<property name="name">MSRP</property>
<text-property
name="displayName">MSRP</text-property>
<expression name="expression"
type="javascript">dataSetRow["MSRP"]</expression>
<property
name="dataType">float</property>
</structure>
</list-property>
<column id="74">
<property
name="width">1.1458333333333333in</property>
</column>
<column id="75">
<property
name="width">0.4791666666666667in</property>
</column>
<header>
<row id="25">
<cell id="28">
<label id="29">
<text-property
name="text">PRODUCTNAME</text-property>
</label>
</cell>
<cell id="30">
<label id="31">
<text-property
name="text">PRODUCTLINE</text-property>
</label>
</cell>
</row>
</header>
<detail>
<row id="44">
<cell id="47">
<data id="48">
<property
name="resultSetColumn">PRODUCTNAME</property>
</data>
</cell>
<cell id="49">
<data id="50">
<property
name="resultSetColumn">PRODUCTLINE</property>
</data>
</cell>
</row>
</detail>
<footer>
<row id="63">
<cell id="65"/>
<cell id="66"/>
</row>
</footer>
</table>
</cell>
</row>
</detail>
<footer>
<row id="17">
<cell id="18"/>
<cell id="22"/>
</row>
</footer>
</table>
</body>
</report>

On 7/23/2010 12:27 PM, Smitha wrote:
> yes i could...but whats confusing is that params["parmtable"].value is
> determined by another column in report_params table..
> report_params table has a column named 'report_level' ....so on a
> high-level the query has to be something like:
> A 'report' has many report-params and for each report-param do the
> following:
> if report_level= 'ABC' then select from table 'ABC_details'
> if report_level ='XYZ' then select from table 'XYZ_details' and so on
>
> so how can i set that condition for a parameter?
>
>

Previous Topic:	Aggregating Percentage
Next Topic:	iText

Goto Forum:

-=] Back to Top [=-

Current Time: Wed Jul 23 14:41:53 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter