locked
data set runs twice instead of once RRS feed

  • Question

  • Hi we run 2016 enterprise.  One of my datasets X is used to lay down my data once for reuse every time my sub report runs from the next salesman's tablix cell...rather than rerunning all the joins etc each time the sub report runs.  It also returns a control number that is used to set a hidden parameter which can then be passed to each execution of the sub rport.

    My first param is called impersonate and it defaults to a null value.  It is visible.  It is text.  There are no "available values".  Nulls are allowed.

    My next param is a list of salesmen.  It is dependent on impersonate.  But also the userid (via a hidden user id param) of the user if impersonate wasn't entered.  available and default values are sourced by the same dataset.

    My next two params are from and to dates which default to prior Monday and coming Friday via expression.  There are no available values.

    My last parameter is a list of sections that can show in each cell for each salesman.  Multiple values are allowed and both avail and default are sourced by the same dataset.

    X is running twice.  After I unclick the null lozenge on impersonate, enter impersonate, click the down arrow on list of salesmen.  And after I enter from date, click view report.

    I don't want X to run till I click view report.  What can I do?  I'm also curious why the list of salesmen doesn't hydrate immediately given that impersonate has a default value.

     

     

    Wednesday, November 28, 2018 10:29 PM

Answers

  • Since ControlNumber depends on salesman, anytime any action occurs which causes salesman parameter to change its values, the dataset for COntrolNumber will get executed to refresh its values

    You cant control that in anyway

    If you want it to be delayed till report gets executed then the dataset query it uses have to be executed from within the report dataset and it should not have any dependency with any of the other parameters.It should not be included as a parameter within report dataset as well.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by db042190 Friday, December 7, 2018 3:30 PM
    Monday, December 3, 2018 12:50 PM
  • this is good info.  Thx Visakh16.  What I've done for now (maybe permanently) is set default for the sections param to none.  Not only did this delay the execution of dataset control number but ssrs (perhaps cuz sections is the last param?) decided to wait till I hit view report before controlNumber was run.  controlNumber is also dependent on sections. 

    I wish ssrs worked differently cuz i'd really like to default sections as well but as Visakh16 pointed out my hands seem tied.  I'll wait a little before marking the various posts as answers.  I did try (before changing sections' behavior) tying a textbox to the first value returned from controlnumber but that didn't help.

    • Marked as answer by db042190 Friday, December 7, 2018 3:30 PM
    Monday, December 3, 2018 4:33 PM

All replies

  • Hi db042190

    According to your description , sorry for not clear about your report structure and what the exactly issue is , what do you mean the dataset is running twice not once.

    Based on my guessing , your scenario could be like below:

    Sub report have dataset   assume subdataset1

    Main report have main dataset  assume maindataset1

    May be in your main report , you have another dataset assume  maindataset2. Maindataset2 might be the same with the subdatatset1.

    Then ,in your suppose , if you run the main report, the subreport would load the dataset from the maindataset2 not the subdataset1, right?

    Seems not .

    The sub report would only use the dataset in its report. The sub report is running separately with the main report. Sub report would not use the main report dataset.

    So ,when you run the main report , the maindataset2 runs once ,and the subdataset1 runs once. Seems the maindataset2 runs twice.

    If your scenario and the problem is not the condition mentioned above , just forget above description.

    You could offer more detailed information about your report structure , dataset from main report ,dataset from sub report (include query), the parameter setting ,the issue scenario…etc. to us for more precise research.

    Hoping for your reply.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    Thursday, November 29, 2018 2:27 AM
  • You cant control the execution of datasets within SSRS report. The datasets will be automatically executed for each rendering of the report unless you have set caching options. Even when caching is set you can only control caching expiration time and cant control on when dataset should be executed from outside

    So if you want to optimize dataset usage you should try to redesign the report architecture to avoid same dataset from being called from multiple reports (main and subreports) or may be make them into single report with drilldown option rather than making it into drillthrough subreport which will force running the dataset again


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, November 29, 2018 6:07 AM
  • thx all, dataset X is only called from the master report.  So pls forget the sub report for the moment.  That was just background info. 

    My suspicion is that the fact that all master params are defaulting is causing ssrs to run dataset X multiple times because it thinks it doesn't need to wait.  ie in effect ssrs is rendering the report multiple times as the user works thru the params. 

    Does this clarify the question?  What I really want is to only run X when the user clicks View Report.  Or at least to wait till the last param is set.  So maybe I need to make the last param's default = None.  And make the user set it always?  

    Thursday, November 29, 2018 12:28 PM
  • thx all, dataset X is only called from the master report.  So pls forget the sub report for the moment.  That was just background info. 

    My suspicion is that the fact that all master params are defaulting is causing ssrs to run dataset X multiple times because it thinks it doesn't need to wait.  ie in effect ssrs is rendering the report multiple times as the user works thru the params. 

    Does this clarify the question?  What I really want is to only run X when the user clicks View Report.  Or at least to wait till the last param is set.  So maybe I need to make the last param's default = None.  And make the user set it always?  

    Nope thats not correct

    It doesnt execute the dataset unless the user click view report

    However when using cascaded parameter report will get refreshed when you change a parameter value as it calls the dependent dataset based on the changed values in the parent parameter and reloads the values returned by the dataset

    As far as dataset for main report is concerned, its only executed when user clicks view report after selecting all parameters. In case parameters have defaults set, then it will get executed based on default values

    If you share same prcedure/dataset for multiple parameters, they wont get executed multiple times unless the parameter values being passed are different


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, November 29, 2018 12:40 PM
  • thx Visakh16.  I have concrete proof that it is running dataset X even before the user hits View Report.

    I'm not sure what you mean by "...report will get refreshed when you change...".  If that means report will run, then you would have to agree that dataset X could be (re) running before user hits View. 

    Either way my original questions stands.  can the community offer ideas for preventing data set X from running multiple times?  The dependencies on what dataset X does/accomplishes go like this right now...

    1) a hidden param (last on the list of params) is set by a control number dataset X returns

    2) a text box shows its value (control number) temporarily to help me understand this behavior (ie its jumping by 2 before each useful report rendering)

    3) the hidden parm's value is passed to each sub report call

    4) there is one other but it caused confusion earlier in the post so i'm not listing it here    

    Thursday, November 29, 2018 2:28 PM
  • Hi db042190

    Seems that the screenshot or the video could help us understand your issue and the question  more clear.

    If possible , you could offer the detailed images to clarify your process, and the evidence about your issue. That would helped a lot.

    Best Regards,

    Eric liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    Friday, November 30, 2018 6:41 AM
  • thx Visakh16.  I have concrete proof that it is running dataset X even before the user hits View Report.

    I'm not sure what you mean by "...report will get refreshed when you change...".  If that means report will run, then you would have to agree that dataset X could be (re) running before user hits View. 

    Either way my original questions stands.  can the community offer ideas for preventing data set X from running multiple times?  The dependencies on what dataset X does/accomplishes go like this right now...

    1) a hidden param (last on the list of params) is set by a control number dataset X returns

    2) a text box shows its value (control number) temporarily to help me understand this behavior (ie its jumping by 2 before each useful report rendering)

    3) the hidden parm's value is passed to each sub report call

    4) there is one other but it caused confusion earlier in the post so i'm not listing it here    

    It means dependent datasets used for filling parameter values will refresh not the actual report dataset

    To be honest your explanation is not very helpful in understanding the overall setup

    As I understood dataset X is only used by main report in which case it cant execute multiple times unless you've an autorefresh mechanism set in the report


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page



    • Edited by Visakh16MVP Friday, November 30, 2018 6:49 AM
    Friday, November 30, 2018 6:47 AM
  • thx Eric and Visakh16.  Because its proprietary I'll take some time to recreate the issue in a generic report/subreport.  And post back here. 

    In the mean time with the param "control number" being hidden but also having default values, I'm wondering what setting its default to none and instead avail values to dataset X might accomplish. I'll post those results as well.

    Friday, November 30, 2018 12:12 PM
  • no go on the last idea as ssrs didn't like a hidden param without a default, at least in my scenario.  I'll throw something together and see if I can recreate my issue. 
    Friday, November 30, 2018 5:09 PM
  • i'm going to attempt to upload as many pics as it takes to explain...starting with image of report already run.  a boss measures his/her salesmen on different perspectives (aka sections, eg CC, BB, ZZ...).  Some bosses like all perspectives, some just some.  Hence the drop down for sections.  the first image shows part of the 1st cell of a tablix in the master report in which the subreport ran for salesman 01.

    Sunday, December 2, 2018 3:34 PM
  • this image shows the end of the 1st cell and beginning of the next for salesman 02.

    Sunday, December 2, 2018 3:43 PM
  • this is the design pane for the master report.  "Run as" is for impersonation and defaults to null.  The param label Bogus1 actually belongs to a param that helps to control Expand / Collapse activity.  Param Bogus2 was put there to occupy some real estate in ssrs so params would line up visually in a more friendly way.  Subreport 1 is actually inside a tablix.  And that tablix's dataset is salesmen.

    Sunday, December 2, 2018 3:52 PM
  • this image gives a better view of the params and datasets belonging to the master report

    Sunday, December 2, 2018 3:55 PM
  • perhaps you believe me.  Perhaps you don't.  After I uncheck the null value in "Run as", enter "xyz" in "Run as", and drop down salesmancode whose label is DRG, dataset controlNumber runs for the first time unwanted.  And that's without rendering the report.  How do I know?  I do a select on a table it inserts to called salesReportControl before and after each of the steps listed in sentence 3 above.  Below is the code for the proc associated with dataset controlNumber.  I suspect that ssrs waits till DRG is dropped down to run ds controlNumber because DRG has no values till "Run as" is entered.  After that, I override the from date, hit view report and ds controlNumber runs again and the report as shown in the previous posts runs.  Right now the text box you saw earlier called controlNumb is plumbed to a param whose value is set bu ds controlNumber.  It was originally put there to help me debug this stuff after I suspected the ds was running too often.  I'm thinking about plumbing the ds directly to this textbox to alter the behavior.  The bottom line is that I don't want ds controlNumber running till its time to render the report.  also the main idea behind each of the executes you see below is that its probably better to lay down all the data once (doing all joins etc) for all salesmen for this instance of the report than to rerun a subreport for each salesman with all the joins in the subreport itself.  each of the executes you see below lays down a table (denormalized, shaped appropriately for better performance) for the perspective shown in the proc name.  Then with the control number being passed to the sub report for each cell along with the next sales man code, the overall performance is much better.  At the moment, I need to reshape all this data at run time.  my biggest concern right now is preventing controlNumber from running when it shouldn't. 

    USE [abcdef]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    
    CREATE PROCEDURE [ghijkl].[usp_salesReportControlNumber]
    	@salesmen varchar(max),
    	@from varchar(23),
    	@to varchar(23),
    	@sections varchar(max)
    AS
    BEGIN
    	
    	SET NOCOUNT ON;
    
    	declare @controlnumber int
    
    BEGIN TRANSACTION
    
      update ghijkl.salesreportcontrol set controlnumber = controlnumber + 1
      from ghijkl.salesreportcontrol
      WITH (TABLOCK, HOLDLOCK)
      WHERE id=1
    
      select @controlnumber = controlnumber from ghijkl.salesreportcontrol where id = 1
    COMMIT TRANSACTION
    exec [ghijkl].usp_salesccstats @salesmen ,
    	                                @from ,
    	                                @to ,
    	                                @sections ,
    									@controlnumber
    exec [ghijkl].usp_salesbbstats @salesmen ,
    	                                @from ,
    	                                @to ,
    	                                @sections ,
    									@controlnumber
    exec [ghijkl].usp_saleszzcomments @salesmen ,
    	                                   @from ,
    	                                   @to ,
    	                                   @sections ,
    									   @controlnumber
    exec [ghijkl].usp_salesxxstats        @salesmen ,
    	                                   @from ,
    	                                   @to ,
    	                                   @sections ,
    									   @controlnumber
    exec [ghijkl].usp_salesxxComments    @salesmen ,
    	                                   @from ,
    	                                   @to ,
    	                                   @sections ,
    									   @controlnumber 
    exec [ghijkl].usp_salesyystats                 @salesmen ,
    	                                   @from ,
    	                                   @to ,
    	                                   @sections ,
    									   @controlnumber 
    exec [ghijkl].usp_salesyycomments       @salesmen ,
    	                                   @from ,
    	                                   @to ,
    	                                   @sections ,
    									   @controlnumber 
    exec [ghijkl].usp_salesaacomments        @salesmen ,
    	                                   @from ,
    	                                   @to ,
    	                                   @sections ,
    									   @controlnumber 
    select @controlnumber controlNumber
    
    END
    GO
    
    
    

    Sunday, December 2, 2018 4:25 PM
  • What is this dataset used for?

    Filling one or more of the parameters or getting data for your report itself?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, December 2, 2018 5:01 PM
  • thx, it is used to set one master report param (controlNumber) that gets passed to each execution of the sub report.

    And each of the procs the ds executes lay down data in tables (about 8 of them) used by the sub report in conjunction with control number param and salesman code for rendering that cell of the tablix.

    Sunday, December 2, 2018 5:38 PM
  • thx, it is used to set one master report param (controlNumber) that gets passed to each execution of the sub report.

    And each of the procs the ds executes lay down data in tables (about 8 of them) used by the sub report in conjunction with control number param and salesman code for rendering that cell of the tablix.

    And is this param dependent on Run As param?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, December 2, 2018 5:47 PM
  • indirectly as DRG (salesmen) depends on "Run as" and control Number depends on DRG.
    Sunday, December 2, 2018 7:43 PM
  • Hi

    According to your description , you think your dataset runs twice judging by the control number ,right?

    It might be that the run the report once ,then the control number add 2 ,then you think the dataset have runs twice.

    So you could use sql server profiler ,extended event or other trace tools to check the each step of your stored procedure .

    Whether the control number are added twice .

    BR,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    Monday, December 3, 2018 8:46 AM
  • indirectly as DRG (salesmen) depends on "Run as" and control Number depends on DRG.

    Sorry didnt understand

    WHat do you mean by indirectly?

    I'm still unable to understand your full setup base don the explanation

    All I say is that (I repeat it!) if dataset is used to fill a dependent parameter part of cascaded group it will get executed when there's any change of value in the dependent parent parameter(s)

    It doesnt wait for report to be executed in that case.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, December 3, 2018 8:51 AM
  • Thx Visakh16.  So in this case how can I stop controlNumber from running till all params have been chosen and user clicks view report?

    By indirectly, pls see code below for salesmen (DRG) dataset.  Notice it depends on @impersonate which is the prompt "Run as".  And you have already seen the params that are passed to controlNumber which are salesmen, from, to, and sections.  so salesmen depends on run as.  and control number depends on salesmen.  @impersonate is the param associated with Run as prompt.  I commented out and stripped some of the original code for purposes of this demo.

              
              declare @UserId1 varchar(50)                       --actual reader
              declare @EmpId varchar(10)                         --actual reader
              declare @ImpersonateEmpId varchar(10)=''
              declare @AdminRole tinyint                         --actual reader
              declare @Like varchar(20)
              set @UserId1 = substring(@UserID,charindex('\',@UserID) + 1,len(@UserID) - charindex('\',@UserID))
    
              --there was a lookup for @empid here
    
              select @adminRole = count(*) from
              (
              select @UserID userid--<----------------------------------------------------------------represents a lookup for admin role on userid
              ) y
    
              if (@adminRole > 0) and (@Impersonate is not null) and (@Impersonate <> '')--leaving reference in here otherwise gutting logic 
              begin
              
              set @Like = '%/' + @ImpersonateEmpId + '/%'--was used to mqtch hierarchy field
              end
              else
              begin
              set @Like = '%/' + @EmpId + '/%'--was used to mqtch hierarchy field
              end
    if @Impersonate is null
    select '00001' salesmancode,'salesman01' Name where 1=0
    else
              select '00001' salesmancode,'salesman01' Name union all
                    select '00002','salesman02' union all
                    select '00003','salesman03' union all
                    select '00004','salesman04' union all
                    select '00005','salesman05' union all
                    select '00006','salesman06' union all
                    select '00007','salesman07' union all
                    select '00008','salesman08' union all
                    select '00009','salesman09' union all
                    select '00010','salesman10' --union all
    
    
              --order by 2
            


    • Edited by db042190 Monday, December 3, 2018 12:34 PM clarity
    Monday, December 3, 2018 12:32 PM
  • Since ControlNumber depends on salesman, anytime any action occurs which causes salesman parameter to change its values, the dataset for COntrolNumber will get executed to refresh its values

    You cant control that in anyway

    If you want it to be delayed till report gets executed then the dataset query it uses have to be executed from within the report dataset and it should not have any dependency with any of the other parameters.It should not be included as a parameter within report dataset as well.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by db042190 Friday, December 7, 2018 3:30 PM
    Monday, December 3, 2018 12:50 PM
  • this is good info.  Thx Visakh16.  What I've done for now (maybe permanently) is set default for the sections param to none.  Not only did this delay the execution of dataset control number but ssrs (perhaps cuz sections is the last param?) decided to wait till I hit view report before controlNumber was run.  controlNumber is also dependent on sections. 

    I wish ssrs worked differently cuz i'd really like to default sections as well but as Visakh16 pointed out my hands seem tied.  I'll wait a little before marking the various posts as answers.  I did try (before changing sections' behavior) tying a textbox to the first value returned from controlnumber but that didn't help.

    • Marked as answer by db042190 Friday, December 7, 2018 3:30 PM
    Monday, December 3, 2018 4:33 PM