Offshore your .NET/SQL Development to Latin America! Languages: en-de-es
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.
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)
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)
GOTO MULTIORELSE –C
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?)
IF @strOrElse = ” GOTO FIN –Case5 (0100)
GOTO EELSE –D there is Else-Input=1 (0101)
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).
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)
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
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
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).
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