Forcing types in Excel connection?

I am using Crystal Reports 9 to build a report that involves connections to both an Excel spreadsheet and an Oracle database. The problem is, the Oracle database contains an ID field stored as text (although the data is currently strictly numeric). The same ID field appears in the excel spreadsheet. I am trying to join the spreadsheet to the database. My problem is that, since ID field in the spreadsheet contains only numeric data, Crystal Reports imports the field as a numeric field then expects it match to the Oracle database's text ID field! Obviously, this creates a type error and the report fails.

My question is: is there any way to force Crystal Reports to regard a field in Excel as text rather than numeric data? Changing the cell attribute to text in Excel didn't seem to work; Crystal still saw numbers in the field and assumed the data was numeric.

This is an urgent matter so any help at all would be appreciated. Thanks!When joining the tables convert oracles table's id to numericsql

Forcing section break to odd pages

Hi - I'm new to this forum and fairly new to Crystal. Using 9.0. Because we print two-sided, I need to force a section break to start on next available odd numbered page so that I can treat the printed sections as separate documents (e.g. I don't want 1st page of new section to print on back of preceeding section). These sections are anywhere from 10 - 18 pages in length, and it is a huge report - over 11,000 pages. I'm pretty sure that there is a way to do this but I don't have time to fumble around with it. I already have 10 hours in this one report!

Thanks in advance
RobinI wrote a report recently that needed something simular, prehaps you can try this. The report prints a list of people at a specific place. Sometimes the place (group one) has lots of people and stems onto two pages and other times it fits on one page. As I'm duplex printing, I need to check whether the pages are even or odd and thus to include a page break or not.

In the section expect, I added a new group footer. In both group footer 1a and 1b I checked the 'new page after'. Then in group footer 1b, I added

Right (ToText(PageNumber),1) in ["0","2","4","6","8"]

which causes my report only to add a blank page after group one has finished if the current page number ends in any of the values above.

Hope it helps.

For XML AUTO

Hi
I'm trying to produce XML datasets to be used in Crystal Reports, but I
cannot seem to view the XML output when produced.
It just comes back with errors, when viewed in IE.
The error is:
************************************************** **************************
********
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and
then click the Refresh button, or try again later.

Only one top level element is allowed in an XML document. Error processing
resource 'file:///C:/Documents and Settings/hsrp...
<Order_ Order_Date="1998-10-07T00:00:00" ConvertedDate="19981007"
ConvertedDate1="19981007"/><Order_ Order_Date="19...
************************************************** **************************
********
Any ideas why this is happening?
Kind Regards
Rikesh
(SQL2K-SP3/W2K-SP4)
FOR XML produces XML fragments - not documents. You might need to add a root
element to the results in order to make it well-formed.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"rikesh" <rikesh_patel@.website.com> wrote in message
news:ejMHMbMZEHA.3752@.TK2MSFTNGP12.phx.gbl...
Hi
I'm trying to produce XML datasets to be used in Crystal Reports, but I
cannot seem to view the XML output when produced.
It just comes back with errors, when viewed in IE.
The error is:
************************************************** **************************
********
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and
then click the Refresh button, or try again later.

