ReportServer Forum

Zergio

Member

Registered: 2016-07-01

Templates to link a Dynamic List with an existing Excel Sheet and Pivo

Hi,
In R.S. I obtain a Dynamic list containing Computed columns(and other columns of my tables SYBASE ANYWARE) and Subtotals such as:
DEPARTT NUMBER (French Geographic department from 01 to 97)/CA_2014 (Sales realized on year 2014)/CA_2015/DELTA (differences between sales of 2015 and 2014).The Subtotal concerns the Department number and i have indicated SUM on aggregates for CA_2014, CA_2015 and DELTA).

I have built an Excel Sheet in an XLS Workbook  with the same columns and added other columns as Area of each department and Previsional Sales of each Year.

I have clicked on EDIT TEMPLATE button, then ADD TEMPLATE ans choose my XLS Workbook by the Browser (with JXLS type)
Then on the top of my dynamic list i have clicked on EXPORT TEMPLATES Button, but no result in my Excel Sheet....

I suppose i have omitted parameters in Excel ...... Can you help me ?

Another question : How to do Diagrams and Customermap in R.S. With France Departments ?
I try to click on PIVOT Button , then drag & drop CA_2014 and CA_2015 in COLUMNS and Number DEPARTT in ROWS and I obtain an error message below:
SQL EXCEPTION -SQL ANYWARE  ERROR 131-SYNTAX ERROR NEAR "!" ON LINE 3
Thank you for your help.
Best regards

Serge

Thomas Davies

datenwerke

Registered: 2016-05-18

Re: Templates to link a Dynamic List with an existing Excel Sheet and Pivo

Hi Serge,

what exactly happens, when you try to export the Dynamic List to the template? Do you get any error message, or anything at all? Could you check the log files after you did an export. If you used the Bitnami installer,
then you should find the log files in

INSTALLATION_DIR/apache-tomcat/logs/

As for the error in the pivot mode you would also need to check the logs, but my guess is that Sybase does not like the queries generated by the pivot mode. An alternative for producing charts would be to use a BIRT report, or a script report,
or an html dadget. For the latter an introduction is given on

https://reportserver.net/blog/2013/10/1 … dashboard/

and on

https://forum.reportserver.net/viewtopic.php?pid=2351

there is the outline of a script report that uses the Google Charts API.

Best regards,
Thomas

Zergio

Member

Registered: 2016-07-01

Re: Templates to link a Dynamic List with an existing Excel Sheet and Pivo

Hi Thomas,

Thank you for your answer.
Let's concentrate on Templates, because Pivot is less important and possible within my Excel 2016.
I try to advance step by step, because i do not clearly understand how to realize a template from a dynamic list, in view to read the result in an Excel sheet.What's more i cannot succeed in sending you Printscreens from my Mac...
This is my first stages concerning the parameters:
1-OK for My Dynamic list in RS (name: DEGAMI CA FACT DEPAT PAYS:DEG CALCUL SOUS TOTTX DEPART CA2014 et CA2015) wich contains Columns of 2 tables (DBA.PCV and DBA.ADP).
I would like to recuperate in Excel only a few computed columns ( names: N°_DEPART,YEAR, CA_2014, CA_2015(sales) AND DELTA)
2-there are many rows for each  departement  number, so I want only the SUM of Sales (CA2014 and CA2015) by departement. It is why I ask a Subtotal by department (is it correct ?), and I have indicated SUM on each numeric columns CA2014, CA2015 and DELTA
3-I have downloaded JXLS-CORE and JXLS-READER (V1 in accordance with the User Guide). But Where is the CLASSPATH to paste these 2 files?
4-To reply to your question, when I click on template Export, the Message is " Report is generated as a table Template..." But no informations writed in my Excel Sheet...
5-In the button EDIT template , what can i browse in the field "Upload template " : My Excel Workbook ?
6- In my Excel Sheets , where can i write instructions : <jx for each item $data... $(department.name..,etc.. ? What is my "data" and my columns names ?
7-How to indicate only my subtotal(of each department, because i do not want the detail rows of my Dynamic list )?
Sorry for my english mistakes.I hope my explanations are clear.
Thanks for your help and time.
Best regards.

Serge

Thomas Davies

datenwerke

Registered: 2016-05-18

Re: Templates to link a Dynamic List with an existing Excel Sheet and Pivo

Hi Serge,

I think you are somewhat on the wrong track here. For starters, there is no need to install any additional libraries to use templates. Let me try to give you a step by step introduction to how to use templates.

1) First Step. Create a Dynamic List and select the columns that you want to use. In the example, we use the T_AGG_ORDER example report and select the PL_PRODUCTLINE and OD_QUANTITYORDERED columns.
(Sorry, the images are a bit big ..)

A preview of the data shows us that the data consists of a productline and quantity for each record. The data is not sorted in any way

2) We can now prepare the template. This is done in Excel and you can use the templating instructions as discussed on http://jxls.sourceforge.net/1.x/reference/tags.html. Also have a look at https://reportserver.net/en/guides/user … #Templates. An example template could be the following (where | denotes the end of a cell)

<jx:forEach items="${data}" groupBy="PL_PRODUCTLINE">	
${group.item.PL_PRODUCTLINE} |	$[SUM(B7)]
<jx:outline>	
<jx:forEach items="${group.items}" var="prod">	
${prod.PL_PRODUCTLINE} | ${prod.OD_QUANTITYORDERED}
</jx:forEach>	
</jx:outline>	
</jx:forEach>

3) Now we need to upload the template. For this we go to the Configure list part of the Dynamic list and go to Edit template. We there create a new template, choose JXLS as format and upload the previously created excel document.

4) We are almost there. Now the Dynamic List offers a new export format (called Template). With the example template, the output would be the following

I hoped this helped.

Best regards,
Thomas

quintg

Member

Registered: 2016-10-19

Re: Templates to link a Dynamic List with an existing Excel Sheet and Pivo

Hello Thomas,

Thanks for this detailed example. I managed to set up a template with just a plain list, so without grouping. But when I exactly repeat the above steps I get the following error:

    Error: An error occurred during report execution.

    The report could not be executed: Can't parse an expression group.items

    To get help please contact an administrator.

    Details

    net.datenwerke.rs.core.service.reportmanager.exceptions.ReportExecutorException: The report could not be executed: Can't parse an expression
    group.items at net.datenwerke.rs.tabletemplate.service.engines.jxls.hookers.JxlsTransformEnvironment.call(JxlsTransformEnvironment.java:70) at
    ...

I am using the 64 bits reportserverenterprise-3.0.2-3 from bitnami.

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Templates to link a Dynamic List with an existing Excel Sheet and Pivo

Hi quintg,

if you don't have grouping, you cannot use the expression group.items -> you don't have any group.
For this, just use the expression ${data}, as in: <jx:forEach items="${data}">, for example:

<jx:forEach items="${data}" var="prod">	
${prod.PL_PRODUCTLINE} | ${prod.OD_QUANTITYORDERED}
</jx:forEach>	

Cheers,
Eduardo