reg exporting crystal reports to Excel format

Hi Babu,

I have created a report using crystal reports and the template has a field that is formatted with (PrintTime) and another field(File Creation Time). When i export this report to .doc file( using VC++ code) I can see the these fields properly. But when i export to .xls file I get ###### in these fields instead of the actual value. These "######" appear only for the time fields and not for the date fields. Also when the cell is formatted to hold a general value , the cell shows a long number.

Can you help as to what would have caused this problem and how i can rectify this?The "problem" you have is not really a problem, it's how Excel was designed to work.

Excel shows "####" when the column isn't wide enough to show the entire contents.

Excel treats dates as long numbers (probably the number of seconds from a certain date) and it's up to the user to make sure the Cell is formatted to display properly.

As for how to fix it, I'm not sure. You can try Crystal's website to see if you can find an article on formatting Excel Cells and Columns.

http://support.businessobjects.com/search/advsearch.asp|||I have increased the cell size and formatted it for showing time.
The ##### still exists. When i change the cell format to general(a long number) is displayed but in the time format these hashes appear|||You may may not be increasing the width of the column enough. In Excel, assuming that you have the date in cell A1, if you double-click the line that separates the column headers "A" and "B", Excel will automatically resize column "A" to fit the width of the longest Cell in the column. I'm not sure if it will help you in Crystal Reports, but in VB I can achieve this with the following line of code:

xlApp.ActiveSheet.Columns("A:A").EntireColumn.AutoFit|||Try printing Printtime and FileCreationDateTIme in different lines. Maybe it's a data issue. I hope there are no special characters in data. Also try displaying Just YYYY from both fields and slowly slowly try adding mm and dd etc..

THanks

Refreshing the field list

