SSRS multi-select (easy way!)

 

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 ball
union 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 null 
  begin
    CREATE TABLE [dbo].[admin_ssrs_log] ([ssrsID] [int] IDENTITY(1, 1)
                                                        NOT NULL,
                                         [PageName] [varchar](255) NULL,
                                         [Username] [nvarchar](520) NOT NULL,
                                         [runDate] [datetime] NOT NULL
                                                              CONSTRAINT [DF_ssrs_log_runDate] DEFAULT (getdate()),
                                         [string] [varchar](max) NULL
                                                                 CONSTRAINT [DF_ssrs_log_string] DEFAULT ('none'),
                                         [Stored Procedure] [nvarchar](255) NULL) 
  end
insert  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)

writing sequential numbers with a zero prefix

 

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=0 
while @number<10000
  begin
    set @number=coalesce(@number, 0)
    -----------------------------
    if len(convert(varchar(20), @number))>0
      and @number<>0 
      begin
    -- ones place
        if len(convert(varchar(20), @number))=1
        begin 
          set @display_number='0000'+convert(varchar(20), @number)
        print @display_number
        end
    -- tens place
        if len(convert(varchar(20), @number))=2 
        begin
          set @display_number='000'+convert(varchar(20), @number)
        print @display_number
        end
    -- hundreds place
        if len(convert(varchar(20), @number))=3 
        begin
          set @display_number='00'+convert(varchar(20), @number)
        print @display_number
        end
    -- thousands place
        if len(convert(varchar(20), @number))=4 
        begin
          set @display_number='0'+convert(varchar(20), @number)
        print @display_number
        end
      end
    set @number=@number+1  
  end

adding windows permissions to Navision

 

my file location: ..\Program Files\Microsoft Dynamics NAV\CSIDE Client

 

 

1.  copy the file xp_ndo.dll save it to a fixed location on the server’s location.

note: if this is a local machine: I typically save it locally to a C:\Data folder so I know the info should NOT be deleted!

 

2.  Open SQL Server Managmenet Studio 3.  (be careful!) tier down to the master database 4.  Programmability –> Extended Stored Procedures 5.  Right-Click “New extended stored procedure name it xp_ndo_enumusersids DLL field select the full file path for the xp_ndo.dll

my location is c:\Data

6.  Assign permissions to the public role: IN the extended Stored Procedures properties, click on “permissions” then “search” type in “public” then OK.  Then assign execute permissions to Public 7.  repeat the previous steps and make another extended stored procedure which is exactly identitcal except this one must be called xp_ndo_enumersgroups in stead of xp_ndo_enumusersids

 

special thanks go to Lars Lohndorf-Larsen – Microsoft Dynamics UK

 

But wait! There’s more!

 

You may just get this error as well.

 

 

You have to put a trace flag on your database.  This enables certain properties in SQL Server.

In SQL Server 2005, a user trying to use a program (..an application role) cannot access metadata (meaningful info you need) outside its own database because application roles are not associated with a server-level principal.

This is a change from earlier versions of SQL Server.
Setting this global flag allows for application roles to access server-level metadata

 

1.  Open SQL Server Manager

2.  Right Click SQL Server –> Properties –> Advanced tag.

3.  Scroll down until you see “Startup Parameters”.. type in –T4616.

4.  Click OK. 

5.  Restart SQL Server

(this site helped: http://plataan.typepad.com/microsoftdynamics/2007/11/how-to-set-trac.html)

ERROR: “Cannot insert the value NULL into column 'permission path', table '@temp'; column does not allow nulls. INSERT fails.”

 

I was scripting a senddb code

and got this error:

“Cannot insert the value NULL into column 'permission path', table '@temp'; column does not allow nulls. INSERT fails.”

 

Descriptive huh!?

This has to do with a profile name not being entered.

 

 

View what the name of the profile is.  mine is “testForMailJob”

 

and I updated my code to include the profile name:

decimal calculation Phakery

I have written a calculation inside a column called written amt.
I multiplied the Amount * Net Weight which is the Net Weight times Amount.
But when I verified the amounts by a unit test (SQL Mag’s InstantDoc ID #37428 or Wikkipedia)
certain values failed.
Notice the Net Weight column for the failed lines:

Object1: failed rows


All the failed rows had net weights of decimals
Cross reference with the amounts.

Then I knew the column specification for my table was smaller than the source table.
source table: decimal (38,20)
destination table (from the select): (38,2)
so, what I see are the truncated amounts shoved by the wayside to give me rounded numbers.
voila.
[insert large exclamation of “DUH” here]