I have a stored procedure that takes a date range and returns all the sales in that date range. I'm trying to create the report model for ad-hoc reporting. When I go to create the dataset view, it only lets me select tables or views.... how do I get around this?
View 2 RepliesI have a datasource view DSV1. It points to a datasource DS1 that is considered the "primary".
I have created a Report Model that uses DSV1 (and thus uses DS1)
I created a new datasource, DS2 that I would like to use instead of DS1. (I can't just modify DS1 because if I modify it, it will overwrite it when we go to our Production environment and break that datasource)
So, I can go into DSV1 and change all the references from DS1 to DS2.
But that's where the problem lies.
When I try to build, I get the following error:
"The Table property of the Entity "E1" refers to the Table "dbo_View", which is not in the primary data source."
Somehow, the entity is tied to the "primary" datasource. When I change it back to DS1, everything works fine. Any thoughts? What can I do?
Hi,
I have found that in the autogenerated model attributes are missing for those fields that have relations to other tables. At first, it may look reasonable since a user can still get down to the field's value through the relation/related table. However, if the relation's key fields is the only thing the user wants to display, then going down to the related table is an overkill.
I can add an attribute manually and bind it to the key field(s). Is there an option in the autogeneration process to do it automatically? The only post I've found so far suggests to do everything manually (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1152575&SiteID=1). Is this the only way?
Thank you,
Leonid
I'm trying to create report with datasource as a cube. I'm able to connect to datasource to pull data with a single parameter. But, I'm unable to succeed in creating a cascading parameter.
The following scenario:
I have a dimension "Period" as Paremeter.
The Period dimension is cascaded to year- quarter- month.
So, I would like to create a cascading parameter for period as year - quarter - month.
Please let me know, how to acheive this.
thanks,
Naveen.
Hi,
I created on model in Business Intelligence Development studio.I wants to change the datasourceview(dsv) assigned for that view.for e.g
My mining Model is StudModel.dmm.
The dsv for that model is StudDSV.dsv.
I wants to change the dsv for that model to marksDSV.dsv.
How to do that in Business Intelligence Development studio.
Thanks,
Karthik.
I've got a strange problem with ReportBuilder. I've created a DataSource name Config and a Model name Customers. We been using this model for month and everything was working fine. Now when we try to create a new report we cannot run it. When Report Builder is running i select the "Customer" model. Then i put some fields on the report. When i try to run it i've got an error saying that it cannot create the command for datasource1. What i don't understand is why the error occured in Datasource1 and not in my datasource config ?
When i open the Report Manger and click on the model "Customer" the datasource use by this model is Config. So the problem is not because they're is a mistake in the configuration. I've tried to rebuild my model and datasource then deployed them but i still have the same problem.
Any idea of what the problem is ? I can't figure out
hi everyone,
I created a report model and deployed it. When I open the rpt builder on web I get the following error.
'The selected data source does not have and content available'
Seemed like it would be a easy issue, but I cannot retrieve any data in the rpt model. The datasource I'm using does work for rpts not associated with the rpt model. Any suggestions? It seems like I've missed a step. Thanks, Lisa
Is there a way to create a Report Model with XML / WebService as datasource?
I am able to generate reports that connect to a WebService through Report Designer, but unable to find similar functionality in Model Designer.
How to create a stored procedure to query the data mining model?
Where can I find some sample code?
Thanks.
Joe.
Hi all,
I'm just getting started with Reporting Services and have a question.
I'm trying to set up a report model based on a UDB data source. However when I use the report model wizard, I get presented with:
[DB2/SUN] SQL0104N An unexpected token "SET TRANSACTION" was found following "BEGIN-OF-STATEMENT" ... SQLSTATE 42601.
I think that this is trying to set transaction isolation levels however this is not valid SQL for a UDB database.
Is there something that I'm missing with my configuration to make RS know that this is a non-SQLServer database?
Many thanks,
JK
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
I am trying to call a stored procedure into report builder. The stored procedure has 2 parameters. When I run the report with allowing nulls and blank value in the parameters it works fine, but when I enter a value in a parameter it ignores the where clause I had in the original query(stored procedure) and displays everything.
View 3 Replies View RelatedHi,
I've created dsv that contain all fields from table database. in the smdl I've remove some fileds due to security. All fields in the smdl do not contain drill.
Issue: When I created calculated field in the report builder the field has a link. When I clicked the drill I saw all the record data including field that not in the smdl.
Questions:
1) Can I remove the link from the calculated fields?
2) Can I prevent from users drill to fields that not in the smdl?
Thanks,
Assaf
I'm receiving the following message after I've created a datasource and datasource view:
[DB2/SUN] SQL0104N An unexpected token "SET TRANSACTION" was found following "BEGIN-OF-STATEMENT" ... SQLSTATE 42601.
I have scoured the internet looking for someone who has run into this issue with no such luck!
Product: Reporting Services 2005 Analysis Services 2005
Our users use Report Builder as an ad-hoc report tool. Data sources for Report Builder come from Report Servers and Analysis Servers.
I have run into issues where a user will select a source for their report that is based on an AS cube. When they select an attribute (field) from a selected entity in the Report Builder Explorer and drop it on the canvas, all entities except the one the field was selected from disappear.
For example:
A cube has a fact table called "Annual Income" with dimension of employee and demographics. In Report Builder, the entities would be listed in the left hand Report Builder Explorer window as
Annual Income
Employee
Demographics
If I select attributes from Employee or Annual Income, as usual, you will see the related hierarchies of the remaining entities. However, if I select an attribute from Demographics, then the Annual Income and Employee entities just disappear and all I can see is the Demographics entity and its attributes.
Has anyone run into this and if so, any ideas? This is becoming extremely frustrating. I have deleted and rebuild cubes, individual dimensions, and everything else I can think of.
Your responses are appreciated,
Scott
Is it possible to dynamically refresh the report model of the report builder?
could it even be using code with any of the interfaces?
When we add a table or add a column to the table in database , will the report model get refreshed automatically or do we need to do it externally. If so, can we use any of the objects and write a custom code in VB.
Hi,
My environment:
Computer A
- SQL 2005 SP2 (32 bits)
- Oracle 9i client
I am the local administrator
Server A
- Oracle 9i Enterprise Edition (64 bit)
I have read only access to Oracle DB.
I am able to create a report model with Oracle DB and deployed it successfully. In my report model (rmOracle), I named my data source: dsOracle and dsvOracle for my data source view which contains one named query. Then I set a logical primary key for the named query, everything is OK so far.
Now, in Report Builder (RB) I have created a simple report (table) based on the report model I created before. Then when I preview it, I got the following error message:
Microsoft Report Builder:
Report execution error
The report might not be valid or the server could not process the data.
Details:
System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.
----------------------------
Cannot create a connection to data source 'dataSource1'.
----------------------------
An error has occurred during report processing.
I tried to look more information by saving the report and run it from the report server directly. And I got the following error message:
An error has occurred during report processing.
Cannot create a connection to data source 'dataSource1'.
System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.
I open the RDL file and found that when creating a report with RB, it creates its own data source, which is fine. The problem here is that data source points to the wrong connection, which I thought it should point to the same connection of the report model its based on. In this case it should point to Oracle connection (dsOracle).
Anyone has any idea how to solve it? Or did I miss something here?
Thanks and Regards,
Uzzi
Is it possible to drop and then create a view from a stored procedure? Like the way you can drop and create a temp table.
I want to create a view of the fields in a table something like: But I cannot include the field names, they may be changed by an admin user.
If exists view 'custom_fields"
drop view 'custom_fields'
Create view custom_fields
Select * From tblCustomFields
And make this a view in the db named custom_fields.
And I want to call it from a button click in my UI.
Experts
I am trying to create a view or Stored Procedure between different table
Table1 consist of the follwing Fields:
Ref_No: String hold the reference number, Unique
Details: String
Table2:
MasterRefNum : String, not Unique
SubscriberRefNum : String, not Unique
What I am trying to do is that when the user enter a refernece number the system should return back
1- the details where Ref_No = the required refernece number
2- get all the SubscriberRefNum from Table2 where MasterRefNum = the required refernece number and from the Table1 get the details for those SubscriberRefNum numbers
Any advice?
hi,
Im am wandering if it is possible to create two views in two different tables from within the same stored proc:ex
create proc myProc
as
use [myDb1]
go
create view myV1
as
select * from mytable
go
use [myDb2]
go
create view myV2
as
select * from mytable
go
go
---
of course the go's are not allowed in a sproc, the create statement must be the first of a query batch and a vew can not have the databaase name preapended like when creating a table plus one can not use the "use" word in a proc, I tried using exec to bypass the "first statement in a batch" and go restrictions but have not been able to overcome the "use [myDb]" restriction, is there a way to solve this problem?
thank you
Is it possible to dynamically create an sql create view statement then execute that sql statement? Or because create views must be the first statement in a query batch, it's not possible?
View 6 Replies View RelatedI have built a data model in visual studio that I am using with report builder in reporting services. The data model is using data from a view in SQL Server 2005. I am trying to apply a filter to the data model to restrict the data available in report builder. I know that I could change the code for the view and filter that data that way, but I would rather creates two models each one producing differnt sets of filtered data. I have tried adding a filter to the data model, but it does not seem to work. Do anyone have any suggestions?
View 3 Replies View RelatedHi guys,
I created a simple report model, which includes both tables and views as a data source view. So here are the problems I am facing when I try to use a report builder to build a report.
1. In the Report Builder window, on the FIELDS column, I am unable to see the primary key column of any of the tables.
2. I am unable to see the VIEWS in the ENTITIES column of the Report Builder; I can see only the tables.
Does anybody has some idea to deal with the above issues?
Thanks!
Hi all
I have created security roles that restrict access to a certain Dimension
and a member therein.
The security works fine when the users finened in it runs a report:
The data is accordingley ristricted.
The problem is when those same users run Report Builder and create a report,
those members are no longer restricted and they have acces to absoluteley
all data from the cubes.
Is there some way that I can force the Data Model to follow the cube's security roles?
Any help is much appreciated...
Gerhard Davids
EDIT:
I have found that it uses the wrong user when opening report builder.
For some reason it uses my windows account instead of the one I used to log onto
report manager. This is way the security isnt working
Any thoughts what may be causing this?
I get the following error when I try to deploy a report builder model. The SQL Server 2005 "Reporting Services" are installed and running on the local machine.
TITLE: Microsoft Semantic Model Designer
------------------------------
A connection could not be made to the report server http://localhost/ReportServer.
------------------------------
ADDITIONAL INFORMATION:
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:
--
<html>
<head>
<title>
SQL Server Reporting Services
</title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.2047.00" />
<meta name="HTTP Status" content="500" />
<meta name="ProductLocaleID" content="9" />
<meta name="CountryLocaleID" content="1033" />
<meta name="StackTrace" content=" at Microsoft.ReportingServices.Diagnostics.RSConfiguration.Load()
at Microsoft.ReportingServices.Diagnostics.RSConfiguration.Construct(String configFileName)
at Microsoft.ReportingServices.Diagnostics.RSConfiguration..ctor(String configFileName, String location)
at Microsoft.ReportingServices.Diagnostics.RSConfigurationManager..ctor(String configFileName, String configLocation)
at Microsoft.ReportingServices.Library.Global.get_ConfigurationManager()
at Microsoft.ReportingServices.WebServer.Global.StartApp()
at Microsoft.ReportingServices.WebServer.Global.Application_BeginRequest(Object sender, EventArgs e)" />
<style>
BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black}
H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt}
LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline}
.ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray}
A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none}
A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#FF3300; TEXT-DECORATION:underline}
A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none}
A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; color:#FF3300; TEXT-DECORATION:underline}
</style>
</head><body bgcolor="white">
<h1>
Reporting Services Error<hr width="100%" size="1" color="silver" />
</h1><ul>
<li>The report server has encountered a configuration error. See the report server log files for more information. (rsServerConfigurationError) </li><ul>
<li>Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.4Reporting ServicesReportServerRSReportServer.config' is denied.</li>
</ul>
</ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span>
</body>
</html>
--. (Microsoft.ReportingServices.SemanticQueryDesign)
------------------------------
BUTTONS:
OK
------------------------------
If I were to create a stored procedure that searches a table using (optional) multiple parameters, what would be the best way to do the search. I want to try and avoid using several "IF" statements (like IF @FirstName IS NOT NULL, etc). How would I do it, or would I just be better off using several "IF" statements? Thanks...
CREATE PROCEDURE intranet_search_GetEmployeesBySearch
(
@FirstName NVarChar(100),
@LastName NVarChar(100),
@Phone NVarChar(50),
@Cell NVarChar(100),
@Pager NVarChar(100),
@Ext NVarChar(50),
@Email NVarChar(100),
@Department NVarChar(200),
@Position NVarChar(100),
@IsManager Bit
)
AS
BEGIN
SET NOCOUNT ON;
END
GO
I am trying to develop a data model that will filter data based on the user logged in. SUSER_SNAME()
I have a list of User Id's in a look up table, a where clause in a view that filters the data based on who is logged in. This works fine and the data even seems to be filtered when I explore the data in the Data Source View. How ever when I publish the data model to the reporting services site and use report builder the user logged in is no longer recognized. All I am really trying to do is set up report builder so that users can generate reports and only have access to thier data via a customer number. I just can't allow users to see other users data and I don't want to set up a different datasource view for each user. Does any one have any experience with this?
I am trying to create a dynamic SQL statement to create a view.I have a stored procedure, which based on the parameters passed callsdifferent stored procedures. Each of this sub stored procedure createsa string of custom SQL statement and returns this string back to themain stored procedure.This SQL statements work fine on there own. The SQL returned from thesub stored procedure are returned fine. The datatype of the variablethat this sql is stored in Varchar(I have tried using nvarchar alsosame problem).If I have more that 6 SQL statements concated then the main SQL getscut off. It doesnt matter in what sequence I create the main SQL.Here is the Stored procedure/**********************************************//*Main Stored Procedure *//**********************************************/CREATE PROC sp_generate_invoice1 @prev_date NVarchar(1000) ,@prev_month NVarchar(32)ASDECLARE invoice_driver_cur CURSOR FORSelect driversid From Invoice_driversOpen invoice_driver_curDeclare@C VARCHAR(8000),@L_args Varchar(8000),@@sqlstmt Varchar(8000),@L_driverid Int,@L_rowcount IntSET QUOTED_IDENTIFIER ONSET TEXTSIZE 32768Set @L_rowcount = 0-- Drop the previous ViewIF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWSWHERE TABLE_NAME = 'custom_invoice')DROP VIEW custom_invoiceFetch Next From invoice_driver_curInto @L_driverid-- Create the new ViewSet @L_args = N'Create View custom_invoice As'--Select @L_driveridWHILE( @@FETCH_STATUS = 0)BEGINSet @L_rowcount = @L_rowcount + 1select @L_driveridIf @L_driverid = 2BeginExec sp_invoice_driver2 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @L_args + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 3BeginExec sp_invoice_driver3 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 4BeginExec sp_invoice_driver4 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 5BeginExec sp_invoice_driver5 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 6BeginExec sp_invoice_driver6 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 7BeginExec sp_invoice_driver7 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 8BeginExec sp_invoice_driver8 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 10BeginExec sp_invoice_driver_niku @prev_date, @prev_month, @L_driverid,@@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndPrint @CFetch Next From invoice_driver_curInto @L_driveridContinueEndClose invoice_driver_curDeAllocate invoice_driver_curExec (@C)--EXEC sp_executesql @CGO/**********************************************//*Sub Procedure sp_invoice_driver2 *//**********************************************/CREATE PROC sp_invoice_driver2 @args NVarchar(1000), @prev_monthNVarchar(100),@sqlstmt Varchar(8000) OutputASSET QUOTED_IDENTIFIER ONSET @sqlstmt = ' Select 1 SortOrder ,( SELECT Drivers.Description) Description,(BillingReport.Active_Accounts * Cast(Fee.fee_rate As decimal(4,2))) / 12 Amount,Drivers.CurrencyFROM BillingReport, Drivers, FeeWHERE ( Fee.Driverid = Drivers.Driversid ) andDrivers.Driversid = 2 andbillingreport.fromdate = ''' + Cast(@args As NVARCHAR(20)) + '''andfee.currentmonth = ''' + Cast(@prev_month As NVARCHAR(12)) +' '''GO/**********************************************/This is what the Print Statement give:/**********************************************/Create View custom_invoice As Select 1 SortOrder ,( SELECT Drivers.Description) Description,(BillingReport.Active_Accounts * Cast(Fee.fee_rate As decimal(4,2))) / 12 Amount,Drivers.CurrencyFROM BillingReport, Drivers, FeeWHERE ( Fee.Driverid = Drivers.Driversid ) andDrivers.Driversid = 2 andbillingreport.fromdate = '9/1/2004' andfee.currentmonth = 'September ' Union Select 2,(SELECT Drivers.Description),(BillingReport.Zero_Balance * Cast(Fee.fee_rate As decimal(9,2) ))/ 12 Amount,Drivers.CurrencyFROM BillingReport, Drivers, FeeWHERE ( Fee.Driverid = Drivers.Driversid ) andbillingreport.fromdate = '9/1/2004' andfee.currentmonth = 'September' andDrivers.Driversid = 3 Union Select 3,(Select Drivers.Description From Drivers Where DriversID = 4),Count(*) * Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 4 )As decimal(6,2)) / 12,(Select Drivers.CurrencyFrom Drivers Where DriversID = 4)From Fund Union Select 4,(Select Drivers.Description From Drivers Where DriversID = 5),(((Sum(BillingReport.Man_Reg_Purch + BillingReport.Man_Reg_Red +BillingReport.Man_Reg_Transexch +BillingReport.Man_Allo_Purch +BillingReport.Man_Allo_Red +BillingReport.Man_Allo_Transexch +BillingReport.Man_Allo_Adj_Purch +BillingReport.Man_Allo_Adj_Red +BillingReport.Man_Allo_Adj_Transexch +BillingReport.Man_Adj_Purch +BillingReport.Man_Adj_Red +BillingReport.Man_Adj_Transexch ) ) +(Select Sum(Cast(satuscnt As int ))From Awd_stubWhere CurrentMonth = 'September'))) *(Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 5 )As decimal(6,2))),(Select Drivers.Currency From Drivers Where DriversID = 5)FROM BillingReportWhere billingreport.fromdate = '9/1/2004' Union Select 5,(Select Drivers.Description From Drivers Where DriversID = 6),( Sum( BillingReport.Auto_Reg_Purch +BillingReport.Auto_Reg_Red +BillingReport.Auto_Reg_TRansexch +BillingReport.Auto_Allo_Purch+BillingReport.Auto_Allo_Red +BillingReport.Auto_Allo_Transexch+BillingReport.Auto_Allo_Adj_Purch+BillingReport.Auto_Allo_Adj_Red+BillingReport.Auto_Allo_Adj_transexch+BillingReport.Auto_Adj_Purch+BillingReport.Auto_Adj_Red+BillingReport.Auto_Adj_Transexch )+(Select Sum(Cast(Processed_msg As Int))From XML_messagingWhere CurrentMonth = 'September'))*(Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 6 )As decimal(6,2))),(Select Drivers.Currency From Drivers Where DriversID = 6)FROM BillingReportWhere billingreport.fromdate = '9/1/2004' Union Select 6,(Select Drivers.Description From Drivers Where DriversID = 7),( ( a.Accountholder_Active_Accounts -(select accountholder_active_accounts from billingreport whereMonth(fromdate) = Month('9/1/2004')-1 ) )+( a.Accountholder_Zero_Balance -(select accountholder_zero_balance from billingreport whereMonth(fromdate) = Month('9/1/2004')-1 ))) *(Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 7 )As decimal(6,2))),(Select Drivers.Currency From Drivers Where DriversID = 7)FROM BillingReport a Where a.fromdate = '9/1/2004' Union Select 7,(Select Drivers.Description From Drivers Where DriversID = 8),( Select telephone From cfxbill Where currentmonth = 'September') *(Cast((select feThanks for any helpMD
View 4 Replies View RelatedCan anyone tell me how can I create a table in (SQL Server 2000) direct from a stored procedure execution or from a SELECT result?
I need something like this: CREATE TABLE < t > FROM <sp_name p1, p2, ...>or like this:
CREATE TABLE < t > FROM SELECT id, name FROM < w > ...
Thank you!
Hello - I am having difficulty enabling the automatic click-through functionality for my model. I have two entities - Customer & Incident. Incident is a role/FK/OptionalMany relationship to customer.
I've read through earlier postings on this subject (below) and have verified that the #Incidents attribute in my incident entity has the EnableDrillThrough=true.
My test report is simple - Customer Name (from customer) and #Incidents (from incident). When I run it, the click-through option is not available (the cursor stays as an arrow and doesn't change to a finger).
Are there other properties or settings that I need to be working with? I have SP2 (9.0.3042) installed on our Standard edition of SQL 2005. I know Infinite Click-Through is not in Std edition, but a few levels should be, correct?
Thanks in Advance,
Toni
Other related postings -
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1260819&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=657279&SiteID=1
I am trying to add a calculated field / column in Report Builder when working with a Report Model built from anAnalysis Services Cube. I can create the calculated Field/Columns, but I get an error whenever I try to use it in a report.
Is there a way to create a report builder calculated column on report models built from a SSAS cube? Is this supported?
Thanks,
I have the following stored procedure:
Code Block
CREATE PROCEDURE udsp_td_queryowner
@state varchar(10) = NULL,
@businesstype varchar(20) = NULL,
@size int = NULL,
@sortorder varchar(20) = 'state'
AS
SELECT gl_t.state AS [State],
gl_t.business_type AS [Business Type],
gl_t.lowsize AS [Low Size],
gl_t.highsize AS [High Size],
e.InternetAddress AS [Owner]
FROM gl_territory gl_t JOIN employee e ON gl_t.employeenumber = e.EmployeeNumber
WHERE state = COALESCE(@state, state) AND
business_type = COALESCE(@businesstype, business_type) AND
COALESCE(@size, lowsize, highsize) between lowsize AND highsize
ORDER BY CASE WHEN @sortorder = 'state' THEN gl_t.state
WHEN @sortorder = 'business type' THEN gl_t.business_type
WHEN @sortorder = 'owner' THEN RTRIM(e.FirstName) + ' ' + RTRIM(e.LastName)
END ASC,
CASE WHEN @sortorder = 'low size' THEN gl_t.lowsize
WHEN @sortorder = 'high size' THEN gl_t.highsize
END ASC,
CASE WHEN @sortorder = 'statedesc' THEN gl_t.state
WHEN @sortorder = 'business typedesc' THEN gl_t.business_type
WHEN @sortorder = 'ownerdesc' THEN RTRIM(e.FirstName) + ' ' + RTRIM(e.LastName)
END DESC,
CASE WHEN @sortorder = 'low sizedesc' THEN gl_t.lowsize
WHEN @sortorder = 'high sizedesc' THEN gl_t.highsize
END DESC
What it allows me to do is enter in any number of the variables when running the stored procedure. For example, EXECUTE udsp_td_queryowner @state = 'IA' would give me everything in the table in the state of IA regardless of the other field values. Likewise, if I ran EXECUTE udsp_td_queryowner @state = 'KY', @size = 15 it would return the records that are in KY and the size of 15 is in the range of the low and high value.
If I run the first example in Query Analyzer I get 53 records from the data I have. It returns every record that has IA as the state. I run the same thing in Reporting Services and all I get is 3 records. Just the 3 records for the state of IA where the business type is either null or blank (I can't tell.) If I allow all of the variables to accept Nulls then it returns the correct data. However, I would like to find a better alternative because when the report is run it returns all of the records in the table initially and if they user wants to enter in any parameters they have to toggle off the null box for the corresponding paramter.
Any ideas?
1. As per my current development SQL Sever Analysis Database consists of two Cube (Cube A and Cube B). Cube A and Cube B share the same data source view (DSV1). The source for both these cubes has the same data source (DS1).
2. As per the requirement I need to create third Cube i.e. Cube C. Is it possible to create a second Data Source View (DSV2). The Source of second Data Source View (DSV2) will be the same data source(DS1).
I am thinking to create second Data Source View (DSV2) for Cube C because existing layout of the DSV1 has become complex. I wanted to know the pros and cons of creating a multiple data source view with same data source