I found many hints that customer parameter interfaces are a possible solution
for several problems.
But I can't find one piece about how this can be done. Not in the
documentation, not in this news group and not in google. I'm really investet
many time, but don't find anything.
Can one give me a hint where I can find information about this?Here's the design doc I wrote for ours:
Reporting Phase II Design
A Dynamic User Interface for Report Navigation and Runtime Parameter Gathering
Introduction
Having a dynamic user interface to present reports to users and gather
runtime parameter values originates from a singular thought: report
customization. We desire that reports be developed by end user
administrators in addition to professional services and in-house developers.
By providing this support we must provide a means by which a report may be
deployed into the production environment and then appear in the application
without any user interface development activity on our part. The best way to
support this desire is to build our user interface such that it does not rely
on specific knowledge of any one report but rather is built with awareness
that reports are present and that they gather runtime parameters. In this
fashion, report developers are free to upload new reports or modify existing
reports independent of a release cycle. The following sections will
illustrate how we may expose this capability from within our application.
Report Navigation
The first challenge regarding our dynamic user interface is to present the
reports so a user may choose among them. Our current user interface design
calls for a hierarchical folder paradigm that contains and organizes all
reports. It begins with a root node labeled â'My Reportsâ' that appears in the
â'Trackingâ' page. This folder can contain any number of sub folders, which
serve to categorize all reports and will initially contain the folders â'Email
Reportsâ', â'Subscriber Reportsâ', â'Admin Reportsâ', and â'Custom Reportsâ'. It is
not clear whether the folder â'My Reportsâ' will ever contain reports
themselves and it is presumed these first level folders will contain reports
and perhaps additional folders.
The Report Services component, created to serve the user interface and other
consumers, provides a method GetReports() that takes no input parameters and
returns to the caller a data table containing all of the reports that the
calling user is authorized to view. This data table will also contain the
hierarchy of folders such that the consumer may illustrate reports in
accordance with the prescribed hierarchy.
The data table returned by GetReports() will contain the following items:
â?¢ ID â' a unique identifier (GUID) that uniquely identifies the report item
â?¢ Type â' an enumeration describing the nature of the report item. This is
supported by the public enum ReportItemType containing Folder and Report
(this may be expanded to include other types as demand warrants.
â?¢ Name â' the plain text name of the report, suitable for illustration in the
user interface. (This value originates from the â'nameâ' property of a report
on the report server, which gets its value from the â'nameâ' property of the
report itself, which is established at design time. It is therefore
necessary for developers to assign a meaningful name at design time,
presumably assigned by Product Management at the time of envisioning.)
â?¢ Description â' A plain text description that serves to illustrate the
content/functionality of the report in greater detail than â'Nameâ' (above).
It may be useful perhaps as tool-tip text given the folder paradigm. This
value originates from the â'descriptionâ' property of the report on the report
server, which gets its value from the â'descriptionâ' property of the report
itself, which is established at design time. It is therefore necessary for
developers to assign a meaningful value during the development process;
presumably this text will originate from Product Management.
â?¢ Path â' A text string that contains the fully qualified path to the report
on the report server. This item equally serves as a unique identifier for a
report, describes the location of the report within the folder hierarchy, and
is used as in input parameter by the RenderReport() method that identifies
the report to be rendered.
â?¢ Parent â' a unique identifier (GUID) that points to a report item that
contains this report item. If null, it indicates the root of the hierarchy.
It is presumed that our user interface will use this data table to construct
the tree view that is illustrated in the user interface document. Further,
all attempts will be made to provide all of the information desired by the
user interface team and in a structure that is most suitable to their
consumption.
Runtime Parameter Value Gathering
While it is necessary to provide a listing of reports suitable for display,
the greater challenge comes in gathering the runtime parameter values that
will be used by the report to scope its contents and perhaps drive the order
in which result rows appear. To support consumers in the gathering of
runtime parameter values, the Report Services component exposes a method
GetReportParameters(). This method takes one parameter (string reportPath)
that is acquired through the â'Pathâ' item returned by GetReports() as
described above and returns a collection containing the items described
below. (Note: the values these items contain are a function of developer
action at design time, primarily via the settings made in the â'Report
Parametersâ' dialog of the IDE.
â?¢ Bool AllowBlank â' Determines whether a parameter value can be an empty
string. This applies only to parameters of type string (identified by
ETParameterTypeEnum Type, below)
â?¢ String[] DefaultValues â' A string array that typically contains one
element whose value indicates the default for this parameter. The user
interface should populate the appropriate control with this value. (Note: in
the future we may choose to support multi-value parameters; perhaps for use
with the SQL IN operator. If and when this becomes true, the potential for
multiple default values becomes true, hence it is declared as an array.)
â?¢ Bool DefaultValuesQueryBased â' Indicates whether DefaultValues (above) is
populated by an expression (false) or by a database query (true).
â?¢ Bool DefaultValuesQueryBasedSpecified â' Indicates whether a determination
can be made at runtime to assign DefaultValuesQueryBased a value. If this
value is false the origin of the default value(s) for this parameter is
indeterminate.
â?¢ String[] Dependencies â' If this parameterâ's default or valid values are
based on a parameterized query and that queryâ's parameter(s) have
indeterminate values, then this array will contain the names of those
parameters. In other words, if this property is not null, you will need to
gather the values for the parameters named in this property, make a
subsequent call to GetReportParameters, and then gather the value for this
parameter. This is detailed in the following section.
â?¢ String Name â' The internal name of this parameter as used n the report
itself. It does not represent a value suitable for display in the user
interface. Instead, use the property â'Promptâ', which is described below.
â?¢ Bool Nullable â' Indicates that a valid value for this parameter can be
null. A combination of Nullable and AllowBlank (both are true) indicate that
this parameter is â'optionalâ' (a user need not specify a value).
â?¢ String NullableSpecified â' Indicates whether Nullable (above) is
indeterminate at runtime (if false). If it makes you feel any better, you
may include this property (along with Nullable and AllowBlank) when
determining whether a given parameter is optional or not. Our stored
procedure standards are such that every SPROC should be written as if all
parameters (with the exception of MemberID) are optional, so you shouldnâ't
encounter any runtime errors.
â?¢ String Prompt â' A string value that names the parameter accordingly.
Unlike â'Nameâ' (above) this value is suitable for display in the user
interface and should be used to label the control accordingly.
â?¢ Bool PromptUser â' This Boolean indicates whether a given parameter should
appear in the dialog. This value is determined at design time and will be
false if and only if there is an empty string defined for the â'Prompt:â' text
in the â'Report Parametersâ' dialog.
â?¢ Bool PromptUserSpecified â' Determines whether PromptUser (above) is
indeterminate at runtime.
â?¢ ETParameterStateEnum State â' This property reports the state of this
parameter. The enumeration is as follows: HasValidValue, MissingValidValue,
HasOutstandingDependencies, or DynamicValuesUnavailable. At first blush it
would appear as if this property can be used to determine whether nor not to
present a given parameter in the dialog (State = MissingValidValue). If a
parameter has a default specified for it, this property will have a State of
HasValidValue although the user has made no entry. However, using this
property to validate a parameter value, through a subsequent call to
GetReportParameters is perfectly allowable.
â?¢ Bool StateSpecified â' Indicates whether State (above) has a value based on
the most recent call to GetReportParameters().
â?¢ ETParameterTypeEnum Type â' Indicates the underlying type of the parameter
and can be one of: Boolean, DateTime, Float, Integer, or String. You will
use this property to determine the control type (and perhaps additional
formatting) of the control you will add to the page. Note: when working with
the Reporting Service component you will always use the string representation
of a parameter value. This string representation is translated under the
covers by SQL Reporting Services to its native type.
â?¢ TypeSpecified â' Indicates whether Type (above) is indeterminate at runtime.
â?¢ ETValidValues ValidValues â' This collection if not null is a list of valid
value for this parameter. The contents of this collection is determined by a
developer at design time. You may use the collection to only to validate a
value specified by a user but you should use the contents to populate a
dropdown list from which a user may select. The most powerful feature of
this property is its ability to support a lookup. The type ETValidValue is a
name/value pair, so for example instead of prompting the user to enter an
email ID, you can populate the dropdown list with email names and then assign
the associated email ID to the ETReportParameterValues collection for
submission to the componentâ's RenderReport method.
â?¢ Bool ValidValuesQueryBased â' Indicates whether ValidValues (above) are the
result of a static list (false) or a database query (true).
â?¢ Bool ValidValuesQueryBasedSpecified â' Indicates whether
ValidValuesQueryBased (above) is indeterminate at runtime (false).
The intent is for the user interface to use this collection to draw a page
containing user prompts and appropriate controls such that the user can
specify values as required. The order of the parameters as they appear in
the collection is the order in which they are displayed in the â'Report
Parametersâ' dialog in the IDE. In other words, the developer has the
opportunity at design time to specify the order in which they appear in the
user dialog (presuming a two column Prompt â' Value layout).
It is suggested that the user interface enumerate over this collection and
create controls according to the values of the collectionâ's properties. The
following steps serve to illustrate this approach:
1. Look only for parameters whose â'PromptUserâ' property is true. (Those
with a PromptUser property of false are used for other purposes and will
ALWAYS have a value resulting from a default value specification.)
2. Look for parameters whoâ's Dependencies are not null. If you find any,
this is indicative of a situation where the parameterâ's DefaultValues or
ValidValues are query based and that query requires an input parameter. You
will need to consult the array elements, take the name of each parameter
contained in an element, and use the following logic to establish a page of
controls to obtain values for the dependent parameters. Once you have these
values, you will make a subsequent call to GetReportParameters() providing
the values you have collected thus far. Then, you will construct a page of
[now] independent parameters seeking their values. Once you have these
values you may proceed with RenderReport(). Note: there is at most one level
of indirection here.
3. Use the â'Promptâ' propertyâ's value as the label text for your control.
(Depending on how you do this, you may have to wait until after the control
type is determined below.)
4. If ValidValues has zero elements, determine the type of control to use
from the Type property.
a. If Type is â'Stringâ' we can presume a Textbox control.
b. If Type is â'DateTimeâ' we can presume a Calendar control.
c. If Type is â'Booleanâ' we can presume a CheckBox control.
d. If Type is â'Integerâ' we can presume a Textbox control with integer
formatting.
e. If Type is â'Floatâ' we can presume a Textbox control with floating point
decimal formatting.
5. If ValidValues has one element, this is indicative of a rare but
potential condition where there is one and only one valid value. Since there
is only one you may consider not prompting the user at all and just use the
value supplied.
6. If ValidValues has two or more elements, the report developer is
indicating that he/she intends on the user picking a value from a list, you
should use a dropdown list control.
a. Determine if the Label property of the ETValidValue collection items is
blank.
i. If Label is NOT blank, then we have a lookup; we will display a
â'externalâ' value such as List Name, and use its corresponding â'internalâ'
value (List ID) as the parameter value. Populate the list itemâ's â'textâ'
property with the Label property of the ETValidValue and the list itemâ's
â'valueâ' property with the â'Valueâ' property of ETValidValue.
ii. If Label IS blank, then we have a simple lookup (such as a list of
dates). Populate the list boxâ's list itemâ's properties â'textâ' and â'valueâ'
with the â'Valueâ' property of ETValidValue.
7. If DefaultValues contains 1 element then use the assign the value of
DefaultValues[0] to the control in accordance with the controlâ's type, thus
establishing the default value for the control.
Future Consideration
While the design stated above is certainly dynamic, it not does not consider
all of the possibilities. We have not yet entertained for example that
report content is ordered in accordance with a runtime parameter. If it was,
we would simply gather the parameter like any other; however, it would be
used in an ORDER BY clause as opposed to a WHERE clause. Although similar in
nature, we would most likely want to visually separate those parameters that
manage scope versus those that manage ordering. SQL Reporting Services does
not directly support that segregation (through an internal property of a
report parameter) so if desires, we would have to implement our own
methodology.
Date ranges (or any range parameters for that matter) are quite common;
however we have no support for multiple ranges specified against a single
value. Should we choose to support this feature we would need to determine a
way to allow the user to enter multiple ranges, pass them to the report
renderer, which passes them along to the stored procedure that delivers the
result set. There is some hope for this support from within SQL Reporting
Services through the MultiValue property of its parameters collection but we
would still need to work out a method by which these values are passed on to
the SPROCS.
For now, user are limited to specifying a single value or (through using two
report parameters) a range of values. I think it will be necessary for us to
support multi value parameters in conjunction with the IN operator of T-SQL.
Quite frequently a user is going to want a report that contains more than
â'one itemâ'. How we will do this is not yet known as SQL Reporting Services
does not yet appear to support Multi-Value.
Another consideration, which is similar in nature to MultiValue is an
â'all-butâ' paradigm. Rather than constructing the â'positiveâ' case using
parameter values (range or multivalue) the user is constructing a WHERE NOT
clause.
In a report scenario that involves a heavy comparison by date, users
frequently desire what I refer to as date plus or minus where the user is
specifying a date and then a span of time either before or after the
specified date. This is nothing more than an alternate date range so we
really have no issue regarding our reports or stored procedures but rather it
becomes an issue for the user-interface to illustrate the parameters in this
fashion. This too is probably not too much of an endeavor however we have no
way at present for the report developer to indicate the type of control to
gather a parameter value by nor do we have support to communicate this fact
to the consumer through the GetParameters() method. Of course, one could
construct the MOADC (mother of all date controls) that allowed the user to
specify a range with a beginning and ending date or a date plus or minus a
day, week, month, quarter, half-year, year etc. but then we would have no way
to associate two date parameters into a range either.
"Dev Main" wrote:
> I found many hints that customer parameter interfaces are a possible solution
> for several problems.
> But I can't find one piece about how this can be done. Not in the
> documentation, not in this news group and not in google. I'm really investet
> many time, but don't find anything.
> Can one give me a hint where I can find information about this?|||@. Herman K: Thank you very much! This is real help and furthermore an
valuable documentation.
My misunderstanding was, that I thought I can replace the parameter section
in a report with something like a custom control. But this seems to be
impossible.
"Herman K" wrote:
> Here's the design doc I wrote for ours:
> Reporting Phase II Design
> A Dynamic User Interface for Report Navigation and Runtime Parameter Gathering
> Introduction
> Having a dynamic user interface to present reports to users and gather
> runtime parameter values originates from a singular thought: report
> customization. We desire that reports be developed by end user
> administrators in addition to professional services and in-house developers.
> By providing this support we must provide a means by which a report may be
> deployed into the production environment and then appear in the application
> without any user interface development activity on our part. The best way to
> support this desire is to build our user interface such that it does not rely
> on specific knowledge of any one report but rather is built with awareness
> that reports are present and that they gather runtime parameters. In this
> fashion, report developers are free to upload new reports or modify existing
> reports independent of a release cycle. The following sections will
> illustrate how we may expose this capability from within our application.
> Report Navigation
> The first challenge regarding our dynamic user interface is to present the
> reports so a user may choose among them. Our current user interface design
> calls for a hierarchical folder paradigm that contains and organizes all
> reports. It begins with a root node labeled â'My Reportsâ' that appears in the
> â'Trackingâ' page. This folder can contain any number of sub folders, which
> serve to categorize all reports and will initially contain the folders â'Email
> Reportsâ', â'Subscriber Reportsâ', â'Admin Reportsâ', and â'Custom Reportsâ'. It is
> not clear whether the folder â'My Reportsâ' will ever contain reports
> themselves and it is presumed these first level folders will contain reports
> and perhaps additional folders.
> The Report Services component, created to serve the user interface and other
> consumers, provides a method GetReports() that takes no input parameters and
> returns to the caller a data table containing all of the reports that the
> calling user is authorized to view. This data table will also contain the
> hierarchy of folders such that the consumer may illustrate reports in
> accordance with the prescribed hierarchy.
> The data table returned by GetReports() will contain the following items:
> â?¢ ID â' a unique identifier (GUID) that uniquely identifies the report item
> â?¢ Type â' an enumeration describing the nature of the report item. This is
> supported by the public enum ReportItemType containing Folder and Report
> (this may be expanded to include other types as demand warrants.
> â?¢ Name â' the plain text name of the report, suitable for illustration in the
> user interface. (This value originates from the â'nameâ' property of a report
> on the report server, which gets its value from the â'nameâ' property of the
> report itself, which is established at design time. It is therefore
> necessary for developers to assign a meaningful name at design time,
> presumably assigned by Product Management at the time of envisioning.)
> â?¢ Description â' A plain text description that serves to illustrate the
> content/functionality of the report in greater detail than â'Nameâ' (above).
> It may be useful perhaps as tool-tip text given the folder paradigm. This
> value originates from the â'descriptionâ' property of the report on the report
> server, which gets its value from the â'descriptionâ' property of the report
> itself, which is established at design time. It is therefore necessary for
> developers to assign a meaningful value during the development process;
> presumably this text will originate from Product Management.
> â?¢ Path â' A text string that contains the fully qualified path to the report
> on the report server. This item equally serves as a unique identifier for a
> report, describes the location of the report within the folder hierarchy, and
> is used as in input parameter by the RenderReport() method that identifies
> the report to be rendered.
> â?¢ Parent â' a unique identifier (GUID) that points to a report item that
> contains this report item. If null, it indicates the root of the hierarchy.
> It is presumed that our user interface will use this data table to construct
> the tree view that is illustrated in the user interface document. Further,
> all attempts will be made to provide all of the information desired by the
> user interface team and in a structure that is most suitable to their
> consumption.
> Runtime Parameter Value Gathering
> While it is necessary to provide a listing of reports suitable for display,
> the greater challenge comes in gathering the runtime parameter values that
> will be used by the report to scope its contents and perhaps drive the order
> in which result rows appear. To support consumers in the gathering of
> runtime parameter values, the Report Services component exposes a method
> GetReportParameters(). This method takes one parameter (string reportPath)
> that is acquired through the â'Pathâ' item returned by GetReports() as
> described above and returns a collection containing the items described
> below. (Note: the values these items contain are a function of developer
> action at design time, primarily via the settings made in the â'Report
> Parametersâ' dialog of the IDE.
> â?¢ Bool AllowBlank â' Determines whether a parameter value can be an empty
> string. This applies only to parameters of type string (identified by
> ETParameterTypeEnum Type, below)
> â?¢ String[] DefaultValues â' A string array that typically contains one
> element whose value indicates the default for this parameter. The user
> interface should populate the appropriate control with this value. (Note: in
> the future we may choose to support multi-value parameters; perhaps for use
> with the SQL IN operator. If and when this becomes true, the potential for
> multiple default values becomes true, hence it is declared as an array.)
> â?¢ Bool DefaultValuesQueryBased â' Indicates whether DefaultValues (above) is
> populated by an expression (false) or by a database query (true).
> â?¢ Bool DefaultValuesQueryBasedSpecified â' Indicates whether a determination
> can be made at runtime to assign DefaultValuesQueryBased a value. If this
> value is false the origin of the default value(s) for this parameter is
> indeterminate.
> â?¢ String[] Dependencies â' If this parameterâ's default or valid values are
> based on a parameterized query and that queryâ's parameter(s) have
> indeterminate values, then this array will contain the names of those
> parameters. In other words, if this property is not null, you will need to
> gather the values for the parameters named in this property, make a
> subsequent call to GetReportParameters, and then gather the value for this
> parameter. This is detailed in the following section.
> â?¢ String Name â' The internal name of this parameter as used n the report
> itself. It does not represent a value suitable for display in the user
> interface. Instead, use the property â'Promptâ', which is described below.
> â?¢ Bool Nullable â' Indicates that a valid value for this parameter can be
> null. A combination of Nullable and AllowBlank (both are true) indicate that
> this parameter is â'optionalâ' (a user need not specify a value).
> â?¢ String NullableSpecified â' Indicates whether Nullable (above) is
> indeterminate at runtime (if false). If it makes you feel any better, you
> may include this property (along with Nullable and AllowBlank) when
> determining whether a given parameter is optional or not. Our stored
> procedure standards are such that every SPROC should be written as if all
> parameters (with the exception of MemberID) are optional, so you shouldnâ't
> encounter any runtime errors.
> â?¢ String Prompt â' A string value that names the parameter accordingly.
> Unlike â'Nameâ' (above) this value is suitable for display in the user
> interface and should be used to label the control accordingly.
> â?¢ Bool PromptUser â' This Boolean indicates whether a given parameter should
> appear in the dialog. This value is determined at design time and will be
> false if and only if there is an empty string defined for the â'Prompt:â' text
> in the â'Report Parametersâ' dialog.
> â?¢ Bool PromptUserSpecified â' Determines whether PromptUser (above) is
> indeterminate at runtime.
> â?¢ ETParameterStateEnum State â' This property reports the state of this
> parameter. The enumeration is as follows: HasValidValue, MissingValidValue,
> HasOutstandingDependencies, or DynamicValuesUnavailable. At first blush it
> would appear as if this property can be used to determine whether nor not to
> present a given parameter in the dialog (State = MissingValidValue). If a
> parameter has a default specified for it, this property will have a State of
> HasValidValue although the user has made no entry. However, using this
> property to validate a parameter value, through a subsequent call to
> GetReportParameters is perfectly allowable.
> â?¢ Bool StateSpecified â' Indicates whether State (above) has a value based on
> the most recent call to GetReportParameters().
> â?¢ ETParameterTypeEnum Type â' Indicates the underlying type of the parameter
> and can be one of: Boolean, DateTime, Float, Integer, or String. You will
> use this property to determine the control type (and perhaps additional
> formatting) of the control you will add to the page. Note: when working with
> the Reporting Service component you will always use the string representation
> of a parameter value. This string representation is translated under the
> covers by SQL Reporting Services to its native type.
> â?¢ TypeSpecified â' Indicates whether Type (above) is indeterminate at runtime.
> â?¢ ETValidValues ValidValues â' This collection if not null is a list of valid
> value for this parameter. The contents of this collection is determined by a
> developer at design time. You may use the collection to only to validate a
> value specified by a user but you should use the contents to populate a
> dropdown list from which a user may select. The most powerful feature of
> this property is its ability to support a lookup. The type ETValidValue is a
> name/value pair, so for example instead of prompting the user to enter an
> email ID, you can populate the dropdown list with email names and then assign
> the associated email ID to the ETReportParameterValues collection for
> submission to the componentâ's RenderReport method.
> â?¢ Bool ValidValuesQueryBased â' Indicates whether ValidValues (above) are the
> result of a static list (false) or a database query (true).
> â?¢ Bool ValidValuesQueryBasedSpecified â' Indicates whether
> ValidValuesQueryBased (above) is indeterminate at runtime (false).
> The intent is for the user interface to use this collection to draw a page
> containing user prompts and appropriate controls such that the user can
> specify values as required. The order of the parameters as they appear in
> the collection is the order in which they are displayed in the â'Report
> Parametersâ' dialog in the IDE. In other words, the developer has the
> opportunity at design time to specify the order in which they appear in the
> user dialog (presuming a two column Prompt â' Value layout).
> It is suggested that the user interface enumerate over this collection and
> create controls according to the values of the collectionâ's properties. The
> following steps serve to illustrate this approach:
> 1. Look only for parameters whose â'PromptUserâ' property is true. (Those
> with a PromptUser property of false are used for other purposes and will
> ALWAYS have a value resulting from a default value specification.)
> 2. Look for parameters whoâ's Dependencies are not null. If you find any,
> this is indicative of a situation where the parameterâ's DefaultValues or
> ValidValues are query based and that query requires an input parameter. You
> will need to consult the array elements, take the name of each parameter
> contained in an element, and use the following logic to establish a page of
> controls to obtain values for the dependent parameters. Once you have these
> values, you will make a subsequent call to GetReportParameters() providing
> the values you have collected thus far. Then, you will construct a page of
> [now] independent parameters seeking their values. Once you have these
> values you may proceed with RenderReport(). Note: there is at most one level
> of indirection here.
> 3. Use the â'Promptâ' propertyâ's value as the label text for your control.
> (Depending on how you do this, you may have to wait until after the control
> type is determined below.)
> 4. If ValidValues has zero elements, determine the type of control to use
> from the Type property.
> a. If Type is â'Stringâ' we can presume a Textbox control.
> b. If Type is â'DateTimeâ' we can presume a Calendar control.
> c. If Type is â'Booleanâ' we can presume a CheckBox control.
> d. If Type is â'Integerâ' we can presume a Textbox control with integer
> formatting.
> e. If Type is â'Floatâ' we can presume a Textbox control with floating point
> decimal formatting.
> 5. If ValidValues has one element, this is indicative of a rare but
> potential condition where there is one and only one valid value. Since there
> is only one you may consider not prompting the user at all and just use the
> value supplied.
> 6. If ValidValues has two or more elements, the report developer is
> indicating that he/she intends on the user picking a value from a list, you
> should use a dropdown list control.
> a. Determine if the Label property of the ETValidValue collection items is
> blank.
> i. If Label is NOT blank, then we have a lookup; we will display a
> â'externalâ' value such as List Name, and use its corresponding â'internalâ'
> value (List ID) as the parameter value. Populate the list itemâ's â'textâ'
> property with the Label property of the ETValidValue and the list itemâ's
> â'valueâ' property with the â'Valueâ' property of ETValidValue.
> ii. If Label IS blank, then we have a simple lookup (such as a list of
> dates). Populate the list boxâ's list itemâ's properties â'textâ' and â'valueâ'
> with the â'Valueâ' property of ETValidValue.
> 7. If DefaultValues contains 1 element then use the assign the value of
> DefaultValues[0] to the control in accordance with the controlâ's type, thus
> establishing the default value for the control.
> Future Consideration
> While the design stated above is certainly dynamic, it not does not consider
> all of the possibilities. We have not yet entertained for example that
> report content is ordered in accordance with a runtime parameter. If it was,
> we would simply gather the parameter like any other; however, it would be
> used in an ORDER BY clause as opposed to a WHERE clause. Although similar in
> nature, we would most likely want to visually separate those parameters that
> manage scope versus those that manage ordering. SQL Reporting Services does
> not directly support that segregation (through an internal property of a
> report parameter) so if desires, we would have to implement our own
> methodology.
> Date ranges (or any range parameters for that matter) are quite common;
> however we have no support for multiple ranges specified against a single
> value. Should we choose to support this feature we would need to determine a
> way to allow the user to enter multiple ranges, pass them to the report
> renderer, which passes them along to the stored procedure that delivers the
> result set. There is some hope for this support from within SQL Reporting
> Services through the MultiValue property of its parameters collection but we
> would still need to work out a method by which these values are passed on to
> the SPROCS.
> For now, user are limited to specifying a single value or (through using two
> report parameters) a range of values. I think it will be necessary for us to
> support multi value parameters in conjunction with the IN operator of T-SQL.
> Quite frequently a user is going to want a report that contains more than
> â'one itemâ'. How we will do this is not yet known as SQL Reporting Services
> does not yet appear to support Multi-Value.
> Another consideration, which is similar in nature to MultiValue is an
> â'all-butâ' paradigm. Rather than constructing the â'positiveâ' case using
> parameter values (range or multivalue) the user is constructing a WHERE NOT
> clause.
> In a report scenario that involves a heavy comparison by date, users
> frequently desire what I refer to as date plus or minus where the user is
> specifying a date and then a span of time either before or after the
> specified date. This is nothing more than an alternate date range so we
> really have no issue regarding our reports or stored procedures but rather it
> becomes an issue for the user-interface to illustrate the parameters in this
> fashion. This too is probably not too much of an endeavor however we have no
> way at present for the report developer to indicate the type of control to
> gather a parameter value by nor do we have support to communicate this fact
> to the consumer through the GetParameters() method. Of course, one could
> construct the MOADC (mother of all date controls) that allowed the user to
> specify a range with a beginning and ending date or a date plus or minus a
> day, week, month, quarter, half-year, year etc. but then we would have no way
> to associate two date parameters into a range either.
>
> "Dev Main" wrote:
> > I found many hints that customer parameter interfaces are a possible solution
> > for several problems.
> >
> > But I can't find one piece about how this can be done. Not in the
> > documentation, not in this news group and not in google. I'm really investet
> > many time, but don't find anything.
> >
> > Can one give me a hint where I can find information about this?|||Read Chapter 11 of the "Hitchhiker's Guide to SQL Server 2000 Reporting
Services" book.
"Dev Main" wrote:
> @. Herman K: Thank you very much! This is real help and furthermore an
> valuable documentation.
> My misunderstanding was, that I thought I can replace the parameter section
> in a report with something like a custom control. But this seems to be
> impossible.
> "Herman K" wrote:
> > Here's the design doc I wrote for ours:
> >
> > Reporting Phase II Design
> > A Dynamic User Interface for Report Navigation and Runtime Parameter Gathering
> > Introduction
> > Having a dynamic user interface to present reports to users and gather
> > runtime parameter values originates from a singular thought: report
> > customization. We desire that reports be developed by end user
> > administrators in addition to professional services and in-house developers.
> > By providing this support we must provide a means by which a report may be
> > deployed into the production environment and then appear in the application
> > without any user interface development activity on our part. The best way to
> > support this desire is to build our user interface such that it does not rely
> > on specific knowledge of any one report but rather is built with awareness
> > that reports are present and that they gather runtime parameters. In this
> > fashion, report developers are free to upload new reports or modify existing
> > reports independent of a release cycle. The following sections will
> > illustrate how we may expose this capability from within our application.
> > Report Navigation
> > The first challenge regarding our dynamic user interface is to present the
> > reports so a user may choose among them. Our current user interface design
> > calls for a hierarchical folder paradigm that contains and organizes all
> > reports. It begins with a root node labeled â'My Reportsâ' that appears in the
> > â'Trackingâ' page. This folder can contain any number of sub folders, which
> > serve to categorize all reports and will initially contain the folders â'Email
> > Reportsâ', â'Subscriber Reportsâ', â'Admin Reportsâ', and â'Custom Reportsâ'. It is
> > not clear whether the folder â'My Reportsâ' will ever contain reports
> > themselves and it is presumed these first level folders will contain reports
> > and perhaps additional folders.
> >
> > The Report Services component, created to serve the user interface and other
> > consumers, provides a method GetReports() that takes no input parameters and
> > returns to the caller a data table containing all of the reports that the
> > calling user is authorized to view. This data table will also contain the
> > hierarchy of folders such that the consumer may illustrate reports in
> > accordance with the prescribed hierarchy.
> >
> > The data table returned by GetReports() will contain the following items:
> > â?¢ ID â' a unique identifier (GUID) that uniquely identifies the report item
> > â?¢ Type â' an enumeration describing the nature of the report item. This is
> > supported by the public enum ReportItemType containing Folder and Report
> > (this may be expanded to include other types as demand warrants.
> > â?¢ Name â' the plain text name of the report, suitable for illustration in the
> > user interface. (This value originates from the â'nameâ' property of a report
> > on the report server, which gets its value from the â'nameâ' property of the
> > report itself, which is established at design time. It is therefore
> > necessary for developers to assign a meaningful name at design time,
> > presumably assigned by Product Management at the time of envisioning.)
> > â?¢ Description â' A plain text description that serves to illustrate the
> > content/functionality of the report in greater detail than â'Nameâ' (above).
> > It may be useful perhaps as tool-tip text given the folder paradigm. This
> > value originates from the â'descriptionâ' property of the report on the report
> > server, which gets its value from the â'descriptionâ' property of the report
> > itself, which is established at design time. It is therefore necessary for
> > developers to assign a meaningful value during the development process;
> > presumably this text will originate from Product Management.
> > â?¢ Path â' A text string that contains the fully qualified path to the report
> > on the report server. This item equally serves as a unique identifier for a
> > report, describes the location of the report within the folder hierarchy, and
> > is used as in input parameter by the RenderReport() method that identifies
> > the report to be rendered.
> > â?¢ Parent â' a unique identifier (GUID) that points to a report item that
> > contains this report item. If null, it indicates the root of the hierarchy.
> >
> > It is presumed that our user interface will use this data table to construct
> > the tree view that is illustrated in the user interface document. Further,
> > all attempts will be made to provide all of the information desired by the
> > user interface team and in a structure that is most suitable to their
> > consumption.
> > Runtime Parameter Value Gathering
> > While it is necessary to provide a listing of reports suitable for display,
> > the greater challenge comes in gathering the runtime parameter values that
> > will be used by the report to scope its contents and perhaps drive the order
> > in which result rows appear. To support consumers in the gathering of
> > runtime parameter values, the Report Services component exposes a method
> > GetReportParameters(). This method takes one parameter (string reportPath)
> > that is acquired through the â'Pathâ' item returned by GetReports() as
> > described above and returns a collection containing the items described
> > below. (Note: the values these items contain are a function of developer
> > action at design time, primarily via the settings made in the â'Report
> > Parametersâ' dialog of the IDE.
> > â?¢ Bool AllowBlank â' Determines whether a parameter value can be an empty
> > string. This applies only to parameters of type string (identified by
> > ETParameterTypeEnum Type, below)
> > â?¢ String[] DefaultValues â' A string array that typically contains one
> > element whose value indicates the default for this parameter. The user
> > interface should populate the appropriate control with this value. (Note: in
> > the future we may choose to support multi-value parameters; perhaps for use
> > with the SQL IN operator. If and when this becomes true, the potential for
> > multiple default values becomes true, hence it is declared as an array.)
> > â?¢ Bool DefaultValuesQueryBased â' Indicates whether DefaultValues (above) is
> > populated by an expression (false) or by a database query (true).
> > â?¢ Bool DefaultValuesQueryBasedSpecified â' Indicates whether a determination
> > can be made at runtime to assign DefaultValuesQueryBased a value. If this
> > value is false the origin of the default value(s) for this parameter is
> > indeterminate.
> > â?¢ String[] Dependencies â' If this parameterâ's default or valid values are
> > based on a parameterized query and that queryâ's parameter(s) have
> > indeterminate values, then this array will contain the names of those
> > parameters. In other words, if this property is not null, you will need to
> > gather the values for the parameters named in this property, make a
> > subsequent call to GetReportParameters, and then gather the value for this
> > parameter. This is detailed in the following section.
> > â?¢ String Name â' The internal name of this parameter as used n the report
> > itself. It does not represent a value suitable for display in the user
> > interface. Instead, use the property â'Promptâ', which is described below.
> > â?¢ Bool Nullable â' Indicates that a valid value for this parameter can be
> > null. A combination of Nullable and AllowBlank (both are true) indicate that
> > this parameter is â'optionalâ' (a user need not specify a value).
> > â?¢ String NullableSpecified â' Indicates whether Nullable (above) is
> > indeterminate at runtime (if false). If it makes you feel any better, you
> > may include this property (along with Nullable and AllowBlank) when
> > determining whether a given parameter is optional or not. Our stored
> > procedure standards are such that every SPROC should be written as if all
> > parameters (with the exception of MemberID) are optional, so you shouldnâ't
> > encounter any runtime errors.
> > â?¢ String Prompt â' A string value that names the parameter accordingly.
> > Unlike â'Nameâ' (above) this value is suitable for display in the user
> > interface and should be used to label the control accordingly.
> > â?¢ Bool PromptUser â' This Boolean indicates whether a given parameter should
> > appear in the dialog. This value is determined at design time and will be
> > false if and only if there is an empty string defined for the â'Prompt:â' text
> > in the â'Report Parametersâ' dialog.
> > â?¢ Bool PromptUserSpecified â' Determines whether PromptUser (above) is
> > indeterminate at runtime.
> > â?¢ ETParameterStateEnum State â' This property reports the state of this
> > parameter. The enumeration is as follows: HasValidValue, MissingValidValue,
> > HasOutstandingDependencies, or DynamicValuesUnavailable. At first blush it
> > would appear as if this property can be used to determine whether nor not to
> > present a given parameter in the dialog (State = MissingValidValue). If a
> > parameter has a default specified for it, this property will have a State of
> > HasValidValue although the user has made no entry. However, using this
> > property to validate a parameter value, through a subsequent call to
> > GetReportParameters is perfectly allowable.
> > â?¢ Bool StateSpecified â' Indicates whether State (above) has a value based on
> > the most recent call to GetReportParameters().
> > â?¢ ETParameterTypeEnum Type â' Indicates the underlying type of the parameter
> > and can be one of: Boolean, DateTime, Float, Integer, or String. You will
> > use this property to determine the control type (and perhaps additional
> > formatting) of the control you will add to the page. Note: when working with
> > the Reporting Service component you will always use the string representation
> > of a parameter value. This string representation is translated under the
> > covers by SQL Reporting Services to its native type.
> > â?¢ TypeSpecified â' Indicates whether Type (above) is indeterminate at runtime.
> > â?¢ ETValidValues ValidValues â' This collection if not null is a list of valid
> > value for this parameter. The contents of this collection is determined by a
> > developer at design time. You may use the collection to only to validate a
> > value specified by a user but you should use the contents to populate a
> > dropdown list from which a user may select. The most powerful feature of
> > this property is its ability to support a lookup. The type ETValidValue is a
> > name/value pair, so for example instead of prompting the user to enter an
> > email ID, you can populate the dropdown list with email names and then assign
> > the associated email ID to the ETReportParameterValues collection for
> > submission to the componentâ's RenderReport method.
> > â?¢ Bool ValidValuesQueryBased â' Indicates whether ValidValues (above) are the
> > result of a static list (false) or a database query (true).
> > â?¢ Bool ValidValuesQueryBasedSpecified â' Indicates whether
> > ValidValuesQueryBased (above) is indeterminate at runtime (false).
> >
> > The intent is for the user interface to use this collection to draw a page
> > containing user prompts and appropriate controls such that the user can
> > specify values as required. The order of the parameters as they appear in
> > the collection is the order in which they are displayed in the â'Report
> > Parametersâ' dialog in the IDE. In other words, the developer has the
> > opportunity at design time to specify the order in which they appear in the
> > user dialog (presuming a two column Prompt â' Value layout).
> >
> > It is suggested that the user interface enumerate over this collection and
> > create controls according to the values of the collectionâ's properties. The
> > following steps serve to illustrate this approach:
> > 1. Look only for parameters whose â'PromptUserâ' property is true. (Those
> > with a PromptUser property of false are used for other purposes and will
> > ALWAYS have a value resulting from a default value specification.)
> > 2. Look for parameters whoâ's Dependencies are not null. If you find any,
> > this is indicative of a situation where the parameterâ's DefaultValues or
> > ValidValues are query based and that query requires an input parameter. You
> > will need to consult the array elements, take the name of each parameter
> > contained in an element, and use the following logic to establish a page of
> > controls to obtain values for the dependent parameters. Once you have these
> > values, you will make a subsequent call to GetReportParameters() providing
> > the values you have collected thus far. Then, you will construct a page of
> > [now] independent parameters seeking their values. Once you have these
> > values you may proceed with RenderReport(). Note: there is at most one level
> > of indirection here.
> > 3. Use the â'Promptâ' propertyâ's value as the label text for your control.
> > (Depending on how you do this, you may have to wait until after the control
> > type is determined below.)
> > 4. If ValidValues has zero elements, determine the type of control to use
> > from the Type property.
> > a. If Type is â'Stringâ' we can presume a Textbox control.
> > b. If Type is â'DateTimeâ' we can presume a Calendar control.
> > c. If Type is â'Booleanâ' we can presume a CheckBox control.
> > d. If Type is â'Integerâ' we can presume a Textbox control with integer
> > formatting.
> > e. If Type is â'Floatâ' we can presume a Textbox control with floating point
> > decimal formatting.
> > 5. If ValidValues has one element, this is indicative of a rare but
> > potential condition where there is one and only one valid value. Since there
> > is only one you may consider not prompting the user at all and just use the
> > value supplied.
> > 6. If ValidValues has two or more elements, the report developer is
> > indicating that he/she intends on the user picking a value from a list, you
> > should use a dropdown list control.
> > a. Determine if the Label property of the ETValidValue collection items is
> > blank.
> > i. If Label is NOT blank, then we have a lookup; we will display a
> > â'externalâ' value such as List Name, and use its corresponding â'internalâ'
> > value (List ID) as the parameter value. Populate the list itemâ's â'textâ'
> > property with the Label property of the ETValidValue and the list itemâ's
> > â'valueâ' property with the â'Valueâ' property of ETValidValue.
> > ii. If Label IS blank, then we have a simple lookup (such as a list of
> > dates). Populate the list boxâ's list itemâ's properties â'textâ' and â'valueâ'
> > with the â'Valueâ' property of ETValidValue.
> > 7. If DefaultValues contains 1 element then use the assign the value of
> > DefaultValues[0] to the control in accordance with the controlâ's type, thus
> > establishing the default value for the control.
> > Future Consideration
> > While the design stated above is certainly dynamic, it not does not consider
> > all of the possibilities. We have not yet entertained for example that
> > report content is ordered in accordance with a runtime parameter. If it was,
> > we would simply gather the parameter like any other; however, it would be
> > used in an ORDER BY clause as opposed to a WHERE clause. Although similar in
> > nature, we would most likely want to visually separate those parameters that
> > manage scope versus those that manage ordering. SQL Reporting Services does
> > not directly support that segregation (through an internal property of a
> > report parameter) so if desires, we would have to implement our own
> > methodology.
> >
> > Date ranges (or any range parameters for that matter) are quite common;
> > however we have no support for multiple ranges specified against a single
> > value. Should we choose to support this feature we would need to determine a
> > way to allow the user to enter multiple ranges, pass them to the report
> > renderer, which passes them along to the stored procedure that delivers the
> > result set. There is some hope for this support from within SQL Reporting
> > Services through the MultiValue property of its parameters collection but we
> > would still need to work out a method by which these values are passed on to
> > the SPROCS.
> >
> > For now, user are limited to specifying a single value or (through using two
> > report parameters) a range of values. I think it will be necessary for us to
> > support multi value parameters in conjunction with the IN operator of T-SQL.
> > Quite frequently a user is going to want a report that contains more than
> > â'one itemâ'. How we will do this is not yet known as SQL Reporting Services
> > does not yet appear to support Multi-Value.
> >
> > Another consideration, which is similar in nature to MultiValue is an
> > â'all-butâ' paradigm. Rather than constructing the â'positiveâ' case using
> > parameter values (range or multivalue) the user is constructing a WHERE NOT
> > clause.
> >
> > In a report scenario that involves a heavy comparison by date, users
> > frequently desire what I refer to as date plus or minus where the user is
> > specifying a date and then a span of time either before or after the
> > specified date. This is nothing more than an alternate date range so we
> > really have no issue regarding our reports or stored procedures but rather it
> > becomes an issue for the user-interface to illustrate the parameters in this
> > fashion. This too is probably not too much of an endeavor however we have no
> > way at present for the report developer to indicate the type of control to
> > gather a parameter value by nor do we have support to communicate this fact
> > to the consumer through the GetParameters() method. Of course, one could
> > construct the MOADC (mother of all date controls) that allowed the user to
> > specify a range with a beginning and ending date or a date plus or minus a
> > day, week, month, quarter, half-year, year etc. but then we would have no way
> > to associate two date parameters into a range either.
> >
> >
> >
> > "Dev Main" wrote:
> >
> > > I found many hints that customer parameter interfaces are a possible solution
> > > for several problems.
> > >
> > > But I can't find one piece about how this can be done. Not in the
> > > documentation, not in this news group and not in google. I'm really investet
> > > many time, but don't find anything.
> > >
> > > Can one give me a hint where I can find information about this?
