Creating Report Based On Multiple Databases?

May 6, 2008

My application uses 2 databases from 2 different server. When i create a report model in business intellegence development studio I can only create 1 datasource for each database, and 1 datasource view for each datasource. Then when I create a new report, I can only use one datasource.

Is it possible that my report can be built on more than one databases?

thanks in advance

View 4 Replies


ADVERTISEMENT

Creating A Database From Multiple Databases Accross Multiple Servers

Sep 13, 2007

Hi,

I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.

I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.

I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!

Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):

SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLOACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a



I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC

Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)

Thanks

View 8 Replies View Related

Reports Based On Multiple Databases

Oct 23, 2007

I'm new to developing reports using 2005 SQL Reporting Services. Is it possible to develop reports based on more than one database, (generally MS SQL)? I need to set up a dataset that includes tables from several different databases linked together so that information can be viewed together in one report.
thanks

View 9 Replies View Related

Creating Multiple Databases From A Single Backup F

Nov 9, 2006

Hi,
I want to create two databases by restoring from a
single backup file in sql server. I am using 2005-sqlexpress .Is it possible?

Thanx in advance..

View 3 Replies View Related

Transact SQL :: Create Email Report Which Gives Result Of Multiple Results From Multiple Databases In Table Format

Jul 24, 2015

I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:

EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary', 
@profile_name  =
'SQL SMTP',
   
[code]....

View 3 Replies View Related

Creating Multiple Databases Running Off The Same Data Engine

Aug 23, 2005

Hi, I have looked and loked for the answer to this question and made no progress.  I want to install MSDE on one of our servers, however, when I went to install it, I found that there was another MSDE already installed and running (it was an agent running with our backup software Veritas).  Is it alright to create another database and have 2 running simultaneous on the same data engine?  Is there any type of limitations that I sould know about - such as number of allowed accesses at one time?  Any help is greatly appreicated.

View 1 Replies View Related

Creating A Report Model Based On A View

Aug 1, 2007

in my report model project, i'm able to successfully create and deploy a report model based on a data source view that is in turn based on a table object in my data store.

but if i try to create a report model that's based on a a data source view that is in turn based on a view in my data store, well then, the report model that gets created comes up blank - i.e. shows no objects.

What gives? Is it not possible to create a report based on a view? that can't be right!

View 6 Replies View Related

SQL Server 2008 :: Creating Multiple Charts Based Off Of Main Chart

Jun 4, 2015

I have this main chart setup. It pulls data from a query based on the selected parameters.

I want to create multiple charts for every catagory in the result set.

On the main chart there is a catagory called "Missing".

I want a chart for this category to display when the main chart is loaded. So below the main chart it would show a chart for each of the categories on the main chart.

Basically this report returns information on a certain part number displaying its problem types. Then I want it to create other charts for each problem type displaying the problem sub-types.

View 1 Replies View Related

Creating Report Based On Parent-child Dimension

Nov 17, 2005

Hi

View 43 Replies View Related

Error Creating Report Model Based Upon Oracle Database

May 29, 2006

In MS Visual Studio, when creating a new Report Model Project, after defining a datasource to an Oracle database (and successfully testing it), and a simple datasource view (1 table), when I click "Run" in Report Model Wizard, I receive the following error:
"ORA-02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }"

It does not appear that one can create Report Models from an Oracle database (since the SQL being used to query the Oracle database cannot be edited and contains syntax errors?). Is this the case?

Thank you.





View 14 Replies View Related

Transact SQL :: Creating Report - Query With Parameters Based Off Values

Sep 1, 2015

I have the following report I need to create with 2 parameters. An equal OR not equal. I need the report to have a drop down that has equal to  '1024' or a drop down option that IS NOT equal to '1024'. I also need the WHERE clause to return the equal or not equal based on the user selection inside of SSRS.

SELECT user1 AS [Company], reference AS [PAI_REF], statenumber,
LEFT(user4, 7) AS [Supplier Code],
user4 AS [Company Information], 
user8 AS [Transaction Type], user2 AS[Invoice Number], 
--CONVERT(VARCHAR,CONVERT(Date, user3, 103),101) AS [Invoice Date],
[routeName] AS [Route], username AS [User Name]

[Code] ....

View 2 Replies View Related

How To Create A Report Using Multiple Databases

Jan 31, 2007

Hi, I am a beginner of Reporting Service. I am trying to create a report using multiple databses. For example, I want to create a report called RevenueByCustomer, so I need to get data from the Customer Table of CRM database, which contains customer information, and I also need to get data from Transaction table of Sales database, which contains all the revenue information. In order to get data from both database, I have created two dataset. One is Customer dataset, which get all required customer data from CRM database, and the other is Revenue dataset, which gets data from Sales database, they used seperate datasource (because each datasource only contains one database connection). Now my problem is how can I make them be displayed in one report ? It seems to be like a Master-Detail report, I need to sum up all trasactions for a particular customer and also need to display the customer name with each TotalAmount, but how can I make these two dataset can be merged together or used an extra query to do it?

Please help me, thanks a lot

This Query below is used to calculate the total amount for each customer in Revenue dataset:

SELECT CustomerID, sum(TotalAmount - TotalTaxAmount) AS TotalExcTax, sum(TotalAmount) AS TotalIncTax
FROM TransactionMaster
WHERE (Updated >= @Start) AND (Updated <= @End)
Group By CustomerID

the other one here is used to get customer code and name in Customer dataset:

SELECT ID, Code, Name
FROM Customer

View 10 Replies View Related

How To Create A Report Using Multiple Databases

Jan 31, 2007

I have two database:

FoxPro database - contains client info (name, address)

SQL Server database - contains appointmetns, orders, jobs etc.

How can I create reports using both databases?

Thank you

View 3 Replies View Related

SQL 2012 :: Create SSRS Report Based On Multiple Locations

Oct 23, 2015

I am looking to create an SSRS report based on multiple locations. There will be one report that presents a summary of all reports, and then individual report for each location.Instead of creating 2 separate reports, is it possible in SSRS to display different datasets based on the parameter selected?

View 3 Replies View Related

Creating Multiple Report Folders In Business Intelligence Studio

Jan 29, 2008

Hi,
Is there a possible way to create multiple folders in a report project so that different reports can be categorized and arranged in separate folders. Apparently, it seems not. Is there a workaround?

Thanks.

View 6 Replies View Related

Linking Tables From Different Databases Or Querying From Multiple Databases

Dec 10, 2007

Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query. 
 

View 3 Replies View Related

How Do I Jump To Another Report Based On A Value In My Current Report? Report Has No Parameters.

May 3, 2007

How do I jump to another report based on a value in my current report. The report that I am jumping from has no parameters, just values.

View 7 Replies View Related

Merging Data From Multiple Databases With Multiple Tables (all With The Same Structure)

Nov 15, 2006

Hi!

I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.

I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.

I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)

Any pointer most welcome!

best regards and thanks

Thibaut

View 1 Replies View Related

SQL Tools :: How To Restore Multiple Databases From Multiple BAK Files At Once

Aug 14, 2012

I am trying to restore multiple .bak backup SQL database files onto a new server. However, I have found that it will not allow me to restore multiple databases at once. Is there a way to do this so that I do not have to manually upload one at a time? I tried adding all the .bak files at once to the backup device window but it only did the first one listed. It would be so much easier to restore them all at once so that I do not have to continue this manual process. I am restoring them via device.

View 13 Replies View Related

JOIN Multiple Tables From Multiple Databases

May 23, 2008

Hello,
I am in the progress of designing a new section of my database and was thinking of creating a hole new database instead of just creating tables inside the database.  My question is can you JOIN multiple tables in an SQL Statement from multiple databases.  Ie, In the Management program I have a database called 'Convention' and another one called 'Services', inside the two databases there are many tables.  Can I link say tblRegister from Convention to tblUser in Services?
Thanks

View 3 Replies View Related

Creating Databases

Nov 2, 2004

Ok I have the Sql all written and I was going to use MYSql but it wasn't working. I was told that MSSQL worked with the files but I can't for the life of me figure out how to use it. I installed the desktop version so it is running but now how do I create and edit databases and tables and so forth. Help is greatly apprieciated as the only other sql I have done is for php forums. Thanks and If you could tell me if I need to download another program to do it graphically or something that would be awesome.

EDIT: I am using Windows Server 2003 Enterprise if that makes any difference. ISS 6.0 (kind of understand some of it)

View 1 Replies View Related

Creating Databases On The Fly

Jun 29, 2006

hello,

i am trying to create a database by using a store procedure. This stored procedure takes two input parameters. i want to assign these parameters to the 'Filename' attributes when i'm creating the database both for the .mdf and .ldf files. However i keep getting an error.

These work -------
FILENAME ='c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctes tdblog.mdf',

FILENAME ='c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctes tdblog.ldf',

These do'nt work--------
FILENAME = @Databasepath,
FILENAME = @Databaselogpath,

Here is my code:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE rico_dbasescript
@Databasepath varchar(100) = 'c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctes tdb.mdf' , @Databaselogpath varchar(100)= 'c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctes tdblog.ldf'
AS
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Sardonyxrioctestdb')
DROP DATABASE [Sardonyxrioctestdb]



CREATE DATABASE Sardonyxrioctestdb
ON
( NAME = 'Sardonyxrioctestdb_dat',
FILENAME = @Databasepath,
--FILENAME ='c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctes tdb.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sardonyxrioctestdb_log',
FILENAME = @Databaselogpath,
--FILENAME ='c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctes tdblog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO


I am still researching my problem but i would appreciate any help. Thanks guys.

JamaicanGuy
**I am a newbie .net developer.
Jah Bless!!!!!!!!!!!!

View 1 Replies View Related

Creating Databases

Oct 13, 2007

When i create a database and later on want to add a file in it

Alter database db1
Add File
(Size = 2MB,
Maxsize = 5MB,
Filegrowth = 20%)

this gives me an error because there's no filename.
I would like to know if filename is mandatory?
Thanks as i got an error when running saying no filename.

View 2 Replies View Related

Creating Over 100-databases.

Apr 21, 2007

Hi,

I'm using SQL Server 2005 Express for an application.



SQL Server 2005 Express supports 4GB user data per database. Because need over 4GB(may be 20GB ~ 30GB) data space, I should split database which each max size up to 200 MB. And create 100 databases.



Is it reasonable? If true, what about the performance or system's overhead?



Thank you.

View 1 Replies View Related

Report Builder Using Report Model Based On Linked Server (Excel) - Primary Key?

Sep 6, 2007

I've created a linked server with a pretty basic Excel spreadsheet, and used this command to create a linked server to it:


sp_addlinkedserver ''XL_SPS_1', 'Excel', 'Microsoft.Jet.OLEDB.4.0', 'c:MyExcel.xls', null, 'Excel 8.0'



I want to use this as the data from which to build a report model. As linked servers don't show up in the Data Source View wizard, I created a view in SQL Server:


create view MyExcel
as
select * from XL_SPS_1...Sheet1$


Okay, great, now the view shows up in the DSV wizard and I can create the data source view. However, when I create a new report model based on this data source view, the Report Model Wizard tells me at "Create entities for all tables" that I've got an error when it processes dbo_MyExcel that "Table does not have a primary key."

I assume this is where the identifying attributes for the entities in the report model are taken from, so I really can't go further. Does anyone have an idea as to how to add a primary key to a linked server (Excel) in SQL 2005? Can this be done? Other than importing spreadsheet data to a SQL table, how can I get around this?

Thanks,
--Stan

View 3 Replies View Related

Reporting Services :: SSRS Linked Report Based On Calculated Field From Main Report

Oct 6, 2015

How do I get data on my linked report based on my grouped subtotal and grand total from the main report.  The subtotal and grand total are calculated columns.

I have a 3 columns in my matrix in the SSRS summary report. Actn_COAST, ActnCITY and NumbOfAccts.  

The following is code for my summary report.  The results are shown below.

SELECT Distinct ActnCITY, Count(ACCT) as NumbOfAccts,
CASE WHEN ActnCITY in ('NY', 'OH', IN, 'NJ', 'SC', 'NC') THEN 'EAST COAST'
WHEN ActnCITY IN ('CA'. 'NV', 'UT', 'WA', 'OR') THEN 'WEST COAST'
ELSE 'OTHER'
END AS Actn_COAST

FROM tbl1
where ACTNDATE between @STARTDT and @EndDT

Code for my detail report contains the following SQL

SELECT * FROM tbl1 where ACTNDATE between @STARTDT and @EndDT AND @ActnCITY = ActnCITY

I have linked my report based on the NumbOfAccts column.  I am able to get data if I click any of the NumbOfAccts values related to the state I want.  However when I am not sure how to make the subtotal and grand total work.  I want when I click on the subtotal of either coast, I should be able to see records of that coast e.g., if I select 37 I should be able to see all the records in East Coast.  If I click on the Grand Total, I only want data related to those 2 coasts.

View 2 Replies View Related

Creating Databases Under Master

Mar 20, 2007

 When creating databases is it a good idea to use the master.dbo.sysdatabases database and then create databases under this.

View 4 Replies View Related

Creating Databases From .mdf File?

Apr 23, 2001

how do you create a database from an .mdf file?

View 2 Replies View Related

Creating Reporting Databases

Sep 11, 2002

I have a reporting server with 5 databases which are currently being updated via log shipping. These databases need to be (read) accessed by the users. All users have System Admin perms due in order to access the databases. I need to tighten security on the server and remove SA perms from the users. The largest database is about 8gb and is growing slowly. What is the best way to move the databases efficiently from the source server to the reporting server? The log shipped databases can't be backed up and restored due to the standby mode. DTS is an option but may pose time issues due to database size especially as the databases grow in the future. Replication is another option but i have heard it has alot of issues. Any help is welcome. Thanks.

View 2 Replies View Related

Creating Databases On Network Drives?

Jul 11, 2001

Is this possible? I seem to remember doing it with SQL Server 7 a long time ago. The Microsoft Knowledgebase says it's not possible with 4.2 through 6.5, but nothing about 7.0 and up.

View 2 Replies View Related

Creating Duplicate Databases Daily

Nov 6, 2005

I want to be able to create a duplicate database and update / refresh it daily automatically. This database copy would be used for testing purposes so I don't want it to write back to the original database. Is replication of some kind the answer?

View 4 Replies View Related

Creating Report Folders And Report Manager After Fresh Install

Oct 31, 2007



New ssrs istall. I think they loaded sp2. It's a cluster, but ssrs was not clustered (I think)


When I attempt to open report manager, i just get

SQL Server Reporting Services
Home


with no option for creating a folder etc, like i have on development


When I attempt to deploy to ther server I get :

Deploying to http://amsqlclusterxx/reportserver/
Error rsItemAlreadyExists : The item '/Retailer' already exists.


How do I create an ssrs reports folder?


Also, and this is strange, but in vs.net I had to create a new shared datasource after removing the old one on the report with the same name. The report renders fine when I do an f5 pointing to a new datasource, but I no longer have the RUN "!" icon available while in the data tag to run my query. Why is this?


Thanks for any help or information.

View 1 Replies View Related

Creating A Tablename Based On A Date

Feb 25, 2002

Hi,

I am new to SQL Server, and this may be a silly problem .. but here goes!

I need to create tables based on the date ie FEB2000 for EOM reports, I thought I may be able to do it by

1) Check if Table Exists, if so fop it
2) Recreate Table
3) Populate it with data

Unfortunately I'm still on step 1 <sad look>

--->

CREATE PROCEDURE TEST AS

DECLARE @TableName varchar(50)

SET @Tablename = 'FEB2000'

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @Tablename)
DROP TABLE @tablename
GO

<---

I would of course hand the table name as a variable, but for testing purposes used a set variable.

Thanks in Advance

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved