I am trying to produce a report that should show summary and detail report for Stores & Districts.
For Example:

TABLE FILE XXX
SUM
SALES
BY DIVISION
WHERE DIVISION EQ XYZ
SUM
SEALES
BY DIVISION NOPRINT
BY STORE
WHERE DIVISION EQ XYZ
END
IT doesnt work because if If user EXCEL2k it produce multiple sheets with OPEN and CLOSE commands. I was wondering if someone can pass me idea.
I looked at previous posts but it seems like most of the post are talking about drillthourh, drilldown, or compound report. But I dont think this is a compound rpeort its the same report top part shall show summary information and bottm part of report should show detail. Is it possible?

This message has been edited. Last edited by: Kerry,

Arif,

Read the manual.


set compound = open nobreak

SET COMPOUND = OPEN NOBREAK

TABLE FILE XXX
SUM
SALES
BY DIVISION
WHERE DIVISION EQ XYZ
ON TABLE PCHOLD FORMAT EXL2K
END

SET COMPOUND = CLOSE

TABLE FILE XXX
SUM
SEALES
BY DIVISION NOPRINT
BY STORE
WHERE DIVISION EQ XYZ
ON TABLE PCHOLD FORMAT EXL2K
END

You could also try something like this:

FILEDEF HOLD DISK HOLD.FTM (APPEND

SET HOLDLIST=PRINTONLY

TABLE FILE CAR
SUM COMPUTE LEVEL/I2 = 1;
    DCOST RCOST
BY COUNTRY
ON TABLE HOLD FORMAT ALPHA
END

TABLE FILE CAR
SUM COMPUTE LEVEL/I2 = 2;
    DCOST RCOST CAR
BY COUNTRY
BY CAR NOPRINT
ON TABLE HOLD FORMAT ALPHA
END

TABLE FILE CAR
SUM COMPUTE LEVEL/I2 = 3;
    DCOST RCOST CAR MODEL
BY COUNTRY
BY CAR NOPRINT
BY MODEL NOPRINT
ON TABLE HOLD FORMAT ALPHA
END

TABLE FILE HOLD
PRINT DEALER_COST RETAIL_COST
BY COUNTRY
BY CAR
BY MODEL
BY LEVEL NOPRINT
END

Hope this helps a bit ...

GamP,

I got a question about that.
We want to use this to spread the load on the dbms.
But how do I clean up the HOLD afterwards?

Unless you specify a physical location in a FILEDEF or use APP HOLDDATA to save the HOLD files, they get deleted at the end of the fex execution. The HOLD files are temporarily created in the "EDATEMP" area of the server (usually /ibi/srv76/wfs/edatemp) and are deleted at the end of execution.

Thanks all for your help. Francies I saw in previos posts that you have answered a lot of SQL related questions. For this very report I am bringing in SQL Erocedure but this procedure is failing with the following code:

 (FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF)
 : SQL OLE DB Interface: HResult: 0x00000000: (null); No Error Information
 : could be obtained from the OLE DB Datasource
 L    (FOC1405) SQL PREPARE ERROR.

  

I have no problem executing SQL procedure in SQL managment studio. Also, I dont have problem calling other stored procedurs in IBI. This procedure is using some #TEMP tables maybe thats the reason. Is there is way to get this work?

 
ENGINE SQLMSS SET DEFAULT_CONNECTION LCG_MIDDLEWARE

SQL SQLMSS PREPARE SQLOUT FOR
EXEC usp_SugarUsageReport '2010/10/29'
END
 

 
USE [LCG_MIDDLEWARE]
GO
/****** Object:  StoredProcedure [dbo].[usp_SugarUsageReport]    Script Date: 11/09/2010 11:10:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_SugarUsageReport]
      @Enddate varchar(10)
AS 

DECLARE @Enddt DATETIME;

SELECT @Enddt = CONVERT(datetime, @Enddate)

SELECT   c.channel, s.division_c division, s.district, s.schoolid, t.FYP, t.FYPW, s2.latest_schoolinfo_modify_date
INTO	#school
FROM (select 'Contact Center Leads' channel UNION select 'School Leads') c,
	dim_time as t,
	vw_accounts_schools s 
LEFT OUTER JOIN (SELECT parent_id school, CONVERT(varchar(10),MAX(date_created),101) latest_schoolinfo_modify_date
		FROM db_lcgce_sugar.dbo.accounts_audit
		WHERE LEN(created_by) = 4
		AND field_name = 'date_modified'
		GROUP BY parent_id) s2 ON s.id = s2.school
WHERE	t.daybegintime <= @Enddt
AND		t.fyp  in (select distinct top 4 fyp from dim_time where  daybegintime <= @enddt order by 1 desc)
AND		s.school_status = 'Open'
AND		s.division not IN (SELECT value FROM lcg_repcontrol WHERE class ='FRANDIV')
group by c.channel, s.division_c, s.district, s.schoolid, t.FYP, t.FYPW, s2.latest_schoolinfo_modify_date
--order by s.schoolid,c.channel,t.fypw

SELECT 'Contact Center Leads' channel, m.school_id schoolid, t.FYP, t.FYPW, 
                    COUNT(1) tourscheduled,
                    SUM(case when class='USAGEREP_TNS' then 1 else 0 end) tournoshow, 
                    SUM(case when class='USAGEREP_ENR' then 1 else 0 end) enrolled,
                    SUM(case when class='USAGEREP_NENR' then 1 else 0 end) not_enrolling,
                    SUM(case when class='USAGEREP_PENR' then 1 else 0 end) pendingenrollment,
                    SUM(case when class='USAGEREP_TG' then 1 else 0 end) tourgiven,
                    0 noactivity,
                    0 activity
INTO #week    
FROM lcg_repcontrol r,vw_leads_meeting m
JOIN dim_time t ON CONVERT(varchar(30), t.daybegintime, 10)  = CONVERT(varchar(30),lcg_middleware.dbo.GetCSTTime(m.date_start), 10)
JOIN vw_leads_families l ON l.id = m.lead_id
WHERE	t.daybegintime <= @Enddt
AND		t.fyp  in (select distinct top 4 fyp from dim_time where  daybegintime <= @enddt order by 1 desc)
AND l.lead_valid = 'yes'
AND Len(m.created_by) <> 4
AND r.value = l.lead_status 
AND r.class like 'USAGEREP%' 
AND Len(m.school_id) = 4
GROUP BY  m.school_id, t.FYP, t.FYPW

INSERT INTO #week
SELECT 'School Leads' channel, l.created_by schoolid, t.FYP, FYPW, 
                    SUM(case when class='USAGEREP_TS' then 1 else 0 end) tourscheduled,
                    SUM(case when class='USAGEREP_TNS' then 1 else 0 end) tournoshow, 
                    SUM(case when class='USAGEREP_ENR' then 1 else 0 end) enrolled,
                    SUM(case when class='USAGEREP_NENR' then 1 else 0 end) not_enrolling,
                    SUM(case when class='USAGEREP_PENR' then 1 else 0 end) pendingenrollment,
                    SUM(case when class='USAGEREP_TG' then 1 else 0 end) tourgiven,
                    0 noactivity,
                    0 activity
FROM lcg_repcontrol r, vw_leads_families l 
JOIN dim_time t ON CONVERT(varchar(30), t.daybegintime, 10)  = CONVERT(varchar(30),lcg_middleware.dbo.GetCSTTime(l.date_entered), 10)
WHERE	t.daybegintime <= @Enddt
AND		t.fyp  in (select distinct top 4 fyp from dim_time where  daybegintime <= @enddt order by 1 desc)
AND l.lead_valid = 'yes'
AND Len(l.created_by) = 4 
AND r.class like 'USAGEREP%' 
AND r.value = l.lead_status
GROUP BY l.created_by, t.FYP, t.FYPW


UPDATE #week SET
	tourscheduled = tourscheduled+tournoshow+enrolled+not_enrolling+tourgiven+pendingenrollment
WHERE channel = 'School Leads'

---- Weekly
UPDATE #Week SET
		noactivity = tourscheduled-(tournoshow+enrolled+not_enrolling),
		activity = tournoshow+enrolled+not_enrolling
		
SELECT
 s.division
,s.district
,s.schoolid
,s.channel
,s.fyp
,s.fypw	
,s.latest_schoolinfo_modify_date Sch_modify_date
,isnull(w.tourscheduled,0) tourscheduled	
,isnull(w.tournoshow,0)	tournoshow
,isnull(w.enrolled,0)	enrolled
,isnull(w.not_enrolling,0) not_enrolling
,isnull(w.pendingenrollment,0) pendingenrollment
,isnull(w.tourgiven,0) tourgiven	
,isnull(w.noactivity,0) noactivity
,isnull(w.activity,0) activity
FROM #school s
LEFT OUTER JOIN #Week W   ON s.schoolid = w.schoolid AND s.channel = w.channel AND w.fypw = s.fypw 
 

Sorry, I can't help with this - I am fairly knowledgeable with SQL, but I haven't written stored procedures with temp tables and UPDATE.

Thanks for all the help.
Here is how I fixed it. in My SQl where I was doing multiple statments focus was gettting confused. I used Begin Trans command at the start of procedure and at the end i commited transaction.

You can do some more fancy work by checking the error after every statement and rollback. My procedure works great now.