SSRS – Hide Rows in a Group based on a Value

SSRS – Hide Rows in a Group based on a Value

If you want to hide rows in a group based on a value in Reporting Services, it is pretty easy to accomplish.  You can do this using an expression, but you will need to add in another expression to get the toggle icon to display correctly.

1. Click on the Tablix.
2. Right-Click on the Row Group and select Visibility…
3. Select Show or hide based on an expression.
4. Select Display can be toggled by this report item: [Select Column Name]
5. Click the fx button.
6. Use an expression such as:

   1	=IIF(Fields!YourField.Value like "*YourValue*",true,false)

7. Click Ok (twice)

If you run the report now, you will notice that the rows in the group you specified will be hidden, but the toggle +- icons will be incorrect.

To fix this:

1. Click on the textbox that contains the drill down.
2. Select the InitialToggleState | Expression.
3. Use an expression such as:

   1	=IIF(Fields!YourField.Value like "*YourValue*",false,true)

   *Notice the false/true is opposite of the first expression.

That should be all there is to hide rows in a group based on a value.  If you have any questions or comments, feel free to comment below.

How to Implement/show Currency Symbol in SSRS Report.

 How to show Currency Symbol in SQL Server Reporting (SSRS):
1>Create a table called "Currency" with column [Currency Name],[Rate] and later join Currency table with your primary table in while creating data set in Report.
 2>Right Click Text box property--> Number-->Custom -->Custom format-->Paste below code in expression field.
3>Red color symbol display for  negative value .
4>Currency Symbol:
$ :US Dollar
AUD :Australian Dollar
€ : Euro
£ : Pound
¥ :Yen
Y:Yuan

=IIF(
Fields!Currency.Value="USD","'$' #,0;('$' #,0)"
,IIF(Fields!Currency.Value="AUD","'AUD' #,0;('AUD' #,0)"
,IIF(Fields!Currency.Value="EUR","'€' #,0;('€' #,0)"
,IIF(Fields!Currency.Value="GBP","'£' #,0;('£' #,0)"
,IIF(Fields!Currency.Value="JPY","'¥' #,0;(''¥' #,0)"
,IIF(Fields!Currency.Value="CNY","'Y' #,0;('Y' #,0)"
,"Error"))))))

How to show all category labels on the Column Chart SSRS 2012

How to show all category labels on the Column Chart X-Axis  SSRS 2012:

1. Right-click the category axis and click Axis Properties. The Axis Properties dialog box opens.
2. In Axis Options, set Interval to 1. Every category group label is displayed. If you want to show every other category group label on the x-axis, type 2.
3. Click OK.

How to have a parameter value automatically selected when another parameter value Selected in MS SSRS.

Step 1: Create cascade parameter in SSRS.
Step 2: One Parameter value is depend on another parameter value.
Step 3: The Depending parameter data set always return One Value.
Step 4:In Parameter Select-->Default Value Tab-->Get value from a Query-->Select Dependent data set--Select Value.

Example:
Dataset1:

select distinct

snapshot_WK

,cast(snapshot_Wkstart as date) as Snapshot_WKstart

,cast(snapshot_Wkend as date) as Snapshot_WKend

from sandbox..[Rawdata_Master](nolock)

order by snapshot_Wk

Dataset2:

select top 1

cast(snapshot_Wkstart as date) as Snapshot_WKstart

,cast(snapshot_Wkend as date) as Snapshot_WKend

from sandbox..[Rawdata_Master](nolock)

where snapshot_WK = @Reporting_Wk

Dataset2 is depend on Dataset1.Both data set are using for Parameter in Report. 

Result:When we select Snapshot_Wk in one parameter,other two parameter automatically filled with corresponding Wk_Start data and Wk_End Date.

Hexadecimal value, is an invalid character: SSRS 2012

Hi,Today i have encounter below issue while deploying my report on SSRS 2012 Report manager.
Report is working fine in MS Visual studio during development,but when i had deployed the same report on report manager i was getting below Error message:

Error :The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.
There is an error in XML document (1, 12827).
' ', hexadecimal value 0x1A, is an invalid character. Line 1, position 12827.

Issue Root cause:
      "This problem occurs because a non printable character is populated into a report parameter that you define in the report".

Solution:
Step1: Check the ASCII number for non printable character  using below statement.

select ASCII(' ')

Note:Might be it will not display on  SSMS,but when you execute select statement you will get  number. in my case it is 26.
Step2: Just use REPLACE() function in your query to eliminate non printable character.

select distinct REPLACE(title, CHAR(26),',') as title from analytics..<<Table_Name>>

order by title desc

 Tag:(SSRS, xml, non printable character, Ascii, Char, Replace)
  

MSSQL Table BackUp

MSSQL Server:

1-Table back up With table schema and Data:

Method 1:

select  * into [dbo].[USER_backup] 

from [dbo].[USER]

Method 2: 

2-Table back up only Schema:

SELECT TOP 0 *

INTO [dbo].[USER_Schema_backup]

FROM [dbo].[USER]

3-Data base Back Up:


4-Data base Differential Back Up: 

Export Package from "Integration Services Catalog" MS SQL Server 2012

Steps to Export Package From Integration Services Catalog(SQL Server 2012):
1.Connect to server
2.Drill down "Integration Services Catalog" folder.
3.Drill down till projects folder.
4.Right Click on Package-->Export
5.Save file on your local folder with extension ".ispac"
6.Now,change your saved file extension to ".zip"
7.Extract the Zip file you will get ".dtsx" package file.