I have a task to roll certain numbers up what have an ID with a letter at the end.
something like this:
I need to ‘roll’ values (ie: Sales Amount, Quantities) regardless of the version (A..B..C..)
Here is some sample code that will help create some sample data:
create a fake table called blah:
if object_id('blah') is not null drop table blah
if object_id('blah') is null create table blah (id int primary key identity, mixed_charachters varchar(20))
Now, insert values into that table:
insert into blah (mixed_charachters)
select mixed_charachters
from (select '8B' as mixed_charachters union
select '244' union
select '51B' union
select '172' union
select '247A' union
select '274' union
select '249' union
select '273' union
select '194B' union
select '198' union
select '271' union
select '50A' union
select '276C' union
select '254' union
select '115' union
select '93' union
select '158' union
select '210C' union
select '50D' union
select '102' union
select '265' union
select '250' union
select '51A' union
select '196' union
select '188' union
select '216E' union
select '34' union
select '254B' union
select '276' union
select '65' union
select '78' union
select '178' union
select 'TEXTVALUE union
select '221A' union
select '209' union
select '96A' union
select '73' union
select '190' union
select '262' union
select '258' union
select '278' union
select '194A' union
select '46' union
select '227A') as charachters_to_insert
Now, I can start working with the numeric values that have A LETTER IN IT
select * from blah where isnumeric(mixed_charachters)<>1
Here is a source I found that will show you all the stored procedures that are stored in your SQL Server Reporting Services.
This is a staple for me! It can get pretty crazy, not knowing what stored procedure goes to what report!
;
with xmlnamespaces (
default 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' as rd
)
select name,x.value('CommandType[1]','VARCHAR(50)') as CommandType,x.value('CommandText[1]','VARCHAR(50)') as CommandText,
x.value('DataSourceName[1]','VARCHAR(50)') as DataSource
from (select name,cast(cast(content as varbinary(max)) as xml) as reportXML
from NRDEV1.ReportServer.dbo.Catalog
where content is not null
and TYPE = 2) a
cross apply reportXML.nodes('/Report/DataSets/DataSet/Query') r (x)
where x.value('CommandType[1]','VARCHAR(50)') = 'StoredProcedure'
order by name
Open Visual Studio Business Intellegence Studio
Open an existing project or create a new project.
| Select the Data Tab |  |
| create new DataSet |  |
call this dataset Select:
Here is my select statement.select * from (select 'basket ball' as ballunion select 'baseball'union select 'foot ball'union select 'nerf ball') t where t.ball in(@ball)
Call this ddl_select (this will be the drop-down menu you need for the multi-select)
select 'basket ball'union select 'baseball'union select 'foot ball'union select 'nerf ball'
if you have a debug table you want to test your values (see how they are written, here’s some code:
if object_id('admin_ssrs_log') is nullbeginCREATE TABLE [dbo].[admin_ssrs_log] ([ssrsID] [int] IDENTITY(1, 1)NOT NULL,[PageName] [varchar](255) NULL,[Username] [nvarchar](520) NOT NULL,[runDate] [datetime] NOT NULLCONSTRAINT [DF_ssrs_log_runDate] DEFAULT (getdate()),[string] [varchar](max) NULLCONSTRAINT [DF_ssrs_log_string] DEFAULT ('none'),[Stored Procedure] [nvarchar](255) NULL)endinsert dbo.admin_ssrs_log (PageName,Username,runDate,string,[Stored Procedure])values ('test multi-select parameters', -- PageName - varchar(255)'domain\username', -- Username - nvarchar(520)getdate(), -- runDate - datetime@ball, -- string - varchar(max)'(code behind execution; no stored proc)' -- Stored Procedure - nvarchar(255)
Now, create the multi-select drop down for “ball”
here you can select the options (but you aren’t done yet! the clincher is next):
The multi-select parameter is written but SQL Server needs it parsed in a certain way.
(oh my, this is soooo easy!)
1. Go to your data tabl
2. Edit the dataset that needs the @ball parameter.
3. Select the Parameters tab
The key is to use the Replace function and the Join function
Chagne your value
from =Parameters!ball.Value
to =Replace(Join(Parameters!ball.Value,", ")," ", "")
voila. your multi-select will work every time!
helpful source (kinda obscure, but here it is)
At times you may need a zero as a prefix to sequential numbers
ie:
00001
00002..
00009
00010…
Here is test code that flows through one to ten thousand:
declare @number int,@prefix varchar(20),@display_number varchar(20)-----------------------------set @number=0while @number<10000beginset @number=coalesce(@number, 0)-----------------------------if len(convert(varchar(20), @number))>0and @number<>0begin-- ones placeif len(convert(varchar(20), @number))=1beginset @display_number='0000'+convert(varchar(20), @number)print @display_numberend-- tens placeif len(convert(varchar(20), @number))=2beginset @display_number='000'+convert(varchar(20), @number)print @display_numberend-- hundreds placeif len(convert(varchar(20), @number))=3beginset @display_number='00'+convert(varchar(20), @number)print @display_numberend-- thousands placeif len(convert(varchar(20), @number))=4beginset @display_number='0'+convert(varchar(20), @number)print @display_numberendendset @number=@number+1end
