Dynamic Meta tag with SQL field

Hello all, I use Asp.net 1.1 vb script and MS SQL server 2000. I'm wondering if there's a way to put a database field into the title meta tag. For example if I have a detail page for cars which pulls the text info from the SQL data base and I want the field "name" in the title tag

details.aspx?name=Hummer would have the Title meta tag as Cars..."Hummer"

and

details.aspx?name=Corvette would have the Title meta tag as Cars..."Corvette"

so that for each car showing in details.aspx page it wouldn't have to have the same title tag for search engines

I am not understanding how the SQL database comes into play here. Could you explain further?|||

The SQL database comes into play bcause it danamically creates what look like mutiple pages from a single details page. Lets say the details page calls up fruits from the SQL database. and the Name column has, oranges, apples and blueberry. The details page can be

details.aspx?name=orange
or
details.aspx?name=apple
or
details.aspx?name= blueberry

and have info on each but all three will have the same Titile tag because they all run off of the details.aspx page.

So a solution could be to insert the "name" into the title so that the title meta tag and at the top of the web browser would also hchange for each fruit

sql

dynamic grouping

Hi,
I have a report with grouping. I am passing a parameter via asp.net and if
that parameter @.paramrep=7 then i want to group by type and by region
otherwise i want to group only by region.
I have looked at Chris Hay's example but I am still not sure i do get it to
work as I keep on getting an error: the expression referenced a non-existing
field in the fields collection.
This is my expression:
=iif(Parameters!Paramreport.Value
=7,1,Fields(iif(Parameters!Paramreport.Value =7,
"region",Parameters!Paramreport.Value)).Value)
I am not sure what I am doing wrong as basically I didn't really understand
the article.
I would appreciate any help offered.
ThanksWhile I haven't seen the example you mention, I often use functions for
dynamic grouping ie...( my syntax here might be bad...)
Public function GetGroups(Byref Groupid as integer, Byref Paramval as
Integer) as String
Switch Paramval
Case 7 If Groupid = 1 Then
Return("Price")
Else Return("Productname")
End IF
Case Else If Groupid = 1 Then
Return("Othercol1")
Else Return("Othercol2")
End IF
End
End
THen in the Grouping dialog
=Fields(GetGroups(1,Parameters!Parametername.Value)).Value
=Fields(GetGroups(2,Parameters!Parametername.Value)).Value
Hope this helps... By the way, sometimes I have to dynamically change the
sort to match the grouping at the highest level, otherwise I have gotten an
error...But the sort is handled the same way..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"collie" <collie@.discussions.microsoft.com> wrote in message
news:416E56D8-9856-412A-8381-A7BBD1DCBAC5@.microsoft.com...
> Hi,
> I have a report with grouping. I am passing a parameter via asp.net and if
> that parameter @.paramrep=7 then i want to group by type and by region
> otherwise i want to group only by region.
> I have looked at Chris Hay's example but I am still not sure i do get it
to
> work as I keep on getting an error: the expression referenced a
non-existing
> field in the fields collection.
> This is my expression:
> =iif(Parameters!Paramreport.Value
> =7,1,Fields(iif(Parameters!Paramreport.Value =7,
> "region",Parameters!Paramreport.Value)).Value)
> I am not sure what I am doing wrong as basically I didn't really
understand
> the article.
> I would appreciate any help offered.
> Thanks|||Hi,
Thanks so much for your response.
I have a few questions about your code if you could please clarify (I feel
stupid for asking but...)
Ok here goes :-)
What do you mean by groupid such as groupid=1?
What is price? A field name to group by if groupid=1?
Parmetername in my case would be the parameter that i send from asp.net
@.paramrep=7 correct?
Thanks
"Wayne Snyder" wrote:
> While I haven't seen the example you mention, I often use functions for
> dynamic grouping ie...( my syntax here might be bad...)
> Public function GetGroups(Byref Groupid as integer, Byref Paramval as
> Integer) as String
> Switch Paramval
> Case 7 If Groupid = 1 Then
> Return("Price")
> Else Return("Productname")
> End IF
> Case Else If Groupid = 1 Then
> Return("Othercol1")
> Else Return("Othercol2")
> End IF
> End
> End
>
> THen in the Grouping dialog
> =Fields(GetGroups(1,Parameters!Parametername.Value)).Value
> =Fields(GetGroups(2,Parameters!Parametername.Value)).Value
> Hope this helps... By the way, sometimes I have to dynamically change the
> sort to match the grouping at the highest level, otherwise I have gotten an
> error...But the sort is handled the same way..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "collie" <collie@.discussions.microsoft.com> wrote in message
> news:416E56D8-9856-412A-8381-A7BBD1DCBAC5@.microsoft.com...
> > Hi,
> >
> > I have a report with grouping. I am passing a parameter via asp.net and if
> > that parameter @.paramrep=7 then i want to group by type and by region
> > otherwise i want to group only by region.
> > I have looked at Chris Hay's example but I am still not sure i do get it
> to
> > work as I keep on getting an error: the expression referenced a
> non-existing
> > field in the fields collection.
> > This is my expression:
> > =iif(Parameters!Paramreport.Value
> > =7,1,Fields(iif(Parameters!Paramreport.Value =7,
> > "region",Parameters!Paramreport.Value)).Value)
> > I am not sure what I am doing wrong as basically I didn't really
> understand
> > the article.
> >
> > I would appreciate any help offered.
> >
> > Thanks
>
>|||Wayne your code was a great help.
Thanks :-)
"Wayne Snyder" wrote:
> While I haven't seen the example you mention, I often use functions for
> dynamic grouping ie...( my syntax here might be bad...)
> Public function GetGroups(Byref Groupid as integer, Byref Paramval as
> Integer) as String
> Switch Paramval
> Case 7 If Groupid = 1 Then
> Return("Price")
> Else Return("Productname")
> End IF
> Case Else If Groupid = 1 Then
> Return("Othercol1")
> Else Return("Othercol2")
> End IF
> End
> End
>
> THen in the Grouping dialog
> =Fields(GetGroups(1,Parameters!Parametername.Value)).Value
> =Fields(GetGroups(2,Parameters!Parametername.Value)).Value
> Hope this helps... By the way, sometimes I have to dynamically change the
> sort to match the grouping at the highest level, otherwise I have gotten an
> error...But the sort is handled the same way..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "collie" <collie@.discussions.microsoft.com> wrote in message
> news:416E56D8-9856-412A-8381-A7BBD1DCBAC5@.microsoft.com...
> > Hi,
> >
> > I have a report with grouping. I am passing a parameter via asp.net and if
> > that parameter @.paramrep=7 then i want to group by type and by region
> > otherwise i want to group only by region.
> > I have looked at Chris Hay's example but I am still not sure i do get it
> to
> > work as I keep on getting an error: the expression referenced a
> non-existing
> > field in the fields collection.
> > This is my expression:
> > =iif(Parameters!Paramreport.Value
> > =7,1,Fields(iif(Parameters!Paramreport.Value =7,
> > "region",Parameters!Paramreport.Value)).Value)
> > I am not sure what I am doing wrong as basically I didn't really
> understand
> > the article.
> >
> > I would appreciate any help offered.
> >
> > Thanks
>
>

