Gavin Joyce's Blog

So...are you gonna share some code?

I have uploaded the source code - http://www.gavinjoyce.com/files/misc/DataSet.DataProcessingExtension.zip

could you tell me how to use it
thanks
terry@ss40.sertek.com.tw

Terry,

You can read up on Data Processing Extensions in the Reporting Services Books On-Line. Let me know if you have any specific questions.

Gavin

Do you have a URL for the books on-line?

How about some good web site or user group references?

Thanks,

Megan

Books On-Line:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPORTAL/HTM/rs_gts_portal_3vqd.asp

How do you pass parameters to your DAL? In your DPE, you do not implement iDBCommandAnalysis and the GetParameters() method.
Do you know if I need to parse the command text looking for Paramiters? What does GetParameters need to do? How did you solve this issue

Are U using the MS Report Designer to design your report?

If yes, as I found there is the only way to set up the report by input a SQL with data source; how can U set the report to read the dataset from your middle layer?

Dion,

Our RDL reports take a parameter called UserSessionStateXML. This is passed to the DPE as the CommandText, and is in turn passed to DAL. The DAL deserialises this XML and uses it to constrain the report data.

Thanks,
Gavin

Dick,

We do use the MS Report Designer to design the reports, but also edit the RDL XML manually. Specifically, we use the following as the DataSet definition:

<DataSources>
<DataSource Name="CustomDataSource">
<rd:DataSourceID>56ffab73-824a-4ad0-a69a-e1c3b7f575d4</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>CustomDataSetProvider</DataProvider>
<ConnectString />
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>

And the following as the Query:

<Query>
<DataSourceName>CustomDataSource</DataSourceName>
<CommandText>=Parameters!UserSessionStateXML.Value</CommandText>
<Timeout>300</Timeout>
</Query>

Thank you for your help!

U had mention serveral item in the RDL, I want to have a clear picture about them.

1. <DataSource Name="CustomDataSource">
is the word "CustomDataSource" user define or not? if yes, where we should define? can I see it in your "c# DPE doenload"?

2. <DataProvider>CustomDataSetProvider</DataProvider>
is the word "CustomDataSetProvider" user define or not? if yes, where we should define? can I see it in your "c# DPE doenload"?


3. <CommandText>=Parameters!UserSessionStateXML.Value</CommandText>
Where is the value for "Parameters!UserSessionStateXML.Value" come from?
is it the parmeter coming from the report?

4. if I have a middle layer, how can the middle layer function be called?

Gavin,

I am trying to use an xml file as the basis for report data and an ADO.NET dataset appears to be the way to do it. Can you please provide some insite as to the steps I would need to take to do this?

Thanks,

Mike

Can show me what your CommandText looks like with the parameter UserSessionStateXML
and what is FilterDataDOM?

I try to implement IDbCommandAnalysis to get the parameter from report designer. The coding is as following in Command.vb:

***************
Public Function GetParameters() As Microsoft.ReportingServices.DataProcessing.IDataParameterCollection Implements Microsoft.ReportingServices.DataProcessing.IDbCommandAnalysis.GetParameters
Dim abc As Microsoft.ReportingServices.DataProcessing.IDataParameterCollection
Dim para As Mcrosoft.ReportingServices.DataProcessing.IDataParameter

para.ParameterName = "HELLO"
para.Value = "ABC"
abc.Add(para)

Return abc
End Function
***************************

After I deploy the DLL to report designer and run the query, a message "An error ocurred while retrieving the parameters in the query. Object reference not set to an instance of an object." is prompted.
Can anyone tell me why?????
Anyone has tried to implement IDbCommandAnalysis , can U share some sample coding to me??

Hi,Gavin Joyce

is the word "rd:DataSourceID" user define or not? if yes, where we should define?


thanx for you work.

I mean the value:"56ffab73-824a-4ad0-a69a-e1c3b7f575d4"

Dick,

