Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

More efficient MS SQL for Reporting Services ledger

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)
FROM dbo.tickets INNER JOIN
(SELECT tickets_1.ticket_number AS ticket_number_1, tickets_1.client_id       AS client_id_1, tickets_extra_1.date AS date_1
FROM dbo.tickets AS tickets_1 INNER JOIN dbo.tickets_extra AS tickets_extra_1 ON tickets_1.id = tickets_extra_1.id
       WHERE (tickets_1.id = @ticket_id)) AS derived_table ON dbo.tickets.client_id = derived_table.client_id_1 LEFT OUTER JOIN dbo.tickets_extra AS tickets_extra_1 ON dbo.tickets.id = tickets_extra_1.id
WHERE (tickets_extra_1.date < derived_table.date_1) OR
      (tickets_extra_1.date = 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.ticket_number, dbo.tickets.name, dbo.tickets_extra.date, dbo.tickets_extra.hours, dbo.tickets_extra.balance
FROM         dbo.tickets LEFT OUTER JOIN dbo.tickets_extra ON dbo.tickets.id = dbo.tickets_extra.id
WHERE     (dbo.tickets.client_id = $client_id) AND (dbo.tickets_extra.date > $begin_date) OR
                      (dbo.tickets_extra.date < $end_date)
ORDER BY dbo.tickets_extra.date, dbo.tickets.ticket_number
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)
      FROM dbo.tickets INNER JOIN
            (SELECT TOP (100) PERCENT tickets_extra_1.date AS date_1, tickets_1.ticket_number AS ticket_number_1
            FROM dbo.tickets AS tickets_1 INNER JOIN dbo.tickets_extra AS tickets_extra_1 ON tickets_1.id = tickets_extra_1.id
        WHERE (tickets_1.client_id = @client_id)
        ORDER BY date_1 DESC) AS derivedtable ON dbo.tickets.client_id = @client_id LEFT OUTER JOIN dbo.tickets_extra AS tickets_extra_1 ON dbo.tickets.id = tickets_extra_1.id
      WHERE (tickets_extra_1.date < derivedtable.date_1) OR (tickets_extra_1.date = derivedtable.date_1) AND (dbo.tickets.ticket_number <= 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?

1 Solution
jdroger2Author Commented:
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?  
Mark WillsTopic AdvisorCommented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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