Report with Multiple Tables Join

I am designing a Receipt using Crystal Reports 8.5 Developer, MS SQL 2000 Server and MS Visual Basic 6.0 sp6. My Receipt will grab some data from RCP, RCPGL, RCPDetail and Customer tables.

SQL = SELECT RCP.RCPID, RCPGL.RCPGLID, RCPDetail.RCPDetailID, RCP.RCPDate, RCP.RCPID, RCPGL.GLDate, RCPGL.ChequeNo, RCPGL.GrossAmount, RCPDetail.InvoiceNo, RCPDetail.AmountPaid, Customer.CustomerName FROM RCP, RCPDetail, RCPGL, Customer WHERE RCP.RCPID=RCPDetail.RCPID AND RCP.RCPID=RCPGL.RCPID AND RCP.CustomerID=Customer.CustomerID AND RCP.RCPID= glngRCPID

Let say RCPDetail has 3 Invoices data and RCPGL has 2 cheque transactions, my SQL query will returns 6 records.

What I want is to display a section with 3 records of Invoice and another section to display 2 lines of cheque transactions.

Anyone can give me some recommendation?Group on the record type. If this is implicit from the data (e.g. ChequeNo is non-0 for cheques) then create a formula to return a type and group on the formula.|||Hi JaganEllis,
Thanks for your suggestion. I have found an idea and it is using grouping too. I will create a temporary table call TempReceipt. I'll SELECT data from RCPGL table and INSERT "RCPGL" as the key into TempReceipt and then SELECT data from RCPDetail table and INSERT "RCPDetail" as the key. After that join the TempReceipt table with Customer table using ADO. When display the data in Report, I'll use Group by the key of TempReceipt to separate the data.sql

Report with Multiple DB Tables

Dear All,
I want to get the data from two tables (no relationship exist) and want to display the data in a single report.
From one table i just want to show one record but from the other table i want to show multiple records.
Is it possible ? If yes please suggest me how ?Do you mean thet in first table you have only one record, and in the second multiple records?

Report with 2 or more tables and repeated rows.

Hello,

I'm encountering a problem. The following happens:
I make 2 tables in my report. Both tables have detail rows.
The 2 tables can have a different amount of (repeated)detail rows (e.g. The one table most to the top of the report has 3 detail rows an the other, below the topmost table, has 6)

The thing now is that every detailrow in the bottom table is repeated 3 times(so 3x6 is 18 rows...)
Does anyone know how to solve this problem?

Thanks,

Fedor BrouwerI don't think the 2 tables are affecting each other, unless one is nested inside the other. This will probably be down to the way the row groups have been set up but without more information about the report it's a bit difficult to say.

Report Viewer - add table

Is it possible to add tables (like in the report editor) programmatically to a report ?

I want to use 1 report for an infinite number of datatables.

Hi Friend,

I think this will give you some idea....... Thanks

publicvoid logonInfo()

{

TableLogOnInfo logOnInfo =newTableLogOnInfo();

logOnInfo = oRpt.Database.Tables["<table>"].LogOnInfo;

logOnInfo.ConnectionInfo.ServerName ="<name of Server>";

logOnInfo.ConnectionInfo.DatabaseName ="<name of DB>";

logOnInfo.ConnectionInfo.UserID ="<user>";

logOnInfo.ConnectionInfo.Password ="<pwd>";

logOnInfo.TableName ="<table>";TableLogOnInfos infos =newTableLogOnInfos();

infos.Add(logOnInfo);

CrystalReportViewer1.LogOnInfo = infos;

}

Thank you for reading my reply.

HGONE

|||

Sorry, I forgot to metion that I am using the Microsoft Reports , not Crystal Reports.

Thanks for the reply though!

Drew

Report Table Question

I have a report that contains 3 tables. I want to create a 4th table that contains data from the 3 other tables. Is there a way to reference the value from one of the other tables' textbox in my new table. All of these tables are contained in the same report. The 4th table is basically a summary of the other three tables.

Thanks in advance.

i dont think so, because all the textboxes in the table will be created dynamically and you can't refer those textboxes.

you better go for a stored procedure which fetch data from those tables and create a dataset based on this SP and refer this dataset in your fourth table.

Thanks.

|||I've done this before. You have to name the text box that you are referencing first in its name property. Then in the other table you put =ReportItems!Nameoftextbox

Report Server Setup when query against terabytes of data

Hi all,

Can anyone point me to a resource on the best way to setup and use SSRS 2005 when reporting on tables that could have 1 billion rows. The database in question will have mulitple schema's each being at least 1 terabyte in size.

Thanks

-JW

The size of the database matters much less than the size of the data you want in the report. My advice is to push as much filtering and sorting logic as you can into the back end database and return only the result set you want to display into SSRS 2005. Assuming you filter the data down to 20-100 page reports, going against billions of rows and terabytes of data is not difficult.

Thanks.

Donovan Smith

|||

Thanks Donovan

Report Server database advice

Hi,
is there likely to be any problem with us adding our own custom tables and
stored procedures to the report server database?
We will not be modifying any of the existing objects. Our objects are just
to add some of our own functionality.
thanks
MattI have tried it once.
Didnt have any problems with that!
>--Original Message--
>Hi,
>is there likely to be any problem with us adding our own
custom tables and
>stored procedures to the report server database?
>We will not be modifying any of the existing objects. Our
objects are just
>to add some of our own functionality.
>thanks
>Matt
>
>.
>|||There should be no problem with doing this. The only issue you could run
into is if an upgrade of RS uses a table or SP with the same name. If you
make your names unique enough, there should be no issues.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ravi" <ravikantkv@.rediffmail.com> wrote in message
news:7f8b01c495a1$5d4f7c20$a601280a@.phx.gbl...
> I have tried it once.
> Didnt have any problems with that!
> >--Original Message--
> >Hi,
> >
> >is there likely to be any problem with us adding our own
> custom tables and
> >stored procedures to the report server database?
> >
> >We will not be modifying any of the existing objects. Our
> objects are just
> >to add some of our own functionality.
> >
> >thanks
> >
> >Matt
> >
> >
> >.
> >

Report selection formula

this does not show actual code, I have simplified it to post here.

I have linked two tables in my report

MasterTable
ID : Person Name

DetailTable
ID : workDone

and using a selection formula
{MasterTable.ID} > 1

Fields In report are being displayed.

ID | PersonName | WorkDone

the problem is if Detail Table does not have any related record. Report does not show any thing at all. I expect to view ID and PersonName fields with WorkDone field empty.

can any body help. Thanks a lotIf you use a JOIN or RIGHT JOIN to link the 2 tables together, it will only return a row from the first table (MasterTable ) if there's a match in the second table (DetailTable). If youchange it to a LEFT JOIN, it will return all rows from both Tables.

Ex.

Master Table Rows:
ID | PersonName
-----
01 | Bob
02 | Joe
03 | Bill

DetailTable Rows:
ID | WorkDone
----
01 | Data1
03 | Data3

SELECT M.ID, M.PersonName, D.WorkDone
FROM MasterTable As M
LEFT JOIN DetailTable As D ON M.ID = D.ID

will give you this:

Results:
ID | PersonName | WorkDone
--------
01 | Bob | Data1
02 | Joe | NULL <-- Returns a NULL if there is no matching row in DetailTable
03 | Bill | Data3|||Thanks a lot Malleyo

You are right about SQL part but i am writing a record selection formula in crystal report. can you please help me in this regard.

thanks|||go to your table expert or table expert.. you know the window with the table linked on it.
right clic on one link , link option and select RIGHT JOIN. it will be propqgqted if you've set more than one link.

Luc|||thanks a lot to all those help me, it works now

Report running continuously (forever)

I have a query that uses a lot of joins, subqueries and temp tables (I inherited it and am leary about rewriting it). I tried to rewrite it using table variables at one point, but the run time just got ridiculous for any query spanning more than a few months.

It currently runs as-is in about 3 minutes or less in Management Studio; however, when I try to run it via SRS or the Visual Studios 2005 Designer it runs indefinitely. It also runs indefinitely if I try to refresh the fileds or run it from the data tab (basically any time it has to call the stored procedure).

Does anyone have an idea of how I can get this report to run or know why it runs differently in SRS than in Management Studio?

Here is the very long procedure:

CREATE PROCEDURE [dbo].[ISP_RPT_RENEWAL_RETENTION_TEST]

(

@.FROM DATETIME,

@.TO DATETIME,

@.ASOF DATETIME,

@.REPORT VARCHAR(10) = 'ADHOC')

AS

SET NOCOUNT ON

--DECLARE @.FROM DATETIME

--DECLARE @.TO DATETIME

--DECLARE @.ASOF DATETIME

--DECLARE @.REPORT VARCHAR(10)

--

--SET @.REPORT = 'YTD'

--SET @.FROM = '1/1/2006'

--SET @.TO = '10/31/2006'

--SET @.ASOF = '10/31/2006'

IF (@.REPORT = 'ADHOC' OR @.REPORT IS NULL OR @.REPORT = '')

BEGIN

--Check to see if user has input data

--First of the previous month

IF (@.FROM IS NULL OR @.FROM = '') SET @.FROM = dbo.PreviousMonthEndDate(dbo.PreviousMonthEndDate(GetDate()))+1

IF (@.TO IS NULL OR @.TO = '') SET @.TO = dbo.PreviousMonthEndDate(GetDate())

IF (@.ASOF IS NULL OR @.ASOF = '') SET @.ASOF = dbo.PreviousMonthEndDate(GetDate())

END

ELSE IF @.REPORT = 'MTD'

BEGIN

--Disregard user input and run for MTD

--First of the previous month

SET @.FROM = dbo.PreviousMonthEndDate(dbo.PreviousMonthEndDate(GetDate()))+1

SET @.TO = dbo.PreviousMonthEndDate(GetDate())

SET @.ASOF = dbo.PreviousMonthEndDate(GetDate())

END

ELSE IF @.REPORT = 'YTD'

BEGIN

--Disregard user input and run for YTD

SET @.FROM = dbo.FirstOfTheYear(GetDate())

SET @.TO = dbo.PreviousMonthEndDate(GetDate())

SET @.ASOF = dbo.PreviousMonthEndDate(GetDate())

END

SET @.FROM = dbo.MidnightDate(@.FROM)

SET @.TO = dbo.MidnightDate(@.TO)

SET @.ASOF = dbo.MidnightDate(@.ASOF)

/*** if user runs report before month end is closed, then use data from premium table

if however user runs report after month end close, then pull from reinsurance table ***/

DECLARE @.CHECKDATE DATETIME

SELECT @.CHECKDATE = MAX(MONTHENDDATE) FROM MPATREINSURANCE

WHERE MONTHENDDATE = @.TO

/*********************************************** IDENTIFY ALL RENEWALS ***************************************************/

SELECT DISTINCT

RC.POLICYNO POLICYNO , MCITCUST.CURRENTNAMENO,RC.POLICYID, RC.PMRSEQUENCE, RC.SUBCOMPANYID, RC.POLICYEFFDATE,

MCITCUST.FULLNAME ,MAX(ISNULL(RC1.POLICYNO,RC.PREVIOUSPOLICYNO)) PREVIOUSPOLICYNO ,RC.INSUREDNAMENO,RC.PROCESSINGSTATUS, RC.PMRSTATUS PMRSTATUS,V.COVERAGE

INTO #MPAV_RENEWALS

FROM MPATPMRC RC

INNER JOIN MCITCUST ON

RC.COMPANYID = MCITCUST.COMPANYID AND

RC.INSUREDNAMENO = MCITCUST.NAMENO

LEFT OUTER JOIN MPATCGLUWVERSIONS V ON

V.POLICYID = RC.POLICYID AND

V.PMRSEQUENCE = RC.PMRSEQUENCE AND

V.SUBCOMPANYID = RC.SUBCOMPANYID

INNER JOIN MCITCUST M1 ON

MCITCUST.COMPANYID =M1.COMPANYID AND

MCITCUST.CURRENTNAMENO = M1.CURRENTNAMENO

INNER JOIN MPATPMRC RC1 ON

RC1.COMPANYID = M1.COMPANYID AND

RC1.INSUREDNAMENO = M1.NAMENO

LEFT OUTER JOIN MPATCGLUWVERSIONS V1 ON

V1.POLICYID = RC1.POLICYID AND

V1.PMRSEQUENCE = RC1.PMRSEQUENCE AND

V1.SUBCOMPANYID = RC1.SUBCOMPANYID

WHERE

RC.POLICYTYPE ='CGL' AND

RC.POLICYEFFDATE >= '1/1/1998' AND

--FIND THE VERY FIRST VALID TRANSACTION BY LOOKING AT WHICH ONE OF RN/NB/RW IS THE LATEST

RC.TXNISSUED <= @.ASOF AND

RC1.TXNISSUED <= @.ASOF AND

RC.PMRSEQUENCE = (SELECT MAX(PMRSEQUENCE) FROM MPATPMRC RC2

WHERE RC2.POLICYID = RC.POLICYID AND

RC2.SUBCOMPANYID = RC.SUBCOMPANYID AND

RC2.POLICYYEAR = RC.POLICYYEAR AND

RC2.TXNISSUED <= @.ASOF AND

RC2.TXNTYPE IN ('RN','NB','RW','RE') AND

( (RC2.POLICYNO = RC.POLICYNO AND RC.POLICYNO IS NOT NULL)

OR (RC.POLICYNO IS NULL)

) AND

RC2.PMRSTATUS IN ('HS', 'EX', 'IN','PC','PA')AND

RC2.PROCESSINGSTATUS IN ('*','P','A','I','Q')

)

AND ((RC1.TXNTYPE ='CN' AND RC1.CANCELMETHOD <> 'F') OR (RC1.CANCELMETHOD IS NULL OR RC1.TXNTYPE <> 'CN'))

AND RC1.PMRSEQUENCE = (

SELECT MAX(PMRSEQUENCE) FROM MPATPMRC

WHERE MPATPMRC.POLICYID = RC1.POLICYID AND

MPATPMRC.POLICYNO = RC1.POLICYNO AND

MPATPMRC.TXNISSUED <= @.ASOF AND

MPATPMRC.PMRSTATUS IN ('HS', 'EX', 'IN','CN') AND

MPATPMRC.PROCESSINGSTATUS IN ('*','C')AND

MPATPMRC.TXNTYPE NOT IN ('EA','ER' )) AND

(

(RC1.TXNTYPE <>'CN' AND RC1.POLICYEXPDATE = RC.POLICYEFFDATE ) OR

(RC1.TXNTYPE ='CN' AND RC1.TXNEFFECTIVE = RC.POLICYEFFDATE )

)

AND (V.COVERAGE = 'B' AND V1.COVERAGE = 'P' OR V.COVERAGE = 'P' AND V1.COVERAGE = 'B' OR

V.COVERAGE = V1.COVERAGE)

GROUP BY RC.POLICYNO , MCITCUST.CURRENTNAMENO,RC.POLICYID, RC.PMRSEQUENCE, RC.SUBCOMPANYID, RC.POLICYEFFDATE,

MCITCUST.FULLNAME , RC.INSUREDNAMENO,RC.PROCESSINGSTATUS, RC.PMRSTATUS ,V.COVERAGE

/**************************************FIND LAST VERSION*********************************/

SELECT

RC.POLICYNO,

RC.POLICYYEAR,

RC.POLICYID,

RC.SUBCOMPANYID,

RC.PMRSEQUENCE,

RC.TXNTYPE,

POLICYEXPDATE = CASE WHEN RC.TXNTYPE = 'CN' THEN RC.TXNEFFECTIVE ELSE RC.POLICYEXPDATE END,

REPORTDATES =CASE

WHEN RC.TXNTYPE ='CN' AND RC.TXNEFFECTIVE <= RC.TXNISSUED THEN RC.TXNISSUED

WHEN RC.TXNTYPE ='CN' AND RC.TXNEFFECTIVE > RC.TXNISSUED THEN RC.TXNEFFECTIVE

ELSE RC.POLICYEXPDATE END,

CN_REASON = CASE WHEN RC.TXNTYPE = 'CN' THEN

(CASE WHEN RC.TXNSUBTYPE IN ('PF','NP') THEN 'Non-pay'

WHEN RC.TXNSUBTYPE IN ('IN') THEN 'Ins Request'

WHEN RC.TXNSUBTYPE IN ('UW') THEN 'UW Reasons' ELSE 'Other' END)

Else '' END,

CN_METHOD = CASE WHEN RC.TXNTYPE = 'CN' THEN CANCELMETHOD ELSE '' END,

INS.FULLNAME INSURED,

RC.INSUREDNAMENO,

INS.CURRENTNAMENO,

RC.STATE,

RC.ORIGINCEPTIONDATE

INTO #LAST_VERSION

FROM MPATPMRC RC

