Can not created Named Calculation...

I can not seem to create a Named Calculation on a table that I have already created a Named Query on in my data source view. Any idea why this is and if there is a work around for it?

TIA

Wellman

If you have already a Named Query you can add an expression-based column to the SELECT statement using the syntax of the underlying data source.|||

Thank you for this information. Any idea why this is so? Doesn't make much sense to me.

Also, can I reference other tables as well?

TIA

Wellman

|||Which part doesn't make sense? You can view a named query as a SQL view. Just like a SQL view, your SELECT statement can include any valid syntax, including other tables, calling UDF, etc.

Can not create my Publisher

Hi,
I install Sql server 2000 on a server named JMI_SQL
After Installation we decide to rename the server JMI_SQL for SQL2007
I create a Distributor on the server SQLDIST2007
Once my distibutor created i try to create my Publisher on SQL2007 and I
receive this message
Error 18482 Could not connec to server SqlDIST2007 because JMI_SQL is not
defined as a remote Server
In the Wizard when I was creating my Publisher I select SQl2007 as Publisher
and the wizard is looking for JMI_SQL which the old name of the server
I try those commands in sql query analyser to get some information
select @.@.servername Show JMI_SQL
SELECT srvname, srvid
FROM sysservers
Show this
ADAMM_DB1
BESERVER_DB2
CATALOG_DB3
JMI_SQL0
repl_distributor 4
In the tables master.sysservers I try to Change manually all the JMI_SQL for
SQL2007 Sql server dont let me change it
Is there a way that I can change the JMI_SQL for SQL2007 in the sysservers
table
I would like to avoid to uninstall and reinstall Sql Server 2000
Before trying to do something I would like to have an advise from
experimented person on how to do that.
Thanks in advance and have a nice day!
Hi Gav,
Thanks a lot for the information
I appreciate very much !
Have a nice day
"Gav" wrote:

> Using Query Analyzer, execute the following:
> Use master
> go
> sp_dropserver 'JMI_SQL', droplogins
> go
> sp_addserver 'SQL2007', LOCAL
> go
> After you have done this make sure you restart the SQL service or restart
> your computer (whichever you fancy!) and that should do it
> Regards
> Gav
> "GC" <GC@.discussions.microsoft.com> wrote in message
> news:65162A3C-16DB-43C1-884D-601056BEFAF8@.microsoft.com...
>
>
sql

can not access clustering SQL Server after relocation

Hi all:

I have a clustering SQL Server on Node1 and Node2, the Node1 has named
Instance1 and Node2 has named Instance2, no default instance. We
tested it that everthing is OK, then we decide to move to DR location.

The relocation kept the same virtual and phusical server name, and we
did not change SQL Server server network utility. But server IP
addresses were changed(vitual and physical). We can start the
clustering SQL Server as before, and everything looks like well.

However the users can not access it remotely, and they did not change
the client network utility. But if I login the server,I can access it
from Query Analyzer even though ther is not client network utility
setup on the server. I can ping the vitual server name, how can I make
sure the ports are working well? (We did not use the default port for
named instance).

I remember I did input the virtual server IP address when I installed
the clustering SQL Server. Of course, it was change after relocation.
Is it the reason for failed remote login? If it is, how to fix it?

Does anyboday know there is document for clustering SQL Server
relocation?

Thanks
Williew2jin@.hotmail.com (willie) wrote in message news:<6610106b.0408251200.14624760@.posting.google.com>...
> Hi all:
> I have a clustering SQL Server on Node1 and Node2, the Node1 has named
> Instance1 and Node2 has named Instance2, no default instance. We
> tested it that everthing is OK, then we decide to move to DR location.
> The relocation kept the same virtual and phusical server name, and we
> did not change SQL Server server network utility. But server IP
> addresses were changed(vitual and physical). We can start the
> clustering SQL Server as before, and everything looks like well.
> However the users can not access it remotely, and they did not change
> the client network utility. But if I login the server,I can access it
> from Query Analyzer even though ther is not client network utility
> setup on the server. I can ping the vitual server name, how can I make
> sure the ports are working well? (We did not use the default port for
> named instance).
> I remember I did input the virtual server IP address when I installed
> the clustering SQL Server. Of course, it was change after relocation.
> Is it the reason for failed remote login? If it is, how to fix it?
> Does anyboday know there is document for clustering SQL Server
> relocation?
> Thanks
> Willie

