Excel Export Problem - Flattened Recursive Hierarchy
Feb 1, 2008
Hi,
I have a recursive hierarchy in SSRS2005 which contains accounts like so:
+ Revenue
+ Expense
which is grouped using details group and can be expanded like:
+ Revenue
- Revenue type
- Revenue type
- etc.
+ Expense
- Expense type
- Expense type
- etc.
It can be drilled down in five levels. This works fine in both SSAS and SSRS, but when I export the report to excel 2003, the hierarchy gets flattened, meaning that every account on the detail level (level 5) is exposed. Not what I had in mind. Rather SSRS should create a hierarchy in excel as well..
If you've ever worked with CI (UPC barcodes), certain commercially distributed products can be "packaged" in certain ways as defined by suppliers. For example, buying a six-pack of Coca-Cola, each one of the cans have a barcode UPC that indicates that the particular can is to only be distributed as part of a "pack" - they can't be distributed or sold individually.
Certain products can be distributed and sold in more than one way. For example, that same six pack can be distributed and sold as a case bundle, or it can be distributed as a single six pack. Each packaging method has a certain hierarchy.you can't represent the CI model using anything less than an adjacency matrix.Now, this can be represented in SQL pretty easily - there's tons of examples online that go through the basics of how to do it and how to write the recursive CTE function to represent and return data in this format. My problem is with MDS.
In Master Data Services (MDS) you have several hierarchy types to choose from. The hierarchy type that makes the most sense to me is to use the recursive derived hierarchy. It makes sense because I want to define an entity that contains all the "acceptable" adjacencies for particular items, such that when a user looks at the data in MDS or tries to update its value, it will have certain domain-based enforcement (and thusly lookups).I think that I've crossed a bridge too far with MDS - but, perhaps one of you knows something I don't know. It looks like any hierarchy in MDS is only based in a parent - child relationship, so that's not quite what the doctor ordered here.
I noticed that by inserting another group higher than the existing one gave me the effect of a nested group...so what would a recursive hierarchy have done for me that is different? I read the BOL writeup and didnt come away with any sense of what these hierarchies offer, seemingly thru the use of a "parent" entry.
does anyone know why my initial visibility expression (=Level()>1) stays in effect after generating my report? The idea was to display only a couple of levels in the hierarchy at first, so that the user could more easily decide what he wants to expand. But expand doesnt do anything after initial rendering.
The only way I could get this kind of report to work (show hierarchy and expand/collapse) was to live with initial visibility = "visible", but unfortunately, that setting shows all levels right away, not a very user friendly thing.
I am running Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Anyone know why cells within a matrix that are formatted as numeric export to Excel with a cell format proprty of "General"? Cells within a table however export with an appropriate format.
When I open the spreadsheet in Excel 2000, it works fine. When I try to print, it crashes Excel. In testing, I narrowed it down to the Header/Footer, because it also crashes when I go to Page Setup and click on the header/footer tab.
However, I can print the same spreasheet from Excel 2007.
Am I just dealing with a "you need to upgrade all your clients" situation, or is there a known issue with certian formatting that is passed out with reports that is not supported by older versions of Excel?
I am using Reporting Services 2005 SP2 to serve up the report that is exported to Excel.
I've been googling this for a while now and can't seem to find any elegant answers.
I'm looking for an automated way to present a FORMATED Excel Spreadsheet to the Customer from a stored procedure output.
Can anyone advise me the best method of doing this - should I / can I assign an Excel Template to the DTS Task output ?
His mind is set on Excel and the formatting is basic and easy to write in a Macro which I've done, but this requires human interaction to finish the task (Automated Run Once on opening etc).
In an ideal world an individual would send an email to the Server with two formated parameters (@FromDate & @ToDate) and would be emailed back a ready formatted S/Sheet. But I believe he would be willing to just select the relevant SpreadSheet for the Daily / Weekly / Monthly periods dumped.
I am having problem to apply updates into this function below. I triedusing cursor for updates, etc. but no success. Sql server keeps tellingme that I cannot execute insert or update from inside a function and itgives me an option that I could write an extended stored procedure, butI don't have a clue of how to do it. To quickly fix the problem theonly solution left in my case is to convert this recursive functioninto one recursive stored procedure. However, I am facing one problem.How to convert the select command in this piece of code below into an"execute" by passing parameters and calling the sp recursively again.### piece of code ############SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid######### my function ###########CREATE FUNCTION Mkt_GetChildren(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)RETURNS decimalASBEGINIF EXISTS (SELECTuidFROMcategories WHEREParentID = @uid)BEGINDECLARE my_cursor CURSOR FORSELECT uid, classid5 FROM categories WHERE parentid = @uiddeclare @getclassid5 varchar(50), @getuid bigint, @calculate decimalOPEN my_cursorFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,@DateEnd)SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))ENDCLOSE my_cursorDEALLOCATE my_cursorSELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uidENDRETURN @subtotalENDGORod
--Example Schema posted at end of message:---For reporting purposes, I need to build a single comma delimited string ofvalues from the "many" tableof a database.The easiest way to see what I want is to look at the sample after mysignature.By the way, this is actually a busines problem, not homework! I justcreated a simpleexample using class and persons because everyone is familiar with thatrelationship.I have two tables on the 'one' side of the relationship: PERSON and CLASSThe ENROLLMENT table resolves the many to many relationship between PERSONand CLASS.(I know that a real system would be date effective, etc, but this is just asimple example.that will show my problem). ENROLLMENT has one row for each Class in which aPerson is enrolled.Look at the sample report: I have to "flatten" the join result and listthe class titles in acomma delimited string. I am stuck with this reporting requirement, and Iam NOT going to denormalizethe tables.One way to accomplish the result is to use a cursor to step through the rowsand build a "Classes"string with concatenation. I don't much like this option. I am not writingthe front end code,but I want to make it easy for the developer. Ideally, I would like to givehim a flattened viewso he can just do a simple join and run his report.I believe that what I want cannot be accomplished with ANSI SQL. However,does MS SQL have someextensions that could help me do the job? Failing that, how could I write astored procedure that wouldreturn the personID and the "Classes" string in a format that would bejoinable to the other tables?Thanks,Bill MacLeanP.S. Some people like to see actual database scripts as samples instead ofa textual representation.I have pasted in a script that creates sample tables and populates them.--Sample Tables and Reports:TABLE PERSONPersonID LastNM FirstNM--------- ----------- ---------1 Smith John2 Jones Sara3 Smith LucilleTABLE CLASSClassID ClassNM----------- ------------------10 SQL Server 10120 C++25 Object Oriented Design40 Inorganic Chemistry50 Organic Chemistry80 Early Lit.TABLE ENROLLMENTPersonID ClassID-------- ---------1 102 101 401 803 203 25SAMPLE REPORTPerson ID Name Classes--------- ----------------------- -----------------------------------------------------------------1 Smith, John SQL Server 101, Inorganic Chemistry,Early Lit.2 Jones, Sara SQL Server 1013 Smith, Lucille C++, Object Oriented Design/************************************************** ********SQL Server Script/************************************************** ********/CREATE TABLE [dbo].[CLASS] ([ClassID] [int] NOT NULL ,[ClassNM] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[ENROLLMENT] ([PersonID] [int] NOT NULL ,[ClassID] [int] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[PERSON] ([PersonID] [int] NOT NULL ,[LastNM] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[FirstNM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOALTER TABLE [dbo].[CLASS] WITH NOCHECK ADDCONSTRAINT [PK_CLASS] PRIMARY KEY CLUSTERED([ClassID]) ON [PRIMARY]GOALTER TABLE [dbo].[ENROLLMENT] WITH NOCHECK ADDCONSTRAINT [PK_ENROLLMENT] PRIMARY KEY CLUSTERED([PersonID],[ClassID]) ON [PRIMARY]GOALTER TABLE [dbo].[PERSON] WITH NOCHECK ADDCONSTRAINT [PK_PERSON] PRIMARY KEY CLUSTERED([PersonID]) ON [PRIMARY]GOALTER TABLE [dbo].[ENROLLMENT] ADDCONSTRAINT [FK_ENROLLMENT_CLASS] FOREIGN KEY([ClassID]) REFERENCES [dbo].[CLASS] ([ClassID]),CONSTRAINT [FK_ENROLLMENT_PERSON] FOREIGN KEY([PersonID]) REFERENCES [dbo].[PERSON] ([PersonID])GO-- Insert rwo for each CLASSINSERT INTO CLASS VALUES (10,'SQL Server 101');INSERT INTO CLASS VALUES (20,'C++');INSERT INTO CLASS VALUES (25,'Object Oriented Design');INSERT INTO CLASS VALUES (40,'Inorganic Chemistry');INSERT INTO CLASS VALUES (50,'Organic Chemistry');INSERT INTO CLASS VALUES (80,'Early Lit.');-- Insert row for each PERSONINSERT INTO PERSON VALUES (1, 'Smitn','John');INSERT INTO PERSON VALUES (2, 'Jones','Sara');INSERT INTO PERSON VALUES (3, 'Smith','Lucille');--Insert row for each ENROLLMENTINSERT INTO ENROLLMENT VALUES (1,10);INSERT INTO ENROLLMENT VALUES (1,40);INSERT INTO ENROLLMENT VALUES (1,80);INSERT INTO ENROLLMENT VALUES (2,10);INSERT INTO ENROLLMENT VALUES (3,20);INSERT INTO ENROLLMENT VALUES (3,25);
I've built a fairly straight forward report in RS that looks normal in preview mode and in PDF format with out any issues.But when I export it to Excel report header is not appearing in each page.Any ideas as to why this is occurring?thanks in advance,Ramesh KS
Hello All, I'm simply exporting data from SQL to Excel via the Export Data feature in SQL Management Studio.My problem is I have a column aliased as [ID #] and when it exports it changes the Pound Sign into a decimal -> ID .SPACE(25) As [ID#], It's not a major problem but does anyone have any suggestions? Thanks, Kyle
Hi there I know its possible to export data from a gridview to excel. I'm looking to export data directly from a stored procedure at the click of a button.Somebody suggested using the following:insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C: esting.xls;', 'SELECT * FROM [SheetName$]') select * from table-nameWhen I tried executing the above lines of codes I got the following error message:"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server." If anyone has any idea whats wrong ... plssssssssssssssssssss ... let me know .... Thanks in advance.
I have a job which produces a daily report and exports it to excel. However, instead of overwriting the contents of the file the data is being appended to the file. Any suggestions welcome.
I'm trying to export to excel using dts, but using a stored procedure where i have a query using temporary tables (#D,#T,#R) and the result is a table with a variable number of columns (sometimes 3 columns and sometimes 10 columns)
I want to run Stored procedure that is returning recordsets and is using cursors/ temporary tables (MS Sql 2000 Server). The output of this SP is to be used to prepare an excel Report.
It shows me the data in the Preview, but asks me to define transformations. Further on the transformations, it does not shows up the source columns (although they were populated in the preview)
When I perform the same task using DTS Export utility, i get the following error:
Error source: MS ole db provider for sql server Error Desc : Null Accessors are not supported by this provider context: error calling CreateAccessor. Your provider does not support all the interface/methods required by DTS
1. I created a spreadsheet named test.xls with the column headings from my temp table 2. Saved and closed this xls 3. Tried to run the following:
USE [PEI]; GO INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:DELL est.xls;', 'SELECT * FROM [Sheet1$]') SELECT * FROM tblCFPooled GO
Where C:DELL est.xls is where I saved test.xls, tblCFPooled is the table I have populated in the firstplace and PEI is the database name.
When I run this the following error crops up:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.". Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
I then found something on a different site: http://www.mssqltips.com/tip.asp?tip=1202
So based on this I tried:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:DELL est.xls;', 'SELECT * FROM [T$]') select * from tblCFPooled
And obtained the following error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'T$'. Make sure the object exists and that you spell its name and the path name correctly.". Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
(I renamed the sheet1 to T if you are wondering where T$ comes in!)
Before running either of these 'export' queries I did as instructed in the first link: EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO
Which produced results: Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.
I assumed from that I had successfully enabled Ad Hoc Distributed Queries - Is that correct?
Both methods seem to throw up a similar error, an error which makes me think its some kind of SQL authentication issue.
Has anyone successfully exported from SQL to Excel - if so, any tips?!
(For the record I am using Management Studio, we are running SQL Server 2005, I am doing all this on a Vista Business machine, the SQL server is on a local server on our network here. )
I have a report when I run that report if the result is span for multiple page the table header is displaying for each page.When exported to PDf it is displaying the table header for each page.but when we export to Excel the table headers are not displaying.Any work around for this.
Hi , I have a simple report which i want to export to excel format. the are no groups in the report. I need each page of the report to appear as a seperate page in excel. currently when i export the report i get a single continuous page. is there any way in which i can do this ????
I have some reports that I used vbcrlf in. Whenever they are exported to Excel only the first line appears.
Ex: Name (&vbcrlf) Address 1 (& vbcrlf) Address 2 (& vbcrlf) City, State, Zip
This exports to Excel and displays the name line only. However, if I close the file that was exported and the extra blank screen, and hit export to excel again for a second time, all of the information is displayed.
I am having a problem exporting data to excel using t-sql. I need to create a t-sql stored procedure that exports the resultset of a dynamic query to Excel where the columns are not know beforehand.
I have been able to do it using bcp, but the problem is that the bcp method just exports the data to an tab-delimited ascii file with a .xls extension. Excel is smart enough to pick it up and parse it out into the individual cells, but the problem arises if the field value contains any special chars that may have been picked up when the user input the data by using copy & paste. Then the columns/rows are skewed for the record that has the field with the special chars in its field value.
I have tried using the openrowset method and if you know the fields(cols) that you want to retrieve, it works great. All you have to do is pre-create an excel file with the first row containing the columnNames for each column. But for dynamic queries, this is not an option. I have read somewhere that you can do a range, but have not been able to figure out to do that yet. You would have to pre-determine the number of columns & rows and figure out the range. Columns A-Z may not be as big an issue, but then I would have to figure out the values for any columns if the column count exceeded 26.
Outside of using Ole-Automation, is there any way than I can achieve the results that I really need? It just seems like over-kill for something that should be a simple task. If it cannot be done outside of Ole-Automation, is there a good example that someone can point me to?
Hi all, I have a SSRS Report which I have built using only a table. There are many table header rows, detail rows and table footers rows. For the layout of the report, I have merged many Table columns as well.
So when I am exporting this report to excel, it renders the report as it is but I am not able to manipulate any data in the excel till I do a lot of cleaning up. I have to remove the table headers, footers and also unmerge the merged columns etc.
My users are asking that they need a way throught which when they export the data it presents them with just the data which can be easily sorted, added, manipulated.
Is there a way of doing this. I thought about two ways of doing it.
Make another report in which only the data is shown and thus when this data is exported, then only the data gets exported to excel. But this is a very tedious way. Because as the number of reports increases, thi sis going to be a tough task to follow.
Make a web service or a plug in which will be connected to my web form. Then when the Export to Excel is clicked, this plug in will clean up the SSRS Report and export to excel in a way that only the data is exported. But I dont know much about web services or plug ins.
Could you please guide me that if it is achieveble. And what are the various ways in which this task can be done.
I really need this urgently. Any help or suggestion is welcome.
I have list within an list and we have section 1 thru 7. We display the list based the parameters seleted from report for some result section might start from 3 thru 6 . In such case when try to export to excel i get huge gaps as i am missing section 1 thru 2 in the report.
How do we avoid those spaces when exporting to excel,
I'm trying to use the export to Excel (Excel Destination) however, I wish to supply a dynamic piece of SQL to generate the data.
When I set the properties I just get an option to enter "Table or View", "Table or View as Variable" or "SQL Command".
The issue is that the table does not exist until the SSIS job is run and the columns will not always be the same anyway.
How can I use a simple dynamic piece of SQL (or stored proc) to get a recordset that is simply dumped out to excel - rows... columns... nothing more ?.
I have several similar reports all with a table with more or less columns depending on the report it self.
On one report I made the table rows with a bigger Height( because I have a small column with big data where I need to wrap)
It works OK on the Preview (Designer) it works OK on the reports Web Page (ReportManager) . it works OK when I export to PDF When I export to Excel the rows are not with the height I specified and not all the data is visible (I have to adjust row height in excel).
When our report exported to Excel, In the Excel file it is giving some extra columns between the data columns.How to avoid those extra columns.This extra columns causes problems when we want to sort one column it is throwing sort can't be applied on merged columns.
I am using SQL Server CE as a backend to a dekstop application. I would like the ability to export some of the SQL Server CE tables in my databse to MS Excel. Is this possible?
