.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 find a rownumber in a matrix in SSRS and use it to manipulate data

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
Hi, I have an MDX query which returns me a dataset. It is something like this. Country Measure 1 Measure 2 Country 1 99 100 City 1 45 50 City 2 40 30 City3 14 20 Now in the actual report I need to show the data for the City Rows using a logic like City.Measure - Country.Measure. This has to be dynamic as I dont know the number of rows in my report. I have taken a tablix and bound my cells to the appropriate measures. My problem is to write an expression for the cell such that if it is the 1st row (Country row), then it should directly take the value, else it should do a logic like City.Value- Country.Value (in ex. for City 1, Measure 1 data should be -44 ). Can someone help me in writing the expression. I checked on net and saw that we can use the RowNumber function, but I could not use it like =iif(Rownumber=1, "1st Condition", "2nd Condition"). Any help will be appreciated. Thanks, Suman

View Complete Post

More Related Resource Links

Easily manipulate data with the GridView control in ASP.NET 2.0

Last week, I introduced ASP.NET 2.0's GridView data control and covered the basics of using it. This week, I go a step further by going into the details of working with the data contained in the control. This includes viewing, editing, and deleting data. Thankfully, the GridView control makes it easy to utilize these features. Before diving into these topics, I begin with an examination of the different field types supported by the GridView control.

Real-World XML: Manipulate XML Data Easily with the XPath and XSLT APIs in the .NET Framework


XPath is emerging as a universal query language. With XPath, you can identify and process a group of related nodes in XML-based data sources. XPath provides an infrastructure that is integral to XML support in the .NET Framework. The XPath navigation model is even used under the hood of the XSLT processor. In this article, the author reviews the implementation details of the XPath navigator and the XSLT processor and includes practical examples such as asynchronous transformations, sorted node-sets, and ASP.NET server-side transformations.

Dino Esposito

MSDN Magazine July 2003

Real-World XML: Manipulate XML Data Easily with Integrated Readers and Writers in the .NET Framework


In the .NET Framework, XmlTextReader and XmlTextWriter provide for XML-driven reading and writing operations. In this article, the author discusses the architecture of readers and how they relate to XMLDOM and SAX parsers. He also shows how to use readers to parse and validate XML documents, how to leverage writers to create well-formed documents, and how to optimize the processing of large XML documents using functions to read and write Base64 and BinHex-encoded text. He then reviews how to implement a stream-based read/write parser that combines the functions of a reader and a writer into a single class.

Dino Esposito

MSDN Magazine May 2003

System.Data.OleDb.OleDbException: Could not find installable ISAM.


Hi, i got the following error while i am trying to get my web application to run on windows server 2003. I've installed 2007 data access components on windows server 2003 but the web application does not seem to work. The codes that i use is as follows:

string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + address + ";Extended Properties=Excel 12.0;"; OleDbConnection objConn = new OleDbConnection(strConn); string strSQL = "SELECT * FROM [Sheet 1$]"; OleDbCommand objCmd = new OleDbCommand(strSQL, objConn);

OleDbDataReader oddReader = null;


oddReader = objCmd.ExecuteReader();







I've read a lot of forum posts and almost all of them mentioned ab out the connectionstring being wrong. However, when i ran the codes on my local machine, there were no errors. The error only show when i was running on windows server 2003.

I've underlined where the error is pointing to. Please advise. Thanks

Data Mining solution - SSRS Problem

I have created a Data Mining - Time Series solution and I am getting proper results from SSAS(Mining Model prediction tab) & SQL Management Studio query. However when I try to create an report using SSRS, In the field where I am expecting the predicted value, I am getting "#Error" Any suggestions as why this is happening.

Data validation for datetime parameter in SSRS

Hi,   I wanted to know more about validation of SSRS parameters. I have a simple report which has a parameter called startdate of DateTime datatype. The datetime parameter in SSRS takes manual input as well. So, the user can enter any junk value. I want to ensure that the input parameter is in correct format and I want to display an error msg when the format is incorrect. My report has the following VB code for validation:   Public Function Validate( ByVal startdate As String) As BooleanIf IsDate(startdate) = True Then Return TrueElse Return FalseEnd IfEnd Function   And my report has a textbox which has the expression property set to; =Code.Validate(Parameters!startdate.Value) the textbox on the report has to display if the entered date is valid or not.   But, when i enter an erroneous date, SSRS doesn't render the report and throws a generic error. This happens even before the code written for validating the parameter executes.   Also couldn't find a way to disable the manual input for the datetime parameter. Even that would solve the problem.   Another alternative was to make the startdate parameter as string, but i want the calendar control button to be provided for the user.

SSRS 2008 Export to PDF fails for large data with System.Exception: Parameter is not valid

Hi, We are working on Range Bar Chart using SSRS 2008. The report has huge data. It is properly displaying the data, but when we tried to export to pdf it is failing with the following exception. Exporting to excel works fine. Also if the report has small/medium data it is property exporting to PDF. It is failing only in case of huge data (we have custom page size of 28" X 14"). Of course the report has lot of expressions we used to customize the colors/text as per our requirements.   Server Error in '/Reports_SQLDEV2008' Application. Parameter is not valid. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Exception: Parameter is not valid. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:   [Exception: Parameter is not valid.]   [Exception: An error occurred during rendering of the report.]   [Exception: An error occurred during rendering of the report.]    Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream ou

Multivalued parameter & Data driven subscription for SSRS 2005

