Data Processing Extensions - Parameters ?

Hi,
I am submitting a report to be run via sending the reportname and some
parameters via the RS WebService. Behind this I have a DPE. This all works
fine when there are no parameters. when I do pass params though, I have a
problem.
Trouble is that I cannot seem to lay my hands on the passed parameter values
(I presume a collection ?) when in my implementation class of the IDbCommand,
IDbCommandAnalysis interfaces
How can I obtain these params in the DPE ?
any help would be appreciated, thanks,The IDbCommandAnalysis interface serves the orthogonal purpose. It lets the
Report Designer know about the parameters that your query expects. During
runtime the Report Server calls IDataParameterCollection.Add to pass the
parameter values. This is where you will load them, e.g.:
public int Add(IDataParameter value)
{
Trace.WriteLine("DataSet Extension: Add(IDataParameter value");
if (((DsDataParameter)value).ParameterName != null)
{
return base.Add(value);
}
else
throw new ArgumentException("parameter must be named");
}
public DsDataParameter GetByName(string parameterName)
{
DsDataParameter parameter = null;
IEnumerator enumerator = this.GetEnumerator();
while (enumerator.MoveNext())
{
DsDataParameter tempParameter = (DsDataParameter) enumerator.Current;
if (tempParameter.ParameterName == parameterName)
{
parameter = tempParameter;
break;
}
}
return parameter;
}
Then, in your DataReader implementation you can get the parameters:
DsDataParameter parameter = m_parameters.GetByName(Util.DATA_SOURCE)
as DsDataParameter;
You can get a complete working DPE sample here:
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
--
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"softgui" <softgui@.discussions.microsoft.com> wrote in message
news:FE3B6E92-4E95-42E7-8E1B-2868F9670CB0@.microsoft.com...
> Hi,
> I am submitting a report to be run via sending the reportname and some
> parameters via the RS WebService. Behind this I have a DPE. This all
works
> fine when there are no parameters. when I do pass params though, I have a
> problem.
> Trouble is that I cannot seem to lay my hands on the passed parameter
values
> (I presume a collection ?) when in my implementation class of the
IDbCommand,
> IDbCommandAnalysis interfaces
> How can I obtain these params in the DPE ?
> any help would be appreciated, thanks,
>|||Many thanks Teo.
- simon
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
news:%23OFyCBoqEHA.3712@.TK2MSFTNGP15.phx.gbl...
> The IDbCommandAnalysis interface serves the orthogonal purpose. It lets
> the
> Report Designer know about the parameters that your query expects. During
> runtime the Report Server calls IDataParameterCollection.Add to pass the
> parameter values. This is where you will load them, e.g.:
> public int Add(IDataParameter value)
> {
> Trace.WriteLine("DataSet Extension: Add(IDataParameter value");
> if (((DsDataParameter)value).ParameterName != null)
> {
> return base.Add(value);
> }
> else
> throw new ArgumentException("parameter must be named");
> }
> public DsDataParameter GetByName(string parameterName)
> {
> DsDataParameter parameter = null;
> IEnumerator enumerator = this.GetEnumerator();
> while (enumerator.MoveNext())
> {
> DsDataParameter tempParameter = (DsDataParameter) enumerator.Current;
> if (tempParameter.ParameterName == parameterName)
> {
> parameter = tempParameter;
> break;
> }
> }
> return parameter;
> }
> Then, in your DataReader implementation you can get the parameters:
> DsDataParameter parameter = m_parameters.GetByName(Util.DATA_SOURCE)
> as DsDataParameter;
> You can get a complete working DPE sample here:
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> --
> Hope this helps.
> ----
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
> "softgui" <softgui@.discussions.microsoft.com> wrote in message
> news:FE3B6E92-4E95-42E7-8E1B-2868F9670CB0@.microsoft.com...
>> Hi,
>> I am submitting a report to be run via sending the reportname and some
>> parameters via the RS WebService. Behind this I have a DPE. This all
> works
>> fine when there are no parameters. when I do pass params though, I have a
>> problem.
>> Trouble is that I cannot seem to lay my hands on the passed parameter
> values
>> (I presume a collection ?) when in my implementation class of the
> IDbCommand,
>> IDbCommandAnalysis interfaces
>> How can I obtain these params in the DPE ?
>> any help would be appreciated, thanks,
>>
>|||Hi Mr Softgui,
You can extend the DataParameter Extension to create your own types and then
cast them back into the (object) value property.
What do you think Teo?
Peter.
"softgui" wrote:
> Hi,
> I am submitting a report to be run via sending the reportname and some
> parameters via the RS WebService. Behind this I have a DPE. This all works
> fine when there are no parameters. when I do pass params though, I have a
> problem.
> Trouble is that I cannot seem to lay my hands on the passed parameter values
> (I presume a collection ?) when in my implementation class of the IDbCommand,
> IDbCommandAnalysis interfaces
> How can I obtain these params in the DPE ?
> any help would be appreciated, thanks,
>

Data processing extension and parameters

hi, I have implemented a data processing extension successfully and I have my
own query syntax (even implemented a custom query designer).
However, if I want to make use of the report parameter(s), it seems that I
have to append Parameters!{ParameterName} to my query.Value in order to get
the parameter value entered by the user.
Is this correct?
Thanks
jennyHello Jenny,
When you reference parameter value in report, ou could use
Parameters!{ParameterName}.value. However, as for parameter for data
processing extension of data source, it depends on how you implement this.
For data processing extension s for SQL server, you could use @.varaiable
for parameter in SQL query. You may want to try this with your data
procssing extension to test.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner 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.
--
| Thread-Topic: Data processing extension and parameters
| thread-index: AcXu+7x8ZtRJnUVcSr2mLOF0DypeJA==| X-WBNR-Posting-Host: 209.17.156.248
| From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
<yinjennytam@.newsgroup.nospam>
| Subject: Data processing extension and parameters
| Date: Mon, 21 Nov 2005 16:29:01 -0800
| Lines: 11
| Message-ID: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:63712
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| hi, I have implemented a data processing extension successfully and I
have my
| own query syntax (even implemented a custom query designer).
|
| However, if I want to make use of the report parameter(s), it seems that
I
| have to append Parameters!{ParameterName} to my query.Value in order to
get
| the parameter value entered by the user.
|
| Is this correct?
| Thanks
| jenny
|
||||Thanks for your reply. Yes if I append the Parameters!{ParameterName}.value
to my query, it does work and I get the right value selected by users.
However, my main concern is that the query used in my data extension has its
own syntax and I'm thinking of not using the generic query designer but my
own custom query designer (contains some text boxes and combo boxes for
example to generate a query from the user inputs). That is, to accomodate
parameters in reports, the user will need to append
Parameters!{ParameterName}.value to the query command text as well. Do I
understand this correctly?
BTW, is there a limit on number of parameters used in a report? I don't
think so. Correct?
Thanks a lot!
Jenny
"Peter Yang [MSFT]" wrote:
> Hello Jenny,
> When you reference parameter value in report, ou could use
> Parameters!{ParameterName}.value. However, as for parameter for data
> processing extension of data source, it depends on how you implement this.
> For data processing extension s for SQL server, you could use @.varaiable
> for parameter in SQL query. You may want to try this with your data
> procssing extension to test.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner 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.
> --
> | Thread-Topic: Data processing extension and parameters
> | thread-index: AcXu+7x8ZtRJnUVcSr2mLOF0DypeJA==> | X-WBNR-Posting-Host: 209.17.156.248
> | From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
> <yinjennytam@.newsgroup.nospam>
> | Subject: Data processing extension and parameters
> | Date: Mon, 21 Nov 2005 16:29:01 -0800
> | Lines: 11
> | Message-ID: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:63712
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | hi, I have implemented a data processing extension successfully and I
> have my
> | own query syntax (even implemented a custom query designer).
> |
> | However, if I want to make use of the report parameter(s), it seems that
> I
> | have to append Parameters!{ParameterName} to my query.Value in order to
> get
> | the parameter value entered by the user.
> |
> | Is this correct?
> | Thanks
> | jenny
> |
> |
>|||Hello Jenny,
Report rdl contains dataset section that describes data fields and query.
Dataset section may contain one or more datasets depending on the report
layout. Each dataset entry has query section with command text entry and
query parameters. This is also true for data processing extension query.
For example:
<Query>
<DataSourceName>AdventureWorks</DataSourceName>
<CommandText>SELECT C.FirstName + ' ' + C.LastName AS
Employee, DATEPART(Year, SOH.OrderDate) AS OrderYear,
DATEPART(Month, SOH.OrderDate) AS OrderMonthNum,
DATENAME(Month, SOH.OrderDate) AS OrderMonth, SUM(SOD.LineTotal) AS Sales
FROM Sales.SalesOrderHeader SOH INNER JOIN
Sales.SalesOrderDetail SOD ON SOH.SalesOrderID =SOD.SalesOrderID INNER JOIN
Sales.SalesPerson SP ON SOH.SalesPersonID = SP.SalesPersonID
INNER JOIN
HumanResources.Employee E ON SP.SalesPersonID = E.EmployeeID
INNER JOIN
Person.Contact C ON E.ContactID = C.ContactID
WHERE (DATEPART(Year, SOH.OrderDate) <= @.ReportYear - 1 OR
DATEPART(Year, SOH.OrderDate) = @.ReportYear AND DATEPART(Month,
SOH.OrderDate) <= @.ReportMonth) AND
(SOH.SalesPersonID = @.EmpID)
GROUP BY C.FirstName + ' ' + C.LastName, SOH.SalesPersonID,
DATEPART(Year, SOH.OrderDate),
DATEPART(Month, SOH.OrderDate), DATENAME(Month,
SOH.OrderDate)</CommandText>
<QueryParameters>
<QueryParameter Name="@.ReportYear">
<Value>=Parameters!ReportYear.Value</Value>
</QueryParameter>
<QueryParameter Name="@.ReportMonth">
<Value>=Parameters!ReportMonth.Value</Value>
</QueryParameter>
<QueryParameter Name="@.EmpID">
<Value>=Parameters!EmpID.Value</Value>
</QueryParameter>
</QueryParameters>
<Timeout>30</Timeout>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
No matter how you implement your data extension or cutom query designer,
the result rdl shall include the commandtext and parameters you want.
I did not find any limiatation in number of parameters in a report and I
think it is limited by server performance though.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner 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.
--
| Thread-Topic: Data processing extension and parameters
| thread-index: AcXvg6xl+JTGVcWuQlyb36CPBUDX8A==| X-WBNR-Posting-Host: 209.17.156.248
| From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
<yinjennytam@.newsgroup.nospam>
| References: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
<y78iI7y7FHA.832@.TK2MSFTNGXA02.phx.gbl>
| Subject: RE: Data processing extension and parameters
| Date: Tue, 22 Nov 2005 08:42:06 -0800
| Lines: 86
| Message-ID: <AED34EDB-530C-4060-A3FA-3F82AFD91536@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:63759
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Thanks for your reply. Yes if I append the
Parameters!{ParameterName}.value
| to my query, it does work and I get the right value selected by users.
|
| However, my main concern is that the query used in my data extension has
its
| own syntax and I'm thinking of not using the generic query designer but
my
| own custom query designer (contains some text boxes and combo boxes for
| example to generate a query from the user inputs). That is, to
accomodate
| parameters in reports, the user will need to append
| Parameters!{ParameterName}.value to the query command text as well. Do I
| understand this correctly?
|
| BTW, is there a limit on number of parameters used in a report? I don't
| think so. Correct?
|
| Thanks a lot!
| Jenny
|
|
| "Peter Yang [MSFT]" wrote:
|
| > Hello Jenny,
| >
| > When you reference parameter value in report, ou could use
| > Parameters!{ParameterName}.value. However, as for parameter for data
| > processing extension of data source, it depends on how you implement
this.
| > For data processing extension s for SQL server, you could use
@.varaiable
| > for parameter in SQL query. You may want to try this with your data
| > procssing extension to test.
| >
| > Best Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner 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.
| >
| > --
| > | Thread-Topic: Data processing extension and parameters
| > | thread-index: AcXu+7x8ZtRJnUVcSr2mLOF0DypeJA==| > | X-WBNR-Posting-Host: 209.17.156.248
| > | From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
| > <yinjennytam@.newsgroup.nospam>
| > | Subject: Data processing extension and parameters
| > | Date: Mon, 21 Nov 2005 16:29:01 -0800
| > | Lines: 11
| > | Message-ID: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > | Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.reportingsvcs:63712
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | hi, I have implemented a data processing extension successfully and I
| > have my
| > | own query syntax (even implemented a custom query designer).
| > |
| > | However, if I want to make use of the report parameter(s), it seems
that
| > I
| > | have to append Parameters!{ParameterName} to my query.Value in order
to
| > get
| > | the parameter value entered by the user.
| > |
| > | Is this correct?
| > | Thanks
| > | jenny
| > |
| > |
| >
| >
||||When I checked the rdl file that is generated, I couldn't find what you said
below, but I found the followings instead (where <ReportParameters> is at the
same level as <DataSources>):
<ReportParameters>
<ReportParameter Name="State">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>CA</Value>
</Values>
</DefaultValue>
<Prompt>State</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>CA</Value>
<Label>California</Label>
</ParameterValue>
<ParameterValue>
<Value>GA</Value>
<Label>Georgia </Label>
</ParameterValue>
<ParameterValue>
<Value>NY</Value>
<Label>New York</Label>
</ParameterValue>
<ParameterValue>
<Value>WA</Value>
<Label>Washington</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
</ReportParameters>
Within the <DataSets> element, I could only find <Query> with <CommandText>
as follows (some details omitted):
<CommandText>=" ... State = " & Parameters!State.Value</CommandText>
Have I done anything wrong? It seems to work for me so far. I did get the
right selected parameter value when processing the report though.
Thanks again for your help.
Jenny
"Peter Yang [MSFT]" wrote:
> Hello Jenny,
> Report rdl contains dataset section that describes data fields and query.
> Dataset section may contain one or more datasets depending on the report
> layout. Each dataset entry has query section with command text entry and
> query parameters. This is also true for data processing extension query.
> For example:
> <Query>
> <DataSourceName>AdventureWorks</DataSourceName>
> <CommandText>SELECT C.FirstName + ' ' + C.LastName AS
> Employee, DATEPART(Year, SOH.OrderDate) AS OrderYear,
> DATEPART(Month, SOH.OrderDate) AS OrderMonthNum,
> DATENAME(Month, SOH.OrderDate) AS OrderMonth, SUM(SOD.LineTotal) AS Sales
> FROM Sales.SalesOrderHeader SOH INNER JOIN
> Sales.SalesOrderDetail SOD ON SOH.SalesOrderID => SOD.SalesOrderID INNER JOIN
> Sales.SalesPerson SP ON SOH.SalesPersonID = SP.SalesPersonID
> INNER JOIN
> HumanResources.Employee E ON SP.SalesPersonID = E.EmployeeID
> INNER JOIN
> Person.Contact C ON E.ContactID = C.ContactID
> WHERE (DATEPART(Year, SOH.OrderDate) <= @.ReportYear - 1 OR
> DATEPART(Year, SOH.OrderDate) = @.ReportYear AND DATEPART(Month,
> SOH.OrderDate) <= @.ReportMonth) AND
> (SOH.SalesPersonID = @.EmpID)
> GROUP BY C.FirstName + ' ' + C.LastName, SOH.SalesPersonID,
> DATEPART(Year, SOH.OrderDate),
> DATEPART(Month, SOH.OrderDate), DATENAME(Month,
> SOH.OrderDate)</CommandText>
> <QueryParameters>
> <QueryParameter Name="@.ReportYear">
> <Value>=Parameters!ReportYear.Value</Value>
> </QueryParameter>
> <QueryParameter Name="@.ReportMonth">
> <Value>=Parameters!ReportMonth.Value</Value>
> </QueryParameter>
> <QueryParameter Name="@.EmpID">
> <Value>=Parameters!EmpID.Value</Value>
> </QueryParameter>
> </QueryParameters>
> <Timeout>30</Timeout>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> No matter how you implement your data extension or cutom query designer,
> the result rdl shall include the commandtext and parameters you want.
> I did not find any limiatation in number of parameters in a report and I
> think it is limited by server performance though.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner 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.
> --
> | Thread-Topic: Data processing extension and parameters
> | thread-index: AcXvg6xl+JTGVcWuQlyb36CPBUDX8A==> | X-WBNR-Posting-Host: 209.17.156.248
> | From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
> <yinjennytam@.newsgroup.nospam>
> | References: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
> <y78iI7y7FHA.832@.TK2MSFTNGXA02.phx.gbl>
> | Subject: RE: Data processing extension and parameters
> | Date: Tue, 22 Nov 2005 08:42:06 -0800
> | Lines: 86
> | Message-ID: <AED34EDB-530C-4060-A3FA-3F82AFD91536@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:63759
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Thanks for your reply. Yes if I append the
> Parameters!{ParameterName}.value
> | to my query, it does work and I get the right value selected by users.
> |
> | However, my main concern is that the query used in my data extension has
> its
> | own syntax and I'm thinking of not using the generic query designer but
> my
> | own custom query designer (contains some text boxes and combo boxes for
> | example to generate a query from the user inputs). That is, to
> accomodate
> | parameters in reports, the user will need to append
> | Parameters!{ParameterName}.value to the query command text as well. Do I
> | understand this correctly?
> |
> | BTW, is there a limit on number of parameters used in a report? I don't
> | think so. Correct?
> |
> | Thanks a lot!
> | Jenny
> |
> |
> | "Peter Yang [MSFT]" wrote:
> |
> | > Hello Jenny,
> | >
> | > When you reference parameter value in report, ou could use
> | > Parameters!{ParameterName}.value. However, as for parameter for data
> | > processing extension of data source, it depends on how you implement
> this.
> | > For data processing extension s for SQL server, you could use
> @.varaiable
> | > for parameter in SQL query. You may want to try this with your data
> | > procssing extension to test.
> | >
> | > Best Regards,
> | >
> | > Peter Yang
> | > MCSE2000/2003, MCSA, MCDBA
> | > Microsoft Online Partner 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.
> | >
> | > --
> | > | Thread-Topic: Data processing extension and parameters
> | > | thread-index: AcXu+7x8ZtRJnUVcSr2mLOF0DypeJA==> | > | X-WBNR-Posting-Host: 209.17.156.248
> | > | From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
> | > <yinjennytam@.newsgroup.nospam>
> | > | Subject: Data processing extension and parameters
> | > | Date: Mon, 21 Nov 2005 16:29:01 -0800
> | > | Lines: 11
> | > | Message-ID: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
> | > | MIME-Version: 1.0
> | > | Content-Type: text/plain;
> | > | charset="Utf-8"
> | > | Content-Transfer-Encoding: 7bit
> | > | X-Newsreader: Microsoft CDO for Windows 2000
> | > | Content-Class: urn:content-classes:message
> | > | Importance: normal
> | > | Priority: normal
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | > | Path:
> TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | > | Xref: TK2MSFTNGXA02.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:63712
> | > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> | > |
> | > | hi, I have implemented a data processing extension successfully and I
> | > have my
> | > | own query syntax (even implemented a custom query designer).
> | > |
> | > | However, if I want to make use of the report parameter(s), it seems
> that
> | > I
> | > | have to append Parameters!{ParameterName} to my query.Value in order
> to
> | > get
> | > | the parameter value entered by the user.
> | > |
> | > | Is this correct?
> | > | Thanks
> | > | jenny
> | > |
> | > |
> | >
> | >
> |
>|||Hello Jenny,
Thank you for your reply. ReportParameters is necessary for all reports
involving parameter. However, In SQL or OLEDB extenstion, parameters used
by query are "@.variable" which is mapped to reportparmeter via
<QueryParameters>.
As you have noticed, you could use Parameters!paramtername.Value directly
in commandtext anyway. Autually this is the method to build up dynamical
query from report parameters when using SQL/OLEDB extensions.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner 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.
--
| Thread-Topic: Data processing extension and parameters
| thread-index: AcXwUOY3bcJ4f+OHTTWQZWHBs9LJkA==| X-WBNR-Posting-Host: 209.17.156.248
| From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
<yinjennytam@.newsgroup.nospam>
| References: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
<y78iI7y7FHA.832@.TK2MSFTNGXA02.phx.gbl>
<AED34EDB-530C-4060-A3FA-3F82AFD91536@.microsoft.com>
<1gHNWo$7FHA.4000@.TK2MSFTNGXA02.phx.gbl>
| Subject: RE: Data processing extension and parameters
| Date: Wed, 23 Nov 2005 09:11:10 -0800
| Lines: 247
| Message-ID: <8252C18E-9A71-4E37-B59D-64FE1405FE65@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:63867
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| When I checked the rdl file that is generated, I couldn't find what you
said
| below, but I found the followings instead (where <ReportParameters> is at
the
| same level as <DataSources>):
|
| <ReportParameters>
| <ReportParameter Name="State">
| <DataType>String</DataType>
| <DefaultValue>
| <Values>
| <Value>CA</Value>
| </Values>
| </DefaultValue>
| <Prompt>State</Prompt>
| <ValidValues>
| <ParameterValues>
| <ParameterValue>
| <Value>CA</Value>
| <Label>California</Label>
| </ParameterValue>
| <ParameterValue>
| <Value>GA</Value>
| <Label>Georgia </Label>
| </ParameterValue>
| <ParameterValue>
| <Value>NY</Value>
| <Label>New York</Label>
| </ParameterValue>
| <ParameterValue>
| <Value>WA</Value>
| <Label>Washington</Label>
| </ParameterValue>
| </ParameterValues>
| </ValidValues>
| </ReportParameter>
| </ReportParameters>
|
| Within the <DataSets> element, I could only find <Query> with
<CommandText>
| as follows (some details omitted):
|
| <CommandText>=" ... State = " & Parameters!State.Value</CommandText>
|
|
| Have I done anything wrong? It seems to work for me so far. I did get
the
| right selected parameter value when processing the report though.
|
| Thanks again for your help.
| Jenny
|
|
|
|
| "Peter Yang [MSFT]" wrote:
|
| > Hello Jenny,
| >
| > Report rdl contains dataset section that describes data fields and
query.
| > Dataset section may contain one or more datasets depending on the
report
| > layout. Each dataset entry has query section with command text entry
and
| > query parameters. This is also true for data processing extension
query.
| > For example:
| >
| > <Query>
| > <DataSourceName>AdventureWorks</DataSourceName>
| > <CommandText>SELECT C.FirstName + ' ' + C.LastName AS
| > Employee, DATEPART(Year, SOH.OrderDate) AS OrderYear,
| > DATEPART(Month, SOH.OrderDate) AS OrderMonthNum,
| > DATENAME(Month, SOH.OrderDate) AS OrderMonth, SUM(SOD.LineTotal) AS
Sales
| > FROM Sales.SalesOrderHeader SOH INNER JOIN
| > Sales.SalesOrderDetail SOD ON SOH.SalesOrderID =| > SOD.SalesOrderID INNER JOIN
| > Sales.SalesPerson SP ON SOH.SalesPersonID =SP.SalesPersonID
| > INNER JOIN
| > HumanResources.Employee E ON SP.SalesPersonID =E.EmployeeID
| > INNER JOIN
| > Person.Contact C ON E.ContactID = C.ContactID
| > WHERE (DATEPART(Year, SOH.OrderDate) <= @.ReportYear - 1 OR
| > DATEPART(Year, SOH.OrderDate) = @.ReportYear AND
DATEPART(Month,
| > SOH.OrderDate) <= @.ReportMonth) AND
| > (SOH.SalesPersonID = @.EmpID)
| > GROUP BY C.FirstName + ' ' + C.LastName, SOH.SalesPersonID,
| > DATEPART(Year, SOH.OrderDate),
| > DATEPART(Month, SOH.OrderDate), DATENAME(Month,
| > SOH.OrderDate)</CommandText>
| > <QueryParameters>
| > <QueryParameter Name="@.ReportYear">
| > <Value>=Parameters!ReportYear.Value</Value>
| > </QueryParameter>
| > <QueryParameter Name="@.ReportMonth">
| > <Value>=Parameters!ReportMonth.Value</Value>
| > </QueryParameter>
| > <QueryParameter Name="@.EmpID">
| > <Value>=Parameters!EmpID.Value</Value>
| > </QueryParameter>
| > </QueryParameters>
| > <Timeout>30</Timeout>
| > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
| > </Query>
| >
| > No matter how you implement your data extension or cutom query
designer,
| > the result rdl shall include the commandtext and parameters you want.
| >
| > I did not find any limiatation in number of parameters in a report and
I
| > think it is limited by server performance though.
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner 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.
| >
| > --
| > | Thread-Topic: Data processing extension and parameters
| > | thread-index: AcXvg6xl+JTGVcWuQlyb36CPBUDX8A==| > | X-WBNR-Posting-Host: 209.17.156.248
| > | From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
| > <yinjennytam@.newsgroup.nospam>
| > | References: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
| > <y78iI7y7FHA.832@.TK2MSFTNGXA02.phx.gbl>
| > | Subject: RE: Data processing extension and parameters
| > | Date: Tue, 22 Nov 2005 08:42:06 -0800
| > | Lines: 86
| > | Message-ID: <AED34EDB-530C-4060-A3FA-3F82AFD91536@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.reportingsvcs:63759
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | Thanks for your reply. Yes if I append the
| > Parameters!{ParameterName}.value
| > | to my query, it does work and I get the right value selected by
users.
| > |
| > | However, my main concern is that the query used in my data extension
has
| > its
| > | own syntax and I'm thinking of not using the generic query designer
but
| > my
| > | own custom query designer (contains some text boxes and combo boxes
for
| > | example to generate a query from the user inputs). That is, to
| > accomodate
| > | parameters in reports, the user will need to append
| > | Parameters!{ParameterName}.value to the query command text as well.
Do I
| > | understand this correctly?
| > |
| > | BTW, is there a limit on number of parameters used in a report? I
don't
| > | think so. Correct?
| > |
| > | Thanks a lot!
| > | Jenny
| > |
| > |
| > | "Peter Yang [MSFT]" wrote:
| > |
| > | > Hello Jenny,
| > | >
| > | > When you reference parameter value in report, ou could use
| > | > Parameters!{ParameterName}.value. However, as for parameter for
data
| > | > processing extension of data source, it depends on how you
implement
| > this.
| > | > For data processing extension s for SQL server, you could use
| > @.varaiable
| > | > for parameter in SQL query. You may want to try this with your data
| > | > procssing extension to test.
| > | >
| > | > Best Regards,
| > | >
| > | > Peter Yang
| > | > MCSE2000/2003, MCSA, MCDBA
| > | > Microsoft Online Partner 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.
| > | >
| > | > --
| > | > | Thread-Topic: Data processing extension and parameters
| > | > | thread-index: AcXu+7x8ZtRJnUVcSr2mLOF0DypeJA==| > | > | X-WBNR-Posting-Host: 209.17.156.248
| > | > | From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
| > | > <yinjennytam@.newsgroup.nospam>
| > | > | Subject: Data processing extension and parameters
| > | > | Date: Mon, 21 Nov 2005 16:29:01 -0800
| > | > | Lines: 11
| > | > | Message-ID: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
| > | > | MIME-Version: 1.0
| > | > | Content-Type: text/plain;
| > | > | charset="Utf-8"
| > | > | Content-Transfer-Encoding: 7bit
| > | > | X-Newsreader: Microsoft CDO for Windows 2000
| > | > | Content-Class: urn:content-classes:message
| > | > | Importance: normal
| > | > | Priority: normal
| > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > | > | Path:
| > TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | > | Xref: TK2MSFTNGXA02.phx.gbl
| > microsoft.public.sqlserver.reportingsvcs:63712
| > | > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > | > |
| > | > | hi, I have implemented a data processing extension successfully
and I
| > | > have my
| > | > | own query syntax (even implemented a custom query designer).
| > | > |
| > | > | However, if I want to make use of the report parameter(s), it
seems
| > that
| > | > I
| > | > | have to append Parameters!{ParameterName} to my query.Value in
order
| > to
| > | > get
| > | > | the parameter value entered by the user.
| > | > |
| > | > | Is this correct?
| > | > | Thanks
| > | > | jenny
| > | > |
| > | > |
| > | >
| > | >
| > |
| >
| >
||||Thank you for your help. I've noticed that when using the SQL extension, if
the query contains a @.variable, Report Designer automatically creates
corresponding report parameters in the report.
This does not happen in my data extension, and I believe this is because my
query parser does not do anything special when the query contains a @.variable.
Thank you
Jenny
"Peter Yang [MSFT]" wrote:
> Hello Jenny,
> Thank you for your reply. ReportParameters is necessary for all reports
> involving parameter. However, In SQL or OLEDB extenstion, parameters used
> by query are "@.variable" which is mapped to reportparmeter via
> <QueryParameters>.
> As you have noticed, you could use Parameters!paramtername.Value directly
> in commandtext anyway. Autually this is the method to build up dynamical
> query from report parameters when using SQL/OLEDB extensions.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner 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.
> --
> | Thread-Topic: Data processing extension and parameters
> | thread-index: AcXwUOY3bcJ4f+OHTTWQZWHBs9LJkA==> | X-WBNR-Posting-Host: 209.17.156.248
> | From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
> <yinjennytam@.newsgroup.nospam>
> | References: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
> <y78iI7y7FHA.832@.TK2MSFTNGXA02.phx.gbl>
> <AED34EDB-530C-4060-A3FA-3F82AFD91536@.microsoft.com>
> <1gHNWo$7FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> | Subject: RE: Data processing extension and parameters
> | Date: Wed, 23 Nov 2005 09:11:10 -0800
> | Lines: 247
> | Message-ID: <8252C18E-9A71-4E37-B59D-64FE1405FE65@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:63867
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | When I checked the rdl file that is generated, I couldn't find what you
> said
> | below, but I found the followings instead (where <ReportParameters> is at
> the
> | same level as <DataSources>):
> |
> | <ReportParameters>
> | <ReportParameter Name="State">
> | <DataType>String</DataType>
> | <DefaultValue>
> | <Values>
> | <Value>CA</Value>
> | </Values>
> | </DefaultValue>
> | <Prompt>State</Prompt>
> | <ValidValues>
> | <ParameterValues>
> | <ParameterValue>
> | <Value>CA</Value>
> | <Label>California</Label>
> | </ParameterValue>
> | <ParameterValue>
> | <Value>GA</Value>
> | <Label>Georgia </Label>
> | </ParameterValue>
> | <ParameterValue>
> | <Value>NY</Value>
> | <Label>New York</Label>
> | </ParameterValue>
> | <ParameterValue>
> | <Value>WA</Value>
> | <Label>Washington</Label>
> | </ParameterValue>
> | </ParameterValues>
> | </ValidValues>
> | </ReportParameter>
> | </ReportParameters>
> |
> | Within the <DataSets> element, I could only find <Query> with
> <CommandText>
> | as follows (some details omitted):
> |
> | <CommandText>=" ... State = " & Parameters!State.Value</CommandText>
> |
> |
> | Have I done anything wrong? It seems to work for me so far. I did get
> the
> | right selected parameter value when processing the report though.
> |
> | Thanks again for your help.
> | Jenny
> |
> |
> |
> |
> | "Peter Yang [MSFT]" wrote:
> |
> | > Hello Jenny,
> | >
> | > Report rdl contains dataset section that describes data fields and
> query.
> | > Dataset section may contain one or more datasets depending on the
> report
> | > layout. Each dataset entry has query section with command text entry
> and
> | > query parameters. This is also true for data processing extension
> query.
> | > For example:
> | >
> | > <Query>
> | > <DataSourceName>AdventureWorks</DataSourceName>
> | > <CommandText>SELECT C.FirstName + ' ' + C.LastName AS
> | > Employee, DATEPART(Year, SOH.OrderDate) AS OrderYear,
> | > DATEPART(Month, SOH.OrderDate) AS OrderMonthNum,
> | > DATENAME(Month, SOH.OrderDate) AS OrderMonth, SUM(SOD.LineTotal) AS
> Sales
> | > FROM Sales.SalesOrderHeader SOH INNER JOIN
> | > Sales.SalesOrderDetail SOD ON SOH.SalesOrderID => | > SOD.SalesOrderID INNER JOIN
> | > Sales.SalesPerson SP ON SOH.SalesPersonID => SP.SalesPersonID
> | > INNER JOIN
> | > HumanResources.Employee E ON SP.SalesPersonID => E.EmployeeID
> | > INNER JOIN
> | > Person.Contact C ON E.ContactID = C.ContactID
> | > WHERE (DATEPART(Year, SOH.OrderDate) <= @.ReportYear - 1 OR
> | > DATEPART(Year, SOH.OrderDate) = @.ReportYear AND
> DATEPART(Month,
> | > SOH.OrderDate) <= @.ReportMonth) AND
> | > (SOH.SalesPersonID = @.EmpID)
> | > GROUP BY C.FirstName + ' ' + C.LastName, SOH.SalesPersonID,
> | > DATEPART(Year, SOH.OrderDate),
> | > DATEPART(Month, SOH.OrderDate), DATENAME(Month,
> | > SOH.OrderDate)</CommandText>
> | > <QueryParameters>
> | > <QueryParameter Name="@.ReportYear">
> | > <Value>=Parameters!ReportYear.Value</Value>
> | > </QueryParameter>
> | > <QueryParameter Name="@.ReportMonth">
> | > <Value>=Parameters!ReportMonth.Value</Value>
> | > </QueryParameter>
> | > <QueryParameter Name="@.EmpID">
> | > <Value>=Parameters!EmpID.Value</Value>
> | > </QueryParameter>
> | > </QueryParameters>
> | > <Timeout>30</Timeout>
> | > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> | > </Query>
> | >
> | > No matter how you implement your data extension or cutom query
> designer,
> | > the result rdl shall include the commandtext and parameters you want.
> | >
> | > I did not find any limiatation in number of parameters in a report and
> I
> | > think it is limited by server performance though.
> | >
> | > Regards,
> | >
> | > Peter Yang
> | > MCSE2000/2003, MCSA, MCDBA
> | > Microsoft Online Partner 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.
> | >
> | > --
> | > | Thread-Topic: Data processing extension and parameters
> | > | thread-index: AcXvg6xl+JTGVcWuQlyb36CPBUDX8A==> | > | X-WBNR-Posting-Host: 209.17.156.248
> | > | From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
> | > <yinjennytam@.newsgroup.nospam>
> | > | References: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
> | > <y78iI7y7FHA.832@.TK2MSFTNGXA02.phx.gbl>
> | > | Subject: RE: Data processing extension and parameters
> | > | Date: Tue, 22 Nov 2005 08:42:06 -0800
> | > | Lines: 86
> | > | Message-ID: <AED34EDB-530C-4060-A3FA-3F82AFD91536@.microsoft.com>
> | > | MIME-Version: 1.0
> | > | Content-Type: text/plain;
> | > | charset="Utf-8"
> | > | Content-Transfer-Encoding: 7bit
> | > | X-Newsreader: Microsoft CDO for Windows 2000
> | > | Content-Class: urn:content-classes:message
> | > | Importance: normal
> | > | Priority: normal
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | > | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | > | Xref: TK2MSFTNGXA02.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:63759
> | > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> | > |
> | > | Thanks for your reply. Yes if I append the
> | > Parameters!{ParameterName}.value
> | > | to my query, it does work and I get the right value selected by
> users.
> | > |
> | > | However, my main concern is that the query used in my data extension
> has
> | > its
> | > | own syntax and I'm thinking of not using the generic query designer
> but
> | > my
> | > | own custom query designer (contains some text boxes and combo boxes
> for
> | > | example to generate a query from the user inputs). That is, to
> | > accomodate
> | > | parameters in reports, the user will need to append
> | > | Parameters!{ParameterName}.value to the query command text as well.
> Do I
> | > | understand this correctly?
> | > |
> | > | BTW, is there a limit on number of parameters used in a report? I
> don't
> | > | think so. Correct?
> | > |
> | > | Thanks a lot!
> | > | Jenny
> | > |
> | > |
> | > | "Peter Yang [MSFT]" wrote:
> | > |
> | > | > Hello Jenny,
> | > | >
> | > | > When you reference parameter value in report, ou could use
> | > | > Parameters!{ParameterName}.value. However, as for parameter for
> data
> | > | > processing extension of data source, it depends on how you
> implement
> | > this.
> | > | > For data processing extension s for SQL server, you could use
> | > @.varaiable
> | > | > for parameter in SQL query. You may want to try this with your data
> | > | > procssing extension to test.
> | > | >
> | > | > Best Regards,
> | > | >
> | > | > Peter Yang
> | > | > MCSE2000/2003, MCSA, MCDBA
> | > | > Microsoft Online Partner 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.
> | > | >
> | > | > --
> | > | > | Thread-Topic: Data processing extension and parameters
> | > | > | thread-index: AcXu+7x8ZtRJnUVcSr2mLOF0DypeJA==> | > | > | X-WBNR-Posting-Host: 209.17.156.248
> | > | > | From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
> | > | > <yinjennytam@.newsgroup.nospam>
> | > | > | Subject: Data processing extension and parameters
> | > | > | Date: Mon, 21 Nov 2005 16:29:01 -0800
> | > | > | Lines: 11
> | > | > | Message-ID: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
> | > | > | MIME-Version: 1.0
> | > | > | Content-Type: text/plain;
> | > | > | charset="Utf-8"
> | > | > | Content-Transfer-Encoding: 7bit
> | > | > | X-Newsreader: Microsoft CDO for Windows 2000
> | > | > | Content-Class: urn:content-classes:message
> | > | > | Importance: normal
> | > | > | Priority: normal
> | > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | > | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250

Data Processing Extension - parameters not created

Hi,
I'm writing a Data Processing Extension (DPE) for Reporting Services 2000,
using the FsiExtension sample as a basis. My problem is that the
CreateParameter method of the command class doesn't get called, so when I am
to execute my datareader, the parameters aren't there. I have added the
parameters manually to the dataset in the report, but they don't get carried
over to the DPE. I've debugged the code, and the CreateParameter never gets
called. The strange thing is that I had it working earlier, because I then
could access the parameter collection in the debugger, but now suddenly it
fails.
public IDataParameter CreateParameter()
{
return (IDataParameter)(new dbpDataParameter());
}
Any tips?
Brgds
JonasI found the problem, my class only implemented IDbComamand. When I added
IDbCommandAnalysis and
a GetParameters method it worked like it should.
/Jonas
"Jonas" <Jonas@.nospam.pl> wrote in message
news:OyFcwrwiGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'm writing a Data Processing Extension (DPE) for Reporting Services 2000,
> using the FsiExtension sample as a basis. My problem is that the
> CreateParameter method of the command class doesn't get called, so when I
> am to execute my datareader, the parameters aren't there. I have added the
> parameters manually to the dataset in the report, but they don't get
> carried over to the DPE. I've debugged the code, and the CreateParameter
> never gets called. The strange thing is that I had it working earlier,
> because I then could access the parameter collection in the debugger, but
> now suddenly it fails.
> public IDataParameter CreateParameter()
> {
> return (IDataParameter)(new dbpDataParameter());
> }
>
> Any tips?
> Brgds
> Jonas
>

Data Parameter Question

Hi All:
Reporting Services newbie here. I have a query that has a fieldcalled Date. I have created two parameters in my report namedStart Date and End Date.
I need to know the proper syntax to build an expression that willreturn a recordset that contains all records with dates between mystart and end dates.
How do I accomplish this? And also, does anyone know of aresource anywhere that lays out examples of the syntax for variousexpressions used in Reporting Services. The help documentationisn't very clear.
Thanks
Brennan

What you need involve Time Interval and it is not implemented in SQL Server now but I found a UDF(User Defined Function) that can do it. Use it and you will get what you need, I would also look at the stored procs in the Time Tracker starter kit it will have time interval cals. Try the link below for the code. Hope this helps.

http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-38-udf_DT_AddTime.htm

|||you can either call a stored proc that takes the startdate and enddate as parameters and gives you the result set or you can get the entire result set from the table and use filters to filter out the data between the dates. I'd recommend using a stored proc.|||

I am not sure if this is what you need but like most suggest you should use a stored proceedure but the syntax for the query would look like:

SELECT column_name FROM table_nameWHERE column_nameBETWEEN @.start_dateAND @.end_date

You can just try this directly in the report designer before making a stored procedure for it.

Note: @.start_date and @.end_date will be the value the user put in the parameter box.

