Knowledgebase: EC Enterprise
EC Enterprise: Reports say Unavailable
Posted by Ryan Ohlson, Last modified by Joel Brickell on 06 February 2013 04:51 PM
Problem:  Your user account has permissions to execute Reports, but when you log into EnergyCAP, it says "Unavailable" for the Installed Reports.

Solution:  There is likely an issue with the underlying tables which hold the available reports for all the users in EnergyCAP.  The specific table which governs this is the SpecificReport table in conjunction with the ReportGroup and ReportGroupType tables.  The following SQL script will repair the entries in these tables.  After running the SQL script, you will need to Update the Reports again in the database for All Users.  See this Help Manual Page for instructions on Updating Reports: http://help.energycap.com/reporting-on-data/updating-reports/

SQL SCRIPT (run as esuser or sa):

-- =====================

INSERT INTO 
  ReportUser(systemUserID, reportUserCode)
SELECT
  SU.systemUserID, SU.systemUserCode
FROM 
  SystemUser SU
  LEFT JOIN ReportUser RU ON SU.systemUserID = RU.systemUserID
WHERE
  RU.reportUserID IS NULL

 

INSERT INTO
  ReportGroup(reportGroupTypeID, reportUserID, parentID)
SELECT
 RU.reportUserID, RGT.reportGroupTypeID, NULL
FROM
  ReportGroupType RGT
  CROSS JOIN ReportUser RU
 LEFT JOIN ReportGroup RG ON RU.reportUserID = RG.reportUserID AND RGT.reportGroupTypeID = RG.reportGroupTypeID
WHERE
  RG.reportGroupID IS NULL AND
  RU.reportUserID <> 1
-- =====================

 

-- ReportGroup.parentID is only used for public report folders, and those are always assigned to the SYSTEM user, hence always NULL for this purpose.


(300 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments: