Help! Issues with Export to Excel

Hi,
I need to export my reports to Excel, and I've encountered strange layout problems, as below.
Problem 1: Looks ok in report, looks crazy in Excel
----
I understand that data regions within table and matrices are not supported (see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_dc_v1_8yhy.asp).. so I have a matrix in a rectangle(instead of a table), and this rectangle within a list. Report generates this fine,.. nice and neat.., butonce exported to Excel, the layout is messy and unintelligible. One report column can be represented by 1 and some even 10 cells. Does anyone know what is the cause of this? Perhaps the use of lists?
Problem 2: What's #NAME?
--
I have a column X in report that a calculated value, and formula is
=(ReportItems!textbox213.Value / ReportItems!textbox211.Value) ...
textbox213 and textbox211 both have values from sums of other field items. So column X has a proper value when generated, but once exported, it says #NAME in the Excel column (error i suppose). When I click on #NAME, it says =(_146/_144) <-- what does this mean?
I would really appreciate anyone's help on this, since i've spend loads of time (too much!) on this.. Seems like what I see in the report is not what I get in Excel! Anyway, thank you in advance.
Best regards,
Julie
--
Message posted via http://www.sqlmonster.comIt's recommended to use tables rather than rectangles and lists when
exporting to Excel. As the link you provided describes, you get
unpredictable results when using anything other than tables or matrixes.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"JTay via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d285b020b8244ddeb4394efec2c3fb23@.SQLMonster.com...
> Hi,
> I need to export my reports to Excel, and I've encountered strange layout
> problems, as below.
> Problem 1: Looks ok in report, looks crazy in Excel
> ----
> I understand that data regions within table and matrices are not supported
> (see
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_dc_v1_8yhy.asp)..
> so I have a matrix in a rectangle(instead of a table), and this rectangle
> within a list. Report generates this fine,.. nice and neat.., butonce
> exported to Excel, the layout is messy and unintelligible. One report
> column can be represented by 1 and some even 10 cells. Does anyone know
> what is the cause of this? Perhaps the use of lists?
> Problem 2: What's #NAME?
> --
> I have a column X in report that a calculated value, and formula is
> =(ReportItems!textbox213.Value / ReportItems!textbox211.Value) ...
> textbox213 and textbox211 both have values from sums of other field items.
> So column X has a proper value when generated, but once exported, it says
> #NAME in the Excel column (error i suppose). When I click on #NAME, it
> says =(_146/_144) <-- what does this mean?
> I would really appreciate anyone's help on this, since i've spend loads of
> time (too much!) on this.. Seems like what I see in the report is not what
> I get in Excel! Anyway, thank you in advance.
> Best regards,
> Julie
> --
> Message posted via http://www.sqlmonster.com|||I can't use tables to encapsulate the matrix. If I do put the matrix within the table, it would say "Data Regions within table/matrix cells are ignored" on Excel when exported. This is a well known issue and is currently not supported, even in SP1.
However, I managed to get it to look slightly better in Excel, but after *much* manipulation on the alignment of the matrices and lists...
--
Message posted via http://www.sqlmonster.com|||Okay, I get it, you're right, there's no easy answer -- just lots of
tweaking layout.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"JTay via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:7e935d41037d4da694ce1277e681894a@.SQLMonster.com...
>I can't use tables to encapsulate the matrix. If I do put the matrix within
>the table, it would say "Data Regions within table/matrix cells are
>ignored" on Excel when exported. This is a well known issue and is
>currently not supported, even in SP1.
> However, I managed to get it to look slightly better in Excel, but after
> *much* manipulation on the alignment of the matrices and lists...
> --
> Message posted via http://www.sqlmonster.com

HELP! HOW TO CALL A DTS PACKAGE FROM A STORED PROC

I'm a rookie with MSSQL. I need to run a DTS package to export a result set
to an MX Excel spread sheet. I need to call the DTS from a stored procedure
and pass it three values, depending on the input parameters to the stored
proc.

DTS package is no problem. Pretty easy with the DTS wizard. My problem is
that I can't figure out how to instansiate the DTS package object from a
stored proc and pass the three values as parameters to the DTS package so
they can populate the parameters I created in it.

I found an article related to it, but I'm too much of a rookie to grasp it.
It showed how to do this from a stored procedure:

EXEC @.hr = sp_OASetProperty @.oPKG, 'GlobalVariables("MyGVName").Value,
'MyGVValue'
IF @.hr <> 0
BEGIN
PRINT '*** GlobalVariable Assignment Failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
END

I have three values and tree global variables to populate. Do I need to do
the above 3 times?

How do I instantiate the package object? I've read up some on sp_OACreate,
but I don't get it, yet.

How do I initiate the variable @.oPKG?
It contains the name of the sp_OACreate string, right? How do I address a
DTS package in the sp_OACreate string?

I would really appreciate just writing out the sp_OACreate string and how I
pass values for three existing global variables to a DTS package named
"DTS_1".

I'm under some real pressure to get this done.

Thanks for any help I can get.

GunnyHi

Check out

http://www.databasejournal.com/feat...cle.php/1459181

A good resource for everything DTS are books online and
http://www.sqldts.com

John

"Cox News Server" <cfgunny@.cox.net> wrote in message
news:RJClb.97602$AH4.96936@.lakeread06...
> I'm a rookie with MSSQL. I need to run a DTS package to export a result
set
> to an MX Excel spread sheet. I need to call the DTS from a stored
procedure
> and pass it three values, depending on the input parameters to the stored
> proc.
> DTS package is no problem. Pretty easy with the DTS wizard. My problem is
> that I can't figure out how to instansiate the DTS package object from a
> stored proc and pass the three values as parameters to the DTS package so
> they can populate the parameters I created in it.
> I found an article related to it, but I'm too much of a rookie to grasp
it.
> It showed how to do this from a stored procedure:
> EXEC @.hr = sp_OASetProperty @.oPKG, 'GlobalVariables("MyGVName").Value,
> 'MyGVValue'
> IF @.hr <> 0
> BEGIN
> PRINT '*** GlobalVariable Assignment Failed'
> EXEC sp_displayoaerrorinfo @.oPKG, @.hr
> END
> I have three values and tree global variables to populate. Do I need to do
> the above 3 times?
> How do I instantiate the package object? I've read up some on sp_OACreate,
> but I don't get it, yet.
> How do I initiate the variable @.oPKG?
> It contains the name of the sp_OACreate string, right? How do I address a
> DTS package in the sp_OACreate string?
> I would really appreciate just writing out the sp_OACreate string and how
I
> pass values for three existing global variables to a DTS package named
> "DTS_1".
> I'm under some real pressure to get this done.
> Thanks for any help I can get.
>
> Gunny

Help! Computed Column

