1. Rocky Scofield
  2. Report Editor
  3. Вторник, Ноябрь 15 2022, 04:28 PM
  4.  Подписаться через email
Hello. I am using VStudio Pro 11.3.1 (64 bit).

I have a report where I would like to use a SQL query to do a single column lookup from a table that is not in the original query defined for the report.

I have an id column in my report query. Using that column data, I would like to perform a second SQL query within the report that returns column data. It would be something like this:


SELECT description FROM other_table WHERE id = $F('desc_id')


Ideally, this could go into an Expression text field where the 'description' from this lookup would display in that field. I do not need this query to run for every row in the page body, only once within a Group Header.

I can build a more complex main report query for this information, but it makes the query much slower. The query has to retrieve this information for each record to be displayed in the page body, not just the few records where column criteria for the Group Header changes.

A subreport looks like it wants to display the results of a secondary query in a tabular format, whereas I just need a single column's data.

Thanks for the help,
Rocky Scofield
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Hello Rocky,

Sorry for the delay (we’re still having big problems with electricity and internet connection).

A subreport is not necessarily shows a tabular data, it can show one value. Please try to use parameter in the query for subreport and map desc_id value to it. Unfortunately, can’t test it myself.
Комментарий
There are no comments made yet.
Rocky Scofield Ответ принят
Thank you for your response. I will try that as soon as I can.

I saw a method when I was investigating javascript options:

report.sqlselect()


It looks like it is supposed to return a cursor.

I could not find this documented anywhere. Does it work? If so, this could also be used in an Expression text field.

Rocky
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Yes, I think JavaScript expression in Group Header can be used, too, here’s my example:


{
let curs = report.datasource.sqlSelect( 'SELECT name FROM category WHERE category_id = ' + $F('category_id') ); curs.columnValue( 'name');
}

Комментарий
There are no comments made yet.
Rocky Scofield Ответ принят
Thank you for the follow up, Sergey. I could not get the datasource.sqlselect() function to work. It produces a 'not a function' error. I tried it in a few areas where scripts can be placed.

Maybe it is because my version is from July 2021?

Rocky
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
I tried it with 11.3.1 and it works fine, attached the screenshots.
Вложения
Комментарий
There are no comments made yet.
Rocky Scofield Ответ принят
As always, thanks for your great support. Your example worked well.
My syntax was wrong when trying to combine a string with the results from the query.
I also was trying to use 'return' to send the result.

Rocky
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
And it is possible to use return - just declare an anonymous function and call it:

( function()
{
let curs = report.datasource.sqlSelect( 'SELECT name FROM category WHERE category_id = ' + $F('category_id') );
return curs.columnValue( 'name' );
} )
()
Комментарий
There are no comments made yet.
  • Страница :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories