A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


SQL Jobs for SSRS Subscriptions are made with a GUID as the name . Find the actual report.

The GUID within the Job is a nightmare, use the following query to find the guid for a SQL Server Reporting Server Report Subscription .
 
 
 

SELECT a.ScheduleID

, c.name

FROM ReportServer.dbo.ReportSchedule a

join ReportServer.dbo.Catalog c on c.ItemID = a.ReportID

where c.name = 'Report Name'

 

 
 
 
Of course there's always the option to open up these jobs copy the SQL and create your own scheduled task for running the Subscription. 
 
 
 

Format SSAS Dates from '1900-01-01 00:00:00.000' to '01/01/1900'

 
 
Steps:
1. Add a Varchar Date to your Data Source View
go into your data source view
right click on your date tables header
create a new named calculation
 
 
 
make a new varchar date
 
 
 
 
 
2. In your date Dimenision, Add your new field as the NameColumn on your datetime field.
Find the date you want to change


View it's properties
 Change the namecolumn field , add your new field.
 
 
 
 
 

Create UPC Check Digit Function

 

 
 
The function returns a UPC with the check digit when provided with the prefix numbers.
 
 

 

create function f_get_upc_check_digit (@upc_prefix varchar(32))

returns varchar(32) -- returns the whole upc.

as

 

begin

       -- http://www.gs1.org/barcodes/support/check_digit_calculator

 

       declare @base_index int

       set @base_index = len(@upc_prefix)

 

       declare @index int, @multiplier_start int, @sum int

       set @index = 1

       set @sum = 0

       set @multiplier_start = case when @base_index = 12 then 1 else 3 end

 

       while @index <= @base_index

       begin

                     set @sum = @sum + cast(substring(@upc_prefix, @index, 1) as int) * @multiplier_start

                     set @index = @index + 1

                     set @multiplier_start = case when @multiplier_start = 1 then 3 else 1 end

 

       end

 

       declare @calc_mod int, @check_digit int

       set @calc_mod = @sum%10

       set @check_digit = case when @calc_mod = 0 then 0 else -1 end

 

       while @calc_mod <> 0

       begin

              set @check_digit = @check_digit + 1     

              set @calc_mod = @sum%10

              set @sum = @sum + 1

       end

 

       return @upc_prefix + cast(@check_digit as varchar(64))

 

end

 

go

 

 
 

Test: Actual GTIN-12 UPCs

--886792630591

print dbo.f_get_upc_check_digit ('88679263059')

--886792630522

print dbo.f_get_upc_check_digit ('88679263052')

--886792630553

print dbo.f_get_upc_check_digit ('88679263055')

--886792630584

print dbo.f_get_upc_check_digit ('88679263058')

--886792630515

print dbo.f_get_upc_check_digit ('88679263051')

--886792630546

print dbo.f_get_upc_check_digit ('88679263054')

--886792630577

print dbo.f_get_upc_check_digit ('88679263057')

--886792635268

print dbo.f_get_upc_check_digit ('88679263526')

--886792630539

print dbo.f_get_upc_check_digit ('88679263053')

--886792630560

print dbo.f_get_upc_check_digit ('88679263056')
 
 
Result : 
 
 
 
 
 
Manual Check Digit Check.