Find your ReportServer Stored Procedures

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

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]

Weighted Averages in SQL Server

Here is a screen shot of my brain dump regarding weighted averages.

The Financial Dictionary says:

Weighted Average definition

An average in which each quantity to be averaged is assigned a weight. These weightings determine the relative importance of each quantity on the average. Weightings are the equivalent of having that many like items with the same value involved in the average.
Investopedia Commentary
To demonstrate, let's take the value of letter tiles in the popular game Scrabble.
Value: 10 8 5 4 3 2 1 0
Occurrences: 2 2 1 10 8 7 68 2
To average these values, do a weighted average using the number of occurrences of each value as the weight. To calculate a weighted average:
1. Multiply each value by its weight. (Ans: 20, 16, 5, 40, 24, 14, 68, and 0) 2. Add up the products of value times weight to get the total value. (Ans: Sum=187) 3. Add the weight themselves to get the total weight. (Ans: Sum=100)
4. Divide the total value by the total weight. (Ans: 187/100 = 1.87 = average value of a Scrabble tile)

here's a start:

SELECT     ItemToCount, price AS value, COUNT(ItemToCount) AS weight, COUNT(ItemToCount) * price AS value_times_weight FROM         SalesTable GROUP BY ItemToCount, price ORDER BY ItemToCount

research used:
(I played “with rollup” to give me subtotals):
http://msdn.microsoft.com/en-us/library/ms189305(SQL.90).aspx
Webster’s Dictonary lookup on Weighted Average:
http://dictionary.reference.com/browse/weighted+average