SSRS Error when I use RunningValue function

Posted on 2008-11-03
Last Modified: 2012-05-05
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:


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.ticket_number, tickets_extra.hours

FROM tickets LEFT OUTER JOIN tickets_extra ON =

WHERE (tickets.client_id = @client_id)

Open in new window

Question by:jdroger2
    LVL 12

    Expert Comment

    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?
    LVL 1

    Author Comment

    I meant that the report has the calculated field, not the database.  I added the calculation to the dataset, not the SQL table.  
    LVL 12

    Accepted Solution

    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.
    LVL 1

    Author Comment

    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?

    LVL 12

    Expert Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now