Author Topic: Filtering requirements using custom SQL searches in Virtual Documents  (Read 215 times)

frfucci

  • EA Novice
  • *
  • Posts: 2
  • Karma: +0/-0
    • View Profile
Hello everyone,
I have problem related to Virtual Documents generation, i tried to search in the forum but I didn't find anything useful for me.
My goal is to generate a document all the requirements objects which match with a phase. On these filtered requirements, I should also print eventual trace relationships between the current processed object and other requirements, which belong to other packages.
For this purpose, I created a SQL query which correctly filters requirements according to the phase that is passed via the Search Box, the format of the Alias and the Stereotype. I tested it in the Project Search and it works correctly.

This is the query MySearch:

select t.ea_guid AS CLASSGUID, t.Object_Type AS CLASSTYPE, t.Name from t_object t where t.Phase like '#WC#<Search Term>#WC#' and t.Alias like '4%' and t.Stereotype <> 'information'

This is the template MyTemplate:
https://pasteboard.co/GJS7I81.png

To use this query inside the Virtual Document, I created a model document and modified the model document tags as follows:
- RFTTemplate MyTemplate
- SearchNamer MySearch
- SearchValue MyPhase (either First or Second or Third)

What I get is that all the requirements in the package are processed, not only the filtered ones. So the generated document covers all the requirements in the package and not only the ones filtered by the query.

Another way I thought to make this by using the Custom SQL Query in Template fragments. As far as I know, Custom SQL Query cannot take as input a search value. I would avoid making too many fragments or templates when they are not required, so I would prefer the first solution.

Am I doing something wrong?

Thanks,
Francesco

qwerty

  • EA Guru
  • *****
  • Posts: 8906
  • Karma: +134/-122
  • I'm no guru at all
    • View Profile
Re: Filtering requirements using custom SQL searches in Virtual Documents
« Reply #1 on: September 11, 2017, 11:48:58 pm »
'4%'  looks suspicious. % is a wildcard for EAP files.

q.

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 7671
  • Karma: +156/-21
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Filtering requirements using custom SQL searches in Virtual Documents
« Reply #2 on: September 12, 2017, 01:08:13 am »
You can't use <Search Term> in an SQL Fragment.
You'll either have to use #OBJECTID# or #PACKAGEID# to limit your search results to the context.

Geert

frfucci

  • EA Novice
  • *
  • Posts: 2
  • Karma: +0/-0
    • View Profile
Re: Filtering requirements using custom SQL searches in Virtual Documents
« Reply #3 on: September 12, 2017, 01:25:14 am »
Hi q and Geert,
thanks for your answers, unfortunately removing the '4%' didn't work as well.
I would use the model document tags that I find in the Tags tab of its Properties windows .
So what I understand from the documentation is that if I put in the model document the following tags
RTFTemplate=MyTemplate, SearchName= MySQLSearch (that I defined in the Find In Project tab), SearchValue should contain the <Search Term> i can generate the document using as filter the query MySQLSearch.
Then in the template MyTemplate i proceed directly referring to the element or the package section, without referring to the custom section.
Is that possible in Enterprise Architect? What is the purpose of the RTFTemplate, SearchName and SearchValue tags in the model document ?

Thanks,
Francesco

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 7671
  • Karma: +156/-21
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Filtering requirements using custom SQL searches in Virtual Documents
« Reply #4 on: September 12, 2017, 04:22:08 pm »
Hi q and Geert,
thanks for your answers, unfortunately removing the '4%' didn't work as well.
I would use the model document tags that I find in the Tags tab of its Properties windows .
So what I understand from the documentation is that if I put in the model document the following tags
RTFTemplate=MyTemplate, SearchName= MySQLSearch (that I defined in the Find In Project tab), SearchValue should contain the <Search Term> i can generate the document using as filter the query MySQLSearch.
Then in the template MyTemplate i proceed directly referring to the element or the package section, without referring to the custom section.
Is that possible in Enterprise Architect? What is the purpose of the RTFTemplate, SearchName and SearchValue tags in the model document ?

Thanks,
Francesco
Ah, you're talking about virtual documents using an SQL search as the source of the contents, I misunderstood.
I'm not sure what's wrong, but I do find it strange that you mix both #WC# and %

Geert