Spectrum Global Collection is missing from the drop down list when running a Report in JasperSoft

Products

• Spectrum
• Network Observability

Issue/Introduction

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.

Environment

Release : Any
Component : Spectrum Report Manager / SRM

Cause

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.

Resolution

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 -p<PASSWD> 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 5, but is not listed for the query in Step 4, this indicates the reason  why Jaspersoft is 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.

Additional Information