Can this Stored Proc be more efficient?

Hey all,
Figured I'd get everyone's input on this. The stored proc below works
fine, no errors, however the ASP.NET page which calls it takes forever
to load (it averages 25 seconds per search). Anyone have any insight on
how I can boost the speed? 25-35 seconds is too dang long. For those
who want a full perspective: I have a sortable datagrid with custom
paging. On the site there is a textbox where one can search by
"containernum." As you can see below, the core of the search uses LIKE
'%'+@.con_num+'%' which is where the slowdown seems to occur. I have a
full-text index on the containernum field but perhaps I did it wrong
(it's the first time I've used that feature) because there appears to
be no gain in speed. Help! :-)
BTW, there are about a million records in my test table, the real table
has almost 5 million records, so I can just imagine the slowdown on
that one. :-(
CREATE PROCEDURE [Get_Data]
@.CurrentPage int,
@.PageSize int,
@.SortField nvarchar(50),
@.TotalRecords int output,
@.con_num nvarchar(8)
AS
SET NOCOUNT ON
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
uid uniqueidentifier NOT NULL,
event nvarchar(6) NOT NULL,
bookingnum nvarchar(50) NOT NULL,
vanowner nvarchar(6) NOT NULL,
containernum nvarchar(8) NULL,
tcn nvarchar(20) NULL,
poe nvarchar(50) NULL,
pod nvarchar(6) NULL,
shipname nvarchar(50) NULL,
vdn nvarchar(8) NULL,
eventlocation nvarchar(50) NOT NULL,
pcfn nvarchar(8) NULL
)
INSERT INTO #TempTable
(
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
)
SELECT
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
FROM
dbo.new315_itv
WHERE
containernum LIKE '%'+@.con_num+'%'
ORDER BY
CASE
WHEN @.SortField = 'event' THEN event
WHEN @.SortField = 'bookingnum' THEN bookingnum
WHEN @.SortField = 'containernum' THEN containernum
WHEN @.SortField = 'tcn' THEN tcn
WHEN @.SortField = 'poe' THEN poe
WHEN @.SortField = 'pod' THEN pod
WHEN @.SortField = 'shipname' THEN shipname
WHEN @.SortField = 'vdn' THEN vdn
WHEN @.SortField = 'eventlocation' THEN eventlocation
WHEN @.SortField = 'pcfn' THEN pcfn
END
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
SELECT
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
FROM
#TempTable
WHERE
ID > @.FirstRec AND ID < @.LastRec
SELECT @.TotalRecords = COUNT(*) FROM #TempTable
GOANytime you use Like with a % at the beginning of the value, as in
containernum LIKE '%'+@.con_num+'%'
you automatically induce a full table scan. this is why your query is so
slow.
You need to extract the @.con_num portion of the data into a separate column
and index that... change the query so that it uses = instead of like, or at
least so that there is no % at the beginning...
Also the idea you are using of creating a temp table of all the values, and
then extracting only one pages wrth to return over the wire is a good one,
but you cancarry this a step furthur... Instead of using a temp table, use a
table variable, with an identity Primary Key RowNum, and ONLY put the keys
into this table variable... (You are incurring an enormous amount of
overhead right now stuffing ALL the data into the temp table, not just the
data you will eventually return to client)
Declare @.T Table (RowNum Integer Primary Key Identity Not Null,
PK Integer Not Null)
Insert @.T
Select uid
From ....
Then, at the end just use this table variable t o join back to your main
table, based on which StartRownNum and EndRowwNum defines the page you want
SELECT uid,event,bookingnum,
vanowner,containernum,tcn,
poe,pod,shipname,
vdn,eventlocation,pcfn
FROM new315_itv O Join @.T
On T.RowNum = O.uid
WHERE RowNum Between @.FirstRec AND @.LastRec
"roy.anderson@.gmail.com" wrote:

