.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

SSRS Data Extension

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

I hope so I am not the first one who has this requirement. I have the requirement that on the based of the users input I should get the value for another field. Sounds easy but this value should be get from combination of activedirectory and StoreProc.

I have a custom Project that extends ReportingService.Interfaces ie IDB...

1) Create a Connection string based on what input the programmer has set during creating datasource while creating the report. -- COMPLETED and Working fine

2) Based on the commandType -- Send the Command text to the function that will call the storeproc and also active direcotry and get the values  -- This is working fine  only when the CommandType is set to TEXT and "select * from Locations" which will give @StoreNo

But Now I have to pass the storeproc with the parameter ie in the text it will be "exec uspselectdeptByLocations @StoreNo"  WHAT SHOULD BE DONE... How can I pass the parameter value to this custom dataextension. I know this is not at all good explanation but I need help.

My custom dataextension should accept any commandType with dynamic parameter value.



View Complete Post

More Related Resource Links

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

How to find a rownumber in a matrix in SSRS and use it to manipulate data

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

SSRS custom security extension behind a router fire wall not working

I have a instance of SSRS 2008 installed (we are not using IIS) with custom security extension which accepts a application user ID and password and sends back a sql authorization cookie back to client for logging into SSRS.  The (SSRS) server is behind a router,  SSRS is configured on port 81,  Port 81 in router if forwarded to the SSRS server. Here is the problem If I am inside the router firewall (RDP'ed to the machine) I am able to access report server (http://xx.xxx.xxx.xx:81/ReportServer) and reports manager (http://xx.xxx.xxx.xx:81/Reports) without any issues. If  am outside the firewall and try to go to report server page (http://xx.xxx.xxx.xx:81/ReportServer) from browser and try to login it works (I am getting the cookie back and authentication goes through fine).   But if I try to access reports manager (http://xx.xxx.xxx.xx:81/Reports) the login page shows up when I enter user ID and password and hit enter to authenticate it comes back to the login screen (I am not gettint the cookie back from SSRS). Any help is highly appreciated on how to debug /fix this issue. Note : if I first login to Reportserver (which sends the cookie back) and in the same session I login to Reports Manager I am able to get into Reports Manager page since I got the cookie when I logged in to reports server page already.  When I

ASP.Net Dynamic Data - DropDownList Field Template with AJAX Cascading DropDown Extension

Hi all,   Have spent hours trying to create a Dynamic Data Field Template holding a DropDownList which is extended by the AJAX Cascading DropDown extension. Final form holds multiple cascading dropdown boxes. User selects Country (Land) then Postcodes, Counties (Provincie) are populated. Once postcode is selected then towns (Plaats) are populated.  The problem I am now facing is that the viewstate for the dropdown lists is not being saved/restored after an insert action or custom validation error. So basically the dropdown lists are being reset to their default values. Some code (if missing parts, just ask and I will post them) - Don't mind the code structures etc... needs some refactoring :) Attribute I created:using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace Data { [AttributeUsage(AttributeTargets.Property | AttributeTargets.Field, AllowMultiple = false)] sealed public class CascadingDropDownParametersAttribute : Attribute { //public string TargetControlID; // The ID of the DropDownList to populate. public string Category; // The name of the category this DropDownList represents. public string PromptText; // Optional text to display before the user has selected a value from the DropDownList. public string

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

Simple Data Extension

Hi I have a requirement to apply security filtering (column and row level) above and beyond the capabilities of SSRS Model security. in 2005 and 2008 I achieved this by creating a data Extension and tweaking the Command text and execute reader methods, and all was Well with the world. Now all I wanted was a bit of customisation of the SQL data Extension  (SQL Connection Wrapper)... of course its Sealed so couldn't inherit, so I composite / wrapped it... and all was well with the world......  Now along comes SQL2008 R2 .... and someone has discovered the internal Keyword !  now everything is locked down.  the Constructor of SQL Connection Wrapper  is internal. Luckily my code is not yet producion code, and I know Microsoft are completely within their rights ... but why!!! ... all is not good with the world, I doubt I am alone in this ! Any suggestions how to implement a minor customisation to SQL data Extension, without reimplementing the whole of the dataExtensions DLL ! Any chance the decison might be reversed ?  regards Iain   

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...

Hiding rows/columns completely in SSRS tablix even when they have data

I have several columns and rows in my tablix.  I don't want to see all of them at once.  I am using a parameter that controls the visibility of the columns or rows of the tablix.  The issue is that it hides but the white space is left.  How do i take out that white space?

a way to submit data from ssrs to database and commit?

Hi, I want to know a way to submit data from ssrs to database and commit? if yes, how? Thank you Francis sZEfsze88

Does Microsoft plan to enhance SSRS to allow access to multiple data sources?

We have the need to join multiple data sources into one table in SSRS.  At present, as I understand it, this is not a part of current delivered functionality.  We get around it by creating links withing the source database to access other databases.  The links themselves have their own set of constraints and performance issues. The other option is to use sub reports.  It would be nice to be able to define connections to disparate databases to join data from multiple data sources.  Does Microsoft have plans to deliver such functionality in an upcoming release of the SSRS product?
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