Excel Export error in RS200SP2 when nesting tables within matirx

I was hoping someone could help out with this issue....I've got a report I created in Reporting Services 2000 SP2 where I am nesting table elements within the row and detail sections of the matrix. This allows me to have multiple items within my "row" data with column headings, and works perfectly fine within the designer, and viewer, and whenever I export it to any format other than Excel. Exporting to excel results in a "Specified cast is not valid" error.

To me this seems to be a bug in the Excel rendering extension, since it seems to be supported in RDL. Has anyone else run into this? Is there a fix to the renderer or a workaround that anyone is aware of?

I'd be happy to post the RDL if anyone is interested.

Thanks,

Casey

The Excel rendering extension does not support exporting nested tables within tables/matrices, so this won't work for you. The behavior is documented in books online somewhere.

However, normally the "nested" region should simply be exported as a blank space without throwing an error. A bug was reported on this problem, and it is fixed in 2005 (by "fixed", you still won't be able to export nested data regions, but you won't get an error).

|||

That's cool....I figured it wouldn't work, but thought it worth a try. I using nested tables mostly to give me the ability to add headers to rowgroup items, which I was able to reproduce visually by dropping a rectangle object on the matrix and arranging textboxes to simulate the table headers.

Thanks for the response.

|||

Were you able to find a workaround for exporting the nested tables to Excel?|||Not really, it looks like the limitation is in the Excel renderer. What I was able to do in my case which helped was to embed rectangles within the matrix column headers and add multiple text boxes within them. For the report I was working on that gave me the ability to approximate the format I was wanting. Not perfect, but good enough.

Excel Export error in RS200SP2 when nesting tables within matirx

I was hoping someone could help out with this issue....I've got a report I created in Reporting Services 2000 SP2 where I am nesting table elements within the row and detail sections of the matrix. This allows me to have multiple items within my "row" data with column headings, and works perfectly fine within the designer, and viewer, and whenever I export it to any format other than Excel. Exporting to excel results in a "Specified cast is not valid" error.

To me this seems to be a bug in the Excel rendering extension, since it seems to be supported in RDL. Has anyone else run into this? Is there a fix to the renderer or a workaround that anyone is aware of?

I'd be happy to post the RDL if anyone is interested.

Thanks,

Casey

The Excel rendering extension does not support exporting nested tables within tables/matrices, so this won't work for you. The behavior is documented in books online somewhere.

However, normally the "nested" region should simply be exported as a blank space without throwing an error. A bug was reported on this problem, and it is fixed in 2005 (by "fixed", you still won't be able to export nested data regions, but you won't get an error).

|||

That's cool....I figured it wouldn't work, but thought it worth a try. I using nested tables mostly to give me the ability to add headers to rowgroup items, which I was able to reproduce visually by dropping a rectangle object on the matrix and arranging textboxes to simulate the table headers.

Thanks for the response.

|||

Were you able to find a workaround for exporting the nested tables to Excel?|||Not really, it looks like the limitation is in the Excel renderer. What I was able to do in my case which helped was to embed rectangles within the matrix column headers and add multiple text boxes within them. For the report I was working on that gave me the ability to approximate the format I was wanting. Not perfect, but good enough.

sql

Excel Export error in RS200SP2 when nesting tables within matirx

I was hoping someone could help out with this issue....I've got a report I created in Reporting Services 2000 SP2 where I am nesting table elements within the row and detail sections of the matrix. This allows me to have multiple items within my "row" data with column headings, and works perfectly fine within the designer, and viewer, and whenever I export it to any format other than Excel. Exporting to excel results in a "Specified cast is not valid" error.

To me this seems to be a bug in the Excel rendering extension, since it seems to be supported in RDL. Has anyone else run into this? Is there a fix to the renderer or a workaround that anyone is aware of?

I'd be happy to post the RDL if anyone is interested.

Thanks,

Casey

The Excel rendering extension does not support exporting nested tables within tables/matrices, so this won't work for you. The behavior is documented in books online somewhere.

However, normally the "nested" region should simply be exported as a blank space without throwing an error. A bug was reported on this problem, and it is fixed in 2005 (by "fixed", you still won't be able to export nested data regions, but you won't get an error).

|||

That's cool....I figured it wouldn't work, but thought it worth a try. I using nested tables mostly to give me the ability to add headers to rowgroup items, which I was able to reproduce visually by dropping a rectangle object on the matrix and arranging textboxes to simulate the table headers.

Thanks for the response.

|||

