sql reports: assembly

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

Wednesday, March 7, 2012

Can SQL 2005 load assembly from GAC?

Thursday, February 16, 2012

Can report parameter type be determined in code?

Can report content be generated dynamically?

sql reports

Blog Archive

About Me