We have released a new version of the CMS Query Builder.
- Reduced memory consumption when building XLSX file.
- Possibility to export to CSV.
- Command line interface.
We have released a new version of the CMS Query Builder.
You must be logged in to post a comment.
Great tool, unfortunately the -path parameter isn’t recognized which means it isn’t possible to select the PATH from the command line (non gui) version.
Many thanks for the feedback! This error has been fixed and we have updated the software. Now you can run the tool with command:
cmsquery-cmd.bat -sql "select si_name from ci_infoobjects where si_kind='webi' and si_instance=0" -csv "webi.csv" -path "yes"
Thanks, this now works! Another nice to have option would be the ability to select single columns from containers. Right now it’s only possible to select the whole container (e.g. SI_FILES) and not a single column from a container (e.g. SI_FILES.SI_FILE1).
Furthermore, maybe you can also store the last picked options for ‘Include path’ and ‘Include containers’ in the cmsquery.cfg file.
For the command line version of the tool, you might want to correct the following sentence:
Escape charatersin CVS? (yes/no) Default=’yes’
To:
Escape charaters in CSV? (yes/no) Default=’yes’
You can achieve this by using combination of options -containers, -columns, -selectedonly. For instance:
cmsquery-cmd.bat -sql "select SI_CUID, SI_FILES from CI_APPOBJECTS" -containers "yes" -columns "SI_CUID, SI_FILES.SI_FILE1" -selectedonly "yes"
Many thanks for the feedback!
Using CMS Query Builder, how to get a list of WebI Reports of a particular Category (or all categories). When I execute the below query
Select * FROM CI_INFOOBJECTS WHERE SI_KIND = 'Category'
it is getting the document IDs but I want to include the name of the WebI Report along with the Category.
Thank you in advance
You can use the following query to get the list of the Webi reports of a particular category
SELECT SI_NAME FROM CI_INFOOBJECTS
WHERE SI_KIND = 'Webi' AND
DESCENDANTS("SI_NAME='Category-Document'", "SI_NAME='{category}'")
Hi, thanks for this great tool! I’m running this query:
SELECT SI_NAME, SI_CUID, SI_ID, SI_OWNER, SI_AUTHOR,SI_CREATION_TIME, SI_FILES FROM CI_INFOOBJECTS WHERE si_ancestor=23 AND SI_KIND= ‘Webi’ AND SI_INSTANCE = 0
Interactively it returns 12304 occurrences of SI_ID but only 1000 rows are generated in the Excel output. Is the amount of data that can be exported to Excel limited to 1000 rows?
Please add TOP 100000 after SELECT. I.e. SELECT TOP 100000 SI_NAME, SI_CUID, SI_ID, SI_OWNER, SI_AUTHOR,SI_CREATION_TIME, SI_FILES FROM CI_INFOOBJECTS WHERE si_ancestor=23 AND SI_KIND= ‘Webi’ AND SI_INSTANCE = 0
Thanks that solved it !