Comparing individual performance to overall average

Hello. I'll begin by stating that I've only been using BusinessObjects Crystal Reports XI for a short amount of time--somewhere around two months. This problem is rather frustrating as I'm sure there must be a more direct means of solving it (i.e. avoiding subreports and hackish workarounds of printtime formula evaluations, etc...)

Briefing: I am connecting to a simple single-element Business View (on top of MS SQL Server). Each record in the view is a trouble ticket handled by my group at work. Using simple date arithmetic, I am able to determine how long it takes for each trouble ticket to go from an open state to a closed state. Naturally, it is crucial to know how quickly these tickets are being resolved.

All is simple, so far. The objective behind the report in question is to analyze how long, on average, one client's tickets take to close against the average for all clients--grouped by fiscal quarter. The company/companies to compare against the overall average are determined by a parameter.

To restate: I have one level of grouping for fiscal Quarter, beginning with 2005 Q1. Under this I need to group by Company, and this needs to be based on the aforementioned string array parameter. The trick is that I also need to preserve the data for all clients while simultaneously isolating the selected customers. The data will be used to make a multi-bar graph.

So...how do I go about grouping and sorting and selecting and whatnot as to achieve my desired effect? As I alluded to above, I feel that there is a convoluted way to approach this problem, and Im looking for some wise soul to inject some wisdom.

Any and all help would be much appreciated.EDIT:

I've solved the problem. If anyone would like to know how, simply ask.sqlsql

Compare 2000 with 2005 Performance - 32/64 Decide to buy?

I need to show my boss that 2005 will on average be faster than 2000.

Are there any performance benchmark results available to show this?

Also I need similar benchmarks to show 64 bit will be faster than 32 bit SQL 2005.

Ian

Try:

http://download.microsoft.com/download/a/4/7/a47b7b0e-976d-4f49-b15d-f02ade638ebe/SQLServer2005_WhyUpgrade.doc

|||I'm looking specifically for the answer to," Why move to 64 bit from 32 bit"

The document is good, but only addresses moving from 2000 to 2005 and not justification of moving hardware to 64 bit.

Ian

|||The main reason to go 64bit is better memory performance for large amounts of memory (ie 16gb+), and better CPU performance for 64bit applications. The other reason is, everything will be 64bit at some time. It is the future.

Now the problems. The main problem you will have with 64bit is with ODBC drivers and other 3rd party drivers not supporting 64bit.

Before you even entertain the idea of changing, check to make sure everything you run on the server is compatable and SUPPORTED in Windows 2003 64bit.

|||

FYI

I have a few 2005 (SS2k5 x64) installs and many 2000 (SS2K x32) installs.

Last evening a developer emailed and said her select was taking to long to run, 22 minutes on the SS2K x32. I cut\pasted her select and executed it on a new X64 Sql Server 2005 SP2 (3050). The databases are identical as the X64 will be her new Sql server.

The sql ran in 36 seconds.

I look good

|||

See if this helps:

http://www.microsoft.com/sql/editions/64bit/default.mspx

Compare 2000 with 2005 Performance - 32/64 Decide to buy?

I need to show my boss that 2005 will on average be faster than 2000.

Are there any performance benchmark results available to show this?

Also I need similar benchmarks to show 64 bit will be faster than 32 bit SQL 2005.

Ian

Try:

http://download.microsoft.com/download/a/4/7/a47b7b0e-976d-4f49-b15d-f02ade638ebe/SQLServer2005_WhyUpgrade.doc

|||I'm looking specifically for the answer to," Why move to 64 bit from 32 bit"

The document is good, but only addresses moving from 2000 to 2005 and not justification of moving hardware to 64 bit.

Ian

|||The main reason to go 64bit is better memory performance for large amounts of memory (ie 16gb+), and better CPU performance for 64bit applications. The other reason is, everything will be 64bit at some time. It is the future.

Now the problems. The main problem you will have with 64bit is with ODBC drivers and other 3rd party drivers not supporting 64bit.

Before you even entertain the idea of changing, check to make sure everything you run on the server is compatable and SUPPORTED in Windows 2003 64bit.

|||

FYI

I have a few 2005 (SS2k5 x64) installs and many 2000 (SS2K x32) installs.

Last evening a developer emailed and said her select was taking to long to run, 22 minutes on the SS2K x32. I cut\pasted her select and executed it on a new X64 Sql Server 2005 SP2 (3050). The databases are identical as the X64 will be her new Sql server.

The sql ran in 36 seconds.

I look good

|||

See if this helps:

http://www.microsoft.com/sql/editions/64bit/default.mspx

Common Expressions Format syntax errror!

Hi All
I have three data sets in a report.
I am trying to format an average based on of the data sets (pManagerAvg),
here is what i have :
= " Average hrs/manager --> Total Finished: " & Avg(Fields!
Measures_Finished_Count.Value), "pManagerAvg")
This works fine, however I get 156.8956235698 as a result. I would like to
show only 156 withput the decimal. I tried Format as follows, as per BOL
without the adtaset specification:
= " Average hrs/manager --> Total Finished: " & Avg(Format(Fields!
Measures_Finished_Count.Value,"#,##)), "pManagerAvg") and i am getting
"The value expression for the textbox ?textbox1? contains an error: Format
specifier was invalid."
Can anyone help here to get the proper Format working.
Thank you in advance.
--
Message posted via http://www.sqlmonster.comThe Format function call has to be applied to the result of the Avg
function.
= " Average hrs/manager --> Total Finished: " &
Format(Avg(Fields!Measures_Finished_Count.Value, "pManagerAvg"), "#,##")
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"James Woo via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:63c20858525d42e6bd8708d9ca4c589c@.SQLMonster.com...
> Hi All
> I have three data sets in a report.
> I am trying to format an average based on of the data sets (pManagerAvg),
> here is what i have :
> = " Average hrs/manager --> Total Finished: " & Avg(Fields!
> Measures_Finished_Count.Value), "pManagerAvg")
> This works fine, however I get 156.8956235698 as a result. I would like to
> show only 156 withput the decimal. I tried Format as follows, as per BOL
> without the adtaset specification:
> = " Average hrs/manager --> Total Finished: " & Avg(Format(Fields!
> Measures_Finished_Count.Value,"#,##)), "pManagerAvg") and i am getting
> "The value expression for the textbox 'textbox1' contains an error: Format
> specifier was invalid."
> Can anyone help here to get the proper Format working.
> Thank you in advance.
> --
> Message posted via http://www.sqlmonster.com|||Thank you very much. :)
--
Message posted via http://www.sqlmonster.com