Solved

More efficient MS SQL for Reporting Services ledger

Posted on 2008-10-31
2
307 Views
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))
RETURNS float
AS
BEGIN
      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
END
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))
RETURNS float
AS
BEGIN
      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
END
E.      Now I can make Report #2 with this query:
SELECT name, dbo.CalculateBalanceForClient(id) AS Balance
FROM dbo.clients
ORDER BY name

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?

Thanks!
0
Comment
Question by:jdroger2
2 Comments
 
LVL 1

Author Comment

by:jdroger2
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:

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?  
0
 
LVL 51

Accepted Solution

by:
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 ?

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

How to increase the row limit in Jasper Server.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

705 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

19 Experts available now in Live!

Get 1:1 Help Now