Both 'CustomDataSource' and 'CustomDataSetProvider' are custom names - you can change them to whatever you wish. You have to modify a number of XML configuration files to register your DPE. Please refer to the following links for more information:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_dataproc_5c2q.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_dataproc_5ug2.asp

Thanks,
Gavin

Mike,

You could possible load the XML file into a DataSet and then expose it through the DataSet DPE. Alternatively, you could write a DPE that exposed the data in the XML file directly. Please refer to the following URL for more information.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_dataproc_5c2q.asp

Thanks,
Gavin

Dion,

The CommandText looks as follows:

<Query>
<DataSourceName>CustomDataSource</DataSourceName>
<CommandText>=Parameters!UserSessionStateXML.Value</CommandText>
<Timeout>300</Timeout>
</Query>

The FilterDataDOM is an object graph which contains a user’s filter criteria. It is the UserSessionStateXML deserialised as an object graph. We had complex filter requirements like filter interdependencies and filter hierarchies in our project, so we rolled our own filtering mechanism. Whatever data you need to pass to your middle layer/data access layer has to be passed through the CommandText parameter. As this is a single string, an XML structure serialised as text is ideal.


Thanks,
Gavin

Felix,

The guid "56ffab73-824a-4ad0-a69a-e1c3b7f575d4" was added by the Reporting Services Designer.

Thanks,
Gavin

The Link for code download is not working.....
Kindly help me out!!!!!!!!!!!


Regards,
Shahzad Ahmad qureshi

We are an ASP and need to be able to run the same reports against multiple client database catalogs on the same machine and on other machines and don't want to replicate each report for each customer database. The report server won't know the database data source until the report is requested from the client application.

We can do this with crystal reports no problem but it seems messy when trying to do this with. SQL Reporting Services. (i.e. using the use statement as a parameter and linking SQL servers). There could be potentially hundreds of databases.

Could the DPE be used is this senario i.e. only to change the datasource at report render time and get the data direct from the database as normal.

Yes, you could use the DPE for this. When calling the report, pass in the datasource information in the CommandText parameter. Your DPE would then use this to query the correct datasource, populate a DataSet and return it.

I have two make use of two datasets.
One is from SQL Server Stored Procedure and another from Mainframe
Stored
procedure.

And one dataset's has to provide an input to another dataset and
accordingly has to get the values from it corresponding to the first
dataset value.
And the dataset in which I am giving the input from the first
dataset
doesn't display that column.

Bsed on above criteria I have to display that records.

Please suggest.

Ashwani,

I do not fully understand you post above.

You can customise the DPE to execute any business logic that your application requires. All it has to do is return a DataSet.

Thanks,
Gavin

Do you have the source code which is in VB.net(.VB)?

I also need it so could you please provide source code for DPE in VB.net.

Sorry, the DPE is only available in C#. You can easily convert it by hand.

I have done a VB conversion if anyone wants it? tell me where to upload it.

Toby, please send to me if you can
steve.flitcroft@escendency.com

Cheers

Toby, can you send it to me also?
eddie.jenkins@bdk.com

Toby, I can host this for you if you like. You can mail me at gavin@gavinjoyce.com.

Thanks,
Gavin

Gav, can you clear an issue up, I wish to connect to multiple sql server databases that all have the same schema just different clients, is writing an Data Processing Extension the best (only) way to go about being able to select a dynamic connection depending on the user logged into the system??
Cheers
Steve

Steve,

I am not completely sure that is the only way, but it is an elegant solutions to your problem.

Thanks,
Gavin

Steve, sounds like we have the same problem !! i'd be happy to share the code to do this once we have something up and running. There is also a work around on the newsgroup. using a parameter to set the database before executing the query. but it isn't very elegant & it won't work on stored procedures or across different servers (unless they are linked).
i.e

="use "& Parameters!Database.Value & " ; < query >"