Were you able to find a workaround for exporting the nested tables to Excel?|||Not really, it looks like the limitation is in the Excel renderer. What I was able to do in my case which helped was to embed rectangles within the matrix column headers and add multiple text boxes within them. For the report I was working on that gave me the ability to approximate the format I was wanting. Not perfect, but good enough.

Excel DSN Query

I have an excel spreadsheet that I have created a query using a DSN to access a sql database. My question; is there a way to change the DSN without creating a new query?

I have a SQL database that is used as a backend to an accounting application. Currently many upper management users have created spreadsheets to pull reports from this database using the backend (DSN connection). Since this is a production database I have created a script to restore the nightly backup to another server and I have denied access to the production server backend. Now I have many spreadsheets with queries set up to use a DSN connection to the production database. I would like to know if there is a known way or script to change the DSN without creating new queries.

Thanks for any help that you can provide.

SboivinGO to Settings - Control Panel - Administrative Tools - Data Sources (ODBC)

Find DSN you used in your queries and hit Configure. Here you can change server name or just follow directions.

Don't change DSN name itself. This way when queries will refer to this DSN they will be pointed to a right server.

Good Luck.|||

Quote:

Originally Posted by iburyak

GO to Settings - Control Panel - Administrative Tools - Data Sources (ODBC)

Find DSN you used in your queries and hit Configure. Here you can change server name or just follow directions.

Don't change DSN name itself. This way when queries will refer to this DSN they will be pointed to a right server.

Good Luck.

Good idea but I can't actually do this because we have an application that uses this DSN which connects to the original production database.

Thanks tho...

Excel Drill-Down Report

How can I create a Drill-Down Report for Excel ?
I've created a report with several Lists. The visibilty of the lists depends
on a Parameter:
For Example:
=Parameters!managementsummary.Value.ToString() = "0"
This works fine for HTML and other outputs but not for excel. I always get a
RenderingError. Perhaps someone can help me.If you use tables and set the drill down visibility (ToggleItem
property) at group level rather than cell level, the export to excel
automatically recreates the drill down using 'Groups and Outlines'.
Chris
Karpo wrote:
> How can I create a Drill-Down Report for Excel ?
> I've created a report with several Lists. The visibilty of the lists
> depends on a Parameter:
> For Example:
> =Parameters!managementsummary.Value.ToString() = "0"
> This works fine for HTML and other outputs but not for excel. I
> always get a RenderingError. Perhaps someone can help me.|||Found the Error...was the matrix...a self calculated row
"Karpo" wrote:
> How can I create a Drill-Down Report for Excel ?
> I've created a report with several Lists. The visibilty of the lists depends
> on a Parameter:
> For Example:
> =Parameters!managementsummary.Value.ToString() = "0"
> This works fine for HTML and other outputs but not for excel. I always get a
> RenderingError. Perhaps someone can help me.

Excel destination fails

Hi All!

I am trying to create a table destination to excel which fails with the following error that " Table cannot be created " System Error in Field definition" where as the same works in the Falt File Destination. What could be the problem?

Thanks

jas

It maybe due to the fact that excel has in built data types and column sizes attached to the columns whereas a flat file does not have that.

Try creating a table first and then map the excel columns to it to extract the records from the excel spreadsheet. When you import into a staging table, it is best to just keep the columns as varchar(255). If you have fields like remarks field that could be even bigger, you might want to customize your staging table.sql

Excel Destination

Does an empty file (text/ Excel) needs to be created manually for the connection manager for (text/excel)destinations. Can I create the file at run time?A file will be created for you.sql

Excel 2007 can't read data from AS 2005 cube

I have created an AS 2005 cube for my tester to access thru Excel 2007. He can connect to the cube fine, but can't read any measurement data elements from Excel. What Excel return is #N/A in the cell. I've checked many times the role that was setup to grant him permission to the cube. He should have all the rights to read data. In fact, the role setup was fine before I installed service pack 2. I wonder what's been changed in sp2. Any help would be great!

Mitch

It might be worth revisiting the "Cell Data" tab for the role, to ensure that none of the 3 "Enable .." options is checked.|||I wonder what the 3 "Enable..." options do in the "Cell Data" tab. I did unchecked the options and it works now.

Excel 2003 doesn't work with SQL 2005 AS

Greetings!
We have a connectivity problem between Excel 2003 and SQL Server 2005 AS. We
created cubes and it works OK on server. Then we installed Excel 2003 on AS
server to check connectivity. It works OK. But on any other workstation with
Excel installed it could not connect. It shos the message that server is not
available. Please help me if you know the solution.
We have SQL 2005 SP1 and Excel 2003 SP2 installed.
Thank you.
Igor A. ChechetHi Igor,
I'm pretty sure you'll need the most recent OLAP provider for connecting to
AS 2005. It's named "Microsoft OLE DB Provider for Analysis Services 9.0" and
can be downloaded from this here:
http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
/Martin
"Igor A. Chechet" wrote:
> Greetings!
> We have a connectivity problem between Excel 2003 and SQL Server 2005 AS. We
> created cubes and it works OK on server. Then we installed Excel 2003 on AS
> server to check connectivity. It works OK. But on any other workstation with
> Excel installed it could not connect. It shos the message that server is not
> available. Please help me if you know the solution.
> We have SQL 2005 SP1 and Excel 2003 SP2 installed.
> Thank you.
> Igor A. Chechet
>
>

Excel 2003 and nonemptycrossjoin

I am trying to utilize an Excel 2003 pivot table as a client tool to front end a project I am developing. I have created the cube in Analysis Services 2005 and it works fine when I run MDX against it directly. The problem that I am having is that Excel is not using nonemptycrossjoin in the pivot table. I have Teo Lachev's book "Applied Microsoft Analysis Services 2005' and I was looking for tips and found a note on p. 633 referring to a workaround (http://blogs.msdn.com/bi_systems/articles/162852.aspx).

To simplify testing, I reduced the query down to the most essential dimensions on rows and a single non-calculated measure on columns. The below query takes 1 second from SQL Server Management Studio.

select
nonemptycrossjoin([Product Family].[Product Families].[Product Brand].&[Family1],
[Shift Calendar].[Shift Fiscal].[Fiscal Year].&[2.006E3].&[2006-3].&[2006-08].&[2006-33],
[Failure Root Cause].[FA Failure Root Causes].[FA Pass Fail Indicator].&[Fail].&[Failure1],
[Comp1 Supplier].[Comp1 Suppliers].[Comp1 Supplier Description],
[Comp2 Supplier].[Comp2 Suppliers].[Comp2 Supplier Description]) on rows
{[Measures].[Sum Failed Qty]} on columns
from
[MyCube]

If I remove the nonempycrossjoin, keeping the () on rows to get the implicit crossjoin, the query runs but takes 114s. Now, if I start creating a similar query in Excel, the first few dimensions are pretty fast, but adding the last dimension on rows takes so long that my machine consumes all of its memory (2 GB) and CPU. I have to kill Excel to get my machine back.

So, Excel 2003 does not provide a facility to alter the Data Source, so after a bit of looking around I found the data source in "C:\Documents and Settings\username\Application Data\Microsoft\Queries". Opening my file test.oqy:

QueryType=OLEDB
Version=1
CommandType=Cube
Connection=Provider=MSOLAP.3;Cache Authentication=False;User ID="";Initial Catalog=Analysis Services Test Project;Data Source=localhost;Impersonation Level=Impersonate;Location=localhost;Mode=ReadWrite;Protection Level=Pkt Privacy;Auto Synch Period=20000;Default Isolation Mode=0;Default MDX Visual Mode=0;MDX Compatibility=0;MDX Unique Name Style=0;Non Empty Threshold=0;SQLQueryMode=Calculated;Safety Options=1;Secured Cell Value=0;SQL Compatibility=0;Compression Level=0;Real Time Olap=False;Packet Size=4096
CommandText=AS Test Cube

According to the workaround above, this query should use the "new" method to eliminate empty rows since: 1) Non Empty Threshold is set lower than the number of tuples returned. and 2) there are no calculated measures in the query. Meeting both these criteria should clear the way for the "new" method to be used. Does anyone have any other ideas that I could employ to make this pivot table any faster? Any other tools that would do this with nonemptycrossjoin? I need slice/dice capabilities which is preventing my from using Reporting Services.

Thanks,

Keehan

Hi Keehan,

A question I would have, though, is whether the "Non Empty Threshold" connection property works in the same way in AS 2005 (or is just included for backward compatibility). Unfortunately, I can't locate this info in BOL; but Appendix B of the "MDX Solutions" 2nd Edition covers connection parameters. Hopefully, someone from [MS] will be able to authoritatively answer this?

In any case, I assume that the number of rows returned in your scenario easily exceeds the default threshold. If so, is there any possibility of restructuring the dimensions to exploit the AS 2005 Auto Exists feature - such as folding some separate dimensions into hierarchies of a single dimension?

http://www.wiley.com/WileyCDA/WileyTitle/productCd-0471748080.html

>>

Appendix B: Connection Parameters That Affect MDX.

>>

|||

Deepak,

I kind of see where you are going with auto-exists but I'm not sure how it would be implemented. So, basically, for auto-exists to kick in, the attributes would have to be in the same dimension. So, how would I have say product and calendar in the same dimension. Is this the kind of work around you are thinking about?