> Hey all,
> Figured I'd get everyone's input on this. The stored proc below works
> fine, no errors, however the ASP.NET page which calls it takes forever
> to load (it averages 25 seconds per search). Anyone have any insight on
> how I can boost the speed? 25-35 seconds is too dang long. For those
> who want a full perspective: I have a sortable datagrid with custom
> paging. On the site there is a textbox where one can search by
> "containernum." As you can see below, the core of the search uses LIKE
> '%'+@.con_num+'%' which is where the slowdown seems to occur. I have a
> full-text index on the containernum field but perhaps I did it wrong
> (it's the first time I've used that feature) because there appears to
> be no gain in speed. Help! :-)
> BTW, there are about a million records in my test table, the real table
> has almost 5 million records, so I can just imagine the slowdown on
> that one. :-(
>
> CREATE PROCEDURE [Get_Data]
> @.CurrentPage int,
> @.PageSize int,
> @.SortField nvarchar(50),
> @.TotalRecords int output,
> @.con_num nvarchar(8)
> AS
> SET NOCOUNT ON
> CREATE TABLE #TempTable
> (
> ID int IDENTITY PRIMARY KEY,
> uid uniqueidentifier NOT NULL,
> event nvarchar(6) NOT NULL,
> bookingnum nvarchar(50) NOT NULL,
> vanowner nvarchar(6) NOT NULL,
> containernum nvarchar(8) NULL,
> tcn nvarchar(20) NULL,
> poe nvarchar(50) NULL,
> pod nvarchar(6) NULL,
> shipname nvarchar(50) NULL,
> vdn nvarchar(8) NULL,
> eventlocation nvarchar(50) NOT NULL,
> pcfn nvarchar(8) NULL
> )
> INSERT INTO #TempTable
> (
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> )
> SELECT
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> FROM
> dbo.new315_itv
> WHERE
> containernum LIKE '%'+@.con_num+'%'
> ORDER BY
> CASE
> WHEN @.SortField = 'event' THEN event
> WHEN @.SortField = 'bookingnum' THEN bookingnum
> WHEN @.SortField = 'containernum' THEN containernum
> WHEN @.SortField = 'tcn' THEN tcn
> WHEN @.SortField = 'poe' THEN poe
> WHEN @.SortField = 'pod' THEN pod
> WHEN @.SortField = 'shipname' THEN shipname
> WHEN @.SortField = 'vdn' THEN vdn
> WHEN @.SortField = 'eventlocation' THEN eventlocation
> WHEN @.SortField = 'pcfn' THEN pcfn
> END
> DECLARE @.FirstRec int, @.LastRec int
> SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
> SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
> SELECT
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> FROM
> #TempTable
> WHERE
> ID > @.FirstRec AND ID < @.LastRec
> SELECT @.TotalRecords = COUNT(*) FROM #TempTable
> GO
>|||Hey CB,
Thanks for the terrific knowledge. I learned something new today! :-)
Having said that... while using a table variable has increased the
performance, it's only saved me 3 or 4 seconds on average. Here's the
weird thing, you would imagine that using LIKE '%'+@.con_num+'%' would
slow down the search, but it doesn't. In fact, the opposite occurs!
When I use LIKE @.con_num+'%' or LIKE '%'+@.con_num the average time is
27 seconds. When I use LIKE '%'+@.con_num+'%' the average time is 24
seconds.
I'm clueless as to why this is occuring. :-( The only thing I can
come up with is that the "containernum" field is a nvarchar and
includes a mishmash of char's and integers, which may be distorting the
scans somehow.|||This is an indication that your quuery optimizer has decided NOT to use the
index on containernum, and is still doing table scan... (There IS an index o
n
containernum , right ?)
This can happen when there are a large number of records which are a "match"
for the criteria you are passing in... If this query the only query you are
running on this table than you might cnsider mking the index on containernum
the clustered index.. That would improve perfoemce when using ...
containernum Where Like @.con_Num + '%'
What xactly is containernum, and what kind of values are stored in there?
"roy.anderson@.gmail.com" wrote:

> Hey CB,
> Thanks for the terrific knowledge. I learned something new today! :-)
> Having said that... while using a table variable has increased the
> performance, it's only saved me 3 or 4 seconds on average. Here's the
> weird thing, you would imagine that using LIKE '%'+@.con_num+'%' would
> slow down the search, but it doesn't. In fact, the opposite occurs!
> When I use LIKE @.con_num+'%' or LIKE '%'+@.con_num the average time is
> 27 seconds. When I use LIKE '%'+@.con_num+'%' the average time is 24
> seconds.
> I'm clueless as to why this is occuring. :-( The only thing I can
> come up with is that the "containernum" field is a nvarchar and
> includes a mishmash of char's and integers, which may be distorting the
> scans somehow.
>|||"containernum" is an nvarchar(8) field and contains various
alphanumeric characters. The length of entries in that field varies
from 1 to 8. I did have an clustered index on "containernum"
originally, but I was getting slow results and one of my coworkers
suggested enabled a Full-Text Index using "containernum." When I did
that it deleted the original "containernum" clustered index and
replaced it with a "UID" clustered index (UID is the PK for table
new315_itv). I'm currently using the full-text index. Should I delete
it and switch back to using the containernum clustered index?
Am I making sense? :-)|||<roy.anderson@.gmail.com> wrote in message
news:1112638709.920563.71130@.f14g2000cwb.googlegroups.com...
> "containernum" is an nvarchar(8) field and contains various
> alphanumeric characters. The length of entries in that field varies
> from 1 to 8. I did have an clustered index on "containernum"
> originally, but I was getting slow results and one of my coworkers
> suggested enabled a Full-Text Index using "containernum." When I did
> that it deleted the original "containernum" clustered index and
> replaced it with a "UID" clustered index (UID is the PK for table
> new315_itv). I'm currently using the full-text index. Should I delete
> it and switch back to using the containernum clustered index?
> Am I making sense? :-)
>
I think CBretana was suggesting that you reconsider you table design.
Specifically, the containernum column seems to contain composite data; the
container number plus whatever the alphabetic data represents. If these
pieces of data were separated out into discreet columns, the query analyzer
could take advantage of the index on the container_number column. In the
alternative, if you are unable to alter the table model, you might consider
creating an indexed view on the new315_itv table that includes a calculated
expression to extract the actual container number from the containernum
column. Here's a proof of concept on how to extract a number from an
alphanumeric string:
DECLARE @.s NVARCHAR(8)
SET @.s = 'abc123de'
SELECT SUBSTRING(
@.s,
PATINDEX('%[0-9]%',@.s),
CASE PATINDEX('%[0-9]',@.s)
WHEN 0 THEN PATINDEX('%[0-9][^0-9]%',@.s) - PATINDEX('%[0-9]%',@.s) + 1
ELSE PATINDEX('%[0-9]',@.s) - PATINDEX('%[0-9]%',@.s) + 1
END
)
Also, have you considered using VARCHAR instead of NVARCHAR for your textual
data. NVARCHAR requires twice the storage of VARCHAR an should only be used
if your textual data includes Unicode characters. Here's an article:
http://aspfaq.com/show.asp?id=2354
Finally, here's an article that compares various methods of paging through a
recordset. It includes an example of a stored procedure that makes use of a
temp table as well as other stored procedure examples with better
performance.
http://aspfaq.com/show.asp?id=2120
HTH
-Chris Hohmann|||I agree w/Chris... If you can, Redesign the table so that each discreet data
element is in it's own column... But yes, you should switch back to using
Clustered index on the column that the query predicate (Whats in the Where
Clause) uses... ESPECIALLY If the query extracts a range of values.
And that is what ...
Where X Like @.Value + '%' will be doing, since it translates to Where X >=
@.Value And <= @.Value + 'ZZZZZZZZZZZZZZZZZZZZZZZZ' (actually whatever the
Query Parser determines is the last possible value in sort order that will
satisfy the Like.)
"Chris Hohmann" wrote:

