More efficient MS SQL for Reporting Services ledger

Posted on 2008-10-31
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 (when simplified):

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

I need to create two reports in SSRS:
1.      A ledger for a given clients hours sorted by date and then case number.
2.      A table of clients and their current hours balance.
I need to find an efficient way to generate these reports.  I would prefer a simple query, but here is the way I have it working now:
A.      I create a user defined function CalculateBalanceForTicket(@ticket_id) like this:
CREATE FUNCTION CalculateBalanceForTicket (@ticket_id varchar(50))
      DECLARE @balance float
SELECT @balance = SUM(tickets_extra_1.hours)
(SELECT tickets_1.ticket_number AS ticket_number_1, tickets_1.client_id       AS client_id_1, AS date_1
FROM AS tickets_1 INNER JOIN dbo.tickets_extra AS tickets_extra_1 ON =
       WHERE ( = @ticket_id)) AS derived_table ON = derived_table.client_id_1 LEFT OUTER JOIN dbo.tickets_extra AS tickets_extra_1 ON =
WHERE ( < derived_table.date_1) OR
      ( = derived_table.date_1) AND (ticket_number <= derived_table.ticket_number_1)
      RETURN @balance
B.      Then I created a new computed column on tickets_extra called balance whose formula is CalculateBalanceForTicket(id)
C.      Now I can make Report #1 with this query:
SELECT     TOP (100) PERCENT,,, dbo.tickets_extra.hours, dbo.tickets_extra.balance
FROM LEFT OUTER JOIN dbo.tickets_extra ON =
WHERE     ( = $client_id) AND ( > $begin_date) OR
                      ( < $end_date)
D.      Then I created another user defined function CalculateBalanceForClient() like this:
CREATE FUNCTION CalculateBalanceForClient (@client_id varchar(50))
      DECLARE @balance float
      SELECT @balance = SUM(tickets_extra_1.hours)
            (SELECT TOP (100) PERCENT AS date_1, tickets_1.ticket_number AS ticket_number_1
            FROM AS tickets_1 INNER JOIN dbo.tickets_extra AS tickets_extra_1 ON =
        WHERE (tickets_1.client_id = @client_id)
        ORDER BY date_1 DESC) AS derivedtable ON = @client_id LEFT OUTER JOIN dbo.tickets_extra AS tickets_extra_1 ON =
      WHERE ( < derivedtable.date_1) OR ( = derivedtable.date_1) AND ( <= derivedtable.ticket_number_1)
      RETURN @balance
E.      Now I can make Report #2 with this query:
SELECT name, dbo.CalculateBalanceForClient(id) AS Balance
FROM dbo.clients

Report #2 loads reasonably fast, but Report #1 takes FOREVER even on clients with only 50 or so tickets.  How can I do this more efficiently so the report (#1) will load?

Question by:jdroger2

Author Comment

ID: 22873291
I think I have made some progress on my own:

It seems like to have a report with a running total I need to do the calculations in the report and not in the actual database.  I've added a new report that doesn't use the balance column, instead 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?  
LVL 51

Accepted Solution

Mark Wills earned 500 total points
ID: 22966693
It does sound and look very complex, and supposing that it is over engineered... e.g. report query number 2 is so close to the function, thaqt all you really need to to group by customer (though have not checked recursiveness or dependancy within the sub-query on the client number)

For the first report - what determines date ? Is it just the date of the ticket, or is it more like a calendar report showing a range of date and then plugging in the information as needed ?

What is the query for report 1 - you show us report 2 query, and guess it is the same ?


Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Hello, In my precious Article  ( saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

910 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

21 Experts available now in Live!

Get 1:1 Help Now