Calling Oracle Stored Proc from SqlServer

I am trying to call an Oracle stored procedure from SqlServer. I have tried
both the MS and Oracle OLE DB Providers.
I can select records from Oracle and I can insert records to Oracle. The
stored proc call dies.
The interesting part in this is that I can write a VB script that calls the
stored procedure from the server that the SqlServer instance is running on
and it is able to execute the proc. The VB script uses Oracle's OLE DB
Provider.
We are using SqlServer 2000 and Oracle 9.2.0.6
I am sure that there is something setup incorrectly with SqlServer.
Any ideas
RSLHimm?
So i think u must use sp_OACreate, sp_OAMethod, sp_OADestroy methods in sql
server procedures or triggers.
First, make a dll for oracle actions. And call dll in sql server t-sql with
those method.
Message posted via http://www.droptable.comsql

Calling Oracle Stored Proc from SqlServer

I am trying to call an Oracle stored procedure from SqlServer. I have tried
both the MS and Oracle OLE DB Providers.
I can select records from Oracle and I can insert records to Oracle. The
stored proc call dies.
The interesting part in this is that I can write a VB script that calls the
stored procedure from the server that the SqlServer instance is running on
and it is able to execute the proc. The VB script uses Oracle's OLE DB
Provider.
We are using SqlServer 2000 and Oracle 9.2.0.6
I am sure that there is something setup incorrectly with SqlServer.
Any ideas
RSLHimm?
So i think u must use sp_OACreate, sp_OAMethod, sp_OADestroy methods in sql
server procedures or triggers.
First, make a dll for oracle actions. And call dll in sql server t-sql with
those method.
--
Message posted via http://www.sqlmonster.com

Calling Oracle Stored Proc from SqlServer

I am trying to call an Oracle stored procedure from SqlServer. I have tried
both the MS and Oracle OLE DB Providers.
I can select records from Oracle and I can insert records to Oracle. The
stored proc call dies.
The interesting part in this is that I can write a VB script that calls the
stored procedure from the server that the SqlServer instance is running on
and it is able to execute the proc. The VB script uses Oracle's OLE DB
Provider.
We are using SqlServer 2000 and Oracle 9.2.0.6
I am sure that there is something setup incorrectly with SqlServer.
Any ideas
RSL
Himm?
So i think u must use sp_OACreate, sp_OAMethod, sp_OADestroy methods in sql
server procedures or triggers.
First, make a dll for oracle actions. And call dll in sql server t-sql with
those method.
Message posted via http://www.droptable.com

Calling on a dll from sql server