|||Hi,
I have a similar question as you do, in fact I'm tryning to filter my report results between two dates, and I'm trying to do this directly in the Report designer using two report parameters, for the begin and for the end date. My problem is that the data type available for date parameters is "Date Time", and I want my parameter formated as a Date ( I don′t want to do it on the query because the parameter mus be shown as a date in the report) .Is there a way of doing it? Or I must set the parameters as strings, and cast them in the query? If so, how can I send an error message if the dates are not well fomated?
Thanks in advance for any help!
|||I didnt understand your question. Can you rephrase ?|||

Yes, what I wrote before was a little bit confuse,

In fact I have 2 different questions:

The first one is, can I have a report parameter configured as a Date instead of Date Time? (the data types available for the report parameters don't include the type Date).
The second question is, how can I send a pop-up with an error message in my report ? For example, if the parameter value inserted by the user is not valid for my report.

Thanks,
Cat.

|||

Hello:
Yes, just CAST the datetime value provided by the parameter within RS within SQL to the date - but thre is more to it than that!
The date passed as a parameter could be in any number of many formats - YYYY.DD/DD or YYYY-MM-DD, etc.
The dates based upon the culture or the dates stored within the data base!
This "date" thing drives me nuts at times in that I have to support Thai (which their year is 543 years ahead of the western calendar) but this is what I do and this may or may not be of help.
In my SQL data base I have dates that are in YYYY-MM-DD Hour:Minute:Sec, YYYYMMDD, MM/DD/YY, MM/DD/YYYY, DD/MM/YYYY, DD/MM/YY, etc. I did not create this mess but inherited it from programmers who developed the system in Thailand and Korea which I used the data within this database for another application -- I get really frustrated at times.
So, I define all date parameters in RS as strings. When an RS report is launched and the date parameter is displayed I provide a drop down list of available parameters which are:
NONE, Today, Yesterday, Beginning of Week, BI -Weekly, Beginning of Month, Monthly, Bi-Monthly, Quarterly, Yearly not in this particular order but nevertheless.
If they select NONE - I provide and additional two(2) parameters which allow them to enter the date in YYYYMMDD format.
I have also created a table in SQL called a Calendar Time Dimension (which is mandatory for Analysis Services).
The Calendar Time Dimension has a day for each day of the year and I have populated for 1995 through 2015.
The Calendar Time Dimension file has for each date within the year(s) that I populate the following:
Time Key which has a value for (eg. todays date 20050904) in the following formats:
YYYYMMDD, YYMMDD, MMDDYY, MMDDYYYY, DDMMYY, DDMMYYYY, MM/DD/YYYY, DD/MM/YYYY, including the Thai date which ifor today i s 20050904 + 543 (years) = 25480904, etc. I have all of the weirdo formats defined.
Also in the Calendar Time Dimension is the Calendar Week, Calendar Quarter, Fiscal Quarter, Month of Year, Week within Month, etc.
So when a user enters their own date via the date parameter (and elects not to select the any other parameter such as Today) within RS (a call to a stored procedure) I validate against the Calendar Time Dimension and if the date is invalid I provide back an error message via RS that indicates the date is invalid.
So my point (even though LOOONG WINDED) is that you should consider to manipulate the dates within a stored procedure based upon anything entered by the user within the date parameter within RS and validate against a similar Calendar Time Dimension table within the data base. So when a date is valid you use a JOIN to the Calendar Time Dimension to filter out the records you want within SQL. Another added benefit is that this really improves performance in that you are not filtering or comparing dates within each record within the DB.
If the user selects TODAY, Yesterday, Weekly etc. I have UDF's that generate the date ranges I use against the Calendar Time Dimension to process only the records I need against the table I go after in the Stored Procedure for the respective RS program.
If you want I can give you a stored procedure to create the Calendar TIme Dimension and then you can go from there.
Best Regards,

|||Thanks a lot for your post!
Cat.

Data not displayed in Preview Tab, but is displayed in Data Tab

This has happened on more than one occasion. I have defined a stored
procedure type datasource with 12 parameters. When I execute the SP in the
Data tab, the resulting rows are correctly displayed. When I use the same
parameters when previewing the report, no data is displayed. I have found
that empty string parameters need to be <blank> on the data tab, but need to
be '' on the preview tab, so I know about this little gotcha.
Has anyone else had this problem and found a solution. I have check the rdl
file to make sure that the datasource and fields are correct. I have a table
and a pie chart on the report and both are empty when I preview it.
===================== Thanks!
Keith
p.s. Get off the couch and exercise!I have answered my own question.
This might come in handy for a few people. If one of your parameters is a
string and the SP allows an empty string, then make sure to check the
Parameter definition box "Allow Blank Value". otherwise if you try to send
it a blank or '' (empty string), then SQL profiler shows the parameter as
N'''. This solved my problem and I am now getting data.
=====================Thanks!
Keith
p.s. Get off the couch and exercise!
"Keith" wrote:
> This has happened on more than one occasion. I have defined a stored
> procedure type datasource with 12 parameters. When I execute the SP in the
> Data tab, the resulting rows are correctly displayed. When I use the same
> parameters when previewing the report, no data is displayed. I have found
> that empty string parameters need to be <blank> on the data tab, but need to
> be '' on the preview tab, so I know about this little gotcha.
> Has anyone else had this problem and found a solution. I have check the rdl
> file to make sure that the datasource and fields are correct. I have a table
> and a pie chart on the report and both are empty when I preview it.
>
> =====================> Thanks!
> Keith
> p.s. Get off the couch and exercise!

Data Issues

I have a static report that has 2 parameters begin date and end date. The
report has been running fine for several months but today we had a major data
problem. The query returns dollar amounts based on sales, When running the
report for 12/29/204 to 1/5/05 we received 3 million in sales, which we knew
was incorrect. We then queried the tables directly through PL/SQL developer
and came up with the correct numbers. We then stopped and started the
reporting service's service and reposted the report but we came back with the
bad sales number. Out of curiosity I ran the report for dates in the future
such as 1/10/05, 1/15/05 etc. and came back with sales numbers. Again I
checked the tables were hitting and of course their was no data. This was all
in the morning. I just ran the report again this afternoon and now it is
correct. Does anyone have a clue as to why this might be happening?Is the report cached or run on a schedule?..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Zach" <Zach@.discussions.microsoft.com> wrote in message
news:391A179E-B8F2-4E62-81D6-7FF4ED92C9D1@.microsoft.com...
> I have a static report that has 2 parameters begin date and end date.
The
> report has been running fine for several months but today we had a major
data
> problem. The query returns dollar amounts based on sales, When running
the
> report for 12/29/204 to 1/5/05 we received 3 million in sales, which we
knew
> was incorrect. We then queried the tables directly through PL/SQL
developer
> and came up with the correct numbers. We then stopped and started the
> reporting service's service and reposted the report but we came back with
the
> bad sales number. Out of curiosity I ran the report for dates in the
future
> such as 1/10/05, 1/15/05 etc. and came back with sales numbers. Again I
> checked the tables were hitting and of course their was no data. This was
all
> in the morning. I just ran the report again this afternoon and now it is
> correct. Does anyone have a clue as to why this might be happening?|||No. The end user just clicks on the report and keys in the begin and end
dates.
"Wayne Snyder" wrote:
> Is the report cached or run on a schedule?..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Zach" <Zach@.discussions.microsoft.com> wrote in message
> news:391A179E-B8F2-4E62-81D6-7FF4ED92C9D1@.microsoft.com...
> > I have a static report that has 2 parameters begin date and end date.
> The
> > report has been running fine for several months but today we had a major
> data
> > problem. The query returns dollar amounts based on sales, When running
> the
> > report for 12/29/204 to 1/5/05 we received 3 million in sales, which we
> knew
> > was incorrect. We then queried the tables directly through PL/SQL
> developer
> > and came up with the correct numbers. We then stopped and started the
> > reporting service's service and reposted the report but we came back with
> the
> > bad sales number. Out of curiosity I ran the report for dates in the
> future
> > such as 1/10/05, 1/15/05 etc. and came back with sales numbers. Again I
> > checked the tables were hitting and of course their was no data. This was
> all
> > in the morning. I just ran the report again this afternoon and now it is
> > correct. Does anyone have a clue as to why this might be happening?
>
>|||It's that pesky Y2K005 issue...
Can you run a trace of the SQL activity, and check what is being passed as
parameters? Are the dates being processed as strings?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Zach" <Zach@.discussions.microsoft.com> wrote in message
news:391A179E-B8F2-4E62-81D6-7FF4ED92C9D1@.microsoft.com...
>I have a static report that has 2 parameters begin date and end date.
>The
> report has been running fine for several months but today we had a major
> data
> problem. The query returns dollar amounts based on sales, When running
> the
> report for 12/29/204 to 1/5/05 we received 3 million in sales, which we
> knew
> was incorrect. We then queried the tables directly through PL/SQL
> developer
> and came up with the correct numbers. We then stopped and started the
> reporting service's service and reposted the report but we came back with
> the
> bad sales number. Out of curiosity I ran the report for dates in the
> future
> such as 1/10/05, 1/15/05 etc. and came back with sales numbers. Again I
> checked the tables were hitting and of course their was no data. This was
> all
> in the morning. I just ran the report again this afternoon and now it is
> correct. Does anyone have a clue as to why this might be happening?|||I just took a look at your code in the other thread. You're creating
heartache for yourself by breaking apart the dates. Wayne gave you good
advice about using the DateAdd function.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Zach" <Zach@.discussions.microsoft.com> wrote in message
news:391A179E-B8F2-4E62-81D6-7FF4ED92C9D1@.microsoft.com...
>I have a static report that has 2 parameters begin date and end date.
>The
> report has been running fine for several months but today we had a major
> data
> problem. The query returns dollar amounts based on sales, When running
> the
> report for 12/29/204 to 1/5/05 we received 3 million in sales, which we
> knew
> was incorrect. We then queried the tables directly through PL/SQL
> developer
> and came up with the correct numbers. We then stopped and started the
> reporting service's service and reposted the report but we came back with
> the
> bad sales number. Out of curiosity I ran the report for dates in the
> future
> such as 1/10/05, 1/15/05 etc. and came back with sales numbers. Again I
> checked the tables were hitting and of course their was no data. This was
> all
> in the morning. I just ran the report again this afternoon and now it is
> correct. Does anyone have a clue as to why this might be happening?