Data source with a type of SSIS

I know that you can do this, because I have read about it in MSDN, but I'm not suer about the syntax.

I am creating a report that needs an SSIS type data source. I have deleted the comments in the relevant config files both on my local machine (development) and the reporting services server, and can see SSIS as data type. Now for the tricky bit!

I go to create a new data source. Give it a name, choose SSIS as a data type, and now for the connection string. I "think" I know what I would do if it were a file system SSIS package, but it is stored in the MSDB. What connection string do I use to connect to this data source? There are no DTSX files on the server. Somehow, I presume, I have to talk directly to the MSDB..... Any ideas anyone?

OK, so I gave up on trying to get data out of the MSDB! I capitulated to the MSDN way of doing things and saved my SSIS package as a file system object and attached in that way.

Works like a dream - surprisingly fast. I wonder how it will perform (if at all) when using report snapshots!

If anyone has been successful in reporting over an SSIS, MSDB data source, I would appreciate it if you could post the solution here.

Data Shaping Question (Syntax)

Hi. I guess it's simple, but I think I'm really dumm... (and, on top, I
can see only one example of data shaping in MSDN). How do I calculate
fields ? Starting with:
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
SomeNumber FROM Customers} AS Customers " & _
"APPEND ({SELECT CustomerID, OrderID, ShipVia, EmployeeID,
OrderDate FROM Orders} AS Orders " & _
"RELATE CustomerID TO CustomerID)"
(from Northwind), I'd like to find out the syntax for sum-ming on, say,
ShipVia, (although it does not make any logical sense to sum on that
column). So, in other words, I'd like to add ***to the parent record***
(the one coming from Customers), a calculated column containing a sum
on the child (chapter), so for customer "ALFKI" I should add up 1, 2,
1, 3, 1, and 1, obtaining 9.
I can, of course, change it to
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
ContactTitle FROM Customers} AS Customers " & _
"APPEND ({SELECT CustomerID, OrderID, EmployeeID, OrderDate,
SUM(ShipVia) as SumOfShipVia FROM Orders " & _
"GROUP BY CustomerID, OrderID, EmployeeID, OrderDate} AS Orders
" & _
"RELATE CustomerID TO CustomerID)"
but then the SUM is not a sum, since it's still in the child chapter.
How do I show the sum in the parent record ?
Similar - This contains three levels: Customers, Orders, and
OrderDetails.
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
ContactTitle FROM Customers} AS Customers " & _
"APPEND ((SHAPE {SELECT CustomerID, OrderID, EmployeeID,
OrderDate FROM Orders} AS Orders " & _
"APPEND ({SELECT OrderID, ProductID, UnitPrice, Quantity FROM
[Order Details]} AS Orderdetails " & _
"RELATE OrderID TO OrderID) As OrderDetails) " & _
"RELATE CustomerID TO CustomerID)"
What if I wanted to add to the child (Orders) a calculated column on a
column in the grandchild, OrderDetails, say, OrdersDetails.UnitPrice ?
What would be the syntax then ?
Thank you very much for your help.
Alex.
"Radu" <cuca_macaii2000@.yahoo.com> wrote in message
news:1166718883.058620.244010@.80g2000cwy.googlegro ups.com...
> Hi. I guess it's simple, but I think I'm really dumm... (and, on top, I
> can see only one example of data shaping in MSDN). How do I calculate
> fields ? Starting with:
> . . ..
This data shaping syntax is an old, dead feature of ADO. Don't use it. In
..NET use multi-table DataSets, which are a much better solution for
client-side shaped data.
David
|||Well, David, the thing is that I don't use dotnet - I have to write
some VBA code in Excel 2000 to show data in a hierarchical data grid,
so that the user can look at a list of workgroups with their details
(aggregate data at workgroup level), then click on a workgroup and
explode it so that the teams contained in that workgroup are seen, with
their details, and then clicks on one of the teams in the workgroup and
sees the contained team-members, with their details, kind of a treeview
(actually, a fancy datagrid). Since I don't have any 3rd party tools
available, I have to make it work with the default (MS) controls.
MSHFlexGrid is one of them, and it does exactly what the user needed
(show data in a tree-like structure).
So I should show:
root
-- list of workgroups, with agregate data (#hours worked, for
instance, by workgroup)
-- list of teams in current workgroup, with agregate data (#hours
worked by team)
-- list of workers in current team, with #hours worked by
worker
PS. All this data comes from some MSAccess tables.
Thank you very much.
David Browne wrote:
> "Radu" <cuca_macaii2000@.yahoo.com> wrote in message
> news:1166718883.058620.244010@.80g2000cwy.googlegro ups.com...
> This data shaping syntax is an old, dead feature of ADO. Don't use it. In
> .NET use multi-table DataSets, which are a much better solution for
> client-side shaped data.
> David
|||"Radu" <cuca_macaii2000@.yahoo.com> wrote in message
news:1166727210.486584.307260@.i12g2000cwa.googlegr oups.com...
> Well, David, the thing is that I don't use dotnet - I have to write
> some VBA code in Excel 2000 to show data in a hierarchical data grid,
> so that the user can look at a list of workgroups with their details
> (aggregate data at workgroup level), then click on a workgroup and
> explode it so that the teams contained in that workgroup are seen, with
> their details, and then clicks on one of the teams in the workgroup and
> sees the contained team-members, with their details, kind of a treeview
> (actually, a fancy datagrid). Since I don't have any 3rd party tools
> available, I have to make it work with the default (MS) controls.
> MSHFlexGrid is one of them, and it does exactly what the user needed
> (show data in a tree-like structure).
> So I should show:
> root
> -- list of workgroups, with agregate data (#hours worked, for
> instance, by workgroup)
> -- list of teams in current workgroup, with agregate data (#hours
> worked by team)
> -- list of workers in current team, with #hours worked by
> worker
> PS. All this data comes from some MSAccess tables.
>
SO first off, this is posted in a SQL Server group. Your solution doesn't
involve SQL Server. If it did, I'd advise you to create views in SQL Server
that add the appropriate columns to the resultset. Perhaps you can do the
same in access with saved queries, or subqueries.
David
>

