Data Structures

(Wow; surfing this site has really illuminated what a lowly hack-programmer I am to this field of SQL and relational processing :S )

I am creating a temp table, doing an Bill of Material explosion for a single Order Line Item.

(note: SQL Server 2000)

I used blindman's "accumulator method (click here) (http://www.dbforums.com/showpost.php?p=6239109&postcount=6)" to generate the entire potential BOM tree.

So; step 1 works wonderfully! :beer: Cheers blindman!

Now; I want to remove unwanted nodes (and all their children) from the temp-table. I have a bunch of functions (0 to 1 for each node) that return a Yes or No. If "No", then flag-to-eliminate immediately that branch and don't revisit anything on it (and therein lies my problem). This will leave me with a temp table of only valid nodes.

Since this is recursive, and since it will involve Dynamic SQL (the function name varies), all I can come up with is using a Cursor in a WHILE loop. Even at that; since a CURSOR is point-in-time (ie: values don't change once selected), I'll have to re-check the current temp table values (or create of 2nd temp table of only deleted nodes and repeat a SELECT with NOT EXISTS in it, hmmm).

Since blindman's method generates a table ordered by level, the sequence of processing is pre-determined - unless I can re-order it into a more traditional hierarchy (1 entire branch at a time) and number the levels, in which case the cursor could just skip to the next branch of equal or higher level.

Note: The thought does occur to me I could have an intermediary function (static name) that in turn does the Dynamic SQL. These functions contain the Business Logic that looks at a myriad of column values and relationships in the database and there's no one-size-fits-all decision tree so Dynamic SQL is necessary.

The max cursor size will be maybe 300, and on average 100. Number of levels will normally be 3 or 4, but conceivably could be up to 10. Given the average 100 potential components/sub-assemblies, the final assembly will be about 30. As a periodic background process; it will do 3,000 Order Line Items a day, so I'm figuring 1 second response time per build is adequate (ie: the user's not waiting on it so it doesn't have to be blinding fast) - however why waste?

Anyhow; I thought this might be a fun problem for some Data Structure genius who wants to give a lesson in Relational Programming.

Thanks for looking.

Here's what I have so far:

CREATE PROCEDURE dbo.sp_ExplodeTest1 (@.recID int = 1) AS
/* tbTestH is a table containing an assembly hierarchy.
Assemblies with no parent are Builds.
Assemblies with no children are Components.
It's columns:
MyID int,
ParentID int,
(other descriptive columns)
*/

declare @.t table (
TempNodeID int identity(1,1)
,MyID int
)

-- Seed the tree with the Build's ID.
insert into @.t (MyID) values (@.recID)

/* This populates the temp table with the entire
Assembly for the given build.
It is Ordered By the level in the assembly.
Number of assembly levels is infinite.

For example:
Level 1 = the Build. It comes first
Level 2 = all parents are level 1. Is next (no particular seq)
Level 3 = all parents are level 2. Is next (no particular seq)
etc.

*/
while @.@.Rowcount > 0
insert into @.t (MyID)
select tbTestH.MyID
from tbTestH
inner join @.t rList on tbTestH.ParentID = rList.MyID
where not exists (
select *
from @.t CurrentList
where CurrentList.MyID = tbTestH.MyID)

-- now to display the results so far.
select t.*, tbTestH.*
from tbTestH inner join @.t t on tbTestH.MyID = t.MyID
order by t.TempNodeID

GOOk, well you base code looks fine. But I don't understand what you are trying to do next.

Do you want to use one or more functions to eliminate a branch and all of its children from the dataset you have created? Why not just embed the function in the code you use to populate the table, filtering out all and excluding all those that fail your function tests?

And thanks for the beer.|||Ok, well you base code looks fine. But I don't understand what you are trying to do next.

Do you want to use one or more functions to eliminate a branch and all of its children from the dataset you have created? Why not just embed the function in the code you use to populate the table, filtering out all and excluding all those that fail your function tests?

And thanks for the beer.
You're welcome and well deserved.

I'm not sure about the ultimate possibilities, but if I'm forced with the cursor approach then you are right, I could just skip them. Deleting from the list (or flagging for deletion, since that would have less overhead) only has value if there will be multiple passes, particularly using cursors. In other words, I don't want to repeat work.

I'm realizing that if I have 3000 Order Line Items to process, that I may be able to do them all in one swoop (attaching the OrderLineItemID to each row of course).

Each node of the final build will have a quantity and size that also require calculation. I've defined 15 different methods for calculating quantity and size (example: same as height, same as width, xref-lookup based on height, xref-lookup based on width, xref-lookup based on height/width combo, hard coded at 1, etc.). As well; each component translates (either directly or via a color-xref lookup) into an actual Product ID.

In cases where the node is not a component (ie: has children), it's quantity is used as a multiplier for it's branch. Example; if there's 4 "ladder assemblies", and each ladder has 2 plastic plugs, then the "plastic plug" quantity will be 8. Therefore; the heirchy probably needs to be traversed in sequence - unless I can think of a fancy single update with group-by statement that zooms it all together.

I'm afraid my brain is incapable of looking at the problem and knowing just how to best solve it. I plan to use this remainder of this week to work that out. If someone here is inclined to help out, I'll be most grateful (hey; I'll hand out a whole page of beers for an elegant solution).

Not sure if I've given enough info for the next step. I guess that would be either
1. Ordering this into hierarchical sequence and assigning level numbers
2. Ordering this into hierarchical sequence and assigning high-low node numbers.
Hopefully; with a nice single-SQL update statement (or series of statements like Blindman's WHILE ... INSERT).

My goal isn't the most elegant solution in the world, but one that'll work pretty well for the problem at hand (BOM, MRP, Forcasting system for 35m/yr custom build-to-order manufacturer). By "well", I mean modestly fast, scalable, maintainable, modular, flexible, and accurate.

Thanks!|||So you want to run different function tests depending upon whether the node has children or not?
I still do not see why you think you need a cursor for this.|||So you want to run different function tests depending upon whether the node has children or not?
I still do not see why you think you need a cursor for this.

I believe it's not 1 level of children.....|||So you want to run different function tests depending upon whether the node has children or not?
I still do not see why you think you need a cursor for this.
Each node, regardless of it's status (Tree, Branch, or Leaf) may be either:
1. Always present, given that it's parent is present.
or
2. Optionally present, dependent upon the Y/N result of a custom function.

The fuction for each node is custom only to that node.

Example:

CREATE FUNCTION fn_YN_10 (parms...)
RETURNS bit AS
BEGIN
DECLARE @.MyAnswer bit
SET @.MyAnswer = 1
IF (SELECT MySpecialFeature from tbLineItem where LineItemID = @.parmLineItemID) = "S"
SET @.MyAnswer = 0

RETURN @.MyAnswer

END

The name "fn_YN_10" is a value on the tbAssy. How can I execute that except within a Cursor? Please illuminate?

Also; I need to skip the entire branch if the answer is 1 (ie: No). One method to do that is have the Cursor order everything in hierarchical sequence(everything for a branch together) and process it in traditional loop fashion and with the levels numbered (tree = 1, next branch = 2, leafs = some higher number). If the answer is 1 (false) then it skips everything until the next node at the same or a lower level.

In skipping a branch; I could possibly add to a 2nd table of "Excluded Branches" and have a select with "NOT EXISTS". I believe there's a way to do that HOWEVER; if I have to use a cursor to get the Dynamic SQL anyway, then the first approach makes the most sense.

Note: If I do process 3000 Line items simultaneously, with avg 100 nodes each, we're talking 300,000 nodes. Now it's time to address performance. If I can avoid a Dynamic SQL Cursor, then by all means, that's the way to go. Not sure if doing all at once will make things faster or just cause indexing problems.

One possible solution: I could create an intermediate fuction to which the Function Name (and applicable parms) is passed. That would have a static name. It could then do the Dynamic SQL. That would avoid forcing the processing to use a CURSOR just because of the need for Dynamic SQL. But then it's back to how I can skip a branch without doing a Cursor Loop.|||I believe it's not 1 level of children.....
Correct:

Level 1 = tree
Level 2 or higher = branch
Level 2 or higher = leaf

Children always have higher number so by definition cannot be 1.|||but are we talking about children, grand children, great grandchildren, ect|||but are we talking about children, grand children, great grandchildren, ect
Yes.

Example of the tree representation:
Tree = no parent
Branch = has parent and child
Leaf = no child

01 (tree's root - level 1)
..02 (leaf - level 2)
..03 (branch - level 2)
...04 (branch - level 3)
.....05 (leaf - level 4)
......06 (leaf - level 5)
..07 (branch - level 2)
...08 (branch - level 3)
.....09 (leaf - level 4)
.....09 (leaf - level 4)
..10 (leaf - level 2)
..11 (branch - level 2)
...12 (leaf - level 3)

The child, father, grandfather, great grandfather traverses in the other direction and as such, has limitations
01 (great grandfather)
..02 (child) oops, this doesn't really work ...
..03 (grandfather)
...04 (father)
.....05 (child) oops, this doesn't work either
......06 (child) grandchild? doesn't fit.
etc

So; Child, Father, Grandfather notation works when referencing the lineage of a single elementry component, but not for representing a jagged hierarchy from it's highest level on down.

I see where child needs to be level 01 and so on using this notation method, ergo the confusion.

My bad for using the Child/Parent notation. I should have stuck with the Tree notation. Sorry about the confusion.

Data Source Error

When I click on a report, the following error occurs.
How should I resolve this matter?
ERROR:
An error has occurred during report processing.
Cannot create a connection to data source 'IPC_VISION_DEV'.
For more information about this error navigate to the report server on the
local server machine, or enable remote errorsAre you sure that the specified credentials for that data set are valid?|||Thanks!
However, the user did not have dbread/write on backend database.
Again, thanks!
"F. Dwarf" wrote:
> Are you sure that the specified credentials for that data set are valid?

Data Processing Script Justs Stops

I have a problem with a stored procdure I wrote. This proc will
process 80 to 100 million every night, it aggregates, inserts into a
different table, and then deletes the original data. The process will
hang after processing 20 to 40 million records. I must stop the proc
and then restart it before I can complete the process.

Any ideas why it would hang?

Current Specs:
Hardware: Compaq Proliant 370 - Dual 933MHz - 1GB or Ram - 35 gig Raid
5
OS: Windows 2k SP4
DB: MS SQL 2000 Enterprise SP3Not without seeing the SP.
Maybe the transaction log is unable to grow?

Nigel Rivett
www.nigelrivett.net

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Data processing extensions on SQL Server Express Edition?

I developed nice reports using custom data processing extensions. When I deployed the reports on my report server (I am using the express edition of SQL Server 2005) I was surprised to see that my reports were not rendering successfully.

After searching the web, I found this page listing the supported/unsupported features of SQL Server 2005 Express Edition: http://msdn2.microsoft.com/en-us/library/ms365166.aspx

On this page it clearly says “The Reporting Services API extensible platform for delivery, data processing, rendering, and security is not supported.”

Is there a way to get my reports to work on the express edition?

If not, which minimal version of SQL Server should a buy to get it to work (workgroup, standard or enterprise)?

Thanks for your help.

You will need Standard or Enterprise edition:

http://msdn2.microsoft.com/en-us/library/ms143761.aspx

Data Processing Extensions and Data Sources

Anyone know about DPE? Specifically, I want to know if it can help me to
make a data source dynamic in a multi-user environment.
I need to integrate SRS into my web app, which allows the user to select a
database from a list before running a report. Several people could try to
run the same report at the same time. If I use the API
"SetDataReportSources" then this switches the data source for all users of
the report. As noted before in this group, this is a limitation of SQL RS.
What I really want to know is: Can I develop a DPE that allows me to
dynamically set the data source at run-time? This is a requirement for my
application, and I want to avoid creating multiple copies of reports or
passing the data source in a stored procedure. Either of these work-arounds
involve more maintenance and hassle for the future. Can DPE allow me to make
a permanent solution?
I looked in the books online, and read about implementing a connection
class. If I made my own connection object, could I set it dynamically at run
time?
Hopefully,
MalikI'm trying to do the same thing as well but we havn't had the time or
resource yet to finish it off. Once I have a DPE running I would be happy to
share it on gotdotnet. Or if you get there first maybe you could to.
Try this link for further info. This is where I got started. I have aslo
uploaded some VB code to Gavin's page to complement the C# he has already.
It's still not as straight forward as you might hope but it will acomplish
what you are after.
http://weblogs.asp.net/gavinjoyce/archive/2004/01/29/64339.aspx?Pending=true
Regards
Toby,.
"Abdul Malik Said" <diplacusis@.hotmNOSPAMail.com> wrote in message
news:O4sJVKDZEHA.556@.tk2msftngp13.phx.gbl...
> Anyone know about DPE? Specifically, I want to know if it can help me to
> make a data source dynamic in a multi-user environment.
> I need to integrate SRS into my web app, which allows the user to select a
> database from a list before running a report. Several people could try to
> run the same report at the same time. If I use the API
> "SetDataReportSources" then this switches the data source for all users of
> the report. As noted before in this group, this is a limitation of SQL RS.
> What I really want to know is: Can I develop a DPE that allows me to
> dynamically set the data source at run-time? This is a requirement for my
> application, and I want to avoid creating multiple copies of reports or
> passing the data source in a stored procedure. Either of these
work-arounds
> involve more maintenance and hassle for the future. Can DPE allow me to
make
> a permanent solution?
> I looked in the books online, and read about implementing a connection
> class. If I made my own connection object, could I set it dynamically at
run
> time?
> Hopefully,
> Malik
>

Data Processing Extensions - Parameters ?

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

data processing extension sample

I'm trying to write some code to retrieve a reports list from a report folder.
Is it possible to create data processing extension in old asp? Either yes
or no, could someone post a link to some sample code, aspx samples would be
just fine?I forgot to mention that I only know VB. So please provide the samples in
VB. Thx a mil!
"JL" wrote:
> I'm trying to write some code to retrieve a reports list from a report folder.
> Is it possible to create data processing extension in old asp? Either yes
> or no, could someone post a link to some sample code, aspx samples would be
> just fine?|||http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
try this one. the best i have found.
[]s
Renato
"JL" <JL@.discussions.microsoft.com> escreveu na mensagem
news:1E1F4C39-7C3E-4263-B883-D2865507C968@.microsoft.com...
> I forgot to mention that I only know VB. So please provide the samples in
> VB. Thx a mil!
> "JL" wrote:
> > I'm trying to write some code to retrieve a reports list from a report
folder.
> >
> > Is it possible to create data processing extension in old asp? Either
yes
> > or no, could someone post a link to some sample code, aspx samples would
be
> > just fine?|||Thx a lot. But I only want to retrieve the reports list from a report
folder. Do I need to use the same extension? Or is there an alternative
(easier) way? Thx.
"Renato Aloi" wrote:
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> try this one. the best i have found.
> []s
> Renato
> "JL" <JL@.discussions.microsoft.com> escreveu na mensagem
> news:1E1F4C39-7C3E-4263-B883-D2865507C968@.microsoft.com...
> > I forgot to mention that I only know VB. So please provide the samples in
> > VB. Thx a mil!
> >
> > "JL" wrote:
> >
> > > I'm trying to write some code to retrieve a reports list from a report
> folder.
> > >
> > > Is it possible to create data processing extension in old asp? Either
> yes
> > > or no, could someone post a link to some sample code, aspx samples would
> be
> > > just fine?
>
>|||I saw a sample that demonstrate this... But I don't remmember where... What
I remmember is that you can write some code to do that, using ListChildren
method of Reporting Service Web Server, like this:
dim items() as CatalogItem
items = (new ReportingService).ListChildren("/", True)
then populate a list:
for each item as CatalogItem in items
cboWhatever.Items.Add(new ReportItem(item.name, item.Path))
next
That is it. Do not forget to reference the RS web service...
[]s
Renato
"JL" <JL@.discussions.microsoft.com> escreveu na mensagem
news:F408C4D6-DB48-40F1-A6A5-22BE8E958114@.microsoft.com...
> Thx a lot. But I only want to retrieve the reports list from a report
> folder. Do I need to use the same extension? Or is there an alternative
> (easier) way? Thx.
> "Renato Aloi" wrote:
> >
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> >
> > try this one. the best i have found.
> >
> > []s
> > Renato
> >
> > "JL" <JL@.discussions.microsoft.com> escreveu na mensagem
> > news:1E1F4C39-7C3E-4263-B883-D2865507C968@.microsoft.com...
> > > I forgot to mention that I only know VB. So please provide the
samples in
> > > VB. Thx a mil!
> > >
> > > "JL" wrote:
> > >
> > > > I'm trying to write some code to retrieve a reports list from a
report
> > folder.
> > > >
> > > > Is it possible to create data processing extension in old asp?
Either
> > yes
> > > > or no, could someone post a link to some sample code, aspx samples
would
> > be
> > > > just fine?
> >
> >
> >

Data Processing Extension not visible

hello,

I have written a Custom Data Processing Extension for SSRS 2005.

The Report Server is in SharePoint integrated mode, this works perfect except for the extension.

When I want to set a DataSource in SharePoint (WSS 3.0), the extension is not in the ComboBox.

This works well on another server with the same extension.

The DLL file of that extension is copied in the reportserver bin folder.

I have added the references in the rsreportserver.config and rssrvpolicy.config (see below).

Code Snippet

rsreportserver.config

<Data>

<Extension Name="SPSLISTS" Type="ICom.ReportingServices.SharepointListsExtension.Connection,

ICom.ReportingServices.SharepointListsExtension" />

<Data>

Code Snippet

rssrvpolicy.config

This code is located inside the CodeGroup with Name="SharePoint_Server_Strong_Name".

<CodeGroup class="UnionCodeGroup"

version="1"

PermissionSetName="FullTrust"

Name="SPSLISTS_CodeGroup"

Description="Code group for my SPS LISTS data processing extension">

<IMembershipCondition class="UrlMembershipCondition"

version="1"

Url="D:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin\ICom.ReportingServices.SharepointListsExtension.dll"

/>

</CodeGroup>

I already tried re-installing Reporting Services and the add-in for SharePoint but the problem remains.

Does anyone has an idea of what's wrong here?

Thanks in advance,

Tom

Found the solution, NTFS permissions needed to be set.

|||Hi Tom.

I was wondering if you could elaborate on your solution. I have run into this same problem and am unsure of what NTFS permissions you are talking about. Could you clear that up for me, as I think it would solve my problem as well. Thanks!

V

Data Processing Extension in SSRS05

Hi,

I'm trying to deploy my assembly (work fine in Report Manager) into Report Server, but I can't.

I followed the steps in help to deploy, but didn't work. When I run my report, show-me: "An attempt has been made to use a data extension 'GF' that is not registered for this report server". If I go to "New Data Source", my extension don't appear in "Connection Type".

Thanks,

Cristiano Leite

Did you verify that the data extensions works successfully in report designer (see: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_dataproc_9cds.asp)?

If it works in report designer, make sure to follow the steps for the server deployment, described in http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_dataproc_1fn5.asp

-- Robert

|||

Thanks Robert.

I found the error. I'm using a LogEvent class who is found in report designer, but isn't trusted into Report Server.

I commented this code end work fine.

Thanks

Cristiano Leite.

Data Processing Extension for ADO.NET?

Is there a data processing extension for use with an ADO.NET provider? MSDN
lists ones for SQL Server, OLEDB, Oracle, and ODBC.. ADO.NET is a glaring
omission.
The extension interfaces (IDbConnection, etc) are all similar to ADO.NET's.
I could write a pretty thin custom extension wrapper around an ADO.NET
provider, but why should I have to?Ah, it says you can use a .NET data provider directly from Reporting
Services, without a data processing extension. However, how do you let
Reporting Services know about your provider?
"Daniel Michaeloff" wrote:
> Is there a data processing extension for use with an ADO.NET provider? MSDN
> lists ones for SQL Server, OLEDB, Oracle, and ODBC.. ADO.NET is a glaring
> omission.
> The extension interfaces (IDbConnection, etc) are all similar to ADO.NET's.
> I could write a pretty thin custom extension wrapper around an ADO.NET
> provider, but why should I have to?|||When using a managed provider, you can either use it directly or write a
custom data extension which internally uses the provider. In general you can
use most .NET data providers (which implement System.Data.IDBConnection,
etc.). A .NET data provider would need to be registered in both,
rsReportDesigner.config and rsReportServer.config.
Lets assume your .NET provider has the assembly name "MyAssembly" and the
actual class that implements IDBConnection is called
"MyAssembly.MyConnection". Here is how you would register it:
* RSReportServer.config:
<Extension Name="MyProvider"
Type="MyAssembly.MyConnection,MyAssembly"/>
* RSReportDesigner.config
The registration for report designer would need to happen in two places - in
the <Data> section and in the <Designer> section.
<Data>
...
<Extension Name="MyProvider"
Type="MyAssembly.MyConnection,MyAssembly"/>
</Data>
<Designer>
...
<Extension Name="MyProvider"
Type="MyAssembly.MyConnection,MyAssembly"/>
</Designer>
After registering, you will get a new entry "MyProvider" in the data source
type drop-down list.
See also:
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_8iqq.asp
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Daniel Michaeloff" <DanielMichaeloff@.discussions.microsoft.com> wrote in
message news:BD89B815-BC0E-4ABD-9A36-07F7B31A303B@.microsoft.com...
> Is there a data processing extension for use with an ADO.NET provider?
> MSDN
> lists ones for SQL Server, OLEDB, Oracle, and ODBC.. ADO.NET is a glaring
> omission.
> The extension interfaces (IDbConnection, etc) are all similar to
> ADO.NET's.
> I could write a pretty thin custom extension wrapper around an ADO.NET
> provider, but why should I have to?|||Correction to my posting below - the <Designer> element in
RSReportDesigner.config should have the following contents:
<Designer>
...
<Extension Name="MyProvider"
Type="Microsoft.ReportDesigner.Design.GQDQueryDesigner,Microsoft.ReportingServices.Designer"/>
</Designer>
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23qgs5sXXFHA.3280@.TK2MSFTNGP09.phx.gbl...
> When using a managed provider, you can either use it directly or write a
> custom data extension which internally uses the provider. In general you
> can use most .NET data providers (which implement
> System.Data.IDBConnection, etc.). A .NET data provider would need to be
> registered in both, rsReportDesigner.config and rsReportServer.config.
> Lets assume your .NET provider has the assembly name "MyAssembly" and the
> actual class that implements IDBConnection is called
> "MyAssembly.MyConnection". Here is how you would register it:
> * RSReportServer.config:
> <Extension Name="MyProvider"
> Type="MyAssembly.MyConnection,MyAssembly"/>
> * RSReportDesigner.config
> The registration for report designer would need to happen in two places -
> in the <Data> section and in the <Designer> section.
> <Data>
> ...
> <Extension Name="MyProvider"
> Type="MyAssembly.MyConnection,MyAssembly"/>
> </Data>
> <Designer>
> ...
> <Extension Name="MyProvider"
> Type="MyAssembly.MyConnection,MyAssembly"/>
> </Designer>
> After registering, you will get a new entry "MyProvider" in the data
> source type drop-down list.
> See also:
> http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_8iqq.asp
>
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Daniel Michaeloff" <DanielMichaeloff@.discussions.microsoft.com> wrote in
> message news:BD89B815-BC0E-4ABD-9A36-07F7B31A303B@.microsoft.com...
>> Is there a data processing extension for use with an ADO.NET provider?
>> MSDN
>> lists ones for SQL Server, OLEDB, Oracle, and ODBC.. ADO.NET is a glaring
>> omission.
>> The extension interfaces (IDbConnection, etc) are all similar to
>> ADO.NET's.
>> I could write a pretty thin custom extension wrapper around an ADO.NET
>> provider, but why should I have to?
>|||Thanks Robert, this is what I needed.
However, is there a way to avoid putting our provider assemblies in the
global assembly cache?
"Robert Bruckner [MSFT]" wrote:
> Correction to my posting below - the <Designer> element in
> RSReportDesigner.config should have the following contents:
> <Designer>
> ...
> <Extension Name="MyProvider"
> Type="Microsoft.ReportDesigner.Design.GQDQueryDesigner,Microsoft.ReportingServices.Designer"/>
> </Designer>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:%23qgs5sXXFHA.3280@.TK2MSFTNGP09.phx.gbl...
> > When using a managed provider, you can either use it directly or write a
> > custom data extension which internally uses the provider. In general you
> > can use most .NET data providers (which implement
> > System.Data.IDBConnection, etc.). A .NET data provider would need to be
> > registered in both, rsReportDesigner.config and rsReportServer.config.
> >
> > Lets assume your .NET provider has the assembly name "MyAssembly" and the
> > actual class that implements IDBConnection is called
> > "MyAssembly.MyConnection". Here is how you would register it:
> > * RSReportServer.config:
> > <Extension Name="MyProvider"
> > Type="MyAssembly.MyConnection,MyAssembly"/>
> >
> > * RSReportDesigner.config
> > The registration for report designer would need to happen in two places -
> > in the <Data> section and in the <Designer> section.
> >
> > <Data>
> > ...
> > <Extension Name="MyProvider"
> > Type="MyAssembly.MyConnection,MyAssembly"/>
> > </Data>
> > <Designer>
> > ...
> > <Extension Name="MyProvider"
> > Type="MyAssembly.MyConnection,MyAssembly"/>
> > </Designer>
> >
> > After registering, you will get a new entry "MyProvider" in the data
> > source type drop-down list.
> >
> > See also:
> > http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_8iqq.asp
> >
> >
> > --
> > Robert M. Bruckner
> > Microsoft SQL Server Reporting Services
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> >
> >
> > "Daniel Michaeloff" <DanielMichaeloff@.discussions.microsoft.com> wrote in
> > message news:BD89B815-BC0E-4ABD-9A36-07F7B31A303B@.microsoft.com...
> >> Is there a data processing extension for use with an ADO.NET provider?
> >> MSDN
> >> lists ones for SQL Server, OLEDB, Oracle, and ODBC.. ADO.NET is a glaring
> >> omission.
> >>
> >> The extension interfaces (IDbConnection, etc) are all similar to
> >> ADO.NET's.
> >> I could write a pretty thin custom extension wrapper around an ADO.NET
> >> provider, but why should I have to?
> >
> >
>
>|||Actually, I'm not getting Report Designer to pick up my assembly from the
GAC, and I don't see anything like "Microsoft.ReportingServices" in the GAC
either. I've also tried dropping my dll in Report Designer's main directory.
Where does Report Designer / Reporting Services look for extensions?
"Daniel Michaeloff" wrote:
> Thanks Robert, this is what I needed.
> However, is there a way to avoid putting our provider assemblies in the
> global assembly cache?
> "Robert Bruckner [MSFT]" wrote:
> > Correction to my posting below - the <Designer> element in
> > RSReportDesigner.config should have the following contents:
> >
> > <Designer>
> > ...
> > <Extension Name="MyProvider"
> > Type="Microsoft.ReportDesigner.Design.GQDQueryDesigner,Microsoft.ReportingServices.Designer"/>
> > </Designer>
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> > news:%23qgs5sXXFHA.3280@.TK2MSFTNGP09.phx.gbl...
> > > When using a managed provider, you can either use it directly or write a
> > > custom data extension which internally uses the provider. In general you
> > > can use most .NET data providers (which implement
> > > System.Data.IDBConnection, etc.). A .NET data provider would need to be
> > > registered in both, rsReportDesigner.config and rsReportServer.config.
> > >
> > > Lets assume your .NET provider has the assembly name "MyAssembly" and the
> > > actual class that implements IDBConnection is called
> > > "MyAssembly.MyConnection". Here is how you would register it:
> > > * RSReportServer.config:
> > > <Extension Name="MyProvider"
> > > Type="MyAssembly.MyConnection,MyAssembly"/>
> > >
> > > * RSReportDesigner.config
> > > The registration for report designer would need to happen in two places -
> > > in the <Data> section and in the <Designer> section.
> > >
> > > <Data>
> > > ...
> > > <Extension Name="MyProvider"
> > > Type="MyAssembly.MyConnection,MyAssembly"/>
> > > </Data>
> > > <Designer>
> > > ...
> > > <Extension Name="MyProvider"
> > > Type="MyAssembly.MyConnection,MyAssembly"/>
> > > </Designer>
> > >
> > > After registering, you will get a new entry "MyProvider" in the data
> > > source type drop-down list.
> > >
> > > See also:
> > > http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_8iqq.asp
> > >
> > >
> > > --
> > > Robert M. Bruckner
> > > Microsoft SQL Server Reporting Services
> > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > >
> > >
> > >
> > > "Daniel Michaeloff" <DanielMichaeloff@.discussions.microsoft.com> wrote in
> > > message news:BD89B815-BC0E-4ABD-9A36-07F7B31A303B@.microsoft.com...
> > >> Is there a data processing extension for use with an ADO.NET provider?
> > >> MSDN
> > >> lists ones for SQL Server, OLEDB, Oracle, and ODBC.. ADO.NET is a glaring
> > >> omission.
> > >>
> > >> The extension interfaces (IDbConnection, etc) are all similar to
> > >> ADO.NET's.
> > >> I could write a pretty thin custom extension wrapper around an ADO.NET
> > >> provider, but why should I have to?
> > >
> > >
> >
> >
> >|||Okay, I tried actually following that link you gave. Sorry.
"Daniel Michaeloff" wrote:
> Actually, I'm not getting Report Designer to pick up my assembly from the
> GAC, and I don't see anything like "Microsoft.ReportingServices" in the GAC
> either. I've also tried dropping my dll in Report Designer's main directory.
> Where does Report Designer / Reporting Services look for extensions?
> "Daniel Michaeloff" wrote:
> > Thanks Robert, this is what I needed.
> >
> > However, is there a way to avoid putting our provider assemblies in the
> > global assembly cache?
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Correction to my posting below - the <Designer> element in
> > > RSReportDesigner.config should have the following contents:
> > >
> > > <Designer>
> > > ...
> > > <Extension Name="MyProvider"
> > > Type="Microsoft.ReportDesigner.Design.GQDQueryDesigner,Microsoft.ReportingServices.Designer"/>
> > > </Designer>
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >
> > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> > > news:%23qgs5sXXFHA.3280@.TK2MSFTNGP09.phx.gbl...
> > > > When using a managed provider, you can either use it directly or write a
> > > > custom data extension which internally uses the provider. In general you
> > > > can use most .NET data providers (which implement
> > > > System.Data.IDBConnection, etc.). A .NET data provider would need to be
> > > > registered in both, rsReportDesigner.config and rsReportServer.config.
> > > >
> > > > Lets assume your .NET provider has the assembly name "MyAssembly" and the
> > > > actual class that implements IDBConnection is called
> > > > "MyAssembly.MyConnection". Here is how you would register it:
> > > > * RSReportServer.config:
> > > > <Extension Name="MyProvider"
> > > > Type="MyAssembly.MyConnection,MyAssembly"/>
> > > >
> > > > * RSReportDesigner.config
> > > > The registration for report designer would need to happen in two places -
> > > > in the <Data> section and in the <Designer> section.
> > > >
> > > > <Data>
> > > > ...
> > > > <Extension Name="MyProvider"
> > > > Type="MyAssembly.MyConnection,MyAssembly"/>
> > > > </Data>
> > > > <Designer>
> > > > ...
> > > > <Extension Name="MyProvider"
> > > > Type="MyAssembly.MyConnection,MyAssembly"/>
> > > > </Designer>
> > > >
> > > > After registering, you will get a new entry "MyProvider" in the data
> > > > source type drop-down list.
> > > >
> > > > See also:
> > > > http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_8iqq.asp
> > > >
> > > >
> > > > --
> > > > Robert M. Bruckner
> > > > Microsoft SQL Server Reporting Services
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > > rights.
> > > >
> > > >
> > > >
> > > > "Daniel Michaeloff" <DanielMichaeloff@.discussions.microsoft.com> wrote in
> > > > message news:BD89B815-BC0E-4ABD-9A36-07F7B31A303B@.microsoft.com...
> > > >> Is there a data processing extension for use with an ADO.NET provider?
> > > >> MSDN
> > > >> lists ones for SQL Server, OLEDB, Oracle, and ODBC.. ADO.NET is a glaring
> > > >> omission.
> > > >>
> > > >> The extension interfaces (IDbConnection, etc) are all similar to
> > > >> ADO.NET's.
> > > >> I could write a pretty thin custom extension wrapper around an ADO.NET
> > > >> provider, but why should I have to?
> > > >
> > > >
> > >
> > >
> > >

Data Processing Extension and query designer

hi, I have successfully implemented my custom data processing extension for
Reporting Services (SQL Server 2005) and now I want to explore how to
implement my own query designer as well.
I did get a sample of how to do that, but the sample is targeted for
Reporting Services 2000. I could somehow modify it to suit my needs, but I
have a simple question. The sample I got shows how to execute the query to
get the IDataReader object and fill up the DataGrid. Now I want to try the
new DataGridView control instead, but is there a better way than the
following.
The major reason why I ask is that I find the data shown in the grid is one
row at a time (i.e. very SLOW).
IDataReader reader = null;
try
{
reader = command.ExecuteReader(CommandBehavior.SingleResult);
System.Data.DataTable dataTable = new System.Data.DataTable();
for (int i = 0; i < reader.FieldCount; i++)
{
string fieldName = reader.GetName(i);
dataTable.Columns.Add(fieldName);
}
while (reader.Read())
{
System.Data.DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
{
dataRow[i] = reader.GetValue(i);
}
dataTable.Rows.Add(dataRow);
}
dataGridView.DataSource = dataTable;
}
finally
{
if (reader != null)
{
reader.Dispose();
}
}
Any help is appreciated!!Hi,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood your main concern is the performance
for executing the query to
get the IDataReader object and fill up the DataGrid is very slow. If I have
misunderstood your concern, please feel free to point it out.
I have reviewed the your codes and it is OK. Maybe you should consider use
a stored procedure with SELECT * statement to get the result from resultset
at once instead of getting the data row one by one.
Sincerely yours,
Michael Cheng
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.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for your prompt reply. However, I don't think getting results back
from my data extension is slow ... when I switched back to the generic query
designer in Report Designer, the time taken to fill up the data grid is
instantaneous! But as soon as I use my custom query designer, I could see
how each data row is filled, one at a time (i.e. very slow).
Do you have any other suggestions?
Thank you for your help
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood your main concern is the performance
> for executing the query to
> get the IDataReader object and fill up the DataGrid is very slow. If I have
> misunderstood your concern, please feel free to point it out.
> I have reviewed the your codes and it is OK. Maybe you should consider use
> a stored procedure with SELECT * statement to get the result from resultset
> at once instead of getting the data row one by one.
>
> Sincerely yours,
> Michael Cheng
> 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.
> =====================================================> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
> Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
> If you are outside the United States, please visit our International
> Support page: http://support.microsoft.com/common/international.aspx
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Jenny,
Is it possible for you to generate a small sample of your custom Query
Designer for us to reproduce it on my side?
You may attach the zipped file here or send it to me directly. I understand
the information may be sensitive to you, my direct email address is
v-mingqc@.microsoft.com, you may send the file to me directly and I will
keep secure.
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
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.|||Thank you for your offer. I have sent you a sample already.
Just to make sure we are on the same page, I'm using VS .NET 2005 and SQL
Server 2005.
Thank you very much
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi Jenny,
> Is it possible for you to generate a small sample of your custom Query
> Designer for us to reproduce it on my side?
> You may attach the zipped file here or send it to me directly. I understand
> the information may be sensitive to you, my direct email address is
> v-mingqc@.microsoft.com, you may send the file to me directly and I will
> keep secure.
> If you have any questions or concerns, don't hesitate to let me know. We
> are always here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> 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.
>|||Hi Jenny,
Thanks for your email.
I am looking into this issue with the help of .Net Expert. I will keep you
updated as soon as I find anything valueable.
Thank you for your patience and cooperation.
Sincerely yours,
Michael Cheng
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.|||Hi Jenny
I have received your sample project, and build it without any error.
However, I am not sure how to use it to reproduce out your issues. It seems
that the project run type is "Class Library", Can you show me how to use
this class library? I see the entry point should be in CsvDesigner class,
however, it seems that you missed the code to bootstrap this class.
Sincerely yours,
Michael Cheng
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.|||Don't worry, Michael. I have figured it out myself.
The trick is to use the splitter control and make sure the datagridview
control dock property is set to 'fill' underneath the splitter.
Now the datagridview control is filled up instantly.
Cheers
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi Jenny
> I have received your sample project, and build it without any error.
> However, I am not sure how to use it to reproduce out your issues. It seems
> that the project run type is "Class Library", Can you show me how to use
> this class library? I see the entry point should be in CsvDesigner class,
> however, it seems that you missed the code to bootstrap this class.
>
> Sincerely yours,
> Michael Cheng
> 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.
>|||Hi Jenny,
Thanks so much for the update and it's great to hear you have resolved it
:) I this information is very helpful for those who encounter the same
problem.
Sincerely yours,
Michael Cheng
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.

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