.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

OLAP and excel reporting

Posted By:      Posted Date: September 06, 2010    Points: 0   Category :ASP.Net
Hi guys:I have an OLAP database which I need to create a reporting facility for my users, Excel seems to be fine, I have a web site and need to place a link for users to donwload the reports.  The thing is that once the report is downloaded I cannot drill down or do any query operation because the report it is trying to connect to the remote server (of course once dowloaded it is outside of the network) It is there any sugestions on how to address this issue?, maybe storing the data in the excel sheet? what about using reporting services instead?Thanks in advance

View Complete Post

More Related Resource Links

OLAP: Build an OLAP Reporting App in ASP.NET Using SQL Server 2000 Analysis Services and Office XP


Many organizations analyze their business-critical data using Online Analytical Processing (OLAP) technology. OLAP-based data mining provides a way to query multidimensional data sets and drill down into the data to find patterns. ASP.NET and the Microsoft Office Web Components (OWC) enable Web-based OLAP reporting. The OWC controls include PivotTable and Chart components that can be embedded in a Web page and scripted by programmers. In this article, the authors build a Web-based OLAP reporting app using ASP.NET, OWC, and SQL Server 2000 Analysis Services to illustrate the process.

Jeffrey Hasan and Kenneth Tu

MSDN Magazine October 2003

Excel 2007 Pivot table OLAP- SSAS 2008 Error

Hi, I do have a strange issue with Excel 2007 pivot table report; the data source is SSAS 2008 cube. When refresh the excel 2007 report data using the 'Refresh All' or 'Refresh' button available under the Data menu, the below error is thrown. "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples." After some analysis, the report works well on the below workaround, 1. Remove all the calculated measures from report (3 measures in this scenario) 2. Remove one row label (dimension attribute) from report But the measures, attributes are valid one from cube and it is required fields in the report. So, understand that removing few items is not at all a solution. The below workaround also works. 1. Check the 'Defer Layout Update' option from the Pivot Table Field List window. 2. Click on the Update button After that Unchecked the 'Defer Layout Update' option and apply the filter, etc in the report. The report works very well. But every time, we need to use the above work around after open the excel report. I have NO clue on this issue. Please help to resolve this issue. Thanks, Jey

Generating Multiple Excel Sheet using Reporting Services 2005.

<p> Hi, I'm currently developing a program using ASP.NET 2.0 (C#) and i am using Reporting Services 2005. One of the report requirements is to export a data to an excel with multiple excel sheet. Is there other way to generate multiple excel sheet using the reporting services? if yes, i would like to ask a sample code... Thanks in advance, JP  </p> 

Performace point/Excel services OLAP cubes

Running Sharepoint 2010 Ent fresh install.  I have created a new site and performance point service as instructed in the following tutorial. http://blogs.msdn.com/b/performancepoint/archive/2009/11/24/deploying-performancepoint-2010-soup-to-nuts.aspx I have then uploaded an Excel document which pulls data from OLAP cubes.  The Excel doc as a standalone works fine.  After it has been uploaded users can open it as normal etc but when changing slicers and trying to pull info from the cubes I get the following error.. An error occurred durning an attempt to establish a connection to the external data source.  The following connections failed to refresh: "Name of out cubes here" I have been playing with the secure store and generating keys etc to no avail.  I have tried exporting the .odc file from the Excel document and uploading it to the data connections library/dashboard site but this doesnt help The anoying thing is that I had it working once and then the next day it failed for no apparent reason and even after a fresh install I cant get it to work again.  Any pointers or tutorials would be greatly appreciated! Andy

Convert from Number to Text- Exporting to Excel from SQL Reporting services 2000

I was trying to export a report which contains a number format. When I do that, all the numbers in excel will have a green small tag beside it saying "Convert from Number to Text". Is there anyway that I can change the format to a number when I export it to excel?

Excel Export Report with Parent Child Dimension OLAP





I have a problem with a reporting services 2005 report.

The data source is OLEDB for OLAP 9.

The report contains all the members of a parent child dimension. An example of the implementation is defined in the post following the forum msdn:

The report works fine in web. The problem is when the report is exported as Excel, the groups disappeared and the entire dimension is ragged down.

Normally toggled groups in reporting services are exported to Excel with the appearance '+' or '-' on the

Merging Multiple Excel files (exports from Reporting Services)


So we have Office 2007 on our system and I'm trying to use Excel Interop to take a number of Excel files, which are exports from Reporting Services, and combine them into one large workbook.  I'm using the code below which works fine to get all the information from all the files into one workbook but the colors of things goes absolutely haywire.  Anyone know why my colors are getting all out of wack?

protected void MergeFiles(string destinationFile, string[] sourceFiles, string guid)
                //1. Validate folder,  
                //2. Instantiate excel object  
                //3. Loop through the files  
                //4. Add sheets  
                //5. Save and enjoy!  


Password protected PDF/Excel reports using SQL Reporting services


I am generating a reports using SQL Reporting services 2005. I need to protect the protect the reports(pdf and excel) with the password while the user is exporting the report to to excel or pdf.

Is there any way to protect the report.

Thanks in advance...


SSRS Reporting - Exporting To Excel


I am new to SSRS Reporting 2008 and most of the reports I have developed required to export to Excel. This reports are embedded into ASP.NET, developed in C#. Now there is some questions I would like to ask:

1) When I am exporting to Excel, I would like to insert some comment in the some cells in the Excel (comment like where you see a red dot in each cell in Excel, and when mouse over you can see comment of this cell)

2) Is there anyway to put a filter to the header after export to Excel?

Excel 2010 OLAP Pivot Tables - Can a Date Dimension name be used as a Date not String?


Scenario:  Have built a SSAS 2008 cube and am using Excel 2010 pivot tables as the UI to access the data.  The end-users want to be able to treat the Date Dimension name value as an Excel friendly date, mainly to apply custom formats for pivot charts (such as mmm-yy or dd/mmm/yyyy) and to be able to sort ascending and descending.

Problem:  Although the Date dimension attribute is setup with 'mmm-yy' formatted text as the name and a datetime value as the value, Excel refuses to treat the dimension name as a date.  The result being custom formatting is ignored and pivot sorting results in text sorting only (Apr-00, Apr-01 etc).

Investigation:  Have tried several string combinations for dimension attribute name (even using the Excel datevalue numbers) with no success. Have found some earlier blogs which go part of the way in explaining.  It appears that no matter what you set as the name value you are always going to end up with a string label that can't be formatted or sorted in any other way other than text, as indicated by Darren Gosbell answer to a similiar question.

“...When you display a dimension attribute in an Excel pivot table what you are seeing is the name of each member. The name property of

Suggestion for web based Ad hoc OLAP reporting tool

Hi Guys,

I want a suggestion from you people regarding the best tool for OLAP reporting.

What I want to do is

Allow administrator to create OLAP based web report, which he/she can publish to users of his/her choice.
Similarly can create dashboards using the OLAP.

This needs to be done in web based application.

Can anybody suggest any 3rd party tool for this??

Thanks in advance

Cannot refresh OLAP-based data in Excel Services workbook in browser (SP 2010 / Project Server 2010)



In SharePoint 2010, Project Server 2010, Business Intelligence Center, upon attempting to refresh OLAP data in an Excel chart in the browser, the following error appears:

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

My ODC Connection Name

We have loaded and configured Project Server 2010 according to spec (http://technet.microsoft.com/en-us/library/ee662109.aspx).  Excel Services is configured according to spec (global settings, trusted file locations, trusted data providers and trusted data connection libraries).  The Secure Store Service is configured with the ProjectServerApplication target application. 

Non-OLAP data refreshes of spreadsheet data work fine in the browser.  OLAP data refreshes in the Excel client itself work fine.  Only the OLAP refreshes in the browser fail.  The problem occurs whether using a Microsoft ODC or one that we created (saved in a trusted location).

The ULS log has only one error, which is:

06/21/2010 13:47:17.86  w3wp.exe (0x1238)               &

Best solution for reporting OLTP/OLAP?



I am not sure if this is the correct place for this question. We have a reporting requirement. The OLTP database is SQL Server 2008R2. Number of reports required is 6 and not sure about the number of users to provide access. On an average 20k records are added per day.  Now as the number of reports is just 6, is it worth to create a multi dimensional database (OLAP)? Are there any OLTP reporting tools ? Any help would be appreciated.

Measure Group Not Found Error - when connecting to OLAP from Excel 2007



I had a measure group in my cube that I have removed.  Now when trying to connect to the cube using Excel for some slicing/analysis, I get an error

ERROR= the 112458261 measure group was not found. and Excel will not connect

How can I get past this error?  I have;

Deleted and reprocessed the spcific cube without luck

Deleted the entire database and rebuilt/redeployed and reprocessed the entire cube.

I have no references in my cube any longer to the old measure group and processing completes fine.

HELP! (please)


Securing access to specific tables for Excel reporting


I am relatively new to SQL Server 2005 and inherited it as configured.   What I want to do is improve reporting capabilties to users through a tool they are familiar with, Excel 2003.   As admin, this is not a problem and I can report on all the tables.   For a user, I want to restrict which tables a user has access to.   Not sure how.  I have read some documentation, security best practices and lots of Googling.

The SQL Server is configured with a number of databases with each having lots of tables.  The one database I need to use has the tables the user needs but also a lot of tables he should not be accessing.  They all share the same schema.  I want to restrict the tables he can see when importing the data into Excel.    Not sure how and am hoping someone can provide some constructive suggestions, whether it can be done at all, or guidance as to where I can find the answer.   I've also thought of using a view but am not sure nor can I find where I can it make it avaible through Excel.

Any advise will be much appreciated.    I hope this makes sense.


Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

Excel Automation in .Net

Excel Automation is a buzz word in both webapps and winapps. In the programming life (like Mr.Anderson neo) I guess almost every one could have come across this word. For others let me go further in detail. Excel Automation is automating some or all of the process involved in creating or updating excel worsheets.
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