Reg:CSDW_sql, Csdw_olap Datasources In Commerce Server 2007 Dataware House Reports
Mar 25, 2008
hi all,
i unpacked starter site and dw.pup file and i got 34 reports provided by commerce server 2007. i want to edit these reports.
i followed these steps
1)created a "Report server project" in Visual studio 2005
2)In that project in "Shared data sources" folders i added "Startersite_Datawarehouse" of type "Microsoft SQL Server"
3)i added another shared data source name "Startersite_Datawarehouse" of the type "Microsoft SQL services analysis services"
4)later i added one of the reports to "Reports" folder by selecting "Add existing item".
But in design mode of that report when i clicked "Preview" tab i am getting the error
"an error occured during the local report processing. The item /CSDW_olap cant be found"
when i clicked "data" tab i am getting this below error
"Connection cant be made to database"
I need some help from u. This is datawarehousing related stuff. I am having a source table as "test" and target table as "trg". I need to extract the data in required format as per below loading instructions and then load the data into "trg" table. Below sample data is only given one zipcode.There can be several codes.
drop table test
create table test ( currentyear int, district varchar(10), school varchar(10), rollno int, zipcode varchar(10), flag1_handicapped char(1), flag2_disadvantaged char(1), status varchar(10), relation varchar(10) ) /* inserted 11 rows */ insert into test values(2005,'D1','S1',101,'530024','Y','Y','E','R' ) insert into test values(2005,'D1','S1',101,'530024','N','N','E','R' ) insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ') insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ') insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ') insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ') insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ') insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ') insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ') insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ') insert into test values(2005,'D1','S1',101,'530024','Y','Y','E','NR ')
select * from test
--- Structure of the target table create table trg ( cyear int, district varchar(10), school varchar(10), RollNo int, zipcode varchar(10), type varchar(20), /* This is an extra column with hard coded values which we need to assume as Total,flag1_handicapped,flag2_Disadvantaged.For Every unique zipcode i need to GROUP BY these 3 values. These values never come from the source table i.e "test".But we can make use of the 2 source columns "flag1_handicapped" & "flag2_Disadvantaged"*/ actaul_cnt int, empl_related int, empl_not_related int, modified_date datetime )
-- The below table shows what values should get loaded into trg table
---------------------------------------------------------------------- trg table columnvalue to be loaded Description ----------------------------------------------------------------------- cyear test.currentyear districttest.district schooltest.school rollnotest.rollno zipcodetest.zipcode type /* here we to load 3 rows with 3 values This table contains some calculated columns. such as "actual_cnt","empl_related","empl_not_related" and so on... Every calculation should be grouped by this "type" column.For reference the you can see the bottom output rows how they should look like. The 3 valid values for this type column is "Total","flag1_handicapped","Disadavantaged". "Total" means = All the records which satisfies the calculation. "flag1_handicapped" means = All the records which statisfies the calculation and have test.flag1_handicapped = 'Y' "flag2_Disadvantaged" means = All the records which satisfies the calculation and have test.disadvanatged = 'Y'*/
actaul_cnt This is a calculated column. The calc is as follows: count of records grouped by currentyear,district,school,zipcode,type(flag1_han dicapped,flag2_Disadvantaged,total)
empl_relatedThis is again calculated column. The calc is as follows. count of records where status='E' and relation = 'R' grouped by currentyear,district,school,zipcode,type(flag1_han dicapped,flag2_Disadvantaged,total)
empl_not_related This is again calculated column. The calc is as follows. count of records where status='E' and relation = 'NR' grouped by currentyear,district,school,zipcode,type(flag1_han dicapped,flag2_Disadvantaged,total)
Here is the sample template which i felt like using to load the data. We need to modify this query littlt bit accordingly as per above rules.
select currentyear as "CYear", district as "District", school as "School", rollno as "RollNo", zipcode as "zipcode", count(*) "actaul_count", sum(case when (status='E' and relation='R') then 1 else 0 end) "Emp_Related", sum(case when (status='E' and relation='NR') then 1 else 0 end) "Emp_Not_Related", getdate() "Date" from test group by currentyear, district, school, rollno, zipcode
/* Using the above query we need to load 3 rows into below target table whose structure is defined as follows */
------------------------------------------------------------------------------------------------------------------------ But using above SELECT,i am able to get only row as output that to i am not able to show the "type" column in the output
------------------------------------------------------------------------------------------------------------------------ Basically i am not getting how to build the Group by clause and displaying the type code using above rules. Can anyone help me out in solving the problem. Do we need to perform any UNION ALL ON test.flag1_handicapped and test.flag2_Disadvantaged columns.? This is totally seems out of box for me.
I am into starting a new dataware house project where i will be loading data from several sources to respective tables within databases.Are there any basic things which i need to set in doing this.
Iam using Sql Server Integration Service to transfer the data. I have to methods to transfer the data.
Method -1
Daily cleaning Dataware house Data base and transfering the data from Source database.
Method -2
Only new rows transfering from the Source database to dataware house data base.
If i use first method, Any performence issues's will come in the future?. In future my source data will have upto 6 lacks records.
If i use second method, daily date based i can transfer the data. But if they delete any previous records from the source database how can i reflect the same in Data ware house Data base.
I wonder if anyone can help me save the few remaining hairs on my head? I am trying to deploy reports created in SQL Server Business Intelligence Development Studio to SharePoint running SQL Server 2005 Reporting Services in SharePoint integrated mode. Whenever I try to deploy a report, the Reporting Services Login window pops up no matter which user/password I provide it won't accept any. I have the Reporting Services database set up to use Windows credentials and my Domain adminstrator account so I presume that this is user that I should be providing when prompted when deploying.
hello when i try to view reports in reports manage i get the following error
The type Microsoft.SharePoint.Portal.Analytics.UI.ReportViewerMessages, Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c does not implement IReportViewerMessages or could not be found 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: Microsoft.Reporting.WebForms.InvalidConfigFileTypeException: The type Microsoft.SharePoint.Portal.Analytics.UI.ReportViewerMessages, Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c does not implement IReportViewerMessages or could not be found
Source Error:
The source code that generated this unhandled exception can only be shown when compiled in debug mode. To enable this, please follow one of the below steps, then request the URL:
1. Add a "Debug=true" directive at the top of the file that generated the error. Example:
<%@ Page Language="C#" Debug="true" %>
or:
2) Add the following section to the configuration file of your application:
Note that this second technique will cause all files within a given application to be compiled in debug mode. The first technique will cause only that particular file to be compiled in debug mode.
Important: Running applications in debug mode does incur a memory/performance overhead. You should make sure that an application has debugging disabled before deploying into production scenario. Stack Trace:
I am having multiple URL in multiple zones for my site.I am not able to view SSRS reports on Internet which is in Internet zone where as i can view the same reports on Intranet site having URL in default zone. I m getting the following Error while accessing reports over internet:
" Unable to connect to the remote server.No connection to be made because target machine actively refused."
It would be great help if any one can solve this problem.
In short, we have started deploying Office 2007 to our users and Excel is currently the only client we use to interact with our AS2005 cubes.
A few users have reported issues (which I've verified), but the explanation in the KB article doesn't make any sense to me. These reports were originally developed in Excel 2003 and when opening them up in Excel 2007, we'll see a message saying that Excel found unreadable content in the .xls file and after clicking 'Yes' to recover contents of the workbook, we then receive a message that a PivotTable report was discarded due to integrity problems. If I opened up this report in Excel 2003, I don't receive these errors or messages.
Per the KB's explanation (http://support.microsoft.com/default.aspx/kb/929766):
This issue occurs if the following conditions are true: €¢The workbook contains a PivotTable that uses key performance indicators (KPIs).€¢The KPIs are created in the Analysis Services Business Intelligence Development Studio.€¢One or more of the KPIs have an expression in the Current Time Member property. Now, we are running 2005 Standard Edition with no SP, but will be deploying SP2 in a few days. Our cubes do not have any KPIs defined. Can I even define KPIs if we are only running Standard Edition?
I am wondering, in theory, how I would use a typical one-way hash function to protect customer password on my website server (which is a small e-commerce server)?
does anybody know how I would create a connection string in asp.net 2.0 express to access a database in a different location from the web server. I have a static IP address on the web server, so I've allowed access from the web server through the firewall. I have a user name and password, and I've made sure that I have the correct database. Would I access the database using the network IP address and if so, where do I put it. Thank you to anyone that can give me some insight to this problem, I'm very new to asp.net so, about a weeks worth of knowledge, so if you could explain as such I would appreciate it.
Hi everybody. I am new to VBA,.,, I Have created a folder under inbox in Outlook 2007 which stored the receipients information like name, E-mail, which is not present in the address book of outlook. I have imported this folder in access 2007. My problem is ... it does not display the email address. Rest of the informations is displayed... Is there any way i could do this.?? is there any way i could get the email addresses to appear(import/get) in to the access table??? Is there any module i mite have to create??
Hi everyone, I am using VS2005, just installed SP1, and the help -> about window reflects this. I have also installed the SQL Server Compact Edition SDK, but when i try to add a new data source connection (following the MANY tutorials on this), i do not see an option for SQL Server Compact Edition nor do i see an option for SQL Server Mobile. I am at my wits end and i just got started! Yikes... Any thoughts on how to proceed?
insert @table select '123b apple avenue' union all select '12b apple avenue' union all select '3b apple avenue' union all select '32 apple avenue' union all select '322 apple avenue' union all select '3 apple avenue'
select * from @table
how to select the records where the house number contain letter.
My name is Dr. James Triton. I am working on a research program for an unnamed University. Without getting into too much detail, I was wondering if any lower level college (or perhaps late high-school, depending on the strength of your Computer Science program) would be interested in filling out a few SQL queries, making an ER model or DDLs for a database.
Here is some example questions:
1.List each employee's first name, middle initial, and last name, concatenated to form a single value, as well as how many years they have been working for the company. An example name would appear as "JOHN Q SMITH". Sort the output by descending number of employment years. (HINT: create a column alias.) 2.List all of the job functions (such as "CLERK"), and the last names of all employees associated with each function. Order the output alphabetically by the functions. (HINT: there are a total of seven different functions.) 3.List the last name of every manager, along with the last names of the employees that the manager manages. Create column aliases "MANAGER" and "EMPLOYEE", and order the output by manager last name.
(there are 15 such queries) The table for these is located here:
I'm only have permition to query table data and with local sql server installed. Also, capable link server from local machine to live sql server (Blue). Getting error type when ran a query that commons: Column name or number of supplied values does not match table definition. Others, ambiguous column name " ". Here are the information below before i ran the query.
In house data:-Table fields contain (address,city,state,zip,zip4,fips) -All data type fields are character -No Primary Key
Local machine table: -Fipscodes (table) contain data was provided by customer that I inserted into my machine and column fields (state,zip,fips) made Zip as Primary Key.
So, I wants to able run a query to retrieve data from live server by using some kind of join table with table(FipsCodes)locate in my local machine.
FROM blue5.Homeowner.dbo.homeowners AS h INNER JOIN FipsCodes AS n ON h.FIPS = n.FIPS
WHERE state ='AL' AND fips='001' AND pubDate >= '12/1/2006' AND pubDate <='1/8/2007'
Error occur: Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'State'. Server: Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'FIPS'.
, please help me solve this issue and thank you very much everyone.
Hello SQL Server Experts, Data Analysts, and Report Writers et al:
re: Reporting Options with SQL Server
I wanted to propose an offshoot to the pryor thread:
Would anyone take a stab at comparing Access Reports, Crystal Reports, Cognos or other options to all the Reporting Services and its components offered as part ofSQL Server, especially as to extracting data from SQL Server into a report format?
I guess this is a far as capabilites, ease of use, limitations, and especially formatting or presentation of the end report product?
Thank you to all, and I hope this is a beneficial discussion to others.
A few weeks ago I've done a SQL Server migration from SQL Server 2000 to SQL Server 2014.It was a bit tricky but anything worked fine.
I have some legacy VB6 (Visual Basic 6) applications written in house which worked with Databases on the old SQL server 2000.Surprisingly, these applications worked well after the upgrade to SQL Server 2014 without having to change a piece of code.
Now, some users tell me that they receive some unusual message when saving data from these legacy applications.After investing for a few hours, I discovered that triggers are not executed when those users try to save data from grids or forms in their applications.Trying to reproduce the INSERT statement in SQL Server Management Studio, the triggers run well.From the application, they don't.
These applications connect to Database Server thru OLEDB connection with the following ADO connection string :
Provider=SQLOLEDB.1;Password={password};User ID={user};Initial Catalog={db};Data Source={datasource}.the {user} is a true SQL account who have read/write/delete access in the databases.
On the web there is a lot of questions involving the same issue, but only from SSIS.I found some articles about an OLEDB connection parameter named FastLoadOptions with a value of FIRE_TRIGGERS, but nowhere how to put it in the ADO OLEDB connection string.
how to reactivate the "normal" use of triggers from an ADO OLEDB connection ?Either with some obscur parameter in the connection string or options somewhere in the SQL Server 2014.
I have an intranet environment consists of two servers:
- An application server windows 2003 server with 64-bit server Sharepoint 2007 SP1 (Standard version for search) - A database server windows 2003 server with 64-bit SQL Server 2005 SP2 64-bit (Standard - 9.0.3054)
It happens regularly that the database server crash (frozen black screen) while the application server indexes (crawl) the content of the intranet site based on sharepoint. There is no alert / error in the observer of events, nor in the sql logs.
The crash is uncertain: dice once all goes well, soon after that crash. When i set the parameter search service (service management research), I can define a regulatory impact of the robot to change the number of documents at the same time : "Request 2 documents at the same time, the crash is more rare, "ask 64 documents at a time, the crash is more common.
My Intranet under sharepoint is composed of a collection site with a dozen sub-sites. There are 3 large library of PDF document. All done in the 4 go in terms of volume.
My problem for about 3-4 months. (Maybe more. Before, indexing was not yet in place).
hi everybody I've been working in the database field for 9 months I started from very simple tables, to some sql stuff And then I started studying SQL Server 2005 extensively until I took MCTS SQL Server 2005 During that time, I was building a database for a new company from scratch, and while I build it for them I keep on reading books and learning, and using this knowledge to upgrade the database Recently, I could successfully migrate the back-end to SQL Server 2005, and after days of struggling I could connect the front end to it via ODBC in a LAN My next step is connecting the database through the internet, so I just changed the server name in the DSN file to the address of the server, and opened the ports, but it didn't work.... Can you tell me what's the problem? Is there a book that can help me do this thing? I can buy any book and I have the will to learn, provided that I'll apply for MCITP Database Developer next months
I have installed System Center Essentials on our network. Everything seems to be working but we are now getting Perflib errors (event id 1008) on our windows 2000 servers. This error indicates there is a problem with our ASP.net v2.xx.xx.xx. We have been unable to resolve this and are looking for more things to try.
i have made some Data Mining Model Examples in Excel 2007 (not temporarily!). They where there after leaving an re-opening Excel. I have used them several times. Then I want to look, if I can see them also via SQL Server Managment Studio in the Analysis Services. There where nothing in the DMAddInDB in Analysis Services.
And after this, in Excel my DataMining Models have disappeared and all Models i have made since this disappeared also.
Perhaps I have destroyed the database. But will this happen every time? Can I share Data Mining Models I have made with Excel with Projects in SQL Server Analysis Services?
I have a problem with connecting to our SQL Server 2005 through Access 2007. I get a msg "ODBC-- failed. [Microsoft][ODBC SQL Server Driver][SQL Server] SELECT permission denied on object 'clients', database 'Contacts', schema 'dbo' (#229)" We have moved some of our tables from Access over to SQL Server 2005. I recently got Access 2007 and I'm the only one at the office that has this problem (everyone else uses Access 2003 or older). I can link tables to SQL Server but I can't open them and I can't open Forms or Reports that has those tables as data source. I can open other objects in Access that are not linked to SQL Server.
Is it possible to run Microsoft project server 2007 on LAN without using Active Directory Is it possible to keep Active Directory on a separate machine and install project server 2007 on a different machine.
One being a query which sums up claims for a group of members. Col A beign the memberID and B and C being amounts.
Now I set the output to Isosrted = true and set the output column A sorted property to 1.
The other OLE source is a table. Col A being member and b,c,d.. being name, address... I set the same properties for column a and I am trying to use a merge join.
The first two sources return 2245 rows and after the merge I end up with 648.
What would cause the merge to strip some records out?
I have several SSIS packages which I have setup using a datasource to connect to the database. I should be able to switch between databases by just changing the server and database on the datasource but for some reason the ssis package keeps on using the old database. I have the connection managers setup to reference the datasource and can see the connection manager pointing to the new database but when I run a task by right clicking on it and choosing execute task it calls the stored procedure in the old database. I know it is doing this because I renamed the stored procedure in the old database and the task errored out saying it could not find the stored procedure. Can someone please tell me what I am doing wrong?
Hmm.. where to start? Well, I have installed proj serv 2007 and have it up and running fine. I was recently asked to get the data analysis portion of the proj server working. After much digging for some guides, I found a few helpful and got sql analysis server installed with everything else it need to operate. (as far as I know). One of the guides that I have encountered and using asks me to use sql studio management to access the analysis server. Problem is, I dont have the option to choose a server type. (Its greyed out) Which only has the option for "Database Engine". Furthermore, I cannont complete the steps required without this option. Could someone please, please, point me in the right direction and possibly tell me what Ive done wrong. Project server 2007 documentation is horrible. Just to be able to use the server has been a process.
I recently installed MS Accounting Express 2007 which also installed an instance of SQL Server 2005 but without the standard interface. But I want to install SQL Server Express 2005 as well. Can the two coexist? Is there a way to have both applications running on the same machine? I am asking before trying the standard SQL Express install. Thanks, Jim.
