pass a where clause to reporting services
-
Hi all,
I need to develpe a report that takes user's input from .net editable datagrid - the datagrid is dynamic populated from user's selection from a listbox.
Say from the listbox, 3 items were selected, then the datagrid will display the related info for these 3 items. The datagrid has 4 columns. First is the item name, second is the Minimum of the item value, third is the maximum of the item value, the fourth column is an operator 'and/or'. The user can edit the datagrid (sort of build a query, like narrows down the range of the min and max and/or 2nd item's criterias and/or 3rd item's criteria, etc).
I would like to pass this query to reporting services in the where clause. Is it possible? I just thik reporting services matrix is so powerful. It will be much faster than if I build it in .net.
Your help is very much appreciated,
-
You probably will need to use the web service. Set up the report with the parameters specified in the query using the @ParameterName convention. Then you have to set a reference to the web service in the .Net project. Once you have that the code sample below should help get you started to output the report.
Sub ClientReportGeneration( _
ByVal sRSWeb_Report_Location As String, _
ByVal sOutput_Report_Name As String, _
ByVal sOutput_SubFolder_Location As String, _
ByVal sOutput_Report_Format As String, _
ByVal bReport_Parameters As Boolean)
'Below is the name of the web service that was set up in the reference
Dim rs As New WebService_dub0pwsql20.ReportingService
Dim arWarnings() As WebService_dub0pwsql20.Warning
Dim LogonCredentials As System.Net.NetworkCredential
' Render arguments
Dim result As Byte() = Nothing
'Dim rptformat As String = "EXCEL"
Dim historyID As String = Nothing
Dim devInfo As String = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
Dim credentials As WebService_dub0pwsql20.DataSourceCredentials() = Nothing
Dim showHideToggle As String = Nothing
Dim encoding As String
Dim mimeType As String
Dim warnings As WebService_dub0pwsql20.Warning() = Nothing
Dim reportHistoryParameters As WebService_dub0pwsql20.ParameterValue() = Nothing
Dim streamIDs As String() = Nothing
Dim sh As New WebService_dub0pwsql20.SessionHeader
Dim sOutputFormatFileType As String
Dim iRowIndex As Integer
Dim iNewRowIndex As Integer
Dim iCnt As Integer
Dim parameters() As WebService_dub0pwsql20.ParameterValue 'A zero based array for the parameters for this report
'In this case I am going through a datagrid to pick up and generate multiple reports.
iRowIndex = 0
iNewRowIndex = 1
dgRSReportParameters.CurrentRowIndex() = 0
If bReport_Parameters = True Then
'loop through the paramenters for this report get the name and value
While iRowIndex <> iNewRowIndex
' The datagrid row index will not increment above the number of rows.
'This loop continues until the row counter does not increment any more
'At that point you are at the end of the data in the datagrid.
'The data grid doesn't have a count for the records showing in a parent child grid
'So this is used to get the number of records and create the parameter array.
'Set the current row index
iRowIndex = dgRSReportParameters.CurrentRowIndex()
'Increment the current row
dgRSReportParameters.CurrentRowIndex() += 1
'Set the new row index
iNewRowIndex = dgRSReportParameters.CurrentRowIndex()
End While
ReDim parameters(iNewRowIndex) 'As WebService_dub0pwsql20.ParameterValue
'For each parameter set the value in the array as shown beow
For iCnt = 0 To iNewRowIndex
parameters(iCnt) = New WebService_dub0pwsql20.ParameterValue
parameters(iCnt).Name = dgRSReportParameters.Item(iCnt, 1)
parameters(iCnt).Value = dgRSReportParameters.Item(iCnt, 2)
Next
Else ' There are no parameters indicated in the report grid
parameters =
Nothing
End If
'Set the login credentials that will be used to run the report
LogonCredentials =
New System.Net.NetworkCredential("sjohnson03", "sj4XXXXX51", "PutDomainNameHere")
rs.Credentials = LogonCredentials
rs.PreAuthenticate =
True
rs.SessionHeaderValue = sh
Try
result = rs.Render(sRSWeb_Report_Location, sOutput_Report_Format, historyID, devInfo, parameters, _
credentials, showHideToggle, encoding, mimeType, reportHistoryParameters, warnings, streamIDs)
sh.SessionId = rs.SessionHeaderValue.SessionId
Catch e As Exception
Me.Cursor = Cursors.Default
MessageBox.Show(sRSWeb_Report_Location.ToString & vbCrLf & dgRSReportParameters.Item(iCnt, 2))
End Try
Select Case sOutput_Report_Format
Case Is = "Excel"
sOutputFormatFileType = ".xls"
Case Is = "PDF"
sOutputFormatFileType = ".pdf"
Case Is = "MHTML"
sOutputFormatFileType = ".mhtml"
End Select
Try
Dim stream As FileStream = System.IO.File.Create(sOutput_SubFolder_Location & sOutput_Report_Name & sOutputFormatFileType, result.Length)
stream.Write(result, 0, result.Length)
stream.Close()
Catch e As Exception
Me.Cursor = Cursors.Default
MessageBox.Show(e.Message)
End Try
-
When I want to pass variables from VB.Net to Report Writer I just use something like this:
Dim IEForm As New frmIEWindow
With IEForm
.URLAddress = "http://SQL/ReportServer?%2fTrustSafekeepingReports%2fHoldingListForAccount&AccountID=" & AccountID & "&rc
arameters=false".ShowDialog()
End With
-
I want to thank you both for the replies.
To be honest, I am not familiar with the web server, so I probably need to read up some materials before I can implement the way Stuart provided. But I believe if I understand it, it will give me a great flexibility using reporting services. If someone can point me to an article regarding codes in .net to handle reporting services, I will really appreciate that.
I used parameters in reporting services only for part of the where clause not the whole where clause, maybe it'll still work. But after I re examine the project, I maybe can use datagrid to display the data within .net. User's selection only affect the records (row numbers) not the number of columns - I thought I needed to use the matrix in reproting services for dynamic column adding.
But thanks again for your replies,
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply