AnsweredAssumed Answered

SQL Count() works on MS SQL Mgmt Studio but not Report Generator.  Any ideas

Question asked by Julie Ide on Jun 22, 2018
Latest reply on Jun 25, 2018 by Julie Ide
  • Comment • 4

I'm trying to produce a report that will count the number of ECRs that are older than 30, 60 and 90 days.  The query below is for 30 days.  It uses the function COUNT() and works perfectly in MS SQL Mgmt Studio.  It won't, however, import to Report Generator.  (I've confirmed that I have put the SQL in the proper .crp format).  Does anyone have ideas on how to rewrite the following query without using COUNT?  (PS:  I'm an SQL newbie.  I'm sure this query can be improved upon.)  Can anyone confirm that COUNT() can't be used?

 

SELECT COUNT(D.filename) AS [> 30 Days]

FROM Status AS S

INNER JOIN Documents AS D ON S.StatusID = D.CurrentStatusID

JOIN TransitionHistory AS TH ON D.DocumentID = TH.DocumentID

JOIN Workflows AS W ON S.WorkflowID = W.WorkflowID

WHERE (W.name = 'ECR Workflow') and (D.deleted = '0') AND (DATEDIFF (dd, TH.Date, GETUTCDATE()) > '30')

 

Thanks!  Julie

Martin Solem
Martin Solem Jun 25, 2018 4:40 AM
Correct Answer

Hi Julie,

 

It seems to work for me, this is my entry in the CRP file:

 

@[OldDocuments]

§Name [Count Documents older than 30 days]

 

§Company [SolidWorks]

 

§Description

[This query lists all documents older than 30 days.]

 

§Version [1.1]

 

§Arguments

[

]

 

§Sql

[          

 

SELECT COUNT(D.filename) AS [> 30 Days]

FROM Status AS S

INNER JOIN Documents AS D ON S.StatusID = D.CurrentStatusID

JOIN TransitionHistory AS TH ON D.DocumentID = TH.DocumentID

JOIN Workflows AS W ON S.WorkflowID = W.WorkflowID

WHERE (W.name = 'For reference only') and (D.deleted = '0') AND (DATEDIFF (dd, TH.Date, GETUTCDATE()) > '30')

]

 

/*******************                        ********************/

It reports 199 files, which is the same i get from a SQL query.

See the reply in context
No one else had this question

Outcomes