• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

Exporting Related Data From a Dataset

Hi

I am exporting data from a dataset into excel (.xls) format. At the moment this is just a test extract from the Northwind test database consisting of Customer,Orders and Order_Details Tables. What happens when I open excel is that three spreadsheets are created one for each table. However when I export a report from MS Reporting services my data hierarchy is maintained within excel. Could someone advise me on what to do to maintain the data hierarchy when exporting data?

Thanks

Paul G
0
pgilfeather
Asked:
pgilfeather
  • 5
  • 2
1 Solution
 
rockiroadsCommented:
what code u using for exporting?

in that code, check to see if u can specify a sheetname
0
 
rockiroadsCommented:
sorry, what code = what language and what method
0
 
pgilfeatherAuthor Commented:
VB.NET or C# would be nice.

Thanks
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
rockiroadsCommented:
here is an example

http://www.dotnetspider.com/kb/Article950.aspx

uses excel automation like old vb does

u should then be able to specify what sheet u want to write to

Worksheets(1).Select()

I think u can also do Worksheets("sheetname").Select()
0
 
rockiroadsCommented:
Im assuming this is what u mean, u want all exports in one spreadsheet. If not, could u expand a bit further on your requirements
0
 
pgilfeatherAuthor Commented:
rockiroads

What I am looking for is my .xls file to open with as many subdatasheets as necessary. So that it is possible for a user to drill down into the hierarchy levels of data.

example

You can have the following format in excel

 + RootTableRow1
 + RootTableRow2
 + RootTableRow3
 + RootTableRow4
 -  RootTableRow5
           +  ChildRowA1
           +  ChildRowA2
           -   ChildRowA3
                       +  ChildRowB1
                       -   ChildRowB2
                                   ChildRowC1
                                   ChildRowC2
                                   ChildRowC3
                                   ChildRowC4
                         
       





0
 
rockiroadsCommented:
Ive not seen an excel spreadsheet like that, are your +/- expandable/collapsable buttons?

The only way I could think of is if you write your code to get the data from 3 tables

these queries u run, can u run them as union statements?
if not, could u put the results of these 3 queries into a temp table
then write your code to query the temp table (or union if u can manage that)

then as you write the the excel object, if a child row with children, write to next column, if not, go back a column
Put the logic in the write to the excel object
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now