I have created a massive dll for manipulating/reading records from my
company databases. In the past I have added refrences to the dll to
access dbs and then called on the sub procs...
Is there a way to use these .net dlls in sql server? Where to I add
refrences to them?
-Peter"Peter" <peter@.mclinn.com> wrote in message
news:dcde2a5a.0408170601.58a08a85@.posting.google.com...
> I have created a massive dll for manipulating/reading records from my
> company databases. In the past I have added refrences to the dll to
> access dbs and then called on the sub procs...
> Is there a way to use these .net dlls in sql server? Where to I add
> refrences to them?
>
You need to expose them to COM interop, host them in COM+ and invoke them
through the sp_oaXXX extended stored procedures.
msftngp13.phx.gbl" target="_blank">http://groups.google.com/groups?hl=...ftngp13.phx.gbl
David|||Peter/David,
unfortunately direct interoperability of SQL Server 2000 with the CLR is not
supported (http://support.microsoft.com/defaul...kb;en-us;322884).
You could alternatively create an extended stored procedure dll in C or
Delphi. If you do create a COM dll, as well as spOA.. extended stored
procedure calls, you can call it using ActiveX scripts in DTS. This is a
scripting environment so you have to use late binding, but it still is a
more familiar environmnet for developers. It has the benefit of easier error
handling and debugging. On the downside I have found it slightly slower than
using the sp_OA..procedures. If you want to use the latter method, look in
BOL for an example that uses SQLDMO
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ac
data.chm::/ac_8_qd_14_2ktw.htm).
HTH,
Paul Ibison|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eGFSluIhEHA.3520@.TK2MSFTNGP10.phx.gbl...
> Peter/David,
> unfortunately direct interoperability of SQL Server 2000 with the CLR is
not
> supported
(http://support.microsoft.com/defaul...kb;en-us;322884).
Which is why you need to host the component in COM+. When you install a COM
component as a COM+ server application, the DLL actually runs in the
dllhost.exe process. COM supplies an unmanaged proxy interface in your
local process and proxies the calls into the dllhost.process.
This gets aground the problem mentioned in the knoledge base since the
COM-callable wrapper and the .NET assembly are loaded only in the dllhost
process.
David|||David,
very interesting - it looks clever and I have never heard of such a solution
before. Does the unmanaged proxy interface support QueryInterface? What
about fibers? If both of these are supported then I agree that your solution
looks a generally viable one. However I have not seen a precedent on the MS
website advocating this method which makes me very wary (the only page I've
seen is the one I referred to). If you or anyone else has a MS reference to
this then please post it up.
Regards,
Paul Ibison

Calling on a dll from sql server

I have created a massive dll for manipulating/reading records from my
company databases. In the past I have added refrences to the dll to
access dbs and then called on the sub procs...
Is there a way to use these .net dlls in sql server? Where to I add
refrences to them?
-Peter"Peter" <peter@.mclinn.com> wrote in message
news:dcde2a5a.0408170601.58a08a85@.posting.google.com...
> I have created a massive dll for manipulating/reading records from my
> company databases. In the past I have added refrences to the dll to
> access dbs and then called on the sub procs...
> Is there a way to use these .net dlls in sql server? Where to I add
> refrences to them?
>
You need to expose them to COM interop, host them in COM+ and invoke them
through the sp_oaXXX extended stored procedures.
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=u%24dR%233nTEHA.3476%40tk2msftngp13.phx.gbl
David|||Peter/David,
unfortunately direct interoperability of SQL Server 2000 with the CLR is not
supported (http://support.microsoft.com/default.aspx?scid=kb;en-us;322884).
You could alternatively create an extended stored procedure dll in C or
Delphi. If you do create a COM dll, as well as spOA.. extended stored
procedure calls, you can call it using ActiveX scripts in DTS. This is a
scripting environment so you have to use late binding, but it still is a
more familiar environmnet for developers. It has the benefit of easier error
handling and debugging. On the downside I have found it slightly slower than
using the sp_OA..procedures. If you want to use the latter method, look in
BOL for an example that uses SQLDMO
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\ac
data.chm::/ac_8_qd_14_2ktw.htm).
HTH,
Paul Ibison|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eGFSluIhEHA.3520@.TK2MSFTNGP10.phx.gbl...
> Peter/David,
> unfortunately direct interoperability of SQL Server 2000 with the CLR is
not
> supported
(http://support.microsoft.com/default.aspx?scid=kb;en-us;322884).
Which is why you need to host the component in COM+. When you install a COM
component as a COM+ server application, the DLL actually runs in the
dllhost.exe process. COM supplies an unmanaged proxy interface in your
local process and proxies the calls into the dllhost.process.
This gets aground the problem mentioned in the knoledge base since the
COM-callable wrapper and the .NET assembly are loaded only in the dllhost
process.
David|||David,
very interesting - it looks clever and I have never heard of such a solution
before. Does the unmanaged proxy interface support QueryInterface? What
about fibers? If both of these are supported then I agree that your solution
looks a generally viable one. However I have not seen a precedent on the MS
website advocating this method which makes me very wary (the only page I've
seen is the one I referred to). If you or anyone else has a MS reference to
this then please post it up.
Regards,
Paul Ibison

Calling on a dll from sql server

I have created a massive dll for manipulating/reading records from my
company databases. In the past I have added refrences to the dll to
access dbs and then called on the sub procs...
Is there a way to use these .net dlls in sql server? Where to I add
refrences to them?
-Peter
"Peter" <peter@.mclinn.com> wrote in message
news:dcde2a5a.0408170601.58a08a85@.posting.google.c om...
> I have created a massive dll for manipulating/reading records from my
> company databases. In the past I have added refrences to the dll to
> access dbs and then called on the sub procs...
> Is there a way to use these .net dlls in sql server? Where to I add
> refrences to them?
>
You need to expose them to COM interop, host them in COM+ and invoke them
through the sp_oaXXX extended stored procedures.
http://groups.google.com/groups?hl=e...tngp13.phx.gbl
David
|||Peter/David,
unfortunately direct interoperability of SQL Server 2000 with the CLR is not
supported (http://support.microsoft.com/default...b;en-us;322884).
You could alternatively create an extended stored procedure dll in C or
Delphi. If you do create a COM dll, as well as spOA.. extended stored
procedure calls, you can call it using ActiveX scripts in DTS. This is a
scripting environment so you have to use late binding, but it still is a
more familiar environmnet for developers. It has the benefit of easier error
handling and debugging. On the downside I have found it slightly slower than
using the sp_OA..procedures. If you want to use the latter method, look in
BOL for an example that uses SQLDMO
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\ac
data.chm::/ac_8_qd_14_2ktw.htm).
HTH,
Paul Ibison
|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eGFSluIhEHA.3520@.TK2MSFTNGP10.phx.gbl...
> Peter/David,
> unfortunately direct interoperability of SQL Server 2000 with the CLR is
not
> supported
(http://support.microsoft.com/default...b;en-us;322884).
Which is why you need to host the component in COM+. When you install a COM
component as a COM+ server application, the DLL actually runs in the
dllhost.exe process. COM supplies an unmanaged proxy interface in your
local process and proxies the calls into the dllhost.process.
This gets aground the problem mentioned in the knoledge base since the
COM-callable wrapper and the .NET assembly are loaded only in the dllhost
process.
David
|||David,
very interesting - it looks clever and I have never heard of such a solution
before. Does the unmanaged proxy interface support QueryInterface? What
about fibers? If both of these are supported then I agree that your solution
looks a generally viable one. However I have not seen a precedent on the MS
website advocating this method which makes me very wary (the only page I've
seen is the one I referred to). If you or anyone else has a MS reference to
this then please post it up.
Regards,
Paul Ibison

Calling a Web Service from DTS?

I have a DTS that will cycle though records coming from a FTP file. As it
inserts some of the records into the database, it will need a call a web
service to get photo information for each record it inserts to also write as
a blob to the database.
How/Can I have a DTS call a WS to get additonal data'
JP
.NET Software DeveloperThe first hit should get you started.
http://groups.google.co.uk/groups?h...rver+oj+xmlhttp
-oj
"JP" <JP@.discussions.microsoft.com> wrote in message
news:BFB437D7-D5FB-47B3-AEDD-0272C224B34B@.microsoft.com...
>I have a DTS that will cycle though records coming from a FTP file. As it
> inserts some of the records into the database, it will need a call a web
> service to get photo information for each record it inserts to also write
> as
> a blob to the database.
> How/Can I have a DTS call a WS to get additonal data'
> --
> JP
> .NET Software Developer|||Now you can all a Webservices from SQL (DTS, triggers, SQL Analizar,
Function, Procedure)
Call a WebService from SQL 2000
http://www.rdlcomponents.com/EXSP/default.aspx
Thanks
Jerry
"oj" wrote:

> The first hit should get you started.
> http://groups.google.co.uk/groups?h...rver+oj+xmlhttp
> --
> -oj
>
> "JP" <JP@.discussions.microsoft.com> wrote in message
> news:BFB437D7-D5FB-47B3-AEDD-0272C224B34B@.microsoft.com...
>
>

Calling a stored procedure that inserts records and generates an output parameter

I will be calling a stored procedure in SQL Server from SSIS. The stored procedure inserts records in a table by accepting input parameters. In the process, it also generates an output parameter that it passes as part of the parameters defined inside the stored procedure. The output parameter value acts as the primary key value for the record inserted using the stored procedure.

How can I call this stored procedure in SSIS? This is just one of the n steps as I will be extracting the output parameter generated by this stored procedure for the succeeding steps.

The Execute SQL Task sounds best suited to this scenario: http://www.sqlis.com/default.aspx?58

-Jamie

|||I've already tried that but I need to pass multiple records in the Execute SQL task, not just one.|||

So you're going to be executing the SP for every record in the pipeline? If that is the case then the OLE DB Command transform will be of help but I would recommend you find an alternate means of doing this. Executing a seperate query for each row is not considered great practice.

-Jamie

|||How do you use the OLEDB Command Transform? Actually, I just created a simple app to do the trick. The thing is, I cannot do anything about it. I just need to migrate existing records into a new database. I need to use the existing SP to insert the records from an Oracle database into the new SQL Server database because they are of different schemas and design.

Calling a Stored Procedure on Multiple Records

I have a stored procedure on an SQL Server database which displays statistical data for a single record. The example below selects a user ID as a parameter and displays the user's name and the total amount of transactions he has made:
CREATE PROCEDURE dbo.GetUserStats @.UserID BIGINT AS
DECLARE @.TempTable TABLE
(
UserID BIGINT,
UserName VARCHAR(60),
TotalAmt FLOAT
)

INSERT INTO @.TempTable (UserID, UserName, TotalAmt)
SELECT u.RecID, u.LastName + ', ' + u.FirstName,
(SELECT SUM(t.Amount) FROM Transactions t WHERE t.UserID = @.UserID)
FROM Users u
WHERE u.RecID = @.UserID

SELECT * FROM @.TempTable
GO

So if I execute this amount entering a single ID, it returns a single row for that user:

UserID UserName TotalAmt
--------
1 Doe, John 100.00

What I would like to do is create another stored procedure which calls this one for every user returned in a query, thus returning the same data for several users:

UserID UserName TotalAmt
--------
1 Doe, John 100.00
2 Smith, Bob 123.45
3 Blow, Joe 150.55

Is there a way to re-use a stored procedure within another, based on the results of a query?Some thoughts:

1. This sounds more like a function than a stored procedure. Would make the execution concept much easier (ie, SELECT dbo.MyFunction(MyUserID) FROM MyTable).

2. You could wrap this with another stored procedure; you could pass in the user list as a parameter (there was an excellent article recently over at SQLServerCentral.com about using an xml string as a parameter).

But I really think that what you want is a function.

Regards,

hmscott|||Thanks for your feedback.

I'm not familiar with functions. Would I want to convert GetUserStats to a function?|||may, maybe not. you do know you can do that whole thing without the temp table.

BTW, bigint for userid? do you know there are not that many people in the world?|||I'm not familiar with functions. Would I want to convert GetUserStats to a function?

I wouldn't "convert" per se; at least not the way that you have written it.
Try this:

Open Query Analyzer
Click on File | New
Double click on Create Function
Double click on Create Scalar Function

Read in SQL BOL about Scalar functions.

I think you will get an idea of what a function is and how to go about writing your own. From there I think you will find it easier to understand how to proceed.

Regards,

hmscott|||may, maybe not. you do know you can do that whole thing without the temp table.

BTW, bigint for userid? do you know there are not that many people in the world?
The example I posted is very simplified. The actual query I'm trying to pull off is pretty complicated and pulls data from several tables, that's why I included the temp table. Can I use a temp table in a function?

Yeah, I guess a BIGINT is more than I need for a user ID. I had the misconception of thinking INTs only go to 32,000 when I set up this database (as they do in many programming languages).|||I have tried building a few functions. I can get them to run using a direct statemtent such as:

SELECT * FROM MyFunction(1)

But If I try to use the line above in a stored procedure or another function, I get an error:

Invalid object name 'MyFunction'.|||They're fiesty. You need the user name as a prefix, as in:SELECT *
FROM dbo.MyFunction(1)-PatP

call database Maintenance plan from Sql Server Agent->Job

Hi There
can i call database Maintenance plan from Sql Server Agent->Job
what i'm trying to do
I've to truncated records on log table,
then shrink all databases
the call maintenance plan to take all database backup
Thanks
GaneshGanesh
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Ganesh" <gsganesh@.yahoo.com> wrote in message
news:A9B19A8E-AE37-4B2B-A0C9-B800E6119472@.microsoft.com...
> Hi There
> can i call database Maintenance plan from Sql Server Agent->Job
> what i'm trying to do
> I've to truncated records on log table,
> then shrink all databases
> the call maintenance plan to take all database backup
> --
> Thanks
> Ganesh|||Probably the best advice the OP should follow, but probably not what he
wanted to hear. :)
ML
http://milambda.blogspot.com/|||Check out this. Let me know if this is what you were looking for.
http://www.dbazine.com/sql/sql-articles/cook10

Calculations among Subreports

I have three subreports in detail section of main report.The first two subreports have detail records.In third subreport i want to diplay result of sum of values on two of the subreports record wise as below :

SUBREPORT 1 SUBREPORT2 SUBREPORT3



55 65 120

100 35

61 45

Problem is only result for first record on subreports is being displayed whereas i want for all.I am doing all this by using shared variables on subreports.I 've also used whileprintingrecords statement before defining shared variables.Is there any way to solve this problem.Can you explain a bit in detail?

Calculating time difference between two records....

I have a data set like so:

UTC_TIME Timestamp NodeID Message Flag
Line
Station
11/19/2005 10:45:07 1132397107.91 1 3 5 1028
1034
11/3/2005 21:05:35 1131051935.20 2 3 5 1009
1043
11/25/2005 21:12:16 1132953136.59 3 3 5 1037
1049

I added the UTC_TIME column in as aconversion of the unix timestamp in

the TIMESTAMP column.

Keeping things simple and straightforward, I need to be able to
calculate the difference from one record to the next (ordered by
TIMESTAMP or UTC_TIME) and output the result into another column in the

table.

NODEID is the unique id.

First, what is the function to do so if, say, I only wanted to
calculate the difference between 2 records as just a basic SELECT
statement. That way I can answer quick question based on any one or two

NODEID's.

Second, how would I further that to continually calculate (as stated
above)?

WOuld this be a stored procedure? A trigger? A cursor?

I am learning as I go here. Any help is greatly appreciated.

R.DATEDIFF ( datepart , startdate , enddate ) is the function that will
return the interval between two dates.
Check the help in QA for the datepart arguments.

As far to tell the difference between two rows: this isnt perfect.
I wrote this using the NORTHWIND database, but it I think it is exacly
what you are looking for:
SELECT
ORDERID,
ORDERDATE,
DATEDIFF(d,orderdate, (SELECT TOP 1 ORDERDATE FROM ORDERS WHERE
ORDERID > O.ORDERID ORDER BY ORDERID )) AS DAYS
FROM ORDERS O|||Your narative is vague and does not compile; why did you not post DDL
that everyone could use for testing?

The usual way to model events is to have a (start_time, end_time) pair
in the table because time is a continuum. A NULL end_time means the
event is on-going. The druation of the event is trival at that point.

A row is not a record -- a row has to be a complete fact, while a
record does not.|||actually, the way R. set it up, he is using facts in his rows. At x
time, an event occurred. I think you are ASSUMING start and end times,
and attempting to get him to change his data model to become
non-normalized.

Shame on you.

To answer more of the person's question, you can use the datediff
function against teh current time to find out how long ago an event
occured from whenever you run the report.

doug|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

You don't say what time part (days, months, years, hours, minutes,
seconds) you want returned. My example will show hours.

SELECT NodeID, LineStation, MessageFlag, UTC_Time,
DateDiff(Hour, (SELECT MAX(UTC_Time) FROM table_name
WHERE NodeID=T.NodeID AND LineStation=T.LineStation
AND UTC_Time < T.UTC_Time),
UTC_Time) As HoursInterval

FROM table_name As T
WHERE ... < your criteria > ...
ORDER BY NoteID, LineStation, UTC_Time

The "table_name" in both the main query & the subquery should be the
same.

Your data implies that the LineStation is receiving a message
(MessageFlag) at specified times (UTC_TIME). Therefore, I set up the
query to return info on LineStations on the same NodeID. If you just
want to track messages on the NodeID, no matter the LineStation, then
remove the "AND LineStation=T.LineStation" part of the subquery's
criteria (WHERE clause), and remove the LineStation from the ORDER BY
clause.

See the SQL Server BooksOnLine for more info on DateDiff() function.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ7HpM4echKqOuFEgEQJWFgCdEMfPaY7aQPYOL66ZJryJpR 4fTMMAoO0r
WEDMaPHxQiZ352eHx0ER72Ur
=YKsf
--END PGP SIGNATURE--

iamonthisboat@.gmail.com wrote:
>
>
> I have a data set like so:
>
> UTC_TIME Timestamp NodeID Message Flag
> Line
> Station
> 11/19/2005 10:45:07 1132397107.91 1 3 5 1028
> 1034
> 11/3/2005 21:05:35 1131051935.20 2 3 5 1009
> 1043
> 11/25/2005 21:12:16 1132953136.59 3 3 5 1037
> 1049
>
> I added the UTC_TIME column in as aconversion of the unix timestamp in
> the TIMESTAMP column.
>
> Keeping things simple and straightforward, I need to be able to
> calculate the difference from one record to the next (ordered by
> TIMESTAMP or UTC_TIME) and output the result into another column in the
> table.
>
> NODEID is the unique id.
>
> First, what is the function to do so if, say, I only wanted to
> calculate the difference between 2 records as just a basic SELECT
> statement. That way I can answer quick question based on any one or two
> NODEID's.
>
> Second, how would I further that to continually calculate (as stated
> above)?
>
> WOuld this be a stored procedure? A trigger? A cursor?
>
> I am learning as I go here. Any help is greatly appreciated.
>
> R.

Calculating textboxes values grouped in diferent Lists

I have to Lists filtered by the same field. One List shows all records with the field IsClokedIn value = TRUE and the other list =FALSE. I have only one dataset that retrieve the ClockIn and Clock out for one user. In a perfect world, the user MUST clock in and always clock out. So, when i created my dataset i ordered by UserClockingID (whick is primary key). So, this MUST retrieve a dataset with alternate records IN / OUT (TRUE or FALSE)

Ex.

UserClokingID UserID Time IsClockedIN

2323 34 8:32:03 TRUE

2324 34 12:07:02 FALSE

2325 34 13:05:03 TRUE

2326 34 14:53:02 FALSE

2327 34 15:10:03 TRUE

2328 34 17:32:02 FALSE

All i need is to calculate (sum then divide by 60) the minutes returned by the method DateDiff between the first row in my TRUE filtered list and the first row in my FALSE filetered list... and so on for the 2nd, 3rd ..... I can get the minutes by taking the values directly from the Fields!IsClockedIn.Value and using a condition to "grab" the previous record if the current record ClockedIn.Value=FALSE. I already did that.

my problem is that when i try to perform a calculation of textboxes values(those with the ammount of minutes for every pair of records from the datalist) out of the group. It says you can only do that inside the group or dataregion.

Is there any work around?

I really need to get this solved.

Have you tried to sum the expression you are using to populate the textbox?|||

I couldn't. When you click on Preview automatically you get this error that you can't perform this out of the DataRegion that textbox belongs to. This is driving me crazy and I honestly think if this is not possible to do, then Reporting Services is missing something big here 'cause i'm sure i'm not the one trying to calculate fields in a List out of the list itself.

Calculating Percent

Hey Everyone,
I have a report displaying records, the report also shows when they were opened and when they were closed. I grouped them by month and I would like to calculate the percent of records closed within 60 days by month and create a chart of it. Can anyone help?Nevermind, I figured it out.|||Arrgggh.
I calculated percent by using running sums, NumTotalRecords and NumRecordsIn60. The Percent is then calculated by dividing the two running totals, but now I can't chart by that can anyone help???

Calculating average from a number of records

I have a table in my database that records project details.
Two fields that I have are DateOpened & DateClosed.

I need to be able to calculate the average of the total time in days projects have been opened
.
This will give me the days opened for one project (DateClosed-DateOpened)
I am not able to calculate this for a number of projects.

Also, if the date closed field is blank then this project should not be calculatedYou must have first the total of days & of course the total of proyects, to count the total of proyects that I suppouse that you have in a group just type this formula:

NumberVar Count;
If Not OnFirstRecord Then
If previous (id_proyect_field) = id_proyect_field Then
Count:= Count + 1
Else
Count:= 1
Else
Count:= 1

Lay this formula in detail section and type the next formula to put it in the group footer

NumberVar Count;
WhilePrintingRecords;
Count:= Count;
Count

Those tow easy formulas give you the number of proyects, on the other side, take the diference between your openday and closeday fields and sum them.

To finish, do it: TotalOfDays / Count

This last action is in the report footer.

I hope this help you

Calculated/derived measures

Hello again.

I'm wondering if it's possible to add some form of calculated field to my fact table.

Scenario... I have a fact table that records customer account balances a the end of each day. As this is a bank we're talking about, some of the balances are in credit & some overdrawn.

Since the AccountBalance field aggregates by default to sum, if for example I wish to view balances by product I get the NET balances (i.e. all debit & credit balances summed). This is OK, however obviously my people will want to be able to split these in to

Credit Balances

Product

a 100

b 100

c 100

Debit Balances

Product

a -50

b -50

c -50

as opposed to the current situation where I get

Balances

Product

a 50

b 50

c 50

Hope this is clear... question is... how do I go about it ? Calculated field, derived field... I tried a simple IIF calculation but the trouble is it went something like

IIF([measures].[FactAccountBals] >=0, [measures].[FactAccountBals] ,0)

to give me credit balances, however (as you can imagine) it returns ZERO for all debit balance accounts, whereas I want to ignore them... but how ?

You can make another column in your fact table that is either "Credit" or "Debit" and then make an attribute out of that.

Then you can add that attribute to your mdx statement and have items broken out by transaction type.

|||Sorry, stupid question, but how do I make an attribute out of a fact table field record ?|||

OK for anyone else here's what I ended up doing.

Created a New Dimension called Dim Baltype with just 2 records, Debit Bal & Credit Bal

Then ran an update query on the fact table where I added and extra field to signify Dr or Cr balance & linked that to Dim Baltype - This now enables me to split by Loans and deposits in the cube.

Thanks for the steer