This article might help:

http://support.microsoft.com/defaul...8&Product=sql2k

Simon

can not access clustering SQL Server after relocation

Hi all:
I have a clustering SQL Server on Node1 and Node2, the Node1 has named
Instance1 and Node2 has named Instance2, no default instance. We
tested it that everthing is OK, then we decide to move to DR location.
The relocation kept the same virtual and phusical server name, and we
did not change SQL Server server network utility. But server IP
addresses were changed(vitual and physical). We can start the
clustering SQL Server as before, and everything looks like well.
However the users can not access it remotely, and they did not change
the client network utility. But if I login the server,I can access it
from Query Analyzer even though ther is not client network utility
setup on the server. I can ping the vitual server name, how can I make
sure the ports are working well? (We did not use the default port for
named instance).
I remember I did input the virtual server IP address when I installed
the clustering SQL Server. Of course, it was change after relocation.
Is it the reason for failed remote login? If it is, how to fix it?
Does anyboday know there is document for clustering SQL Server
relocation?
Thanks
Williew2jin@.hotmail.com (willie) wrote in message news:<6610106b.0408251200.14624760@.posting.googl
e.com>...
> Hi all:
> I have a clustering SQL Server on Node1 and Node2, the Node1 has named
> Instance1 and Node2 has named Instance2, no default instance. We
> tested it that everthing is OK, then we decide to move to DR location.
> The relocation kept the same virtual and phusical server name, and we
> did not change SQL Server server network utility. But server IP
> addresses were changed(vitual and physical). We can start the
> clustering SQL Server as before, and everything looks like well.
> However the users can not access it remotely, and they did not change
> the client network utility. But if I login the server,I can access it
> from Query Analyzer even though ther is not client network utility
> setup on the server. I can ping the vitual server name, how can I make
> sure the ports are working well? (We did not use the default port for
> named instance).
> I remember I did input the virtual server IP address when I installed
> the clustering SQL Server. Of course, it was change after relocation.
> Is it the reason for failed remote login? If it is, how to fix it?
> Does anyboday know there is document for clustering SQL Server
> relocation?
> Thanks
> Willie
This article might help:
http://support.microsoft.com/defaul...8&Product=sql2k
Simon

can not access clustering SQL Server after relocation

Hi all:
I have a clustering SQL Server on Node1 and Node2, the Node1 has named
Instance1 and Node2 has named Instance2, no default instance. We
tested it that everthing is OK, then we decide to move to DR location.
The relocation kept the same virtual and phusical server name, and we
did not change SQL Server server network utility. But server IP
addresses were changed(vitual and physical). We can start the
clustering SQL Server as before, and everything looks like well.
However the users can not access it remotely, and they did not change
the client network utility. But if I login the server,I can access it
from Query Analyzer even though ther is not client network utility
setup on the server. I can ping the vitual server name, how can I make
sure the ports are working well? (We did not use the default port for
named instance).
I remember I did input the virtual server IP address when I installed
the clustering SQL Server. Of course, it was change after relocation.
Is it the reason for failed remote login? If it is, how to fix it?
Does anyboday know there is document for clustering SQL Server
relocation?
Thanks
Williew2jin@.hotmail.com (willie) wrote in message news:<6610106b.0408251200.14624760@.posting.google.com>...
> Hi all:
> I have a clustering SQL Server on Node1 and Node2, the Node1 has named
> Instance1 and Node2 has named Instance2, no default instance. We
> tested it that everthing is OK, then we decide to move to DR location.
> The relocation kept the same virtual and phusical server name, and we
> did not change SQL Server server network utility. But server IP
> addresses were changed(vitual and physical). We can start the
> clustering SQL Server as before, and everything looks like well.
> However the users can not access it remotely, and they did not change
> the client network utility. But if I login the server,I can access it
> from Query Analyzer even though ther is not client network utility
> setup on the server. I can ping the vitual server name, how can I make
> sure the ports are working well? (We did not use the default port for
> named instance).
> I remember I did input the virtual server IP address when I installed
> the clustering SQL Server. Of course, it was change after relocation.
> Is it the reason for failed remote login? If it is, how to fix it?
> Does anyboday know there is document for clustering SQL Server
> relocation?
> Thanks
> Willie
This article might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;319578&Product=sql2k
Simonsql