Data Shaping Question (Syntax)

Hi. I guess it's simple, but I think I'm really dumm... (and, on top, I
can see only one example of data shaping in MSDN). How do I calculate
fields ? Starting with:
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
SomeNumber FROM Customers} AS Customers " & _
"APPEND ({SELECT CustomerID, OrderID, ShipVia, EmployeeID,
OrderDate FROM Orders} AS Orders " & _
"RELATE CustomerID TO CustomerID)"
(from Northwind), I'd like to find out the syntax for sum-ming on, say,
ShipVia, (although it does not make any logical sense to sum on that
column). So, in other words, I'd like to add ***to the parent record***
(the one coming from Customers), a calculated column containing a sum
on the child (chapter), so for customer "ALFKI" I should add up 1, 2,
1, 3, 1, and 1, obtaining 9.
I can, of course, change it to
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
ContactTitle FROM Customers} AS Customers " & _
"APPEND ({SELECT CustomerID, OrderID, EmployeeID, OrderDate,
SUM(ShipVia) as SumOfShipVia FROM Orders " & _
"GROUP BY CustomerID, OrderID, EmployeeID, OrderDate} AS Orders
" & _
"RELATE CustomerID TO CustomerID)"
but then the SUM is not a sum, since it's still in the child chapter.
How do I show the sum in the parent record ?
Similar - This contains three levels: Customers, Orders, and
OrderDetails.
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
ContactTitle FROM Customers} AS Customers " & _
"APPEND ((SHAPE {SELECT CustomerID, OrderID, EmployeeID,
OrderDate FROM Orders} AS Orders " & _
"APPEND ({SELECT OrderID, ProductID, UnitPrice, Quantity FROM
[Order Details]} AS Orderdetails " & _
"RELATE OrderID TO OrderID) As OrderDetails) " & _
"RELATE CustomerID TO CustomerID)"
What if I wanted to add to the child (Orders) a calculated column on a
column in the grandchild, OrderDetails, say, OrdersDetails.UnitPrice ?
What would be the syntax then ?
Thank you very much for your help.
Alex."Radu" <cuca_macaii2000@.yahoo.com> wrote in message
news:1166718883.058620.244010@.80g2000cwy.googlegroups.com...
> Hi. I guess it's simple, but I think I'm really dumm... (and, on top, I
> can see only one example of data shaping in MSDN). How do I calculate
> fields ? Starting with:
> . . ..
This data shaping syntax is an old, dead feature of ADO. Don't use it. In
.NET use multi-table DataSets, which are a much better solution for
client-side shaped data.
David|||Well, David, the thing is that I don't use dotnet - I have to write
some VBA code in Excel 2000 to show data in a hierarchical data grid,
so that the user can look at a list of workgroups with their details
(aggregate data at workgroup level), then click on a workgroup and
explode it so that the teams contained in that workgroup are seen, with
their details, and then clicks on one of the teams in the workgroup and
sees the contained team-members, with their details, kind of a treeview
(actually, a fancy datagrid). Since I don't have any 3rd party tools
available, I have to make it work with the default (MS) controls.
MSHFlexGrid is one of them, and it does exactly what the user needed
(show data in a tree-like structure).
So I should show:
root
-- list of workgroups, with agregate data (#hours worked, for
instance, by workgroup)
-- list of teams in current workgroup, with agregate data (#hours
worked by team)
-- list of workers in current team, with #hours worked by
worker
PS. All this data comes from some MSAccess tables.
Thank you very much.
David Browne wrote:
> "Radu" <cuca_macaii2000@.yahoo.com> wrote in message
> news:1166718883.058620.244010@.80g2000cwy.googlegroups.com...
> > Hi. I guess it's simple, but I think I'm really dumm... (and, on top, I
> > can see only one example of data shaping in MSDN). How do I calculate
> > fields ? Starting with:
> > . . ..
> This data shaping syntax is an old, dead feature of ADO. Don't use it. In
> .NET use multi-table DataSets, which are a much better solution for
> client-side shaped data.
> David|||"Radu" <cuca_macaii2000@.yahoo.com> wrote in message
news:1166727210.486584.307260@.i12g2000cwa.googlegroups.com...
> Well, David, the thing is that I don't use dotnet - I have to write
> some VBA code in Excel 2000 to show data in a hierarchical data grid,
> so that the user can look at a list of workgroups with their details
> (aggregate data at workgroup level), then click on a workgroup and
> explode it so that the teams contained in that workgroup are seen, with
> their details, and then clicks on one of the teams in the workgroup and
> sees the contained team-members, with their details, kind of a treeview
> (actually, a fancy datagrid). Since I don't have any 3rd party tools
> available, I have to make it work with the default (MS) controls.
> MSHFlexGrid is one of them, and it does exactly what the user needed
> (show data in a tree-like structure).
> So I should show:
> root
> -- list of workgroups, with agregate data (#hours worked, for
> instance, by workgroup)
> -- list of teams in current workgroup, with agregate data (#hours
> worked by team)
> -- list of workers in current team, with #hours worked by
> worker
> PS. All this data comes from some MSAccess tables.
>
SO first off, this is posted in a SQL Server group. Your solution doesn't
involve SQL Server. If it did, I'd advise you to create views in SQL Server
that add the appropriate columns to the resultset. Perhaps you can do the
same in access with saved queries, or subqueries.
David
>sql

Data Shaping Question (Syntax)

Hi. I guess it's simple, but I think I'm really dumm... (and, on top, I
can see only one example of data shaping in MSDN). How do I calculate
fields ? Starting with:
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
SomeNumber FROM Customers} AS Customers " & _
"APPEND ({SELECT CustomerID, OrderID, ShipVia, EmployeeID,
OrderDate FROM Orders} AS Orders " & _
"RELATE CustomerID TO CustomerID)"
(from Northwind), I'd like to find out the syntax for sum-ming on, say,
ShipVia, (although it does not make any logical sense to sum on that
column). So, in other words, I'd like to add ***to the parent record***
(the one coming from Customers), a calculated column containing a sum
on the child (chapter), so for customer "ALFKI" I should add up 1, 2,
1, 3, 1, and 1, obtaining 9.
I can, of course, change it to
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
ContactTitle FROM Customers} AS Customers " & _
"APPEND ({SELECT CustomerID, OrderID, EmployeeID, OrderDate,
SUM(ShipVia) as SumOfShipVia FROM Orders " & _
"GROUP BY CustomerID, OrderID, EmployeeID, OrderDate} AS Orders
" & _
"RELATE CustomerID TO CustomerID)"
but then the SUM is not a sum, since it's still in the child chapter.
How do I show the sum in the parent record ?
Similar - This contains three levels: Customers, Orders, and
OrderDetails.
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
ContactTitle FROM Customers} AS Customers " & _
"APPEND ((SHAPE {SELECT CustomerID, OrderID, EmployeeID,
OrderDate FROM Orders} AS Orders " & _
"APPEND ({SELECT OrderID, ProductID, UnitPrice, Quantity FROM
[Order Details]} AS Orderdetails " & _
"RELATE OrderID TO OrderID) As OrderDetails) " & _
"RELATE CustomerID TO CustomerID)"
What if I wanted to add to the child (Orders) a calculated column on a
column in the grandchild, OrderDetails, say, OrdersDetails.UnitPrice ?
What would be the syntax then ?
Thank you very much for your help.
Alex."Radu" <cuca_macaii2000@.yahoo.com> wrote in message
news:1166718883.058620.244010@.80g2000cwy.googlegroups.com...
> Hi. I guess it's simple, but I think I'm really dumm... (and, on top, I
> can see only one example of data shaping in MSDN). How do I calculate
> fields ? Starting with:
> . . ..
This data shaping syntax is an old, dead feature of ADO. Don't use it. In
.NET use multi-table DataSets, which are a much better solution for
client-side shaped data.
David|||Well, David, the thing is that I don't use dotnet - I have to write
some VBA code in Excel 2000 to show data in a hierarchical data grid,
so that the user can look at a list of workgroups with their details
(aggregate data at workgroup level), then click on a workgroup and
explode it so that the teams contained in that workgroup are seen, with
their details, and then clicks on one of the teams in the workgroup and
sees the contained team-members, with their details, kind of a treeview
(actually, a fancy datagrid). Since I don't have any 3rd party tools
available, I have to make it work with the default (MS) controls.
MSHFlexGrid is one of them, and it does exactly what the user needed
(show data in a tree-like structure).
So I should show:
root
-- list of workgroups, with agregate data (#hours worked, for
instance, by workgroup)
-- list of teams in current workgroup, with agregate data (#hours
worked by team)
-- list of workers in current team, with #hours worked by
worker
PS. All this data comes from some MSAccess tables.
Thank you very much.
David Browne wrote:
> "Radu" <cuca_macaii2000@.yahoo.com> wrote in message
> news:1166718883.058620.244010@.80g2000cwy.googlegroups.com...
> This data shaping syntax is an old, dead feature of ADO. Don't use it. I
n
> .NET use multi-table DataSets, which are a much better solution for
> client-side shaped data.
> David|||"Radu" <cuca_macaii2000@.yahoo.com> wrote in message
news:1166727210.486584.307260@.i12g2000cwa.googlegroups.com...
> Well, David, the thing is that I don't use dotnet - I have to write
> some VBA code in Excel 2000 to show data in a hierarchical data grid,
> so that the user can look at a list of workgroups with their details
> (aggregate data at workgroup level), then click on a workgroup and
> explode it so that the teams contained in that workgroup are seen, with
> their details, and then clicks on one of the teams in the workgroup and
> sees the contained team-members, with their details, kind of a treeview
> (actually, a fancy datagrid). Since I don't have any 3rd party tools
> available, I have to make it work with the default (MS) controls.
> MSHFlexGrid is one of them, and it does exactly what the user needed
> (show data in a tree-like structure).
> So I should show:
> root
> -- list of workgroups, with agregate data (#hours worked, for
> instance, by workgroup)
> -- list of teams in current workgroup, with agregate data (#hours
> worked by team)
> -- list of workers in current team, with #hours worked by
> worker
> PS. All this data comes from some MSAccess tables.
>
SO first off, this is posted in a SQL Server group. Your solution doesn't
involve SQL Server. If it did, I'd advise you to create views in SQL Server
that add the appropriate columns to the resultset. Perhaps you can do the
same in access with saved queries, or subqueries.
David
>

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 Parsing in SQL Server views

If column1 in SQL Server column is text: 19980701

What is the syntax in the select statement to convert it to a date
like: 07/01/1998

Thanks for any help

RbollingerYou can use CAST() on a string to get a DATETIME data type. Of course,
you know that you do not formatting in the database in a tiered
architecture. And you know that you should only use the ISO-8601
standards for temporal data. If you want to be a bad programmer, you
can use CONVERT(). It is a proprietary piece of legacy syntax that
will let you pick a national format.