Hi all,
running RS with the serve on sp2 - have a question about refreshing the
field list in the data tab of the designer.
I have copied (and changed slightly) a stored procedure that one of our
reports runs on. All of the field names are the same, that didn't change...I
can run the SP in the designer, and was attempting to change the SP the
report uses to the newer version. I can get no fields in the field list, and
if I try to preview the report I have boatloads of errors (one for every
field, because it doesn't know where the data is).
If you create a new sp for a report to run on, but the new sp has all of the
same field names, etc. , shouldn't you be able to 'plug and play') - the
field names being returned when I run the query in the RS data tab are the
same as the fieldd names from the original query.
Cheers,Execute the SP from the Data source area and the fields will be refreshed..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Myles" <Myles@.discussions.microsoft.com> wrote in message
news:CA47B9E1-8F90-41DA-91B5-34D5F27641C2@.microsoft.com...
> Hi all,
> running RS with the serve on sp2 - have a question about refreshing the
> field list in the data tab of the designer.
> I have copied (and changed slightly) a stored procedure that one of our
> reports runs on. All of the field names are the same, that didn't
> change...I
> can run the SP in the designer, and was attempting to change the SP the
> report uses to the newer version. I can get no fields in the field list,
> and
> if I try to preview the report I have boatloads of errors (one for every
> field, because it doesn't know where the data is).
> If you create a new sp for a report to run on, but the new sp has all of
> the
> same field names, etc. , shouldn't you be able to 'plug and play') - the
> field names being returned when I run the query in the RS data tab are the
> same as the fieldd names from the original query.
>
> Cheers,
>
>

Refreshing links in frontend

Hi everyone,
Could someone explain to me how to refresh the links to my sql server?
Each time i modify or create a field in a table, the modifications are
not implented in the front-end. How can i resolve this.
Greetings,
StefanieAre you selecting from views with SELECT *? In this case, check out
sp_refreshview in the Books Online.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Stefanie Pindew" <spindew@.yahoo.com> wrote in message
news:ehJkMpZXDHA.1004@.TK2MSFTNGP12.phx.gbl...
> Hi everyone,
> Could someone explain to me how to refresh the links to my sql server?
> Each time i modify or create a field in a table, the modifications are
> not implented in the front-end. How can i resolve this.
> Greetings,
> Stefanie
>|||Or are you referring to an Access front end? In that case,
you can use the Linked Table Manager or do it
programmatically with the refresh method of the tabledefs
collection.
--Sue
On Fri, 08 Aug 2003 12:46:17 +0200, Stefanie Pindew
<spindew@.yahoo.com> wrote:
>Hi everyone,
>Could someone explain to me how to refresh the links to my sql server?
>Each time i modify or create a field in a table, the modifications are
>not implented in the front-end. How can i resolve this.
>Greetings,
>Stefanie|||>--Original Message--
>Or are you referring to an Access front end? In that case,
>you can use the Linked Table Manager or do it
>programmatically with the refresh method of the tabledefs
>collection.
>--Sue
>On Fri, 08 Aug 2003 12:46:17 +0200, Stefanie Pindew
><spindew@.yahoo.com> wrote:
>>Hi everyone,
>>Could someone explain to me how to refresh the links to
my sql server?
>>Each time i modify or create a field in a table, the
modifications are
>>not implented in the front-end. How can i resolve this.
>>Greetings,
>>Stefanie
>.
>Hi,
I want it do it programmatically in the access front-end
and in the access adp front-end|||Stefanie,
I've just checked the following code and it works:
Dim tdLoop As TableDef
For Each tdLoop In CurrentDb.TableDefs
tdLoop.RefreshLink
Next
However, I can remember having problems when using the
.RefreshLink method... Give it a try though.
Regards,
Danny
"Stefanie Pindew" <spindew@.lollo.com> wrote in message news:02f201c35fee$71d670c0$a001280a@.phx.gbl...
> >--Original Message--
> >Or are you referring to an Access front end? In that case,
> >you can use the Linked Table Manager or do it
> >programmatically with the refresh method of the tabledefs
> >collection.
> >
> >--Sue
> >
> >On Fri, 08 Aug 2003 12:46:17 +0200, Stefanie Pindew
> ><spindew@.yahoo.com> wrote:
> >
> >>Hi everyone,
> >>
> >>Could someone explain to me how to refresh the links to
> my sql server?
> >>Each time i modify or create a field in a table, the
> modifications are
> >>not implented in the front-end. How can i resolve this.
> >>
> >>Greetings,
> >>
> >>Stefanie
> >
> >.
> >Hi,
> I want it do it programmatically in the access front-end
> and in the access adp front-end|||>--Original Message--
>Stefanie,
>I've just checked the following code and it works:
>Dim tdLoop As TableDef
>For Each tdLoop In CurrentDb.TableDefs
> tdLoop.RefreshLink
>Next
>However, I can remember having problems when using the
>..RefreshLink method... Give it a try though.
>Regards,
>Danny
>
>"Stefanie Pindew" <spindew@.lollo.com> wrote in message
news:02f201c35fee$71d670c0$a001280a@.phx.gbl...
>> >--Original Message--
>> >Or are you referring to an Access front end? In that
case,
>> >you can use the Linked Table Manager or do it
>> >programmatically with the refresh method of the
tabledefs
>> >collection.
>> >
>> >--Sue
>> >
>> >On Fri, 08 Aug 2003 12:46:17 +0200, Stefanie Pindew
>> ><spindew@.yahoo.com> wrote:
>> >
>> >>Hi everyone,
>> >>
>> >>Could someone explain to me how to refresh the links
to
>> my sql server?
>> >>Each time i modify or create a field in a table, the
>> modifications are
>> >>not implented in the front-end. How can i resolve
this.
>> >>
>> >>Greetings,
>> >>
>> >>Stefanie
>> >
>> >.
>> >Hi,
>> I want it do it programmatically in the access front-end
>> and in the access adp front-end
>
>.
>He thanks man, it works for me too|||Stefanie,
There are some issues with using .RefreshLink with Access97,
but I can't remember what they are. In case you have any
problems, I ended up implementing a solution that deleted the
linked tables from within Access and then re-linked them.
Both tasks can be accomplished using the DoCmd object:
DoCmd.DeleteObject
DoCmd.TransferDatabase
Email me if you want some sample code.
Regards,
Danny
"Stefanie Pindew" <spindew@.lollo.com> wrote in message news:066501c35ffe$995740b0$a301280a@.phx.gbl...
> >--Original Message--
> >Stefanie,
> >
> >I've just checked the following code and it works:
> >
> >Dim tdLoop As TableDef
> >For Each tdLoop In CurrentDb.TableDefs
> > tdLoop.RefreshLink
> >Next
> >
> >However, I can remember having problems when using the
> >..RefreshLink method... Give it a try though.
> >
> >Regards,
> >Danny
> >
> >
> >"Stefanie Pindew" <spindew@.lollo.com> wrote in message
> news:02f201c35fee$71d670c0$a001280a@.phx.gbl...
> >>
> >> >--Original Message--
> >> >Or are you referring to an Access front end? In that
> case,
> >> >you can use the Linked Table Manager or do it
> >> >programmatically with the refresh method of the
> tabledefs
> >> >collection.
> >> >
> >> >--Sue
> >> >
> >> >On Fri, 08 Aug 2003 12:46:17 +0200, Stefanie Pindew
> >> ><spindew@.yahoo.com> wrote:
> >> >
> >> >>Hi everyone,
> >> >>
> >> >>Could someone explain to me how to refresh the links
> to
> >> my sql server?
> >> >>Each time i modify or create a field in a table, the
> >> modifications are
> >> >>not implented in the front-end. How can i resolve
> this.
> >> >>
> >> >>Greetings,
> >> >>
> >> >>Stefanie
> >> >
> >> >.
> >> >Hi,
> >>
> >> I want it do it programmatically in the access front-end
> >> and in the access adp front-end
> >
> >
> >.
> >He thanks man, it works for me too

Refresh Fields list

Ok, what is the secret key combination to refresh the fields list? Please? :-)
I am modifying a drilldown report I created, and have added a field returned from my stored procedure, and would now like to include this field on my report. I am clicking around and I cannot seem to trigger it to refresh the fields list, and I am receiving a build error as follows:
"The value expression for the textbox â'Regionâ' refers to the field â'REGION_ORG_CODEâ'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
This field is within the current data set scope, or at least it should be. How can I include this field on my report? CTRL-ALT-F?DOH! Just noticed there is a button on the toolbar in the Data tab for this. It would be nice if this were a little more obvious.
Also, I would suggest that this feature be added to the Right click context menu in the Fields list itself.
Thanks.
"Joe" wrote:
> Ok, what is the secret key combination to refresh the fields list? Please? :-)
> I am modifying a drilldown report I created, and have added a field returned from my stored procedure, and would now like to include this field on my report. I am clicking around and I cannot seem to trigger it to refresh the fields list, and I am receiving a build error as follows:
> "The value expression for the textbox â'Regionâ' refers to the field â'REGION_ORG_CODEâ'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
> This field is within the current data set scope, or at least it should be. How can I include this field on my report? CTRL-ALT-F?