can not access clustering SQL Server after relocation

Hi all:
I have a clustering SQL Server on Node1 and Node2, the Node1 has named
Instance1 and Node2 has named Instance2, no default instance. We
tested it that everthing is OK, then we decide to move to DR location.
The relocation kept the same virtual and phusical server name, and we
did not change SQL Server server network utility. But server IP
addresses were changed(vitual and physical). We can start the
clustering SQL Server as before, and everything looks like well.
However the users can not access it remotely, and they did not change
the client network utility. But if I login the server,I can access it
from Query Analyzer even though ther is not client network utility
setup on the server. I can ping the vitual server name, how can I make
sure the ports are working well? (We did not use the default port for
named instance).
I remember I did input the virtual server IP address when I installed
the clustering SQL Server. Of course, it was change after relocation.
Is it the reason for failed remote login? If it is, how to fix it?
Does anyboday know there is document for clustering SQL Server
relocation?
Thanks
Willie
w2jin@.hotmail.com (willie) wrote in message news:<6610106b.0408251200.14624760@.posting.google. com>...
> Hi all:
> I have a clustering SQL Server on Node1 and Node2, the Node1 has named
> Instance1 and Node2 has named Instance2, no default instance. We
> tested it that everthing is OK, then we decide to move to DR location.
> The relocation kept the same virtual and phusical server name, and we
> did not change SQL Server server network utility. But server IP
> addresses were changed(vitual and physical). We can start the
> clustering SQL Server as before, and everything looks like well.
> However the users can not access it remotely, and they did not change
> the client network utility. But if I login the server,I can access it
> from Query Analyzer even though ther is not client network utility
> setup on the server. I can ping the vitual server name, how can I make
> sure the ports are working well? (We did not use the default port for
> named instance).
> I remember I did input the virtual server IP address when I installed
> the clustering SQL Server. Of course, it was change after relocation.
> Is it the reason for failed remote login? If it is, how to fix it?
> Does anyboday know there is document for clustering SQL Server
> relocation?
> Thanks
> Willie
This article might help:
http://support.microsoft.com/default...&Product=sql2k
Simon

can not access clustering SQL Server after relocation

Hi all:
I have a clustering SQL Server on Node1 and Node2, the Node1 has named
Instance1 and Node2 has named Instance2, no default instance. We
tested it that everthing is OK, then we decide to move to DR location.
The relocation kept the same virtual and phusical server name, and we
did not change SQL Server server network utility. But server IP
addresses were changed(vitual and physical). We can start the
clustering SQL Server as before, and everything looks like well.
However the users can not access it remotely, and they did not change
the client network utility. But if I login the server,I can access it
from Query Analyzer even though ther is not client network utility
setup on the server. I can ping the vitual server name, how can I make
sure the ports are working well? (We did not use the default port for
named instance).
I remember I did input the virtual server IP address when I installed
the clustering SQL Server. Of course, it was change after relocation.
Is it the reason for failed remote login? If it is, how to fix it?
Does anyboday know there is document for clustering SQL Server
relocation?
Thanks
Willie
w2jin@.hotmail.com (willie) wrote in message news:<6610106b.0408251200.14624760@.posting.google. com>...
> Hi all:
> I have a clustering SQL Server on Node1 and Node2, the Node1 has named
> Instance1 and Node2 has named Instance2, no default instance. We
> tested it that everthing is OK, then we decide to move to DR location.
> The relocation kept the same virtual and phusical server name, and we
> did not change SQL Server server network utility. But server IP
> addresses were changed(vitual and physical). We can start the
> clustering SQL Server as before, and everything looks like well.
> However the users can not access it remotely, and they did not change
> the client network utility. But if I login the server,I can access it
> from Query Analyzer even though ther is not client network utility
> setup on the server. I can ping the vitual server name, how can I make
> sure the ports are working well? (We did not use the default port for
> named instance).
> I remember I did input the virtual server IP address when I installed
> the clustering SQL Server. Of course, it was change after relocation.
> Is it the reason for failed remote login? If it is, how to fix it?
> Does anyboday know there is document for clustering SQL Server
> relocation?
> Thanks
> Willie
This article might help:
http://support.microsoft.com/default...&Product=sql2k
Simon

