.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

columns order reverses in matrix when exported to excel

Posted By:      Posted Date: October 12, 2010    Points: 0   Category :Sql Server

Hi eb

I haver a simple matrix with header that holds description for every column

in matrix. Lets say header looks like:

col1Desc col2Desc col3Desc.

My problem: when report is exported to pdf or excel columns order reverses! so now it is:col3Desc col2Desc col1Desc

The only workaround i found to keep columns headres in place was to place header in a table on top of matrix.

This is fine but now matrix itself has no header so i can't display the columns header in every page of the report just on the first one..

I would very much appriciate a hint for solving any of these problems

either prevent columns reverse in matrix header when report is exported

or some how force the column to appear on top of each page

when contained in a separate table.

Thanks alot!




View Complete Post

More Related Resource Links

Document Library "export to spreadsheet" doesn't export all columns in Excel 2003

Hi, we are working with MOSS 2007, Infopath Forms Services, KPIs and Excel 2003. There is a view for some data of the Infopath forms which has to be exported to Excel by our users, but some columns are not exported - without any error message. If I try to export it to Excel 2007 everything is exported as expected, but our users have to use Excel 2003. Any idea?

Why data F+20-20% become F20% When Exported To Excel

Hi,My data F+20-20% in Gridview when exported to Excel become F20%.I find no way unless to have " ' " at the begining and ending of it that is  'F+20-20%'  in order for it to be exported correctly.I have try to make the excel file column to be TEXT  format and the coding is as below but fail.The  exported result to excel is F20%Dim strStyle As String = "<style>.text { mso-number-format:\@; } </style>" For intTemp As Integer = 1 To ldstCompareResult.Tables(0).Rows.Count - 1 dgdResult.Items(intTemp).Cells(0).Attributes.Add("class", "text") Next lsFileName = txtModel.Text.Trim & "-" & txtRev.Text.Trim & "_" & _ txtModelTo.Text.Trim & "-" & txtRevTo.Text.Trim & ".xls" Response.ClearContent() Response.Charset = "" Response.Buffer = True Response.AddHeader("Content-Disposition", "attachment;filename=" & lsFileName) Response.ContentType = "application/vnd.ms-excel" Dim style As String style = "<style> .text { mso-number-format:\@; } </style> " Response.Write(style) I have try to solve it by using sql but fail. The  exported result to excel is F20% select ' '+

Sql Order by not working with decimal/money type columns