INNER JOIN --LOOKING UP THE LATEST VERSION OF POLICIES

(SELECT DISTINCT POLICYNO, POLICYID, SUBCOMPANYID, MAX( PMRSEQUENCE) LASTPMR

FROM MPATPMRC

WHERE

POLICYTYPE ='CGL' AND

PMRSTATUS IN ('HS', 'EX', 'IN','CN') AND

PROCESSINGSTATUS IN ('*','C') AND

TXNTYPE NOT IN ('EA','ER')

AND MPATPMRC.TXNISSUED <= @.ASOF

GROUP BY POLICYNO, POLICYID, SUBCOMPANYID) LV ON

RC.POLICYNO = LV.POLICYNO AND

RC.SUBCOMPANYID = LV.SUBCOMPANYID AND

RC.POLICYID = LV.POLICYID AND

RC.PMRSEQUENCE = LV.LASTPMR

LEFT OUTER JOIN MCITCUST INS ON

RC.INSUREDNAMENO = INS.NAMENO

/*=============================================IDENTIFY RENEWALS FOR THE PERIOD==============================*/

SELECT R.POLICYNO,

R.CURRENTNAMENO,

R.POLICYID,

R.PMRSEQUENCE,

R.SUBCOMPANYID,

R.POLICYEFFDATE,

R.FULLNAME,

R.PREVIOUSPOLICYNO,

R.INSUREDNAMENO,

R.PROCESSINGSTATUS,

R.PMRSTATUS,

R.COVERAGE

INTO #RENEWALS

FROM #MPAV_RENEWALS R

LEFT OUTER JOIN #LAST_VERSION LV ON

R.POLICYNO = LV.POLICYNO

--take off pendings

WHERE LV.CN_METHOD <> 'F' AND

POLICYEFFDATE BETWEEN @.FROM AND @.TO

/* LETS GET RENEWAL BROKER */

SELECT

R.POLICYNO,

P.NAMENO,

C.FULLNAME,

A.CITY

INTO #RN_BRK

FROM #RENEWALS R

INNER JOIN MPATPRODUCERS P ON

R.POLICYID = P.POLICYID AND

R.PMRSEQUENCE = P.PMRSEQUENCE AND

R.SUBCOMPANYID = P.SUBCOMPANYID AND

PAYOR = 1

INNER JOIN MCITCUST C ON

P.NAMENO = C.NAMENO

INNER JOIN MCITADDR A ON

C.MAILINGADDRESS = A.ADDRESSID

/*==============================================GET RENEWING PREMIUMS=========================================*/

--this gives us written premium as of report date

SELECT DISTINCT

R.POLICYNO,

R.POLICYID,

MAX(R.SUBCOMPANYID) SUB,

MAX(MPATPMRC.POLICYEXPDATE) EXPDATE,

MAX(R.PREVIOUSPOLICYNO) EXPIRED_POLICY,

MAX(MPATPMRC.POLICYYEAR) POLICYYEAR,

MAX(MPATPMRC.UNDERWRITER)UW,

RN_DIRECT = SUM(WRITTENPREM1 + WRITTENPREM2 + WRITTENPREM3),

RN_CEDED_REIN = SUM(WRITTENPREM3),

CEDED_RETAINED = 0

INTO #RN_PREM

FROM #RENEWALS R

LEFT OUTER JOIN MPATPMRC MPATPMRC ON

MPATPMRC.POLICYNO = R.POLICYNO

LEFT OUTER JOIN MPATPREMIUMS MPATPREMIUMS ON

MPATPMRC.COMPANYID = MPATPREMIUMS.COMPANYID AND

MPATPMRC.PMRSEQUENCE = MPATPREMIUMS.PMRSEQUENCE AND

MPATPMRC.POLICYID = MPATPREMIUMS.POLICYID AND

MPATPMRC.SUBCOMPANYID = MPATPREMIUMS.SUBCOMPANYID

WHERE @.CHECKDATE IS NULL AND

MPATPMRC.PMRSTATUS NOT LIKE '%P%' AND

((MPATPMRC.TXNISSUED >= MPATPMRC.TXNEFFECTIVE AND MPATPMRC.TXNISSUED BETWEEN @.FROM AND @.TO) OR

(MPATPMRC.TXNEFFECTIVE >= MPATPMRC.TXNISSUED AND MPATPMRC.TXNEFFECTIVE BETWEEN @.FROM AND @.TO))

GROUP BY R.POLICYNO , R .POLICYID

UNION ALL

--this gives us written premium as of report date

SELECT DISTINCT

R.POLICYNO,

R.POLICYID,

MAX(R.SUBCOMPANYID) SUB,

MAX(MPATPMRC.POLICYEXPDATE) EXPDATE,

MAX(R.PREVIOUSPOLICYNO) EXPIRED_POLICY,

MAX(MPATPMRC.POLICYYEAR) POLICYYEAR,

MAX(MPATPMRC.UNDERWRITER) UW,

RN_DIRECT = SUM(MPATREINSURANCE.WRITTENPREM1 + MPATREINSURANCE.WRITTENPREM2 + MPATREINSURANCE.WRITTENPREM3) ,

RN_CEDED_REIN = SUM(MPATREINSURANCE.CEDEDAMOUNT1 + MPATREINSURANCE.CEDEDAMOUNT2 + MPATREINSURANCE.CEDEDAMOUNT3) ,

CEDED_RETAINED= SUM(

(CASE WHEN CEDEDPERCENT1 <> 0 THEN

((1-CEDEDPERCENT1) * WRITTENPREM1) ELSE 0 END)

+

(CASE WHEN CEDEDPERCENT2 <> 0 THEN

((1-CEDEDPERCENT2) * WRITTENPREM2) ELSE 0 END)

+

(CASE WHEN CEDEDPERCENT3 <> 0 THEN

((1-CEDEDPERCENT3) * WRITTENPREM3) ELSE 0 END)

)

FROM #RENEWALS R

LEFT OUTER JOIN MPATPMRC MPATPMRC ON

MPATPMRC.POLICYNO = R.POLICYNO

LEFT OUTER JOIN MPATREINSURANCE MPATREINSURANCE ON

MPATPMRC.COMPANYID = MPATREINSURANCE.COMPANYID AND

MPATPMRC.PMRSEQUENCE = MPATREINSURANCE.PMRSEQUENCE AND

MPATPMRC.POLICYID = MPATREINSURANCE.POLICYID AND

MPATPMRC.SUBCOMPANYID = MPATREINSURANCE.SUBCOMPANYID AND

MPATPMRC.TREATYID = MPATREINSURANCE.TREATYID

WHERE @.CHECKDATE IS NOT NULL AND

MPATPMRC.PMRSTATUS NOT LIKE '%P%' AND

((MPATREINSURANCE.TXNISSUED >= MPATREINSURANCE.TXNEFFECTIVE AND MPATREINSURANCE.TXNISSUED BETWEEN @.FROM AND @.TO) OR

(MPATREINSURANCE.TXNEFFECTIVE >= MPATREINSURANCE.TXNISSUED AND MPATREINSURANCE.TXNEFFECTIVE BETWEEN @.FROM AND @.TO))

GROUP BY R.POLICYNO , R .POLICYID

/*======================= looks for all policies EXPIRED during report time==========================*/

SELECT

LV.POLICYYEAR,

LV.POLICYNO,

LV.POLICYID,

LV.SUBCOMPANYID,

LV.PMRSEQUENCE,

LV.TXNTYPE,

LV.POLICYEXPDATE,

REASONDROPPED =

CASE WHEN LV.TXNTYPE = 'CN' AND CN_REASON = 'Non-pay' AND R.DESCRIPTION IS NULL

THEN 'Cancelled for non pay'

ELSE

ISNULL(ISNULL(R.DESCRIPTION, CLOSEREASONDESC),'No reason entered') END,

CN_METHOD,

V.COVERAGE,

CURRENTNAMENO,

INSUREDNAMENO,

INSURED,

LV.STATE,

ORIGINCEPTIONDATE,

CLOSECATEGORYDESC,

CLOSEREASONDESC,

COMPETITORDESC,

MKTFOLLOWUPDESC

INTO #EXPIRED

FROM #LAST_VERSION LV

LEFT OUTER JOIN MCITINSURED ON

LV.INSUREDNAMENO = MCITINSURED.NAMENO

LEFT OUTER JOIN MSOTDROPREAS R ON

MCITINSURED.REASONDROPPED = R.REASONID

LEFT OUTER JOIN MPATCGLUWVERSIONS V ON

V.POLICYID = LV.POLICYID AND

V.PMRSEQUENCE = LV.PMRSEQUENCE AND

V.SUBCOMPANYID = LV.SUBCOMPANYID

LEFT OUTER JOIN MSOTCLOSECATEGORIES CC ON

MCITINSURED.CLOSECATEGORYID = CC.CLOSECATEGORYID

LEFT OUTER JOIN MSOTCLOSEREASONS CR ON

MCITINSURED.CLOSEREASONID = CR.CLOSEREASONID

LEFT OUTER JOIN MSOTCOMPETITORS CO ON

MCITINSURED.COMPETITORID = CO.COMPETITORID

LEFT OUTER JOIN MSOTMKTFOLLOWUP MK ON

MCITINSURED.MKTFOLLOWUPID = MK.MKTFOLLOWUPID

WHERE REPORTDATES BETWEEN CAST(CAST(@.FROM AS VARCHAR(11)) AS DATETIME) --DF CHANGE

AND CAST(CAST(@.TO AS VARCHAR(11)) AS DATETIME)

/*==============================================GET EXPIRED POLICY INFO=========================================*/

--GET INFO FOR ALL TXNS SO U ALSO GET THE PREMIUMS

SELECT DISTINCT

MAX(MCITCUST.NAMENO) NAMENO,

R.RISKSEQUENCE,

MAX(MCITCUST.CURRENTNAMENO) CURRENTNAMENO,

MAX(MCITCUST.FULLNAME) FULLNAME,

RC.POLICYNO,

RC.POLICYID,

MAX(RC.SUBCOMPANYID) SUB,

MAX(RC.POLICYYEAR) POLICYYEAR,

EXPDATE = MAX(EX.POLICYEXPDATE),

SALES = SUM(CASE WHEN PR.COVERAGETYPE ='P' AND PR.PMRSEQUENCE = EX.PMRSEQUENCE THEN ISNULL(PR.SALES,0) ELSE 0 END),

MAX(RC.UNDERWRITER) UW,

P.HAZARDCLASS,

P.CLASSCODE PRODUCTCODE,

MAX(P.DESCRIPTION) [DESCRIPTION],

DISTRIBUTOR = (CASE WHEN P.DISTRIBUTOR = 1 THEN 'YES' ELSE 'NO' END),--R.PRODUCTCODE

MEMBER = MAX(CASE WHEN MEMBER = 1 THEN 'DEVICE' ELSE 'EXP' END),

SUM(WRITTENPREM1 + WRITTENPREM2) LAYER1_2,

