Date variables (last months data)

To use parameters in a CR Command:

 Create the parameters in the Command edit window.  It has to be there, as opposed to the main CR editor window.  Once the parameters are created, they can be edited from the main window.

 Then, basically, insert the parameter name where you need it in the SQL code.  The simplest thing is to position the cursor where you want the parameter and then double-click the parameter name in the parameter list on the right.  When CR runs the query, it will simply replace the parameter name with its value.  If the parameter value could be a string, put the parameter name in single quotes.  For a date or datetime, I don't think you'll need them.

SELECT
        [Assessee Name:]
      , COUNT([Unique Ref]) AS 'Total'
      , 'No of Assessments' AS 'Type'
FROM [Temp Database$A1:AB10000]
WHERE [Skill Category] = 'Total'
        AND MONTH([Date & Time]) = 8 -- param here
        AND YEAR([Date & Time]) = 2013 -- and another param here
GROUP BY [Assessee Name:]

Select allOpen in new window

SELECT
        [Assessee Name:]
      , COUNT([Unique Ref]) AS 'Total'
      , 'No of Assessments' AS 'Type'
FROM [Temp Database$A1:AB10000]
WHERE [Skill Category] = 'Total'
        AND MONTH([Date & Time]) = MONTH(getdate()) -- "auto month"
        AND YEAR([Date & Time]) = YEAR(getdate()) -- "auto year"
GROUP BY [Assessee Name:]

Select allOpen in new window