I am unable to view / select and report on some Global collections within JasperSoft Server as they are missing from the dropdown. The global collections are all visible in Spectrum.
The Jaspersoft MySQL inner join query is checking if the global collection name exists on both the model and globalcollection tables.
If the Global Collection name is not returned for both it will not be populated into the * Select the Global Collection list.
Release : 20.2
Component : Spectrum Core / SpectroSERVER
1. Login to SRM system as the user that owns the Spectrum installation
2. If on Windows, start a bash shell by running "bash -login"
3. Enter the following command to log into mysql:
./mysql --defaults-file=../my-spectrum.cnf -uroot -proot reporting
4. Enter the following at the mysql prompt to check that all GCs exist within reporting DB globalcollection table:
select * from globalcollection;
5. Enter the following command at the mysql prompt to query and see if the GC also appears in the model table replacing Name_of_GC with the model name of the GC that is missing
select * from model where model_name like '%Name_of_GC%';'
If the GC is returned for the query in Step 2, but is not listed for the query in Step 3 this is why Jaspersoft it not populating the GC in the dropdown list.
Steps to Resolve:
1. Edit the GC and append with an _1 (This will force an update and create a new entry in both the globalcollection and model tables).
2. Wait ~1 hour or more for the changes to synchronize to the Reporting DB (See the additional information field for how to force the synchronization process).
3. Verify the renamed _1 GC appears in the JasperSoft Dropdown list.
4. Follow the same steps to Rename the GC Back to the original name.
To force synchronization\update to reporting database:
Administration > Report Manager (Report Manager Admin Tools) > SPECTRUM Status: