I currently run queries via sql server using ad-hoc sql which is very cool, but I'm the only one with access to do this. I was advised to create these as add-in queries so the payroll and HR folks can run their own queries - most are standard, every pay period and I've already created the query that just gets run with the new pay dates.

I was under the impression that add-ins are not that sophisticated and a lot of my queries use case statements, functions, joined tables, subqueries, etc.

Is MS Addins able to run these types of queries or is it limited to SIMPLE queries?

Not sure of your definition of "SIMPLE" queries, but they definitely do not have all the capabilities of a sql query, and not even all of the capability of crystal reporting. We use the add-ins here ALL the time, but for more advanced reporting, we use other options.

I THINK THAT IT IS SAFE TO ASSUME THAT CASE STATEMENTS, SUBQUERIES AND UNIONS ARE OUT OF THE QUESTION FOR ADD INS.

WHAT ABOUT QUERIES THAT JOIN MULTIPLE TABLES USING INNER OR LEFT OUTER...CAN ADDINS HANDLE THAT?

CAN I ASSUME THAT THEY CAN HANDLE THE MIXING OF AND/OR CRITERIA IN THE WHERE CLAUSE?


THIS IS A MORE SIMPLE QUERY TO ME, IT USES A TRIM FUNCTION ON THE COLUMN, JOINS A TALBE AND HAS LIMITING ITEMS IN THE WHERE CLAUSE - CAN I ASSUME THAT THE ADDINS COULD HANDLE THIS TYPE OF QUERY?

select A.EMPLOYEE, DED_CODE, (RTRIM(LAST_NAME) + ', ' + RTRIM(FIRST_NAME)) as name, FICA_NBR, DED_AMT

from lawson.PAYDEDUCTN A (nolock)
INNER JOIN lawson.EMPLOYEE B (nolock) ON A.EMPLOYEE = B.EMPLOYEE
LEFT OUTER JOIN lawson.PAYMASTR C (NOLOCK) ON A.CHECK_ID = C.CHECK_ID

WHERE DED_CODE = 'DFCU'
AND CHECK_TYPE NOT IN ('R', 'M', 'J', 'V') AND C.CHECK_DATE = '20071221'

order by name

We use a couple of approaches and which approach is used in a situation is dependent on the sophistication of user usually.

1. We use Excel Addins. This is for the least sophisticated user. It requires very little programming and logic experience.
2. In a couple of cases we've created Crystal Reports that are downloadable into a spreadsheet. Also delivered to non-sophisticated users through Dashboard.
3. In another situation I have an MS Access expert working in HR so I gave him access to use that for Ad-Hoc
4. Lastly I have two end users who can write simple SQL queries so I made them read only SQL Server users.

Insane but every is relatively happy.