Please help me

I'm in a trouble with crystal report 8.5. My printer only uses for A4 sheet. But I want to make a report with A3 scale. So please show me the way to compose in two A4 sheets instead of A3.
Thank you very much!When designing the report you need to design according to that formatsql

PL/SQL RPC call from Sql Server linked server (OraOLEDB)

I'ma having trouble calling a remote pl/sql proc from SQL server 2000 using
a
linked server provider=OraOLEDB.Oracle
Here's the call:
EXEC PS..TESTDTA.esp_F0101Z2_EDSP_set
'C',
'MCHAMBERS',
'23577',
'107399',
2000.0
This is the proc, straight forward...
CREATE PROCEDURE TESTDTA.esp_F0101Z2_EDSP_set (
flag in varchar2,
p_szedus in nchar,
p_szedbt in nchar,
p_szedtn nchar,
p_szedln number
)
AS
BEGIN
UPDATE TESTDTA.F0101Z2 SET
SZEDSP = flag
WHERE
szedus = p_szedus AND
szedbt = p_szedbt AND
szedtn = p_szedtn AND
szedln = p_szedln;
END;
Linked Server Properties:
Collation Compatible = true
Data Access = true
RPC = true
RPC Out = true
Use Remote Collation = true
Provider Properties:
AllowInProcess = 1
DisallowAdhocAccess = 0
DynamicParameters = 1
IndexAsAccessPath = 0
LevelZeroOnly = 0
NestedQueries = 0
NonTransactedUpdates = 0"Alien2_51" <dan.billow.remove@.monacocoach.removeme.com> wrote in message
news:A0EB9CA3-F18A-4777-8F5B-46CE8F1856B4@.microsoft.com...
> I'ma having trouble calling a remote pl/sql proc from SQL server 2000
> using a
> linked server provider=OraOLEDB.Oracle
> Here's the call:
> EXEC PS..TESTDTA.esp_F0101Z2_EDSP_set
> 'C',
> 'MCHAMBERS',
> '23577',
> '107399',
> 2000.0
>
> This is the proc, straight forward...
> CREATE PROCEDURE TESTDTA.esp_F0101Z2_EDSP_set (
> flag in varchar2,
> p_szedus in nchar,
> p_szedbt in nchar,
> p_szedtn nchar,
> p_szedln number
> )
> AS
> BEGIN
> UPDATE TESTDTA.F0101Z2 SET
> SZEDSP = flag
> WHERE
> szedus = p_szedus AND
> szedbt = p_szedbt AND
> szedtn = p_szedtn AND
> szedln = p_szedln;
> END;
>
In SQL 2000 you have to use OPENQUERY and jump through hoops:
http://groups.google.com/group/micr.../>
ce8a?hl=en&
SQL 2005 makes this much easier a couple of enhancements to the EXEC
statement, you can specify a linked server to run remotely, and you can pass
parameters in and out of a dynamic SQL query.
--Oracle SQL---
CREATE OR REPLACE PROCEDURE scott.esp_F0101Z2_EDSP_set (
flag in varchar2,
p_szedus in nchar,
p_szedbt in nchar,
p_szedtn nchar,
p_szedln number,
p_result out varchar2
)
AS
BEGIN
p_result := 'it worked';
END;
--end Oracle SQL---
EXEC sp_addlinkedserver @.server = 'ORACLE',
@.srvproduct='Oracle',
@.provider='OraOLEDB.Oracle',
@.datasrc='//localhost/xe'
EXEC sp_serveroption @.server='ORACLE', @.optname='rpc out', @.optvalue='true'
EXEC sp_addlinkedsrvlogin 'ORACLE', 'false', NULL, 'Scott', 'tiger'
GO
EXEC (
'
BEGIN
SCOTT.esp_F0101Z2_EDSP_set(
:p_flag,
:p_szedus,
:p_szedbt,
:p_szedtn,
:p_szedln,
:p_result);
END;
',
'C',
'MCHAMBERS',
'23577',
'107399',
2000.0,
@.result output
) at ORACLE
print @.result

Pivot Select

Hi,
I'm able to get Pivot to work but I'm having trouble limiting the record set. I want it to select only records from this FiscalYear. I have a table with a field called CurrentBudgetYear that I use as a control. So FiscalYear should equal CurrentBudgetYear but my results include all FiscalYears.

SELECT ProjNo, TaskCode, [1] AS P1, [2] AS P2, [3] AS P3, [4] AS P4, [5] AS P5, AS P6, [7] AS P7, AS P8, [9] AS P9, [10] AS P10, [11] AS P11, [12] AS P12
FROM
(SELECT e.ProjNo, e.TaskCode, e.FiscalPeriod, e.ActualAmt
FROM tblActualExpend AS e
INNER JOIN tblBudgetConfig ON e.FiscalYear = tblBudgetConfig.CurrentBudgetYear
)p
PIVOT
(
SUM(ActualAmt)
FOR FiscalPeriod IN
( [1], [2], [3], [4], [5], , [7], , [9], [10], [11], [12])
)AS pvt
ORDER BY ProjNo, TaskCode;

Thanks in advanced for any help.

It appears that in the derived table, you need a WHERE clause to constrain the data to a specific FiscalYear.

Something like:

WHERE e.FiscalYear >= '20060101' and e.FiscalYear < '20070101'

Or whatever dates demarc your Fiscal Year.

|||Thanks Arnie, I can see where that would work. I really want something more automated. Thats why I use the control table. This way all I have to do is change the value in one field in one table and all my queries are current.|||

As you noticed, If your 'control table' has rows all of your Fiscal Years, you retreive all Fiscal Years. The only way it would work without a WHERE clause is if the 'control table' has only one row of data for the current FiscalYear.

If you want only one fiscal Year, then you will have to specify which one. The query processor can't read your mind.

You could still 'automate' the process, for example, assuming your FiscalYear begins July 1:

WHERE e.FiscalYear >= cast( cast( ( year( getdate() ) - 1) AS char(4)) + '0701' AS datetime )
AND e.FiscalYear < cast( cast( ( year( getdate() )) AS char(4)) + '0701' AS datetime )

Will always derive the Fiscal year for the current date.

|||

Sorry to have bothered you. The pivot works exactly as I want. I was running it on test data that I took at the end of the last fiscal year so I was getting all of the fiscal periods. Becuase of that I was sure it was not working. I just updated my tables and I'm now getting the results I was expecting.

As an FYI, the 'control' table only has one record in it....the FiscalYear. For reporting purposes we can't cut over to the new fiscal year at the start of the year (atleast in the database) so we use this table to control all of that.

Cheers!

|||Not a problem!