I have uploaded Toby's VB version of the DPE to http://www.gavinjoyce.com/files/misc/DataSet.DataProcessingExtension.VB.zip. Thanks for the conversion Toby.

Gavin

Could you pls tell me how can I do processing with two datasets in this code?
I have to pass parameters to both of these datasets and has to make one.

Mary,

You will have to merge the two DataSets into one DataSet.

Thanks,
Gavin

Thats correct but at which place I should write that code?

Mary,

You could write your custom code in the DPE itself or in some external assembly or web service.

Do you have any idea regrading the 'Access Denied' error which I am getting while accessing the report(reporting services) through Web server.

I am calling my report through ASP page after preparing my parameters list and caling the Render function but getting the following error:

-------------------------
Server Error in '/ITSWeb' Application.
The request failed with HTTP status 401: Access Denied.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Net.WebException: The request failed with
HTTP status 401: Access Denied.
-----------------------
Source Error:
-------------------
Line 547:"",
RequestNamespace:="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"">http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices",
ResponseNamespace:="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"">http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices",
Use:=System.Web.Services.Description.SoapBindingUse.Literal,
ParameterStyle:=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)> _
Line 548: Public Function Render(ByVal Report As String, ByVal
Format As String, ByVal HistoryID As String, ByVal DeviceInfo As String,
ByVal Parameters() As ParameterValue, ByVal Credentials() As
DataSourceCredentials, ByVal ShowHideToggle As String, ByRef Encoding As String,
ByRef MimeType As String, ByRef ParametersUsed() As ParameterValue, ByRef
Warnings() As Warning, ByRef StreamIds() As String) As
<System.Xml.Serialization.XmlElementAttribute("Result", DataType:="base64Binary")>
Byte() Line 549: Dim results() As Object = Me.Invoke("Render",
New Object() {Report, Format, HistoryID, DeviceInfo, Parameters,
Credentials, ShowHideToggle}) Line 550: Encoding =
CType(results(1),String) Line 551: MimeType = CType(results(2),String)
Source File: C:\Inetpub\wwwroot\ITSWeb\Web
References\wRptService\Reference.vb Line: 549

------------------------
Stack Trace:
-----------------------
[WebException: The request failed with HTTP status 401: Access
Denied.]
System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream,
Boolean asyncCall)
System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
ITS.ITSWeb.wRptService.ReportingService.Render(String Report, String Format, String
HistoryID, String DeviceInfo, ParameterValue[] Parameters,
DataSourceCredentials[] Credentials, String ShowHideToggle, String& Encoding, String&
MimeType, ParameterValue[]& ParametersUsed, Warning[]& Warnings,
String[]& StreamIds) in C:\Inetpub\wwwroot\ITSWeb\Web
References\wRptService\Reference.vb:549 ITS.ITSWeb.wfOpenReport.cp_ShowReport() in
C:\Inetpub\wwwroot\ITSWeb\PetitionReport\wfOpenReport.aspx.vb:120
ITS.ITSWeb.wfOpenReport.Page_Load(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\ITSWeb\PetitionReport\wfOpenReport.aspx.vb:53
System.Web.UI.Control.OnLoad(EventArgs e) System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()

Mark,

Maybe you should try the ReportingServices newsgroup?

Thanks,
Gavin

Gavin,

Thanks for sharing your DPE code! I think I am trying to achieve the same thing that you have already achieved: I am frustrated with the way that Reporting Services parameters work, and would like to bypass them and provide my own interface to the user that allows them to build a complex WHERE clause on the fly. I understand your DPE code, but I'm confused about your "ASP.NET filter server control". Is this something that you plug in to customize the run-time Reporting Server interface? Or is it something that works at design time? I'm hoping to customize the run-time interface so that instead of seeing the Reporting Services parameters, the user sees my WHERE clause builder interface (hopefully the rest of the interface would stay the same). When my DPE is invoked, it would obtain the user's WHERE clause and use this to build the dataset. Is this possible? If so, how do I build and plug in my WHERE clause component? Thanks...

If I have a dataset with 2 tables, how do I select the fields from both tables in the designer?? i have tried Table1, Table2 in the query builder but it error's!!
Thanks

i am also struggling with the same problem , did u get any answer for this, if yes please share it with me


regards
venu gopal

No answer for it. Its better if u can go for Oracle Reprts instead of Reporting Services.

I am new to sql server reportig services, i want to connect datasource dynamically at the run time, i've downloaded the code also. but i am not getting how to use the same so that i'll be able to connect to diffrent datasource at run time while generating report. i've two database of same structure i want to connect the database as the name passed by user.

Sumit

hello ,
can anybody tell me
1) How to have 2 datasets/tables as input for my report .
2) How to link 2 tables in the report ( ie like inner join )