Can named subscribers be added as a group

When using named subscriptions, can a group be added to the list of
replication partners, or does each subscrber need to be added individually?
It must be done individually.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Tony Toker" <xxxx@.xxxx.com> wrote in message
news:cellc1$q4i$1$8302bc10@.news.demon.co.uk...
> When using named subscriptions, can a group be added to the list of
> replication partners, or does each subscrber need to be added
individually?
>
>
|||Tony,
each one has to be added separately, but if you have a list, you could use
SQL DMO to add them in a loop. I have added a couple of scripts to create
publications and subscribers using VB.Net and SQLDMO on
www.replicationanswers.com.
HTH,
Paul Ibison

Can I use dynamic SQL in a stored procedure to call another stored

Hi,
I am having one stored procedure named SP1 which is having one input and one
output parameter as follows
CREATE PROCEDURE dbo.SP1
@.xmldoc TEXT
,@.FLAG_EXEC VARCHAR(5)
AS
BEGIN
...
END
I have write another stored procedure named SP2 which is creating a dynamic
SQL statement like
CREATE PROCEDURE dbo.SP2
AS
BEGIN
DECLARE @.CURR_PROC_NAME VARCHAR(125)
DECLARE @.STR_XML_AS_VARCHAR VARCHAR(125)
DECLARE @.STR_RET_QRY_MN VARCHAR(125)
DECLARE @.STR_EXECUTE_QRY VARCHAR(4000)
SET @.STR_EXECUTE_QRY = 'exec ' + @.CURR_PROC_NAME + ''''+
@.STR_XML_AS_VARCHAR + ''' , @.STR_RET_QRY_MN OUTPUT ,@.FLAG_EXEC = ''FALSE'' '
print @.STR_EXECUTE_QRY
EXEC (@.STR_EXECUTE_QRY)
END
but it always says to declare @.STR_RETURN_STRING variable. Can I do like
above? If yes then how? If no then why?
With thanx in advance,Hi Rajendra,
this does not work because each EXEC is running in it's own process.
That means in fact that proc2 does not know anything about the variables
you have declared in proc1.
You should handle it as follows:
Proc2 (Sub-Proc) writes the ReturnValue into a table
Proc2 (Calling Proc) reads after the run of Proc2 the value from the table
Another way is to work with "sp_executesql". See BOL for details...
HTH ;-)
Gru, Uwe Ricken
MCP for SQL Server 2000 Database Implementation
GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
________________________________________
____________
dbdev: http://www.dbdev.org
APP: http://www.AccessProfiPool.de
FAQ: http://www.donkarl.com/AccessFAQ.htm|||Rajendra (Rajendra@.discussions.microsoft.com) writes:
> I have write another stored procedure named SP2 which is creating a
> dynamic SQL statement like
> CREATE PROCEDURE dbo.SP2
> AS
> BEGIN
> DECLARE @.CURR_PROC_NAME VARCHAR(125)
> DECLARE @.STR_XML_AS_VARCHAR VARCHAR(125)
> DECLARE @.STR_RET_QRY_MN VARCHAR(125)
> DECLARE @.STR_EXECUTE_QRY VARCHAR(4000)
> SET @.STR_EXECUTE_QRY = 'exec ' + @.CURR_PROC_NAME + ''''+
> @.STR_XML_AS_VARCHAR + ''' , @.STR_RET_QRY_MN OUTPUT ,@.FLAG_EXEC = ''FALSE''
'
> print @.STR_EXECUTE_QRY
> EXEC (@.STR_EXECUTE_QRY)
> END
> but it always says to declare @.STR_RETURN_STRING variable. Can I do like
> above? If yes then how? If no then why?
You are making it too complicated. Just say:
EXEC @.CURR_PROC_NAME @.STR_XML_AS_VARCHAR, STR_RET_QRY_MN OUTPUT,
@.FLAG_EXEC = 'FALSE'
This will execute the procedure of which the name is in @.CURR_PROC_NAME.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Can I use a variable to hold the target table name.