Dynamic Group Sorting in Crystal Report

i am using crystal report application in visual studio .net. Now, I make one crystal report, has 5 groups. Now, I want to change their order from VB .net application. What to do for that?? I'm new in crystal report.... I guess you need to organize your dynamic grouping in your report. You can use a parameter(s) for that, it will let you/user to select/change a group. Then create a formula based on the parameter response.

For example:
I would create a parameter to allow you/user to select 'SDate' Shipping Date or 'CustID' - Customer ID.

Then I would write a formula for my 1st group @.Group1:

If {?Group1}='SDate' then {table.shipping_date} else {table.CustomerID}

For my 2nd group, I would write a formula @.Group2:

If {?Group1}='SDate' {table.CustomerID} else {table.shipping_date}

After that, I would grouped my records on @.Group1 and on @.Grop2.

----

Or you can create one parameter per group and then a formula based on that parameter (1 per group as well):
For your 1st group I would create a parameter {?Group1} which allows me to select 'SDate' for shipping date, 'CustID' for CustomerID, 'Country' for country code etc.

Then create a formula @.Group1:

If {?Group1}='SDate' then {table.shipping_gdate} else
If {?Group1}='CustID' then {table.CustomerID} else
If {?Group1}='Country' then {table.CountryCode} else

For my 2nd group I would create a parameter {?Group2} and then a formula @.Group2:

If {?Group2}='Source' then {table.origination_number} else
If {?Group2}='DestCity' then {table.destination_city} else
If {?Grop2}='Carrier' then {tabme.CarrierID} else
.
.
.
You can create as many group options as you need.

I hope this will be helpful

dynamic graphics for barcode

Hi,
I need to create a report with barcodes. Currently I have a method in
asp.net that generates the barcodes by selecting different graphics based on
the characters passed into the method.
Is this possible to do in SRS, or do I need to use a third-party tool to
create the barcode with fonts?
Thanks,
ScottScott,
Have you looked at placing your existing ASP.Net barcode stuff into a
class that is also accessible from the report server? You can include
your own namespaces and then call them (using something like
=Code.MyNameSpace.MyFunc(a) I believe).
Alternatively, you could have a page in your web site that returns a
GIF or JPEG based on the parameters passed to it (such as
http://localhost/mydyanmicbarcode.aspx?BarCode=1234abc) and then access
this via the Image control, setting it to External and the Value to
'http://localhost/mydyanmicbarcode.aspx?BarCode=xxxReportFieldHerexxx'
Ryan|||Great, thanks Ryan, I just discovered this functionality.
That should work for me.
-Scott
"Ryan" wrote:
> Scott,
> Have you looked at placing your existing ASP.Net barcode stuff into a
> class that is also accessible from the report server? You can include
> your own namespaces and then call them (using something like
> =Code.MyNameSpace.MyFunc(a) I believe).
> Alternatively, you could have a page in your web site that returns a
> GIF or JPEG based on the parameters passed to it (such as
> http://localhost/mydyanmicbarcode.aspx?BarCode=1234abc) and then access
> this via the Image control, setting it to External and the Value to
> 'http://localhost/mydyanmicbarcode.aspx?BarCode=xxxReportFieldHerexxx'
> Ryan
>

Dynamic Field DB Schema Brainstorming

Hello -
Have a project where we are going to build a form creation application. (ASP.NET). This will allow an administrator to build a form on the fly - this form will appear on the front end of the site.
This is a fairly common thing. Are there any resources out there as to where to start designing the DB schema? I'm not looking to reinvent the wheel. Here's the basic objects I'm seeing:
Tables
Forms
FormFieldNames
FormFieldTypes
FormFieldJS
Any tips on the right direction to go?
Thanks
Rob
Try the link below and right click to download the PPT slides for datamodeling. The key to data modeling is files and associations, that means you may have fifty files but you may only have three tables based on files association. The table relationship is determined by upper and lower bound cardinality and it is not complicated. There are six complete database catalogs in the book. Hope this helps.

http://wings.buffalo.edu/mgmt/courses/mgtsand/data.html|||

Thanks for the resource, good stuff - organized nicely. I was looking specifically to my example, so I didn't plow through an ERD of what's already out there, thankx.

|||A relational database sounds like a completely inapropiate tool to build this application you're looking to build. When you are using a RDBMS, you need to know what data you are modeling. You can't just try to make it up on the fly.
You need to consider other solutions, technologies, tools. XML may be suited formatted for this -- the administrator would define an XML schema (possibly through your tool) and then build a form to input data to create XML documents that conform to the schema.
No less, any time I see anyone try to do this in a RDBMS, it falls apart in less than a year -- if it ever makes it out of development.|||

Sounds like you're both directing me the same way - a relatively small DB schema that holds the forms, the fields, and descriptive text for the fields, etc. The actual field types, values, javascript, etc... should be in a bunch of files that describe each fieldID, most likely in XML.

Thank you for that. We were thinking about compiling a bunch of .JS files for each field, and then one large.js file for the form aggregate fields. But XML would make more sense for the fields' descriptive data I believe, no?

Dynamic destination address in SSIS packages

Hi,

I am using VS.net 2003 as a front end and SQL server 2005 backend.

i am creating SSIS packages for Datatransformation programically in .NET.

but the package created is compatible to the previous version of SQL server ie SQL server 2000.

So i need to migrate it in SSIS package compatible to SQL server 2005.

it is migrate also using Data Transformation migration wizard.

But i want to migrate my DTS package programically or by using stored procedure.

Is there any stored procedure or any code is there from which i can migrate DTS into SSIS ?

Thank you

Hi Sanjay,

For what I've read from microsofts webpages about migrating from DTS to SSIS it is far from a simpel process that can be done 100% automatic - some task (especially activex tasks) can not be migrated without human interviention - there is a migration tool that can help you identify what problems you will have and how to solve them. The program is called "Microsoft SQL Server 2005 Upgrade Advisor"

Regards
Simon
|||

Thank you Simon for the replay,

Is there any process from which i can change the desitination address in the package dynamically,If i create my package using business development intelligent studio integrated service.

Every time the destination changes by any means when we require.

or should i am able to create SSIS packages using VS.NET 2003 compatible to SQL server 2005?

Thank you

Sanjay

|||

Hi sanjay,

In Visual Studio 2003, We cannot create or even open SSIS Packages.

I do not understand what do you mean by "change the desitination address in the package dynamically"

Thanks

Subhash Subramanyam

|||

i dont create or even open SSIS packages in VS 2003,

But i am able to run SSIS in SQL server 2005 by scheduling him in SQL jobs..

As i am using SQL server 2005 so i am able to create the SSIS package in SQL server 2005 Integration service.

I am creating it for the data transformation task, but my destination changes.

ie. i want send data from one server to multiple server.

But the data sent through the server is different.ie, no server are getting the same data.

ie. i want to desgin one to many tronsformation in SSIS.

My source server is constant always but the destination server address is saved dynamically from the programme in made in VS2003.

it means the address of destination are multiple and changes whenever it required to.

how i can do it?

I hope you understand my problem.

Thank you.

|||

Sanjay wrote:

i dont create or even open SSIS packages in VS 2003,

But i am able to run SSIS in SQL server 2005 by scheduling him in SQL jobs..

As i am using SQL server 2005 so i am able to create the SSIS package in SQL server 2005 Integration service.

This is not quite right - you are able to RUN SSIS packages from within SQL Server 2005 Intergration Services. If you want to create packages that are not trivial (like the export function in Management Studio) you need to use Visual Studio 2005 (VS2005). If you don't have it, you should be able to install it from the "Clients Components" along with Management Studio.

Sanjay wrote:

I am creating it for the data transformation task, but my destination changes.

ie. i want send data from one server to multiple server.

This is possible with VS2005. You can use dynamically destinations - ex. get values from a table or flat file.

Sanjay wrote:

My source server is constant always but the destination server address is saved dynamically from the programme in made in VS2003.

How is the address saved?
|||

should i am able to run DTS package without migrating into SSIS by scheduling him in SQL jobs in SQL server 2005?

|||

Sanjay wrote:

should i am able to run DTS package without migrating into SSIS by scheduling him in SQL jobs in SQL server 2005?

You can create a SQL Agent Job with a CmdExec (command line) step type. You just need to provide the command line to execute your DTS package.

|||

Sanjay wrote:

should i am able to run DTS package without migrating into SSIS by scheduling him in SQL jobs in SQL server 2005?

Under "SQL Server Management Studio" if you connect to the SQL server you will find: Management>Legacy>Data Transformation Services

Right click the folder at choose import - if you have it as a file.

If you can run it directly from the filesystem I don't remember but you can try it out.

Dynamic database with MS ACCESS

Hi,
I am using ASP.NET to display reports using the report viewer object.
I have multiple MS Access databases that can be used as the datasource to a
report.
I know how to make a connection to multiple different SQL databases passing
the database as a parameter in the URL of my query and then into my stored
procs, but the same technique cannot be applied to MS Access databases.
I figured out that the parameter to be passed must be the *.mdb file path on
the server... but where to use it?
ThxI have a huge query for as one of my datasets, something like this (this
query works well) :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN 'c:\db1.mdb'
ORDER BY tabl1.x
I tried the following :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN @.DBPath
ORDER BY tabl1.x
But apparently u can't use a named parameter with OLE DB ... so I tried this :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN ?
ORDER BY tabl1.x
But when I execute the query and am asked what value should ? take, whatever
the value i enter it doesnt work. I tried 'c:\db1.mdb', c:\db1.mdb, and
[c:\db1.mdb]
Nothing works. I also don't know how to map the ? variable to a @.Parameter,
because the ? doesn't generate a parameter in the parameter tab ...
AAAaaaarggg!|||You are right about using unnamed parameters. First, are you in the generic
query designer (2 panes). You will want to be there (button is to the right
of the ...). Next, if a parameter is not created automatically for you then
go to the form design, menu report->report parameters and add a parameter.
Then go back to the data tab, click on the ..., go to the parameters tab and
then put in the ? on the left and select your parameter on the right.
My guess is that you cannot just have a parameter in your query where it is
not part of the where clause (which in this case is not). However, you can
still do this. Use an expression which would look like this:
= "SELECT tabl1.*, ..., tablx.* FROM tabl1, ... tablx IN '" &
Parameters!Paramname.Value & "' ORDER BY tabl1.x"
I sometimes create a report with no datasource, just my parameters and a
textbox. I set the textbox to the expression so I can see what I have
created so I know if it is correct before I set the dataset to this value.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6024F625-1721-4212-AB86-B8C131A12CE2@.microsoft.com...
>I have a huge query for as one of my datasets, something like this (this
> query works well) :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN 'c:\db1.mdb'
> ORDER BY tabl1.x
> I tried the following :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN @.DBPath
> ORDER BY tabl1.x
> But apparently u can't use a named parameter with OLE DB ... so I tried
> this :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN ?
> ORDER BY tabl1.x
> But when I execute the query and am asked what value should ? take,
> whatever
> the value i enter it doesnt work. I tried 'c:\db1.mdb', c:\db1.mdb, and
> [c:\db1.mdb]
> Nothing works. I also don't know how to map the ? variable to a
> @.Parameter,
> because the ? doesn't generate a parameter in the parameter tab ...
> AAAaaaarggg!
>

Dynamic database in Web Service

I have a question about changing the database source dynamically in a Web Service. I have created a Web Service for a Crystal Report using VS .NET 2003. I use Crystal Reports Web Viewer to display the report in an ASP.NET page. My database is an Access database and I have a copy of the report source database for each unique user to the Web page. I read the post on changing the database dynamically but it seems to be changed on the Crystal Reports viewer and not in the Crystal report which would be done in the Web Service. I have tried setting debug stops in the code behind module in the Web Service but the debugger does not seem to reach the breakpoints. Can you offer any advice on modifying the database connections dynamically in the Web Service?I have a similar problem. I can set the viewer db properties when i declare a web service but i cant use report engine object model. Can you tell me how you have accomplished that|||I found it fairly easy to bind to a single database. I merely opened the Crystal Report in Visual Studio .NET. In the Report Design view, I right click on the Database Fields in the Field Explorer and Select "Set Location". I defined an ODBC connection to the database and bind the report to that. Turning the report into a Web Services was easy as well. I added the report to a project that I was using to define Web Services. Then right click on the project and select Publish the report as a Web Service. Visual Studio does all the rest. Unforunately all of this is a design/build time and I haven't figured out how to change the database during runtime.

Hope this helps you.|||thanks for the reply. However that is not my problem. I want to be able to export these reports as well. Exporting code only works from report engine object model and you cant use it from client application if your reports are published as web service. That is my complication. I can set parameters, db changes at runtime when i do web services. Still thanks for the reply|||I'm sorry. I guess I did not understand the question about the the report engine object model. I also am not sure what you are referring to on exporting the reports. I haven't done anything beyond the defaults that you get when Visual Studio creates the Web Service except to connect it to the Crystal Reports Web Viewer in the ASPX pages. What did you mean when you said you can set parameters, db changes at runtime through web services? My main question is how to specify a different database file(Access database) at runtime.|||This is how u do it with web services and a strored procedure as your data source. If you use tables as data source. loop through all the tables and set the propetries of the tables(uid,pwd,dbname, servername)
Hope this helps.
Exporting questions is i have to export the reports in crystal to excel, pdf, word format.That can rbe done using viewer object model and i have to do it in web services but i dont know how to modify the engine in the web service code. I have the same problem you have. U cant debug any code in web services

Dim mytablelogoninfos As New CrystalDecisions.Shared.TableLogOnInfos
Dim mytablelogoninfo As New CrystalDecisions.Shared.TableLogOnInfo
Dim myconnectioninfo As New CrystalDecisions.Shared.ConnectionInfo

With myconnectioninfo
.UserID = "uid"
.ServerName = "servername"
.Password = "pwd"
.DatabaseName = "dbname"
End With

mytablelogoninfo.TableName = "cspAppointmentsReport;1"
With mytablelogoninfo.ConnectionInfo
.UserID = "uid"
.Password = "pwd"
End With
mytablelogoninfo.ConnectionInfo = myconnectioninfo
mytablelogoninfos.Add(mytablelogoninfo)
CrystalReportViewer1.LogOnInfo = mytablelogoninfos
CrystalReportViewer1.ReportSource = New localhost.AppointmentSubReportService|||Now I understand what you mean by exporting and no I haven't tried anything like that yet. Sorry I don't think I can help you.

Thanks for the sample code. I will see if I can make it work for what I'm trying to do. I appreciate the help.

Good luck solving your problem.

Dynamic Database Connection

Hi,

I done some Crystal reports(10.0) in asp .net using SQL server 2000(DB). While creating the reports, OLE DB Connection Information is must. Now i want to change the Source and Database for all the Reports. I want to do it Dynamically. Could I ?. I want to create reports without giving these(Source and Database) all. That is for each reports I dont want to give Source,Database,User Id and Password.
Pls help me in this regard.almost same problem with mine..
but mine is in report environment in vb6.

i'm not sure, but in vb6 (it think its not quite different :) )
u can do such this ..
firstly u must make a reference to the crystal report.
next...
--create a new Crystal Report object ...
it may looks like this

dim cr as new crystal report object

i think there must be a method of that CR object that could add a new report

hope this gonna help at least give a clue.. since i'm not that expert in .NET tech ..

good luck|||Hi szpilman,
The Problem is while creating the reports we have to give the Source and Database, But we should not do in this method. While creating the reports we should point one thing( It may be DSN).For this , All the reports should point out one (DSN(applies for VB .NET)). Here I want to point out all the reports to one( like DSN). Whenever I want to Change i should change in this Pointed one( like DSN ). Like DSN "i want one", where i can change the Source and Database dynamically.
Thank U

Dynamic Crystal Reports w/ .NET

I am trying to replicate the following screen in a Crystal Reports for .NET.

http://bellsouthpwp.net/w/i/wilburton/Schedule.JPG

I am having problems. You cannot create a formula for the position/width of a box or textbox. Also, there doesn't appear to be a way to write code to format the template as each row is processed. Change the width/left of the boxes only changes the template before the data is processed and the report is created.

I talked to Crystal Reports technical support and there only decent suggestion was to use a bar chart. This almost works but due to the flexibility of the charting components I don't think I will be able to get anything reasonably close to this format.

Any ideas?Maybe a chart would work?

Dynamic Connection string (config file)

Hi,

I have a web application (.NET 2005), which reads data from a multi-company database either SQL 2005 or Oracle. The company_id and connection string and other information is specified through a config file. I need to display the report from the click on a menu item.

The developers design the reports using Report Designer and store the RDL file in a designated folder (configured in the config file of the website). On the click of the menu the page (.aspx) page accepts values for the parameters of the report.

1. In this scenario, can these parameters, company_id (from config file) be passed to the report (which is already designed using Report Designer) and the RDL is already generated ?
If yes, then I can programmatically publish the data source (connection string from config file) and the report (from RDL file) onto the Report server and open the report using URL re-direction and display.

If no, then do I need to edit the RDL content programmatically to include the connection string and values for the parameters etc. I mean, the developer must require the connection string and company_id and other information to design the report. Can the developer user the config file to read the information and use in the report design.

2. The report parameters can also be specified in the Report designer. Is there any performance benefit of using this method ?

Sorry for the long post, but required to explain the scenario.

Thanks in advance.There are multiple options to achieve dynamic connection strings. Below is a description of available options.

In your case, on way of doing it is to use the new VS 2005 report viewer controls (particularly the Webforms control), read the parameter from the config file and pass it on to the report viewer control which renderers the report in remote mode on a report server and displays the output locally (see http://www.gotreportviewer.com/).
For the data source connection string and command text you could use expressions to dynamically construct these from the parameters (see the bottom of this posting for a simple example).

General approaches for dynamic database connections in RS 2000 are available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server; please check BOL for more details
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.

In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" &amp; Parameters!ServerName.Value &amp; ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||

dynamic variable sql database Connection String

this is work easy

http://www.codeproject.com/useritems/PersistConnectionStrings.asp

Dynamic Connection string (config file)

Hi,

I have a web application (.NET 2005), which reads data from a multi-company database either SQL 2005 or Oracle. The company_id and connection string and other information is specified through a config file. I need to display the report from the click on a menu item.

The developers design the reports using Report Designer and store the RDL file in a designated folder (configured in the config file of the website). On the click of the menu the page (.aspx) page accepts values for the parameters of the report.

1. In this scenario, can these parameters, company_id (from config file) be passed to the report (which is already designed using Report Designer) and the RDL is already generated ?
If yes, then I can programmatically publish the data source (connection string from config file) and the report (from RDL file) onto the Report server and open the report using URL re-direction and display.

If no, then do I need to edit the RDL content programmatically to include the connection string and values for the parameters etc. I mean, the developer must require the connection string and company_id and other information to design the report. Can the developer user the config file to read the information and use in the report design.

2. The report parameters can also be specified in the Report designer. Is there any performance benefit of using this method ?

Sorry for the long post, but required to explain the scenario.

Thanks in advance.There are multiple options to achieve dynamic connection strings. Below is a description of available options.

In your case, on way of doing it is to use the new VS 2005 report viewer controls (particularly the Webforms control), read the parameter from the config file and pass it on to the report viewer control which renderers the report in remote mode on a report server and displays the output locally (see http://www.gotreportviewer.com/).
For the data source connection string and command text you could use expressions to dynamically construct these from the parameters (see the bottom of this posting for a simple example).

General approaches for dynamic database connections in RS 2000 are available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server; please check BOL for more details
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.

In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" &amp; Parameters!ServerName.Value &amp; ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||

dynamic variable sql database Connection String

this is work easy

http://www.codeproject.com/useritems/PersistConnectionStrings.asp