locked
how to put values from child tables in comma separated based on value in Parent table in SSRS report in ASP.NET RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    User-1644108997 posted

    Hi Team,

    We have scenario something like this explained below..

    Lets say we have two stored procedure  (see the complete script below)

    1) GetRequest

    2)GetRequestHistory

    We created a dataset with 2 table  for each procedure.We are not allowed to change the procedure.

    With the above objects we need to created a SSRS report like below

                                                      Request details of 1

                                                     OS Installation on <Report GeneratedDate)

                          Report Id : 1

                          RequestName : OS Installation

                          Request by : User1

                          Report History : Open (2015-06-23 22:20:52.750) , InProgress (2015-07-23 22:20:52.750) , Closed (2015-08-20 22:25:52.147)

    how to do this...Here the procedure GetRequestHistory  returns the history of the details of the procedure GetRequest.

    In the ReportHistory in the report we want the details of the history in comma separated..

    We are not allowed to change the procedure. We are working in ASP.NET we are using reportView to host the report.We will pass the RequestID as  parameter in query string.

    CREATE TABLE [dbo].[tblRequest](
    [RequestID] [int] IDENTITY(1,1) NOT NULL,
    [RequestName] [varchar](500) NOT NULL,
    [RequestBy] [varchar](500) NOT NULL,
    )
    GO
    CREATE TABLE [dbo].[tblRequestHistory](
    [HistoryID] [int] IDENTITY(1,1) NOT NULL,
    [RequestID] [int] NULL,
    [Status] [varchar](50) NOT NULL,
    [ModifiedDate] [datetime] NULL CONSTRAINT [DF_tblRequestHistory_ModifiedDate] DEFAULT (getdate())
    )
    GO
    ALTER TABLE [dbo].[tblRequestHistory] WITH CHECK ADD CONSTRAINT [FK_tblEntity_tblCase] FOREIGN KEY([RequestID])
    REFERENCES [dbo].[tblRequest] ([RequestID])
    GO
    ALTER TABLE [dbo].[tblRequestHistory] CHECK CONSTRAINT [FK_tblEntity_tblCase]
    GO
    CREATE PROC [dbo].[GetRequest]
    @RequestId INT
    AS
    SELECT * FROM [dbo].[tblRequest] WHERE RequestID=@RequestId
    GO
    CREATE PROC [dbo].[GetRequestHistory]
    @RequestId INT
    AS
    SELECT * FROM tblRequestHistory WHERE RequestID=@RequestId
    GO

    Sunday, August 23, 2015 1:06 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    User1644755831 posted

    Hello balasubramanianKM,

    You can do this on the asp.net side instead of the Storeprocedure.

    1. in C# code once you get the data table in the parent table add a new column which will hold the commaseparated values.

    2. loop through parents rows and for each request id get the child rows. you can use DataTable.Select Method (String) for directly filtering on the column and then using string.join to make commasepated values.

    //for example in the loop you can do something like this
    var rows = dt.Select(string.Format("RequestID = {0}",1));
var values = rows.Select(x => x.Field<string>("Your History column")).ToArray();
string commaseparatedvalue = string.Join(",", values);
//now set the column value in the parent column

    Hope this helps.

    With Regards,

    Krunal Parekh

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 25, 2015 4:39 AM