We need to pull from a table that is named tablename_mmddyy and populate a table with the same format tablename_mmddyy. The date will be different every month so I want to be able to build the tablenames every month. Is there a way to do this in SSIS? Thank you.Yes, using expressions in your variables, you can do what you want.

Create a variable and then for its properties, set EvaluateAsExpression to True and then build your expression accordingly in the Expression box.

Then in your OLE DB source/destination, you can use that variable as the table. Just know that you'll want to probably set DelayValidation to True on your connection managers when you do this.

There are plenty of examples in this forum if you search for them on how to build expressions, if you need.|||I found the option for this under Data Access Mode but we need to do the same thing for the database name which will be in a similar format e.g. databasename_mmddyy and the source db name will change every month.|||Same thing. You can use expressions on your connection managers to dynamically create the current connection string.|||The same principals can be applied to a connection manager (i.e. database name of database server). You can use an expression to build the connection string from a variable in the properties window of the connection manager.|||Any examples of this?|||

agentf1 wrote:

Any examples of this?

In the ConnectionString expression box for a connection manager. Also it can't hurt to set DelayValidation to True.

"Data Source=" + @.[User::YourDatabaseNameVariable] +";Initial Catalog=Report;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"|||What goes in the database name field? I am assuming that it gets overridden by the expression.|||

agentf1 wrote:

What goes in the database name field? I am assuming that it gets overridden by the expression.

The ConnectionString will override any other parameters.|||

agentf1 wrote:

Any examples of this?

http://search.live.com/results.aspx?FORM=QBJK&q1=macro%3Ajamiet.ssis&q=expressions

-JT

|||This seems to be changing both of my OLE DB Connections. Is there a way one can be static and the other updated by the variable? It also appears that Data Source controls the server and Catalog is the database name.|||

agentf1 wrote:

This seems to be changing both of my OLE DB Connections. Is there a way one can be static and the other updated by the variable?

That's impossible (unless one of us has misunderstood). An expression on the ConnectionString property of a connection manager only changes that connection manager.

agentf1 wrote:

It also appears that Data Source controls the server and Catalog is the database name.

Correct.

-Jamie

|||

Jamie Thomson wrote:

agentf1 wrote:

This seems to be changing both of my OLE DB Connections. Is there a way one can be static and the other updated by the variable?

That's impossible (unless one of us has misunderstood). An expression on the ConnectionString property of a connection manager only changes that connection manager.

agentf1 wrote:

It also appears that Data Source controls the server and Catalog is the database name.

Correct.

-Jamie

My bad, I had the expression on both connections. I am OK, thanks for all the help, worked beautifully.

Can I query my report dataset to populate a textbox?