Reformatting numeric value to zero-filled string

I need to take a numeric field that I need converted to a zero-filled text
value, for example:
123456 convert to 000123456
I've looked at the CONVERT and CASE and haven't come up with a combination
that will perform this function.
Thanks!
Dougtry this as an example:
SELECT ISNULL(RIGHT('000' + CONVERT(VARCHAR, '123456'), 10), '')
yosh
"Leighton.d" <Leightond@.discussions.microsoft.com> wrote in message
news:464BC261-BE2F-4799-B179-B13C0B1DFAB6@.microsoft.com...
>I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||select replace(str( 10,10),' ','0')
"Leighton.d" <Leightond@.discussions.microsoft.com> wrote in message
news:464BC261-BE2F-4799-B179-B13C0B1DFAB6@.microsoft.com...
>I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||Try,
declare @.num int
set @.num = 123456
select right(replicate('0', 9) + ltrim(@.num), 9)
go
"Leighton.d" wrote:

> I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||Wouldn't it be nice if the Access FORMAT$ function were available.
Thanks for all of the input...
Doug|||As a good practice, try the formatting in the client side.
AMB
"Leighton.d" wrote:

> Wouldn't it be nice if the Access FORMAT$ function were available.
> Thanks for all of the input...
> Doug

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 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 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 field by name in code

