To calculate the Group total in SSRS

We had a situation to generate a report which is grouped by our job number and to show the jobstatus the number of days the job was in each status.

To do this I had grouped by jobnumber, so the we do not was to repeate the job value each time, it was displayed in the group row. Now to calculate the group total, we do not have a option in SSRS, so we had to create a separate Dataset for the total and show the total.

To calculate No of days in a report

We had a situation to generate a report, where we need to calculate the number of days the order was in each and every department. We are saving this information by jobstatus and job date. To get the number of days the job was in hold in each department we had find the datediff between the first vs second date.

To get the report generate in SSRS we use the funtion under the code area. The code area in SSRS is in Report - Properties --- code

We had used the below code to calculate the No of days, but note this includes the saturday and sunday.
--************************************************************************************
dim gd_Date1 as datetime = "1900-01-01"dim ln_DtDif as integerdim lotno1 as string
public function No_of_days (transdate as datetime,date_received as datetime,lotno as string) as integer
if lotno1 <> lotno Then
ln_DtDif = datediff ("d",date_received,transdate)
else ln_DtDif = datediff ("d",gd_Date1,transdate )end if
gd_Date1 = transdatelotno1 = lotnoreturn ln_DtDif
end function

Change File Name in SSRS

SSRS Report File Name.
There are two options for subscription on the SSRS one for email and one for file share, on the file share you can customize the name of the file, but the physical excel file need to be save on the system, this file will not be automatically emailed to the user. To do this you need to create SSIS job to email the file.

On the other hand the email subscription option does not provide the option to customize the name of the file for eg excel file.

Conclusion
We need to split the report into number of subscriptions needed or use the file share option and create SSIS job to email them.

Reduce MSDB File

To reduce the file size of msdb, one of checks is to check the sysjobhistory in msdb database. This will grow with large number of records. This can be purged using the below syntax

sp_purge_jobhistory { [ @job_name = ] 'job_name' [ @job_id = ] job_id } [ , [ @oldest_date = ] oldest_date ]

USE msdb ;
GO
EXEC dbo.sp_purge_jobhistory
@job_id = N'93B30316-FF0E-42C7-80C7-7CBAA98817DB'
GO

or

USE msdb ;
GO
EXEC dbo.sp_purge_jobhistory
@oldest_date = N '2010-07-01'
GO

SELECT COUNT(*) FROM dbo.sysjobhistory
USE msdb
EXEC sp_delete_backuphistory '01/20/98'

USE msdb
select count(*) from backupset with (nolock) where backup_start_date < '2011-01-01'
USE msdb
SELECT [Size in MB] = SUM(IDX.reserved)/128, [Object Name] = OBJ.name FROM msdb.dbo.sysindexes IDX JOIN msdb.dbo.sysobjects OBJ ON OBJ.id = IDX.id WHERE IDX.indid IN (0, 1, 255) GROUP BY IDX.id, OBJ.name ORDER BY 1 DESC

Temporary table dropping

IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N'tempdb..#temp1')
)
BEGIN
drop table #temp1
END

SQL Reports Parameters

Report Parameters

Next week From date :

=CDate(FormatDateTime(DateAdd("ww",1,DateAdd("d",-(DatePart("w",NOW)-2),NOW)),2))

Next week To date:

=CDate(FormatDateTime(DateAdd("ww",1, DateAdd("d",4, DateAdd("d",-(DatePart("w",NOW)-2),NOW) )) ,2))

Current week From date:

=CDate(FormatDateTime(DateAdd("d",-(DatePart("w",NOW)-2),NOW),2))

Current week to date:

=CDate(FormatDateTime(DateAdd("d",4,DateAdd("d",-(DatePart("w",NOW)-2),NOW)),2))

access to linked server

Linked server syntax
insert into [xxx.xxx.x.xx].bmsus.dbo.bimsus_AccountsReceivable_Outbound_Data
select * from bimsus_AccountsReceivable_Outbound_Data