Solved

Custom function to SSRS using array and dataset values

Posted on 2014-04-10
6
5,559 Views
Last Modified: 2014-05-05
I'm experimenting with a report where the Project Managers can play around with the hours of employees and see how it would impact their budgets.  I've got a report that shows a project's employee list with budgeted hours/cost and actual hours/cost.  I've removed the actual figures and am including a variance column to hold the number of hours the PM wants to change (-/+ #).  

I'm using 2 parameters to allow the PM to enter their proposed changes: one is a drop-down list of employees on that specific project and the other is a text box where they can manually enter a number representing the hours they want added/deducted from the employee's budgeted hours.
I'm using global variables to hold the hours entered and the id of the employee chosen
In a column of the details part of my table I'm calling a function and passing the employee id of that row's data and the parameters selected
=Code.getAdjustedHours(Fields!WipEmp_ID.Value, Parameters!Hours.Value, Parameters!EmployeeID.Value)

Open in new window

My function tests to see if the current employee id matches the one selected from the drop-down and if so it assigns the hours entered to the hours variable
Dim public adjustHours as String
Dim public adjustingEmployee as String

Public Function getAdjustedHours(ByVal e as String, ByVal h as String, ByVal c as String)
	Dim empID as String= e
	adjustingEmployee = c
	adjustHours = "0"
	If (adjustingEmployee = empID) Then
		adjustHours = h
	End if
End Function

Open in new window


This is working great  in that I can change the employee and hours and the table updates appropriately as to who should get the added/subtracted hours.  But it only works for the currently adjusted employee.  If I select a new employee and enter a new hour amount then the previous entry is lost.
Note*  I'm not sending this information to the database; it is intended for PM's to play around with the team's hours and see the changes it would cause to the budget given the varying pay rates

I'd like to change my function so that it uses a two-dimensional array using RowCount to set the size of the array and entering the employee id as the identifier and all 0's as the hours.  Then as the hours are entered I'd use the employee id parameter to attach hours to that employee.  Then under New Hours I'd take the currently recorded hours budgeted and add the hours of the array to the employee's hours to get the new hours.  Then multiply that by their rate the new cost associated with those changes.

Example of my finished table
It's been a number of years since I programmed with VB so I'm at a bit of a loss at how to change the global hours variable to a global array and how to set the hours to 0 initially, then add the hours added.  Any help is greatly appreciated!!!!
0
Comment
Question by:HSI_guelph
  • 4
  • 2
6 Comments
 
LVL 27

Assisted Solution

by:planocz
planocz earned 250 total points
ID: 40000310
I don't know if this will help your problem,
but thiss is all you need for your function.....

 Public Function getAdjustedHours(ByVal empID As String, ByVal adjEmp As String, ByVal adjHrs As String) As String
        If Trim(adjEmp) = Trim(empID) Then
            Return adjHrs
        Else
            Return "0"
        End If
        Return Nothing
    End Function
0
 

Author Comment

by:HSI_guelph
ID: 40015279
Sorry I missed this, I had another question that was on fire.  Is this an improvement on the function I have
Public Function getAdjustedHours(ByVal e as String, ByVal h as String, ByVal c as String)
	Dim empID as String= e
	adjustingEmployee = c
	adjustHours = "0"
	If (adjustingEmployee = empID) Then
		adjustHours = h
	End if
End Function

Open in new window


I'm not sure about returning nothing since I want a column for variance and it will be 0 if that employee hasn't been adjusted.  But what I really need is to incorporate an array.
0
 
LVL 27

Expert Comment

by:planocz
ID: 40016306
The function I have above is the correct way to do it.
If you want somekind of return on the function when the if statements fails, then change the return nothing to return "0".
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Accepted Solution

by:
HSI_guelph earned 0 total points
ID: 40017404
Cool, I'll update that.  Unfortunately it will change when I figure out how to put an array in.  I found this code that lets me pass a field to an array but I need a two-dimensional array that will hold the employee ID and the adjusting hours.  It lets me send one field but I was wondering if it could be modified to take two fields.  I tried adding another ByVal to the function and put values.Add(value, secondvalue) but it said I was sending too many arguments.  Perhaps I need to alter the Dim to initialize it as a two-dimensional array.

Just wondering if you have any suggestions on this before I close the thread down.

Dim values As System.Collections.ArrayList=New System.Collections.ArrayList()
 Function SetText(ByVal value As Integer) As Integer
     values.Add(value)
     return value
 End Function

Open in new window

0
 

Author Comment

by:HSI_guelph
ID: 40033121
I want to give some points for planocz because he tried to help me but I needed help converting from a variable to an array and I still don't' know how to do this.
0
 

Author Closing Comment

by:HSI_guelph
ID: 40041623
This didn't really solve my specific problem but provided some valuable input into what I was currently doing.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql 2014,  lock limit 5 32
VB.Net/LINQ: ".Contains" condition not finding existing result in MSSQL 16 36
vb.net 1 month apart 11 31
sql how to count case when 4 18
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question