SSRS wrap/line break row and column within group

This SSRS solution is for making row/column break in grouping.

For the result we will consider following queary as an Example:

SQL Query :-

select '1' [Rank],'Product1' Product

union all

select '2','Product2'

union all

select '3','Product3'

union all

select '4','Product4'

union all

select '5','Product5'

union all

select '6','Product6'

and using above we get the result as shown below

Result :-

Rank       Products

1          Product1
2          Product2
3          Product3
4          Product4
5          Product5
6          Product6

Now in SSRS we need to show the result as shown below and for that we will use report Builder and creating new report.

So we have to create a dataset using above query and create one table as shown below:

Now add Row Group and Column Group as shown

In Row Group

1.  Go to the properties and add group expressions

2.  Add this =(Fields!Rank.Value- 1) Mod 3

In Column Group

1.  Go to properties of Column Group

2.  Add this to expression =Floor((Fields!Rank.Value - 1) / 3)

See the result as shown Below:

SQL - Converting Numbers to Words in SQL Server

Here is the SQL Function to Convert Digit into Word:

CREATE FUNCTION [dbo].[fn_DigitToWord]( @RUPEES AS DECIMAL(30,2)) 

RETURNS VARCHAR(max)

AS

BEGIN

DECLARE @INNTBL_01 TABLE (RECNO INT IDENTITY(1, 1), MTEXT NVARCHAR(50))

DECLARE @INNTBL_02 TABLE (RECNO INT IDENTITY(1, 1), MTEXT NVARCHAR(50))

DECLARE @AMOUNT NUMERIC(38,0)

DECLARE @PAISE AS INT

SET @AMOUNT = FLOOR(@RUPEES)

SET @PAISE =   CAST(CONVERT(DECIMAL(10,0),(@RUPEES % 1) * 100  ) AS INT)

---STEP 2:

INSERT INTO @INNTBL_01 SELECT 'One '

INSERT INTO @INNTBL_01 SELECT 'Two '

INSERT INTO @INNTBL_01 SELECT 'Three '

INSERT INTO @INNTBL_01 SELECT 'Four '

INSERT INTO @INNTBL_01 SELECT 'Five '

INSERT INTO @INNTBL_01 SELECT 'Six '

INSERT INTO @INNTBL_01 SELECT 'Seven '

INSERT INTO @INNTBL_01 SELECT 'Eight '

INSERT INTO @INNTBL_01 SELECT 'Nine '

INSERT INTO @INNTBL_01 SELECT 'Ten '

INSERT INTO @INNTBL_01 SELECT 'Eleven '

INSERT INTO @INNTBL_01 SELECT 'Twelve '

INSERT INTO @INNTBL_01 SELECT 'Thirteen '

INSERT INTO @INNTBL_01 SELECT 'Fourteen '

INSERT INTO @INNTBL_01 SELECT 'Fifteen '

INSERT INTO @INNTBL_01 SELECT 'Sixteen '

INSERT INTO @INNTBL_01 SELECT 'Seventeen '

INSERT INTO @INNTBL_01 SELECT 'Eighteen '

INSERT INTO @INNTBL_01 SELECT 'Nineteen '

INSERT INTO @INNTBL_01 SELECT 'Twenty '

-- SIMILARY, INSERT THE MULTIPLES

INSERT INTO @INNTBL_02 SELECT 'Ten '

INSERT INTO @INNTBL_02 SELECT 'Twenty '

INSERT INTO @INNTBL_02 SELECT 'Thirty '

INSERT INTO @INNTBL_02 SELECT 'Forty '

INSERT INTO @INNTBL_02 SELECT 'Fifty '

INSERT INTO @INNTBL_02 SELECT 'Sixty '

INSERT INTO @INNTBL_02 SELECT 'Seventy '

INSERT INTO @INNTBL_02 SELECT 'Eighty '

INSERT INTO @INNTBL_02 SELECT 'Ninety '

---STEP 3:

DECLARE @WORD VARCHAR(300)

SELECT @WORD = ''

DECLARE @M_AMT01  INT, @M_AMT02 INT

IF @AMOUNT < 1000000000 AND @AMOUNT >= 10000000 BEGIN

SET @M_AMT01 = @AMOUNT

SELECT @AMOUNT = ( @AMOUNT % 10000000 )

SET @M_AMT01 = ( @M_AMT01 - @AMOUNT ) / 10000000

DECLARE @WORD1 VARCHAR(300)

SET @WORD1 = ''

IF @M_AMT01 < 100 AND @M_AMT01 > 20 BEGIN

SET @M_AMT02 = @M_AMT01

SET @M_AMT01 = ( @M_AMT01 % 10)

SET @M_AMT02 = ( @M_AMT02 - @M_AMT01 ) / 10

SET @WORD1 = ( SELECT @WORD1 + MTEXT FROM @INNTBL_02 WHERE RECNO = @M_AMT02 )

END

IF @M_AMT01 <= 20 AND @M_AMT01 <> 0 BEGIN

SET @WORD1 = ( SELECT @WORD1 + MTEXT FROM @INNTBL_01 WHERE RECNO = @M_AMT01 )

END

SET @WORD = @WORD + @WORD1 + 'Crore '

END

IF @AMOUNT < 10000000 AND @AMOUNT >= 100000 BEGIN

SET @M_AMT01 = @AMOUNT

SELECT @AMOUNT = ( @AMOUNT % 100000 )

SET @M_AMT01 = ( @M_AMT01 - @AMOUNT ) / 100000

SET @WORD1 = ''

IF @M_AMT01 < 100 AND @M_AMT01 > 20 BEGIN

SET @M_AMT02 = @M_AMT01

SET @M_AMT01 = ( @M_AMT01 % 10)

SET @M_AMT02 = ( @M_AMT02 - @M_AMT01 ) / 10

SET @WORD1 = ( SELECT @WORD1 + MTEXT FROM @INNTBL_02 WHERE RECNO = @M_AMT02 )

END

IF @M_AMT01 <= 20 AND @M_AMT01 <> 0 BEGIN

SET @WORD1 = ( SELECT @WORD1 + MTEXT FROM @INNTBL_01 WHERE RECNO = @M_AMT01 )

END

SET @WORD = @WORD + @WORD1 + 'Lakh '

END

IF @AMOUNT < 100000 AND @AMOUNT >= 1000 BEGIN

SET @M_AMT01 = @AMOUNT

SET @AMOUNT = ( @AMOUNT % 1000 )

SET @M_AMT01 = ( @M_AMT01 - @AMOUNT ) / 1000

SET @WORD1 = ''

IF @M_AMT01 < 100 AND @M_AMT01 > 20 BEGIN

SET @M_AMT02 = @M_AMT01

SET @M_AMT01 = ( @M_AMT01 % 10 )

SET @M_AMT02 = ( @M_AMT02 - @M_AMT01 ) / 10

SET @WORD1 = ( SELECT @WORD1 + MTEXT + '' FROM @INNTBL_02 WHERE RECNO = @M_AMT02 )

END

IF @M_AMT01 <= 20 AND @M_AMT01 <> 0 BEGIN

SET @WORD1 = ( SELECT @WORD1 + MTEXT +'' FROM @INNTBL_01 WHERE RECNO = @M_AMT01 )

END

SET @WORD = @WORD + @WORD1 + 'Thousand '

END

IF @AMOUNT < 1000 AND @AMOUNT > = 100 BEGIN

SET @M_AMT01 = @AMOUNT

SET @AMOUNT = ( @AMOUNT % 100 )

SET @M_AMT01 = ( @M_AMT01 - @AMOUNT ) / 100

SET @WORD = ( SELECT @WORD + ' ' +MTEXT + 'Hundred ' FROM @INNTBL_01 WHERE RECNO = @M_AMT01)

END

IF @AMOUNT < 100 AND @AMOUNT > 20 BEGIN

SET @M_AMT01 = @AMOUNT

SET @AMOUNT = ( @AMOUNT % 10 )

SET @M_AMT01 = ( @M_AMT01 - @AMOUNT ) / 10

SET @WORD = ( SELECT @WORD + MTEXT + '' FROM @INNTBL_02 WHERE RECNO = @M_AMT01 )

END

IF @AMOUNT <= 20 AND @AMOUNT >= 1 BEGIN

SET @WORD = ( SELECT @WORD + MTEXT +'' FROM @INNTBL_01 WHERE RECNO = @AMOUNT )

END

SET @WORD = @WORD + 'Rupees Only'

-- STEP 4:

--DECLARE @WORDP VARCHAR(300)

--SET @WORDP = ''

--IF @PAISE <> 0 BEGIN

--IF @PAISE < 100 AND @PAISE > 20 BEGIN

--DECLARE @PAISE_01 VARCHAR(300)

--SET @PAISE_01 = @PAISE

--SET @PAISE = ( @PAISE % 10 )

--SET @PAISE_01 = ( @PAISE_01 - @PAISE ) / 10

--SET @WORDP = ( SELECT @WORDP + MTEXT FROM @INNTBL_02 WHERE RECNO = @PAISE_01 )

--END

--IF @PAISE <= 20 AND @PAISE >= 1 BEGIN

--SET @WORDP = ( SELECT @WORDP + MTEXT FROM @INNTBL_01 WHERE RECNO = @PAISE )

--END

--SET @WORD = @WORD + 'and ' + @WORDP + 'paise'

--END

--IF @AMOUNT>=1000000000 BEGIN

--SET @WORD = ''

--END

RETURN @WORD

END


Result:

select [dbo].[fn_DigitToWord](3250)

Output: "Three Thousand  Two Hundred Fifty Rupees Only"

SSRS Download all reports from report server using SQL

Before download of report you have to make some config changes given below

-- Allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1
GO

-- Update the currently configured value for advanced options.
RECONFIGURE
GO

-- Enable xp_cmdshell

EXEC sp_configure 'xp_cmdshell', 1
GO

-- Update the currently configured value for xp_cmdshell
RECONFIGURE
GO

-- Disallow further advanced options to be changed.

EXEC sp_configure 'show advanced options', 0
GO
-- Update the currently configured value for advanced options.

RECONFIGURE

GO

The following code is for download the reports from report server


DECLARE @FilterReportPath AS VARCHAR(500) = NULL 

DECLARE @FilterReportName AS VARCHAR(500) = NULL

--reports to be downloaded..
DECLARE @OutPath AS VARCHAR(500) = 'C:\\Reports\\Download\'
--Make sure this folder exist in drive
--Used to prepare the dynamic query

DECLARE @TSQL AS NVARCHAR(MAX)

--Simple validation of OutputPath; this can be changed as per ones need.


IF LTRIM(RTRIM(ISNULL(@OutPath,''))) = ''

BEGIN

  SELECT 'Invalid Output Path'

END

ELSE
print @OutPath

BEGIN

   --select * from Catalog
   SET @TSQL = STUFF((SELECT

                      ';EXEC master..xp_cmdshell ''bcp " ' +
                      ' SELECT ' +
                      ' CONVERT(VARCHAR(MAX), ' +
                      '       CASE ' +
                      '         WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''''''') '+
                      '         ELSE C.Content '+
                      '       END) ' +
                      ' FROM ' +
                      ' [ReportServer].[dbo].[Catalog] CL ' +
                      ' CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C ' +
                      ' WHERE ' +
                      ' CL.ItemID = ''''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''' " queryout "' + @OutPath + '' + CL.Name + '.rdl" ' + '-T -c -x'''
                    FROM
                      [ReportServer].[dbo].[Catalog] CL
                    WHERE
                      CL.[Type] = 2 --Report
                      AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%' + @FilterReportPath + '%/%', '/' + CL.[Path] + '/')
                      AND CL.Name LIKE COALESCE('%' + @FilterReportName + '%', CL.Name)
                    FOR XML PATH('')), 1,1,'')

  --Execute the Dynamic Query
  print @TSQL

  EXEC SP_EXECUTESQL @TSQL

END