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