I have a field (Fields!myfield) that I would like to pass in the name as two parts(Code.GetField("my","field") and dynamically return either a reference to myfield or the value itself.

I'm getting Reference to a shared field requires a shared reference error when I try to return Fields!Myfield.Value from the function.

I would like the ability to pass in the name of a field to a function and return the actual field name (or value of the field) to the report. Any ideas?

That was easy. Fields("my" & code.getname("test")).Value seems to work well.

Referencing another table in MS_SQL?

Hello everyone,
We would like to have the user_ID field in the security table reference the
email field in the Cusomer table. Basically, I would like instant updates
of security login information when the email is changed/added in the
customer table. I would prefer not to have periodic updates/sync. The
preferred method would reference the email field rather than update from the
email field.
Example of use: When an end user modifies their personal info (email field
in the customer table), the login will instantly be set/updated in the
user_ID field in the security table.
Any help is appreciated. Thanks."Kevin" <kevin_aoki@.NOSPAMhotmail.com123> wrote in message
news:9Oyhd.80$2N2.46980@.news.uswest.net...
> Hello everyone,
> We would like to have the user_ID field in the security table reference
the
> email field in the Cusomer table. Basically, I would like instant updates
> of security login information when the email is changed/added in the
> customer table. I would prefer not to have periodic updates/sync. The
> preferred method would reference the email field rather than update from
the
> email field.
> Example of use: When an end user modifies their personal info (email field
> in the customer table), the login will instantly be set/updated in the
> user_ID field in the security table.
> Any help is appreciated. Thanks.
Are the columns compatible datatypes? If so make the user_id column in the
security table a foreign key referencing the email column in the customer
table WITH UPDATE CASCADE. In order for this to work rhw email column must
have a UNIQUE or PRIMARY KEY CONSTRAINT.
http://snodland.blogspot.com
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.786 / Virus Database: 532 - Release Date: 30/10/2004|||"Kevin" <kevin_aoki@.NOSPAMhotmail.com123> wrote in message
news:9Oyhd.80$2N2.46980@.news.uswest.net...
> Hello everyone,
> We would like to have the user_ID field in the security table reference
> the
> email field in the Cusomer table. Basically, I would like instant updates
> of security login information when the email is changed/added in the
> customer table. I would prefer not to have periodic updates/sync. The
> preferred method would reference the email field rather than update from
> the
> email field.
> Example of use: When an end user modifies their personal info (email field
> in the customer table), the login will instantly be set/updated in the
> user_ID field in the security table.
> Any help is appreciated. Thanks.
Two words: UPDATE TRIGGER

Referencing another table in MS_SQL?

Hello everyone,
We would like to have the user_ID field in the security table reference the
email field in the Cusomer table. Basically, I would like instant updates
of security login information when the email is changed/added in the
customer table. I would prefer not to have periodic updates/sync. The
preferred method would reference the email field rather than update from the
email field.
Example of use: When an end user modifies their personal info (email field
in the customer table), the login will instantly be set/updated in the
user_ID field in the security table.
Any help is appreciated. Thanks."Kevin" <kevin_aoki@.NOSPAMhotmail.com123> wrote in message
news:9Oyhd.80$2N2.46980@.news.uswest.net...
> Hello everyone,
> We would like to have the user_ID field in the security table reference
the
> email field in the Cusomer table. Basically, I would like instant updates
> of security login information when the email is changed/added in the
> customer table. I would prefer not to have periodic updates/sync. The
> preferred method would reference the email field rather than update from
the
> email field.
> Example of use: When an end user modifies their personal info (email field
> in the customer table), the login will instantly be set/updated in the
> user_ID field in the security table.
> Any help is appreciated. Thanks.
Are the columns compatible datatypes? If so make the user_id column in the
security table a foreign key referencing the email column in the customer
table WITH UPDATE CASCADE. In order for this to work rhw email column must
have a UNIQUE or PRIMARY KEY CONSTRAINT.
http://snodland.blogspot.com
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.786 / Virus Database: 532 - Release Date: 30/10/2004|||"Kevin" <kevin_aoki@.NOSPAMhotmail.com123> wrote in message
news:9Oyhd.80$2N2.46980@.news.uswest.net...
> Hello everyone,
> We would like to have the user_ID field in the security table reference
> the
> email field in the Cusomer table. Basically, I would like instant updates
> of security login information when the email is changed/added in the
> customer table. I would prefer not to have periodic updates/sync. The
> preferred method would reference the email field rather than update from
> the
> email field.
> Example of use: When an end user modifies their personal info (email field
> in the customer table), the login will instantly be set/updated in the
> user_ID field in the security table.
> Any help is appreciated. Thanks.
Two words: UPDATE TRIGGER

Referencing another table in MS_SQL?

Hello everyone,
We would like to have the user_ID field in the security table reference the
email field in the Cusomer table. Basically, I would like instant updates
of security login information when the email is changed/added in the
customer table. I would prefer not to have periodic updates/sync. The
preferred method would reference the email field rather than update from the
email field.
Example of use: When an end user modifies their personal info (email field
in the customer table), the login will instantly be set/updated in the
user_ID field in the security table.
Any help is appreciated. Thanks.
"Kevin" <kevin_aoki@.NOSPAMhotmail.com123> wrote in message
news:9Oyhd.80$2N2.46980@.news.uswest.net...
> Hello everyone,
> We would like to have the user_ID field in the security table reference
the
> email field in the Cusomer table. Basically, I would like instant updates
> of security login information when the email is changed/added in the
> customer table. I would prefer not to have periodic updates/sync. The
> preferred method would reference the email field rather than update from
the
> email field.
> Example of use: When an end user modifies their personal info (email field
> in the customer table), the login will instantly be set/updated in the
> user_ID field in the security table.
> Any help is appreciated. Thanks.
Are the columns compatible datatypes? If so make the user_id column in the
security table a foreign key referencing the email column in the customer
table WITH UPDATE CASCADE. In order for this to work rhw email column must
have a UNIQUE or PRIMARY KEY CONSTRAINT.
http://snodland.blogspot.com
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.786 / Virus Database: 532 - Release Date: 30/10/2004
|||"Kevin" <kevin_aoki@.NOSPAMhotmail.com123> wrote in message
news:9Oyhd.80$2N2.46980@.news.uswest.net...
> Hello everyone,
> We would like to have the user_ID field in the security table reference
> the
> email field in the Cusomer table. Basically, I would like instant updates
> of security login information when the email is changed/added in the
> customer table. I would prefer not to have periodic updates/sync. The
> preferred method would reference the email field rather than update from
> the
> email field.
> Example of use: When an end user modifies their personal info (email field
> in the customer table), the login will instantly be set/updated in the
> user_ID field in the security table.
> Any help is appreciated. Thanks.
Two words: UPDATE TRIGGER

Referencing a field in another table as part of a formula in a computed column

Hi guys, I am new to all of this stuff so please bare with me. I will try to explain this as simply as I can. I have 2 tables.. Table1 and Table2 they are linked using both the STOCKCODE and SUPPLIER fields. In Table 1 is the STOCKCODE, LENGTH AND SUPPLIER fields (there are others but these are the 3 that I need to use) In Table2 is STOCKCODE, SUPPLIER and SUPPLER_MASS. What I want to do is put a calculated column into Table2. The formula I need is fairly simple it is: 1/(LENGTH*SUPPLIER_MASS)

Ok so here is what I would like:

1/(SUPPLIER_MASS*(SELECT Table1.LENGTH FROM Table1 WHERE Table1.STOCKCODE=Table2.STOCKCODE AND Table1.SUPPLIER=Table2.SUPPLIER)

From what I understand I can't have a query in a calculated column's "formula" field?

So how would I do this? I don't want to run a query because LENGTH or SUPPLIER_MASS could change on a day to day basis meaning I will have to run the query everything one of these changes. The benefit of having the calculated column is that it will update as soon as LENGTH or SUPPLIER_MASS changes.

Is there any way I can do this?

Thanks guys (and girls)You can have insert, update trigger which will update calculated column every time you update a record or insert a new one.

Thank you.

Referencing a calculated field in a query

I would have a question, if it is possible to reference a calculated field in T-SQL query. The following expression does not work in SQLExpress 2005

SELECT Qty, UnitPrice, Tax, Qty*UnitPrice as Expr1, Expr1*(1.0 + Tax) AS Expr2

This problem has occurred after upsizing to SQLExpress from Access XP/Jet Engine. Since Access does not have any issue with the expression above, the SQLExpress does not even accept it.

The only way how to avoid the issue in the SQLExpress seems probably to be

- Duplicate some calculations (i.e. expand every expression with duplicating some mathematic operations with some performance loss)

- Utilize computed columns if possible (no idea on performance impact here)

Am I right or is there any other way how to reference a calculated field?

Any suggestion is greatly welcomed! Thanks in advance.The problem is not really with the calculated field. The problem is that column aliases are not assigned until the SELECT statement's results are processed, so you cannot refer to them within the statement.
Two workarounds are to repeat the formula within the result set each time it is needed, or to calculate expression 1 in a SELECT subquery and then refer to it in expression 2 in the outer query.
Of the two, I prefer the first option most of the time.

Reference an alias field name in an SQL Statement

Is there a way to Reference an alias field name in an SQL Statement?
Example:
Select
1 + 1 AS F1,
F1 + 1 AS F2Hello Kent,
Darren has given a good answer to your problem. I use derived tables or
sub queries to handle the kind of functionality you are looking for.
Your example shows two columns one contains a table field with a
calculation. The second has another calculation based on the first
field. Using derived tables is a very effect way to handle complex set
operations and calculations. Check out the script below.
CREATE TABLE [MyTable] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[F1] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO [sandpit].[dbo].[MyTable]( [F1])
VALUES( 100)
GO
SELECT T.F1, T.F1+1 AS F2 FROM (SELECT ID, F1+1 AS F1 FROM MyTable) AS
T
GO
DROP TABLE MyTable
GO
I hope this makes it a bit clearer. I might help if you give the group
a bit more background on what you are trying to do.
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
Kent Prokopy wrote:
> Is there a way to Reference an alias field name in an SQL Statement?
> Example:
> Select
> 1 + 1 AS F1,
> F1 + 1 AS F2|||Hello Kent,
Darren has given a good answer to your problem. I use derived tables or
sub queries to handle the kind of functionality you are looking for.
Your example shows two columns one contains a table field with a
calculation. The second has another calculation based on the first
field. Using derived tables is a very effect way to handle complex set
operations and calculations. Check out the script below.
CREATE TABLE [MyTable] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[F1] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO [sandpit].[dbo].[MyTable]( [F1])
VALUES( 100)
GO
SELECT T.F1, T.F1+1 AS F2 FROM (SELECT ID, F1+1 AS F1 FROM MyTable) AS
T
GO
DROP TABLE MyTable
GO
I hope this makes it a bit clearer. I might help if you give the group
a bit more background on what you are trying to do.
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
Kent Prokopy wrote:
> Is there a way to Reference an alias field name in an SQL Statement?
> Example:
> Select
> 1 + 1 AS F1,
> F1 + 1 AS F2

Reference alias field name

Is there a way to Reference an alias field name in an SQL Statement?
Example:
Select
1 + 1 AS F1,
F1 + 1 AS F2Not sure if this can do what you wish, but create the subquery in your FROM
clause.
EXAMPLE:
Using the Northwind database
SELECT quant.Quantity
FROM (SELECT Quantity + 1 AS [Quantity]
FROM [Order Details]) AS quant
The subquery is aliased using quant and will return the quantity field +1.
This could also be written as
SELECT *
FROM (SELECT Quantity + 1 AS [Quantity]
FROM [Order Details]) AS quant
as the only column created in the sub query is the quantity +1, but just to
give you direction for your query.
Good Luck and hope this helped.
"Kent Prokopy" wrote:

> Is there a way to Reference an alias field name in an SQL Statement?
> Example:
> Select
> 1 + 1 AS F1,
> F1 + 1 AS F2|||select sub.F1 as F1
,sub.F1 + 1 as F2
from (
select 1 + 1 as F1
) sub
ML
http://milambda.blogspot.com/|||>> Is there a way to Reference an alias field name in an SQL Statement? <<
You are still thinking of a procedural "left-to-right" programming
language based on files and not tables.
Here is how a SELECT works in SQL ... at least in theory. Real
products will optimize things, but the code has to produce the same
results.
a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there. The <table expression> AS <correlation name> option allows you
give a name to this working table which you then have to use for the
rest of the containing query.
b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (i.e. reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.
c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the
new grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items. The original table no longer exists.
d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.
e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The AS
operator can also give names to expressions in the SELECT list. These
new names come into existence all at once, but after the WHERE clause,
GROUP BY clause and HAVING clause have been executed; you cannot use
them in the SELECT list or the WHERE clause for that reason.
If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).
f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.
g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the SELECT
clause list, and the sorting is done there. The ORDER BY clause cannot
have expression in it, or references to other columns because the
result set has been converted into a sequential file structure and that
is what is being sorted.
As you can see, things happen "all at once" in SQL, not "from left to
right" as they would in a sequential file/procedural language model. In
those languages, these two statements produce different results:
READ (a, b, c) FROM File_X;
READ (c, a, b) FROM File_X;
while these two statements return the same data:
SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;
Think about what a mess this statement is in the SQL model.
SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;
That is why such nonsense is illegal syntax.

Refer to a field in a header

Ii,
I have a text box in the body of my report containing:
=Code.setMonth(ReportItems!textbox13.Value) but i want this to appear in
another textbox in my header. I have you read you can't refer directly to a
field. This is what I have in my Header textbox:
=ReportItems("textbox14").Value. Nothing shows up when i run the report. How
can I get this to appear?
Thanks in advance,
RhondaTo update this, it is actually a custom function I am calling, if I place th
e
call in the header textbox nothing appears.
"Rhonda" wrote:

> Ii,
> I have a text box in the body of my report containing:
> =Code.setMonth(ReportItems!textbox13.Value) but i want this to appear in
> another textbox in my header. I have you read you can't refer directly to
a
> field. This is what I have in my Header textbox:
> =ReportItems("textbox14").Value. Nothing shows up when i run the report. H
ow
> can I get this to appear?
> Thanks in advance,
> Rhonda

refence a field by column position

Is it possible to reference a field from its position in the column as opposed to its name?

=Fields!Ownership.Value maybe something like =Fields(2).value this doesn't work however, but is there a way.

Not sure why you'd need to do this...What are you trying to accomplish? Maybe there is a workaround. Something like, you want to use the same table with 2 different queries that produce different column names?