[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 762
  • Last Modified:

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

0
jdroger2
Asked:
jdroger2
  • 3
  • 2
1 Solution
 
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
 
jgvCommented:
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
 
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now