I am trying to pass a parameter containing a xml and I want to bind it on a dataset in my external dataset but I cant get the parameters.

ParameterValue[] pv=null;
if(txtParam.Text.Trim().Length>0)
{
pv = new ParameterValue[1];
pv[0] = new ParameterValue();
pv[0].Name = "costatus";
pv[0].Label = "costatus";
pv[0].Value = txtParam.Text.Trim().ToUpper();
}
ResultStream = rs.Render("/test/costatus", "PDF", null, "<DeviceInfo><StreamRoot>/ReportTest/</StreamRoot></DeviceInfo>", pv,null, null, out OptionalParam, out OptionalParam, out optionalParams,out optionalWarnings, out StreamIdentifiers);

// Write the report to Response
Response.BinaryWrite(ResultStream);

-----------
I had understood that you can only get something by the commantext but how can I send the commandtext trought this method.

The goal I whant to accomplish is to get a pdf directly from a report that was generated by an external dataset that gets his parameters from the middle tier.

Can anyone help me
thanks in advance

how to connect the crystal report into database sql server 2000 in asp.net

Pls let me know how i shd call a report developed using SQL Reporting Services in ASP.

Pls let me know how i shd call a report developed using SQL Reporting Services in ASP.

In Reporting Services i need to merge two datasets into one dataset so that in table i can display all the details. is this feature available in reporting services?

i am having mainly two tables, tblpurchaseorders,tbldeliverychallan. tblpurchaseorders contains pono,item name,item qty,item rate. tbldeliverychallan contains pono,dcno,dcdate.

Each po can have more than one dc.

Each po may/may not have charges and item taxes, these details available from different tables.poamount=itemqty*itemrate+charge amt+sum(itemtax)

using single query i am not able to get poamount correct. i need to have the details of pono,poamount,dcno,dcdate. with two queries ... one query for retreiving po details such as pono, poamount and another for retreiving dc details such as dcno,dcdate corresponding to the pos i am getting.

So kindly give me solution to the above problem... can i merge two datasets into one dataset and display the details in Table?

or any alternative solution ....

Waiting for reply from u ...

pulmicort. All about of pulmicort.

Wonderful and informative web site.I used information from that site its great.

My Catalogue site of Biker jacket leather

http://solisearch.net/bloghoster/?u=siniy

Hi

Can someone tell me how to wrap reporting services in plain ASP and not ASP.net?

is this possible to reporting services in a non .net environment?

Avesh

Lucy! Please call me,Lucy! Please call me

<a href= http://www.angelfire.com/goth/wagoly >aalborgs</a> <a href= http://www.angelfire.com/crazy/jukana >a level revision notes</a> <a href= http://www.angelfire.com/blog/wimide >a lot of love trailer</a> <a href= http://www.angelfire.com/funky/xocizo >aacharity.com car charitable charitable charity donate donation donation</a> <a href= http://www.angelfire.com/blog/dycyfe >a2 golf</a>

