ReportServer Forum

Andrew

Member

From: Saint-Petersburg, Russia

Registered: 2017-03-09

csv file as metadata

Good day,

I have the problem with understanding how this things work. Hope you can help me and end my suffering. I will write all my steps, so it will be not so difficult to find where i am mistaken.
Firstly, i went to Datasources in admin area and created a CSV list, gave it a name, choosed the text connector and  hitted enter.Then ckicked apply.
After that i went to the report managment and created a dynamic list, gave it a name, wrote my query, choosed datasource, after that i choosed as metadata datasource the csv file that i have just created.
And at this part i met my problem - what i have to write in Query wrapper, so it will work? Without metadata datasource everything works but not in the way i need.
Andrew,
Regards

Last edited by Andrew (2017-03-09 13:34:02)

eduardo

Administrator

Registered: 2016-11-01

Website

Re: csv file as metadata

Hi Andrew,

what exactly is not working? What exact reportserver version are you trying with? I tried the following steps with the demo database and it is working:

1. Create CSV Datasource and select "Text connector".
2. Enter the following into the text field:

COLUMN_NAME;DEFAULT_ALIAS;DESCRIPTION
OD_ORDERLINENUMBER;olinenumber;The order line number
OD_ORDERNUMBER;onumber;The order number

Here, we define the metadata for two columns: OD_ORDERLINENUMBER and OD_ORDERNUMBER.

3. Apply
4. Create a dynamic list

5. Select the "Demo data" Datasource with query:

SELECT * FROM T_AGG_ORDER

6. Select your CSV Datasource as "Metadata datasource"
7. Leave the query wrapper empty
8. Apply
9. Double click on your dynamic list
10. "Select columns"
11. Select the columns  OD_ORDERLINENUMBER and OD_ORDERNUMBER.

You should see the default alias and the description here.
In the report preview, the column names should also be the ones defined in your CSV datasource.

Cheers,
Eduardo

Andrew

Member

From: Saint-Petersburg, Russia

Registered: 2017-03-09

Re: csv file as metadata

Hello Eduardo! Thanks for your response! It is really informative and useful.

Actually, by the time you answered i already have found the solution for my issue. But still thanks!
As it turned out i didn't read properly your BLOG, this is why i was missing this : ($!{TMP_TABLENAME}).So when I figured it out I wrote in the query wrapper:
SELECT  column_name, default_alias, description
FROM ($!{TMP_TABLENAME})
Everything started to work! But still thanks for your help!

Last edited by Andrew (2017-03-16 08:45:14)

Patryx

Member

Registered: 2019-03-25

Re: csv file as metadata

Hi Eduardo,
I have similar scenario with a small difference: I have CSV datasource with Argument Connector (maybe that's problem for query wrapper).

You wrote:
"7. Leave the query wrapper empty".
In this case it's ok, but I want to ask you how can I use Query Wrapper here with CSV datasource?

As Andrew wrote I tried to write there:

SELECT COLUMN_NAME, DEFAULT_ALIAS, DESCRIPTION
FROM ($!{TMP_TABLENAME})

but I got an error when executed report and went to Configue list->Selected Column:

net.datenwerke.gxtdto.client.servercommunication.exceptions.NonFatalException: Could not load Metadata
<br>	at net.datenwerke.rs.base.service.reportengines.table.utils.TableReportColumnMetadataServiceImpl.createColumnMetadataMap(TableReportColumnMetadataServiceImpl.java:86)
<br>	at net.datenwerke.rs.base.service.reportengines.table.TableReportUtilsImpl.getReturnedColumns(TableReportUtilsImpl.java:174)
<br>	at net.datenwerke.rs.base.service.reportengines.table.TableReportUtilsImpl.getReturnedPlainColumns(TableReportUtilsImpl.java:144)
<br>	at net.datenwerke.rs.base.server.table.TableReportUtilityServiceImpl.getReturnedColumns(TableReportUtilityServiceImpl.java:217)
<br>	at com.google.inject.persist.jpa.JpaLocalTxnInterceptor.invoke(JpaLocalTxnInterceptor.java:66)
<br>	at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
<br>	at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
<br>	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
<br>	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
<br>	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
<br>	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
<br>	at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:587)
<br>	at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:333)
<br>	at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
<br>	at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
<br>	at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:303)
<br>	at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
<br>	at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
<br>	at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:373)
<br>	at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
<br>	at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
<br>	at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
<br>	at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
<br>	at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
<br>	at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
<br>	at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
<br>	at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
<br>	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
<br>	at com.google.inject.persist.PersistFilter.doFilter(PersistFilter.java:89)
<br>	at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163)
<br>	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
<br>	at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:168)
<br>	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
<br>	at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:168)
<br>	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
<br>	at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
<br>	at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
<br>	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
<br>	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
<br>	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:200)
<br>	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
<br>	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490)
<br>	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
<br>	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
<br>	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:678)
<br>	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
<br>	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
<br>	at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:394)
<br>	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
<br>	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:836)
<br>	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1747)
<br>	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
<br>	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
<br>	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
<br>	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
<br>	at java.base/java.lang.Thread.run(Thread.java:834)
<br>Caused by: net.datenwerke.rs.core.service.reportmanager.exceptions.ReportExecutorException: Query could not be executed: ERROR: syntax error at or near &quot;)&quot;<br>  Pozycja: 99
<br>	at net.datenwerke.rs.base.service.datasources.table.impl.TableDBDataSource.open(TableDBDataSource.java:276)
<br>	at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:430)
<br>	at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:422)
<br>	at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:154)
<br>	at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:124)
<br>	at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:116)
<br>	at net.datenwerke.rs.base.service.reportengines.table.utils.TableReportColumnMetadataServiceImpl.createColumnMetadataMap(TableReportColumnMetadataServiceImpl.java:64)
<br>	... 55 more
<br>Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near &quot;)&quot;<br>  Pozycja: 99
<br>	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2284)
<br>	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2003)
<br>	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:200)
<br>	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424)
<br>	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:161)
<br>	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
<br>	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:1418)
<br>	at net.datenwerke.rs.base.service.datasources.table.impl.TableDBDataSource.open(TableDBDataSource.java:271)
<br>	... 61 more
<br>

