Solved

More efficient MS SQL for Reporting Services ledger

Posted on 2008-10-31
2
323 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
How to increase the row limit in Jasper Server.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

726 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