Can this be done?

Is anyone aware of a utility, product or method to change a report definition at run-time? For example, I'd like to create a report and then perhaps remove columns, or change column order at run-time etc, based on user preference.

Thanks - Amos.

Out of the box you could use Report Builder to do this. There are some limitations in terms of layout and programmability, but it provides a very easy drag and drop experience for ad-hoc reporting.|||

John,

Thanks for the response. However, I am using the Report Viewer component to do strictly client side reporting using datasets. So, I don't think Report Builder will work, since, I think it only connects to a Report Services server (correct?).

Amos.

|||Yes, report builder requires the server.

Can this be done using TSQL ?!

Hello
im writing an inventory application for a customer that needs to calculate
item cost by Moving Average method which requires calculating the cost after
each operation, i have a good experience with TSQL but so far i failed to
write the statement that can do this WITHOUT writing cursors
im trying to avoid calculating cost after each transaction to the inventory
. and by writing a stored procedure , get a list showing transactions and
item avg in a period
here is a description of moving avg method , also available here for those
who cant read html
http://www.fms.indiana.edu/auxiliary/inventory.asp
Moving Average--Perpetual
Continuous or moving average assigns a unit value to cost of goods available
for sale. In this scenario, the average cost determines cost of goods sold
at the time of each sale. This method requires a calculation of average unit
cost after each purchase as illustrated below.
# of Units Cost per Unit Total Cost Moving Avg. Cost
Beginning inventory, 7/1 200
$5,000
$25.00
Purchase, 8/10 100
$26.00
2,600
Inv. Balance 300
7,600
25.33
Sale, 9/15 (100)
25.33
(2,533)
Inv. Balance 200
5,067
Purchase, 12/7 600
27.00
16,200
Inv. Balance 800
21,267
26.58
Sale, 12/18 (300)
26.58
(7,975)
Inv. Balance 500
13,292
Sale, 2/22 (250)
26.58
(6,645
Inv. Balance 250
6,647
Purchase, 3/20 300
28.00
8,400
Inv. Balance 550
15,047
27.36
Sale, 5/15 (150)
27.36
(4,104)
Inv. Balance 400
27.36
10,943
Ending Inventory 400
10,943
Cost of Goods Sold 100
2,533
300
7,975
250
6,645
150
4,104
800
$21,257
Regards
Bassamcan you post DDL and some data..and also the example..pasted correctly..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Bassam" <egbas@.yahoo.com> wrote in message
news:ORrEOZODFHA.3596@.TK2MSFTNGP12.phx.gbl...
> Hello
> im writing an inventory application for a customer that needs to calculate
> item cost by Moving Average method which requires calculating the cost
after
> each operation, i have a good experience with TSQL but so far i failed to
> write the statement that can do this WITHOUT writing cursors
> im trying to avoid calculating cost after each transaction to the
inventory
> . and by writing a stored procedure , get a list showing transactions and
> item avg in a period
> here is a description of moving avg method , also available here for those
> who cant read html
> http://www.fms.indiana.edu/auxiliary/inventory.asp
> Moving Average--Perpetual
> Continuous or moving average assigns a unit value to cost of goods
available
> for sale. In this scenario, the average cost determines cost of goods sold
> at the time of each sale. This method requires a calculation of average
unit
> cost after each purchase as illustrated below.
> # of Units Cost per Unit Total Cost Moving Avg. Cost
> Beginning inventory, 7/1 200
> $5,000
> $25.00
> Purchase, 8/10 100
> $26.00
> 2,600
>
> Inv. Balance 300
> 7,600
> 25.33
> Sale, 9/15 (100)
> 25.33
> (2,533)
>
> Inv. Balance 200
> 5,067
>
> Purchase, 12/7 600
> 27.00
> 16,200
>
> Inv. Balance 800
> 21,267
> 26.58
> Sale, 12/18 (300)
> 26.58
> (7,975)
>
> Inv. Balance 500
> 13,292
>
> Sale, 2/22 (250)
> 26.58
> (6,645
>
> Inv. Balance 250
> 6,647
>
> Purchase, 3/20 300
> 28.00
> 8,400
>
> Inv. Balance 550
> 15,047
> 27.36
> Sale, 5/15 (150)
> 27.36
> (4,104)
>
> Inv. Balance 400
> 27.36
> 10,943
>
> Ending Inventory 400
> 10,943
>
> Cost of Goods Sold 100
> 2,533
>
> 300
> 7,975
>
> 250
> 6,645
>
> 150
> 4,104
>
> 800
> $21,257
>
>
> Regards
> Bassam
>|||Please post DDL and some INSERT statements of your sample data:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||On Mon, 7 Feb 2005 09:27:19 +0200, Bassam wrote:

>im writing an inventory application for a customer that needs to calculate
>item cost by Moving Average method which requires calculating the cost afte
r
>each operation, i have a good experience with TSQL but so far i failed to
>write the statement that can do this WITHOUT writing cursors
Hi Bassam,
I think you can get the moving average by a simple self-join with group
by. Check the following example:
-- First, create a table to hold all transactions
-- Opening balance is considered a transaction in this simplified example
CREATE TABLE Operations
(OpDate smalldatetime not null primary key,
Amount int not null, -- >0 purchase <0 sale
UnitPrice money not null)
go
-- Insert all data (same as on web page you mentioned)
INSERT Operations (OpDate, Amount, UnitPrice)
SELECT '20040701', 200, 25
UNION ALL
SELECT '20040810', 100, 26
UNION ALL
SELECT '20040915', -100, 25.33
UNION ALL
SELECT '20041207', 600, 27
UNION ALL
SELECT '20041218', -300, 26.58
UNION ALL
SELECT '20050222', -250, 26.58
UNION ALL
SELECT '20050320', 300, 28
UNION ALL
SELECT '20050515', -150, 27.36
go
-- Here's the statement that will calculate amount, value and moving
-- average after each of the transaction.
SELECT a.OpDate AS InvDate,
SUM(b.Amount) AS Amount,
SUM(b.Amount * b.UnitPrice) AS Value,
SUM(b.Amount * b.UnitPrice) / SUM(b.Amount) AS MovingAvg
FROM Operations AS a
INNER JOIN Operations AS b
ON b.OpDate <= a.OpDate
GROUP BY a.OpDate
ORDER BY a.OpDate
go
-- Done. Now clean up the mess.
DROP TABLE Operations
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you for your input but result of your statement will calculate
"Weighted Average" not "Moving Average"
difference is shown in examples in this link
http://www.fms.indiana.edu/auxiliary/inventory.asp
if you open this page and search for weighted average you fill find the
example which works with your statement but the just below example which is
for moving average won't work
i will post DDL and some data here to clear the case
Regards
Bassam
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hhme01diqfdiohvb2rj3t896k5deoc47e9@.
4ax.com...
> On Mon, 7 Feb 2005 09:27:19 +0200, Bassam wrote:
>
calculate
after
> Hi Bassam,
> I think you can get the moving average by a simple self-join with group
> by. Check the following example:
> -- First, create a table to hold all transactions
> -- Opening balance is considered a transaction in this simplified example
> CREATE TABLE Operations
> (OpDate smalldatetime not null primary key,
> Amount int not null, -- >0 purchase <0 sale
> UnitPrice money not null)
> go
> -- Insert all data (same as on web page you mentioned)
> INSERT Operations (OpDate, Amount, UnitPrice)
> SELECT '20040701', 200, 25
> UNION ALL
> SELECT '20040810', 100, 26
> UNION ALL
> SELECT '20040915', -100, 25.33
> UNION ALL
> SELECT '20041207', 600, 27
> UNION ALL
> SELECT '20041218', -300, 26.58
> UNION ALL
> SELECT '20050222', -250, 26.58
> UNION ALL
> SELECT '20050320', 300, 28
> UNION ALL
> SELECT '20050515', -150, 27.36
> go
> -- Here's the statement that will calculate amount, value and moving
> -- average after each of the transaction.
> SELECT a.OpDate AS InvDate,
> SUM(b.Amount) AS Amount,
> SUM(b.Amount * b.UnitPrice) AS Value,
> SUM(b.Amount * b.UnitPrice) / SUM(b.Amount) AS MovingAvg
> FROM Operations AS a
> INNER JOIN Operations AS b
> ON b.OpDate <= a.OpDate
> GROUP BY a.OpDate
> ORDER BY a.OpDate
> go
> -- Done. Now clean up the mess.
> DROP TABLE Operations
> go
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Mon, 7 Feb 2005 14:32:20 +0200, Bassam wrote:

>Hello Hugo,
>Thank you for your input but result of your statement will calculate
>"Weighted Average" not "Moving Average"
>difference is shown in examples in this link
>http://www.fms.indiana.edu/auxiliary/inventory.asp
>if you open this page and search for weighted average you fill find the
>example which works with your statement but the just below example which is
>for moving average won't work
Hi Bassam,
I did check that page, and the results of my query were equal to the
moving average quoted on that page (the table directly after the heading
"Moving Average--Perpetual").

>i will post DDL and some data here to clear the case
Excellent idea!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
I tested your statement, great , it works to a great detail !! , i found a
problem in the moving avg in date 2/22/05 , it should be exactly as the one
done on 12/18/04 (sales also) to be 26.5860 , but the one on 2/22/05 is
26.5920 (it is exactly 26.5860 in table) so i will make my tests what if i
put 20 sales transactions and see the result
but your statement gave max accurate to date to table.do you know a way to
overcome this small shift ?
thank you and welcome to any comments
Bassam|||On Mon, 7 Feb 2005 15:24:41 +0200, Bassam wrote:

>Hello Hugo,
>I tested your statement, great , it works to a great detail !! , i found a
>problem in the moving avg in date 2/22/05 , it should be exactly as the one
>done on 12/18/04 (sales also) to be 26.5860 , but the one on 2/22/05 is
>26.5920 (it is exactly 26.5860 in table) so i will make my tests what if i
>put 20 sales transactions and see the result
Hi Bassam,
I noted the difference as well. This is caused by rounding errors.
Consider the first few rows in the sample data. The beginning inventory
shows 200 units at a total cost of $ 5,000 - exactle $ 25.00 on average.
After the first purchase, there are 300 units on stock and the total cost
is equal to $ 7,600. The average price is $ 25.333333333333333 (etc), but
it is rounded down to $ 25.33. This would mean that if the following sale
would not be for 100 units (as listed in the example), but for 300 units,
the total sale price would be $ 7,599 and the remaining stock would be 0
units, for a total price of $ 1.
The example on the web page graciously avoids this anomaly by only
including a new price after each purchase. It doesn't list the moving avg
cost after a sale, so I could not verify if the values given by my query
are correct or not.
If you need the moving average cost to reflect the situation after the
last purchase instead of after the last sale, try this (slightly more
complicated) query:
SELECT a.OpDate AS InvDate,
SUM(b.Amount) AS Amount,
SUM(b.Amount * b.UnitPrice) AS Value,
(SELECT SUM(c.Amount * c.UnitPrice) / SUM(c.Amount)
FROM Operations AS c
WHERE c.OpDate <= (SELECT MAX(d.OpDate)
FROM Operations AS d
WHERE d.OpDate <= a.OpDate
AND d.Amount > 0)) AS MovingAvg
FROM Operations AS a
INNER JOIN Operations AS b
ON b.OpDate <= a.OpDate
GROUP BY a.OpDate
ORDER BY a.OpDate
(Note: if you only need the date and the moving average, not the amount
and value of the inventory, you can remove the group by and the join to
"Operations AS b" - IOW, you can simplify to:
SELECT a.OpDate AS InvDate,
(SELECT SUM(c.Amount * c.UnitPrice) / SUM(c.Amount)
FROM Operations AS c
WHERE c.OpDate <= (SELECT MAX(d.OpDate)
FROM Operations AS d
WHERE d.OpDate <= a.OpDate
AND d.Amount > 0)) AS MovingAvg
FROM Operations AS a
ORDER BY a.OpDate
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Bassam,
Thanks to Hugo for posting the DDL for the table, I will assume it is
correct. Here is a try, but do not compare to the table in the link, there i
s
an error there.
Error in the link:
Sale, 12/18 (300) 26.58 (7,975)
well, -300 * 26.58 should be 7,974.
select
a.OpDate,
sum(b.Amount) as number_of_units,
sum(b.Amount * b.UnitPrice) as total_cost,
(
select
sum(c.Amount * c.UnitPrice) / sum(c.Amount)
from
Operations as c
where
c.OpDate <= (
select
max(d.OpDate)
from
Operations as d
where
sign(d.Amount) >= 0 and d.OpDate <= a.OpDate
)
) as moving_avg_cost
from
Operations as a
inner join
Operations as b
on a.OpDate >= b.OpDate
group by
a.OpDate
order by
a.OpDate
go
AMB
"Bassam" wrote:

> Hello Hugo,
> Thank you for your input but result of your statement will calculate
> "Weighted Average" not "Moving Average"
> difference is shown in examples in this link
> http://www.fms.indiana.edu/auxiliary/inventory.asp
> if you open this page and search for weighted average you fill find the
> example which works with your statement but the just below example which i
s
> for moving average won't work
> i will post DDL and some data here to clear the case
> Regards
> Bassam
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:hhme01diqfdiohvb2rj3t896k5deoc47e9@.
4ax.com...
> calculate
> after
>
>|||Hello Hugo,
Thank you, Clear and efficient ! , only last problem , what if a user need
to delete a purchasing happened at beginning of month - or adjust its unit
price value, that means all next averages used in next sales is wrong and
need to be recalculated, is there a way to recalculate unit price for sales
transactions ' i need to adjust that before using your statement again or
result will be wrong
to make situation more complicated is that it might also be more purchasing
down there with sales, i mean suppose user deleted 1 of 5 purchasing done at
beginning of the month , on day 1 , while other purchasing happened on day 5
, 8 , 12 , 14 remains , and there are sales in between. , how then i can
recalculate unit price (which is the moving average) for sales
transactions.in between ?
Thank you
Bassam

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
> >
>
>