Duane
Duane

Reputation: 65

SSRS report calling stored procs

We're using SSRS 2008 R2

We have several reports that call multiple stored procedures. It seems that as soon as the report is called its runs the stored procs with their default values then reruns the stored procs with the passed parameters. Does that make sense?

We're considering using snapshots to store a snapshot of report with all the default parameters but is there a better way?

rptViewer.ProcessingMode = ProcessingMode.Remote
rptViewer.ShowCredentialPrompts = False
rptViewer.ShowBackButton = False
rptViewer.ShowDocumentMapButton = False
rptViewer.ShowExportControls = False
rptViewer.ShowFindControls = False
rptViewer.EnableViewState = True
rptViewer.ShowPageNavigationControls = False
rptViewer.ShowParameterPrompts = True
rptViewer.ShowRefreshButton = False
rptViewer.ShowPrintButton = True
rptViewer.ShowPromptAreaButton = False
rptViewer.ShowToolBar = True
rptViewer.ShowZoomControl = False
rptViewer.SizeToReportContent = True
rptViewer.AsyncRendering = False
rptViewer.Height = Unit.Percentage(100)
rptViewer.Width = Unit.Percentage(100)

Dim RepParameters As New ReportParams
With RepParameters
  .ApplicationID = MyBase.CurrentApplicationID.ToString
  .EntityID = EntityIDList
  If ShowTitle Then .isExported = "True" Else .isExported = "False"
  .LanguageID = CShort(MyBase.CurrentLanguage.ID).ToString
  .UserSecurityID = CInt(MyBase.CurrentLoggedUser.SecurityID).ToString
End With

**rptViewer.ServerReport.SetParameters(rep.SsrsReportParameters(RepParameters))**

Upvotes: 1

Views: 921

Answers (2)

Duane
Duane

Reputation: 65

More of a workaround than a solution.

We ended up setting the default value to one of the parameters to -1 and in the stored procedure we only run the code if the parameter is not -1.

Upvotes: 0

Phil
Phil

Reputation: 4092

Sounds like you are passing your parameters too late for the reportviewer control. Make sure in the page's execution cycle you are setting them preferably as soon as possible. I worked around this problem by initialising the ReportViewer control in the page load code behind code rather than declaring one in the aspx part. This solved it for me.

Upvotes: 1

Related Questions