Remove link on column when export

when export report you want remove hyperlink in column drilldown



reporting service question next page

I have this report that I am trying to fix, it shows “1 Of 2?” (with that question mark) while it should show “1 Of 5”. Here is an image
Report toolbar showing question mark
How can i fix this? if anyone can please help.
Fix : 
this.objReportViewer.PageCountMode = PageCountMode.Actual;

credit :

Reporting service parameter type datetime culture thai (ไทย)

ปัญหาต่อมาถ้าเราทำการกำหนด ค่า parameter มี type datetime แล้วเรากำหนดค่า default value เป็นปี ค.ศ. (กรณีที่ report server เป็น en ซึ่งส่วนใหญ่ก็เป็นแบบนั้น) แต่ ในหน้าเว็บเราใช้ culture เป็น th-TH default value ที่ได้จะเป็นค่า ปี ค.ศ. แต่ culture เป็นไทย พอส่งเข้า report server ก็จะทำให้ date ผิด


default value  –   get parameter value – set to report server
1-1-1900                 1-1-1900                   1-1-1357          – Min value datetime ของ sql server 1900

คือตอน get มาทำไมไม่ดู culture ไปด้วยละไอ้กาก

เห็นไหมว่า default เป็นอะไรมันก็ขึ้นมาแบบนั้นไม่ได้สนใจ culture

ปัญหาแก้ไขโดยการ ก่อนส่ง parameter value จาก code behind ก็กำหนดค่า default ซะก็จบ

Refresh Method not render report

resolve this issue u pass all value parameter
u allow blank or null parameter it complete in report server web
but not enough in asp page u pass default value from code behind can’t pass null to paramter

ปัญหาคือ ต่อให้เรา set parameter เป็น Allow Blank และ Allow Null แล้ว แต่ถ้าเราไม่กำหนดค่า default ให้กับ parameter ตัวนั้น มันก็จะไม่สามารถ render จากฟังก์ชั่น refresh ได้ต้องกด view report จาก Report Viewer Control เอา

Parameter type datetime

เวลาเราส่ง parameter เข้า reporting service นั้นจากที่เราเลือกวันที่ในหน้า report เหมือนมันจะ text แต่จริงมันส่ง object type เป็น datetime นะจ๊ะ


New ReportParameter(objitem.paraName, objitem.paraValue.ToString())

มันรับแต่ value ที่เป็น string คือมันรับ value อย่างเดียวจริงๆ อะไม่ได้ดู object type
สิ่งที่ต้อง concern คือ ถ้าใน store procedure เรารับ parameter เป็น datetime แล้วไซร้
ไอ้ตรงที่เราส่ง parameter จาก code behind นั้นมึงต้องคำนึงว่า store procedure สามารถ convert
ค่านั้นไปเป็น datetime object ได้หรือไม่

แต่ถ้าเลือกจากหน้า web report server ก็ชิวๆ เพราะเหมือน string ก็จริงแต่มันให้ค่า object type เป็น

ไม่ต้องงง ประเด็นเลยทำไม microsoft ไม่ทำให้ ReportParameter สามารถระบุไปได้เลยว่า ค่าที่จะส่งนั้น Object Type เป็นอะไร เพราะใน reporting service แม่งเสือกระบุได้ไง ตามภาพเลย

ที่เครื่อง setting region date เป็น United Kingdom และ custom format เป็น dd-MM-yyyy ด้วย
แต่ในโปรแกรมบังคับ format ไว้ เป็น dd/MM/yyyy และเป็น th-TH
 แอบฉลาดนะนิรับ parameter ตาม culture นั้นเลยถ้าใส่เป็น 2012 แบบนี้ error ด้วยนะ
ที่นี้มาดูบน report server บ้าง
เห็นไหมว่าเราสามารถใส่ได้ตาม culture บน server เลย 

เป็นการยืนยันว่ามันส่ง parameter แบบมี object type จริงๆ นะเธออออ
ปล. เพราะใน object datetime มันจะเก็บ culture ไว้ให้ด้วยว่ามี culture เป็นอะไรที่มันฉลาดอีกนิดคือถ้าไม่ใช่ ค.ศ. มัน convert ให้ด้วย เลิฟๆ ไม่งั้นกูคงปวดกบาลอีกพอสมควร

Past Table Value Parameter to Reporting Services

Using Table-Valued Parameters With SQL Server Reporting Services

In my last post I talked about using table-valued parameters to pass a list of integer values to a stored procedure without resorting to using comma-delimited strings and parsing out each value into a TABLE variable. In this post I’ll extend the “Customer Transaction Summary” report example to see how we might leverage this same stored procedure from within an SQL Server Reporting Services (SSRS) report. I’ve worked with SSRS off and on for the past several years and have generally found it to be a very useful tool for building nice-looking reports for end users quickly and easily. That said, I’ve been frustrated by SSRS from time to time when seemingly simple things are difficult to accomplish or simply not supported at all. I thought that using table-valued parameters from within a SSRS report would be simple, but unfortunately I was wrong.

Customer Transaction Summary Example

Let’s take the “Customer Transaction Summary” report example from the last post and try to plug that same stored procedure into an SSRS report. Our report will have three parameters:
  1. Start Date – beginning of the date range for which the report will summarize customer transactions
  2. End Date – end of the date range for which the report will summarize customer transactions
  3. Customer Ids – One or more customer Ids representing the customers that will be included in the report
The simplest way to get started with this report will be to create a new dataset and point it at our Customer Transaction Summary report stored procedure (note that I’m using SSRS 2012 in the screenshots below, but there should be little to no difference with SSRS 2008):
When you initially create this dataset the SSRS designer will try to invoke the stored procedure to determine what the parameters and output fields are for you automatically. As part of this process the following dialog pops-up:
Obviously I can’t use this dialog to specify a value for the ‘@customerIds’ parameter since it is of the IntegerListTableType user-defined type that we created in the last post. Unfortunately this really throws the SSRS designer for a loop, and regardless of what combination of Data Type, Pass Null Value, or Parameter Value I used here, I kept getting this error dialog with the message, “Operand type clash: nvarchar is incompatible with IntegerListTableType“.
This error message makes some sense considering that the nvarchar type is indeed incompatible with the IntegerListTableType, but there’s little clue given as to how to remedy the situation. I don’t know for sure, but I think that behind-the-scenes the SSRS designer is trying to give the @customerIds parameter an nvarchar-typed SqlParameter which is causing the issue. When I first saw this error I figured that this might just be a limitation of the dataset designer and that I’d be able to work around the issue by manually defining the parameters. I know that there are some special steps that need to be taken when invoking a stored procedure with a table-valued parameter from ADO .NET, so I figured that I might be able to use some custom code embedded in the report  to create a SqlParameter instance with the needed properties and value to make this work, but the “Operand type clash” error message persisted.

The Text Query Approach

Just because we’re using a stored procedure to create the dataset for this report doesn’t mean that we can’t use the ‘Text’ Query Type option and construct an EXEC statement that will invoke the stored procedure. In order for this to work properly the EXEC statement will also need to declare and populate an IntegerListTableType variable to pass into the stored procedure. Before I go any further I want to make one point clear: this is a really ugly hack and it makes me cringe to do it. Simply put, I strongly feel that it should not be this difficult to use a table-valued parameter with SSRS. With that said, let’s take a look at what we’ll have to do to make this work.

Manually Define Parameters

First, we’ll need to manually define the parameters for report by right-clicking on the ‘Parameters’ folder in the ‘Report Data’ window. We’ll need to define the ‘@startDate’ and ‘@endDate’ as simple date parameters. We’ll also create a parameter called ‘@customerIds’ that will be a mutli-valued Integer parameter:
In the ‘Available Values’ tab we’ll point this parameter at a simple dataset that just returns the CustomerId and CustomerName of each row in the Customers table of the database or manually define a handful of Customer Id values to make available when the report runs.
Once we have these parameters properly defined we can take another crack at creating the dataset that will invoke the ‘rpt_CustomerTransactionSummary’ stored procedure. This time we’ll choose the ‘Text’ query type option and put the following into the ‘Query’ text area:
   1: exec('declare @customerIdList IntegerListTableType ' + @customerIdInserts + 
   2: ' EXEC rpt_CustomerTransactionSummary 
   3: @startDate=''' + @startDate + ''', 
   4: @endDate='''+ @endDate + ''', 
   5: @customerIds=@customerIdList')
By using the ‘Text’ query type we can enter any arbitrary SQL that we we want to and then use parameters and string concatenation to inject pieces of that query at run time. It can be a bit tricky to parse this out at first glance, but from the SSRS designer’s point of view this query defines three parameters:
  1. @customerIdInserts – This will be a Text parameter that we use to define INSERT statements that will populate the @customerIdList variable that is being declared in the SQL. This parameter won’t actually ever get passed into the stored procedure. I’ll go into how this will work in a bit.
  2. @startDate – This is a simple date parameter that will get passed through directly into the @startDate parameter of the stored procedure on line 3.
  3. @endDate – This is another simple data parameter that will get passed through into the @endDate parameter of the stored procedure on line 4.
At this point the dataset designer will be able to correctly parse the query and should even be able to detect the fields that the stored procedure will return without needing to specify any values for query when prompted to. Once the dataset has been correctly defined we’ll have a @customerIdInserts parameter listed in the ‘Parameters’ tab of the dataset designer. We need to define an expression for this parameter that will take the values selected by the user for the ‘@customerIds’ parameter that we defined earlier and convert them into INSERT statements that will populate the @customerIdList variable that we defined in our Text query. In order to do this we’ll need to add some custom code to our report using the ‘Report Properties’ dialog:
Any custom code defined in the Report Properties dialog gets embedded into the .rdl of the report itself and (unfortunately) must be written in VB .NET. Note that you can also add references to custom .NET assemblies (which could be written in any language), but that’s outside the scope of this post so we’ll stick with the “quick and dirty” VB .NET approach for now. Here’s the VB .NET code (note that any embedded code that you add here must be defined in a static/shared function, though you can define as many functions as you want):
   1: Public Shared Function BuildIntegerListInserts(ByVal variableName As String, ByVal paramValues As Object()) As String
   2:     Dim insertStatements As New System.Text.StringBuilder()
   3:     For Each paramValue As Object In paramValues
   4:         insertStatements.AppendLine(String.Format("INSERT {0} VALUES ({1})", variableName, paramValue))
   5:     Next
   6:     Return insertStatements.ToString()
   7: End Function
This method takes a variable name and an array of objects. We use an array of objects here because that is how SSRS will pass us the values that were selected by the user at run-time. The method uses a StringBuilder to construct INSERT statements that will insert each value from the object array into the provided variable name.
Once this method has been defined in the custom code for the report we can go back into the dataset designer’s Parameters tab and update the expression for the ‘@customerIdInserts’ parameter by clicking on the button with the “function” symbol that appears to the right of the parameter value. We’ll set the expression to:
   1: =Code.BuildIntegerListInserts("@customerIdList ", Parameters!customerIds.Value)
In order to invoke our custom code method we simply need to invoke “Code.” and pass in any needed parameters. The first parameter needs to match the name of the IntegerListTableType variable that we used in the EXEC statement of our query. The second parameter will come from the Value property of the ‘@customerIds’ parameter (this evaluates to an object array at run time).
Finally, we’ll need to edit the properties of the ‘@customerIdInserts’ parameter on the report to mark it as a nullable internal parameter so that users aren’t prompted to provide a value for it when running the report.

Limitations And Final Thoughts

When I first started looking into the text query approach described above I wondered if there might be an upper limit to the size of the string that can be used to run a report. Obviously, the size of the actual query could increase pretty dramatically if you have a parameter that has a lot of potential values or you need to support several different table-valued parameters in the same query. I tested the example Customer Transaction Summary report with 1000 selected customers without any issue, but your mileage may vary depending on how much data you might need to pass into your query.
If you think that the text query hack is a lot of work just to use a table-valued parameter, I agree! I think that it should be a lot easier than this to use a table-valued parameter from within SSRS, but so far I haven’t found a better way. It might be possible to create some custom .NET code that could build the EXEC statement for a given set of parameters automatically, but exploring that will have to wait for another post. For now, unless there’s a really compelling reason or requirement to use table-valued parameters from SSRS reports I would probably stick with the tried and true “join-multi-valued-parameter-to-CSV-and-split-in-the-query” approach for using mutli-valued parameters in a stored procedure.

Authenticate Reporting Service (SSRS) ReportServerCredentials

โดนปกติเราจะใช้การ authenticate reporting servcie ด้วย method ReportServerCredentials

        ReportViewer1.ServerReport.ReportServerCredentials = New ReportCredentials(“Pokpong”, “Kak”, “DomainName”)
        ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote
        ReportViewer1.ServerReport.ReportServerUrl = New System.Uri(strUrl)
        ReportViewer1.ServerReport.ReportPath = “/CSWExternalSector/RptExtBOPBath”

ส่วน class ReportCredentials

Public Class ReportCredentials
    Implements Microsoft.Reporting.WebForms.IReportServerCredentials

    Dim _userName, _password, _domain As String

    Sub New(ByVal userName As String, ByVal password As String, ByVal domain As String)
        _userName = userName
        _password = password
        _domain = domain
    End Sub
    Public Function GetFormsCredentials(ByRef authCookie As System.Net.Cookie, ByRef userName As String, ByRef password As String, ByRef authority As String) As Boolean Implements Microsoft.Reporting.WebForms.IReportServerCredentials.GetFormsCredentials

        userName = _userName
        password = _password
        authority = _domain
        authCookie = New System.Net.Cookie(“.ASPXAUTH”, “.ASPXAUTH”, “/”, “Domain”)
        Return True
    End Function

    Public ReadOnly Property ImpersonationUser() As System.Security.Principal.WindowsIdentity Implements Microsoft.Reporting.WebForms.IReportServerCredentials.ImpersonationUser
            Return Nothing
        End Get
    End Property

    Public ReadOnly Property NetworkCredentials() As System.Net.ICredentials Implements Microsoft.Reporting.WebForms.IReportServerCredentials.NetworkCredentials
            Return New System.Net.NetworkCredential(_userName, _password, _domain)
        End Get
    End Property
End Class

ปัญหาของการทำ Credential แบบนี้คือมันต้อง hard code username , password ลงไปเราจะทำยังไงละให้user นั้น dynamic ตาม user profile ของเครื่อง

เราแก้ปัญหาด้วยการ ไม่ใช้


identity impersonate=”true”
ลงไปใน web.config แทน