.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

How to use Dynamic Set to fix Multiselect Issue in Excel

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


I have a calculation that fails within Excel 2010 when I select more than one date from my dimension . I have been told (by Deepak) that this is because Excel uses a subselect instead of a where clause (which Excel 2003 & earlier use). I have tested this by saving a new file as an .xls then open it in compatability mode and create my pivot table as an older version; in this instance, my calculation is (mostly) okay. I was refered to this article for help: http://sqlblog.com/blogs/mosha/archive/2007/08/25/mdx-in-katmai-dynamic-named-sets.aspx

Please see my next post for more info (as I have had formatting issues in the past with my first posts).


View Complete Post

More Related Resource Links

SSIS issue with dynamic excel sheet name

Hi Team, I am creating a Excel file dynamically using a script, excute sql and then the dataflow. I am not able to cretae a excel sheet name without an underscore exisitng file: "team data" even though am giving the table name in excute sql task(which is excel sheet name) as "team data" it is creating the name as "team_data" because of which the package is failing? I am able to achieve this with an underscore, as it is a client requirement to generate wihtout an underscore am posting this question. Is this an issue with Sql server 2005? Thanks, Eshwar  

export to excel issue


Hello ,

I have a browser enabled form which i published to sharepoint ..now i want to export the contents of the form to excel..i have 1 repeating table and two repeating sections ..whenever i go to export to excel and then choose just one repeating table or a repeating section or any option other than export all form data i get an error ...first the progress bar just freezes and then when i end the task through task m anager i get a msg that microsoft excel could not start.make sure excel is installed correctly and then try again....

Can somebody tell me how to debug this..i have tried the export of a form containg repeating table and sections and it works good ..so that means there is some problem with my form..so now can somebody give me an idea as to what can the problem be and how can i debug it.


ASP.NET Excel Export Issue



I am trying to export data from SP to a .xlsx sheet. Total no of rows is more  than 15000 with total of 13 columns.The data is generated with the help of SQL Server SP. Till this point it is working fine. However while exporting to excel I do not get any data , the excel  has blank content.

If I have records less than 12000 it generated the excel report with  relevant data. However as row increases the report is blank.

Also the excel is generated through the code but when I deploy to server and try to generate it generates a blank excel. 

 Please advice what needs to be done. I have tried multiple options but none works.

An issue of using UDF in Excel Service

I am using Excel Service and it call an UDF(User Defined Function) to get data from a list. Currently I come accross following issues. Any one can give me suggestions are very appriciated. 1. In the UDF, if I marked the method as "ReturnsPersonalInformation=true". The data cannot be shown correctly, all cells appear "#Value?". 2. When I fist time upload a excel file to the Document Library, then open the excel on browser. All cells appear "<nativehr>0x80070005</nativehr><nativestack></nativestack>". When I manually click "Calculate Workbook", then the data comes correctly. Thanks again for any hints.

Issue while exporting to Excel

Hi All, We are having report in SSRS 2005, everything is working fine while Running the report on BIDS as well as on Report Server, but as soon as we export the report to Excel it is creating issue(S) not just a issue. First - I'm not able to see "Footer" in Excel, although it does exist in BIDS as well as Report Server. It is visible while doing Print-Preview in Excel, why so is it a bug, is their any work around to make it visible without doing Print-Preview, please do let me know! Second - It is regarding "Header", I have couple of text boxes in my Report Header, as soon as I include text boxes in Report Header and when I export to excel, the rows & columns height as well as width is getting disturb (meaning some getting compressed and some getting expanded), so to make it look attractive we have to manually arrange in proper order before delivering to our end-user, Can anybody tell me why rows & columns are getting disturbed and also how to arrange with exact height & width without any manual interpection, please help me out, this is getting me crazy?   Thanks Regards, Kumar

Dynamic Column in Excel Source

Hi,I am having Excel Source Which needs to be imported into Sql Server Table using SSIS.In the Excel Source I dont have Month and Year Column.But in Table I have Month and year column and both the columns are Primary Key columns.So i am not able to Import data from Excel to Table.So is there any possiblities to add Columns Dynamically in Excel source inorder to  get the Year and Month

VB.net issue when using dsoFramer to display Excel Worksheet

Hi to all, I'm using the dsoFramer control to display an Excel file , the problem is that suddenly it stops working fine and I'm receiving the following message. "Unable to display the inactive document.  Click here to reactivate the object" So I have to click on the Excel Sheet to start showing the information... that's awful for me and my tool users? anybody knows how can it be fixed. FYI, I use Visual Basic.net 2008 Express Edition

How to have dynamic column names in an Excel Destination?

I have a situation where both the column names in the source (SQL) and destination (Excel) are being created dynamically. The number of columns, their positions, and datatypes remain the same. How do I change the OLE DB Source and Excel Destination meta-data to handle these changes at run-time?

ASP.Net Export Excel issue

Is there file size limit while exporting excel 2007 file using Microsoft.ACE.OleDB driver from ASP.Net? I can export file with any size (like 10 MB) when same code is used with Console app, but with ASP.Net page I am only able to generate file of around 500 Kb. When data exceeds this, it can not insert into the file. Can someone help?

Issue with Excel automation in C#

Hi, I am dynamically creating an excel file with some list options using C#. There will be 3 or more columns with drop down list. The issue is that in some columns there will not be values in some rows. But the drop down list shows all the empty column values also. Is there a way by which I can filter the excel column with all non empty values? Sample Code  Range r = (Range)ExcelWS.Columns.get_Item(c.Ordinal + 1, Missing.Value);r.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, Missing.Value, "=$B:$B", Missing.Value);r.Validation.ErrorMessage = "Please select from available options";r.Validation.IgnoreBlank = true;r.Validation.ShowError = true;r.Validation.InCellDropdown = true;  

Issue with valiadation of excel connections

Hi Team, I have an issue with a package I have a package which takes input and output file names dynamically from variables.  I made the delay valiadation of excel connection as true. after 1st execution when i close and reopen it again it is showing errors and execution is failing from second time. Thanks, Eshwar.

SSRS 2005 Render Format Excel Issue with Time Field


I have a report that uses a datetime field but gets populated only with the time. So, in SQL db, it gets stored with a default date value of 1899-12-30 12:01:01 with the correct time filled in. Report in SSRS Preview mode displays the value just fine when I use the Time format in Layout mode. I build and deploy and able to view the report in a web browser with no problem displaying the time field. But when I subscribe to the report and choose Excel render format, when I open up the Excel file from the email that was sent, I get negative decimal values. I have tried every possible Time format in SSRS but I get the same result. I also tried just the default and still gives me negative decimal values that show up as ####### in Excel.

Any help will be greatly appreciated. Thanks

Create dynamic dropdown list when we export data to excel using SSRS.


Hi All,

I have assigned a work to create a report, that should render in excel with dropdown list. i.e. report should contain a input parameter as a table names, when user selects the table name, report should show only the field names of the selected table. Some of the field name have look up values that values should be displayed as dropdown when we export to excel.

Can any one have any idea how to proceed with this report?



SSRS 2008 formatting issue - different in visual studio preview/pdf/excel vs. internet explorer


The formatting of the report is perfect when viewed in Visual studio and also after exporting it to pdf or excel. But when I deployed my solution and opened it on IE/Firefox there is a lot of problem in formatting. Specifically the row height is giving me a problem. I manually entered them to be 0.1in and made the CanGrow field as false to force that row height but some rows in the tablix expand ( to 0.15in, which is the height of my header). This is true for a matrix as well. Is there is a feature to lock the row height in IE o any other web browser?


Creating a dynamic excel file


Is it possible that i can create a dynamic excel file (destination)


ex, i want to create a Dyanamic Excel destination file with a filename base on the date

this will run on jobs. Is this possible?

11172006.xls, 11182006.xls

Excel Export: Indentation/Padding issue


Hello Jerry and fellow RS forum members,

I noticed this section with respect to Excel export limitations from SSRS.

Show and Hide

Microsoft Excel has limitations with how it manages hidden and displayed report items when they are exported. Groups, rows, and columns that contain report items that can be toggled are rendered as Excel outlines. Excel creates outlines that expand and collapse rows and columns across the entire row or column which can cause the collapse of report items that are not intended to be collapsed. In addition, Excel's outlining symbols can become cluttered with overlapping outlines.


Now I have this basic report with a table data region having multiple groups. Each Parent Group provides a toggle capability (+/-) in front of it. Each text box for the grouped item has left padding set to provide indentation. Example:

+ Group A

    +  Group B

          + Group C

                   Detail data

The report displays perfectly fine within the HTML viewer and PDF export with proper indentation and padding obv

Excel Services connection issue


Hi All,


We have an spread sheet which is pulling data form SSAS. The spread sheet works fine when refreshed individually but when we publish it to SharePoint, we can't refresh it and the following error shows up. What account is used to connect to SSAS when Exl. services is connecting to SSAS? Any idea how to fix this?


Thanks in advance.


An error occurred during an attempt to establish a connection to the external data source. The following connection failed to refresh.


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