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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.