Get the date in the Report

jaymz69
jaymz69 used Ask the Experts™
on
Since I am doing a GROUP BY user

I always get the error
"Column 'OPENQUERY.PCHDATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

All I really need is the Date of the transaction from the SELECT

I tell the select to CURRENT_DATE - DAYS -- AS400 SQL

is there a way to pass it on to the subscription text in the email?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
could you post your query...

Author

Commented:

SELECT pchpuser 'User',
	COUNT(*) 'Total Lines Counted by User',
	pchdate 'dt'

FROM OPENQUERY (MyData, '
SELECT *
FROM picounthst
WHERE pchloc = 41
	AND pchdate = (CURRENT_DATE - 1 DAYS)
')

GROUP BY pchpuser ;

-- The subribed report form SSRS will have the User and count but
-- I also want the date from wne the transaction happenedsomewhere...
-- or atleats the value from the CURRENT_DATE - 1 DAYS

Open in new window

Commented:
try this

SELECT pchpuser 'User',
	COUNT(*) 'Total Lines Counted by User',
	MIN(pchdate) 'dt'

FROM OPENQUERY (MyData, '
SELECT *
FROM picounthst
WHERE pchloc = 41
	AND pchdate = (CURRENT_DATE - 1 DAYS)
')

GROUP BY pchpuser ;

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial