SSRS - Show SSRS Reports Properly in Chrome

After lot's of search i found  chrome doesn't supports IE's styles.

Here is the steps to find the issue.

Open a SSRS Report in Chrome, press F12 to open the Chrome DevTools window, press Ctrl-Fto search for the <div> tag with and id containing the words ReportArea. To confirm you had found it you should be able to see the <div> element with id containingVisibleReportContent above it.

Click on <div> with the id that looks like this “ctl99_ctl99” then look for the overflow style which is currently set to auto as illustrated below.

Now at right hand side you can see the style property like overflow:auto;

So, That is the reason while we are getting the  blank report every time in chrome.

Solution:

To make the change permanent you have to then go to your SSRS Server then on the path where SSRS is installed you have to do some modifications on the js file called ReportingServices.js. 

By default in SQL Server 2012 it will be in C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportManager\js

Go to the bottom part then copy and paste the following js code.

function pageLoad() {
    var element = document.getElementById("ctl32_ctl09");
    if (element) {
        element.style.overflow = "visible";
    }
}

Then you have to refresh your http://localhost/report server and check the changes.

:D

SSRS - Alternate Change Column Background Color of Matrix/Table

Query:

select 1 Month,'JAN' Month_Name 
union all
select 2 ,'FEB'
union all
select 3 ,'MAR'
union all
select 4 ,'APR'
union all
select 5 ,'JUN'
union all
select 6 ,'JUL'
union all
select 7 ,'AUG'

Create a dataset using this query.
add your local datasource for this dataset.

Create Matrix as shown below and select column and follow the steps.

Step 1:

Step 2:

Step 3:

Output:

Execute SSRS Report Subscriptions Manually using SQL Query

-->Please change DateTime of LastRunTime field from below Query
-->select LastRunTime from [dbo].[Subscriptions] order by LastRunTime


-->in below Query update LastRunTime 
-->LastRunTime between '2014-11-09 07:00:04.607' and '2014-11-09 09:35:08.460'

select * into #TempR from
(select ROW_NUMBER()Over(order by j.name)ID,  cast(j.name as varchar(40)) Step 
from msdb.dbo.sysjobs j  
join  msdb.dbo.sysjobsteps js on js.job_id = j.job_id 
join ReportSchedule A on js.command like '%' + cast(A.subscriptionid as varchar(40)) + '%'


-->This join return the report id from LastRunTime date
left join
(select ItemID from [dbo].[Catalog]
where ItemID in (
select Report_OID from [dbo].[Subscriptions]
where LastRunTime between '2014-11-09 07:00:04.607' and '2014-11-09 09:35:08.460'
))P
on A.ReportID=P.ItemID
where ItemID is not null)P