EX_LIMIT = MAX(CASE WHEN V.COV_PROD_AGGREGATE = '1000000' OR V.COV_CGL_AGGREGATE = '1000000' THEN 1

WHEN V.COV_PROD_AGGREGATE = '2000000' OR V.COV_CGL_AGGREGATE = '2000000' THEN 2

WHEN V.COV_PROD_AGGREGATE = '3000000' OR V.COV_CGL_AGGREGATE = '3000000' THEN 3

WHEN V.COV_PROD_AGGREGATE = '4000000' OR V.COV_CGL_AGGREGATE = '4000000' THEN 4

WHEN V.COV_PROD_AGGREGATE = '5000000' OR V.COV_CGL_AGGREGATE = '5000000' THEN 5

WHEN V.COV_PROD_AGGREGATE = '6000000' OR V.COV_CGL_AGGREGATE = '6000000' THEN 6

WHEN V.COV_PROD_AGGREGATE = '7000000' OR V.COV_CGL_AGGREGATE = '7000000' THEN 7

WHEN V.COV_PROD_AGGREGATE = '8000000' OR V.COV_CGL_AGGREGATE = '8000000' THEN 8

WHEN V.COV_PROD_AGGREGATE = '9000000' OR V.COV_CGL_AGGREGATE = '9000000' THEN 9

WHEN V.COV_PROD_AGGREGATE = '10000000' OR V.COV_CGL_AGGREGATE = '10000000' THEN 10 END),

EX_BROKER_GROUP =

(SELECT [DESCRIPTION] FROM MSOTCOMMPLAN

WHERE

SUBCOMPANYID = MPATPRODUCERS.SUBCOMPANYID AND

COMMISSIONPLAN =

(SELECT TOP 1 COMMISSIONPLAN FROM MAGTCOMMPLAN

WHERE NAMENO = MPATPRODUCERS.NAMENO AND SUBCOMPANYID =MPATPRODUCERS.SUBCOMPANYID AND

COMMRATEEFFDATE <= EX.POLICYEXPDATE

ORDER BY COMMRATEEFFDATE DESC )),

MAX(BROKER.FULLNAME) BRK_NAME,

MAX(MPATPRODUCERS.COMMISSIONRATE * 100) RATE,

MAX(AA.CITY) BRK_CITY

INTO #EX

FROM MPATPMRC RC

INNER JOIN MPATPREMIUMS PR ON

RC.POLICYID = PR.POLICYID AND

RC.SUBCOMPANYID = PR.SUBCOMPANYID AND

RC.PMRSEQUENCE = PR.PMRSEQUENCE

INNER JOIN #EXPIRED EX ON

EX.POLICYNO = RC.POLICYNO

INNER JOIN MCITCUST ON

RC.COMPANYID = MCITCUST.COMPANYID AND

RC.INSUREDNAMENO = MCITCUST.NAMENO

LEFT OUTER JOIN MPATCGLUWRISKS R ON

PR.RISKSEQUENCE = R.RISKSEQUENCE AND

EX.POLICYID = R.POLICYID AND

EX.PMRSEQUENCE = R.PMRSEQUENCE AND

EX.SUBCOMPANYID = R.SUBCOMPANYID

LEFT OUTER JOIN MSOTPRODUCTS P ON

R.PRODUCTCODE = P.PRODUCTCODE

LEFT OUTER JOIN MPATCGLUWVERSIONS V ON

V .POLICYID = EX.POLICYID AND

V .PMRSEQUENCE =EX.PMRSEQUENCE AND

V .SUBCOMPANYID = EX.SUBCOMPANYID

INNER JOIN MPATPRODUCERS ON

EX.SUBCOMPANYID = MPATPRODUCERS.SUBCOMPANYID AND

EX.POLICYID = MPATPRODUCERS.POLICYID AND

EX.PMRSEQUENCE = MPATPRODUCERS.PMRSEQUENCE AND

MPATPRODUCERS.PAYOR = 1

INNER JOIN MCITCUST BROKER ON

MPATPRODUCERS.COMPANYID = BROKER.COMPANYID AND

MPATPRODUCERS.NAMENO = BROKER.NAMENO

LEFT OUTER JOIN MCITADDR AA ON

BROKER.MAILINGADDRESS = AA.ADDRESSID

WHERE RC.PMRSTATUS IN ('IN','EX','HS','CN') AND

RC.PROCESSINGSTATUS IN ('*','C')

GROUP BY R.RISKSEQUENCE ,RC.POLICYNO, RC.POLICYID,P.HAZARDCLASS, P.CLASSCODE,

MPATPRODUCERS.SUBCOMPANYID, MPATPRODUCERS.NAMENO, EX.POLICYEXPDATE,P.DISTRIBUTOR

ORDER BY RC.POLICYNO

/*****************************************************************************************************************/

SELECT

R.POLICYNO,

EX_DIRECT = SUM( WRITTENPREM1 + WRITTENPREM2 + WRITTENPREM3),

EX_CEDED_REIN = SUM(WRITTENPREM3),

EX_CEDED_RETAINED= 0

INTO #EX_PREM

FROM MPATPMRC R

INNER JOIN #EXPIRED E ON

R.POLICYNO = E.POLICYNO

LEFT OUTER JOIN MPATPREMIUMS ON

R.PMRSEQUENCE = MPATPREMIUMS.PMRSEQUENCE AND

R.POLICYID = MPATPREMIUMS.POLICYID AND

R.SUBCOMPANYID = MPATPREMIUMS.SUBCOMPANYID

WHERE @.CHECKDATE IS NULL AND

((R.TXNISSUED >= R.TXNEFFECTIVE AND R.TXNISSUED <= @.TO) OR

(R.TXNEFFECTIVE >= R.TXNISSUED AND R.TXNEFFECTIVE <= @.TO))

GROUP BY R.POLICYNO

UNION ALL

SELECT

R.POLICYNO,

EX_DIRECT = SUM(MPATREINSURANCE.WRITTENPREM1 + MPATREINSURANCE.WRITTENPREM2 + MPATREINSURANCE.WRITTENPREM3) ,

EX_CEDED_REIN = SUM(MPATREINSURANCE.CEDEDAMOUNT1 + MPATREINSURANCE.CEDEDAMOUNT2 + MPATREINSURANCE.CEDEDAMOUNT3) ,

EX_CEDED_RETAINED=

SUM(

(CASE WHEN CEDEDPERCENT1 <> 0 THEN

((1-CEDEDPERCENT1) * WRITTENPREM1) ELSE 0 END)

+

(CASE WHEN CEDEDPERCENT2 <> 0 THEN

((1-CEDEDPERCENT2) * WRITTENPREM2) ELSE 0 END)

+

(CASE WHEN CEDEDPERCENT3 <> 0 THEN

((1-CEDEDPERCENT3) * WRITTENPREM3) ELSE 0 END)

)

FROM MPATPMRC R

INNER JOIN #EXPIRED E ON

R.POLICYNO = E.POLICYNO

LEFT OUTER JOIN MPATREINSURANCE ON

R.PMRSEQUENCE = MPATREINSURANCE.PMRSEQUENCE AND

R.POLICYID = MPATREINSURANCE.POLICYID AND

R.SUBCOMPANYID = MPATREINSURANCE.SUBCOMPANYID

WHERE @.CHECKDATE IS NOT NULL AND

((MPATREINSURANCE.TXNISSUED >= MPATREINSURANCE.TXNEFFECTIVE AND MPATREINSURANCE.TXNISSUED <= @.TO) OR

(MPATREINSURANCE.TXNEFFECTIVE >= MPATREINSURANCE.TXNISSUED AND MPATREINSURANCE.TXNEFFECTIVE <= @.TO))

GROUP BY R.POLICYNO

/******************************************************************************************************************/

EXECUTE ISP_RPT_MKTSEGMENT

SET NOCOUNT OFF

SELECT

E.POLICYNO EXP_POLICY,

E.POLICYEXPDATE,

A.UW,

E.INSURED INS_NAME,

STATE INS_STATE,

BROKER,

BROKER_GROUP = CASE WHEN BROKER_GRP LIKE '%Others%' THEN 'Others'

WHEN BROKER_GRP LIKE '%Preferred%' THEN 'Preferred'

WHEN BROKER_GRP LIKE '%Marsh%' THEN 'Marsh'

WHEN BROKER_GRP LIKE '%Champion%' THEN 'Champion'

ELSE 'Others' END, COMM_RATE,

DOMINANT_PROD DOM_PROD,

DOMINANT_PROD_DESC DESCR,

MK.DISTRIBUTOR,

DOMINANT_HAZ DOM_HAZD,

EX_LIMIT,

MEMBER,

A.SALES,

MKT_SEGMENT,

ISNULL(EX_DIRECT,0) TOTAL_WRTTN,

(ISNULL(EX_CEDED_REIN,0) + ISNULL(EX_CEDED_RETAINED,0)) TOTAL_CEDED,

CEDED_REINSURE = CASE WHEN @.CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_CEDED_REIN,0) END ,

CEDED_RETAINED = CASE WHEN @.CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_CEDED_RETAINED,0) END ,

NWP_2M = CASE WHEN @.CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_DIRECT,0) - ((ISNULL(EX_CEDED_REIN,0) + ISNULL(EX_CEDED_RETAINED,0))) END,

XOL_NWP = CASE WHEN @.CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_DIRECT,0) - ISNULL(EX_CEDED_REIN,0) END,

REASON_LOST =

CASE WHEN R.POLICYNO IS NULL THEN

(SELECT 'LOST -'+REASONDROPPED FROM #EXPIRED WHERE POLICYNO = E.POLICYNO)

ELSE '' END,

ISNULL(R.POLICYNO, '') RN_POLICY,

ISNULL(RN_DIRECT,0) RN_TOTAL_WRTTN,

(ISNULL(RN_CEDED_REIN,0) + ISNULL(CEDED_RETAINED,0)) RN_TOTAL_CEDED,

RN_CEDED_REINSURE = CASE WHEN @.CHECKDATE IS NULL THEN 0 ELSE ISNULL(RN_CEDED_REIN,0) END ,

RN_CEDED_RETAINED = CASE WHEN @.CHECKDATE IS NULL THEN 0 ELSE ISNULL(CEDED_RETAINED,0) END ,

RN_NWP_2M = CASE WHEN @.CHECKDATE IS NULL THEN 0 ELSE ISNULL(RN_DIRECT,0) - ((ISNULL(RN_CEDED_REIN,0) + ISNULL(CEDED_RETAINED,0))) END,

RN_XOL_NWP = CASE WHEN @.CHECKDATE IS NULL THEN 0 ELSE ISNULL(RN_DIRECT,0) - ISNULL(RN_CEDED_REIN,0) END,

[INSURED INFORCE TODAY?] = CASE WHEN (SELECT COUNT(*)

FROM MPATPMRC

WHERE PMRSTATUS ='IN' AND

INSUREDNAMENO IN

(SELECT NAMENO FROM

MCITCUST WHERE CURRENTNAMENO = E.CURRENTNAMENO)

)>0 THEN 'YES' ELSE 'NO' END,

[FIRST_JOINED_DATE] =

ISNULL(

(SELECT MIN(DATEJOINED) FROM MCITINSURED

WHERE NAMENO IN

(SELECT DISTINCT NAMENO FROM

MCITCUST WHERE CURRENTNAMENO = E.CURRENTNAMENO)), ORIGINCEPTIONDATE),

[# OF POLICY YEARS] = (

SELECT COUNT(DISTINCT POLICYYEAR)

FROM MPATPMRC

WHERE PMRSTATUS IN ('EX','HS','IN') AND TXNTYPE IN ('CV','NB','RN','RW') AND PROCESSINGSTATUS ='*'

AND POLICYNO IS NOT NULL AND

INSUREDNAMENO IN (SELECT DISTINCT NAMENO FROM

MCITCUST WHERE CURRENTNAMENO = E.CURRENTNAMENO)),

MONTHS = (CASE WHEN MONTH(E.POLICYEXPDATE) = 1 THEN 'January'

WHEN MONTH(E.POLICYEXPDATE) = 2 THEN 'February'

WHEN MONTH(E.POLICYEXPDATE) = 3 THEN 'March'

WHEN MONTH(E.POLICYEXPDATE) = 4 THEN 'April'

WHEN MONTH(E.POLICYEXPDATE) = 5 THEN 'May'

WHEN MONTH(E.POLICYEXPDATE) = 6 THEN 'June'

WHEN MONTH(E.POLICYEXPDATE) = 7 THEN 'July'

WHEN MONTH(E.POLICYEXPDATE) = 8 THEN 'August'

WHEN MONTH(E.POLICYEXPDATE) = 9 THEN 'September'

WHEN MONTH(E.POLICYEXPDATE) = 10 THEN 'October'

WHEN MONTH(E.POLICYEXPDATE) = 11 THEN 'November'

WHEN MONTH(E.POLICYEXPDATE) = 12 THEN 'December' END),

BRK_CITY,

RB.FULLNAME RN_BROKER,

RB.CITY RN_BRK_CITY,

ORIG_EXP =

(SELECT MIN(POLICYEXPDATE) POLICYEXPDATE

FROM

MPATPMRC

WHERE POLICYNO = E.POLICYNO AND TXNTYPE IN ('NB','RN','RW')) ,

DROP_CATEGORY = CASE WHEN R.POLICYNO IS NULL THEN CLOSECATEGORYDESC ELSE '' END ,

DROP_REASON = CASE WHEN R.POLICYNO IS NULL THEN CLOSEREASONDESC ELSE '' END ,

COMPETITOR = CASE WHEN R.POLICYNO IS NULL THEN COMPETITORDESC ELSE '' END ,

MKTFOLLOWUP = CASE WHEN R.POLICYNO IS NULL THEN MKTFOLLOWUPDESC ELSE '' END,

MKT_SEGMENTSORT = MS.SORTORDER

FROM #EXPIRED E

LEFT OUTER JOIN #RENEWALS R ON

E.POLICYNO = R.PREVIOUSPOLICYNO

LEFT OUTER JOIN

(SELECT POLICYNO, SUM(SALES)SALES, MAX(UW) UW, SUM(LAYER1_2) PREM_2MIL, MAX(EX_LIMIT) EX_LIMIT,

MAX(BRK_NAME) BROKER, MAX(EX_BROKER_GROUP) BROKER_GRP, MAX(RATE) COMM_RATE, MAX(BRK_CITY) BRK_CITY

FROM #EX

GROUP BY POLICYNO ) A ON

E.POLICYNO = A.POLICYNO

LEFT OUTER JOIN #EX_PREM EP ON

E.POLICYNO = EP.POLICYNO

LEFT OUTER JOIN #RN_PREM RP ON

R.POLICYNO = RP.POLICYNO

LEFT OUTER JOIN MPAT_MKTSEGMENTS MK ON

A.POLICYNO = MK.POLICYNO

LEFT OUTER JOIN MSOTMARKETSEGMENT MS ON

MK.MKT_SEGMENT = MS.MARKETSEGMENT

LEFT OUTER JOIN #RN_BRK RB ON

R.POLICYNO = RB.POLICYNO

WHERE ISNULL(E.CN_METHOD,'X') <> 'F'

ORDER BY R.POLICYNO

Looks like something I just worked on, coincidentally in insurance too.

There are a couple of workarounds other than optimizing the query. RS & VS don't like temp tables.

One option may be to create the temp table structure at the beginning of the stored proc and then use insert into rather than select into syntax. Not sure if this will solve the issue though.

Another would be to create a wrapper stored proc that would return an empty dataset with the structure you need. Then point to the real stored proc when running the report.

A final one would be to use a nested query (derived table) instead of a temp table.

cheers,

Andrew

|||Thanks for the response. I'll try it out. I wish there was a better answer to why RS and VS don't like temp tables though. Seems like that should be addressed by MS as temp tables are about as standard as they come.|||

Another strange development...

When I try to run the stored procedure in my test environment via Management Studio (a restore from the previous night's production backup on another server) it also runs continuously. So, I have a stored proc in production that runs fine, the same stored proc in test that doesn't, and it also doesn't work correctly when I try to run it from the production stored proc via SRS.

|||

You could try using SQL profiler to see what's going on.

Here is another reason to move to derived tables.

http://www.sql-server-performance.com/jg_derived_tables.asp

cheers,

Andrew

|||

I have it fixed.

Here's a little more info for you. I use this stored proc in two reports (one for adhoc use and one for a monthly auto-run report). Last month I added two new summary tables to the adhoc report that both use the same dataset. Then the user called me this passed week and asked why these two new summary tables weren't in the monthly report. So, I added them and told him to get the data through the adhoc report for this month. That's when he told me the report wasn't working (same with the monthly report once the tables were added).

So, I have since removed the two tables from the adhoc report (it ran fine) and then added them back in manually (still works fine). I will be doing the same thing to the monthly report now as well.

However, now I have two outstanding oddities 1) why did adding the two tables cause it to run forever in the first place, but then removing and readding them makes it run fine & 2) why does the stored proc run forever in my test environment using enterprise manager. Now, I don't really need those two things answered because the issue is resolved, but it sure would help troubleshooting on future report issues.

Thanks for your posts even though I led you in the wrong direction.