Error Message:- Cannot convert a char value to money. The char value has incorrect syntax. DECLARE @Order NVARCHAR(50) = 'asc', @OrderBy NVARCHAR(50) = 'Shape'SELECTtop 1 Row_Number() OVER(ORDER BY  CASE WHEN @Order IS NULL OR @Order = 'asc' THEN    CASE WHEN @OrderBy IS null THEN Shape    WHEN @OrderBy = 'Shape' THEN Shape    WHEN @OrderBy = 'Cut' THEN Cut    WHEN @OrderBy = 'Color' THEN Color    WHEN @OrderBy = 'Clarity' THEN Clarity    WHEN @OrderBy = 'Price' THEN FinalPrice    --MONEY    WHEN @OrderBy = 'Carat' THEN [Weight]   --DECIMAL   END  END ASC,  CASE WHEN @Order = 'desc' THEN    CASE WHEN @OrderBy IS null THEN Shape    WHEN @OrderBy = 'Shape' THEN Shape    WHEN @OrderBy = 'Cut' THEN Cut    WHEN @OrderBy = 'Color' THEN Color    WHEN @OrderBy = 'Clarity' THEN Clarity    WHEN @OrderBy = 'Price' THEN FinalPrice  --MONEY   WHEN @OrderBy = 'Carat' THEN [Weight]  --DECIMAL   END  

Dimensions order in excel pivotal table

The list of dimensions in excel pivotal table are in alphabetical order. is it possible to reorder the dimensins?

Hiding Columns (not column groups) in a Matrix, can it be done

Hi.  I want to be able to hide some of the columns of this report.  For example , I don't want to see the % of State Total for Total column, and I don't want to see the % of Location Total for the other column groups.  Can this be done?  Any help would be appreciated.  Thanks J I am using Visual Studio 2005   Total   Anaheim CA Arlington VA Atlanta GA Count % of State Total % Of Location Total Count % of State Total % Of Location Total Count % of State Total % Of Location Total Count % of State Total % Of Location Total Count % of State Total % Of Location Total USA   3 100.00% 0.02%   0.00% 0.00%   0.00% 0.00%   0.00% 0.00%   0.00% 0.00%   AB 2 100.00% 0.01%   0.00% 0.00%   0.00% 0.00%   0.00% 0.00%   0.00% 0.00%   AE 8 100.00% 0.04%   0.00% 0.00%   0.00% 0.00% 3 37.50% 0.10% 1 12.50% 0.07%   AK 32 100.00% 0.17%   0.00% 0.00%   0.00% 0.00% 3 9.38% 0.10% 1 3.13% 0.07%   AL 161 100.00% 0.85%   0.00% 0.00%   0.00% 0.00% 17 10.56% 0.54% 84 52.17% 6.11%   AP 3 100.00% 0.02%   0.00% 0.00%   0.00% 0.00%   0.00% 0.00% 1 33.33% 0.07%

How to read an Excel file and show data from 2 columns

Hi,I have an Excel file called Products.xls .I have Columns A and B, with the titles NAME and QUANTITY.The name of the sheet is SHEET1.The file has about 40 lines.How do i show these data on a Gridview or Listview ?Thank you.

Sharepoint export to spreadsheet gives two extra columns in excel 2007 named as type and path, is th

When i export a list in excel 2007, it creates two extra columns named as type and path. At the same time this behaviour is not exhibited in office 2000. Is there any way to remove these two columns without using code ? Is there any configuration settings required for this, to remove it on the fly ? Thanks Ravish Verma

List exported to Excel 2003

We are currently in the process of moving our Sharepoint 2007 server from one farm to another and the URL is going to change.  We have a lot of users who edit and update their data using Excel 2003 ( Using the Export to Spreadsheet option ).   Some of these spreadsheets have multiple tabs  and VBA code  for processing the data and generating reports.  There is no menu option that lets us edit the query to make the change to the URL. Is there any easy way to replace the URLs of these queries to point to the new URL? Vijay  

Change order of columns in a list


Hi there

I'm looking for a way to change the order of the colums in a list in SharePoint 2010. I only have access to SharePoint Designer 2010 for editing the site.

I already looked at the function to create custom views for lists. However this are only useful when viewing the list as a whole, however when you edit an existing, or create a new entry you will see

  • first: the columns in the order in which they were created.
  • and second: all of the columns. i cant find a way to make certain columns invisible for the user.
I already looked through the functions that are given to me by SharePoint Designer 2010 when editing the columns of a list and I also checked for a way to change the order of the columns when surfing the actual website with Internet Explorer.

Does anyone know how to do this? Or am I just trying to find a function that doesn't make sense because it's covered by another feature?

Thanks for your help!

Report exported to Excel 2007 is EXTREMELY slow once a filter is applied.


I am exporting a 12,000 row, 20 column report to Excel from SSRS 2008.  Once opened in Excel, everything is fine.  If I further filter the data through Excel (no external data connections) performance degrades.  If I filter down to say 20 rows, it nearly completely bogs down my entire PC.  I've emailed this spreadsheet to several others, who then experience the same issue.

If I export this same set of data from a different BI tool, such as Microstrategy, there are no performance issues what so ever.

Merge Columns Like Excel In Gridview


Hello everybody.

I have a excel file which is in structure a class routine:

                  | 9-10 | 10-11 | 11-12 | 12-01 | 01-02 | 02-03 | 03-04 | 04-05 |

tue             |           subject1                      |     subject2     |    subject3      |

wed            |   subject2     |                subject3                   |    subject1      |

thu             |            subject3        |     subject2     |             subject1          

Display columns in matrix based on a group even if data don't exist in resultset


I am trying to create columns in a matrix for data that currently does not exist.  I created a matrix that groups on office name (ROWS) and Month of Appointment Date (Columns).

For example, the matrix currently looks like this.....

  January February March April

SSIS cannot find excel columns when using SQL command to import

I'm trying to import an Excel file, but receive the following error during the process:

Validation is completed
[DTS.Pipeline] Information: Prepare for Execute phase is beginning.
Progress: Prepare for Execute - 0 percent complete
Progress: Prepare for Execute - 33 percent complete
Progress: Prepare for Execute - 66 percent complete
Progress: Prepare for Execute - 100 percent complete
[DTS.Pipeline] Information: Pre-Execute phase is beginning.
Progress: Pre-Execute - 0 percent complete
[Source - Individuals$ [1]] Error: Column "Last Name 2" cannot be found at the datasource.
[DTS.Pipeline] Error: component "Source - Individuals$" (1) failed the pre-execute phase and returned error code 0xC0202005.

My Excel Source is using "SQL command" data access mode:
Select * from [Members$]

Is there something that I'm missing when using the 'SQL command' mode? Thanks in advance for any help.

Problem with List Columns in Foundation 2010 when Importing from Excel


This is a pretty detailed issue. I will give you all the steps I am doing and hopefully there is a solution.

I recently updated our intranet site from WSS 3.0 to SharePoint Foundation. In WSS 3.0 I added a few of the application templates to use, including Asset Tracking. When I moved to Foundation I decided not to use those (and not sure if I can anyway) and exported the Asset Tracking data to Excel, then imported it to Sharepoint Foundation as a list.

Here is the issue I am having: when I imported from Excel all but one of the columns was created as "multiple lines of text". This means I cannot sort by these columns. I thought I could create similar columns as single lines of text, export back to Excel, copy the data, and import back. This changes the columns again to multiple lines. I even tried just typing in the data as a test and same problem. As long as there is no data, the columns will remain as single line of text; but as soon as I put data in I get this issue.

I can cofirm that these columns do not have more text than should be available in a single line. Any ideas of what I can do to make this work? I am at my wits end on this.

Thanks for any and all help.

Hiding Columns in Matrix (RS 2005) Report Based on Columns' Values



Is there a way to hide automatically columns in a Matrix type raport in RS 2005.  The columns I want to hide have only 'N/A' values in them because there is no data available to populate them.  Here is the example:

  Fall 2010 Fall 2009

Excel spreadsheet properties push to sharepoint library columns


Hope this is the right forum; if not, please direct me to the right one.

In Excel 2007 I am filling in a document property programmatically in an Excel 2003 spreadsheet, and creating/filling a custom one.  Just FYI, I use the following VBA code:

    ActiveWorkbook.BuiltinDocumentProperties("Company") = "TEST COMPANY NAME"
    ActiveWorkbook.CustomDocumentProperties.Add "Manager's Name", False, msoPropertyTypeString, "TEST MANAGER NAME"

Then I save the document to a SharePoint library.  Right now it is saved as a 2003 version spreadsheet (.xls).  There are a Company column and a Manager's Name column in the SharePoint library, and Microsoft's online documentation says the properties should populate the corresponding columns in the library.  But they don't!

That's my problem--getting them to populate.  Incidentally, thinking that the spreadsheet version was the problem, I have tried saving these documents both as 2003 spreadsheets and 2007 spreadsheets in my code, but the SharePoint columns never populate either way.  The only way I can do it is by manually filling in the document property from the UI.

Does anyone know what I'm doing wrong, either in Excel or SharePoint or somewhere in between, to cause this?  Is there a non-code solution to the problem?  I can do some C# code if I h

How to read excel in C# with custom columns

I am reading read excel file in C# like

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + txtPath.Text.Trim() + "';Excel 8.0;HDR=YES;";

        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

        DbDataAdapter adapter = factory.CreateDataAdapter();

        DbCommand selectCommand = factory.CreateCommand();
        selectCommand.CommandText = "SELECT * FROM [MSDN$]";

        DbConnection connection = factory.CreateConnection();
        connection.ConnectionString = connectionString;

        selectCommand.Connection = connection;

        adapter.SelectCommand = selectCommand;

        DataSet cities = new DataSet();


        dataGridView1.DataSource = cities.Tables[0];

But above code is not reading correct values in case of cus
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend