http://reportingservices.blog.co.uk/en-UKMokoFeed10http://reportingservices.blog.co.uk/http://data5.blog.de/design/preview/22/87799b2ec0fa9142d3662973f15181_160x200.jpghttp://reportingservices.blog.co.uk/2009/07/07/using-a-select-statement-with-a-searched-case-expression-6464668/tag:reportingservices.blog.co.uk,2009-07-07:/2009/07/07/using-a-select-statement-with-a-searched-case-expression-6464668/Tue, 07 Jul 2009 11:00:59 +0200 <p><strong> Using a SELECT statement with a searched CASE expression </strong></p> <p>SELECT ProductNumber, Name, 'Price Range' =<br> CASE<br> WHEN ListPrice = 0 THEN 'Mfg item - not for resale'<br> WHEN ListPrice <50 THEN 'Under $50'<br> WHEN ListPrice >= 50 and ListPrice <250 THEN 'Under $250'<br> WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'<br> ELSE 'Over $1000'<br> END<br> FROM Production.Product<br> ORDER BY ProductNumber ;</p> <p> <small> <a href="http://reportingservices.blog.co.uk/2009/07/07/using-a-select-statement-with-a-searched-case-expression-6464668/#comments">Comments</a> </small> </p>http://reportingservices.blog.co.uk/2009/07/07/using-a-select-statement-with-a-searched-case-expression-6464668/#commentshttp://reportingservices.blog.co.uk/2009/03/27/reporting-services-tip-5842288/tag:reportingservices.blog.co.uk,2009-03-27:/2009/03/27/reporting-services-tip-5842288/Fri, 27 Mar 2009 15:36:16 +0100 <p>Use UNION ALL rather than UNION wherever necessary. This will speed up the query exceution of the report </p> <p> <small> <a href="http://reportingservices.blog.co.uk/2009/03/27/reporting-services-tip-5842288/#comments">Comments</a> </small> </p>union-reporting-serviceshttp://reportingservices.blog.co.uk/2009/03/27/reporting-services-tip-5842288/#commentshttp://reportingservices.blog.co.uk/2009/03/27/create-row-numbers-based-on-the-rows-available-in-a-table-5842255/tag:reportingservices.blog.co.uk,2009-03-27:/2009/03/27/create-row-numbers-based-on-the-rows-available-in-a-table-5842255/Fri, 27 Mar 2009 15:30:32 +0100 <p>SELECT ROWID=IDENTITY(int,1,1) , EMPID, FNAME, LNAME<br> INTO EMPLOYEE2 FROM EMPLOYEE ORDER BY EMPID</p> <p> <small> <a href="http://reportingservices.blog.co.uk/2009/03/27/create-row-numbers-based-on-the-rows-available-in-a-table-5842255/#comments">Comments</a> </small> </p>rowid-rownumber-identity-rowshttp://reportingservices.blog.co.uk/2009/03/27/create-row-numbers-based-on-the-rows-available-in-a-table-5842255/#commentshttp://reportingservices.blog.co.uk/2009/03/27/parse-numbers-from-alphanumeric-strings-5842105/tag:reportingservices.blog.co.uk,2009-03-27:/2009/03/27/parse-numbers-from-alphanumeric-strings-5842105/Fri, 27 Mar 2009 15:04:55 +0100 <p>CREATE FUNCTION [dbo].[ParseNumbers]<br> (<br> @string VARCHAR(50)<br> )<br> RETURNS VARCHAR(50)<br> AS<br> BEGIN<br> DECLARE @IncorrectCharLoc SMALLINT<br> SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)<br> WHILE @IncorrectCharLoc > 0<br> BEGIN<br> SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')<br> SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)<br> END<br> SET @string = @string<br> RETURN @string<br> END </p> <p> <small> <a href="http://reportingservices.blog.co.uk/2009/03/27/parse-numbers-from-alphanumeric-strings-5842105/#comments">Comments</a> </small> </p>http://reportingservices.blog.co.uk/2009/03/27/parse-numbers-from-alphanumeric-strings-5842105/#commentshttp://reportingservices.blog.co.uk/2009/02/17/create-function-propercase-text-as-varchar-8000-returns-varchar-5594420/tag:reportingservices.blog.co.uk,2009-02-17:/2009/02/17/create-function-propercase-text-as-varchar-8000-returns-varchar-5594420/Tue, 17 Feb 2009 18:08:44 +0100 <p>create function ProperCase(@Text as varchar(8000))<br> returns varchar(8000)<br> as<br> begin<br> declare @Reset bit;<br> declare @Ret varchar(8000);<br> declare @i int;<br> declare @c char(1);</p> <p> select @Reset = 1, @i=1, @Ret = '';</p> <p> while (@i < = len(@Text))<br> select @c= substring(@Text,@i,1),<br> @Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,<br> @Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,<br> @i = @i +1<br> return @Ret<br> end </p> <p> <small> <a href="http://reportingservices.blog.co.uk/2009/02/17/create-function-propercase-text-as-varchar-8000-returns-varchar-5594420/#comments">Comments</a> </small> </p>http://reportingservices.blog.co.uk/2009/02/17/create-function-propercase-text-as-varchar-8000-returns-varchar-5594420/#commentshttp://reportingservices.blog.co.uk/2009/02/13/rs2005-convert-to-proper-case-5568799/tag:reportingservices.blog.co.uk,2009-02-13:/2009/02/13/rs2005-convert-to-proper-case-5568799/Fri, 13 Feb 2009 21:14:02 +0100 <p>Public Shared Function ConvertToProperCase(ByVal s As String) As String<br> If Not IsNothing(s) then<br> Return UCase(Left(s,1)) & LCase(Right(s,Len(s)-1))<br> End If<br> End Function </p> <p> <small> <a href="http://reportingservices.blog.co.uk/2009/02/13/rs2005-convert-to-proper-case-5568799/#comments">Comments</a> </small> </p>rs2005-convert-proper-casehttp://reportingservices.blog.co.uk/2009/02/13/rs2005-convert-to-proper-case-5568799/#commentshttp://reportingservices.blog.co.uk/2008/07/30/datepicker-control-for-sql-rs2005-sp2-4520864/tag:reportingservices.blog.co.uk,2008-07-30:/2008/07/30/datepicker-control-for-sql-rs2005-sp2-4520864/Wed, 30 Jul 2008 16:34:14 +0200 <p>Dates in the Datetimepicker control appear in the incorrect format when you view a report of SQL Server 2005 Reporting Services with SP2 on a server that is set to a UK locale</p> <p><a href="http://support.microsoft.com/kb/938823/en-us"><a href="http://support.microsoft.com/kb/938823/en-us">http://support.microsoft.com/kb/938823/en-us</a></a></p> <p>In this scenario, the date appears in the mm/dd/yyyy format in the Datetimepicker control. However, you expect that the date appears in the dd/mm/yyyy format. </p> <p> <small> <a href="http://reportingservices.blog.co.uk/2008/07/30/datepicker-control-for-sql-rs2005-sp2-4520864/#comments">Comments</a> </small> </p>datepicker-rs2005-reporting-serviceshttp://reportingservices.blog.co.uk/2008/07/30/datepicker-control-for-sql-rs2005-sp2-4520864/#commentshttp://reportingservices.blog.co.uk/2007/02/02/data_migration_in_oracle~1667559/tag:reportingservices.blog.co.uk,2007-02-02:/2007/02/02/data_migration_in_oracle~1667559/Fri, 02 Feb 2007 12:13:00 +0100 <p>PROCEDURE procDeptMigration<br> IS<br> --<br> -- Cursor to retrieve all the old dept data<br> --<br> CURSOR cu_dept IS<br> SELECT * FROM dept;<br> crec_dept cu_dept%ROWTYPE;</p> <p>BEGIN </p> <p>FOR crec_dept IN cu_dept<br> LOOP</p> <p>--insert in the new table<br> INSERT INTO DEPARTMENT<br> VALUES<br> (crec_dept.deptno, crec_dept.dname, crec_dept.loc);</p> <p>-- Display the entire able on screen<br> DBMS_OUTPUT.PUT_LINE('DEPTNO : '||crec_dept.deptno );<br> DBMS_OUTPUT.PUT_LINE('DNAME : '||crec_dept.dname );<br> DBMS_OUTPUT.PUT_LINE('LOC : '||crec_dept.loc );</p> <p>END LOOP;</p> <p>EXCEPTION<br> WHEN OTHERS THEN<br> DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM||' AddressCode '||crec_dept.deptno);</p> <p>END procDeptMigration; </p> <p> <small> <a href="http://reportingservices.blog.co.uk/2007/02/02/data_migration_in_oracle~1667559/#comments">Comments</a> </small> </p>http://reportingservices.blog.co.uk/2007/02/02/data_migration_in_oracle~1667559/#commentshttp://reportingservices.blog.co.uk/2007/02/02/identify_and_delete_duplicate_rows_in_a_~1667490/tag:reportingservices.blog.co.uk,2007-02-02:/2007/02/02/identify_and_delete_duplicate_rows_in_a_~1667490/Fri, 02 Feb 2007 11:59:07 +0100 <p>create table test (id int identity(1,1), code char(5))<br> insert into test (code) values ('a')<br> insert into test (code) values ('a')<br> insert into test (code) values ('a')<br> insert into test (code) values ('b')<br> insert into test (code) values ('b')<br> insert into test (code) values ('b')<br> insert into test (code) values ('c')<br> insert into test (code) values ('d')<br> insert into test (code) values ('d')</p> <p>--Identify the rows<br> select code from test group by code having count(*) > 1</p> <p>--Remove the rows<br> delete test<br> where id > (<br> select min(m.id)<br> from test m<br> where m.code = test.code<br> ) </p> <p> <small> <a href="http://reportingservices.blog.co.uk/2007/02/02/identify_and_delete_duplicate_rows_in_a_~1667490/#comments">Comments</a> </small> </p>http://reportingservices.blog.co.uk/2007/02/02/identify_and_delete_duplicate_rows_in_a_~1667490/#commentshttp://reportingservices.blog.co.uk/2007/01/15/group_total_summary~1557916/tag:reportingservices.blog.co.uk,2007-01-15:/2007/01/15/group_total_summary~1557916/Mon, 15 Jan 2007 16:29:32 +0100 <p>To get grand total summary for individual group totals, add the group header or footer cells with the correct value,you may rename this cell as grpTotal.Then add a textbox in the Report Footer and use the expression =Sum(ReportItems!grpTotal.Value).This will sum the group totals. </p> <p> <small> <a href="http://reportingservices.blog.co.uk/2007/01/15/group_total_summary~1557916/#comments">Comments</a> </small> </p>http://reportingservices.blog.co.uk/2007/01/15/group_total_summary~1557916/#commentshttp://reportingservices.blog.co.uk/2006/09/22/title~1150574/tag:reportingservices.blog.co.uk,2006-09-22:/2006/09/22/title~1150574/Fri, 22 Sep 2006 15:14:17 +0200 <p>Create PROCEDURE SP_ExportDataUsingTemplate @File_Name as varchar(50) = ''<br> AS<br> BEGIN<br> SET NOCOUNT ON</p> <p> DECLARE @Cmd varchar(1000)<br> DECLARE @fn varchar(500)<br> DECLARE @provider varchar(100)<br> DECLARE @ExcelString varchar(100)</p> <p>-- New File Name to be created<br> IF @File_Name = ''<br> Select @fn = 'D:\Test.xls'<br> ELSE<br> Select @fn = 'D:\' + @File_Name + '.xls'</p> <p>-- FileCopy command string formation<br> SELECT @Cmd = 'Copy D:\Template.xls ' + @fn</p> <p>-- FielCopy command execution through Shell Command<br> EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT</p> <p>-- Mentioning the OLEDB Rpovider and excel destination filename<br> set @provider = 'Microsoft.Jet.OLEDB.4.0'<br> set @ExcelString = 'Excel 8.0; Database=' + @fn</p> <p>-- Executing the OPENROWSET Command for copying the select contents to Excel sheet.<br> exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT FirstName,LastName,Phone,Address,City,State,Zip FROM [Sheet1$]'')<br> select au_fname as FirstName,au_lname as LastName,phone,address,city,State,Zip from authors')</p> <p> exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT StoreId, OrderNo, OrderDate, Quantity FROM [Sheet2$]'')<br> select stor_id as StoreId,Ord_Num as OrderNo,Ord_Date as OrderDate,qty as Quantity from sales')</p> <p> SET NOCOUNT OFF<br> END</p> <p>/*------------sample statements-------------</p> <p>use pubs<br> exec SP_ExportDataUsingTemplate 'AuthorsAndOrders'<br> select * from sales (NOLOCK)<br> select * from authors</p> <p>*/------------------------------------------- </p> <p> <small> <a href="http://reportingservices.blog.co.uk/2006/09/22/title~1150574/#comments">Comments</a> </small> </p>http://reportingservices.blog.co.uk/2006/09/22/title~1150574/#commentshttp://reportingservices.blog.co.uk/2006/09/22/export_data_from_sql_server_to_excel~1150564/tag:reportingservices.blog.co.uk,2006-09-22:/2006/09/22/export_data_from_sql_server_to_excel~1150564/Fri, 22 Sep 2006 15:09:40 +0200 <p>CREATE procedure dbo.ExportData(@filePath as varchar(500))<br> as<br> begin</p> <p> DECLARE @Provider VARCHAR(200),@ExcelString VARCHAR(200),@SQLString VARCHAR(200)<br> DECLARE @Sql AS VARCHAR(800)</p> <p> SET @Provider = '''Microsoft.Jet.OLEDB.4.0'''<br> SET @ExcelString = '''Excel 8.0; Database=' + @filePath + ';HDR=YES'''<br> SET @SQLString = '''SELECT FirstName, LastName, Telephone FROM [Sheet1$]'''</p> <p> SET @SQL='insert into OPENROWSET('+ @Provider + ','+ @ExcelString + ',' + @SQLString + ') SELECT au_fname as FirstName, au_lname as LastName, phone as Telephone from authors'</p> <p> Exec(@Sql)<br> end<br> GO </p> <p> <small> <a href="http://reportingservices.blog.co.uk/2006/09/22/export_data_from_sql_server_to_excel~1150564/#comments">Comments</a> </small> </p>http://reportingservices.blog.co.uk/2006/09/22/export_data_from_sql_server_to_excel~1150564/#commentshttp://reportingservices.blog.co.uk/2005/07/21/switch_function_in_reporting_services/tag:reportingservices.blog.co.uk,2005-07-21:/2005/07/21/switch_function_in_reporting_services/Thu, 21 Jul 2005 11:03:14 +0200 <p>You can use Visual Basic functions to evaluate an input value and return another value depending on the result.</p> <p>The following expression also returns one of three values based on the value of PctComplete, but uses the Switch function instead, which returns the value associated with the first expression in a series that evaluates to true: </p> <p>=Switch(Fields!PctComplete.Value >= .8, "Green", Fields!PctComplete.Value >= .5, "Amber", Fields!PctComplete.Value < .5, "Red")</p> <p> <small> <a href="http://reportingservices.blog.co.uk/2005/07/21/switch_function_in_reporting_services/#comments">Comments</a> </small> </p>http://reportingservices.blog.co.uk/2005/07/21/switch_function_in_reporting_services/#commentshttp://reportingservices.blog.co.uk/2005/07/21/alter_the_query_that_is_used_to_retrieve/tag:reportingservices.blog.co.uk,2005-07-21:/2005/07/21/alter_the_query_that_is_used_to_retrieve/Thu, 21 Jul 2005 11:00:43 +0200 <p>In some complex reports, you may want to vary the query that is used to retrieve data for the report. For example, you may want to provide users with a list of departments by which to filter data, or allow them to view data for all departments. In Transact-SQL, this requires different queries: one with a WHERE clause, and one without. You can use a query expression to build a dynamic query that changes depending on user input. </p> <p>This expression, when used as the command text in a SQL Server dataset, builds a query that retrieves all data if the user selects All (All is tied to a value of 0 in the parameter), or builds a query with a WHERE clause if the user selects a specific department. </p> <p>="SELECT FirstName, LastName, Title FROM Employee" & IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " & Parameters!Department.Value & ")") & " ORDER BY LastName"</p> <p> <small> <a href="http://reportingservices.blog.co.uk/2005/07/21/alter_the_query_that_is_used_to_retrieve/#comments">Comments</a> </small> </p>http://reportingservices.blog.co.uk/2005/07/21/alter_the_query_that_is_used_to_retrieve/#commentshttp://reportingservices.blog.co.uk/2005/07/21/hide_parameters_in_the_report/tag:reportingservices.blog.co.uk,2005-07-21:/2005/07/21/hide_parameters_in_the_report/Thu, 21 Jul 2005 10:57:11 +0200 <p>Use rc:parameters=false to hide the parameters.</p> <p><a href="http://rsServer1/reportserver?/Commercial">http://rsServer1/reportserver?/Commercial</a> Analytics/Units and Net Sales&rc:toolbar=False&rc:para­meters=false&CategoryValueID=5­5 </p> <p> <small> <a href="http://reportingservices.blog.co.uk/2005/07/21/hide_parameters_in_the_report/#comments">Comments</a> </small> </p>http://reportingservices.blog.co.uk/2005/07/21/hide_parameters_in_the_report/#commentshttp://reportingservices.blog.co.uk/2005/07/21/sp2_print_landscape_issue/tag:reportingservices.blog.co.uk,2005-07-21:/2005/07/21/sp2_print_landscape_issue/Thu, 21 Jul 2005 10:52:35 +0200 <p>There is an oversight in the documentation of the new client print functionality of Reporting Services Service Pack 2. SP2 has a new behavior that extracts the page size and margin information at report publishing time so that it can be used by the print control. If you look at the Property column of the Catalog table in the ReportServer database, you will see the following on a report published with SP2:</p> <p><Properties><br> ...<br> <PageHeight<215.9</PageHeight><br> <PageWidth<279.4</PageWidth><br> <TopMargin<19.05</TopMargin><br> <BottomMargin<19.05</BottomMargin><br> <LeftMargin<19.05</LeftMargin><br> <RightMargin<19.05</RightMargin><br> </Properties></p> <p>Reports that were published before SP2 do not contain these properties so they will default to 8.5" x 11" (portrait) with .5" margins. You can either set these properties by republishing the report definition through Report Manager or via the SetReportDefintion method in the Web Services API. Below is a script that you can use with the Reporting Services script host (rs.exe) to republish all reports on the server:</p> <p>Public Sub Main()<br> Dim definition As [Byte]() = Nothing<br> Dim item As CatalogItem = Nothing<br> Dim items() As CatalogItem = Nothing</p> <p> rs.Credentials = System.Net.CredentialCache.DefaultCredentials</p> <p> items = rs.ListChildren("/", True)</p> <p> For Each item In items<br> If item.Type = ItemTypeEnum.Report Then<br> Console.WriteLine("Republishing Report: {0}", item.Path)<br> definition = rs.GetReportDefinition(item.Path)<br> rs.SetReportDefinition(item.Path, definition)<br> End If<br> Next<br> End Sub</p> <p> <small> <a href="http://reportingservices.blog.co.uk/2005/07/21/sp2_print_landscape_issue/#comments">Comments</a> </small> </p>http://reportingservices.blog.co.uk/2005/07/21/sp2_print_landscape_issue/#commentshttp://reportingservices.blog.co.uk/2005/07/15/clear_report_cache/tag:reportingservices.blog.co.uk,2005-07-15:/2005/07/15/clear_report_cache/Fri, 15 Jul 2005 16:51:06 +0200 <p>To clear report cache in the browser user rs:ClearSession=true</p> <p><a href="http://server16/reportserver?%2fApp+Reports+Development%2fSimplePlanningRpt&rs:ClearSession=true">http://server16/reportserver?%2fApp+Reports+Development%2fSimplePlanningRpt&rs:ClearSession=true</a></p> <p>This will create a new version everytime the report is refreshed even when using exisiting parameters. </p> <p> <small> <a href="http://reportingservices.blog.co.uk/2005/07/15/clear_report_cache/#comments">Comments</a> </small> </p>http://reportingservices.blog.co.uk/2005/07/15/clear_report_cache/#commentshttp://reportingservices.blog.co.uk/2005/07/15/convert_numbers_or_currency_to_english_w/tag:reportingservices.blog.co.uk,2005-07-15:/2005/07/15/convert_numbers_or_currency_to_english_w/Fri, 15 Jul 2005 16:45:43 +0200 <p><code><br> Public Function NumToString(ByVal nNumber As Currency) As String</p> <p>Dim bNegative As Boolean<br> Dim bHundred As Boolean</p> <p>If nNumber < 0 Then<br> bNegative = True<br> End If</p> <p>nNumber = Abs(Int(nNumber))</p> <p>If nNumber < 1000 Then<br> If nNumber \ 100 > 0 Then<br> NumToString = NumToString & _<br> NumToString(nNumber \ 100) & " hundred"<br> bHundred = True<br> End If<br> nNumber = nNumber - ((nNumber \ 100) * 100)<br> Dim bNoFirstDigit As Boolean<br> bNoFirstDigit = False<br> Select Case nNumber \ 10<br> Case 0<br> Select Case nNumber Mod 10<br> Case 0<br> If Not bHundred Then<br> NumToString = NumToString & " zero"<br> End If<br> Case 1: NumToString = NumToString & " one"<br> Case 2: NumToString = NumToString & " two"<br> Case 3: NumToString = NumToString & " three"<br> Case 4: NumToString = NumToString & " four"<br> Case 5: NumToString = NumToString & " five"<br> Case 6: NumToString = NumToString & " six"<br> Case 7: NumToString = NumToString & " seven"<br> Case 8: NumToString = NumToString & " eight"<br> Case 9: NumToString = NumToString & " nine"<br> End Select<br> bNoFirstDigit = True<br> Case 1<br> Select Case nNumber Mod 10<br> Case 0: NumToString = NumToString & " ten"<br> Case 1: NumToString = NumToString & " eleven"<br> Case 2: NumToString = NumToString & " twelve"<br> Case 3: NumToString = NumToString & " thirteen"<br> Case 4: NumToString = NumToString & " fourteen"<br> Case 5: NumToString = NumToString & " fifteen"<br> Case 6: NumToString = NumToString & " sixteen"<br> Case 7: NumToString = NumToString & " seventeen"<br> Case 8: NumToString = NumToString & " eighteen"<br> Case 9: NumToString = NumToString & " nineteen"<br> End Select<br> bNoFirstDigit = True<br> Case 2: NumToString = NumToString & " twenty"<br> Case 3: NumToString = NumToString & " thirty"<br> Case 4: NumToString = NumToString & " forty"<br> Case 5: NumToString = NumToString & " fifty"<br> Case 6: NumToString = NumToString & " sixty"<br> Case 7: NumToString = NumToString & " seventy"<br> Case 8: NumToString = NumToString & " eighty"<br> Case 9: NumToString = NumToString & " ninety"<br> End Select<br> If Not bNoFirstDigit Then<br> If nNumber Mod 10 <> 0 Then<br> NumToString = NumToString & "-" & _<br> Mid(NumToString(nNumber Mod 10), 2)<br> End If<br> End If<br> Else<br> Dim nTemp As Currency<br> nTemp = 10 ^ 12 'trillion<br> Do While nTemp >= 1<br> If nNumber >= nTemp Then<br> NumToString = NumToString & _<br> NumToString(Int(nNumber / nTemp))<br> Select Case Int(Log(nTemp) / Log(10) + 0.5)<br> Case 12: NumToString = NumToString & " trillion"<br> Case 9: NumToString = NumToString & " billion"<br> Case 6: NumToString = NumToString & " million"<br> Case 3: NumToString = NumToString & " thousand"<br> End Select</p> <p> nNumber = nNumber - (Int(nNumber / nTemp) * nTemp)<br> End If<br> nTemp = nTemp / 1000<br> Loop<br> End If</p> <p>If bNegative Then<br> NumToString = " negative" & NumToString<br> End If</p> <p>End Function</p> <p>Public Function DollarToString(ByVal nAmount As Currency) As _<br> String</p> <p> Dim nDollar As Currency<br> Dim nCent As Currency</p> <p> nDollar = Int(nAmount)<br> nCent = (Abs(nAmount) * 100) Mod 100</p> <p> DollarToString = NumToString(nDollar) & " dollar"</p> <p> If Abs(nDollar) <> 1 Then<br> DollarToString = DollarToString & "s"<br> End If</p> <p> DollarToString = DollarToString & " and" & _<br> NumToString(nCent) & " cent"</p> <p> If Abs(nCent) <> 1 Then<br> DollarToString = DollarToString & "s"<br> End If</p> <p>End Function</p> <p></code> </p> <p> <small> <a href="http://reportingservices.blog.co.uk/2005/07/15/convert_numbers_or_currency_to_english_w/#comments">Comments</a> </small> </p>http://reportingservices.blog.co.uk/2005/07/15/convert_numbers_or_currency_to_english_w/#commentshttp://reportingservices.blog.co.uk/2005/07/15/rs_code_sample/tag:reportingservices.blog.co.uk,2005-07-15:/2005/07/15/rs_code_sample/Fri, 15 Jul 2005 16:39:47 +0200 <p>While SQL Server Reporting Services supports a variety of built-in aggregates, one of the things that people ask for is the ability to define custom aggregates. While this is not directly supported in RDL, there is a trick you can do using the block within the report. </p> <p>For example, let's say your report contained a query that returned a non-unique set of OrderIDs with associated freight amounts. You need to do a sum of freight values for distinct values of OrderID. In the Code block (available from the Report Properties dialog), you would add:</p> <p>Dim orderIDs As System.Collections.Hashtable<br> Dim total As Double<br> Function MyFunc(ByVal orderID As Object, ByVal freight As Obect) As Double<br> If (orderIDs Is Nothing) Then<br> orderIDs = New System.Collections.Hashtable<br> End If<br> If (orderID Is Nothing) Then<br> MyFunc = total<br> Else<br> If (Not orderIDs.Contains(orderID)) Then<br> total = total + freight<br> orderIDs.Add(orderID, freight)<br> End If<br> MyFunc = total<br> End If<br> End Function</p> <p>In your report, you add a hidden textbox with the value expression to compute the value:<br> =Sum(Code.MyFunc(Fields!OrderID.Value, Fields!Freight.Value))</p> <p>In the footer of the table, you add a textbox with the value expression:<br> =Code.MyFunc(Nothing, Fields!Freight.Value)<br> to return the total value.</p> <p>Caution: This is a workaround that isn't guaranteed to work in releases after SQL Server 2005. I'm providing it in case there is no way you can get around it and you are willing to retest your reports with future versions of Reporting Services. </p> <p> <small> <a href="http://reportingservices.blog.co.uk/2005/07/15/rs_code_sample/#comments">Comments</a> </small> </p>http://reportingservices.blog.co.uk/2005/07/15/rs_code_sample/#commentshttp://reportingservices.blog.co.uk/2005/07/15/print_reports_from_url/tag:reportingservices.blog.co.uk,2005-07-15:/2005/07/15/print_reports_from_url/Fri, 15 Jul 2005 16:20:20 +0200 <p>there is a way to print directly from a URL (apart from having to select the printer!)</p> <p>The command is "&rs:Command=Get&rc:GetImage=8.00.1038.00RSClientPrint.html"</p> <p>Example:</p> <p><a href="http://rsServer1/reportserver?/Commercial">http://rsServer1/reportserver?/Commercial</a> Analytics/Units and Net Sales&rc:toolbar=False&rc:para­meters=false&CategoryValueID=5­5&ProductID:isnull=true&BeginF­iscalPeriodID=1169&EndFiscalPe­riodID=1180&rs:Command=Get&rc:­GetImage=8.00.1038.00RSClientP­rint.html </p> <p> <small> <a href="http://reportingservices.blog.co.uk/2005/07/15/print_reports_from_url/#comments">Comments</a> </small> </p>http://reportingservices.blog.co.uk/2005/07/15/print_reports_from_url/#comments