> <roy.anderson@.gmail.com> wrote in message
> news:1112638709.920563.71130@.f14g2000cwb.googlegroups.com...
> I think CBretana was suggesting that you reconsider you table design.
> Specifically, the containernum column seems to contain composite data; the
> container number plus whatever the alphabetic data represents. If these
> pieces of data were separated out into discreet columns, the query analyze
r
> could take advantage of the index on the container_number column. In the
> alternative, if you are unable to alter the table model, you might conside
r
> creating an indexed view on the new315_itv table that includes a calculate
d
> expression to extract the actual container number from the containernum
> column. Here's a proof of concept on how to extract a number from an
> alphanumeric string:
> DECLARE @.s NVARCHAR(8)
> SET @.s = 'abc123de'
> SELECT SUBSTRING(
> @.s,
> PATINDEX('%[0-9]%',@.s),
> CASE PATINDEX('%[0-9]',@.s)
> WHEN 0 THEN PATINDEX('%[0-9][^0-9]%',@.s) - PATINDEX('%[0-9]%',@.s) + 1
> ELSE PATINDEX('%[0-9]',@.s) - PATINDEX('%[0-9]%',@.s) + 1
> END
> )
> Also, have you considered using VARCHAR instead of NVARCHAR for your textu
al
> data. NVARCHAR requires twice the storage of VARCHAR an should only be use
d
> if your textual data includes Unicode characters. Here's an article:
> http://aspfaq.com/show.asp?id=2354
> Finally, here's an article that compares various methods of paging through
a
> recordset. It includes an example of a stored procedure that makes use of
a
> temp table as well as other stored procedure examples with better
> performance.
> http://aspfaq.com/show.asp?id=2120
>
> HTH
> -Chris Hohmann
>
>|||Thanks for the info you two. I'll be sure to check out those articles
Chris.
Actually, believe it or not, I have the Stored Proc at a comfortable
spot now. When a user searches using at least 3 characters, the results
return in under a second, when searches occur with less than 3
characters, the results can take up to 35 seconds to return (but this
warning is now noted on the site). See my new stored proc below. I
utilized CB's table variable idea but maintained the full-text index.
Basically (I believe) if one uses LIKE sqlserver looks for a regular
index, if one uses CONTAINS or FREETEXT sqlserver looks for a full-text
index. However, for whatever reason, I can't use CONTAINS on character
searches that contain less than 3 characters. It doesn't error out, it
just doesn't display anything. Probably an idiosyncrasy of full-text
searches.
CREATE PROCEDURE [Get_Data]
@.CurrentPage int,
@.PageSize int,
@.TotalRecords int output,
@.con_num nvarchar(8)
AS
SET NOCOUNT ON
DECLARE @.T Table
(
RowNum INTEGER PRIMARY KEY Identity NOT NULL,
PK UNIQUEIDENTIFIER NOT NULL
)
IF LEN(@.con_num) >= 3
BEGIN
SET @.con_num = '"'+@.con_num+'*"'
INSERT INTO @.T (PK)
SELECT
uid
FROM
dbo.new315_itv
WHERE CONTAINS (containernum, @.con_num)
END
ELSE
BEGIN
INSERT INTO @.T (PK)
SELECT
uid
FROM
dbo.new315_itv
WHERE containernum LIKE '%'+@.con_num+'%'
END
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
SELECT
A.uid,
A.event,
A.bookingnum,
A.vanowner,
A.containernum,
A.tcn,
A.poe,
A.pod,
A.shipname,
A.vdn,
A.eventlocation,
A.pcfn
FROM
dbo.new315_itv A INNER JOIN @.T T ON T.PK = A.uid
WHERE
T.RowNum BETWEEN @.FirstRec AND @.LastRec
SELECT @.TotalRecords = COUNT(*) FROM @.T
GO

Can report parameter type be determined in code?

SSRS 2005
OK, I almost have this figured out.
I have a custom assembly. In the OnInit() method of the report I instantiate
my class and pass a reference to the report's Parameters collection to my
custom class.
In my custom class I then access the Parameters collection to determine the
report parameter values entered by the user. I can then output the parameter
values to a textbox in my report using an expression like
=Code.RptLib.GetParamValues().
The problem I have now is I need to be able to figure out the data type of
each report parameter so I can format the values properly. For example Dates
need to be formatted differently from Floats.
So, how do I figure out the data type of each report parameter by inspecting
the Parameters collection?
I am guessing the answer is that I can't and that I should use the web
service, but I don't want to jump through those hoops and I thought it was
worth asking if there is an easier way.
-- Chris
--
Chris, SSSIHello Chris,
Since the ReportObjectModel does not expose the interface of datatype, you
could not access it.
I would like to know whether your application could access the DOM object
of your report. If so, then you could access the Datatype.
I will also send your feedback to the product team to check whether they
will consider to expose more interface for developer to access the DataType.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hey Chris - out of curiousity, why do you need to go this route to show the
parameters on the report since obviously, you can just =@.param1 in the
textbox expression on the report itself?
=-Chris
"Chris G." <ChrisG@.nospam.nospam> wrote in message
news:FE87C610-CF3A-4FE8-8247-B7338F4C9DA4@.microsoft.com...
> SSRS 2005
> OK, I almost have this figured out.
> I have a custom assembly. In the OnInit() method of the report I
> instantiate
> my class and pass a reference to the report's Parameters collection to my
> custom class.
> In my custom class I then access the Parameters collection to determine
> the
> report parameter values entered by the user. I can then output the
> parameter
> values to a textbox in my report using an expression like
> =Code.RptLib.GetParamValues().
> The problem I have now is I need to be able to figure out the data type of
> each report parameter so I can format the values properly. For example
> Dates
> need to be formatted differently from Floats.
> So, how do I figure out the data type of each report parameter by
> inspecting
> the Parameters collection?
> I am guessing the answer is that I can't and that I should use the web
> service, but I don't want to jump through those hoops and I thought it was
> worth asking if there is an easier way.
> -- Chris
>
> --
> Chris, SSSI|||Hey Chris,
If you look in the reporting services database, in the Catalog table,
there's a column called Parameters. That column contains an XML
formatted expression describing each of the parameters attached to a
report. Probably not the best method, but you could extract the
parameter datatype from that column.
Evan
Chris G. wrote:
> SSRS 2005
> OK, I almost have this figured out.
> I have a custom assembly. In the OnInit() method of the report I instantiate
> my class and pass a reference to the report's Parameters collection to my
> custom class.
> In my custom class I then access the Parameters collection to determine the
> report parameter values entered by the user. I can then output the parameter
> values to a textbox in my report using an expression like
> =Code.RptLib.GetParamValues().
> The problem I have now is I need to be able to figure out the data type of
> each report parameter so I can format the values properly. For example Dates
> need to be formatted differently from Floats.
> So, how do I figure out the data type of each report parameter by inspecting
> the Parameters collection?
> I am guessing the answer is that I can't and that I should use the web
> service, but I don't want to jump through those hoops and I thought it was
> worth asking if there is an easier way.
> -- Chris
>
> --
> Chris, SSSI|||Hi Chris!
Thank you for replying to one of my posts again. I appreciate the input!
>>why do you need to go this route to show the parameters on the report since
>>obviously, you can just =@.param1 in the textbox expression on the report itself?
What I am trying to do is develop a generic approach to output the
parameters for ANY report. My report template for new reports will have all
the logic built into it to automatically output the parameters for the
report. Here is my approach so far:
1. OnInit() in my report instantiates a class in my custom assembly and
passes it a reference to the Parameters global collection. That way my custom
assembly can access the Parameters collection.
2. I have a table in my report which uses an XML data source. The XML
dataset is provided by a function in my custom assembly:
=Code.RptLib.ReportParametersXML. ReportParametersXML loops through the
Parameters collection and builds XML containing the parameter prompts and
values (this also requires defining the parameter prompts in a hidden report
parameter since they are not accessible from the object model) which is
output by the table. So I have two columns in my report. Left column has the
parameter prompts. Right column has the parameter values. ReportParametersXML
automatically handles formatting Single Value and MultiValue parameters (you
can figure that out from the object model). What I can't to is get the
parameter type to know if I am formatting a Date, Integer, Float, etc.
Eventually we will be building custom report parameter pages for our
reports. When we get to that I will be using the web service to get the
parameter definitions and then will have access to the parameter data types
and will be able to pass that information into the report.
However for this release of our project, we are relying on Reporting
Services to generate the report parameter controls. So I was looking for a
short term way to figure out the report parameter types from within the
report (which to be honest I think is a reasonable thing to want to do).
Looks like it is not possible. So since I have to tell the report the
parameter prompts anyway (eventually this will come from the web service
anyway) I can also just define the parameter types.
Hope that made sense.
-- Chris
Chris, SSSI
"Chris Conner" wrote:
> Hey Chris - out of curiousity, why do you need to go this route to show the
> parameters on the report since obviously, you can just =@.param1 in the
> textbox expression on the report itself?
> =-Chris
>
> "Chris G." <ChrisG@.nospam.nospam> wrote in message
> news:FE87C610-CF3A-4FE8-8247-B7338F4C9DA4@.microsoft.com...
> > SSRS 2005
> >
> > OK, I almost have this figured out.
> >
> > I have a custom assembly. In the OnInit() method of the report I
> > instantiate
> > my class and pass a reference to the report's Parameters collection to my
> > custom class.
> >
> > In my custom class I then access the Parameters collection to determine
> > the
> > report parameter values entered by the user. I can then output the
> > parameter
> > values to a textbox in my report using an expression like
> > =Code.RptLib.GetParamValues().
> >
> > The problem I have now is I need to be able to figure out the data type of
> > each report parameter so I can format the values properly. For example
> > Dates
> > need to be formatted differently from Floats.
> >
> > So, how do I figure out the data type of each report parameter by
> > inspecting
> > the Parameters collection?
> >
> > I am guessing the answer is that I can't and that I should use the web
> > service, but I don't want to jump through those hoops and I thought it was
> > worth asking if there is an easier way.
> >
> > -- Chris
> >
> >
> >
> > --
> > Chris, SSSI
>
>|||Chris,
I have seen you use this syntax in another post also:
=@.param1
Is this your way of indicating a parameter from the Parameters collection?
The SSRS documentation mentions these supported syntaxes:
Collection!ObjectName
=User!Language
Collection.Item("ObjectName")
=User.Item("Language")
Collection("ObjectName")
=User("Language")
But I have never seen =@.param1 as a supported syntax.
Is that a 4th alternative or is that just your own shorthand?
-- Chris
--
Chris, SSSI
"Chris Conner" wrote:
> Hey Chris - out of curiousity, why do you need to go this route to show the
> parameters on the report since obviously, you can just =@.param1 in the
> textbox expression on the report itself?
> =-Chris
>
> "Chris G." <ChrisG@.nospam.nospam> wrote in message
> news:FE87C610-CF3A-4FE8-8247-B7338F4C9DA4@.microsoft.com...
> > SSRS 2005
> >
> > OK, I almost have this figured out.
> >
> > I have a custom assembly. In the OnInit() method of the report I
> > instantiate
> > my class and pass a reference to the report's Parameters collection to my
> > custom class.
> >
> > In my custom class I then access the Parameters collection to determine
> > the
> > report parameter values entered by the user. I can then output the
> > parameter
> > values to a textbox in my report using an expression like
> > =Code.RptLib.GetParamValues().
> >
> > The problem I have now is I need to be able to figure out the data type of
> > each report parameter so I can format the values properly. For example
> > Dates
> > need to be formatted differently from Floats.
> >
> > So, how do I figure out the data type of each report parameter by
> > inspecting
> > the Parameters collection?
> >
> > I am guessing the answer is that I can't and that I should use the web
> > service, but I don't want to jump through those hoops and I thought it was
> > worth asking if there is an easier way.
> >
> > -- Chris
> >
> >
> >
> > --
> > Chris, SSSI
>
>|||Hi Evan,
Interesting suggestion. :-)
My only concern is, per Microsoft, you are not supposed to access the DB
directly because the DB schema is subject to change (without notice) in
future releases.
Still, a creative solution.
Overall, the thing is, I am looking for a high performance solution. I could
also use the web service to get the parameter definitions, or inspect the
.rdl file for the report. Both have also been suggested to me. It just seems
silly to me to have to use one of those more complex approaches so that the
report can find out about itself! ;-) Follow what I am saying? Because of
current limitations in the report object model, the report has to "query
itself" via an external approach (the web service or .rdl file from which it
was instantiated). Seems like jumping through hoops to me.
The problem ;-) is I have been spoiled by the Actuate reporting system in
which you work with a full object and event driven programming model...I am
trying to replicate functionality in SSRS that is trivial to build using
Actuate (though I won't get into how much more $$$ Actuate costs over SSRS).
Anyway, I guess I am just still trying to learn how to think like an SSRS
developer. The paradigm shift is a little rough. ;-)
-- Chris
Chris, SSSI
"emorgoch" wrote:
> Hey Chris,
> If you look in the reporting services database, in the Catalog table,
> there's a column called Parameters. That column contains an XML
> formatted expression describing each of the parameters attached to a
> report. Probably not the best method, but you could extract the
> parameter datatype from that column.
> Evan
> Chris G. wrote:
> > SSRS 2005
> >
> > OK, I almost have this figured out.
> >
> > I have a custom assembly. In the OnInit() method of the report I instantiate
> > my class and pass a reference to the report's Parameters collection to my
> > custom class.
> >
> > In my custom class I then access the Parameters collection to determine the
> > report parameter values entered by the user. I can then output the parameter
> > values to a textbox in my report using an expression like
> > =Code.RptLib.GetParamValues().
> >
> > The problem I have now is I need to be able to figure out the data type of
> > each report parameter so I can format the values properly. For example Dates
> > need to be formatted differently from Floats.
> >
> > So, how do I figure out the data type of each report parameter by inspecting
> > the Parameters collection?
> >
> > I am guessing the answer is that I can't and that I should use the web
> > service, but I don't want to jump through those hoops and I thought it was
> > worth asking if there is an easier way.
> >
> > -- Chris
> >
> >
> >
> > --
> > Chris, SSSI
>|||Wei Lu,
As always, thank you for your quick reply! :-)
>>Since the ReportObjectModel does not expose the interface of datatype, you
>>could not access it.
OK that is what I thought. I just wanted to make sure I was not overlooking
something.
>>I would like to know whether your application could access the DOM object
>>of your report. If so, then you could access the Datatype.
Do you mean loading the .rdl file and accessing the parameters node?
>>I will also send your feedback to the product team to check whether they
>>will consider to expose more interface for developer to access the DataType.
Thanks!
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hello Chris,
> Since the ReportObjectModel does not expose the interface of datatype, you
> could not access it.
> I would like to know whether your application could access the DOM object
> of your report. If so, then you could access the Datatype.
> I will also send your feedback to the product team to check whether they
> will consider to expose more interface for developer to access the DataType.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Ack I apologize - I was getting lazy!
I am used to using @.param1 in the data tab... but when you access it via the
layout tab you should use the options you have already mentioned. :)
I.e. =Parameters!param1.value
Anyways, I wanted to say that we have a wizard tool we have build that takes
all the parameters for ANY report and prompts the user, for those values -
but we used the reporting services web service to get this information.
this also allowed us to create specialized parameters that signified whether
we wanted our wizard to show the parameter as a multi-selection list as
opposed to just a combo drop down box, or dates that automatically have the
beginning year or beginning month, or beginning day auto filled - the same
principle for an end date parameter as well.
I find it "funny" how we are doing the same thing.
Mine is for windows based applications (thick clients) - but you are using
it for web forms.
Anyways, you will succeed in your endeavor - but you won't be able to get
the parameter type until you get to the web service unfortunately. :)
=-Chris
"Chris G." <ChrisG@.nospam.nospam> wrote in message
news:F444CEE3-AB58-4F41-9C18-3BEF66525E1A@.microsoft.com...
> Chris,
> I have seen you use this syntax in another post also:
> =@.param1
> Is this your way of indicating a parameter from the Parameters collection?
> The SSRS documentation mentions these supported syntaxes:
> Collection!ObjectName
> =User!Language
> Collection.Item("ObjectName")
> =User.Item("Language")
> Collection("ObjectName")
> =User("Language")
> But I have never seen =@.param1 as a supported syntax.
> Is that a 4th alternative or is that just your own shorthand?
> -- Chris
> --
> Chris, SSSI
>
> "Chris Conner" wrote:
>> Hey Chris - out of curiousity, why do you need to go this route to show
>> the
>> parameters on the report since obviously, you can just =@.param1 in the
>> textbox expression on the report itself?
>> =-Chris
>>
>> "Chris G." <ChrisG@.nospam.nospam> wrote in message
>> news:FE87C610-CF3A-4FE8-8247-B7338F4C9DA4@.microsoft.com...
>> > SSRS 2005
>> >
>> > OK, I almost have this figured out.
>> >
>> > I have a custom assembly. In the OnInit() method of the report I
>> > instantiate
>> > my class and pass a reference to the report's Parameters collection to
>> > my
>> > custom class.
>> >
>> > In my custom class I then access the Parameters collection to determine
>> > the
>> > report parameter values entered by the user. I can then output the
>> > parameter
>> > values to a textbox in my report using an expression like
>> > =Code.RptLib.GetParamValues().
>> >
>> > The problem I have now is I need to be able to figure out the data type
>> > of
>> > each report parameter so I can format the values properly. For example
>> > Dates
>> > need to be formatted differently from Floats.
>> >
>> > So, how do I figure out the data type of each report parameter by
>> > inspecting
>> > the Parameters collection?
>> >
>> > I am guessing the answer is that I can't and that I should use the web
>> > service, but I don't want to jump through those hoops and I thought it
>> > was
>> > worth asking if there is an easier way.
>> >
>> > -- Chris
>> >
>> >
>> >
>> > --
>> > Chris, SSSI
>>|||The only downside to this approach - you will have to also know the path
that your report was executed from from the report server - because if you
have two reports with the same name, they would more than likely have
different parameters.
I.e.
/Custom/Year To Date
/My Reports/Testing/Year To Date
Above are two reports on the report server, I would see in the catalog table
two rows for "Year To Date". When I execute this report, in order for me to
get the right parameter list from the catalog table, I would have to know
which path as well - not just the name of my own report that is executing.
You CAN do it this way, but you should also get the Path.
Chris - I know Microsoft says the schema is subject to change - so use a
view - if they change the schema, you can always update the view.
Better option: The web service... then you won't care if they change the
schema.
=-Chris
"emorgoch" <emorgoch.public@.gmail.com> wrote in message
news:1163777316.972748.135970@.f16g2000cwb.googlegroups.com...
> Hey Chris,
> If you look in the reporting services database, in the Catalog table,
> there's a column called Parameters. That column contains an XML
> formatted expression describing each of the parameters attached to a
> report. Probably not the best method, but you could extract the
> parameter datatype from that column.
> Evan
> Chris G. wrote:
>> SSRS 2005
>> OK, I almost have this figured out.
>> I have a custom assembly. In the OnInit() method of the report I
>> instantiate
>> my class and pass a reference to the report's Parameters collection to my
>> custom class.
>> In my custom class I then access the Parameters collection to determine
>> the
>> report parameter values entered by the user. I can then output the
>> parameter
>> values to a textbox in my report using an expression like
>> =Code.RptLib.GetParamValues().
>> The problem I have now is I need to be able to figure out the data type
>> of
>> each report parameter so I can format the values properly. For example
>> Dates
>> need to be formatted differently from Floats.
>> So, how do I figure out the data type of each report parameter by
>> inspecting
>> the Parameters collection?
>> I am guessing the answer is that I can't and that I should use the web
>> service, but I don't want to jump through those hoops and I thought it
>> was
>> worth asking if there is an easier way.
>> -- Chris
>>
>> --
>> Chris, SSSI
>|||Hello Chris,
Yes, I mean you need to load the rdl file and access the paramenters node.
I understand that this may be more complex than the object model but for
now this is the most usable approach in your project.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Chris,
>>Ack I apologize - I was getting lazy!
No problemo. I just wanted to be sure that I wasn't missing something. :-)
>>Anyways, I wanted to say that we have a wizard tool we have build...
Sounds pretty cool! I think in another post you mentioned that for ownership
reasons you would not be able to share that code. Any thoughts about
commercializing it? ;-)
>>I find it "funny" how we are doing the same thing.
I agree. It would also be great if Microsoft would just build this kind of
capability into the product! :-) I am sure we are not the only developers
facing and solving this problem.
>>Anyways, you will succeed in your endeavor - but you won't be able to get
>>the parameter type until you get to the web service unfortunately. :)
I am with you on that! Eventually...
-- Chris
--
Chris, SSSI
"Chris Conner" wrote:
> Ack I apologize - I was getting lazy!
> I am used to using @.param1 in the data tab... but when you access it via the
> layout tab you should use the options you have already mentioned. :)
> I.e. =Parameters!param1.value
> Anyways, I wanted to say that we have a wizard tool we have build that takes
> all the parameters for ANY report and prompts the user, for those values -
> but we used the reporting services web service to get this information.
> this also allowed us to create specialized parameters that signified whether
> we wanted our wizard to show the parameter as a multi-selection list as
> opposed to just a combo drop down box, or dates that automatically have the
> beginning year or beginning month, or beginning day auto filled - the same
> principle for an end date parameter as well.
> I find it "funny" how we are doing the same thing.
> Mine is for windows based applications (thick clients) - but you are using
> it for web forms.
> Anyways, you will succeed in your endeavor - but you won't be able to get
> the parameter type until you get to the web service unfortunately. :)
> =-Chris
>
> "Chris G." <ChrisG@.nospam.nospam> wrote in message
> news:F444CEE3-AB58-4F41-9C18-3BEF66525E1A@.microsoft.com...
> > Chris,
> >
> > I have seen you use this syntax in another post also:
> > =@.param1
> >
> > Is this your way of indicating a parameter from the Parameters collection?
> >
> > The SSRS documentation mentions these supported syntaxes:
> >
> > Collection!ObjectName
> > =User!Language
> >
> > Collection.Item("ObjectName")
> > =User.Item("Language")
> >
> > Collection("ObjectName")
> > =User("Language")
> >
> > But I have never seen =@.param1 as a supported syntax.
> >
> > Is that a 4th alternative or is that just your own shorthand?
> >
> > -- Chris
> >
> > --
> > Chris, SSSI
> >
> >
> > "Chris Conner" wrote:
> >
> >> Hey Chris - out of curiousity, why do you need to go this route to show
> >> the
> >> parameters on the report since obviously, you can just =@.param1 in the
> >> textbox expression on the report itself?
> >>
> >> =-Chris
> >>
> >>
> >>
> >> "Chris G." <ChrisG@.nospam.nospam> wrote in message
> >> news:FE87C610-CF3A-4FE8-8247-B7338F4C9DA4@.microsoft.com...
> >> > SSRS 2005
> >> >
> >> > OK, I almost have this figured out.
> >> >
> >> > I have a custom assembly. In the OnInit() method of the report I
> >> > instantiate
> >> > my class and pass a reference to the report's Parameters collection to
> >> > my
> >> > custom class.
> >> >
> >> > In my custom class I then access the Parameters collection to determine
> >> > the
> >> > report parameter values entered by the user. I can then output the
> >> > parameter
> >> > values to a textbox in my report using an expression like
> >> > =Code.RptLib.GetParamValues().
> >> >
> >> > The problem I have now is I need to be able to figure out the data type
> >> > of
> >> > each report parameter so I can format the values properly. For example
> >> > Dates
> >> > need to be formatted differently from Floats.
> >> >
> >> > So, how do I figure out the data type of each report parameter by
> >> > inspecting
> >> > the Parameters collection?
> >> >
> >> > I am guessing the answer is that I can't and that I should use the web
> >> > service, but I don't want to jump through those hoops and I thought it
> >> > was
> >> > worth asking if there is an easier way.
> >> >
> >> > -- Chris
> >> >
> >> >
> >> >
> >> > --
> >> > Chris, SSSI
> >>
> >>
> >>
>
>|||>>The only downside to this approach - you will have to also know the path
Not to mention, that you also have to know the URL of the Report Server! We
have a staged release environment. Development, Test and Production. Each has
a different report server (and the report servers are different than the
application web servers) and each stage can have different report versions.
So the production web server would have to access the reports on the
production Report Server to get the correct parameter definitions. I have
already taken care of this capability for other reasons, but my point is it
gets somewhat complicated.
>>Chris - I know Microsoft says the schema is subject to change - so use a
>>view - if they change the schema, you can always update the view.
Agreed.
>>Better option: The web service... then you won't care if they change the
>>schema.
You are absolutely right...and I think I will have to go there sooner than I
expected!
;-)
--
Chris, SSSI
"Chris Conner" wrote:
> The only downside to this approach - you will have to also know the path
> that your report was executed from from the report server - because if you
> have two reports with the same name, they would more than likely have
> different parameters.
> I.e.
> /Custom/Year To Date
> /My Reports/Testing/Year To Date
> Above are two reports on the report server, I would see in the catalog table
> two rows for "Year To Date". When I execute this report, in order for me to
> get the right parameter list from the catalog table, I would have to know
> which path as well - not just the name of my own report that is executing.
> You CAN do it this way, but you should also get the Path.
> Chris - I know Microsoft says the schema is subject to change - so use a
> view - if they change the schema, you can always update the view.
> Better option: The web service... then you won't care if they change the
> schema.
> =-Chris
> "emorgoch" <emorgoch.public@.gmail.com> wrote in message
> news:1163777316.972748.135970@.f16g2000cwb.googlegroups.com...
> > Hey Chris,
> >
> > If you look in the reporting services database, in the Catalog table,
> > there's a column called Parameters. That column contains an XML
> > formatted expression describing each of the parameters attached to a
> > report. Probably not the best method, but you could extract the
> > parameter datatype from that column.
> >
> > Evan
> >
> > Chris G. wrote:
> >> SSRS 2005
> >>
> >> OK, I almost have this figured out.
> >>
> >> I have a custom assembly. In the OnInit() method of the report I
> >> instantiate
> >> my class and pass a reference to the report's Parameters collection to my
> >> custom class.
> >>
> >> In my custom class I then access the Parameters collection to determine
> >> the
> >> report parameter values entered by the user. I can then output the
> >> parameter
> >> values to a textbox in my report using an expression like
> >> =Code.RptLib.GetParamValues().
> >>
> >> The problem I have now is I need to be able to figure out the data type
> >> of
> >> each report parameter so I can format the values properly. For example
> >> Dates
> >> need to be formatted differently from Floats.
> >>
> >> So, how do I figure out the data type of each report parameter by
> >> inspecting
> >> the Parameters collection?
> >>
> >> I am guessing the answer is that I can't and that I should use the web
> >> service, but I don't want to jump through those hoops and I thought it
> >> was
> >> worth asking if there is an easier way.
> >>
> >> -- Chris
> >>
> >>
> >>
> >> --
> >> Chris, SSSI
> >
>
>

Can report content be generated dynamically?

SSRS 2005
I have figured out how to use a custom code assembly to dynamically control
the content of the textboxes in the footer of my report.
For example, I have an expression like this in one of the textboxes:
=Code.OakRptLib.BuildPageFooterLeft()
This approach assumes that the textboxes already exist in the report footer.
I am trying to build custom code assemblies that allow my reports to be
dynamically configured in a consistent, standard manner at run time.
Ideally when the report starts up I would like to dynamically create these
text boxes in the report footer so that I can make sure they all use the same
font settings, positioning, content, etc.
Is there a way that I can dynamically create these text boxes in the report
footer when the report first starts processing? For example with code in the
OnInit() method?
--
Chris, SSSIHello Chris,
Based on my research, you could not dynamically create a report item in the
code.
The only thing you may do is using a program to dynamically create a rdl
file.
Since the RDL file is a XML format, you could use the .NET program to
generate a RDL file.
Hope this will be some help for you.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Thanks Wei!
Did Steven Cheng have any ideas about this?
-- Chris
--
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hello Chris,
> Based on my research, you could not dynamically create a report item in the
> code.
> The only thing you may do is using a program to dynamically create a rdl
> file.
> Since the RDL file is a XML format, you could use the .NET program to
> generate a RDL file.
> Hope this will be some help for you.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Chris,
I have discussed with Steven, and he also confirmed this.
I suggest you may try some suggestion from Chris Conner in other post: "Can
I obtain a reference to a report item?"
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.