I need such information because I am looking for a solution how to show user default_alias and description according to chosen locale (language) so I will need Query Wrapper to write query with WHERE where I can filter rows by language...

Last edited by Patryx (2019-06-04 09:41:07)

eduardo

Administrator

Registered: 2016-11-01

Website

Re: csv file as metadata

Hi Patryx,

if you leave everything exactly as it is, but only change "Argument connector" to "Text connector" -> Does it work ?

Regards,
Eduardo

eduardo

Administrator

Registered: 2016-11-01

Website

Re: csv file as metadata

Hi Patryx,

In your error I see: syntax error at or near &quot;)&quot;<br>  Pozycja: 99

This seems to be a postgresql error. Please double check your query. For example, that you don't have any " ; " characters or similar.

Regards,
Eduardo

Patryx

Member

Registered: 2019-03-25

Re: csv file as metadata

Hi Eduardo,
Text connector does not work too...

As I said if I remove Query Wrapper everything works fine.

If I add in Query Wrapper:

SELECT COLUMN_NAME,DEFAULT_ALIAS,DESCRIPTION
FROM ($!{TMP_TABLENAME})

I get such an error as in my previous post.
If there was a problem with my postgresql it would not work at all.

Patryx

Member

Registered: 2019-03-25

Re: csv file as metadata

What's more can I use PARAMETER in Query wrapper?!
How can I get information about current language (locale) to for example add special WHERE conditions?

Last edited by Patryx (2019-06-04 11:12:10)

eduardo

Administrator

Registered: 2016-11-01

Website

Re: csv file as metadata

Hi Patryx,

can you post some screenshots of your configuration? The configuration not working.

Regards,
Eduardo

Patryx

Member

Registered: 2019-03-25

Re: csv file as metadata

Ok, I adapted query to your AUDIT_LOG, of course I get the same error as before.
Screens:

If I change to:

SELECT COLUMN_NAME,DEFAULT_ALIAS,DESCRIPTION
FROM ($!{TMP_TABLENAME})

I get the same error.
I mentioned about it because I noticed that in Data in CSV List I need to use small letters as a column_name (even if there are used big letters as in your Audit_log query)...

If I remove Query Wrapper, it works.

Last edited by Patryx (2019-06-04 12:12:24)

Patryx

Member

Registered: 2019-03-25

Re: csv file as metadata

What about parameters in Query Wrapper?
How to get current language (locale) in Query wrapper and in report PARAMETERS?

eduardo

Administrator

Registered: 2016-11-01

Website

Re: csv file as metadata

Text connector does not work too...

Hi Patryx,

if you perform the exact steps described here: https://forum.reportserver.net/viewtopi … 3194#p3194 , it doesn't work ?

For being able to find the problem please perform the exact steps here. Since you mention that text connector does not work too, it would be helpful to find out if this does not work by performing the exact steps mentioned.

Regards,
Eduardo

eduardo

Administrator

Registered: 2016-11-01

Website

Re: csv file as metadata

Hi Patryx,

try with this:

SELECT COLUMN_NAME, DEFAULT_ALIAS, DESCRIPTION
FROM $!{TMP_TABLENAME} WHERE DEFAULT_ALIAS = 'My_key_field'

"I mentioned about it because I noticed that in Data in CSV List I need to use small letters as a column_name (even if there are used big letters as in your Audit_log query)..."
For postgresql, everything is in small letters.  I tested in mssql server, so you may need to adapt this to postgresql.

Regards,
Eduardo

Patryx

Member

Registered: 2019-03-25

Re: csv file as metadata

Hi Eduardo,
I have already done steps exactly as you wrote here https://forum.reportserver.net/viewtopi … 3194#p3194.
This example works.

Now I just added and applied in query wrapper:
SELECT COLUMN_NAME, DEFAULT_ALIAS, DESCRIPTION
FROM $!{TMP_TABLENAME}

I got the same error in Configurate list->Select columns as before:
"...
<br>Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near &quot;)&quot;<br>  Pozycja: 97
...

There must be an error in mechanism for building right query for Postgresql via query wrapper for CSV datasource.

Last edited by Patryx (2019-06-04 13:03:56)

eduardo

Administrator

Registered: 2016-11-01

Website

Re: csv file as metadata

Hi Patryx,

I just tested with postgres with the configuration in the screenshot and it works. Please check again.

I noticed that in order to reload the data after a change, you have to select another node, and then reselect the actual report. So after each change please select "Report Root" and then reselect your report.

Regards,
Eduardo

Patryx

Member

Registered: 2019-03-25

Re: csv file as metadata

It works
I noticed that refreshing was not working properly but I closed and opened report again
Problem was ($!{TMP_TEMPLATE}), it should be without ()...
Andrew was wrong here...

Thanks Eduardo.
To continue I have written a new post here:
https://forum.reportserver.net/viewtopic.php?id=1447
There are quite important things to me because I need report in few languages. Reports are going to have parameters so I need not only DEFAULT_ALIAS and DESCRIPTION texts depending on locale (language) but also parameter name/description which are shown to users. I cannot find a right solution (creating different reports with variants for each language seems to be totally inconvenient solution).
Could you help me with this issue, Eduardo?

Last edited by Patryx (2019-06-04 13:42:17)