Referensing an alias field within the same view

I have created an Alias field in a View using a Case statement and next I
want to reference that Alias field in another Alias field with a Case
statement. I get an error stating the first field is not valid. Example of
what I'm trying to do
Column
Alias
CASE WHEN [A] = 0 AND [B] = 1 THEN 1 ELSE 0 END Expr1
CASE WHEN [Expr1] = 1 AND [C] = 1 THEN 1 ELSE 0 END Expr2
SQL doesn't like my referencing Expr1 in the second field. I suppose I could
save the first view and then create a new view based on the first but I was
hoping there might be a way to get around that. Thanks for any help.AkAlan,
As you said, it is an alias and you can not reference it in the same column
list. May be using a derived table, a view, or rewiting the expression.
select
orderid, productid, ext_price * (1.00 - (discount / 100.00)) as exp2
from
(
select orderid, productid, quantity * unitprice as ext_price
from [order details]
) as t
go
AMB
"AkAlan" wrote:

> I have created an Alias field in a View using a Case statement and next I
> want to reference that Alias field in another Alias field with a Case
> statement. I get an error stating the first field is not valid. Example of
> what I'm trying to do
> Column
> Alias
> CASE WHEN [A] = 0 AND [B] = 1 THEN 1 ELSE 0 END Expr1
> CASE WHEN [Expr1] = 1 AND [C] = 1 THEN 1 ELSE 0 END Expr2
> SQL doesn't like my referencing Expr1 in the second field. I suppose I cou
ld
> save the first view and then create a new view based on the first but I wa
s
> hoping there might be a way to get around that. Thanks for any help.
>

referencing value in subreport in calculated field on parent

I wish to reference the value in a subreport in a calculated field
elsewhere...
this subreport has one txtbox, no grid or any other objects.
is this possible?
what is the syntax...'
i.e. =mysubReport.mytxtbox.value...........
thanksOn Sep 28, 2:51 pm, r...@.mgk.com wrote:
> I wish to reference the value in a subreport in a calculated field
> elsewhere...
> this subreport has one txtbox, no grid or any other objects.
> is this possible?
> what is the syntax...'
> i.e. =mysubReport.mytxtbox.value...........
> thanks
You will need to create a dataset in the main report that accesses the
same data (i.e., query/stored procedure) that is used in the subreport
and add the calculation the same way (basically duplicating the
efforts). Then, in the main report, reference this dataset via an
aggregate expression, for example:
=Max(Fields!SomeFieldName.Value, "NewDataSetName")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Sep 29, 10:27 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Sep 28, 2:51 pm, r...@.mgk.com wrote:
> > I wish to reference the value in a subreport in a calculated field
> > elsewhere...
> > this subreport has one txtbox, no grid or any other objects.
> > is this possible?
> > what is the syntax...'
> > i.e. =mysubReport.mytxtbox.value...........
> > thanks
> You will need to create a dataset in the main report that accesses the
> same data (i.e., query/stored procedure) that is used in the subreport
> and add the calculation the same way (basically duplicating the
> efforts). Then, in the main report, reference this dataset via an
> aggregate expression, for example:
> =Max(Fields!SomeFieldName.Value, "NewDataSetName")
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
argh! i was afraid of that...sql

referencing to a function..

I was created a function named aa.

It is possible to reference to it without using the dbo. prnounce ?

eg: select aa(), not select dbo.aa()

thanks,

ViktorThe owner name is required for scalar UDFs but not for table-valued UDFs.
Otherwise it could be difficult to identify from the syntax that a scalar
UDF was being referenced.

--
David Portas
SQL Server MVP
--

Referencing the temporary Dataset in Lookup control SQL

Does anyone know how to reference the ongoing dataset within the dataflow in sql?

So here is the scenario I have.

1) OLE DB SOURCE
2) Lookup a value and that column gets added to the dataset
3) Lookup another value but this time I would rather code the sql rather than select a referencing table. How do I reference the current dataset?

Thanks,
ChestonI'm not clear on what you are trying to do.

You can use SQL in a lookup transformation. You can even build a lookup table with literal values in SQL in that lookup transformation without hitting a table.|||Let me see if I can be more clear on what I am trying to do.

Table 1 - Ole DB source control
Lookup 1 - Reference another table and based on keys add a foreign key
Lookup 2 - I wish to perform a sql statement to update the newly added foreign key from lookup 1 to a default value if it is null. How do I reference the dataset?

So in the Lookup 2 I select SQL query:
Update dataset set foreign key = 0 where foreign key is null

Then continue on with the dataset with more lookups/transformations.

Does this help?

Cheston|||Use a derived column transformation to set that column to 0 if it is null.

Referencing Textbox Values

Is there a way to reference a value from a textbox in a matrix? In other words I want to pull the value in the textbox that is the column header into a cell in the matrix under certain conditions.

You can try this:

=ReportItems!textbox3.value

or

You should be able to share a value in a hidden textbox using code.

Create a hidden textbox. Add the expression =Code.SetValue(mytextboxcontent) to the textbox.

Add the expression =Code.GetValue() to the matrix.

Create 2 functions

public myval as object

function setValue(value as object) as object

myval = value

return value

end function

function getValue() as object
return myval
end function

|||Thanks. You just helped me solve a bigger problem I've been working on for a week now.

Referencing textbox name in expression

I would like to reference a textbox name in an expression. When I write the
following expression, I get an error saying that the textbox name is
"private":
=ReportItems!txtPatientName.Name
Error: The value expression for the textbox â'textbox1â' contains an error:
[BC30390]
'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem.Private
ReadOnly Property Name() As String' is not accessible in this context because
it is 'Private'.I should probably mention that I am trying to ultimately reference the NAME
of the column/field returned from the stored procedure. Since that seems
impossible, I was hoping to set the name of the textbox to the name of the
column/field and then reference that. For example:
SELECT AdmitDate as [AdmitDate],
DischargeDate as [DischargeDate]
FROM Table_Name
In the report I would set the name of the textbox that displays the
AdmitDate field to "AdmitDate" so that I could reference the name of the
column/field being returned by the procedure with this statement:
=ReportItems!AdmitDate.Name
Is there any way to reference the name of the field/column being returned by
the procedure or the name of a textbox?
"mssarahlynn" wrote:
> I would like to reference a textbox name in an expression. When I write the
> following expression, I get an error saying that the textbox name is
> "private":
> =ReportItems!txtPatientName.Name
> Error: The value expression for the textbox â'textbox1â' contains an error:
> [BC30390]
> 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem.Private
> ReadOnly Property Name() As String' is not accessible in this context because
> it is 'Private'.
>
>