I have a strange behavoiur
adding a computed column with the formulae (100 - 38 / 41 * 100)
gives a result in excel as 7.317073171 (correct) but in an sql column it
always returns 100
(the acutal formulae would be (100 - [FIELD] / 41 * 100)
any ideas?
Regards
Toby RileyToby
What's the datatype of [FIELD] you defined in SQL Server?
"TobyRiley" <TobyRiley@.discussions.microsoft.com> wrote in message
news:7B7BA6E5-89FE-4003-9E68-B8B11E12AA57@.microsoft.com...
>I have a strange behavoiur
> adding a computed column with the formulae (100 - 38 / 41 * 100)
> gives a result in excel as 7.317073171 (correct) but in an sql column it
> always returns 100
> (the acutal formulae would be (100 - [FIELD] / 41 * 100)
>
> any ideas?
>
> --
> Regards
> Toby Riley|||>> I have a strange behavoiur
adding a computed column with the formulae (100 - 38 / 41 * 100) <<
Integer math versus decimal math.|||The column might be an integer column
Use it this way.
100 - ([FIELD] *1.0)/ 41 * 100
Hope this helps.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"TobyRiley" wrote:

> I have a strange behavoiur
> adding a computed column with the formulae (100 - 38 / 41 * 100)
> gives a result in excel as 7.317073171 (correct) but in an sql column it
> always returns 100
> (the acutal formulae would be (100 - [FIELD] / 41 * 100)
>
> any ideas?
>
> --
> Regards
> Toby Riley|||Try using "41.0", as (100 - [FIELD] / 41.0 * 100)
Martin C K Poon
Senior Analyst Programmer
====================================
"TobyRiley" <TobyRiley@.discussions.microsoft.com> bl
news:7B7BA6E5-89FE-4003-9E68-B8B11E12AA57@.microsoft.com g...
> I have a strange behavoiur
> adding a computed column with the formulae (100 - 38 / 41 * 100)
> gives a result in excel as 7.317073171 (correct) but in an sql column it
> always returns 100
> (the acutal formulae would be (100 - [FIELD] / 41 * 100)
>
> any ideas?
>
> --
> Regards
> Toby Riley|||If [FIELD] in an integer datatype, then anything less than 41 divided by 41
will be truncated to 0, multiplied by 100 it is STILL 0. Subtract 0 from 100
and you have 100. One of the value in the division must be a datatype that
supports decimal digits.
HTH
Kalen Delaney, SQL Server MVP
"TobyRiley" <TobyRiley@.discussions.microsoft.com> wrote in message
news:7B7BA6E5-89FE-4003-9E68-B8B11E12AA57@.microsoft.com...
>I have a strange behavoiur
> adding a computed column with the formulae (100 - 38 / 41 * 100)
> gives a result in excel as 7.317073171 (correct) but in an sql column it
> always returns 100
> (the acutal formulae would be (100 - [FIELD] / 41 * 100)
>
> any ideas?
>
> --
> Regards
> Toby Riley|||Thank for all your quick replies, that's fixed it.
--
Regards
Toby Riley
"Kalen Delaney" wrote:

> If [FIELD] in an integer datatype, then anything less than 41 divided by 41
> will be truncated to 0, multiplied by 100 it is STILL 0. Subtract 0 from 1
00
> and you have 100. One of the value in the division must be a datatype tha
t
> supports decimal digits.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "TobyRiley" <TobyRiley@.discussions.microsoft.com> wrote in message
> news:7B7BA6E5-89FE-4003-9E68-B8B11E12AA57@.microsoft.com...
>
>

HELP! Can't display calculated member in Excel PivotTable

I have created a few calculated members under one dimension (meaning the par
ent dimension is not Measures, but other dimensions). It can be showed in th
e Analysis Manager, but cannot be displayed in MS Excel PivotTable (MS Offic
e 2k, xp, even 2003). Is th
ere any solution to display the calculated members (as with the dimension) i
n Excel PivotTable? vba code needed? service pack needed?Certain cube viewer (like Excel for example) don't allow you to view
calculated members and member properies ... you can't even do a
drill-through from there ... it's just a limitation on the client side ...
the client must be "programmed" to make use of those properties before it
will work ....
"xhuey" <anonymous@.discussions.microsoft.com> wrote in message
news:1D9E50BB-2936-48ED-9625-329596764B28@.microsoft.com...
> I have created a few calculated members under one dimension (meaning the
parent dimension is not Measures, but other dimensions). It can be showed in
the Analysis Manager, but cannot be displayed in MS Excel PivotTable (MS
Office 2k, xp, even 2003). Is there any solution to display the calculated
members (as with the dimension) in Excel PivotTable? vba code needed?
service pack needed?|||I had a similar problem using ADO MD from Visual Basic. I solved it just
using MSOLAP.2 instead of MSOLAP in the connection string.
Hope it can help you
Andrea
"xhuey" <anonymous@.discussions.microsoft.com> wrote in message
news:1D9E50BB-2936-48ED-9625-329596764B28@.microsoft.com...
> I have created a few calculated members under one dimension (meaning the
parent dimension is not Measures, but other dimensions). It can be showed in
the Analysis Manager, but cannot be displayed in MS Excel PivotTable (MS
Office 2k, xp, even 2003). Is there any solution to display the calculated
members (as with the dimension) in Excel PivotTable? vba code needed?
service pack needed?|||You may find that it's being exlcuded in Excel as Excel tends to make use of
the NONEMPTYCROSSJOIN function, which doesn't include (read as work for)
calculated members.
"xhuey" <anonymous@.discussions.microsoft.com> wrote in message
news:1D9E50BB-2936-48ED-9625-329596764B28@.microsoft.com...
> I have created a few calculated members under one dimension (meaning the
parent dimension is not Measures, but other dimensions). It can be showed in
the Analysis Manager, but cannot be displayed in MS Excel PivotTable (MS
Office 2k, xp, even 2003). Is there any solution to display the calculated
members (as with the dimension) in Excel PivotTable? vba code needed?
service pack needed?

Help with XML to Excel

Hi All,
I have a fairly complex XML file which I wanted to load to a table
using BulkLoad. However, BulkLoad has limitations when it comes to
error in the XML file (if data is bad, it stops).
After doing some research, I found out that if I have a file, say,
Books.xml and I open it in Excel (right click, open with Excel), it
does exactly what I want. I have the entire XML into my excel sheet,
with all the columns headers. It requires no schema at all.
My question is how Excel does this? How does it convert the XML into
the Excel sheet? If I can get that component of Excel that does it,
that will help me a lot. The reason is I do not want to use Excel while
I am loading my XML file into the table. I just want to know how the
Excel does it without schema, and I can do the same to export it into
the table as is.
If Bulk Load did not have the limitation, I would go for Bulk Load, but
the above conversion of xml to excel is even better if I can figure out
how it is done.
I am using Windows XP with MS Excel 2003.
Please help,
Thanks,
ChrisThere are several tools that can help you here. Part of GotDotNet, there is
a tool that will generate an XSD schema for your Xml document. The tool is
here:
http://apps.gotdotnet.com/xmltools/xsdinference/
You can later on apply couple of annotations in the XSD to make Bulkload
work with the generated schema. This should be pretty straightforward since
you want to generate a single table from your Xml Document. Just put
sql:is-constant="1" on the container elements if you have any and you are
good to go.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
<undoredo@.gmail.com> wrote in message
news:1113421813.353131.241690@.o13g2000cwo.googlegroups.com...
> Hi All,
> I have a fairly complex XML file which I wanted to load to a table
> using BulkLoad. However, BulkLoad has limitations when it comes to
> error in the XML file (if data is bad, it stops).
> After doing some research, I found out that if I have a file, say,
> Books.xml and I open it in Excel (right click, open with Excel), it
> does exactly what I want. I have the entire XML into my excel sheet,
> with all the columns headers. It requires no schema at all.
> My question is how Excel does this? How does it convert the XML into
> the Excel sheet? If I can get that component of Excel that does it,
> that will help me a lot. The reason is I do not want to use Excel while
> I am loading my XML file into the table. I just want to know how the
> Excel does it without schema, and I can do the same to export it into
> the table as is.
> If Bulk Load did not have the limitation, I would go for Bulk Load, but
> the above conversion of xml to excel is even better if I can figure out
> how it is done.
> I am using Windows XP with MS Excel 2003.
> Please help,
> Thanks,
> Chris
>

Help with XML to Excel

Hi All,
I have a fairly complex XML file which I wanted to load to a table
using BulkLoad. However, BulkLoad has limitations when it comes to
error in the XML file (if data is bad, it stops).
After doing some research, I found out that if I have a file, say,
Books.xml and I open it in Excel (right click, open with Excel), it
does exactly what I want. I have the entire XML into my excel sheet,
with all the columns headers. It requires no schema at all.
My question is how Excel does this? How does it convert the XML into
the Excel sheet? If I can get that component of Excel that does it,
that will help me a lot. The reason is I do not want to use Excel while
I am loading my XML file into the table. I just want to know how the
Excel does it without schema, and I can do the same to export it into
the table as is.
If Bulk Load did not have the limitation, I would go for Bulk Load, but
the above conversion of xml to excel is even better if I can figure out
how it is done.
I am using Windows XP with MS Excel 2003.
Please help,
Thanks,
Chris
There are several tools that can help you here. Part of GotDotNet, there is
a tool that will generate an XSD schema for your Xml document. The tool is
here:
http://apps.gotdotnet.com/xmltools/xsdinference/
You can later on apply couple of annotations in the XSD to make Bulkload
work with the generated schema. This should be pretty straightforward since
you want to generate a single table from your Xml Document. Just put
sql:is-constant="1" on the container elements if you have any and you are
good to go.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
<undoredo@.gmail.com> wrote in message
news:1113421813.353131.241690@.o13g2000cwo.googlegr oups.com...
> Hi All,
> I have a fairly complex XML file which I wanted to load to a table
> using BulkLoad. However, BulkLoad has limitations when it comes to
> error in the XML file (if data is bad, it stops).
> After doing some research, I found out that if I have a file, say,
> Books.xml and I open it in Excel (right click, open with Excel), it
> does exactly what I want. I have the entire XML into my excel sheet,
> with all the columns headers. It requires no schema at all.
> My question is how Excel does this? How does it convert the XML into
> the Excel sheet? If I can get that component of Excel that does it,
> that will help me a lot. The reason is I do not want to use Excel while
> I am loading my XML file into the table. I just want to know how the
> Excel does it without schema, and I can do the same to export it into
> the table as is.
> If Bulk Load did not have the limitation, I would go for Bulk Load, but
> the above conversion of xml to excel is even better if I can figure out
> how it is done.
> I am using Windows XP with MS Excel 2003.
> Please help,
> Thanks,
> Chris
>