.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 multiple resultset return by SP in a Report

Posted By:      Posted Date: October 05, 2010    Points: 0   Category :ASP.Net


Can we use different resultset in our Report. Like if we have written two select statement in our SP, how can we use the different result set to bind them in our Report.


Thanks in advance. 

View Complete Post

More Related Resource Links

Linq to sql does not return appropriate multiple result sets with "If exists" conditions in the stor


Hello All,

I have a stored proc that returns 4 resultsets, they are in the format.

ALTER PROCEDURE [dbo].[pGetManagerEmployeeSummaryDummy]

    @ManagerStaffID int,
    @PeriodID int



IF EXISTS (select 1 from dbo.temployee e
    inner join dbo.temployeedetail ed
        on e.staffindividualid = ed.staffindividualid
        and ed.periodid = @PeriodID  
    inner join Incentive.tRole r
        on rm.IncentiveRoleID = r.RoleID
    where ed.ManagerIndividualStaffID = @ManagerStaffID
    AND r.RoleID = 1) --Closer
        SELECT e.StaffIndividualID, e.LastName, e.FirstName, r.RoleName, e.xname, e.yname

from dbo.temployee e

inner join dbo.temployeedetail ed
            on e.staffindividualid = ed.staffindividualid
            and ed.periodid = @PeriodID

where ed.ManagerIndividualStaffID = @ManagerStaffID

One report >> Multiple Linked Reports in different folders?

I love Linked Reports in SSRS because of my ability to change one report in one project and post changes to one location on my SSRS server and then have those changes take effect in my linked report.  Today however I was asked to make one of those reports which is already liniked elsewhere to be linked to two locations and I was dismayed to find out that there is a chance that its limited to one linked report for any source report. Can someone tell me this isn't so and show me a way around this?  I don't want to have to post my RDL to multiple locations unless its absolutely necessary. I'm using SQL Server 2008, SSRS2008, Standard Edition. Thanks, Keith

What are my options when I need to create a report with data from multiple sources?

Hello all,  What are my options when I want to create a report that will get records from multiple data sources based on data from a parameter? The user will enter the customer ID, I have to check that ID against 5 different data sources. Have you guys done anything similar? Should I leverage SSIS on this request, adding an extra layer of complexity? Should I do this all from SSRS? What are my options?

A tsql report with multiple counts and pivioted description attributes

Hello,   I have an assignment in which I am to prepare a report displayed in excel to return counts of various attributes in the database. However, the count is a bit challenging because it requires counting certain attributes in a pivot format illustrated below. The counts for each category by recruiter, rolled up by Division, within the specified time frame.   The tables that I would have to deal with are as follows:   User: Has the number of Accounts…user accounts with no Recruiter are unassigned Recruiter: Has the recruiter information LoginHistory: Has a record of all logins EForm: Has the various forms, for example: Release and Authorization, Provider Service Agreement EFormUserXref: You can filter it to return the Physican AppForms                                                                       Surgery                                      &n

multiple instances of report manager VS multiple users

Hello, I want multiple users to access same report or different reports through report manager for a report server. Will the scenario of opening multiple windows of report manager on same machine and execute reports from all windows simultaneously mean same as multiple users accessing the reports at the same time? If not, then how should I generate the scenario of multiple users? Regards.

Multiple Rdlc report in one reportviewer.

Hi, I have multiple reports that take the same parameters. Need to create a master report with all the reports merged together. I dont want to copy paste the rdlc files into one large file. found a control by Telerik called ReportBook but it costs money!! http://www.telerik.com/help/reporting/designing-reports-general-explanation.html anyone knows something similar? or am i missing something very obvious? using visual studio 2005... Cheers will subreports solve my problem? not sure how these work. so say i have a report that lists employers and i create a subreport that lists all employees for a particular empoyer. is it then possible on one rdlc to have the list of employers and a bunch of subreports for each employer's employees?

Execute SQL Task with XML resultset not return properly

Hello, I have a table with one column as XML data type, and it stored XML content from a XML file. In SSIS, I used execute SQL Task, and select the XML column from the table, and set resultset as XML. I then defined a variable XMLList as string, and I used this XMLList variable as resultset. When I run the package, I saw from watch window that this XMLLIst variable not returned the proper value from the table column, but with something like <ROOT><?MSSQLError HResult=\"0x80004005\" etc. Could anyone give me advice on what is wrong with my settings for resultset? Any suggestion will be appreciate!  

Creating a report from multiple data sources

I am working on a report where it requires pulling data from various sources, databases and data exported into excel sheets from different databases. The objective of the report is to create a single report with all the Providers (HPs) that have been terminated. So what I have done is that I have exported all the data on a local server in the pubs database and I am using a TSQL query to return the report. The same providers may exists in the various data sources so I have to make sure that I am only returning the data once...how ever the challenge is that, other than FirstName and LastName, there are no consistent IDs that we can use in all. For example, not all of the records have the SSN, PhoneNumber, or other IDs that uniquly identify the record. I have to create a report that returns the result set and identifiys the matching records and the source system the data is from..for example the output should display as such: Termed System firstname lastname SSN homephone phonenumber Active System AMIE Jeannine Donigan NULL 3522631007 Nurse SL Linde Peggy Vanrensselaer NULL 6077321218 Sbdev Below is the approach I I have taken to return the data and I wanted to know if there is a more efficient and better way of doing this: DECLARE @matchingTerminated Table ( FirstName Varchar(255), LastName Varchar(255), Gender Varchar(255), SSNEncrypted varchar(255), Birt

Adding a table or view multiple times when building a query with BI Report Designer and Report Build

How can I add a table/view multiple times to a single query by using BI Report Designer or Report Builder 3.0? In Report Designer I managed (workaround) to add one table multiple times by creating multiple Named Queries and having the same SELECT. Still I don't know how to add one table multiple times in Report Builder 3.0.

Report using Stored Proc calling multiple Stored Procs


Hi guys,

I am new to SSRS. I have to create a report from a Stored Proc. Thi Stored Proc excepts just one parameter 'ErecNo'. This parameter is passed to fetch the columns from table1. This result set contains OrderID. The OrderId is further passes to 5 SP's in to fetch 5 more result sets. Output of the main stored proc contains 6 result sets.

Now I have to create a report using this SP to show all the Result sets in a Single report. But When I pass the Parameter in SP in SSRS. It ends up showing just one result set with Order ID. 

Can you please guide me how can I show all the result set in a Single report? 



How to return looped procedure results so that they display as multiple rows of a single output inst


I understand that the below fragment will return a separate 1-row results set for each successful loop (also unnecessarily repeating the column header each time). How can I alter my procedure so that each value returned by the loop is appended underneath the previous value as multiple rows of a single results set? Do I have to store each return value in a temp table or someother storage object? It would be nice to just be able to spit them out row by row as each loop returned. Possible? Easy? Hard? I really appreciate the help I've received here so far.

SELECT (whatever)

OPEN techCursor;
FETCH NEXT FROM techCursor INTO @techNumLoop;
EXECUTE pr_FindExpenses4Tech @techNumLoop, @itemCount OUTPUT
SELECT @itemCount Item_Count

Multiple report execution is not in parallel


Hi everybody,

I experienced an issue with Sharepoint Foundation 2010. (Server 2008 and Sharepoint Foundation 2010 64bit)

When I use multiple Reportviewer Web parts on one page, the rendering (and most likely the Query itself) is not executed in parallel, i.e. every web part waits for its predecessor to finish.

I made two screenshots to show the difference in behavior (both are on the exact same hardware)

Sharepoint 2010 (Total execution Time 32 sec) : http://img807.imageshack.us/img807/6712/sp2010.jpg

Multiple Tables needed for Report Builder Problem


I have 3 tables I need to join: Voucher, Voucher_Detail, and Place
Voucher is related to Voucher_Detail by column voucher_run_id
Voucher has no relationship to Place
Voucher_Detail is related to Place by column place_id

I have solved the one to many relationship problem I was having between Voucher and Voucher_Detail by adding a New Named
Query called LeftOuterJoin.

In my DSV, the Voucher table links to the LeftOuterJoin which links to the Voucher_Detail which links to the Place

When I go into Report Builder, as soon as I select Voucher_Detail ( which is the table that has the most columns I need in the report),
the Voucher table disappears from view.

Not sure what I have missed. Obviously I need access to all 3 tables' data.

Strange issue with a huge SSRS 2008 report (supposed to return 3 million records)



NOTE: The strange part (as mentioned in title) will come in the end.

I am running a SSRS 2008 report which fetches 3 million records from a remote server. After around 1 hour the report processing stops and I see an error icon on the lft bottom of the browser window. When I click on that to see the error details it shows some PageRequestManagerSQLErrorException with an unknown error message with code 12029 (sometimes 12002).

When I see the reportserver logs there is an error message logged in it which says "Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached."

The <DatabaseQueryTimeOut> value in the report server configuration file is already having a value set to 7200 seconds(2 hours).<

Can I Combine resultset return by union




Is there a way to row two and three of the result set be returned so that the


following query:


Select Distinct A as TotalUniqueTranTypeT, B as TotalUniqueTranTypeO, C as Week from


SELECT     COUNT(DISTINCT EmailTracking.EmailAddress) AS B, 0 as A, DATEPART(Week, EmailTrackingDetail.TranDate) AS C

FROM         EmailTrackingDetail INNER JOIN

                      EmailTracking ON EmailTrackingDetail.EmailTrackingRecordID = EmailTracking.RecordID

WHERE     (EmailTrackingDetail.TranType = 'O')

GROUP BY DATEPART(Week, EmailTrackingDetail.TranDate)


SELECT     0 AS B, COUNT(DISTINCT EmailTracking.EmailAddress) AS A, DATEPART(Week, EmailTrackingDetail.TranDate) AS C

FROM   EmailTrackingDetail INNER JOIN

                      EmailTracking ON EmailTrackingDetail.EmailTrackingRecordID = EmailTracking.RecordID

WHERE   &n

report from multiple list in sharepoint 2010



I want to create report from multiple list in sharepoint 2010. In general Sql Server Business Intelligence,allow create report from 1 list.

how i can create report from multiple list with Sql Server Business Intelligence?


How to create a View to return all multiple child fields in one record?


I have an application where the users can define up to 5 extra fields for a particular table (e.g. tblMaster). The custom data is all held in one table (e.g. tblCustom) so there could be up to 5 records in tblCustom for each tblMaster record. In my application I build a select statement based on the number of custom fields so that I can get a single row of data for each tblMaster record (sample below). I am now adding new features to the application and I want to expose an SQL View to the users for a report writer that will achieve the same result but still cater for the variable number of custom fields. I realise this needs to use probably a RIGHT OUTER JOIN so that NULL is returned if there is no matching field but I can't work it out. If I had five separate files it would be easy as each one would be a RIGHT OUTER JOIN for each. I could build 6 views (e.g. with no custom fields, with one custom field, with two custom fields etc) but that is not very efficient. I don't particulaly want to restructure the database as there would be a lot of work and retesting.
Any help would be very welcome!

SELECT tblProcess.ProcessNumber, tblProcess.Process, tblProcess.Risk, tblProcess.MRR, tblProcess.Version, tblProcess.LastChange, tblProcess.SecurityLevel, 
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