Keehan

|||

I think I may know what you are talking about now. Let me dig into this a little more and I'll let you know how it goes.

Cheers

Excel - Read-only

I created a system DSN for an Excel file and I have been using it for a
while. I can read from it as well as update it. (The Excel file
includes weekly updates to some records.)

Then I tried to create the same connection with an explicit connection
string. It opens the file fine, I can read from it and all. But when
I try to update it, it complains that the database is read-only. I am
using the connection string: "Driver=Microsoft Excel Driver
(*.xls);DBQ=excel-fle-path;Uid=;Pwd="

Is there a switch to include to say that I would like to open the Excel
file as a read/write database just like via the ODBC set up?No idea - you'll probably get a better response in an ODBC/OLEDB group,
or one for your programming language/environment - VB, C#, ASP etc. But
there is plenty of information available about connecting to Excel and
working with the data:

http://www.carlprothman.net/Default...roviderForExcel
http://support.microsoft.com/defaul...kb;en-us;278973

Simon|||what driver do you use for DSN?
A good check would be to open the DSN file with notepad and check that.

DM Unseen

Examples of Certificates created with Private Key and FROM EXECUTABLE FILE options

Hi

I am looking at documentation of CREATE CERTIFICATE statement. I am having hard time in understanding if I want to create CERTIFICATE with above mentioned options, how I am supposed to create either PRIVATE KEY file or EXECUTABLE file. any example would be really helpful for what I am doing here.

thanks

Satya

Take a look at makecert: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cptools/html/cpgrfcertificatecreationtoolmakecertexe.asp. This utility generates certificates in those two types of files.

But note that you don't need to create those files to create a certificate. Those options are only used to import a certificate from such files. You can create a certificate within SQL Server by not using those options. You can find several examples on my blog, for example: http://blogs.msdn.com/lcris/archive/2005/06/10/428178.aspx.

Also, you can create those files by backing up a certificate created in SQL Server. See syntax for BACKUP CERTIFICATE: http://msdn2.microsoft.com/en-us/library/ms178578(SQL.90).aspx.

Thanks
Laurentiu

Everyone can see the Master database?

I installed SQL Server, created a database for a sql server user and
noticed that the user has access to the master database even though the
checkbox for master database for the user login is not checked.
They can list sysusers and find out all the names. They can list all the
databases as well by using sp_helpdb.

Is this normal behavior?
If I check db_denydatareader and db_denydatawriter in the master database
for that user, will that break anything?

John DalbergKarim (karim3411@.!!yahoo!!.com) writes:
> I installed SQL Server, created a database for a sql server user and
> noticed that the user has access to the master database even though the
> checkbox for master database for the user login is not checked.
> They can list sysusers and find out all the names. They can list all the
> databases as well by using sp_helpdb.
> Is this normal behavior?

Yes. This is because the guest user is present in master. This means that
even if your login does not map to a specific user in master, your login
maps to guest. And Books Onlines says that guest must be present in master.

> If I check db_denydatareader and db_denydatawriter in the master database
> for that user, will that break anything?

Well, to add the login to this role, you would first have to add the user.
But you could add guest to these roles. And, yes, that will break things.
I did a quick test. When I tried to login as a plain user, I got a
permission error on spt_values.

Possibly you could deny access on some tables, but I suspect that you
would be wondering off in the land of unsupported.

The good news is that in the next version of SQL Server, the metadata is
not equally well exposed, and the basic principle is that you should only
see the objects that you have permission to. That is, you may still be
able to read sys.databases, but you would only see the databases you
have permission to.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Event notification on disabled queue: the conversation must end?

Hello,

I have configured an Event Notification for BROKER_QUEUE_DISABLED.

I created a Queue different from the one the Receive my normal messages and on this queue I define an activation.

The Activated SP takes the [EventNotification] message and execute a

RAISEERROR('Message',0,1) WITH LOG.

The error is properly logged in the SQL server event log but I gave a look the Initiator and target sys.conversation_endpoints catalog view and I have see the the conversations are in the status CONVERSING or SI ...

Do I have to call the END CONVERSATION after the commit or these statuses are caused by some mistakes on the message process workflow?

Thank you

Marina B.

The conversations used by the event notifications infrastructure should not be ended. They will be ended by initiator when the event notification object is dropped (DROP EVENT NOTIFICATION ...). You procedure should react to the EndDialog message by ending it's side. If you end the conversations prematurely from the procedure side you are in effect disabling the event notification (it will have to be dropped and created again).

This applies for all event notifications, it is not specific to BROKER_QUEUE_DISABLED.