declare @min int,@max int
set @min=(select Min(ID) from #TempR)
set @max=(select Max(ID) from #TempR)

select * from #TempR

--drop table #TempR

while(@min<=@max)
begin

declare @var nvarchar(max)
set @var=(select Step from #TempR where ID=@min)
execute msdb.dbo.sp_start_job @var
print @var

set @min=@min+1

end

SSRS - Cascade Parameter using Dataset

--Add Dataset to SSRS Report Builder
--1.DataSet (Warehouse)
select * into #TempR from
(select '1' Warehouse
union all
select '2'
union all
select 'AB')P

select * from #TempR

drop table #TempR

--2.DataSet (Item and Price)
select * into #TempR from
(select '1' Warehouse, 'Widget'Item, '$1' Price
union all
select '1', 'Pens', '$2'
union all
select '1', 'Cards', '$5'
union all
select '2', 'Popcorn', '$2'
union all
select '2', 'Boxes', '$10'
union all
select 'AB', 'Ruler', '$10'
union all
select 'AB', 'Stapler', '$12')P

select * from #TempR
where Warehouse=@WareHouse


--Add WareHouse Parameter in Report

1.Create Parameter Name with 'WareHouse'.
2.Go to 'Available Value' in Parameter Property.
3.Go to Get Value from Query.
4.Select Dataset and Value

--Select svailable value from Dataset

--Add ItemandPrice Parameter in Report and select available value

Follow Same Steps.

--Take Textbox 
1.Right Click on Textbox
2.Goto Expression
3.Paste =Cstr(Parameters!Item.Value)+" Price :"+First(Fields!Price.Value, "ItemANDPrice")

Output:
Select Parameter

SSRS - Execute SSRS Failed Job using SQL Server

select * into #TempR from
(select ROW_NUMBER()Over(order by j.name)ID,  cast(j.name as varchar(40)) Step 
from msdb.dbo.sysjobs j  
join  msdb.dbo.sysjobsteps js on js.job_id = j.job_id 
join  [ReportServer].[dbo].[Subscriptions] s  on js.command like '%' + cast(s.subscriptionid as varchar(40)) + '%' 
where s.LastStatus like 'Failure sending mail%')P

declare @min int,@max int
set @min=(select Min(ID) from #TempR)
set @max=(select Max(ID) from #TempR)

--View Jobs List
select * from #TempR

--Execute All Jobs
while(@min<=@max)
begin

declare @var nvarchar(max)
set @var=(select Step from #TempR where ID=@min)
execute msdb.dbo.sp_start_job @var
print @var

set @min=@min+1
end

drop table #TempR

SSRS - Repeat Header using Tablix/Table Properties

Using this steps we can repeat the column header on scrolling in SSRS Reports.
for getting the result we have to follow the steps shown below.
1.Right Click on Tablix.

2.goto Tablix Propeties.

Result:

SQL - Get First Value of Every Category | Using While Loop

create table #temp
(id int,
monthDate date,
value int)

insert into #temp values(1,'1/1/2014',10)
insert into #temp values(1,'1/2/2014',15)
insert into #temp values(1,'1/3/2014',20)
insert into #temp values(2,'1/4/2014',25)
insert into #temp values(2,'1/5/2014',19)

declare @min int,@max int
select @min=MIN(ID) from #temp
select @max=MAX(ID) from #temp

select * from #temp

select top 0 * into  #res 
from #temp

while(@min<=@max)
begin

declare @minDT date
set @minDT=(select MIN(MonthDate) from #temp where id=@min)

insert into #res
select *
from #temp
where ID=@min
and Convert(Date,monthDate,103)=Convert(Date,@minDT,103)

set @min=@min+1
end

select * from #res

drop table #res
drop table #temp

Result:

SSRS - Repeat Tablix Header on Page Scroll

1. Create a SSRS report 

2.Follow the Step 

3. Change Property of the Selected "Static"

Now Check the result. 

SSRS - Show Chart Label on Condition

1.select Data Label of Chart Go to Visible Property.

=IIF(Cstr(Format(Fields!Date.Value,"dd-MMM"))="01-Oct" or Cstr(Format(Fields!Date.Value,"dd-MMM"))="06-Oct",True,False)

SSRS - Add Strikethrough to Textbox Value

Add Stikethrough style to textbox in SSRS Report.

SQL - Create Unique ID using SQL Server

DECLARE @STR VARCHAR(50)

---This is with seconds

SET @STR='ID'+(SELECT REPLACE(CONVERT(VARCHAR,GETDATE(),5),'-','')+REPLACE(CONVERT(VARCHAR,GETDATE(),108),':',''))

---This is with milliseconds
PRINT @STR

SET @STR='ID'+(SELECT REPLACE(CONVERT(VARCHAR,GETDATE(),5),'-','')

+(REPLACE(CONVERT(VARCHAR,GETDATE(),114),':','')))

PRINT @STR

Output:

1)ID131014102125

2)ID131014102145910

Formatted Invoice No.

Select 'EI-U5/'+Cast(right(Year(DateAdd(YY,-1,getdate())),2) as varchar)+'-'+Cast(right(Year(getdate()),2) as varchar)+'/'+REPLACE(CONVERT(VARCHAR,GETDATE(),114),':','')

Output:

EI-U5/14-15/113021013

SSRS - List Demo using SSRS

You can get RDL file from this link 

https://drive.google.com/file/d/0B5rqPoeNMlf-ZzVJQzFGcmNQc28/edit?usp=sharing

Result:

SSRS - Multiple value (Parameter) in textbox

=IIF(Parameters!type.Count=6,"ALL",(JOIN(Parameters!type.Value,",")))

--Here 6 is total no of values in parameter

Result:

SQL - Dynamic Pivot in SQL Server

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)

DECLARE @ColumnName AS NVARCHAR(MAX)

 

--Get distinct values of the PIVOT Column

SELECT @ColumnName= ISNULL(@ColumnName + ',','')

       + QUOTENAME(Course)

FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses

 

--Prepare the PIVOT query using the dynamic

SET @DynamicPivotQuery =

  N'SELECT Year, ' + @ColumnName + '

    FROM #CourseSales

    PIVOT(SUM(Earning)

          FOR Course IN (' + @ColumnName + ')) AS PVTTable'

--Execute the Dynamic Pivot Query

EXEC sp_executesql @DynamicPivotQuery




--OR




EXEC(@DynamicPivotQuery)

Result: