SSRS Error when I use RunningValue function

My clients buy hours from me to use on various trouble tickets.  I have a MSSQL database of trouble tickets that looks like this:

Table tickets with columns: id, name, client_id, case_number
Table tickets_extra with columns: id, hours, date

 I've added a report that uses a calculated field in the dataset.  I defined CalculatedHoursBalance like this:

CalculatedHoursBalance=RunningValue(Fields!hours.Value,Sum,nothing)

I cannot run the report like this because if I try to preview it I get:

Error      1      An internal error occurred on the report server. See the error log for more details.      V:\Visual Studio 2005\Projects\Test Reports\Test Reports\Client Ledger.rdl      0      0      
 
Any ideas?  
SELECT tickets_extra.date, tickets.name, tickets.ticket_number, tickets_extra.hours
FROM tickets LEFT OUTER JOIN tickets_extra ON tickets.id = tickets_extra.id
WHERE (tickets.client_id = @client_id)

Open in new window

LVL 1
jdroger2Asked:
Who is Participating?
 
jgvConnect With a Mentor Commented:
I never mentioned the database/sql table. You cannot use an aggregate function in a calculated field (which I did look into). Aggregates are used in whatever object is displaying the data within the body of the report. If you have a table in the report then you would use the RunningValue function in one of the table cells as part of the cell's expression.
0
 
jgvCommented:
Hmm, I've never tried aggregating from a calculated field in a dataset but I'm pretty sure that it's not possible (could be wrong though). Any particular reason you are trying to do this from a calculated field and not on the report itself?
0
 
jdroger2Author Commented:
I meant that the report has the calculated field, not the database.  I added the calculation to the dataset, not the SQL table.  
0
 
jdroger2Author Commented:
Thanks so much!  The report runs now!  The only thing with doing it this way is:

Lets say the data exists from 01/01/2007 to present.  If I want to run a report that shows only the last three months of data, but the balance column needs to start out with a balance that includes all of the data, I guess sort of an opening balance.  Any ideas on how to do this?

Thanks!
0
 
jgvCommented:
One method would be to include the opening balance in the dataset as another field (pull this from the sql query). This means that it will be repeated with every record so you would have to be careful how you used it. If it's just for display purpose and you won't be adding it to another amount then it should work. I've used this in the past but it does not work in every situation
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.