In my report, I have a calculated field which when exported to excel
shows up as text with the smart tag message 'number as text'. I have
tried all formatting strings as part of the calculated expression
CDbl, Cdec, Int, FormatCurrency etc ... but the 'number as text' does
not seem to go away.
How can I export the calculated field results to excel 2007 from SSRS
as number and not as text.
Please help.
Thank you for your time!I have this same issue! Have you determined how to fix this? dhughes@.cogc.com
"avididy" wrote:
> In my report, I have a calculated field which when exported to excel
> shows up as text with the smart tag message 'number as text'. I have
> tried all formatting strings as part of the calculated expression
> CDbl, Cdec, Int, FormatCurrency etc ... but the 'number as text' does
> not seem to go away.
> How can I export the calculated field results to excel 2007 from SSRS
> as number and not as text.
> Please help.
> Thank you for your time!
>|||Hi,
Did you find a solution to the problem of calculated fields appearing as
text in Excel?sql
Showing posts with label export. Show all posts
Showing posts with label export. Show all posts
Sunday, March 25, 2012
Wednesday, March 7, 2012
Cache ODBC dsn problem in Connection Manager
I have created a simple SSIS package using the Import / Export wizard. I have, in Connection Manager, created a DataReaderSrc connection using .Net Providers\ODBC Provider to connect to my exising DSN name of AMPFM which is an ODBC connection to a InterSystems Cache Database. It test successfully but fails when I run the package with the error at the bottom of this text. I have also tried the following connection string which also tests successfully but then fails when I run the package:
Dsn=AMPFM;server=10.11.1.34;uid=_system;port=1972;database=ALL;query timeout=1
SSIS package "Package1.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0047062 at Data Flow Task, Source - Query [1]: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)
Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "Source - Query" (1) failed validation and returned error code 0x80131937.
Can someone help me with this problem? I need to create several packages to pull data from this Cache database on a regular basis and find myself stuck at this step.
Thanks!
A few more details might help point me/others in the right direction. Specifically:
* Do I understand correctly that when you click "Test Connection" from inside the designer, it's succeeding?
* Can you try running the package in the debugger (i.e. Debug | Start Debugging), and report on whether the connection manager is able to connect there?
* Is it possible that the "Source - Query [1]" component on your Data Flow Task is configured to use a different connection manager than the one you're configuring? (Unlikely, but possible.)
* How are you executing the package (dtexec, dtexecui, designer, etc.) when it fails? Are you running as the same user as you're designing with? Also if you're running under Vista, are you running the package as an Administrator?
* Is your DSN a User or System DSN?
Thanks, -David
|||Here is additional information David:
This SSIS 2005 package is using a 32-bit ODBC system dsn that worked successfully with SQL Server 2000 DTS packages.
I have a SQL Server 2005 x64 Enterprise database system on Windows 2003 x64 Enterprise.
The source query is using the DataReaderSrc connection manager which connects sucessfully when clicking on "Test Connection"
The package is only being tested in BIDS using Debug with my userid which is also the userid that created the package
Thanks!
|||Maybe you are running 32-bit ODBC driver in 64-bit mode. Is your driver 32-bit only?
To check this quickly: right-click on your IS project in VS, choose Properties, select the Debugging node and switch the Run64BitRuntime flag to false.
HTH.
|||That is exactly what was happening, the driver is only 32-bit! I had heard of the Run64BitRuntime flag but could not find it before receiving your instructions above. The job is running correctly now. Thanks so much Bob!
Cache ODBC dsn problem in Connection Manager
I have created a simple SSIS package using the Import / Export wizard. I have, in Connection Manager, created a DataReaderSrc connection using .Net Providers\ODBC Provider to connect to my exising DSN name of AMPFM which is an ODBC connection to a InterSystems Cache Database. It test successfully but fails when I run the package with the error at the bottom of this text. I have also tried the following connection string which also tests successfully but then fails when I run the package:
Dsn=AMPFM;server=10.11.1.34;uid=_system;port=1972;database=ALL;query timeout=1
SSIS package "Package1.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0047062 at Data Flow Task, Source - Query [1]: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)
Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "Source - Query" (1) failed validation and returned error code 0x80131937.
Can someone help me with this problem? I need to create several packages to pull data from this Cache database on a regular basis and find myself stuck at this step.
Thanks!
A few more details might help point me/others in the right direction. Specifically:
* Do I understand correctly that when you click "Test Connection" from inside the designer, it's succeeding?
* Can you try running the package in the debugger (i.e. Debug | Start Debugging), and report on whether the connection manager is able to connect there?
* Is it possible that the "Source - Query [1]" component on your Data Flow Task is configured to use a different connection manager than the one you're configuring? (Unlikely, but possible.)
* How are you executing the package (dtexec, dtexecui, designer, etc.) when it fails? Are you running as the same user as you're designing with? Also if you're running under Vista, are you running the package as an Administrator?
* Is your DSN a User or System DSN?
Thanks, -David
|||Here is additional information David:
This SSIS 2005 package is using a 32-bit ODBC system dsn that worked successfully with SQL Server 2000 DTS packages.
I have a SQL Server 2005 x64 Enterprise database system on Windows 2003 x64 Enterprise.
The source query is using the DataReaderSrc connection manager which connects sucessfully when clicking on "Test Connection"
The package is only being tested in BIDS using Debug with my userid which is also the userid that created the package
Thanks!
|||Maybe you are running 32-bit ODBC driver in 64-bit mode. Is your driver 32-bit only?
To check this quickly: right-click on your IS project in VS, choose Properties, select the Debugging node and switch the Run64BitRuntime flag to false.
HTH.
|||That is exactly what was happening, the driver is only 32-bit! I had heard of the Run64BitRuntime flag but could not find it before receiving your instructions above. The job is running correctly now. Thanks so much Bob!
Tuesday, February 14, 2012
button to export to excel
Hi, I would like to have a button on my report to export it to excel
(instead of having to choose the format from the toolbar and then press
"export").
Is there a way to do it? I know that there is a parameter that I can
add to the url for this but i don't know exactly how to add it to the
current url from my report).
Thanks.Yes it can be done.
Put a text box with text something like "Save as Excel" you can Underline
the text to look more like a hyperlink in the "Action" option of the text box
give the URL as
http://servername/reportserver?/SampleReports/Employee Sales
Summary&rs:Command=Render&rs:format=EXCEL. You can pass the parameters as
well.
PS: You need to create the replica of the original report and point in the
URL which takes it to excel otherwise you will have "Save as Excel" text will
also get exported.
Just to avoid that you can create the same replica (copy paste the report)
and remove the "save as Excel" text from the copy of the report.
Amarnath
"nicknack" wrote:
> Hi, I would like to have a button on my report to export it to excel
> (instead of having to choose the format from the toolbar and then press
> "export").
> Is there a way to do it? I know that there is a parameter that I can
> add to the url for this but i don't know exactly how to add it to the
> current url from my report).
> Thanks.
>|||Hi Amarnath,
Thanks for your reply.
As i understand, The only solution is to have two reports (for example
ORIGINAL and COPY_ORIGINAL).
1) My original report with a 'button' that have a URL of the
COPY_ORIGINAL and in that url i'll add the "rs:format=3DEXCEL" at the
end).
2) My copy that is the same as the first one (just copy&paste) but
without the 'button'.
Is that right?
Its look like it my be a solution exept for the problem that every time
i'll make a change in the original report i'll have to delete the copy
and create it again.
Did I got it right?
Thank.
Amarnath =D7=9B=D7=AA=D7=91:
> Yes it can be done.
> Put a text box with text something like "Save as Excel" you can Underline
> the text to look more like a hyperlink in the "Action" option of the text= box
> give the URL as
> http://servername/reportserver?/SampleReports/Employee Sales
> Summary&rs:Command=3DRender&rs:format=3DEXCEL. You can pass the parameter=s as
> well.
> PS: You need to create the replica of the original report and point in the
> URL which takes it to excel otherwise you will have "Save as Excel" text =will
> also get exported.
> Just to avoid that you can create the same replica (copy paste the report)
> and remove the "save as Excel" text from the copy of the report.
> Amarnath
>
> "nicknack" wrote:
> > Hi, I would like to have a button on my report to export it to excel
> > (instead of having to choose the format from the toolbar and then press
> > "export").
> >
> > Is there a way to do it? I know that there is a parameter that I can
> > add to the url for this but i don't know exactly how to add it to the
> > current url from my report).
> > > > Thanks.
> > > >
(instead of having to choose the format from the toolbar and then press
"export").
Is there a way to do it? I know that there is a parameter that I can
add to the url for this but i don't know exactly how to add it to the
current url from my report).
Thanks.Yes it can be done.
Put a text box with text something like "Save as Excel" you can Underline
the text to look more like a hyperlink in the "Action" option of the text box
give the URL as
http://servername/reportserver?/SampleReports/Employee Sales
Summary&rs:Command=Render&rs:format=EXCEL. You can pass the parameters as
well.
PS: You need to create the replica of the original report and point in the
URL which takes it to excel otherwise you will have "Save as Excel" text will
also get exported.
Just to avoid that you can create the same replica (copy paste the report)
and remove the "save as Excel" text from the copy of the report.
Amarnath
"nicknack" wrote:
> Hi, I would like to have a button on my report to export it to excel
> (instead of having to choose the format from the toolbar and then press
> "export").
> Is there a way to do it? I know that there is a parameter that I can
> add to the url for this but i don't know exactly how to add it to the
> current url from my report).
> Thanks.
>|||Hi Amarnath,
Thanks for your reply.
As i understand, The only solution is to have two reports (for example
ORIGINAL and COPY_ORIGINAL).
1) My original report with a 'button' that have a URL of the
COPY_ORIGINAL and in that url i'll add the "rs:format=3DEXCEL" at the
end).
2) My copy that is the same as the first one (just copy&paste) but
without the 'button'.
Is that right?
Its look like it my be a solution exept for the problem that every time
i'll make a change in the original report i'll have to delete the copy
and create it again.
Did I got it right?
Thank.
Amarnath =D7=9B=D7=AA=D7=91:
> Yes it can be done.
> Put a text box with text something like "Save as Excel" you can Underline
> the text to look more like a hyperlink in the "Action" option of the text= box
> give the URL as
> http://servername/reportserver?/SampleReports/Employee Sales
> Summary&rs:Command=3DRender&rs:format=3DEXCEL. You can pass the parameter=s as
> well.
> PS: You need to create the replica of the original report and point in the
> URL which takes it to excel otherwise you will have "Save as Excel" text =will
> also get exported.
> Just to avoid that you can create the same replica (copy paste the report)
> and remove the "save as Excel" text from the copy of the report.
> Amarnath
>
> "nicknack" wrote:
> > Hi, I would like to have a button on my report to export it to excel
> > (instead of having to choose the format from the toolbar and then press
> > "export").
> >
> > Is there a way to do it? I know that there is a parameter that I can
> > add to the url for this but i don't know exactly how to add it to the
> > current url from my report).
> > > > Thanks.
> > > >
Subscribe to:
Posts (Atom)