Only one top level element is allowed in an XML document. Error processing
resource 'file:///C:/Documents and Settings/hsrp...
<Order_ Order_Date="1998-10-07T00:00:00" ConvertedDate="19981007"
ConvertedDate1="19981007"/><Order_ Order_Date="19...
************************************************** **************************
********
Any ideas why this is happening?
Kind Regards
Rikesh
(SQL2K-SP3/W2K-SP4)
|||Any ideas, how to do that, someone suggested FOR XML AUTO, ELEMENTS, but
that didn't work?
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:ua2LxDNZEHA.1248@.TK2MSFTNGP11.phx.gbl...
> FOR XML produces XML fragments - not documents. You might need to add a
root
> element to the results in order to make it well-formed.
> Cheers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:ejMHMbMZEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'm trying to produce XML datasets to be used in Crystal Reports, but I
> cannot seem to view the XML output when produced.
> It just comes back with errors, when viewed in IE.
> The error is:
>
************************************************** **************************
> ********
> The XML page cannot be displayed
> Cannot view XML input using XSL style sheet. Please correct the error
and
> then click the Refresh button, or try again later.
>
> ----
--
> --
> Only one top level element is allowed in an XML document. Error
processing
> resource 'file:///C:/Documents and Settings/hsrp...
> <Order_ Order_Date="1998-10-07T00:00:00" ConvertedDate="19981007"
> ConvertedDate1="19981007"/><Order_ Order_Date="19...
>
>
************************************************** **************************
> ********
> Any ideas why this is happening?
>
> --
> Kind Regards
> Rikesh
> (SQL2K-SP3/W2K-SP4)
>
>
|||You can't do it in Transact-SQL in SQL Server 2000 (Yukon includes a ROOT
directive). You need to do it on the client. The ADO and ADO.NET classes
shipped with SQLXML have a RootTag property you can use, or you could use a
template or schema. Have a look at the documentation shipped with SQLXML
3.0.
Chers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"rikesh" <rikesh_patel@.website.com> wrote in message
news:%23BdETGNZEHA.3476@.tk2msftngp13.phx.gbl...
Any ideas, how to do that, someone suggested FOR XML AUTO, ELEMENTS, but
that didn't work?
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:ua2LxDNZEHA.1248@.TK2MSFTNGP11.phx.gbl...
> FOR XML produces XML fragments - not documents. You might need to add a
root
> element to the results in order to make it well-formed.
> Cheers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:ejMHMbMZEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'm trying to produce XML datasets to be used in Crystal Reports, but I
> cannot seem to view the XML output when produced.
> It just comes back with errors, when viewed in IE.
> The error is:
>
************************************************** **************************
> ********
> The XML page cannot be displayed
> Cannot view XML input using XSL style sheet. Please correct the error
and
> then click the Refresh button, or try again later.
>
> ----
--
> --
> Only one top level element is allowed in an XML document. Error
processing
> resource 'file:///C:/Documents and Settings/hsrp...
> <Order_ Order_Date="1998-10-07T00:00:00" ConvertedDate="19981007"
> ConvertedDate1="19981007"/><Order_ Order_Date="19...
>
>
************************************************** **************************
> ********
> Any ideas why this is happening?
>
> --
> Kind Regards
> Rikesh
> (SQL2K-SP3/W2K-SP4)
>
>
|||Is this a separate component of SQL!!!
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:uR9QRPQZEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You can't do it in Transact-SQL in SQL Server 2000 (Yukon includes a ROOT
> directive). You need to do it on the client. The ADO and ADO.NET classes
> shipped with SQLXML have a RootTag property you can use, or you could use
a
> template or schema. Have a look at the documentation shipped with SQLXML
> 3.0.
> Chers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:%23BdETGNZEHA.3476@.tk2msftngp13.phx.gbl...
> Any ideas, how to do that, someone suggested FOR XML AUTO, ELEMENTS, but
> that didn't work?
>
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:ua2LxDNZEHA.1248@.TK2MSFTNGP11.phx.gbl...
> root
>
************************************************** **************************
> and
> ----
> --
> processing
>
************************************************** **************************
>
>
|||http://www.microsoft.com/downloads/d...displaylang=en
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"rikesh" <rikesh_patel@.website.com> wrote in message
news:OtBYZUQZEHA.3752@.TK2MSFTNGP12.phx.gbl...
Is this a separate component of SQL!!!
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:uR9QRPQZEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You can't do it in Transact-SQL in SQL Server 2000 (Yukon includes a ROOT
> directive). You need to do it on the client. The ADO and ADO.NET classes
> shipped with SQLXML have a RootTag property you can use, or you could use
a
> template or schema. Have a look at the documentation shipped with SQLXML
> 3.0.
> Chers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:%23BdETGNZEHA.3476@.tk2msftngp13.phx.gbl...
> Any ideas, how to do that, someone suggested FOR XML AUTO, ELEMENTS, but
> that didn't work?
>
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:ua2LxDNZEHA.1248@.TK2MSFTNGP11.phx.gbl...
> root
>
************************************************** **************************
> and
> ----
> --
> processing
>
************************************************** **************************
>
>
|||The root property is available in the OLEDB provider that ships with SQL
Server 2000. So it is not part of the server-side software but the client
component.
Best regards
Michael
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:uPWrT$QZEHA.136@.TK2MSFTNGP11.phx.gbl...
> http://www.microsoft.com/downloads/d...displaylang=en
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:OtBYZUQZEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Is this a separate component of SQL!!!
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:uR9QRPQZEHA.1152@.TK2MSFTNGP09.phx.gbl...
> a
> ************************************************** **************************
> ************************************************** **************************
>
>
|||Thanks chaps, I'll give it a try...
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23Yv8W1XZEHA.2840@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> The root property is available in the OLEDB provider that ships with SQL
> Server 2000. So it is not part of the server-side software but the client
> component.
> Best regards
> Michael
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:uPWrT$QZEHA.136@.TK2MSFTNGP11.phx.gbl...
http://www.microsoft.com/downloads/d...displaylang=en[vbcol=seagreen]
ROOT[vbcol=seagreen]
classes[vbcol=seagreen]
use[vbcol=seagreen]
SQLXML[vbcol=seagreen]
but[vbcol=seagreen]
a[vbcol=seagreen]
I[vbcol=seagreen]
************************************************** **************************[vbcol=seagreen]
error[vbcol=seagreen]
-[vbcol=seagreen]
************************************************** **************************
>

For Stored Procs Gurus

Hi,
The following questions arose during the creation of a Crystal Report Graph.
The amount of data for the graph was so large that making any sense of it by
providing a legend for the colored lines was ridiculous. So we have come up
with a paging system of graphs based on regionIDs (foreign key). Because we
want to add the paging functionality, we basically need to do two SPs one to
gather the data for the Regions and one for the main report that uses the
results of the Regions SP.
Some of these questions may seem trivial, but I was looking for solutions
from those that are better than I at optimizing stored procs.
1. Is there a problem with the passing of 2000 characters in the different
parameters? What if we need to pass more? What are the limits when calling
Stored Procs? Is passing a SQL string built at the program level more
efficient than collecting and sending all the parameters to the SP?
2. Is there a better way to populate the temporary table than calling a
second stored proc for the insert?
3. Can we use an "in memory" table instead of the Temporary table (look for
the new TABLE DATA TYPE in SQL 2000)
4. Is there a better way to "group" the records other than with a cursor? If
not, can anything be done to the cursor to perform better?
5. In the stored proc... is there a better way to fetch the data by using a
VIEW maybe?
I hope you can understand what I have written, if not, let me know and I
will hopefully reword it in a way for you to be helpful.
Thanks,
Michael Murphy
Please do not post the same question independently to multiple newsgroups.
The .programming thread should be used for further communcation.
"Michael D Murphy" <mdmurphy@.scs-techresources.com> wrote in message
news:Oaul5sGjFHA.3540@.TK2MSFTNGP14.phx.gbl...
> Hi,
> The following questions arose during the creation of a Crystal Report
Graph.
> The amount of data for the graph was so large that making any sense of it
by
> providing a legend for the colored lines was ridiculous. So we have come
up
> with a paging system of graphs based on regionIDs (foreign key). Because
we
> want to add the paging functionality, we basically need to do two SPs one
to
> gather the data for the Regions and one for the main report that uses the
> results of the Regions SP.
> Some of these questions may seem trivial, but I was looking for solutions
> from those that are better than I at optimizing stored procs.
> 1. Is there a problem with the passing of 2000 characters in the different
> parameters? What if we need to pass more? What are the limits when calling
> Stored Procs? Is passing a SQL string built at the program level more
> efficient than collecting and sending all the parameters to the SP?
> 2. Is there a better way to populate the temporary table than calling a
> second stored proc for the insert?
> 3. Can we use an "in memory" table instead of the Temporary table (look
for
> the new TABLE DATA TYPE in SQL 2000)
> 4. Is there a better way to "group" the records other than with a cursor?
If
> not, can anything be done to the cursor to perform better?
> 5. In the stored proc... is there a better way to fetch the data by using
a
> VIEW maybe?
> I hope you can understand what I have written, if not, let me know and I
> will hopefully reword it in a way for you to be helpful.
> Thanks,
> Michael Murphy
>

For Stored Procs Gurus

Hi,
The following questions arose during the creation of a Crystal Report Graph.
The amount of data for the graph was so large that making any sense of it by
providing a legend for the colored lines was ridiculous. So we have come up
with a paging system of graphs based on regionIDs (foreign key). Because we
want to add the paging functionality, we basically need to do two SPs one to
gather the data for the Regions and one for the main report that uses the
results of the Regions SP.
Some of these questions may seem trivial, but I was looking for solutions
from those that are better than I at optimizing stored procs.
1. Is there a problem with the passing of 2000 characters in the different
parameters? What if we need to pass more? What are the limits when calling
Stored Procs? Is passing a SQL string built at the program level more
efficient than collecting and sending all the parameters to the SP?
2. Is there a better way to populate the temporary table than calling a
second stored proc for the insert?
3. Can we use an "in memory" table instead of the Temporary table (look for
the new TABLE DATA TYPE in SQL 2000)
4. Is there a better way to "group" the records other than with a cursor? If
not, can anything be done to the cursor to perform better?
5. In the stored proc... is there a better way to fetch the data by using a
VIEW maybe?
I hope you can understand what I have written, if not, let me know and I
will hopefully reword it in a way for you to be helpful.
Thanks,
Michael MurphyPlease do not post the same question independently to multiple newsgroups.
The .programming thread should be used for further communcation.
"Michael D Murphy" <mdmurphy@.scs-techresources.com> wrote in message
news:Oaul5sGjFHA.3540@.TK2MSFTNGP14.phx.gbl...
> Hi,
> The following questions arose during the creation of a Crystal Report
Graph.
> The amount of data for the graph was so large that making any sense of it
by
> providing a legend for the colored lines was ridiculous. So we have come
up
> with a paging system of graphs based on regionIDs (foreign key). Because
we
> want to add the paging functionality, we basically need to do two SPs one
to
> gather the data for the Regions and one for the main report that uses the
> results of the Regions SP.
> Some of these questions may seem trivial, but I was looking for solutions
> from those that are better than I at optimizing stored procs.
> 1. Is there a problem with the passing of 2000 characters in the different
> parameters? What if we need to pass more? What are the limits when calling
> Stored Procs? Is passing a SQL string built at the program level more
> efficient than collecting and sending all the parameters to the SP?
> 2. Is there a better way to populate the temporary table than calling a
> second stored proc for the insert?
> 3. Can we use an "in memory" table instead of the Temporary table (look
for
> the new TABLE DATA TYPE in SQL 2000)
> 4. Is there a better way to "group" the records other than with a cursor?
If
> not, can anything be done to the cursor to perform better?
> 5. In the stored proc... is there a better way to fetch the data by using
a
> VIEW maybe?
> I hope you can understand what I have written, if not, let me know and I
> will hopefully reword it in a way for you to be helpful.
> Thanks,
> Michael Murphy
>

For new in SSIS. To send data from operational d/base to data warehouse

Hi Dear All!

I am Crystal Reports Developer and I am new in SSIS environment. I have started to read Professional SQL Server 2005 IS book. I am really confused by many tasks to choose.

I need to develop reports from data warehouse. But before I have to send the data from operational database (SQL Server 2000) to warehouse (SQL Server 2005) monthly - I have a script for retrieving the data. For my package, I chose Data Flow Task, Execute SQL Task, and OLE DB Destination, and it does not work.

Please help me if I can look similar packages performing?

Thank you!!

You data-flow needs to contain more than just an OLE DB Destination. It needs an OLE DB Source as well.

-Jamie

|||

Also, consider watching the Kimball webcast on using SSIS in datawarehouses. It's on the main page of this forum and has very good information for beginners.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=534505&SiteID=1

|||

Thanks Jamie for your suggestion. Moreover, I will try to use SQL command in Data Access mode for OLE DB Source.

Corres

|||

Thanks Phil for your suggestion. To be honest, it is a bit complicated for me for now, but I have found a lot of useful info for me. Thanks.

Corres

|||When I started, I watched that webcast several times in order to pick up on several topics... Just give it time and play around with data and you'll get the hang of it.

For new in SSIS. To send data from operational d/base to data warehouse

Hi Dear All!

I am Crystal Reports Developer and I am new in SSIS environment. I have started to read Professional SQL Server 2005 IS book. I am really confused by many tasks to choose.

I need to develop reports from data warehouse. But before I have to send the data from operational database (SQL Server 2000) to warehouse (SQL Server 2005) monthly - I have a script for retrieving the data. For my package, I chose Data Flow Task, Execute SQL Task, and OLE DB Destination, and it does not work.

Please help me if I can look similar packages performing?

Thank you!!

You data-flow needs to contain more than just an OLE DB Destination. It needs an OLE DB Source as well.

-Jamie

|||

Also, consider watching the Kimball webcast on using SSIS in datawarehouses. It's on the main page of this forum and has very good information for beginners.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=534505&SiteID=1

|||

Thanks Jamie for your suggestion. Moreover, I will try to use SQL command in Data Access mode for OLE DB Source.

Corres

|||

Thanks Phil for your suggestion. To be honest, it is a bit complicated for me for now, but I have found a lot of useful info for me. Thanks.

Corres

|||When I started, I watched that webcast several times in order to pick up on several topics... Just give it time and play around with data and you'll get the hang of it.