You are not logged in.
NOTE: this has been solved. I wanted essentially a key value pair where key is what end user sees and value is what is returned in the exported table. I found on pg 82 of the admin manual the answer.
--------------------------
I did a multiselect on a dynamic list where 202408 and 202501 would be example seasons that user will select
SELECT A from C
WHERE $X{IN, COLUMN_NAME, values}
becomes
SELECT A FROM C
WHERE COLUMN_NAME IN ('202408', '202501')
I was asked to make the fields more descriptive, so i concatenated the main field with the description.
Now when picking a season it says this,
(202408) Fall 2024
(202501) Winter 2025
But, the original query obviously won't work as it would basically say
SELECT A FROM C
WHERE COLUMN_NAME IN ('(202408) Fall 2024', '(202501) Winter 2025')
I wanted to REGEX the new strings because that worked when it was a single select from a dropdown, but how does that even work with something like this?
The below fails. I will get the error:
Query could not be prepared: Error preparing statement for executing the report query : SELECT * FROM (SELECT * FROM (SELECT '' || ? || '' from dual ) wrappedQry) limitQry WHERE ROWNUM < 0
SELECT '''$X{IN, COLUMN_DATA, Values}''' AS result FROM dual
As does this
SELECT '''${Values}''' AS result FROM dual
I wanted to try returning from dual because if that worked, i'd be able to use regex next, but i can't even get that far. Any help?
SELECT '' || ${Values} || '' from dual also failed
----
I've never tried making a parameter that relies on another parameter before. Maybe I could try that and see if the second parameter can strip the edits to the first one.
So display Pone, then make Ptwo regex on Pone. Wouldn't that run into the same problem?
What if I make an invisible Param with just the original seasons-202408, 202501. and then the user facing parameter concats that, but actually the user input param one. would that be possible?
I also wanted to try and look at the source code and did findstr for "Selection mode" but it's a pretty big list of results.
---
As far as the source code, i do see that parameterkey is only present in these 4 files, so that's promising, but it's still going to take a lot longer than whoever already knows the answer, hopefully
src\net\datenwerke\rs\base\client\reportengines\table\ui\model\Expressions.java: IN("$X{IN, column, parameterkey}"), NOTIN("$X{NOTIN, column, parameterkey}"),
src\net\datenwerke\rs\base\client\reportengines\table\ui\model\Expressions.java: NOTEQUAL("$X{NOTEQUAL, column, parameterkey}"), LESS("$X{LESS, column, parameterkey}"),
src\net\datenwerke\rs\base\client\reportengines\table\ui\model\Expressions.java: LESS_EQUAL("$X{LESS], column, parameterkey}"), GREATER("$X{GREATER, column, parameterkey}"),
src\net\datenwerke\rs\base\client\reportengines\table\ui\model\Expressions.java: GREATER_EQUAL("$X{[GREATER, column, parameterkey}"),
Here is Expressions.java
https://github.com/infofabrik/reportser … sions.java
And the file i found that probably calls it:
https://github.com/infofabrik/reportser … eImpl.java
I'm still pretty confused, I don't do Java code much if at all.
Well, to put it bluntly, it feels like ${singlekey} just takes the exact input and outputs the same as a string, so I can regex and such on it, but if i try to do the same with ${multikey}, it fails for reasons unknown but implying either it doesn't do that, or i'm still too new to SQL and Report Server
Note: I got the idea that the way parameter key works is to replace it with text that would make sense in a SQL query from the manual, that's how it showed $X{IN ..} works. Now I'm not sure when looking at the TableReportUtilityServiceImpl.java method getReturnedColumns because I thought it was just a REGEX originally, but I am not a java person.
other files of interest i will check later
reportserver-main>findstr /s /p "getReturnedColumns" *.java *.xml
src\net\datenwerke\rs\base\client\reportengines\table\columnfilter\propertywidgets\FilterView.java: tableReportUtilityDao.getReturnedColumns(report, execToken, mac);
src\net\datenwerke\rs\base\client\reportengines\table\helpers\ColumnSelector.java: Request request = tableReportUtilityDao.getReturnedColumns(report, execToken, mac);
src\net\datenwerke\rs\base\client\reportengines\table\rpc\TableReportUtilityService.java: ListLoadResult<ColumnDto> getReturnedColumns(TableReportDto tableReport, String executeToken)
src\net\datenwerke\rs\base\client\reportengines\table\rpc\TableReportUtilityServiceAsync.java: Request getReturnedColumns(TableReportDto tableReport, String executeToken,
src\net\datenwerke\rs\base\client\reportengines\table\TableReportUtilityDao.java: public Request getReturnedColumns(TableReportDto tableReport, String executeToken,
src\net\datenwerke\rs\base\client\reportengines\table\TableReportUtilityDao.java: return rpcService.getReturnedColumns(tableReport, executeToken, transformAndKeepCallback(callback));
src\net\datenwerke\rs\base\server\table\TableReportUtilityServiceImpl.java: public ListLoadResult<ColumnDto> getReturnedColumns(@Named("report") TableReportDto tableReportDto,
src\net\datenwerke\rs\base\service\reportengines\table\TableReportUtils.java: List<Column> getReturnedColumns(TableReport report, String executeToken)
src\net\datenwerke\rs\base\service\reportengines\table\TableReportUtils.java: List<Column> getReturnedColumns(TableReport report, User user, String executeToken)
src\net\datenwerke\rs\base\service\reportengines\table\TableReportUtilsImpl.java: return getReturnedColumns(report, user, executeToken, true);
src\net\datenwerke\rs\base\service\reportengines\table\TableReportUtilsImpl.java: public List<Column> getReturnedColumns(TableReport report, String executeToken)
src\net\datenwerke\rs\base\service\reportengines\table\TableReportUtilsImpl.java: return getReturnedColumns(report, user, executeToken, false);
src\net\datenwerke\rs\base\service\reportengines\table\TableReportUtilsImpl.java: return getReturnedColumns(report, user, executeToken, true);
src\net\datenwerke\rs\base\service\reportengines\table\TableReportUtilsImpl.java: public List<Column> getReturnedColumns(TableReport report, User user, String executeToken)
src\net\datenwerke\rs\base\service\reportengines\table\TableReportUtilsImpl.java: return getReturnedColumns(report, user, executeToken, false);
src\net\datenwerke\rs\base\service\reportengines\table\TableReportUtilsImpl.java: protected List<Column> getReturnedColumns(TableReport report, User user, String executeToken,
-----------------------------
update
Ok, progress is made even if it's not solved.
In the admin_rs4-7.pdf, on page 90, we are introduced to the $P!{key_ID} parameter type. Now, when I used the insert Parameter dropdown before, I was not shown the existence of $P syntax, so this is found only in the manual. And it's dangerous because people can do SQL injection attack.
So now if I do
select $P!{Values} from dual
where values is two values from a multiselect datasource
Then end result will return a table with two table columns '(202408)AUGUST2024', '(202501)JANUARY2025' and one row of data, that is, (202408) August 2024, (202501) January 2025
At this point I'm still a little lost, need to do further testing
from admin manual, pg 68-69, RS reassembles a SQL query internally from your source SQL.
as an aside, I am trying to figure out if $P!{SQL_INJECTION} will allow me to bypass the assembler entirely or not. If so, could I call database-specific SQL code? that would be nice, but then that depends on the jdbc connection.
-----------------
Update 2:
on pg 82, but under the Datasource Parameter, ReportServer expects a table with one or two columns, where the first column is the value used when exporting and the second column is the display shown to the user. This fixed my problem and I am much happier.
Although, I do still want to know what I can do with Post-Processing field. it runs Groovy? I don't have any experience with that. Also looking at the JUEL but I'll table that too.
Last edited by zitot (2024-10-31 01:50:48)
Offline