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
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:
Create CSV Datasource and select “Text connector”.
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.
Apply
Create a dynamic list
Select the “Demo data” Datasource with query:
SELECT * FROM T_AGG_ORDER
Select your CSV Datasource as “Metadata datasource”
Leave the query wrapper empty
Apply
Double click on your dynamic list
“Select columns”
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.
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!
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 ")"<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 ")"<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…
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?
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)…
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.
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.
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.
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?