[URL=http://www.and2area.info/cremona] cremona [/URL]   <a href='http://www.and2area.info/cremona'> cremona </a> [URL=http://www.and2area.info/cafe] cafe [/URL]   <a href='http://www.and2area.info/cafe'> cafe </a> [URL=http://www.and2area.info/langue] langue [/URL]   <a href='http://www.and2area.info/langue'> langue </a> [URL=http://www.and2area.info/cori] cori [/URL]   <a href='http://www.and2area.info/cori'> cori </a> [URL=http://www.and2area.info/campagna] campagna [/URL]   <a href='http://www.and2area.info/campagna'> campagna </a> [URL=http://www.and2area.info/photos] photos [/URL]   <a href='http://www.and2area.info/photos'> photos </a> [URL=http://www.and2area.info/videos] videos [/URL]   <a href='http://www.and2area.info/videos'> videos </a> [URL=http://www.and2area.info/edili] edili [/URL]   <a href='http://www.and2area.info/edili'> edili </a> [URL=http://www.and2area.info/sharp] sharp [/URL]   <a href='http://www.and2area.info/sharp'> sharp </a> [URL=http://www.and2area.info/distributori] distributori [/URL]   <a href='http://www.and2area.info/distributori'> distributori </a> [URL=http://www.and2area.info/tastiera] tastiera [/URL]   <a href='http://www.and2area.info/tastiera'> tastiera </a> [URL=http://www.and2area.info/pierre] pierre [/URL]   <a href='http://www.and2area.info/pierre'> pierre </a> [URL=http://www.and2area.info/omega] omega [/URL]   <a href='http://www.and2area.info/omega'> omega </a> [URL=http://www.and2area.info/iscrizione] iscrizione [/URL]   <a href='http://www.and2area.info/iscrizione'> iscrizione </a> [URL=http://www.and2area.info/batteria] batteria [/URL]   <a href='http://www.and2area.info/batteria'> batteria </a> [URL=http://www.and2area.info/problemi] problemi [/URL]   <a href='http://www.and2area.info/problemi'> problemi </a> [URL=http://www.and2area.info/valuta] valuta [/URL]   <a href='http://www.and2area.info/valuta'> valuta </a> [URL=http://www.and2area.info/deejay] deejay [/URL]   <a href='http://www.and2area.info/deejay'> deejay </a> [URL=http://www.and2area.info/sigla] sigla [/URL]   <a href='http://www.and2area.info/sigla'> sigla </a> [URL=http://www.and2area.info/filmati] filmati [/URL]   <a href='http://www.and2area.info/filmati'> filmati </a> [URL=http://www.and2area.info/dopo] dopo [/URL]   <a href='http://www.and2area.info/dopo'> dopo </a> [URL=http://www.and2area.info/tipi] tipi [/URL]   <a href='http://www.and2area.info/tipi'> tipi </a> [URL=http://www.and2area.info/pittore] pittore [/URL]   <a href='http://www.and2area.info/pittore'> pittore </a> [URL=http://www.and2area.info/matera] matera [/URL]   <a href='http://www.and2area.info/matera'> matera </a> [URL=http://www.and2area.info/tunisia] tunisia [/URL]   <a href='http://www.and2area.info/tunisia'> tunisia </a>

<a href= http://www.angelfire.com/punk/zewato >a team jersey</a> <a href= http://www.angelfire.com/goth/gihoru >a best windows</a> <a href= http://www.angelfire.com/funky/gycadu >a d building</a> <a href= http://www.angelfire.com/indie/diguqy >a.c.e</a> <a href= http://www.angelfire.com/funky/tijevi >aaron kwok bio</a>

<a href= http://itrome.t35.com/finanziamento-casa/richiesta-finanziamento-prima-casa.html >richiesta finanziamento prima casa</a>   [url=http://itrome.t35.com/finanziamento-casa/richiesta-finanziamento-prima-casa.html]richiesta finanziamento prima casa[/url]   <a href= http://itrome.t35.com/finanziamento-impresa/finanziamento-impresa-giovanili.html >FINANZIAMENTO IMPRESA GIOVANILI</a>   [url=http://itrome.t35.com/finanziamento-impresa/finanziamento-impresa-giovanili.html]FINANZIAMENTO IMPRESA GIOVANILI[/url]   <a href= http://itrome.t35.com/finanziamento-casa/finanziamento-ristrutturazione-casa.html >finanziamento ristrutturazione casa</a>   [url=http://itrome.t35.com/finanziamento-casa/finanziamento-ristrutturazione-casa.html]finanziamento ristrutturazione casa[/url]   <a href= http://itrome.t35.com/foto-camera/fujifilm-fotocamera-digitali.html >fujifilm fotocamera digitali</a>   [url=http://itrome.t35.com/foto-camera/fujifilm-fotocamera-digitali.html]fujifilm fotocamera digitali[/url]  

Super *** and boobs: <a href=" http://cahn.mnsu.edu/msuatdisc/_msuatdisc/00000420.htm "> Big *** </a>. <a href=" http://cahn.mnsu.edu/msuatdisc/_msuatdisc/00000421.htm "> Huge *** </a>. <a href=" http://cahn.mnsu.edu/msuatdisc/_msuatdisc/00000425.htm "> mature *** </a>. <a href=" http://cahn.mnsu.edu/msuatdisc/_msuatdisc/00000426.htm "> teen *** </a>. <a href=" http://cahn.mnsu.edu/msuatdisc/_msuatdisc/0000041f.htm "> Big boobs </a>. <a href=" http://cahn.mnsu.edu/msuatdisc/_msuatdisc/00000427.htm "> Huge boobs </a>. <a href=" http://cahn.mnsu.edu/msuatdisc/_msuatdisc/00000428.htm "> Mature boobs </a>. <a href=" http://cahn.mnsu.edu/msuatdisc/_msuatdisc/00000429.htm "> Teen boobs </a>.

Super *** and boobs: [url=http://cahn.mnsu.edu/msuatdisc/_msuatdisc/00000420.htm]Big ***[/url]. [url=http://cahn.mnsu.edu/msuatdisc/_msuatdisc/00000421.htm]Huge ***[/url]. [url=http://cahn.mnsu.edu/msuatdisc/_msuatdisc/00000425.htm]mature ***[/url]. [url=http://cahn.mnsu.edu/msuatdisc/_msuatdisc/00000426.htm]teen ***[/url]. [url=http://cahn.mnsu.edu/msuatdisc/_msuatdisc/0000041f.htm]Big boobs[/url]. [url=http://cahn.mnsu.edu/msuatdisc/_msuatdisc/00000427.htm]Huge boobs[/url]. [url=http://cahn.mnsu.edu/msuatdisc/_msuatdisc/00000428.htm]Mature boobs[/url]. [url=http://cahn.mnsu.edu/msuatdisc/_msuatdisc/00000429.htm]Teen boobs[/url].

Hi can you please reupload the code , The address doesn't seem to be working , At least at this time..

Needed to bookmark. Also visit my site you will find it great

It is for sure that obtaining reliable documentation on this topic can be troublesome.

Pingback from  SharePoint Thoughts &raquo; Reporting Services - using parameters with Data Processing Extensions

Pingback from  SharePoint Thoughts &raquo; Reporting Services - using parameters with Data Processing Extensions

Pingback from  SharePoint Thoughts &raquo; Reporting Services - using parameters with Data Processing Extensions

Pingback from  SharePoint Thoughts &raquo; Reporting Services - using parameters with Data Processing Extensions

Pingback from  SharePointed  &raquo; Archive   &raquo; Reporting Services - using parameters with Data Processing Extensions

Pingback from  SharePointed  &raquo; Archive   &raquo; Reporting Services - using parameters with Data Processing Extensions

The times of hopelessly searching for credible judgments regarding this affair are over.

Does buspar work. Buspar online cheaper. Buspar online med. Buspar side effects. Buspar.

dF621S this http://www.google.com is google