HV-Lima Peru

Offshore your .NET/SQL Development to Latin America! Languages: en-de-es

Category Archives: Reporting Services

How to join in T-SQL logically a multi-value Reporting Services Parameter with a Freetext Entry?

From Entry-Logic to the final T-SQL Program:
The most interesting aspect in the following procedure is the one-to-one mapping between the discovered cases in 1., the flowchart in 2. and the T-SQL program in 3..
Initially apparently not very elegant goto-programming in SQL brings here two big advantages:
a. Nothing is easier to discover as a coding error, so when in later years another programmer needs to change the SQL logic, every aspect can be followed, reconstructed and changed, step-by-step.
b. Any ending goto-branch in T-SQL can be implemented by its own stored procedure, which leads to the best possible utilization of precompiled SQL execution plans.
1. Case Construction:
– in cases 9 to 16: it doesn’t matter what else the user clicks or if she enters any freetext, ALL incl. NULL means ALL.
– since SSRS doesn’t accept no entry in a multi-value field, the value NOTHING was invented. This means: to enter nothing in the multi-value field, the value Nothing must be selected.

2. Logic-Flowchart:
The 16 cases from above are flagged (A, B, C, D)

3. T-SQL Program:
Like above, the 16 cases are identified in parentheses (abcd)

–Case 9-16
IF CHARINDEX(‘All’,@SearchValues)<>0 GOTO ALLL –A ‘All’=1??? selected
IF CHARINDEX(‘Nothing’,@SearchValues) = 0 — ‘Nothing’=0 not selected (00)
IF @SearchValues <>          — there is Multi-Input=1 (001)
       IF @strOrElse <>           — there is Else-Input=1 (0011)
        –Case 4
        –Case 3
        GOTO MULTI –B           — there is NO Else-Input=0 (0010)
IF CHARINDEX(‘Nothing’,@SearchValues) <> 0     –‘Nothing’=1 selected (01)
  –Cases 7 and 8 will be considered in later release
  –if @SearchValues <> ” GOTO FIN   — there is Multi-Input=1 (011?)
       –Case 6
       IF @strOrElse = GOTO FIN –Case5 (0100)
       GOTO EELSE –D there is Else-Input=1 (0101)


How to debug custom .NET Assembly in SSRS, using a single Instance of Visual Studio?

Create a new report project using Visual Studio.
1. At the time you create a report project, Visual Studio also creates a solution to contain it.
2. Add a new Class Library project to the existing solution. Make sure that the report project is set as the startup project.

3. How2 call functions in your Class Library, see here
4. In Solution Explorer, select the solution.
5. On the Project menu, click Properties

6. The Solution Property Pages dialog box opens.
7. In the left pane, expand Common Properties if necessary, and click Project Dependencies. Select the report project from the Project drop-down list. Select your assembly project in the Depends On list.

8. Click OK to save the changes, and close the Property Pages dialog.
9. In Solution Explorer, select your custom assembly project.
10. On the Project menu, click Properties.
11. The Project Property Pages dialog box opens.
12. In the left pane, expand Configuration Properties, and click Build if you’re in a C# project or Compile if you’re in a Visual Basic project.
13. On the Build/Compile page, enter the path to the Report Designer folder. By default, this is C:\Program Files\Microsoft SQL Server. Server\90\Tools\Binn\VSShell\Common7\IDE) in the Output Path text box. This builds and deploys an updated version of your custom assembly directly to Report Designer before your report is executed.

14. Disable Deploy in the DebugLocal Config in Configuration Manager
15. Set the Active Solution Configuration to DebugLocal.

16. Once you have designed your report and developed your custom assembly, set breakpoints in your custom assembly code.
17. Run the report under DebugLocal mode by pressing the F5 key. When the report executes in the pop-up preview window, the debugger hits any breakpoints that correspond to executable code in your assembly. Use F11 to step through your custom assembly code.
18. Special Hack: The moment you decide to make the next Build (inside to the VS2005’s private assemblies directory), this will not function, because the assembly is hold open by VS. Instead of shutting down/reopening VS, act smarter: rename your DLL (rename works, delete not).

How to call static Class Libraries’ Functions in your RDL

All depends on the chosen Namespace.

The above class library is called Test, containing a VB-Class RetStrings.
The namespace has been configured as follows:

It follows the class:

When you receive a foreign Assembly (DLL), then have a look inside it with ILDASM:

The output of ILDASM shows, that "reflect" is a C#-static- (VB-shared-) method which returns a string.
You see also the whole namespace, so you know now, after this inspection, how to reference it in SSRS, like in the following picture.
Calling this static (shared) method is that simple, after you became it to know:
(Apodo is nickname in Castellano, which is in this case an SSRS parameter)

How to set the Report User Language OnInit depending on the Client’s Culture?

Create a Workinglanguage Parameter with default value of =code.getLanguage(user!Language)

Create the following User Code:

Public initLanguage As String

Protected Overrides Sub OnInit()
    initLanguage = Report.User!Language
End Sub

Public Function getLanguage(ByVal userLang As String) As Integer
    If instr(userLang, "en") Then Return 1
    If instr(userLang, "de") Then Return 2
    If instr(userLang, "fr") Then Return 3
    If instr(userLang, "it") Then Return 4
    Return 1
End Function

How to forcibly split long running Matrix rows

Problem: if the first Matrix column contains thousands of rows it doesn’t help to set any InteractiveHeight parameter in BIDS.
This problem exists since SSRS 2000 and persists up to the newest SQL Server 2005 SP2 (Feb. 2007).
In IE-clients (IE 6 and IE 7) this leads to a long waiting time for the first page and almost unresponsive behavior because of too much UI-redrawing.
For Winform Apps that use the freely redistributable MS ReportViewer-Control (this includes the newest SP1 as of March 2007) this leads to a total SOAP time-out and hanging IIS (100% and easily 300 MB of occupied memory on the Reporting Server).

So: In the following picture a matrix is embedded into a list.
The first column (u ) will be forced to break with the hbu-matrix-brute-pagebreak pattern.

Set grouping to a user defined function code.setRowAggregate()

Insert the following function in user code:
This will insert a hard-break on any 100 input rows.

Function setRowAggregate() As Integer
    Static Dim a As Integer = 0
    Static Dim b As Integer = 1
    If a < 100 Then
        a += 1
        a = 1
        b += 1
    End If
    Return b
End Function