October 30, 2010 at 1:35 am
Hi Guys,
Im in the process of doing some ammendments to a application that will generate a report showing all values then a total for all the value and then create a Excel Spreadsheet.
The excel Spreedsheet part of it is written in Vb.Net.
But when this report is generated it creates everything which is fine but unfortunately it adds the total value to the bar graph this is not what i want,
i want to be able to see the total but i dont want it to generate a bar within the Bar Graph it selfs, if come across this issue on a couple of SQL Scripts, iv provided an example of one of the SQL Scripts below.
Can you please tell me how i can achieve the above.
USE [ParagonMI]
GO
/****** Object: StoredProcedure [dbo].[rpt_HS_Breakdown_Exc_Claims] Script Date: 10/30/2010 08:27:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[rpt_HS_Breakdown_Exc_Claims]
@insurer varchar(30),
@lwrDate datetime,
@uprDate datetime
AS
BEGIN
Select
Trantype,Count(*) as Policies,
Convert(Decimal(10,2),Sum(Orig_Debt)) as TotalPre,
Convert(Decimal(10,2),
CASE
WHEN
Count(*) = 0 then 0
ELSE
Sum(Orig_Debt)/Count(*)
END )
as AvgPre
From [vw_InfoHomeMI]
Where
Insco=@insurer
And (Term_Code Is Null or Term_Code<> 'NTU')
And Trantype <>'Charge'
And Datecreated>=@lwrDate
And Datecreated<=@uprDate
Group By trantype
Union
Select
'Total' as Trantype,
Count(*) as Policies,
Sum(Orig_Debt) as TotalPre,
Convert(Decimal(10,2),
CASE
WHEN
Count(*) = 0 then 0
ELSE
Sum(Orig_Debt)/Count(*)
END )
as AvgPre
From [vw_InfoHomeMI]
Where
Insco=@insurer
And (Term_Code Is Null or Term_Code<> 'NTU')
And Trantype <>'Charge'
And Datecreated>=@lwrDate
And Datecreated<=@uprDate
END
it brings back a value like the below.
Trantype Policies TotalPre AvgPre
Adjustment 144 -577.01 -4.01
Cancellation 1365 -208435.77 -152.70
Endoresement 2673 13291.86 4.97
New Business 20592 3685738.51 178.99
Reneral 636 111831.90 175.84
Total 25778 3660308.83 141.99
transfrd NB 368 58459.34 158.86
As you can see Total is 2nd from the bottom and i would like that at the bottom of the table and also not to generate a bar graph within the Chart whens this app is started.
Hope some one can help me.
Thanks in advance.
October 30, 2010 at 2:06 am
is this a graph produced in an SSRS report?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 30, 2010 at 2:09 am
No its not,
Within VB i have referenced the stored procedure,
then when this is referenced it will excute and return the values in a DataSet and then generate a Excel Report,
The excel part is written in Vb.net
October 30, 2010 at 6:21 am
You should look into WITH ROLLUP (see BOL for details) to get your total value instead of using the UNION.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply