locked
Store Procedure to populate several data sets in a report RRS feed

  • Question

  • User1682179836 posted

    Good day everyone

    I have this doubt that I don't know it it's possible or not, because I already made some tests but with no luck, this is what I want to achieve:

    I have a report with 5 dataset, one of them is to get the data of the report, but the other 4 is for populate the report's parameters, the dataset are very easy they are to get area, line, station and shift, but what I want to do; to increase the performance in the server, is to write a Store procedure with all the dataset queries to get the data from only one place. I already made test but I don't know if it's possible or the way that the SP needs to be so I can get the info correctly, the datasets are for the parameters which they are conditional, what I mean is if you choose one area it will give you a line depending on the area.

    Well what do you think? Is this possible? If so how the SP would looks like?

    Any help would be really appreciated, Thanks!!

    Monday, June 13, 2011 11:48 AM

All replies

  • User-125547262 posted

    Not sure if I under stood you right but you can write one stored proc to return multiple results sets .All you need to do are have multiple select statem

    Monday, June 13, 2011 11:58 AM
  • User-125547262 posted

    Not sure if I under stood you right but you can write one stored proc to return multiple results sets .All you need to do are have multiple select statem

    Monday, June 13, 2011 11:59 AM
  • User1682179836 posted

    Yes that’s what I was trying to do but I don’t know how to write the SP, this are my queries:

    -->AREA

    SELECT     AreaID, Area FROM Area WHERE (Status <> 0) ORDER BY Area

    -->LINE

    SELECT LineID, Line FROM Line WHERE AreaID = @AreaID UNION SELECT NULL AS LineId, NULL AS Line

    -->STATION

    SELECT StationID, Station FROM Station WHERE LineID = @LineID <st1:place>UNION </st1:place>SELECT NULL AS StationId, NULL AS Station

    How the SP should be? The order I mean.

    Regards!!

    Monday, June 13, 2011 4:21 PM
  • User-125547262 posted

    The order does not matter. Also I have assumed that the areaid and lineID are int. Please correct according to the actual data type

    CREATE PROCEDURE dbo.MyProc

    @AreadID int,

    @LineID int

    AS

    BEGIN

    SELECT     AreaID, Area FROM Area WHERE (Status <> 0) ORDER BY Area

    SELECT LineID, Line FROM Line WHERE AreaID = @AreaID UNION SELECT NULL AS LineId, NULL AS Line

    SELECT StationID, Station FROM Station WHERE LineID = @LineID <st1:place>UNION </st1:place>SELECT NULL AS StationId, NULL AS Station

    END

    Monday, June 13, 2011 10:44 PM
  • User1682179836 posted

    Hi there, yes the data type is correct, well my question was more into how to do it in the report, because the report have to ask the user first for the Area, then according of the AreaID show a line, then according of the LineID show a station and then show the report, but when I add the parameters in the report, one for Area, Line and station and each of them pointing to the SP, only shows the AreaID and Area from the first query, and if I create only one dataset pointing to the SP also only gets the result... don't know if I’m doing something wrong?<o:p></o:p>

    Thanks for the help Laughing<o:p></o:p>

    Tuesday, June 14, 2011 11:42 AM
  • User2105670541 posted

    till my understanding, you have to populate your parameters with seperate datasets, for line station and area each. you cannot populate them the way you are doing, or you have to tweek your sql statement for this, i mean lets suppose you want to have the area first: then you can select all the records from area, if your parameter contain @area then you should select the second select statement inside a if clause with @area condtion to check its not null or empty, in third condition you can check if @area and @line are not blank then go to the third if clause and populate the record from station, you can get a help from code below:

    if (@area is null and @line is null and @station is null)
    begin
    select columnn1 as  parmname,colum2 as paramvalue from areaTable
    end
    if (@area is not null (and @line is null and @station is null))
    begin
    select columnn1 as  parmname,colum2 as paramvalue from areaTable where area_cd=@area
    end
    if (@area is not null and @line is not null and @station is null))
    begin
    select columnn1 as  parmname,colum2 as paramvalue from stationTable where area_cd=@area  and line_cd=@line
    end

    you need to make sure that the column name should be the same in all the select clause because while setting the param values and labels you need to specify them, please make them generic, and revert if you have some problem with understanding my explanation :)

    Wednesday, June 15, 2011 12:02 AM
  • User1682179836 posted

    Hi I did it like you told me to but with no luck, this is my code so far:

    CREATE PROCEDURE [dbo].[SP_Report_DataSets]
    -- Add the parameters for the stored procedure here
    @AreaID Varchar(50),
    @LineID Varchar(50),
    @StationID Varchar(50)
    AS
    BEGIN
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    if (@AreaID is null and @LineID is null and @StationID is null) 
    begin
    SELECT AreaID, Area FROM Area WHERE (Status <> 0) ORDER BY Area
    end
    if (@AreaID is not null and @LineID is null and @StationID is null) 
    begin
    SELECT LineID, Line FROM Line WHERE AreaID =@AreaID 
    end 
    if (@AreaID is not null and @LineID is not null and @StationID is null) 
    begin 
    SELECT StationID, Station FROM Station WHERE LineID =@LineID 
    end
    END

    But when I got to the report parameters in Visual Studio and add the datasets for each, and select the option From Query and then select for each parameter a different dataset I only got AreaID and Area for all of them in the dataset, value field and label field dropdown.

    What am I doing wrong? Thanks!!

    Wednesday, June 15, 2011 3:09 PM
  • User2105670541 posted

    in your every select statement the columnnames should be same like below:

    for area:

    SELECT AreaID as col1, Area as col2 FROM Area WHERE (Status <> 0) ORDER BY Area

    for line:

    SELECT LineID as col1, Line as col2 FROM Line WHERE AreaID =@AreaID

    for station:

    SELECT StationID as col1, Station as col2 FROM Station WHERE LineID =@LineID

    now try to test it and let me know if it wont work

    Wednesday, June 15, 2011 11:19 PM
  • User1682179836 posted

    Hi Ashutosh Pathak thanks you for your inputs, I tried like you told me but still not working, when I execute the SP does not throw me any errors but when I am in the report this are the steps I did:

    1. create 3 datasets for area, line and station
    2. for each dataset I select Value and Label field (the column names I put in the SP)

     But when I ran the report I get these errors:

    The report parameter ‘AreaID’ has a DefaultValue or a ValidValue that depends on the report parameter “AreaID”. Forward dependencies are not valid.
    The report parameter ‘AreaID’ has a DefaultValue or a ValidValue that depends on the report parameter “LineID”. Forward dependencies are not valid.
    The report parameter ‘AreaID’ has a DefaultValue or a ValidValue that depends on the report parameter “StationID”. Forward dependencies are not valid.
    The report parameter ‘LineID’ has a DefaultValue or a ValidValue that depends on the report parameter “LineID”. Forward dependencies are not valid.
    The report parameter ‘LineID’ has a DefaultValue or a ValidValue that depends on the report parameter “StationID”. Forward dependencies are not valid.
    The report parameter ‘StationID’ has a DefaultValue or a ValidValue that depends on the report parameter “StationID”. Forward dependencies are not valid.

    What do you thing is the problem? Regards!!

    Thursday, June 16, 2011 5:09 PM
  • User2105670541 posted

    did you do null handling in your expression, if no parameter is selected, make sure your parameters allows null

    Thursday, June 16, 2011 10:52 PM
  • User1682179836 posted

    Hi Ashutosh Pathak yes I made sure that the parameters accept null values but even with that I have the same errors Frown, what could it be? Or do you think that is not possible?

    Thanks!!

    Monday, June 20, 2011 1:43 PM
  • User1471008070 posted

    Hi arisg,

    It sounds like the order of the parameters are wrong, please have a check.

    Tuesday, June 21, 2011 1:43 AM
  • User2105670541 posted

    yes.. just check what is the sequence of parameters, the same way in select statement they are coming right? and share the way you are passing the parameters to dataset

    Tuesday, June 21, 2011 1:44 AM
  • User1682179836 posted

    Hi there, I apologize to response until now but I was on vacations Cool... well I check the order of the parameters and they are in the correct order.

    The way I'm doing it is:

    I create a Store Procedure with this query:

    if (@AreaID is null and @LineID is null and @StationID is null) 
    begin 
    --select columnn1 as  parmname,colum2 as paramvalue from areaTable 
    SELECT Area AS 'Name', AreaID AS 'Value' FROM Area WHERE (Status <> 0) ORDER BY Area
    end 
    	if (@AreaID is not null and @LineID is null and @StationID is null) 
    	begin 
    	--select columnn1 as  parmname,colum2 as paramvalue from areaTable where area_cd=@area
    	SELECT Line AS 'Name', LineID AS 'Value' FROM Line WHERE AreaID = @AreaID --UNION SELECT NULL AS LineId, NULL AS Line
    	end 
    		if (@AreaID is not null and @LineID is not null and @StationID is null) 
    		begin 
    		--select columnn1 as  parmname,colum2 as paramvalue from stationTable where area_cd=@area  and line_cd=@line
    		SELECT Station AS 'Name', StationID AS 'Value' FROM Station WHERE LineID = @LineID --UNION SELECT NULL AS StationId, NULL AS Station
    end

    In the report I have 4 datasets 3 of them are pointing to the store procedure, in command type I selected "Store Procedure" , the other SP I have is the one that has the query to show in the report:

    SELECT	  Pro.SerialNumber, Pro.StationID, Pro.Employees AS 'Employee', Pro.FinishDate AS 'Date', Pro.Status, Pro.FinishShiftID AS 'Shift',
    		  ISNULL(Pro.Qty, 1) AS QTY, Sta.Station, Lin.Line, Are.Area, ProdS.Status AS Desc_Status, 
    		  Glob.DefectTranslation, Pro.CatalogNumber AS 'CatalogNumber', Pro.OrderNumber AS 'OrderNumber'
    FROM      SimplifiedProductionTracking AS Pro with (nolock) 
    		  INNER JOIN Station AS Sta WITH (NOLOCK) ON Pro.StationID = Sta.StationId 
    		  INNER JOIN Line AS Lin WITH (NOLOCK) ON Sta.LineId = Lin.LineID 
    		  INNER JOIN Area AS Are WITH (NOLOCK) ON Lin.AreaID = Are.AreaID 
    		  INNER JOIN ProductStatus AS ProdS WITH (NOLOCK) ON ProdS.StatusID = Pro.Status 
    		  INNER JOIN ProductionScrap AS ProScr WITH (NOLOCK) ON Pro.SerialNumber = ProScr.SerialNumber 
    		  AND Pro.StationID = ProScr.StationID 
    		  AND DATEPART(MINUTE, Pro.FinishDate) = DATEPART(MINUTE, ProScr.ScrapDate) 
    		  INNER JOIN GlobalDefectsTranslation AS Glob WITH (NOLOCK) ON ProScr.DefectID = Glob.DefectID 
    WHERE     (Pro.Status = '5') AND (Glob.LanguageID = 1) 
    		  AND (ISNULL(Lin.LineID, 0) = ISNULL(@LineID, ISNULL(Lin.LineID, 0))) 
    		AND (ISNULL(Are.AreaID, 0) = ISNULL(@AreaID, ISNULL(Are.AreaID, 0))) 
    		AND (ISNULL(Pro.StationID, 0) = ISNULL(@StationID, ISNULL(Pro.StationID, 0))) 
    		AND (CONVERT(varchar, Pro.FinishDate, 112) BETWEEN CONVERT(varchar, @StartDate, 112) AND CONVERT(varchar, @EndDate, 112))
    ORDER BY Lin.LineID, Sta.Station, Pro.FinishDate, Pro.SerialNumber

    The order of the parameters is: AreaID, LineID, StationID, StartDate and EndDate, so what could you think it could be?

    Regards!!

     

    Tuesday, June 28, 2011 3:43 PM