GREETING SAGE CITIZENS.

Today I am EXCITED to discuss this new topic. This isn't one of those cases where I work for SAGE so I have to be excited about everything. This is one of those cases that if I were with a channel partner or a direct customer I would be utterly ecstatic. You know that feeling when you haven’t eaten all day and you are sitting at the table of your favorite restaurant (I'm really partial to BWW's right now, what's yours) and you start to see your order on the way to your table and you have that moment of immutable joy that you are about to dig in. that is how i feel right now with the new Global reporting feature.

Here's how it works. On upgrade to 10.5 and any significant change to the number of linked employers (to payroll) in HRMS, a series of procedures run to create unified payroll tables as a function of a view under the sagehrms_live database. Not only does it do a merge but it also inserts a PR_company field (at the end of the view) to identify which HRMS company this PR data ties to.

THIS MEANS YOU DO NOT NEED TO CREATE A NEW REPORT FOR EACH PAYROLL COMPANY YOU ARE USING.

THIS MEANS YOU DON'T NEED TO HAVE A PROTRACTED CONVERSATION WITH A DATABASE NERD LIKE ME TO UNDERSTAND VIEWS AND UNION STATEMENT LOGIC.

THIS MEANS YOU DON'T NEED TO METICULOUSLY MAINTAIN THE VIEWS EVERY TIME YOU GET A NEW COMPANY.

If you are not excited, check your pulse you might not be alive.

In order to start using this functionality there are some things you need to know.

• the name of the SQL server (servername/instancename)
• a valid sql users account
• the password for that SQL account
• the name of the database being used as live. (normal sagehrms_live or hrms_live but could literally be anything).

1. To create a new report, just luanch crystal reports (any version really).
2. Then go to report wizard (because that's what I am).
3. -the standard report creation wizard screen will come up.
4. hit the + next to create new connection
5. hit the + next to OLEDB (RDO)
6. choose Microsoft OLE DB PRovider for SQl Server
7. enter in all the specifics such as server name, un, pw , Dbase name.
8. -notice a new set of options appears under OLEDB. It should be the same name as the database beng used for HRMS_LIVE.
9. hit the + next to that.
10. hti the + next to DBO
11. hit the + next to views
12. select all the tables you need to run your report. for example UPEMPL and UPCHKH and UPCHKD.

from this point forward the report creation process is the exact same as before. The use of UPEMPL, UPCHKH and UPCHKD will comprise the most common type of report run from payroll data so we will continue to walk this through some additional steps.

After selecting your tables, it will bring you to a screen asking how these tables link together.

1. link UPEMPL to UPCHKH by EMPLOYEE and PR_company.

2. link UPCHKH to UPCHKD by EMLOYEE, PEREND, ENTRYSEQ, PR_COMPANY

hit next.

Then it will ask you what field you want.

The most practical fields are as follows.

upemple.employee (employee number)

upemple.PR_company (company code)

upemple.fullname (not sure what this is but it's some kind of string of letters. Possibly English).

upchkh.transdate (this is the transaction date of the check. What’s so special about this is IT IS FORMATTED AS AN ACTUAL DATE OPPOSED TO AN INTEGER FORMATTED AS YYYYMMDD. THIS IS HUGE. ).

upchkh.transamt ( the amount of payment for the check).

upchkd.earnded (this is the code of the earning deduction or tax).

upchkd.hours (hours paid if an earning code based on that).

upchkd.erate (rate of pay or percent base for earning deduction or tax)

upchkd.eextend (amount of earning deduction or tax)

upchkd.pcategory (flag to tell you if posting is an earning deduction or tax. 1=earning,2=deduction,3=tax,4=accrual,5=benefit).

upchkd.taxearns ,upchkd.txearnceil (these fields hold taxable wages for the two tax code types. ).

Next step is grouping.

I would reccomend you group by Pr_company, employee, transdate

Then it will ask your for selection criteria

I would recommend you set the transdate to is between two dates of your choosing, then set the PR_company to a company you know has payroll data.

Then just keep hitting next.......

What you will end up with is a fairly detailed payroll history analysis report that you can use to review individual payments to employees. This is what I personally call the EHDLT report and is very handy for all sorts of payroll audit reasons. Also , this report is highly modifiable for advanced users.

What if I had to do it the hard way all along and I want to convert over my existing reports?

Not a big deal.

1. Just open your existing report.
2. go to database/ set data source location
3. create the database driver like you did in the prior process (assuming it isn't already there).
4. look at the tables listed in the top window (ones currently in use). find the same database in the new driver, highlight it and the one above and hit update.
5. repeat the above step until all the listed tables are updated.

there is a chance that when you are done and hit close it may complain about unmapped fields. Don't worry, just uncheck the box for matching data type, then link up whatever fields it wasn't able to relate over to the desired one. (in some cases the field will be the same name just a different data type).

then finish.

YOU'RE NOT DONE YET THOUGH!

you will then need to go to database/database expert,

then go to database linking.

You will need to connect up each table listed that has a pr_company field to the other databases with a pr_company field. Let’s say you already have three databases and they are linked as a string like upemple to upchkh, then from upchkh to upchkd. Just draw links from upemple to upchkh to add the link for Pr_company, then upchkh to upchkd by pr_company, Do not worry about drawing a link from upemple to upchkd as there was no direct linking there in the first place.

I hope this blog post has been helpful and illuminating. I think this is the first new feature I have ever been excited to support. If you leverage this correctly, former Abrasuite users can start to get their sea legs back and apply a lot of the Abrasuite reporting concepts to hrms payroll once you understand the fields in play and the database interactions.

Now imma go build me some reports!!!