My report is a series of textbox fields that all will show data from an
Activity table. The report dataset is SELECT * FROM Activity and is named
"Activities".
Textbox1 = count of all activities
Textbox2 = count of all activities where type = prospect
Textbox3 = count of all activities where type = demo
I know I can create separate datasets for each subsequent query but since
they are all queries from the Activity table, I was wondering if I could set
the value for Textbox2 by querying the dataset named "Activities" instead of
creating a new dataset. For example, SELECT Count(*) FROM Activities Where
Type = 'prospect'
Thank you,
MarkOn Apr 6, 11:01=A0am, Markw911 <Markw...@.discussions.microsoft.com>
wrote:
> My report is a series of textbox fields that all will show data from an
> Activity table. =A0The report dataset is SELECT * FROM Activity and is nam=ed
> "Activities".
> Textbox1 =3D count of all activities
> Textbox2 =3D count of all activities where type =3D prospect
> Textbox3 =3D count of all activities where type =3D demo
> I know I can create separate datasets for each subsequent query but since
> they are all queries from the Activity table, I was wondering if I could s=et
> the value for Textbox2 by querying the dataset named "Activities" instead =of
> creating a new dataset. =A0For example, SELECT Count(*) FROM Activities Wh=ere
> Type =3D 'prospect'
> Thank you,
> Mark
Is there a particular reason you're using a series of textboxes
instead of a table? With a table you could just draw what you need
directly from your dataset.|||I am trying to mimic the layout of an existing report. I had a table but did
not think it could handle some of the more detailed results like:
Count column meetingcanceled if meetingcanceled = 'yes'
I still am not sure how I would handle this in a table:
> > Textbox2 = count of all activities where type = prospect
> > Textbox3 = count of all activities where type = demo
since I am trying to return multiple totals from the same column.
From your comments, it sounds like I could have multiple sums per column
with different criteria. If that is true, how do I go about doing it? Use
the activities type column above to give an example if possible.
Thanks.
"toolman" wrote:
> On Apr 6, 11:01 am, Markw911 <Markw...@.discussions.microsoft.com>
> wrote:
> > My report is a series of textbox fields that all will show data from an
> > Activity table. The report dataset is SELECT * FROM Activity and is named
> > "Activities".
> > Textbox1 = count of all activities
> > Textbox2 = count of all activities where type = prospect
> > Textbox3 = count of all activities where type = demo
> >
> > I know I can create separate datasets for each subsequent query but since
> > they are all queries from the Activity table, I was wondering if I could set
> > the value for Textbox2 by querying the dataset named "Activities" instead of
> > creating a new dataset. For example, SELECT Count(*) FROM Activities Where
> > Type = 'prospect'
> > Thank you,
> > Mark
> Is there a particular reason you're using a series of textboxes
> instead of a table? With a table you could just draw what you need
> directly from your dataset.
>|||On Apr 8, 7:54=A0pm, Markw911 <Markw...@.discussions.microsoft.com>
wrote:
> I am trying to mimic the layout of an existing report. =A0I had a table bu=t did
> not think it could handle some of the more detailed results like:
> Count column meetingcanceled if meetingcanceled =3D 'yes'
> I still am not sure how I would handle this in a table:> > Textbox2 =3D co=unt of all activities where type =3D prospect
> > > Textbox3 =3D count of all activities where type =3D demo
> since I am trying to return multiple totals from the same column.
> From your comments, it sounds like I could have multiple sums per column
> with different criteria. =A0If that is true, how do I go about doing it? ==A0Use
> the activities type column above to give an example if possible.
> Thanks.
>
> "toolman" wrote:
> > On Apr 6, 11:01 am, Markw911 <Markw...@.discussions.microsoft.com>
> > wrote:
> > > My report is a series of textbox fields that all will show data from a=n
> > > Activity table. =A0The report dataset is SELECT * FROM Activity and is= named
> > > "Activities".
> > > Textbox1 =3D count of all activities
> > > Textbox2 =3D count of all activities where type =3D prospect
> > > Textbox3 =3D count of all activities where type =3D demo
> > > I know I can create separate datasets for each subsequent query but si=nce
> > > they are all queries from the Activity table, I was wondering if I cou=ld set
> > > the value for Textbox2 by querying the dataset named "Activities" inst=ead of
> > > creating a new dataset. =A0For example, SELECT Count(*) FROM Activitie=s Where
> > > Type =3D 'prospect'
> > > Thank you,
> > > Mark
> > Is there a particular reason you're using a series of textboxes
> > instead of a table? =A0With a table you could just =A0draw what you need=
> > directly from your dataset.- Hide quoted text -
> - Show quoted text -
You could insert the following expressions into your table cells in
the table footer row. Hide or remove the detail row.
Table Header Row: All Activities_____________/
Prospects______________________________/Demos
Table Footer Row: =3DCount(Fields!Type.Value) =3DSUM(IIF(Fields!
Type.Value =3D "Prospect",1,0)) =3DSUM(IIF(Fields!Type.Value =3D "Demo",
1,0))
OR
Substitute =3DCOUNT(IIF(Fields!Type.Value =3D "Prospect",Fields!
Type.Value,Nothing)) for =3DSUM(IIF(Fields!Type.Value =3D "Prospect",1,0))
Either expression works
Good luck|||On Apr 9, 3:31=A0pm, toolman <t...@.infocision.com> wrote:
> On Apr 8, 7:54=A0pm, Markw911 <Markw...@.discussions.microsoft.com>
> wrote:
>
> > I am trying to mimic the layout of an existing report. =A0I had a table =but did
> > not think it could handle some of the more detailed results like:
> > Count column meetingcanceled if meetingcanceled =3D 'yes'
> > I still am not sure how I would handle this in a table:> > Textbox2 =3D =count of all activities where type =3D prospect
> > > > Textbox3 =3D count of all activities where type =3D demo
> > since I am trying to return multiple totals from the same column.
> > From your comments, it sounds like I could have multiple sums per column=
> > with different criteria. =A0If that is true, how do I go about doing it?= =A0Use
> > the activities type column above to give an example if possible.
> > Thanks.
> > "toolman" wrote:
> > > On Apr 6, 11:01 am, Markw911 <Markw...@.discussions.microsoft.com>
> > > wrote:
> > > > My report is a series of textbox fields that all will show data from= an
> > > > Activity table. =A0The report dataset is SELECT * FROM Activity and =is named
> > > > "Activities".
> > > > Textbox1 =3D count of all activities
> > > > Textbox2 =3D count of all activities where type =3D prospect
> > > > Textbox3 =3D count of all activities where type =3D demo
> > > > I know I can create separate datasets for each subsequent query but =since
> > > > they are all queries from the Activity table, I was wondering if I c=ould set
> > > > the value for Textbox2 by querying the dataset named "Activities" in=stead of
> > > > creating a new dataset. =A0For example, SELECT Count(*) FROM Activit=ies Where
> > > > Type =3D 'prospect'
> > > > Thank you,
> > > > Mark
> > > Is there a particular reason you're using a series of textboxes
> > > instead of a table? =A0With a table you could just =A0draw what you ne=ed
> > > directly from your dataset.- Hide quoted text -
> > - Show quoted text -
> You could insert the following expressions into your table cells in
> the table footer row. =A0Hide or remove the detail row.
> Table Header Row: All Activities_____________/
> Prospects______________________________/Demos
> Table Footer =A0Row: =3DCount(Fields!Type.Value) =A0 =3DSUM(IIF(Fields!
> Type.Value =3D "Prospect",1,0)) =3DSUM(IIF(Fields!Type.Value =3D "Demo",
> 1,0))
> OR
> Substitute =3DCOUNT(IIF(Fields!Type.Value =3D "Prospect",Fields!
> Type.Value,Nothing)) for =3DSUM(IIF(Fields!Type.Value =3D "Prospect",1,0))=
> Either expression works
> Good luck- Hide quoted text -
> - Show quoted text -
That's really ugly...
Hopefully, this is more clear
Table Header Row:
Column 1: All Activities Column 2: Prospects Column 3: Demos
Table Footer Row:
Column 1: =3DCount(Fields!Type.Value)
Column 2: =3DSUM(IIF(Fields!
Type.Value =3D "Prospect",1,0))
Column 3: =3DSUM(IIF(Fields!Type.Value =3D "Demo",1,0))