Best Practice For Report Projects Related To Application Solutions
Feb 20, 2007
Hello Reporting Services Gurus!
I'm about to start on my first reporting services project, but before I mess it up, I'm looking for some guidance on how best to achieve my mission. Here's what I'm looking to achieve:
I have a datacentric application (SQL Server 2005 Express w/ Advanced Services backend) in which I want to build about 50 "canned" reports for the end users. I want to build the reports utilizing server mode so I can take advantage of some of Reporting Services advanced features. I'm not sure what the best practice would be to build the reporting services project. Is it better to include the report project as another project within the application solution? Or, should I build the report project independent of the application solution? What are the pros and cons of doing it either way? How does including the report project build if it's included in the application solution? How would a ClickOnce deployment deploy the report project to the report server?
My ultimate goal would be to have an "off-the-shelf" software solution that includes an installation package consisting of the application project and report project. Is it even possible due to the Reporting Services architecture to achieve an install in this manner with ClickOnce, Windows Installer, or Installshield? Or, is building the report project indepedent of the application project and deploying the reports to the report server "manually" (i.e. deploy within the report server project) the only solution?
I guess the Subject line sums it all up, but I need some experienced explanation of what do a solution and a project represent, and how do I use them to my advantage.
Is a solution an entire database? If so, how can I create a solution from my existing databases?
Are either of them a way to collect together scripts etc which will be run against a production database when the solution is rolled out?
What is a project? Is it a single set of scripts related to an upgrade to a database? If so, can it be executed as a single entity? How is the sequence of execution controlled?
And so on and so on....
2005 is such a step backwards for DBA's with all the features we used to have and now don't. If it wasn't for the fact that MS will eventually stop supporting 2000, I frankly see no incentive to upgrade myself.
Ok, OK, flame off.
Can anybody suggest some resources which might give me some insight into these questions?
I'm currently usiing SSIS as part of a data migration project. I have used the SSIS import export wizard to generate a package to transfer all the tables from one database to another. When I look at the generated package in can see that it connects to a number of 'temporary' text file objects that contain large blocks of SQL code.
The thing that strikes me is that much of the real package activity is effectively encapsulated in these objects and yet thay are not able to be included in my SSIS project within BIDS. I could not deploy this package to another environment without manually locating and deploying the text objects separately and more importantly these objects are excluded from source control.
I know that with traditional SQL systems, it is important to only open connections to a sql server when they are needed. However, since there is no "server" in mobile apps, is it bad practice to leave one open throughout the duration of an application?
The application is going to be constantly reading and writing to the data tables and it seems like it might be a good idea to leave it open.
I'm tring to figure out the best way to organize my reports.
When I create a new Reporting Service Project throught the 2005 .NET IDE, I'm allowed to have 1 folder in the project to deploy to. I've tried adding folders under the default Reports folder, but I haven't found a way to do this. I'd like to be able to add more directories so I can deploy them right to the server, and they will be in the proper folders, with the correct access rights.
A developer here has offered the approach of One Report - One Project. I don't want to do this because the reports have so much in common, like datasources, that I don't want to be flipping back an forth from one project to the next.
I did also look to see if there was a way to assign a dircetory to each report, rather than the project level, but I didn't see way to do that. I also, tried to add folder through the Windows Explorer and see if I could get around this restriction, but no luck there either.
Anyway, How is everyone else organizing they projects?
We are using Microsoft Reporting Service 2005 to develop reports. The reports are pointing to the Analysis Services database.
We want to move the report project developed to another machine. Basically we want to deploy the reports at the client end.
I have found the below url that mentions the steps for SQL Server 2005 http://support.microsoft.com/kb/842425
Is this the only way to acheive the needed functionality or is there any command line utility available that does this? or any other simple procedure to do this?
We want to give the client, simple instructions to deploy the report project. Any help would be appreciated.
When I open BIDS it displays a list of "Recent Projects". How can I edit (or delete) items from this list? (Over time the list has acquired a lot of junk est projects that I no longer need).
I have converted the VS 2013 WinForm project to VS 2015. Once I installed it on client, report viewer is asking for Microsoft.ReportViewer.WinForms 12.0.0.0.With VS 2013 projects Report Viewer 2012 Runtime worked fine. I haven't been able to locate anywhere newer version of Report Viewer Runtime/
I have what should be simple question about related tables in a dataset and using those fields in a report. Here is my simple design.
Order Header -> Line Item -> Line Discount -> Discount Table
Now when designing this report I cannot seem to access the related discount records for the line item. (as more than one can be applied) Every time I try to drag an field from one of the tables in the dataset to my report it automatically adds "First()" onto the field value expression. This seems to work fine for the order header, but doen't even come close to getting the right discount on the line item? I basically need to pass through the Line Discount table to get to the discount name field to put on the same line as my line item data on the report.
I have looked and googled for for an example on this, but have come up short. Can anyone point me in the right direction?
I have 7 cascaded parameters with the dropdowns for each of it in my report. Upon the selection of the first parameter, the values in the second parameter gets loaded and this goes on till parameter 7. It is working fine till here. But the issue i am facing is as follows:
Suppose parameter 1 has its children only till parameter 2 and has N/A for its subsequent children, subsequent parameters should be disabled and upon clicking the view report , the report should get displayed. But in my case, the report is getting displayed only after selecting all the 7 parameters( even if the value is N/A for the subsequent parameters)
Eg: param1 param2 param3 param4
AA AA1 AA11 AA111
AA112 AA12 AA121 AA122
AA2 AA21 AA211 AA212 AA22 AA221 AA222
BB BB1 N/A N/A ( param3 & param 4 should get disabled to view rpt) BB2 N/A N/A -do- BB3 N/A N/A -do-
I hope you understood the issue i am facing. So please help me if you know the solution..
Does anyone have a successful prescribed sequence for installing VS2005 and Business Intelligence Reports Projects on a Vista Business workstation to be used to create reports for a server?
I've looked through everything I can find here and I don't seem to see a clear solution without a lot of trial and error.
Fact is, I've not been successful getting just the reports to install on a plain XP box. Of course, the report creation looks fine on the server but I don't want to work directly on the server.
I am migrating my reports from crystal 8 to reporting services 2005 but before this my client wants answers of following question.Can anybody provide me answer or link related to mentioned question...
1 -Will there be any performance improvement when the same Report (considering big data) is being run with Sql-Server reporting services compared to the one with Crystal report engine?
2- Is it possible to make the labels which are part of Top Palette like €œ1 of 1€?, €œFind€?, €œNext€?, €œSelect a format€?, €˜Export€? according to the Account language?
3- Can both Versions of Reporting Tools (SQL-Reporting service and Crystal Report) run on the same machine? 4- What are the limitations of this Tools like whether there are any additional load on Database Server because of the involvement of Reporting services which are installed on DB Server. 5- Whether there are additional (to Crystal Report) types of Graph formats, Export file types available? 6- How is the licensing done for SQL-Server Report services?
I installed SQL reporting services 2005 and successfully configured reporting services.After designing the report using Bussiness Intelligence Studio, i tried to deploy my reporting services application to the localhost , I got the below mentioned error :
TITLE: Microsoft Report Designer ------------------------------
A connection could not be made to the report server http://localhost/ReportServer.
The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version. (Microsoft.ReportingServices.Designer)
------------------------------
The request failed with HTTP status 404: Not Found. (Microsoft.ReportingServices.Designer)
hi. i dont understand what they mean when they say developing oltp solutions. can anybody pls explain it to me. also does anyone know what ways there are to develop sql oltp solutions using SQL 2005 reporting services, OLTO, Excel Services. as well as any good tutorials for it?
Hello everybodyI work at a company in Iceland and we have developed a 3-tier solutionwritten in ASP - Visual Basic - MSSQL2000, 4 companies are using thesolution almost constantly and accessing it through a browser. Theconnection has never gone down (yet) so that it has affected ourclients but we are thinking of how be able to run the solution locallyat every place and then create a replication to a main server that ishosted at our place.My question is: Does it affect speed for the clients that are usingthe solution or is there a better way of doing this?The solution is a ticket sale system and our clients use it every dayand people that sit at home should be able to order ticket online.Because of that we can't update the database every 5 minutes or 15minutes because we don't want a double booking in the same seat.Any help appreciated!- Sindri
1) Do I have to install publishing on both servers (A and B) even though one will be publisher and the other will be subscriber.
2)a. Can named pipes be used for communication between these two servers which are on the same domain but not on the same network. Why or why not, whatever the answer may be? b. If I use TCP/IP, it the connection set up using the client configuration utility? How is the connection string set up in this case? c. Suppose the publishing server was not using Net-Beui. Could this pose any problems for communication. (is using lmhosts sufficient in this scenario)
3) I have set up a (remote) SQL Server to be a Publisher/ Distributor. Both SQL servers have been configured to be remote servers relative to each other. Following are the steps I have carried out to set up replication:
______On the Publication Server (a remote server) I went to Server --> Replication Configuration ---> Install Publishing
Next, I chose a local distribution server. I think that the instdis.sql script ran fine because the distribution database was installed successfully.
___Next, I went into Manage Publications from Server menu to set up the publications.
_____________When I went the subscription server to subscribe to the published articles, I got the following error message:
Error 14093: [SQL Server] You must be System Administrator (SA) or Database Owner (dbo) or Replication Subscriber (repl_subscriber) to execute the stored procedure.
According to this article from last year:http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1251149,00.htmlThese are the main options:* Merge Replication* Bi-directional Transactional replication* Immediate Updating* Queued Updating* Peer to Peer* RDAAre there any new alternatives that have popped up over the last year? Are all of six above still good options based on needs?We currently have a three server topology using merge replication.ServerA (App1DB) <--> ServerB (App2 DB)ServerA (App1DB) <--> ServerB (App3 DB)ServerA (App1DB) <--> ServerCServerA supports 1 intranet application using 1 DBServerB supports 2 extranet applications using 2 DB's (1 per application)ServerC is our DW server that we have installed a Search DB which is used by all applicationsPrior to our "upgrade" to merge replication we were using 1-way Transactional Replication so our topology looked like:ServerA --> ServerB (App2 DB)ServerA --> ServerB (App3 DB)We also had linked servers between ServerB and ServerA as well as between ServerC and ServerA to update data on ServerA. We would simultaneously update/insert the tables on ServerB/C and create custom stored procedures to handle the data already processed from the subscribers.With our new implementation we are seeing more latency as well as locking since merge replication is not running off of transaction logs anymore.My main question is would we see an increase in performance and less locking as a result of a topology like this:Master <--> ServerA (App1 DB)Master <--> ServerB (App2 DB)Master <--> ServerB (App3 DB)Master <--> ServerCWhere Master is a server and DB supporting no applications (hence no OLTP). Would latency be the same/better/worse? Should we stick with our current implementation and just performance tune it?A secondary question I have is given the bidirectional replication options above did we choose the best one for us? These servers are all on the same network hosted by the same provider over Gigabit Ethernet (I assume). I think we have the polling interval set at 5 seconds and we are thinking of moving it to 10 seconds at most. Real-time latency is not critical to our business but it would be a "nice to have". For conflict resolution we are keeping it simple, whichever was inserted/updated last "wins". It looks like Bi-directional Transactional replication might be a better option for us. Would it give us the autonomy we are looking for? Any major "cons" to using Bi-directional Transactional replication over merge replication (beside scalability). Scalability may come into play a few years down the road but for now it is not a high priority. Also would the Master model described above using Bi-directional Transactional replication be a successful implementation?ETA - One thing merge replication gives us is autonomy between our application servers, particularly when ServerA needs to come down for upgrades, the applications on ServerB can still function without any dependencies like we had before with 1-way transactional replication with linked server calls.
Does anyone know where I can find a Northwind end to end database solutions (examples) written in ASP.NET (VB). I would like to reverse engineer this project to learn more about ASP.NET?
My warehouse app employs a distributed architecture. Extractions from disparate (wildly so) systems, and transformations and loads into a standardized schema are performed at various locations close to the source systems (both physically and "logically" speaking). There are security and other reasons for this. However this causes some related design and implementation challenges for the ETL processing.
For one, the ETL processes must be successfully operated by non-technical medical administrators, who actually have little interest in the application and sometimes even the analytics produced by the system, who have other more pressing day to day work they want to be doing, in organizations where turnover is high,training is spotty, and LANs are fragile and often congested.
So, real-time feedback to the operator during processing is pretty dern important. I have built a fairly sophisticated GUI (using .Net forms inside a script component) for the operational interface-input boxes just wouldn't cut it).
But that interface is lacking real time feedback as to processing progress at runtime.
Anyone got that T-shirt yet? I'm thinking I need progress bars and real-time task and component progress reports. Also. is there a way to capture the built in logging output in real-time?
I am on a project to develope an route finding system that search for the optimal route to stick with for users of the system. The current version that i've done and successfully run is using normal database access in MS SQL 2005. I stored nodes information in the database and the application will query them using normal "select" clauses and return a datatable object to the application. The result is rather slow cause by the multiple access to database server to query. The application used 8 second to look for a short route withour cosidering lots of calculation of traffic information that i will use later. Any comments on the architecture or approach to switch my algo to T-SQl?
I have been informed that all my keyword search solutions are susceptible to SQL injection attacks. Does anyone have links discussing basic ' multiple ' keyword search solutions? I would think this is a very common routine (perhaps so much so than only newbies like myself do not know it). I have read the posts about escaping ', doing replace " ' ", " '' ", using parameters and yet every multiple keyword solution I come up with is said to be injection prone. Example: visitor enters: Tom's antiquesinto a TextBox control and the C# code behind securely generates the below call to the database. SELECT L_Name, L_City, L_State, L_Display FROM tblCompanies WHERE L_Kwords LIKE '%' + 'Tom's' + '%' AND L_Kwords LIKE '%' + 'antiques' + '%' AND L_Display = 1 RETURN I understand that concantenting string parts using an array and then passing the sewn together string to a stored procedure exposes it to injection. I hope that my single keyword routine below is secure, if it is not then I am not understanding how parameterized SP are supposed to be constructed to protect against injection.string CompanyName;CompanyName = TextBox1.Text;PROCEDURE CoNameSearch @CompanyName varchar(100)AS SELECT DISTINCT L_Name, L_Phone, L_City, L_State, L_Zip, L_Enabled, L_Display FROM tblLinksWHERE (L_Name LIKE @CompanyName + '%') AND L_Enabled = 1 AND L_Display = 1 ORDER BY L_NameRETURN
I'm trying to figure out what solution (replication, mirroring, clustering) would work best for me.
I have been reading many articles in BOL and in this forum. Most talk about getting data TO a backup/standby/subscriber, but I can't find a lot of info regarding getting the data BACK after a disaster is over.
We have a main office and a disaster recovery facility. Most of the time there are no data updates at the disaster location. So, I need to get data to the disaster facility via WAN (latency is not a huge issue - end of day syncing is fine) for backup purposes. In the event of a disaster, the main office will be offline and data changes will happen at the disaster site. When the disaster is "over" and we return to the main office, what's the best scheme to reverse the data back to the main office to start business again? We are a financial company, and have gigabytes of relatively static data. Most changes are current day. So, to snapshot a 100GB database when I know only a few hundred MB changes a day doesn't seem feasible to me.
Most replication scenarios (at least from what I see) can't easily "reverse" the replication after a disaster situation. I'm looking at merge replication on a schedule which seems to look good, but was wondering if anyone else has any ideas or suggestions?
I added a database named appdata to the app_data folder to hold temporary application data. I want to show this data in a report. I created a dataset and an object to select the data. I then created an report.fdlc and a report.aspx with a ReportViewer on it pointing to the dataset object. If I right click on the report.aspx and View in Browser, it work perfectly. But when I upload it to the server I get and browse to it I get (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified). The server does allow remote connections. So I am thinking it's my connection string of the fact that the server doesn't have Sql Server Express.? Here's my connection string. What am I missing here? <add name="appdataConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|appdata.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
I seem to be unable to pass parameters from a VB .Net application to Reporting Services.
Before I added the parameters to the query, I got all the rows back. So I know the application is basically working. Now that I have added the parameters, I get nothing.
I thought the SetExecutionParameters function would help, but my syntax is wrong and it fails.
I would appreciate any hint as to what step I am missing.
Report setup:
Parameters
Name Value @Report =Parameters!Report.Value @Corp =Parameters!Corp.Value @Dept =Parameters!Dept.Value
Query conditions
WHERE Report = 'BudgetVarianceSummary' AND PeriodEnd = CONVERT(DateTime,CONVERT(char,GETDATE()- DATEPART(day,GETDATE()),112)) AND Report = @Report AND Corp = @Corp AND Dept = @Dept
VB Code snippet:
Dim reportPath As String = "/FinancialReports/BudgetVarianceSummary" Dim format As String = "PDF"
' Prepare report parameter. Dim parameters(3) As ParameterValue parameters(0) = New ParameterValue() parameters(0).Name = "Report" parameters(0).Value = "BudgetVarianceSummary" parameters(1) = New ParameterValue() parameters(1).Name = "Corp" parameters(1).Value = "10" parameters(2) = New ParameterValue() parameters(2).Name = "Dept" parameters(2).Value = "7255"
Dim execInfo As New ExecutionInfo Dim execHeader As New ExecutionHeader() Dim SessionId As String Dim extension As String = ""
One of my client want to shift his reporting to SSRS. He wants to submit his report requests to SSRS. He wants the report should run at the reporting server side (not on client system) and output should be exported to a folder on server.
I am some what successful in submitting the report requests using subscriptions. But I have no idea to retrieve the requests from SSRS with the execution status.
I have to display all submitted reprot requests with execution status (success or fail) in a data grid and user selects one report to see the out file. Where can I get this information?
And how can I create a subscription that runs immediatly after submission. (without any delay)?
I thought I can probably find out how the report manager works and create a similar application so that I can define my own things in there.
Looks like there is no sample where I could get a lead. Now I have decided if I can include the report manager in a new aspx page in my application and disable the stuff which is not required by the user to look at it.
I can definitely include the report manager url in the Iframe but how do I disable the stuff from the report manager.
My company wants me to research and flags or registry tricks that would allow non-ansi joins '=*' and '*=' in SQL Server 2005 with a compatiblity mode of 90 to be allowed.
The way I understand the situation is that in SQL Server 2005 with the database compatiblity set to 90, non-ansi join SQL such as the following would not work.
Select * from Customer, Sales Where Customer.CustomerID *= Sales.CustomerID
To work, the SQL above would have to be converted to ansi join SQL such as the following:
Select * from Customer LEFT OUTER JOIN Sales On Customer.CustomerID = Sales.CustomerID
Many hours would be spent browsing through millions of lines of code to find the non-ansi SQL and have changes made.
Does anyone know of any trace flaqs or registry entries that would allow SQL Server 2005 work in 90 compatiblity and still allow non-ansi =* and *= joins in SQL?
how can i publish my RS2005 reports with asp.net(2005) application.I am trying by creating virtual directory ,the aspx page runs but it is unable to access reports from report server
Is there any article on how can i deploy report with asp.net so that i can access report using aspx page remotely
I have an existing vb6 application which used Crystal reports .dsr inside the vb application for the forms. Now I need to change to Reporting services reports. I haven't worked in VB before. I am looking for some guidelines as to how to hook up the Reporting Services reports to vb6 application.
