SSRS DateTime parameter problem

I had no luck with a Google search on this so I'm going to document it.


I was developing reports in SSRS for SQL Server 2008 R2 that print based on a date/time stamp in SQL Server.  If that date/time stamp is passed to the report as a date/time parameter the report will work in Visual Studio, but fail in Report Manager.


I believe the reason for this is Report Manager cuts off milliseconds from the date/time parameter, causing the report to return no data, as the parameter no longer matches the underlying data.


To solve this I convert the parameter to string and render the date/time value in the underlying SQL view to also be string.  That way SSRS is matching two string parameters.


This is a workable solution and has proven reliable.  I suppose it's also possible to drop milliseconds from the date/time value in SQL but that may have problems of its own.

Dynamics GP - negative CR amounts in PR distribution

I was trying to balance a custom payroll report I created to the GL and was narrowing the differences when I suddenly realized what I was looking at: In reviewing the UPR30401 table (Payroll Distribution Detail) I saw negative credit amounts.

I had to rub my eyes as I could not believe what I was seeing.  This is Dynamics GP 2010 and the client runs weekly payrolls and has been for years.

I was pulling numbers from UPR30401 and was trying to balance to the GL but could not resolve the difference.  When I drilled down on an account for a specific period I noticed the negative credit amounts in the table.  In order to get the difference resolved I had to use the absolute value of the credit column in that table.  The related GL entry shows a positive credit amount (or has the positive credit subtracted from the debits for summary postings.)

This is completely unexpected behavior of GP and something I will have to watch if I use that table again.  I've never seen negative credits anywhere else in GP, in fact GP avoids negatives in most situations completely.

Again BEWARE if you are using the UPR30401 take the absolute value of the credit column (CRDTAMNT.)

Slow GP login

We had a client running GP from a Citrix server.  For some reason GP decided to take its sweet time to log users in one day.  I clocked over 10 minutes to be able to login.

I found this blog that gave the answer: add the following section to the Dynamics.exe.config file on the Citrix server.

  <runtime>
              <generatePublisherEvidence enabled="false"/>
      </runtime>

Add that to the end of the config file just before the </configuration> line and users were logging in inside of 30 seconds.

SQL 2008 R2 SSRS case sensitive?

Was working on an SSRS report for a client that is a subscription and found that the UNC path is case sensitive.  Anyone else find this?


How this is configured is we have an SSRS report that needs to render check images into PDF files.  Because there are many checks to render, each into separate files we must use a data-driven subscription report.  To this subscription we provide the report parameters as well as the file name and path via the following script:

SELECT xxDatabase, CpnyID, CheckNum, CheckBatch, FileName, FilePath FROM database.dbo.vCheckDetailRun

 So in "Step 4 - delivery extensions" we use the FileName and FilePath columns to name the files.  Then in "Step 5 - parameter values" we use the first four columns for the required parameters.


The trick was when I provided the FilePath in all lower case I received errors for all the records processed in the Subscriptions > Status of the report - and no files rendered.


Oddly in the SSRS Execution Log it happily reported "rsSuccess" as the Status.  And of course the job reports success too.  You just get no results.


Going one step further we can look at the ReportServerService log in the SSRS data folder on the server.  To do that with today's log you have to cycle the SSRS service, otherwise the log is still open and cannot be read.  The log gives a clue as to what's happening with the following messages for every attempt to write a file:

notification!WindowsService_3!11b4!07/10/2014-12:43:27:: i INFO: Handling subscription ee8fc032-c6a1-419a-be10-b5dc9d10b084 to report CheckImage, owner: INVENERGY\BPS, delivery extension: Report Server FileShare. notification!WindowsService_3!11b4!07/10/2014-12:43:27:: e ERROR: Error occured processing subscription ee8fc032-c6a1-419a-be10-b5dc9d10b084: The path is not valid. The path must conform to Uniform Naming Convention (UNC) format.

To resolve this I took the FilePath column off the subscription and pasted in the UNC path from Windows Explorer.  This allowed the report to work and I noticed the only difference between the UNC path I pasted and the UNC path in the FilePath column is capitalization.


I then pasted the proper capitalized UNC path into my FilePath column in the database and the report then ran.  Conclusion: Your UNC for a Subscription report in SSRS is CASE SENSITIVE!

Payroll report by EEOC category

A GP payroll client requested a report showing payroll and benefits by EEOC category.  This report needed to break down the EEOC amounts by full time or part time.  Here are the components I used to assemble this report.


First the EEOC category is stored in the Position Setup table with the Position Code (also called Job Title).  The position master file with this data is the UPR40301 table.  The EEOClass_I column in that table contains a code that correlates to the EEOC category.


While the Position Setup window has the EEOC codes hard coded for the field that is defined, there is a place in GP where the descriptive value can be looked up.  Thanks to this blog I was able to find this data in the SY10997 table in the Dynamics database.  The data must be restricted by columns FuncName = 'HR_FUNC_EEOClass' and Language_ID = 0.  The index value is in FuncEnum and the descriptive value is in SQL_Msg.


Now the position code is recorded with the payroll data so I could cross-reference the payroll history from UPR30300 to the Position Setup table then to the SY10997.  And lastly I needed to grab the Full-/Part-Time status from the Employee Master UPR00100 table in column EmploymentType.


There are a few tricks here that you need to be cognizant of.  First if an employee was in different positions that crossed different EEOC categories during the year they would show up on this report in each of the different categories. 


Also the employment status from the master is only as of right now so when an employee changes from full- to part-time then his wages for that report would also change regardless of what he was at the time payroll was run.  This is unavoidable without a customization that records the employee status with the pay run.

Dynamics GP CheckLinks kills Management Reporter

This is GP 2010 with Management Reporter FP1 - an old version, yes I know.  Don't know if this happens in any new versions but we shall see.

This all started when we needed to run Changer for a client.  The first thing you do before Changer is run CheckLinks in GP against all the Financial series tables.  Everything looked fine in GP after the change but Management Reporter stopped working.  All we got were "unknown error" messages for every report from ever user.

I actually got to the point of reinstalling Management Reporter on a test server and created a new report and row definition pointing to the production GP database.  When I was trying to create a new column I received:

"The operation could not be completed due to a problem in the data provider framework"

That was an indication that something was wrong in GP - a very strong one.

A Google search turned up this forum post that indicated there might be a blank budget ID.  Sure enough CheckLinks had created one.  After deleting that blank budget MR was back and working again.