Referencing Text Boxes from other Text Boxes

Is it possible to reference the value of a text box (txtField1) in
another text box's expression?
Example:
txtField1 Expression (txtField1 is in Group1 footer)
=Sum(WidgetsCount)
txtField2 Expression (txtField2 is in Group2 footer)
=Sum(WidgetsCount)
txtResultLbl Expression (txtResultLbl is in Group1 header)
=IIf(txtField1 > txtField2, "Greater", "Less")
I am not sure if the cell needs to be referenced through the properties
of the table in which all the cells reside. (Table1)
Your help is apprectiated,
sturgisYou are making a conceptual mistake. You do not want to reference the text
box. For this sort of thing you need to be referencing the field not the
text box. For instance, if you have a group (for a subtotal let's say) then
you put this in:
= sum(Fields!Fieldname1.Value)
On the table footer you can put:
=Sum(Fields!Fieldname1.Value) or you can put
=Sum(Fields!Fieldname1.Value,"Datasetname")
What the textbox is called matters not at all.
Hope that helps.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sturgis" <stevesturgis@.gmail.com> wrote in message
news:1128357572.656545.186070@.f14g2000cwb.googlegroups.com...
> Is it possible to reference the value of a text box (txtField1) in
> another text box's expression?
> Example:
> txtField1 Expression (txtField1 is in Group1 footer)
> =Sum(WidgetsCount)
> txtField2 Expression (txtField2 is in Group2 footer)
> =Sum(WidgetsCount)
> txtResultLbl Expression (txtResultLbl is in Group1 header)
> =IIf(txtField1 > txtField2, "Greater", "Less")
> I am not sure if the cell needs to be referenced through the properties
> of the table in which all the cells reside. (Table1)
> Your help is apprectiated,
> sturgis
>|||I don't believe I am making a conceptual mistake since this can easily
be done in Microsoft Access reports.
I am creating a header label that is conditioned upon the totals from
two different groups. You would never expect the group closest to the
detail to have a value less than the next group up but, in some cases,
the group totals could be the same. When they are the same, I don't
want to show the group identifier for the group closest to the detail,
only the identifier for the outer group. This cannot be done by
referencing the field since Sum(Field1.Value) = Sum(Field1.Value) will
always equal.
Hdr1 = "Division " + IIf(DivisionTotals = AreaTotals, "", " > Area ")
Hdr2 - NOT USED
Ftr2 (Visible Condition set to equality of DivisionTotals and
AreaTotals)
Ftr1 = "Division " + IIf(DivisionTotals = AreaTotals, "", " > Area ")
Microsoft Access allows you to reference the text boxes in footers from
expressions in text boxes within headers. Reporting Services has taken
a step backwards if it cannot also be done.
Thanks,
sturgis|||Access and RS are different. You can access textboxes but for what you are
trying to do you should be creating your formulaes based on the field
values. Everything you want to do is possible. RS can do some things Access
can't and Access can do some things that RS can't. Regardless, even if the
end result is the same that does not mean they do it the same way.
Again, you are missing a concept. In BOL search on the phrase expressions.
There will be 5 titles to select from. I suggest reading all 5. They show
doing what you are interested in doing.
If you do as I suggest it will help you make the leap from Access to RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sturgis" <stevesturgis@.gmail.com> wrote in message
news:1128360396.740290.263370@.g14g2000cwa.googlegroups.com...
>I don't believe I am making a conceptual mistake since this can easily
> be done in Microsoft Access reports.
> I am creating a header label that is conditioned upon the totals from
> two different groups. You would never expect the group closest to the
> detail to have a value less than the next group up but, in some cases,
> the group totals could be the same. When they are the same, I don't
> want to show the group identifier for the group closest to the detail,
> only the identifier for the outer group. This cannot be done by
> referencing the field since Sum(Field1.Value) = Sum(Field1.Value) will
> always equal.
> Hdr1 = "Division " + IIf(DivisionTotals = AreaTotals, "", " > Area ")
> Hdr2 - NOT USED
> Ftr2 (Visible Condition set to equality of DivisionTotals and
> AreaTotals)
> Ftr1 = "Division " + IIf(DivisionTotals = AreaTotals, "", " > Area ")
> Microsoft Access allows you to reference the text boxes in footers from
> expressions in text boxes within headers. Reporting Services has taken
> a step backwards if it cannot also be done.
> Thanks,
> sturgis
>|||The nice part of Access reporting was that you didn't have to replicate
the formula in any expression that used it. You merely referenced the
text box with the initial formula. This is better programming practice.
If the formula changes, it changes in all expressions that reference
it. You don't have to try to remember where all the occurances of your
formula are (albeit, there should not be that many).
I have read the 5 titles to which you referred and found no reference
to what I am doing.
Thanks,
sturgis|||I was able to access the text boxes through the ReportItems collection.
Thanks,
sturgis

Referencing tables on a remote server

I have multiple stored procedures which include queries that reference tables
on a remote, linked server. For example,
select id, name from remoteserver.db.dbo.table1
In order to speed up my queries and avoids lockouts while data is being
inserted into those remote tables, I would like to use the WITH (NOLOCK)
clause. However, i cannot use that on remote tables. I thought of creating
local views for each of those remote tables, for example,
create view v_table1 as select id, name from remoteserver.db.dbo.table1
and then using that view instead of the original table in my queries. Would
this be faster? Most of my queries involve multiple tables. The remote
tables do not have primary keys, though they do have multiple indexes.
I should add that I do not have permission to change anything on the remote
server. That includes adding stored procedures to that remote database or
adding indexes or keys.
Also, the remote database is massive, multi-terrabytes massive. The tables
I am interested in have millions of rows.
In a related question, when you create a view that references tables on a
remote server, how quickly/often does it get updated when the data in those
tables gets changed? Am I creating a CPU burden on the remote server or only
my own? My application does not insert/update/delete data on the remote
server, only queries it. On the remote server, however, there is a great
deal of data insertion going on throughout the day and night.
Any suggestions?
Hi
A view does not copy data between servers, it is just a way at looking at
the data in table/tables.
If you create a view, the data, meeting the criteria, still has to be pulled
from the other server to be joined on the local server.
Think of a view as a Window to the other data.
Cheers
Mike
"speegee" wrote:

> I have multiple stored procedures which include queries that reference tables
> on a remote, linked server. For example,
> select id, name from remoteserver.db.dbo.table1
> In order to speed up my queries and avoids lockouts while data is being
> inserted into those remote tables, I would like to use the WITH (NOLOCK)
> clause. However, i cannot use that on remote tables. I thought of creating
> local views for each of those remote tables, for example,
> create view v_table1 as select id, name from remoteserver.db.dbo.table1
> and then using that view instead of the original table in my queries. Would
> this be faster? Most of my queries involve multiple tables. The remote
> tables do not have primary keys, though they do have multiple indexes.
> I should add that I do not have permission to change anything on the remote
> server. That includes adding stored procedures to that remote database or
> adding indexes or keys.
> Also, the remote database is massive, multi-terrabytes massive. The tables
> I am interested in have millions of rows.
> In a related question, when you create a view that references tables on a
> remote server, how quickly/often does it get updated when the data in those
> tables gets changed? Am I creating a CPU burden on the remote server or only
> my own? My application does not insert/update/delete data on the remote
> server, only queries it. On the remote server, however, there is a great
> deal of data insertion going on throughout the day and night.
> Any suggestions?
>

Referencing tables on a remote server

I have multiple stored procedures which include queries that reference tables
on a remote, linked server. For example,
select id, name from remoteserver.db.dbo.table1
In order to speed up my queries and avoids lockouts while data is being
inserted into those remote tables, I would like to use the WITH (NOLOCK)
clause. However, i cannot use that on remote tables. I thought of creating
local views for each of those remote tables, for example,
create view v_table1 as select id, name from remoteserver.db.dbo.table1
and then using that view instead of the original table in my queries. Would
this be faster? Most of my queries involve multiple tables. The remote
tables do not have primary keys, though they do have multiple indexes.
I should add that I do not have permission to change anything on the remote
server. That includes adding stored procedures to that remote database or
adding indexes or keys.
Also, the remote database is massive, multi-terrabytes massive. The tables
I am interested in have millions of rows.
In a related question, when you create a view that references tables on a
remote server, how quickly/often does it get updated when the data in those
tables gets changed? Am I creating a CPU burden on the remote server or only
my own? My application does not insert/update/delete data on the remote
server, only queries it. On the remote server, however, there is a great
deal of data insertion going on throughout the day and night.
Any suggestions?Hi
A view does not copy data between servers, it is just a way at looking at
the data in table/tables.
If you create a view, the data, meeting the criteria, still has to be pulled
from the other server to be joined on the local server.
Think of a view as a Window to the other data.
Cheers
Mike
"speegee" wrote:
> I have multiple stored procedures which include queries that reference tables
> on a remote, linked server. For example,
> select id, name from remoteserver.db.dbo.table1
> In order to speed up my queries and avoids lockouts while data is being
> inserted into those remote tables, I would like to use the WITH (NOLOCK)
> clause. However, i cannot use that on remote tables. I thought of creating
> local views for each of those remote tables, for example,
> create view v_table1 as select id, name from remoteserver.db.dbo.table1
> and then using that view instead of the original table in my queries. Would
> this be faster? Most of my queries involve multiple tables. The remote
> tables do not have primary keys, though they do have multiple indexes.
> I should add that I do not have permission to change anything on the remote
> server. That includes adding stored procedures to that remote database or
> adding indexes or keys.
> Also, the remote database is massive, multi-terrabytes massive. The tables
> I am interested in have millions of rows.
> In a related question, when you create a view that references tables on a
> remote server, how quickly/often does it get updated when the data in those
> tables gets changed? Am I creating a CPU burden on the remote server or only
> my own? My application does not insert/update/delete data on the remote
> server, only queries it. On the remote server, however, there is a great
> deal of data insertion going on throughout the day and night.
> Any suggestions?
>|||OPENQUERY will solve your problem. Examples:
--This will fail:
select * from server1.bb01_db.dbo.bb01 (nolock)
where process_dt = '2002-12-02' and mid = '03301001' and
tran_dt = '2002-12-02'
--This will work if you have your linked servers set up --
correctly:
select * from openquery(server1, 'select * from
bb01_db.dbo.bb01 (nolock)
where process_dt = ''2002-12-02'' and mid = ''03301001''
and tran_dt = ''2002-12-02''')
.
>--Original Message--
>I have multiple stored procedures which include queries
that reference tables
>on a remote, linked server. For example,
>select id, name from remoteserver.db.dbo.table1
>In order to speed up my queries and avoids lockouts while
data is being
>inserted into those remote tables, I would like to use
the WITH (NOLOCK)
>clause. However, i cannot use that on remote tables. I
thought of creating
>local views for each of those remote tables, for example,
>create view v_table1 as select id, name from
remoteserver.db.dbo.table1
>and then using that view instead of the original table in
my queries. Would
>this be faster? Most of my queries involve multiple
tables. The remote
>tables do not have primary keys, though they do have
multiple indexes.
>I should add that I do not have permission to change
anything on the remote
>server. That includes adding stored procedures to that
remote database or
>adding indexes or keys.
>Also, the remote database is massive, multi-terrabytes
massive. The tables
>I am interested in have millions of rows.
>In a related question, when you create a view that
references tables on a
>remote server, how quickly/often does it get updated when
the data in those
>tables gets changed? Am I creating a CPU burden on the
remote server or only
>my own? My application does not insert/update/delete
data on the remote
>server, only queries it. On the remote server, however,
there is a great
>deal of data insertion going on throughout the day and
night.
>Any suggestions?
>.
>

referencing subreport value

What is the syntax to reference a subreports returning value?

I need to subtotal values returning from a subreport, but I don't know how to reference it.

Thanks,

Hi,

From your description, it seems that you want to reference a subreport in the parent report, right?

Based on my knowledge, you cannot reference a subreport from the parentreport. I suggest that you should have a DataSet in the parent report which achieves the summing work from the datasource or just create a subreport with the subtotals you need.

Thanks.

|||

I found out a way to this with a subreport. Nevermind.

Neither of your suggestions will work because the data in my subreport is referencing a child table.

I cannot use a subreport to do the summing because I need to attach these values when a group changes.

Any other ideas?

Referencing SQLDMO in UNSAFE assembly

I created a SQL Server Project in VS 2005, and tried to add a reference to SQLDMO Object dll. But the reference --> add reference, does not allow it. It does not work, even if I set the Assembly Permission Level to UNAFE/EXTERNAL ACCESS.

Reason I'm trying to do this:

We have a SQL 2000 stored procedure that uses SQLDMO using sp_OACreate to BCP files to database. We are converting to 2005 and because of SOX restrictions, I'm trying to replace the sp_OACreate part with external stored procedure written in C#.

According to BOL, I thought atleast UNSAFE should support this, but it seems not.

Probably, I'm expecting too much..

Your help is greatly appreciated.

Thanks

Thanks to Vineet Rao's posting to one of of the answers on this forum, to a similar problem.

I registered "signed" InterOp.SQLDMO.dll (which is obtained by referecing to a regular project) , as UNSAFE, and it appeared in the Reference section of SQL Server Project.

Thanks

Baskar

Referencing ReportItems in Subreport from Main Report

Is this at all possible in SQL 2000 reporting services?
I want to reference a textbox value from the sub report into the main
report.
Thanks
GeorgeOn Dec 6, 6:18 pm, "_george" <none@.nojne@.none> wrote:
> Is this at all possible in SQL 2000 reporting services?
> I want to reference a textbox value from the sub report into the main
> report.
> Thanks
> George
The best way to accommodate this is to create the same dataset that is
used in the subreport's textbox control (where applicable) in the main
report. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

referencing report parameters in code

I'm trying to reference a report parameter in the code window:
Dim CompId As Integer
CompId = Parameters!CompanyId.Value
It gives me the error:
[BC30469] Reference to a non-shared member requires an object reference.
Any ideas (or alternatives) on how to resolve this? Thank you.TechnoSpyke,
The syntax to reference a report parameter has to be done by instantiating a
Reporting Object
For example:
Dim strParamName as string = â'ThisAStringLiteralâ'
Dim strParamValue as string = â'ThisAStringLiteralâ'
Dim Parameters(0) As Reporting.ParameterValue
Parameters(0) = New Reporting.ParameterValue
Parameters(0).Name = strParamName
Parameters(0).Value = strUSerID
For a better documentation, reference the SOAP API call in BOL
rwiethorn
"TechnoSpyke" wrote:
> I'm trying to reference a report parameter in the code window:
> Dim CompId As Integer
> CompId = Parameters!CompanyId.Value
> It gives me the error:
> [BC30469] Reference to a non-shared member requires an object reference.
> Any ideas (or alternatives) on how to resolve this? Thank you.
>
>|||Thanks for the reply, although I still have some problems.
What I wanted was to set a variable based on a report parameter (assuming
that this parameter has already a value), from within the report (code
window). The code below was taken from the BOL (Initializing Custom
Assembly Objects).
<Code>
Dim m_myClass As MyClass
Protected Overrides Sub OnInit()
m_myClass = new MyClass(User!Language, Paramters!Territory)
End Sub
</Code>
This is very similar to what I'm trying to do, yet I get a "[BC30469]
Reference to a non-shared member requires an object reference." everytime I
reference the Parameters.
"rwiethorn" <rwiethorn@.discussions.microsoft.com> wrote in message
news:BA485D54-C3F9-453A-8883-CADAD78570B5@.microsoft.com...
> TechnoSpyke,
> The syntax to reference a report parameter has to be done by instantiating
> a
> Reporting Object
> For example:
> Dim strParamName as string = "ThisAStringLiteral"
> Dim strParamValue as string = "ThisAStringLiteral"
> Dim Parameters(0) As Reporting.ParameterValue
> Parameters(0) = New Reporting.ParameterValue
> Parameters(0).Name = strParamName
> Parameters(0).Value = strUSerID
> For a better documentation, reference the SOAP API call in BOL
> rwiethorn
> "TechnoSpyke" wrote:
>> I'm trying to reference a report parameter in the code window:
>> Dim CompId As Integer
>> CompId = Parameters!CompanyId.Value
>> It gives me the error:
>> [BC30469] Reference to a non-shared member requires an object reference.
>> Any ideas (or alternatives) on how to resolve this? Thank you.
>>|||TechnoSpyke,
Were are you trying to get the value?
-in a custom assembly?
-in the code page of the report?
-in the expression builder of a control on a report?
If you're in a report, the report knows the name of the parameter. You
reference the param by name. So try to assign the value into a variable by
name.
I've not done that, but read about it. Search this newsgroup for params and
search BOL for param usage.
"TechnoSpyke" wrote:
> Thanks for the reply, although I still have some problems.
> What I wanted was to set a variable based on a report parameter (assuming
> that this parameter has already a value), from within the report (code
> window). The code below was taken from the BOL (Initializing Custom
> Assembly Objects).
> <Code>
> Dim m_myClass As MyClass
> Protected Overrides Sub OnInit()
> m_myClass = new MyClass(User!Language, Paramters!Territory)
> End Sub
> </Code>
> This is very similar to what I'm trying to do, yet I get a "[BC30469]
> Reference to a non-shared member requires an object reference." everytime I
> reference the Parameters.
>
> "rwiethorn" <rwiethorn@.discussions.microsoft.com> wrote in message
> news:BA485D54-C3F9-453A-8883-CADAD78570B5@.microsoft.com...
> > TechnoSpyke,
> > The syntax to reference a report parameter has to be done by instantiating
> > a
> > Reporting Object
> >
> > For example:
> > Dim strParamName as string = "ThisAStringLiteral"
> > Dim strParamValue as string = "ThisAStringLiteral"
> > Dim Parameters(0) As Reporting.ParameterValue
> >
> > Parameters(0) = New Reporting.ParameterValue
> > Parameters(0).Name = strParamName
> > Parameters(0).Value = strUSerID
> >
> > For a better documentation, reference the SOAP API call in BOL
> >
> > rwiethorn
> >
> > "TechnoSpyke" wrote:
> >
> >> I'm trying to reference a report parameter in the code window:
> >>
> >> Dim CompId As Integer
> >> CompId = Parameters!CompanyId.Value
> >>
> >> It gives me the error:
> >> [BC30469] Reference to a non-shared member requires an object reference.
> >>
> >> Any ideas (or alternatives) on how to resolve this? Thank you.
> >>
> >>
> >>
>
>|||I am trying to get the parameter value from the code page of the report. My
code looks like this (modified for this purpose):
Protected Overrides Sub OnInit()
Dim compId As Integer
'following code returns error [BC30469]
compId = Parameters!CompanyId
'following code still returns error [BC30469]
compId = Parameters!CompanyId.Value
'following code doesn't return an error, although not sure if I have the
correct value
compId = Report.Parameters("CompanyId").Value
End Sub
"rwiethorn" <rwiethorn@.discussions.microsoft.com> wrote in message
news:A5C1CEDC-1751-48FA-936A-ADEF01259F22@.microsoft.com...
> TechnoSpyke,
> Were are you trying to get the value?
> -in a custom assembly?
> -in the code page of the report?
> -in the expression builder of a control on a report?
> If you're in a report, the report knows the name of the parameter. You
> reference the param by name. So try to assign the value into a variable by
> name.
> I've not done that, but read about it. Search this newsgroup for params
> and
> search BOL for param usage.
> "TechnoSpyke" wrote:
>> Thanks for the reply, although I still have some problems.
>> What I wanted was to set a variable based on a report parameter (assuming
>> that this parameter has already a value), from within the report (code
>> window). The code below was taken from the BOL (Initializing Custom
>> Assembly Objects).
>> <Code>
>> Dim m_myClass As MyClass
>> Protected Overrides Sub OnInit()
>> m_myClass = new MyClass(User!Language, Paramters!Territory)
>> End Sub
>> </Code>
>> This is very similar to what I'm trying to do, yet I get a "[BC30469]
>> Reference to a non-shared member requires an object reference." everytime
>> I
>> reference the Parameters.
>>
>> "rwiethorn" <rwiethorn@.discussions.microsoft.com> wrote in message
>> news:BA485D54-C3F9-453A-8883-CADAD78570B5@.microsoft.com...
>> > TechnoSpyke,
>> > The syntax to reference a report parameter has to be done by
>> > instantiating
>> > a
>> > Reporting Object
>> >
>> > For example:
>> > Dim strParamName as string = "ThisAStringLiteral"
>> > Dim strParamValue as string = "ThisAStringLiteral"
>> > Dim Parameters(0) As Reporting.ParameterValue
>> >
>> > Parameters(0) = New Reporting.ParameterValue
>> > Parameters(0).Name = strParamName
>> > Parameters(0).Value = strUSerID
>> >
>> > For a better documentation, reference the SOAP API call in BOL
>> >
>> > rwiethorn
>> >
>> > "TechnoSpyke" wrote:
>> >
>> >> I'm trying to reference a report parameter in the code window:
>> >>
>> >> Dim CompId As Integer
>> >> CompId = Parameters!CompanyId.Value
>> >>
>> >> It gives me the error:
>> >> [BC30469] Reference to a non-shared member requires an object
>> >> reference.
>> >>
>> >> Any ideas (or alternatives) on how to resolve this? Thank you.
>> >>
>> >>
>> >>
>>

Referencing query results by number

Is there a way to reference the results of a query by column number?
i.e. select Name, City from tblEmployee
so referencing field 1 from the query in a table will list all the employee
names
Thanks.select Name As Field1, City As Field2 from tblEmployee
OR
select au_id As [1], city As [2], state As [3] from authors
[In the latter case, the fields will be generated as ID1, ID2, and ID3
respectively.]
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ali Jaffer" <Ali Jaffer@.discussions.microsoft.com> wrote in message
news:0BB8CBE4-0FCD-4444-B00B-99EDE49CF5D0@.microsoft.com...
> Is there a way to reference the results of a query by column number?
> i.e. select Name, City from tblEmployee
> so referencing field 1 from the query in a table will list all the
employee
> names
> Thanks.

referencing linked server in stored procedures

hello, i need to reference a table in a sqlserver instance from a
stored procedure in a different sqlserverinsctance... it's possible
reference the table from the stored procedure?
Did you try using full naming:
servername.database.owner.Tablename
MC
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1133304999.836436.40130@.g49g2000cwa.googlegro ups.com...
> hello, i need to reference a table in a sqlserver instance from a
> stored procedure in a different sqlserverinsctance... it's possible
> reference the table from the stored procedure?
>

referencing linked server in stored procedures

hello, i need to reference a table in a sqlserver instance from a
stored procedure in a different sqlserverinsctance... it's possible
reference the table from the stored procedure?Did you try using full naming:
servername.database.owner.Tablename
MC
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1133304999.836436.40130@.g49g2000cwa.googlegroups.com...
> hello, i need to reference a table in a sqlserver instance from a
> stored procedure in a different sqlserverinsctance... it's possible
> reference the table from the stored procedure?
>

referencing linked server in stored procedures

hello, i need to reference a table in a sqlserver instance from a
stored procedure in a different sqlserverinsctance... it's possible
reference the table from the stored procedure?Did you try using full naming:
servername.database.owner.Tablename
MC
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1133304999.836436.40130@.g49g2000cwa.googlegroups.com...
> hello, i need to reference a table in a sqlserver instance from a
> stored procedure in a different sqlserverinsctance... it's possible
> reference the table from the stored procedure?
>

referencing fields in the code window

Hi, how do I reference a report field in a custom function which is written
in the code window (Report Properties/code tab)? i.e.
Function Something
If Fields!FIELD1_Company.Value = Nothing Then
Return ""
End If
End Function
This example will not work but it's similar to my needs
Thanks
NPSend the field value as a parameter to your function. i.e. re-write the
function as:
Function SomeFunction(byVal MyFieldValue as datatype) as datatype
if MyFieldValue is nothing
...
return ...
End Function
In your report expression, just call the function:
=Code.SomeFunction(Fields!MyField.Value)
Charles Kangai, MCT, MCDBA
"slk55guy" wrote:
> Hi, how do I reference a report field in a custom function which is written
> in the code window (Report Properties/code tab)? i.e.
> Function Something
> If Fields!FIELD1_Company.Value = Nothing Then
> Return ""
> End If
> End Function
> This example will not work but it's similar to my needs
> Thanks
> NP
>|||Thanks Charles, by the way how are you? I was on one of your DTS courses a
couple of years ago in the city. Small world eh?
I think this will work but there is another post (converting crystal code) I
put up here and basically I'm trying to get the same functionality as I would
in a Crystal function.
Happy Christmas
NP
"Charles Kangai" wrote:
> Send the field value as a parameter to your function. i.e. re-write the
> function as:
> Function SomeFunction(byVal MyFieldValue as datatype) as datatype
> if MyFieldValue is nothing
> ...
> return ...
> End Function
> In your report expression, just call the function:
> =Code.SomeFunction(Fields!MyField.Value)
> Charles Kangai, MCT, MCDBA
>
> "slk55guy" wrote:
> > Hi, how do I reference a report field in a custom function which is written
> > in the code window (Report Properties/code tab)? i.e.
> >
> > Function Something
> > If Fields!FIELD1_Company.Value = Nothing Then
> > Return ""
> > End If
> > End Function
> >
> > This example will not work but it's similar to my needs
> >
> > Thanks
> >
> > NP
> >|||Hi,
It should work. I am using something similar myself.
I did a demonstration and some conversations for a customer earlier this
week. The developers are all Crystal users, and they were so impressed with
Reporting Services that they are going to migrate all their sites in UK,
Italy, USA, and Denmark to RS within the next few weeks.
Great to come across you again - we will be running a Reporting Services
course from the end of Feb. I am the author. Check with Learning Tree.
Merry Christmas!
Charles
"slk55guy" wrote:
> Thanks Charles, by the way how are you? I was on one of your DTS courses a
> couple of years ago in the city. Small world eh?
> I think this will work but there is another post (converting crystal code) I
> put up here and basically I'm trying to get the same functionality as I would
> in a Crystal function.
> Happy Christmas
> NP
> "Charles Kangai" wrote:
> > Send the field value as a parameter to your function. i.e. re-write the
> > function as:
> >
> > Function SomeFunction(byVal MyFieldValue as datatype) as datatype
> > if MyFieldValue is nothing
> > ...
> > return ...
> > End Function
> >
> > In your report expression, just call the function:
> > =Code.SomeFunction(Fields!MyField.Value)
> >
> > Charles Kangai, MCT, MCDBA
> >
> >
> > "slk55guy" wrote:
> >
> > > Hi, how do I reference a report field in a custom function which is written
> > > in the code window (Report Properties/code tab)? i.e.
> > >
> > > Function Something
> > > If Fields!FIELD1_Company.Value = Nothing Then
> > > Return ""
> > > End If
> > > End Function
> > >
> > > This example will not work but it's similar to my needs
> > >
> > > Thanks
> > >
> > > NP
> > >

Referencing field name from different dataset?

Is it possible to use one dataset to reference fields from another dataset
for Reporting Services?
Here's the example for ASP:
Dataset 1:
SELECT CUST_ID, EMP_ID, SALES_ID
FROM CUSTOMER, EMPLOYEE, SALES
WHERE CUSTOMER.CUST_ID = '3232'
ORDER BY CUST_ID
The second query will display the Sales_Amount base on the object from the
first query.
Dataset 2:
SELECT TOP 1 SALES_AMOUNT
FROM CUSTOMER, EMPLOYEE, SALES
WHERE CUSTOMER.CUST_ID = '"&CUST_ID&"'
AND EMPLOYEE.EMP_ID = '"&EMP_ID&"'
AND SALES.ID = '"&SALES_ID&"'
I have a number of reports, migrating from ASP to Reporting Services, and
was wondering if this is possible with RS.
Use the first dataset as is and second dataset. I was thinking about
referencing it like this:
=IIF((Fields!Cust_ID.Value & "Cust_ID").Value AND (Fields!Emp_ID.Value &
"Emp_ID").Value AND (Fields!ID.Value & "Sales_ID").Value),
Fields!Sales_Amount.Value, "N/A")
Intended output:
CustID EmpID SalesID SalesAmount
1 2 3232 $345.00
2 2 3643 $223.00
3 6 8772 $1234.54
4 6 N/A
So Dataset 2 will check the fields and see if there's a match, if it does
then output Sales_Amount base on the output fields. Please advise on what to
do or suggestions.
Thanks.You can not do it the way you are envisioning it but it can be done. The
reason you can't do it the way you are thinking is because RS does not joing
datasets. You can have multiple datasets but they are independent of one
another. What you can do is have two reports. A main and a subreport (a
subreport is a normal report with a parameter). In your case the subreport
would have three parameters. Design the first report, then add a column to
the right of the ones you have (right mouse click, add column). Drop your
subreport into the column. Set the parameters to the fields (right mouse
click on the subreport to do this).
So, to do this make sure you understand report parameters, query parameters,
subreports. It will all fall together for you once you understand those
three things.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"chang" <chang@.discussions.microsoft.com> wrote in message
news:4E26F8E7-F8DB-4177-A967-5FF1178B71FB@.microsoft.com...
> Is it possible to use one dataset to reference fields from another dataset
> for Reporting Services?
> Here's the example for ASP:
> Dataset 1:
> SELECT CUST_ID, EMP_ID, SALES_ID
> FROM CUSTOMER, EMPLOYEE, SALES
> WHERE CUSTOMER.CUST_ID = '3232'
> ORDER BY CUST_ID
> The second query will display the Sales_Amount base on the object from the
> first query.
> Dataset 2:
> SELECT TOP 1 SALES_AMOUNT
> FROM CUSTOMER, EMPLOYEE, SALES
> WHERE CUSTOMER.CUST_ID = '"&CUST_ID&"'
> AND EMPLOYEE.EMP_ID = '"&EMP_ID&"'
> AND SALES.ID = '"&SALES_ID&"'
> I have a number of reports, migrating from ASP to Reporting Services, and
> was wondering if this is possible with RS.
> Use the first dataset as is and second dataset. I was thinking about
> referencing it like this:
> =IIF((Fields!Cust_ID.Value & "Cust_ID").Value AND (Fields!Emp_ID.Value &
> "Emp_ID").Value AND (Fields!ID.Value & "Sales_ID").Value),
> Fields!Sales_Amount.Value, "N/A")
> Intended output:
> CustID EmpID SalesID SalesAmount
> 1 2 3232 $345.00
> 2 2 3643 $223.00
> 3 6 8772 $1234.54
> 4 6 N/A
>
> So Dataset 2 will check the fields and see if there's a match, if it does
> then output Sales_Amount base on the output fields. Please advise on what
to
> do or suggestions.
> Thanks.|||Thank you Bruce. I think what you suggested might be what I need. I've been
pondering this for several weeks now and can't seem to get it to work.
For the subreport, do I need to create another report for that or just the
second dataset?
"Bruce L-C [MVP]" wrote:
> You can not do it the way you are envisioning it but it can be done. The
> reason you can't do it the way you are thinking is because RS does not joing
> datasets. You can have multiple datasets but they are independent of one
> another. What you can do is have two reports. A main and a subreport (a
> subreport is a normal report with a parameter). In your case the subreport
> would have three parameters. Design the first report, then add a column to
> the right of the ones you have (right mouse click, add column). Drop your
> subreport into the column. Set the parameters to the fields (right mouse
> click on the subreport to do this).
> So, to do this make sure you understand report parameters, query parameters,
> subreports. It will all fall together for you once you understand those
> three things.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "chang" <chang@.discussions.microsoft.com> wrote in message
> news:4E26F8E7-F8DB-4177-A967-5FF1178B71FB@.microsoft.com...
> > Is it possible to use one dataset to reference fields from another dataset
> > for Reporting Services?
> >
> > Here's the example for ASP:
> >
> > Dataset 1:
> >
> > SELECT CUST_ID, EMP_ID, SALES_ID
> > FROM CUSTOMER, EMPLOYEE, SALES
> > WHERE CUSTOMER.CUST_ID = '3232'
> > ORDER BY CUST_ID
> >
> > The second query will display the Sales_Amount base on the object from the
> > first query.
> >
> > Dataset 2:
> > SELECT TOP 1 SALES_AMOUNT
> > FROM CUSTOMER, EMPLOYEE, SALES
> > WHERE CUSTOMER.CUST_ID = '"&CUST_ID&"'
> > AND EMPLOYEE.EMP_ID = '"&EMP_ID&"'
> > AND SALES.ID = '"&SALES_ID&"'
> >
> > I have a number of reports, migrating from ASP to Reporting Services, and
> > was wondering if this is possible with RS.
> >
> > Use the first dataset as is and second dataset. I was thinking about
> > referencing it like this:
> >
> > =IIF((Fields!Cust_ID.Value & "Cust_ID").Value AND (Fields!Emp_ID.Value &
> > "Emp_ID").Value AND (Fields!ID.Value & "Sales_ID").Value),
> > Fields!Sales_Amount.Value, "N/A")
> >
> > Intended output:
> > CustID EmpID SalesID SalesAmount
> > 1 2 3232 $345.00
> > 2 2 3643 $223.00
> > 3 6 8772 $1234.54
> > 4 6 N/A
> >
> >
> > So Dataset 2 will check the fields and see if there's a match, if it does
> > then output Sales_Amount base on the output fields. Please advise on what
> to
> > do or suggestions.
> >
> > Thanks.
>
>|||A subreport is just a standard report. Create it by itself and test it out.
Since you want to embed the subreport you should make it very simple. Then
you just drag and drop it into an empty column in a table. Then right click
on it and set the parameters. So, the second dataset is only in the
subreport. You main report only has the one dataset. In certain cases
subreports are really the only way to solve the problem and it can be very
clean way to do so.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"chang" <chang@.discussions.microsoft.com> wrote in message
news:7D5B2A51-394C-4542-8B1A-B47D11BD4305@.microsoft.com...
> Thank you Bruce. I think what you suggested might be what I need. I've
been
> pondering this for several weeks now and can't seem to get it to work.
> For the subreport, do I need to create another report for that or just the
> second dataset?
> "Bruce L-C [MVP]" wrote:
> > You can not do it the way you are envisioning it but it can be done. The
> > reason you can't do it the way you are thinking is because RS does not
joing
> > datasets. You can have multiple datasets but they are independent of one
> > another. What you can do is have two reports. A main and a subreport (a
> > subreport is a normal report with a parameter). In your case the
subreport
> > would have three parameters. Design the first report, then add a column
to
> > the right of the ones you have (right mouse click, add column). Drop
your
> > subreport into the column. Set the parameters to the fields (right mouse
> > click on the subreport to do this).
> >
> > So, to do this make sure you understand report parameters, query
parameters,
> > subreports. It will all fall together for you once you understand those
> > three things.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "chang" <chang@.discussions.microsoft.com> wrote in message
> > news:4E26F8E7-F8DB-4177-A967-5FF1178B71FB@.microsoft.com...
> > > Is it possible to use one dataset to reference fields from another
dataset
> > > for Reporting Services?
> > >
> > > Here's the example for ASP:
> > >
> > > Dataset 1:
> > >
> > > SELECT CUST_ID, EMP_ID, SALES_ID
> > > FROM CUSTOMER, EMPLOYEE, SALES
> > > WHERE CUSTOMER.CUST_ID = '3232'
> > > ORDER BY CUST_ID
> > >
> > > The second query will display the Sales_Amount base on the object from
the
> > > first query.
> > >
> > > Dataset 2:
> > > SELECT TOP 1 SALES_AMOUNT
> > > FROM CUSTOMER, EMPLOYEE, SALES
> > > WHERE CUSTOMER.CUST_ID = '"&CUST_ID&"'
> > > AND EMPLOYEE.EMP_ID = '"&EMP_ID&"'
> > > AND SALES.ID = '"&SALES_ID&"'
> > >
> > > I have a number of reports, migrating from ASP to Reporting Services,
and
> > > was wondering if this is possible with RS.
> > >
> > > Use the first dataset as is and second dataset. I was thinking about
> > > referencing it like this:
> > >
> > > =IIF((Fields!Cust_ID.Value & "Cust_ID").Value AND (Fields!Emp_ID.Value
&
> > > "Emp_ID").Value AND (Fields!ID.Value & "Sales_ID").Value),
> > > Fields!Sales_Amount.Value, "N/A")
> > >
> > > Intended output:
> > > CustID EmpID SalesID SalesAmount
> > > 1 2 3232 $345.00
> > > 2 2 3643 $223.00
> > > 3 6 8772 $1234.54
> > > 4 6 N/A
> > >
> > >
> > > So Dataset 2 will check the fields and see if there's a match, if it
does
> > > then output Sales_Amount base on the output fields. Please advise on
what
> > to
> > > do or suggestions.
> > >
> > > Thanks.
> >
> >
> >|||Thank you Bruce. That was most helpful. I was able to get it working.
Should've post it here weeks ago and got this resolve, but I was trying to
work on my own. This only took me 30 minute to get it working instead of
what took me 2 weeks.
Thanks again.
"Bruce L-C [MVP]" wrote:
> A subreport is just a standard report. Create it by itself and test it out.
> Since you want to embed the subreport you should make it very simple. Then
> you just drag and drop it into an empty column in a table. Then right click
> on it and set the parameters. So, the second dataset is only in the
> subreport. You main report only has the one dataset. In certain cases
> subreports are really the only way to solve the problem and it can be very
> clean way to do so.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "chang" <chang@.discussions.microsoft.com> wrote in message
> news:7D5B2A51-394C-4542-8B1A-B47D11BD4305@.microsoft.com...
> > Thank you Bruce. I think what you suggested might be what I need. I've
> been
> > pondering this for several weeks now and can't seem to get it to work.
> >
> > For the subreport, do I need to create another report for that or just the
> > second dataset?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > You can not do it the way you are envisioning it but it can be done. The
> > > reason you can't do it the way you are thinking is because RS does not
> joing
> > > datasets. You can have multiple datasets but they are independent of one
> > > another. What you can do is have two reports. A main and a subreport (a
> > > subreport is a normal report with a parameter). In your case the
> subreport
> > > would have three parameters. Design the first report, then add a column
> to
> > > the right of the ones you have (right mouse click, add column). Drop
> your
> > > subreport into the column. Set the parameters to the fields (right mouse
> > > click on the subreport to do this).
> > >
> > > So, to do this make sure you understand report parameters, query
> parameters,
> > > subreports. It will all fall together for you once you understand those
> > > three things.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > > "chang" <chang@.discussions.microsoft.com> wrote in message
> > > news:4E26F8E7-F8DB-4177-A967-5FF1178B71FB@.microsoft.com...
> > > > Is it possible to use one dataset to reference fields from another
> dataset
> > > > for Reporting Services?
> > > >
> > > > Here's the example for ASP:
> > > >
> > > > Dataset 1:
> > > >
> > > > SELECT CUST_ID, EMP_ID, SALES_ID
> > > > FROM CUSTOMER, EMPLOYEE, SALES
> > > > WHERE CUSTOMER.CUST_ID = '3232'
> > > > ORDER BY CUST_ID
> > > >
> > > > The second query will display the Sales_Amount base on the object from
> the
> > > > first query.
> > > >
> > > > Dataset 2:
> > > > SELECT TOP 1 SALES_AMOUNT
> > > > FROM CUSTOMER, EMPLOYEE, SALES
> > > > WHERE CUSTOMER.CUST_ID = '"&CUST_ID&"'
> > > > AND EMPLOYEE.EMP_ID = '"&EMP_ID&"'
> > > > AND SALES.ID = '"&SALES_ID&"'
> > > >
> > > > I have a number of reports, migrating from ASP to Reporting Services,
> and
> > > > was wondering if this is possible with RS.
> > > >
> > > > Use the first dataset as is and second dataset. I was thinking about
> > > > referencing it like this:
> > > >
> > > > =IIF((Fields!Cust_ID.Value & "Cust_ID").Value AND (Fields!Emp_ID.Value
> &
> > > > "Emp_ID").Value AND (Fields!ID.Value & "Sales_ID").Value),
> > > > Fields!Sales_Amount.Value, "N/A")
> > > >
> > > > Intended output:
> > > > CustID EmpID SalesID SalesAmount
> > > > 1 2 3232 $345.00
> > > > 2 2 3643 $223.00
> > > > 3 6 8772 $1234.54
> > > > 4 6 N/A
> > > >
> > > >
> > > > So Dataset 2 will check the fields and see if there's a match, if it
> does
> > > > then output Sales_Amount base on the output fields. Please advise on
> what
> > > to
> > > > do or suggestions.
> > > >
> > > > Thanks.
> > >
> > >
> > >
>
>

Referencing composite Primary KEYS

Hi,
i want to make a reference from a table on itself.
The table has a composite Primary Key. But I just want to refernce the TEstCaseID.
So whats wrong? Can anyone help me?

CREATE TABLE dbo.TestCase (
Project_projectID VARCHAR(20) NOT NULL references Project,
testCaseID VARCHAR(50) NOT NULL,
PRIMARY KEY(Project_projectID, testCaseID),
FatherID VARCHAR(50) references TestCase(testCaseID)

)

THanx CreanFor logical reasons, you can only reference unique values. Otherwise, how would SQL Server (or any database engine) know which of many records you were referencing?|||:D Already found out. But thanx a lot