I need to create data driven subscription for our reports. Most our reports have multi valued parameters (7-12 parameters with at least 4-5 with multiple values)and need to be send to a users between the range of 25-200. So standard subscription is really not a choice.I have gone through most of the threads before. Question 1:Does any one have a solution for the SSRS 2005 regarding passing mutiple values for a parameter for the Data subscription table. If yes, could you please share the information. Question 2:I found some solutions using the SOAP API. Can any one explain that in detail- how we can do it ?Reference:http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/8ab615b4-1373-4258-bf49-c2843cfea8e9/http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=516735&SiteID=1 Question 3:Here is the other solution I found in one of the postsApparently passing in values like {A, B, C} only works when you configure the parameter values not to come from a dataset so that SSRS doesnt check for ValidValues.Reference: http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/b2c50aea-2032-4025-a155-306c00fcb856/Any commends about this solution?Thanks

SSRS 2008 R2 Dataset Field Value Property Data Type not numeric

We're building SSRS 2008 R2 reports using SSAS as data sources.  There are several occaissions where the data types of certain dataset field values that represent cube measures are not numeric.  As such, when these fields are used in an aggregate function they throw an error.  Is there a way to remedy this in the dataset? Thanks for your help.

SSRS 2008 R2 Data Source View Wizard does not recognize unique key constraints on views when using O

Hi, SSRS Data Source View Wizard does recongnize Primary/Foreign Key relationships from oracle datasouce tables, but does not recognize unique key constraints on views. How can I solve this problem. Thanks

SSRS 2008 Matrix - Cannot get average formula or growth formula to work

I have a report in Excel that I'm trying to replicate in SSRS 2008.  The report looks like this:   Priority 08/21/10 08/14/10 08/07/10 07/31/10 07/24/10 07/17/10 07/10/10 TOTAL Average Customer Name Code # of Lds # of Lds # of Lds # of Lds # of Lds # of Lds # of Lds  # of Lds   Check  #N/A            4            4            4            8            4            9           -            33          5       83%  A2             1          -            -            -            -            -           

Snapshot report with matrix sometimes does not display data ... rerun report and it does

Hi All, I am having a strange problem with a snapshot report that is scheduled through Report Manager and runs once a day at approximately 18:30 each evening. The report contains a column chart with a textual category, numeric data, and a date series. Above the chart, is a matrix containing the value of the last bar in each series, and the average value of all bars in the series.  Here's a screen capture of what the report looks like: http://img832.imageshack.us/img832/185/wipu.png The problem is that sometimes the matrix is missing.  The chart still displays properly and all the data is correct.  If I rerun the report manually, and save a new snapshot, the matrix re-appears. For the past 6 months this report has been running flawlessly.  In the past 3 weeks, the problem has occurred twice.  I don't know why. Why would the matrix disappear?  What is causing this, and how can I fix it?  Any ideas? I'm using SSRS 2008. Thanks in advance for any insights. Regards, --DanDJAnsc

Choosing the data source dynamically in ssrs

I have a report which pulls data from different database and servers based on date parameters. For instance Server1 and Table1 has the most recent data for the past 3 months and Server2 and Table1 has the archived data, so if a user is issuing the startdate & enddate within the recent 3 months it has to use the datasource1 else datasource2 based on the date parameters. How do we do this in SSRS?

Cannot create a connection to data source !!!! SSRS 2005

I cannot get this error resolve either for myself or any users, and I'm actually part of the Admin group!  Furthermore, a user with whom I'm tring allow to view this report keeps getting prompted for their windows username and password.  I thought that since this datasource was set to Windows Authentification that it would just pass it through? I'm pulling my hair out at this point: Screen Shots: http://www.webfound.net/datasource_connection.jpg http://www.webfound.net/datasource_connection2.jpg http://www.webfound.net/datasource_connection3.jpg Also As far as I know, I've given sufficient permissions to the right logins and right users on my SQL Databases and related stored procs that the datasets (not datasource) run An error has occurred during report processing. Cannot create a connection to data source 'datasourcename'. For more information about this error navigate to the report server on the local server machine, or enable remote errors

Need to find File Share Excel Rendering time--SSRS 2008

we are using SSRS 2008 file share subscription to generate EXCEL reports,Requirement here is need to find the file rendering/completion time in order to send the excel file as attachement using xp_senddbmail. Your help is highly appreciated-- Sathish

How to align data in Chart legend in SSRS 2008?

Hi,I have created a chart of type 3D pie.  I put a String field for Category and Sum(numeric field) for Data.I wanted to show the legend as Category field and Data field and it's displaying as i set.Now how to make alignment of Data in legend, so that my numeric fields should align Right and String field should align Left.  I can make whole legend alignment but I want to make only the numeric field in the legend to align Right.Is it possible or there any work around?Please let me know.

SSRS 2008 - Data Source cannot be found in Preview

Hello; Something weird is happening to me - Never happened before on 100's of reports using Shared Data Sources. I have a Shared data source defined and when I try to preview a new report, I get a message saying that "The data source 'ProjectList' cannot be found" When I created the dataset, I pointed it to the shared data source. I can preview the data when I run my query from query designer, so it's obviously seeing my shared data source. If I use an embedded data source, the report renders just fine. I'm using SSRS 2008R2 BIDS to develop the report - the data source is hosted from a SQL Server 2008R1 database. Anyone ever seens this? How'd you get out of it? ThanksThanks Ron...
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