Creating a VIEW based on other VIEW.

Hi everyone,
Let's say I have a VIEW that is created by some complex joins - This
VIEW is used by many of the application's function. Let's call this
"VIEW_1".
"VIEW_1" select queries are already created using "with (nolock)".
Let's say a new system requirement comes in - I notice that it may be
better if i create another view "VIEW_2" based on "VIEW_1".
eg:
create view VIEW_2
as
(
select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
)
For such cases, what are the performance issues I should watch out
for? WIll there be any performance issues? Should I be doing this in
the first place?
In SQL 2005 mgmt studio, how do I view the execution paths and
timings?
Please advise.
Thanks.
If your query completes in some reasonable time and does not take too much
resources that may be needed for other users then it is not a problem.
You can take a look at the performance and resources used by your query by
using all or some of these:
set statistics io on
set statistics time on
set statistics profile on
set statistics xml on
You can also display the graphic execution plan in SSMS by using the buttons
'Include Actual Execution Plan' or 'Display Estimated Execution Plan'. Text
and XML execution plans are also available.
You can also use some DMVs or SQL Server predefined reports (like
Performance - Top Queries by Total CPU Time ) to compare your query with some
other queries running on the instance.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"stevong" wrote:

> Hi everyone,
> Let's say I have a VIEW that is created by some complex joins - This
> VIEW is used by many of the application's function. Let's call this
> "VIEW_1".
> "VIEW_1" select queries are already created using "with (nolock)".
> Let's say a new system requirement comes in - I notice that it may be
> better if i create another view "VIEW_2" based on "VIEW_1".
> eg:
> create view VIEW_2
> as
> (
> select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
> )
> For such cases, what are the performance issues I should watch out
> for? WIll there be any performance issues? Should I be doing this in
> the first place?
> In SQL 2005 mgmt studio, how do I view the execution paths and
> timings?
> Please advise.
> Thanks.
>

Creating a VIEW based on other VIEW.

Hi everyone,
Let's say I have a VIEW that is created by some complex joins - This
VIEW is used by many of the application's function. Let's call this
"VIEW_1".
"VIEW_1" select queries are already created using "with (nolock)".
Let's say a new system requirement comes in - I notice that it may be
better if i create another view "VIEW_2" based on "VIEW_1".
eg:
create view VIEW_2
as
(
select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
)
For such cases, what are the performance issues I should watch out
for? WIll there be any performance issues? Should I be doing this in
the first place?
In SQL 2005 mgmt studio, how do I view the execution paths and
timings?
Please advise.
Thanks.If your query completes in some reasonable time and does not take too much
resources that may be needed for other users then it is not a problem.
You can take a look at the performance and resources used by your query by
using all or some of these:
set statistics io on
set statistics time on
set statistics profile on
set statistics xml on
You can also display the graphic execution plan in SSMS by using the buttons
'Include Actual Execution Plan' or 'Display Estimated Execution Plan'. Text
and XML execution plans are also available.
You can also use some DMVs or SQL Server predefined reports (like
Performance - Top Queries by Total CPU Time ) to compare your query with som
e
other queries running on the instance.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"stevong" wrote:

> Hi everyone,
> Let's say I have a VIEW that is created by some complex joins - This
> VIEW is used by many of the application's function. Let's call this
> "VIEW_1".
> "VIEW_1" select queries are already created using "with (nolock)".
> Let's say a new system requirement comes in - I notice that it may be
> better if i create another view "VIEW_2" based on "VIEW_1".
> eg:
> create view VIEW_2
> as
> (
> select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
> )
> For such cases, what are the performance issues I should watch out
> for? WIll there be any performance issues? Should I be doing this in
> the first place?
> In SQL 2005 mgmt studio, how do I view the execution paths and
> timings?
> Please advise.
> Thanks.
>sql

Creating a VIEW based on other VIEW.

Hi everyone,
Let's say I have a VIEW that is created by some complex joins - This
VIEW is used by many of the application's function. Let's call this
"VIEW_1".
"VIEW_1" select queries are already created using "with (nolock)".
Let's say a new system requirement comes in - I notice that it may be
better if i create another view "VIEW_2" based on "VIEW_1".
eg:
create view VIEW_2
as
(
select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
)
For such cases, what are the performance issues I should watch out
for? WIll there be any performance issues? Should I be doing this in
the first place?
In SQL 2005 mgmt studio, how do I view the execution paths and
timings?
Please advise.
Thanks.If your query completes in some reasonable time and does not take too much
resources that may be needed for other users then it is not a problem.
You can take a look at the performance and resources used by your query by
using all or some of these:
set statistics io on
set statistics time on
set statistics profile on
set statistics xml on
You can also display the graphic execution plan in SSMS by using the buttons
'Include Actual Execution Plan' or 'Display Estimated Execution Plan'. Text
and XML execution plans are also available.
You can also use some DMVs or SQL Server predefined reports (like
Performance - Top Queries by Total CPU Time ) to compare your query with some
other queries running on the instance.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"stevong" wrote:
> Hi everyone,
> Let's say I have a VIEW that is created by some complex joins - This
> VIEW is used by many of the application's function. Let's call this
> "VIEW_1".
> "VIEW_1" select queries are already created using "with (nolock)".
> Let's say a new system requirement comes in - I notice that it may be
> better if i create another view "VIEW_2" based on "VIEW_1".
> eg:
> create view VIEW_2
> as
> (
> select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
> )
> For such cases, what are the performance issues I should watch out
> for? WIll there be any performance issues? Should I be doing this in
> the first place?
> In SQL 2005 mgmt studio, how do I view the execution paths and
> timings?
> Please advise.
> Thanks.
>

Creating a Report Model on a UDB datasource

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

Hello,

Did you ever receive an answer to this issue?

Thanks.

Creating a Report Model on a UDB datasource

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

Hello,

Did you ever receive an answer to this issue?

Thanks.

Creating a report model based on related entities

I created a report model that has two entities. Both entities, in the SQL
terminology, can have an inner join. Now the two tables do not have any
foreign key relationship or anything. ..
Now when i open the report builder and select a field from one of the entity
the other one just dissapears away. What do i need to do to be able to select
fields from both entities ?The above question is for designing ad hoc reports through the report model.|||It is not visible because when you select an entity then that becomes a
primary entity and remaining is not visible. Now the entities related to the
selected or the primary will be visible. As you said that there is no
relation so it doesn't appear on the list and it wont appear. So you need to
have a relation to be able to select other fields as well.
Amarnath
"Tk_Neo" wrote:
> The above question is for designing ad hoc reports through the report model.|||I have a smilar problem. I have a single data source view which contains 3
tables. Each table is a completely denormalized (like a spreadsheet) dataset
from a particular business system. Within the datasource view editor I
created primary keys on each table and established relationships between each
table. However, when I try to create a report in report builder, as soon as
add an entity from one table to my report, the other 2 tables disappear.
What do I have to do to prevent the tables from disappearing? Please be
specific since I am fairly new to the Ad Hoc component of reporting services.
Thanks.
"Amarnath" wrote:
> It is not visible because when you select an entity then that becomes a
> primary entity and remaining is not visible. Now the entities related to the
> selected or the primary will be visible. As you said that there is no
> relation so it doesn't appear on the list and it wont appear. So you need to
> have a relation to be able to select other fields as well.
> Amarnath
> "Tk_Neo" wrote:
> > The above question is for designing ad hoc reports through the report model.

creating a report model based on a view

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!

NewJoizey wrote:

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

It is possible. But it's a little tricky to set up a model.

|||

that doesn't make sense. - i am sure that people must all the time set up a report based on a view that already exists. maybe there's another way that the procedure i'm trying to accomplish here?

|||

NewJoizey wrote:

i am sure that people must all the time set up a report based on a view that already exists.

Yes. You just have to set up a model with the proper relationships for your database first. Otherwise, it doesn't know what your data looks like.

It's like trying to construct a building without drawing up the blueprints.

Have you created a report before using business intelligence studio?

|||

yes I have successfullly created reports across different data stores, but it only seems to work based on tables, not views

I'm confused because I have an existing view that lives in my db. when I click "data source views" in my report project, i can clearly see all my database defined views in the wizard, and so I point the data source view (in my report project) to my desired view (database) I click finish and everything looks fine.

in my report project environment, i set up my report model based on my newly defined data source view, and under where it says "model" there's nothing zero zip nada, whereras my other report models clearly show all of the fields.

If there are extra steps to be able to do this properly, MS certainly doesn't make it very clear!

|||

I recommend this to you:

http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?culture=en-US&EventID=1032273699&CountryCode=US

Creating a report

Hi,
Im new to dot net and CR.
I would like to create a a report based on an SQL query at run time.
How do i do it.
Tnx
PapsHave you tried doing a search on this forum or on Google or Crystal Report's website? There's tons of information on the many different ways to do Crystal Reports, you just have to a bit of digging!

Crystal Reports:
http://support.businessobjects.com/search/advsearch.asp

Google:
http://www.google.com

Crystal Reports Forum:
http://support.businessobjects.com/forums/default.asp

Creating a partitioned view based on results from select?

I have a situation which seems like it should have a fairly simple
answer... but I'm not sure how to do it.
I want to create a view that joins several remotely distributed tables.
Something like:
CREATE VIEW AllData
AS
SELECT * FROM Server1.SomeDB.dbo.DataTable
UNION ALL
SELECT * FROM Server2.SomeDB.dbo.DataTable
...
SELECT * FROM ServerN.SomeDB.dbo.DataTable
The problem is that we anticipate some downtime or discontinuity with
our server links. Will the view fail if just ONE server can't be
reached? I think it will... please correct me if I'm wrong.
So, to solve this we have another table which stores the server name
and a status field. The question is how do I take this list of
available servers and turn it into a valid view? Is this going to
require some form of dynamic SQL? I'm picturing creating a trigger so
that any time the status DB changes the view gets recreated.status table could be out of sync and you will still get error. Long story
short, there isn't anything builtin right now to allow you precheck for
linked server status other than sending a query to it.
-oj
<bryanp10@.hotmail.com> wrote in message
news:1113511868.193053.138020@.l41g2000cwc.googlegroups.com...
>I have a situation which seems like it should have a fairly simple
> answer... but I'm not sure how to do it.
> I want to create a view that joins several remotely distributed tables.
> Something like:
> CREATE VIEW AllData
> AS
> SELECT * FROM Server1.SomeDB.dbo.DataTable
> UNION ALL
> SELECT * FROM Server2.SomeDB.dbo.DataTable
> ...
> SELECT * FROM ServerN.SomeDB.dbo.DataTable
>
> The problem is that we anticipate some downtime or discontinuity with
> our server links. Will the view fail if just ONE server can't be
> reached? I think it will... please correct me if I'm wrong.
> So, to solve this we have another table which stores the server name
> and a status field. The question is how do I take this list of
> available servers and turn it into a valid view? Is this going to
> require some form of dynamic SQL? I'm picturing creating a trigger so
> that any time the status DB changes the view gets recreated.
>|||OK... not quite what I was asking, but thanks. :)
Basically, I'm useless in T-SQL and what I really need to know is how
to transform a table of server names into:
select * from server1
UNION ALL
select * from server2
...
etc.
Really this is just a basic T-SQL question. In ANY other programming
language I've ever seen, this would be simplicity itself. But for some
reason T-SQL just baffles me.
The other question was whether this absolutely requires dynamic SQL or
not. At this point I'm thinking I might just build the create view
procedure in my app where I have a real language to do it.

Creating a Dynamic YTD calculation in MDX

I have a YTD calculation that I want to make dynamic based on the real current date.

This is the regular YTD formula:

Sum(YTD([Date].[Calendar Hierarchy].CurrentMember),[Measures].[Planned Orders])

This is the YTD formula hard coded with the current date:

Sum(YTD([Date].[Calendar Hierarchy].[Calendar Year].&[2007].&[2007Q1].&[2007-01].&[2007-01-30T00:00:00]),[Measures].[Planned Orders])

The hard coded date works but I need the date to be dynamic. I’m not sure how to get it to be based from the current date.

I’ve been experimenting with using the NOW() function to return the date but I can’t get the syntax correct.

Thank you.

David

Hmmm i think you can find your answer here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

|||

The article is good but it doesn't address my need to make the date part of the calculation relative and based on the current date (down to the day level).

David

|||

Perhaps this thread can help you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=996269&SiteID=1

Regards

Thomas Ivarsson

|||

Thomas,

Unfortunately, I have budget like future date information in the cube. As a result, I probably need to develop a dynamic way of capturing the current date.

Would this possible scenario work using your suggestion:

Suppose I created a new fact table with one record where the date changed every day. I have a measure column called "Sales " with a value of 1.

I add this fact table to the cube and rewrite your named set:

Tail(Filter([Time].[Time_Calendar].[Month].Members,(Time.[Time_Calendar].Currentmember,[Measures].[lSales])>0))

This would make the date always based on the current date. However, I'm not sure if would filter out other records that I need to show.

David

|||

If you have a measure that is updated daily, like actual sales, my solution will work even if you have a budget measure that points to future dates.

This link have some other suggestions.

http://support.dspanel.com/help43/Web_Part/Examples/MDX_Examples.htm

HTH

Thomas Ivarsson

Creating a dynamic temporary table

I need to dynamically create a temporary table like this (I don't know its
structure until runtime - it is based on selections made by user):
DECLARE @.CreateStatement = 'CREATE TABLE #tmpTable (' + @.co1 + 'varchar(250)
+ ', ' + @.col2 + 'varchar2(250))'
EXEC(@.CreateStatement)
--following is code to fill this table
The table created is not accessible after the line EXEC(@.CreateStatement). I
know this (temp table have a scope limited to the stored procedure that
created them).
Is there another way to accomplish this? I also tried using table variables,
but I wasn't able to make a stored procedure that returns a table variable.YOU CAN USE FUNCTION INSTEAD OF STORED PROC
CREATE FUNCTION TEMP (@.col1 varchar(250) ,@.col2 varchar(250))
RETURNS TABLE
as
RETURN SELECT @.col1+','+@.col2 as TEXT
"razdanro" wrote:
> I need to dynamically create a temporary table like this (I don't know its
> structure until runtime - it is based on selections made by user):
> DECLARE @.CreateStatement = 'CREATE TABLE #tmpTable (' + @.co1 + 'varchar(25
0)
> + ', ' + @.col2 + 'varchar2(250))'
> EXEC(@.CreateStatement)
> --following is code to fill this table
> The table created is not accessible after the line EXEC(@.CreateStatement).
I
> know this (temp table have a scope limited to the stored procedure that
> created them).
> Is there another way to accomplish this? I also tried using table variable
s,
> but I wasn't able to make a stored procedure that returns a table variable.[/color
]|||You can create tempdb..temptable or create ##temptable and it will be
available until it is dropped or the sql server is re-booted.. The
difference between #temptable and ##temptable
#temptable is a non-sharable connection specific temporary table. It goes
away when the SP (if created in an sp) or connection goes away..
##temptable can be seen by all spids, and lives until you drop it or the
server is re-booted... if you are wiriting for mutliple concurrnet users,
you may have to check for its existence before creating it... or come up
with some unque name, and/or attach a spid to separate your rows from those
inserted by another spid.
hope this helps.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"razdanro" <razdanro@.discussions.microsoft.com> wrote in message
news:8F211B2B-42BE-4608-98EB-C4E350D64D8C@.microsoft.com...
> I need to dynamically create a temporary table like this (I don't know its
> structure until runtime - it is based on selections made by user):
> DECLARE @.CreateStatement = 'CREATE TABLE #tmpTable (' + @.co1 +
'varchar(250)
> + ', ' + @.col2 + 'varchar2(250))'
> EXEC(@.CreateStatement)
> --following is code to fill this table
> The table created is not accessible after the line EXEC(@.CreateStatement).
I
> know this (temp table have a scope limited to the stored procedure that
> created them).
> Is there another way to accomplish this? I also tried using table
variables,
> but I wasn't able to make a stored procedure that returns a table
variable.|||create the table first using a hard coded "create table" statement (with at
least one column - a dummy column if needed) - then dynamically alter its
structure.
"razdanro" <razdanro@.discussions.microsoft.com> wrote in message
news:8F211B2B-42BE-4608-98EB-C4E350D64D8C@.microsoft.com...
> I need to dynamically create a temporary table like this (I don't know its
> structure until runtime - it is based on selections made by user):
> DECLARE @.CreateStatement = 'CREATE TABLE #tmpTable (' + @.co1 +
'varchar(250)
> + ', ' + @.col2 + 'varchar2(250))'
> EXEC(@.CreateStatement)
> --following is code to fill this table
> The table created is not accessible after the line EXEC(@.CreateStatement).
I
> know this (temp table have a scope limited to the stored procedure that
> created them).
> Is there another way to accomplish this? I also tried using table
variables,
> but I wasn't able to make a stored procedure that returns a table
variable.|||Do not write SQL this way.
Temporary tables tell us that you are really writing procedural code
and have not learned to think in sets and declarative code yet. A temp
table is a "scratch tape" for an algorithm based on procedural steps in
95% of the cases. You probably should be using derived tables or
VIEWs.
Dynamic SQL tell us that you do not know what you are doing, so you
have to let a random stranger create a table in your data model at the
last minute.
Using over-sized VARCHAR(n) values tells us that you did no research to
find the proper size, but just grabbed a large dummy value. This also
means that you have no data model and probalby no data dictionary.
Finally, you will never learn SQL this way. You have already decided
on HOW you want to solve a problem. So people will show you how to
write kludges for your bad solution. But if you had posted WHAT you
want to do, then you might get a relational answer.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Let's try again with the actual problem.|||Here is the data model that I didn't design, but I have to work with right n
ow.
I have three entities: Companies, Sites and Contacts. A Company has 0 or
more Sites, a Site has 0 or more Contacts.
The application must allow users to add properties of these entities
dynamically.
These Properties are held in a table, and the values allowed are in a
PropertyValues table. There is also a table EntityProperty which is an
intersection table between Entities and PropertyValues.
So I have a design that actually stores data and metadata.
And now I have to make a SQLBuilder based on these Entities. I don't know
what Property will be selected as output, that's why I need to create a
dynamic temporary table to return the result.

Creating a dimension based on uniqueidentifier

Is it possible to base a dimension on a uniqueidentifier? My company uses uniqueidentifiers for all primary keys in our tables. However, I am now trying to create a parent-child dimension in Analysis Manager using one of our tables but I always get the error

Unable to count the members of the 'PK' level.

Unable to open the record set.

Error: The count-unique aggregate operation cannot take a uniqueidentifier data type as an argument.

Any suggestions?Note: this is in AS 2000.

Anyone know if this is possible in 2005?

creating a data driven hierarchial navigation

Hi need to create navigation that gets populated via database and is role based. below is the sql

CREATETABLE [TopMenu](

[MenuID] [int]IDENTITY(1, 1)NOTNULL,

[Text] [varchar](50)COLLATE SQL_Latin1_General_CP1_CI_ASNULL,

[Description] [varchar](255)COLLATE SQL_Latin1_General_CP1_CI_ASNULL,

[ParentID] [int]NULL,

CONSTRAINT [PK_Menu]PRIMARYKEYCLUSTERED

(

[MenuID]

)ON [PRIMARY]

)ON [PRIMARY]

GO

INSERTINTO TopMENU

Select'Property','Property',NULL

UNION ALLSelect'Portfolio','Portfolio',NULL

UNION ALLSelect'Capital Expenditure','PortfolioCapex', 1

UNION ALLSelect'Depreciation','PortfolioDepreciation', 1

UNION ALLSelect'Condition Audit','PortfolioCondition', 1

UNION ALLSelect'Historical Expenditure','PortfolioHisEx', 1

UNION ALLSelect'Property Details','PropertyDetail', 2

UNION ALLSelect'Assets','PropertyAsset', 2

UNION ALLSelect'Depreciation','PropertyDepreciation', 2

UNION ALLSelect'Capital Expenditure','PropertyExpenditure', 2

UNION ALLSelect'Insurance','PropertyInsurance', 2

UNION ALLSelect'Documents','PropertyDocuments', 2

UNION ALLSelect'Capex','PortfolioCapex', 3

UNION ALLSelect'Graph','PortfolioCapexGraph', 3

UNION ALLSelect'Graph','PortfolioDepreciationGraph', 4

UNION ALLSelect'Current Depreciation','PortfolioDepreciation', 4

UNION ALLSelect'WDV & Depreciation','PortfolioDepreciationWDV', 4

UNION ALLSelect'Assets','PropertyAsset', 8

UNION ALLSelect'Capex','PropertyCapex', 8

UNION ALLSelect'Depreciation','PropertyDepreciation', 8

UNION ALLSelect'Condition','PropertyCondition', 8

UNION ALLSelect'Expenditure','PropertyExpenditure', 8

UNION ALLSelect'Disposed','PropertyDisposed', 8

UNION ALLSelect'Capital Ex','PropertyExpenditure', 10

UNION ALLSelect'Capital Ex Graph','PropertyExpenditureGraph', 10

GO

now clearly, I will have two top level navigation

property and portfolio. both property will have subsequent navigation and those subsequest navigation may or may not have another sets of navigation.

I need to display in a row style not in a drop down style for eg. If I am in portfolio link the navigation structure should be as below

Portfolio

Capital Expenditure | Depreciation | Condition Audit | Historical Expenditure

Capex | Graph

by default Capex should be selected. it should also highlight Portfolio, Capital Expenditure and Capex as Capex's parent is Capital Expenditure and Portfolio

I was thinking of using a 3 level datagrid. could someone please help me I am using asp.net 2.0 web application project with vb.net and sql server 2005

thanks in advance

Have you looked at the menu class available from the toolbar? I know it works with a hierarchical xml file, it might work with a database source also.

If it does, I think it will do what you want UI-wise.

|||I am doing it in webform not windows form. does it support that?|||

foremorecoast:

I am doing it in webform not windows form. does it support that?

This is an asp.net forum, not a windows forms forum. So I wasn't talking about windows forms. :)

As for whether it supports database-supplied values instead of xml files for the menu entries, you'll have to look that up. It's what I would do first if I had to code a solution like that. But I don't, and you do, so you can look it up and tell us about what you found. :)

|||

the problem here is not the datasource type but the logic on how to display second level navigation and third level navigation based on what is clicked. i.e. property or portfolio. I have seen lots of article which shows how to create a data driven menu using javascript downdown but i want second and third navigation to be static based on what is clicked( either property or portfolio).

please help

|||

I am thinking of alternative solution using 3 level datalist but its not working as there is no onclick property so that based on property link or portfolio link click i could display their childrens and based on their children click i could display their sub childs.

can anyone please help me?

|||

Hi! I used below method to implement 3 level navigation. but I now have a problem of maintaining the selected state of the item after postback.

when user logs in I need to somehow set the default page pointing to

Portfolio

Assets

Capex

and at the same time i need to show those three links active.

each page should go to differnt url with parameter. below are my codes. please help.

aspx

<asp:DataList ID="dlParent" runat="server" RepeatDirection="Horizontal" DataKeyField="MenuId" OnSelectedIndexChanged="parentselectedindexchanged" Font-Names="Trebuchet MS" Font-Size="Smaller" ForeColor="#404040">
<ItemTemplate>
<asp:LinkButton ID="lnkParent" runat="server" CommandName="select" Text='<%# DataBinder.Eval(Container, "DataItem.Text")%>'></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle BackColor="Silver" />
</asp:DataList>
<asp:dataList id="dlChild" runat="server" RepeatDirection="Horizontal" DataKeyField="MenuId" OnSelectedIndexChanged="childselectedindexchanged" Font-Names="Trebuchet MS" Font-Size="Smaller">
<ItemTemplate>
<asp:LinkButton ID="lnkChild" runat="server" CommandName="select" Text='<%# DataBinder.Eval(Container, "DataItem.Text")%>'></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle BackColor="Silver" />
</asp:dataList>
<asp:dataList id="dlSubChild" runat="server" RepeatDirection="Horizontal" Font-Names="Trebuchet MS" Font-Size="Smaller" >
<ItemTemplate>
<asp:LinkButton ID="lnkSubChild" runat="server" CommandName="select" Text='<%# DataBinder.Eval(Container, "DataItem.Text")%>'></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle BackColor="Silver" />
</asp:dataList>

code behind
--
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim myDal As New clsDAL
Dim dsParent As DataSet
'Dim selParent As Integer = -1
'dlParent.SelectedIndex = selParent
If Not IsPostBack Then

dsParent = myDal.getSubMenuItems(0)

If Not dsParent Is Nothing Then
dlParent.DataSource = dsParent
dlParent.DataBind()
End If

End If
End Sub

Protected Function BuildUrl(ByVal field1 As Integer) As String
Dim baseUrl As String
baseUrl = "menu.aspx"
Return String.Format("{0}?navId={1}", baseUrl, field1)
End Function

Public Sub parentselectedindexchanged(ByVal sender As Object, ByVal e As System.EventArgs)
Dim mydal As New clsDAL
Dim NavId As Integer
'Dim selChild As Integer = -1
'dlChild.SelectedIndex = selChild
Dim dsChild As New DataSet
Try
NavId = CInt(dlParent.DataKeys(dlParent.SelectedIndex))

dsChild = mydal.getSubMenuItems(NavId)
dlChild.DataSource = dsChild
dlChild.DataBind()
Catch ex As Exception
Response.Write(ex.ToString)
End Try
End Sub

Public Sub childselectedindexchanged(ByVal sender As Object, ByVal e As System.EventArgs)
Dim mydal As New clsDAL
Dim NavId As Integer
'Dim selSubChild As Integer = -1
'dlSubChild.SelectedIndex = selSubChild
Dim dsSubChild As New DataSet
Try
NavId = CInt(dlChild.DataKeys(dlChild.SelectedIndex))
dsSubChild = mydal.getSubMenuItems(NavId)
dlSubChild.DataSource = dsSubChild
dlSubChild.DataBind()
Catch ex As Exception
Response.Write(ex.ToString)
End Try
End Sub

|||

can someone at least point me to write direction?

|||

You could save the entire object in session cache and restore it with each postback.

Sorry, I don't know how to make it pre-open to a given location.

|||

could you please show me how you will do it as i have never used cache to store the datalist. a code snippet will be of great help.

cheers

Creating a chart based on data in a matrix

I am using a matrix to display current and historical financial data. To do this, I am using 5 different datasets. Each dataset contains 1 row of data for a specific year for a specific account.

Now that problem I am having is displaying this data in a chart. When I create a new chart, it asks which dataset I would like to use. I do not want to use any specific dataset, I want to get chart's data from the matrix. Is this possible?

Anybody have any ideas?

If that can't be done. Is it possible to create a chart based on multiple datasets?

|||

I think it should be possible. No matter what dataset it is theoretically pointed to, when you set up the value expressions, you can still use (First(...)) expressions to reference the other datasets, right? Considering that you only have one row per each, I mean.

>L<

|||

I can use the First() function and use multiple datasets, but this doesn't quite work the way I want it.

I have 5 seperate datasets, CurrentData, 1YearOldData, 2YearOldData, 3YearOldData, and 4YearOldData.

Lets say I want to display "Return on Assets" for the current year, and previous 3 years in my chart.

The forumla I need to use for the current ROA is... =First(Fields!NetIncome.Value, "CurrentData") / Avg(First(Fields!Assets, "CurrentData") + First(Fields!Assets, "1YearOldData")

The formula I need to use for the ROA 1 year ago is... =First(Fields!NetIncome.Value, "1YearOldData") / Avg(First(Fields!Assets, "1YearOldData") + First(Fields!Assets, "2YearOldData")

Etc...

I can't dynamically change the "Values" section of the report to display the data based on different datasets. If I enter each "value" seperately, they get grouped funny and the data isn't displayed how I need it. Is there anyway to use the data from a matrix, or any other workaround for this?

|||

Why don't you do a single dataset, with the data you need, as a UNION of the SELECTs that produce each of the single rows in the various current datasets, then? Isn't this really what you are after?

>L<

|||

Well I can do the select in a way that will give me 5 rows of data in the dataset. Each row representing the one year of data (instead of seperate datasets for each row). However, in my financial calculations, I need to use data from both the current year and the previous year (2 different rows in the dataset). I am unclear on how I can use a union statement to achieve this?

Row1 needs data for year 2006 and certain fields for 2005

Row2 needs data for year 2005 and certain fields for 2004

Row3 needs data for year 2004 and certain fields for 2003

and so on

This is now getting int T-SQL and not reporting services specifically. If its possible to use a matrix as the datasource for a chart, that is really what I want to do.

|||

Okey doke, sorry for suggesting something you don't want to get into. But...

*I* am unclear about why you think a matrix could be a "datasource". It's a representation of data, an evaluation of the data, an output, not a "source".

how to visualize this: you can talk to ReportItems("textboxWhatever").Value as a source for something in a chart. But that would be something you might do for a *label* -- an expression to provide *one* value, IOW, not a series of values. If you tried to do it as a value for the x- or y- axis, you wouldn't get what you were after. The matrix is more complex than a single textbox, but it is still an output, not something in the middle of a pipeline.

That's the best I can describe why it doesn't seem like the right approach for you to be taking. I would be trying to do this by handling the details of what I needed in SQL. Sorry if this answer is not what you're after and again I could be completely wrong.

>L<

|||

Lisa,

You are completely correct about this. I ended up making changes in my stored procedure to bring back all of the needed fields in 1 dataset.

The reason I was talking about a matrix being a "datasource" is because that is essentially how Excel works. You can highlight data in the spreadsheet and create a table from that. I wanted to know if it was possible to do the same thing in reporting services. Apparently it is not.

|||

FWIW... you're not the only one with this kind of expectation...

I could write a book about how the surface similarities between what people (users and developers both) see in Excel and what is actually going on in a reporting result (and not just a SQL Server Reporting Services reporting result!) provides unrealistic expectations.

It would be fun (not!) to write another book about how visual design tools mislead developers about the underlying architecture supporting the runtime behavior,so that when those developers try to actually WRITE SOME CODE THEMSELVES they tie themselves up in knots.

I have to stop now before this turns into a rant <g>.

>L<

Creating a chart based on data in a matrix

I am using a matrix to display current and historical financial data. To do this, I am using 5 different datasets. Each dataset contains 1 row of data for a specific year for a specific account.

Now that problem I am having is displaying this data in a chart. When I create a new chart, it asks which dataset I would like to use. I do not want to use any specific dataset, I want to get chart's data from the matrix. Is this possible?

Anybody have any ideas?

If that can't be done. Is it possible to create a chart based on multiple datasets?

|||

I think it should be possible. No matter what dataset it is theoretically pointed to, when you set up the value expressions, you can still use (First(...)) expressions to reference the other datasets, right? Considering that you only have one row per each, I mean.

>L<

|||

I can use the First() function and use multiple datasets, but this doesn't quite work the way I want it.

I have 5 seperate datasets, CurrentData, 1YearOldData, 2YearOldData, 3YearOldData, and 4YearOldData.

Lets say I want to display "Return on Assets" for the current year, and previous 3 years in my chart.

The forumla I need to use for the current ROA is... =First(Fields!NetIncome.Value, "CurrentData") / Avg(First(Fields!Assets, "CurrentData") + First(Fields!Assets, "1YearOldData")

The formula I need to use for the ROA 1 year ago is... =First(Fields!NetIncome.Value, "1YearOldData") / Avg(First(Fields!Assets, "1YearOldData") + First(Fields!Assets, "2YearOldData")

Etc...

I can't dynamically change the "Values" section of the report to display the data based on different datasets. If I enter each "value" seperately, they get grouped funny and the data isn't displayed how I need it. Is there anyway to use the data from a matrix, or any other workaround for this?

|||

Why don't you do a single dataset, with the data you need, as a UNION of the SELECTs that produce each of the single rows in the various current datasets, then? Isn't this really what you are after?

>L<

|||

Well I can do the select in a way that will give me 5 rows of data in the dataset. Each row representing the one year of data (instead of seperate datasets for each row). However, in my financial calculations, I need to use data from both the current year and the previous year (2 different rows in the dataset). I am unclear on how I can use a union statement to achieve this?

Row1 needs data for year 2006 and certain fields for 2005

Row2 needs data for year 2005 and certain fields for 2004

Row3 needs data for year 2004 and certain fields for 2003

and so on

This is now getting int T-SQL and not reporting services specifically. If its possible to use a matrix as the datasource for a chart, that is really what I want to do.

|||

Okey doke, sorry for suggesting something you don't want to get into. But...

*I* am unclear about why you think a matrix could be a "datasource". It's a representation of data, an evaluation of the data, an output, not a "source".

how to visualize this: you can talk to ReportItems("textboxWhatever").Value as a source for something in a chart. But that would be something you might do for a *label* -- an expression to provide *one* value, IOW, not a series of values. If you tried to do it as a value for the x- or y- axis, you wouldn't get what you were after. The matrix is more complex than a single textbox, but it is still an output, not something in the middle of a pipeline.

That's the best I can describe why it doesn't seem like the right approach for you to be taking. I would be trying to do this by handling the details of what I needed in SQL. Sorry if this answer is not what you're after and again I could be completely wrong.

>L<

|||

Lisa,

You are completely correct about this. I ended up making changes in my stored procedure to bring back all of the needed fields in 1 dataset.

The reason I was talking about a matrix being a "datasource" is because that is essentially how Excel works. You can highlight data in the spreadsheet and create a table from that. I wanted to know if it was possible to do the same thing in reporting services. Apparently it is not.

|||

FWIW... you're not the only one with this kind of expectation...

I could write a book about how the surface similarities between what people (users and developers both) see in Excel and what is actually going on in a reporting result (and not just a SQL Server Reporting Services reporting result!) provides unrealistic expectations.

It would be fun (not!) to write another book about how visual design tools mislead developers about the underlying architecture supporting the runtime behavior,so that when those developers try to actually WRITE SOME CODE THEMSELVES they tie themselves up in knots.

I have to stop now before this turns into a rant <g>.

>L<

Creating a Calculated Member Based on a Range

Is there a way to create a calculated member based on a range of values?

For example, I have two measures in my cube: ResponseTime and TransactionCount.

What I want to do is create a calculated member that returns the total number of Transactions where the ResponseTime is between a certain range, i.e. between 1 and 2 seconds.

Here is what I've tried to do:

IIF( [Measures].[ResponseTime] > 1 And [Measures].[ResponseTime] < 2, [Measures].[TransactionCount], 0)

However, when I browse the cube, all the values for this calculated member are blank. Any ideas?

This problem can easily can generalized. My main problem is just finding a way to group values based on a certain range of values.

Thanks!!

Joel,

Given that you want to look at the "response time" for each individual transaction in your fact table and the cube is going to show you values aggregated based on selections that the end user makes, you may want to consider the following solution:

In your data source view:

Add a name calculation to your fact table named "ResponseTimeRangeKey" using a CASE statement:

CASE

WHEN ResponseTime <= 1 THEN 1

WHEN ResponseTime > 1 AND ResponseTime < 2 THEN 2

ELSE 3

END CASE

Next you can create a dimension table that can be used to build a cube dimension for your response time ranges using either a database view or a named query with a simple SQL statement something like this:

SELECT

1 AS ResponseTimeRangeKey,

'1 sec or less' AS ResponseTimeRangeName

UNION

SELECT

2 AS ResponseTimeRangeKey,

'1-2 sec' AS ResponseTimeRangeName

UNION

SELECT

3 AS ResponseTimeRangeKey,

'more than 2 sec' AS ResponseTimeRangeName

You can then relate your fact table to the new dimension in the data source view. Next you can create your response time range dimension and add it to your cube.

This solution has the benefit of allowing users to look at any measure in your cube using the response time ranges and will accurately aggregate your values for all dimensions.

HTH,

Steve

|||

Thanks for the help. I tried it out, and it worked brilliantly.

|||

I have somewhat the same scenario, I need to find a way to group values based on certain range of values. I have a calculated member, YTD investment. From my fact table I created a named calculation field for SegmentID with initial value of -1 to relate it with Segment Dimension. The key -1 has "Unknown" as the caption. This SegmentID is my dimension key on the fact table and the key of Segment Dimension. The Segment Dimension has the Min and Max attributes where the minimum and maximum range of values for a particular dimension member is defined.

Is there a way for me to populate or assign values to the named calculation field SegmentID by getting the dimension key of segment dimension where the YTD Investment falls on segment dim's min and max attributes/range?

Thanks in advance,
May Lanie

|||

May,

This is can be tricky depending on the dimensionality of your cube and how you want the YTD to roll up. I have done this type of "dynamic" bucketing before using a separate measure group that only contains the row count for the fact table. You can then use the SCOPE function to allocate values along a pre-defined range of buckets according to the value you want to use for distribution purposes. Here is an example of what I am talking about:

Scope([Measures].[Customer Count by Bucket]);

([Bucket].[Bucket Key].[< 50K]) =

Filter(

Existing Customer.[Customer Name].[Customer Name].Members,

([Measures].[Sales Amt],[Bucket].[Bucket Key].[All Bucket]) <= 50000).Count;

([Bucket].[Bucket Key].[50K - 100K]) =

Filter(

Existing Customer.[Customer Name].[Customer Name].Members,

([Measures].[Sales Amt],[Bucket].[Bucket Key].[All Bucket]) > 50000 AND

([Measures].[Sales Amt],[Bucket].[Bucket Key].[All Bucket]) <= 100000).Count;

....

End Scope;

The "Measures.[Customer Count by Bucket]" is the fact table row count measure. There is a predefined "Bucket" dimension that has key values indicating a range, but you could just as easily use your "low" and "high" member values. The "Filter" statements are used to count the number of customers that fall into the buckets using "Sales Amt". You would need to substitute your "YTD Amount" calculation.

HTH,

Steve

Creating a calculated measure that filters

Hi,

Can I create a calculated member that will filter out based on a certain dimension? eg, I have a customer dimension and a sales cube. The sales cube has the sales count measure. Can I have a calculated measure that will give me the sales count for all customers that are flagged as 'New'? That way, I have a total count and a New customer count.

Thanks,

Brian

This should be very easy to do in MDX if you have attribute NewCustomer in the Customer dimension. The expression then would be something like

(Measures.[Sales Count], Customer.NewCustomer.[true])

|||

Thanks Mosha, indeed it is easy! I'd like to expand on this.

So, by adding a new calculated member, (Measures.[Sales Count], Customer.NewCustomer.[true]), it will give me the counts of new customers. Is is possible to now also filter on, say, customerStatus as well? eg.

My Sales cube also has a CustomerType dimension. How can I expand the filter above to also filter on the CustomerType dimension (say, customertype = E).

I'm still trying to get my head around MDX :)

Thanks!

Brian

|||I wouldn't call this "expansion" of the filter, it is more of "restricting" the filter further. Yes, you can add as many hierarchy members to the filter as you want. I recommend going over first few chapters of my "Fast Track to MDX" book to get yourself on the MDX track fast...

createing new items based off a select?

I know this has to be possible with out using a cursor to loop through
this..
say I have tables like this...
Table A
==========
ItemID INT
Item TEXT
Table B
==========
PersonID int
ItemID int (from table A)
Table C
============
PersonID
Item
ItemID
Description
I want to do a select on table A get all items with the Item ID the person
in Table B hase and insert the result into Table C.
So if I have 2 items in A, and my Info in B, I want to do a query and have
records for each of them inserted into C with their info where it matches
together... I could easily do this with a cursor by looping through table A
looking for the ItemID of the current person then doing an Insert into table
C with the persons item information and the persons info... is there a way
to do this WITOUT a cursor and just a query? thanks!You're thinking in procedural language terms.
In T-SQL, it would go something like this...
insert into tablec (personid, item, itemid, description)
select b.personid, a.item, a.itemid, null
from tablea a
join tableb b on (a.itemid = b.itemid)
No idea where description is coming from so I nulled it.
"Brian Henry" <nospam@.nospam.com> wrote in message
news:e4UYeT%23WFHA.1148@.tk2msftngp13.phx.gbl...
> I know this has to be possible with out using a cursor to loop through
> this..
> say I have tables like this...
> Table A
> ==========
> ItemID INT
> Item TEXT
> Table B
> ==========
> PersonID int
> ItemID int (from table A)
>
> Table C
> ============
> PersonID
> Item
> ItemID
> Description
>
> I want to do a select on table A get all items with the Item ID the person
> in Table B hase and insert the result into Table C.
> So if I have 2 items in A, and my Info in B, I want to do a query and have
> records for each of them inserted into C with their info where it matches
> together... I could easily do this with a cursor by looping through table
A
> looking for the ItemID of the current person then doing an Insert into
table
> C with the persons item information and the persons info... is there a way
> to do this WITOUT a cursor and just a query? thanks!
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. If you had followed minimal netiquette, would your
pseudo-code look like this?
CREATE TABLE Items
(item _id INTEGER NOT NULL PRIMARY KEY,
Item_descrp VARCHAR(100) NOT NULL);
An item is not an attribute of a person; it is an entity, so we need to
fix your design.
CREATE TABLE People
(person_id INTEGER NOT NULL PRIMARY KEY,
. );
CREATE TABLE Purchases
(person_id INTEGER NOT NULL
REFERENCES People(person_id),
item_id INTEGER NOT NULL
REFERENCES Items(item_id),
PRIMARY KEY (person_id, item_id));
and have records [sic] for each of them inserted into C with their info
where it matches together... I could easily do this with a cursor by
looping through table A looking for the ItemID of the current person
then doing an Insert into table C with the persons item information and
the persons info... is there a way to do this WITOUT a cursor and just
a query <<
Your tables are not normalized. Rows are not records; no wonder you
think of procedural code and cursors instead of a query. Do not
materialize a new table, as if you were allocating a scratch tape in a
file system.
CREATE VIEW PurchaseReport (..)
AS
SELECT P.*, B.*
FROM Purchases AS P, People AS B, Items AS I
WHERE I.item_id = P.item_id
AND B.person_id = P.person_id;
The VIEW will always be current, unlike a new, redundant base table.|||Try,
insert into tablec (personid, item, itemid)
select b.personid, a.item, a.itemid
from tableb as b inner join tablea as a
on b.itemid = a.itemid
AMB
"Brian Henry" wrote:

> I know this has to be possible with out using a cursor to loop through
> this..
> say I have tables like this...
> Table A
> ==========
> ItemID INT
> Item TEXT
> Table B
> ==========
> PersonID int
> ItemID int (from table A)
>
> Table C
> ============
> PersonID
> Item
> ItemID
> Description
>
> I want to do a select on table A get all items with the Item ID the person
> in Table B hase and insert the result into Table C.
> So if I have 2 items in A, and my Info in B, I want to do a query and have
> records for each of them inserted into C with their info where it matches
> together... I could easily do this with a cursor by looping through table
A
> looking for the ItemID of the current person then doing an Insert into tab
le
> C with the persons item information and the persons info... is there a way
> to do this WITOUT a cursor and just a query? thanks!
>
>|||thats what I was trying to remember right there.. thanks!
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:uGYGQc%23WFHA.2420@.TK2MSFTNGP12.phx.gbl...
> You're thinking in procedural language terms.
> In T-SQL, it would go something like this...
> insert into tablec (personid, item, itemid, description)
> select b.personid, a.item, a.itemid, null
> from tablea a
> join tableb b on (a.itemid = b.itemid)
> No idea where description is coming from so I nulled it.
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:e4UYeT%23WFHA.1148@.tk2msftngp13.phx.gbl...
> A
> table
>

Create View based on User-Defined Function

I would like to create a View in SQL Server 2000, based on a user-defined
function. However when I try it, the "Functions" tab is empty. There are no
functions to choose from. I can select tables and other views.
Josh
Well , have you already created UDF?
"Josh" <Josh@.discussions.microsoft.com> wrote in message
news:4A014F2F-6429-4564-B0CD-A8896D5C1882@.microsoft.com...
>I would like to create a View in SQL Server 2000, based on a user-defined
> function. However when I try it, the "Functions" tab is empty. There are
> no
> functions to choose from. I can select tables and other views.
|||Yes. I have 8 of them.
"Uri Dimant" wrote:

> Josh
> Well , have you already created UDF?
> "Josh" <Josh@.discussions.microsoft.com> wrote in message
> news:4A014F2F-6429-4564-B0CD-A8896D5C1882@.microsoft.com...
>
>
|||The problem is with your tool of choice. Use QA and just type the
appropriate create/alter view query yourself.
|||Josh
I have no problems. Perhpas if you are using EM , you need to refresh
thisn tool im order to be able to see the functions
"Josh" <Josh@.discussions.microsoft.com> wrote in message
news:433B1DEC-B7AB-4C90-A9B1-932CCDE4EE3F@.microsoft.com...[vbcol=seagreen]
> Yes. I have 8 of them.
> "Uri Dimant" wrote:
|||Yeah, I've tried that but the function I'm trying to use contains variables
and the QA throws errors when I try to do it that way.
My ultimate goal is to use the function in MS Access. I was thinking that
I'd need to convert it to a view before I could import or link to it. If
there's another way I'd love to hear about it (I've posted this portion of
the question in one of the Access newsgroups).
Josh
"Scott Morris" wrote:

> The problem is with your tool of choice. Use QA and just type the
> appropriate create/alter view query yourself.
>
>
|||> Yeah, I've tried that but the function I'm trying to use contains
> variables
> and the QA throws errors when I try to do it that way.
It helps to tell us exactly what you are doing. It is also critical to tell
us exactly what "throws errors" means. When you encounter an error, post
the exact text of the error. And get in the practice of specifying which
version (and service pack level) of sql server you are using.

> My ultimate goal is to use the function in MS Access. I was thinking that
> I'd need to convert it to a view before I could import or link to it. If
> there's another way I'd love to hear about it (I've posted this portion of
> the question in one of the Access newsgroups).
> Josh
A view is effectively a pre-cannned select query. You cannot define a view
that will accept arguments and pass those arguments to the function.
|||OK. Hope this will make sense to people unfamiliar with our specific
database. We have a table with a "Comments" field that contains several
pieces of information delimited by slashes (/). I didn't design it this way
and would not have; I'd have put each piece of data in its own field. But
what's done is done and I have to work with it. The DBA created functions to
extract the individual portions of this field. One of them has the syntax
below:
CREATE FUNCTION dbo.GetFirstSlash
( @.Comments varchar(300) )
RETURNS INT
AS
BEGIN
DECLARE @.Result int
SET @.Result = 0
WHILE @.Result < LEN(LTRIM(RTRIM(@.Comments)))
BEGIN
IF SUBSTRING(@.Comments, @.Result, 1) = '/'
BEGIN
BREAK
END
SET @.Result = @.Result + 1
END
RETURN (@.Result)
END
When I try to create a view based on this function in QA by changing "Create
Function" to "Create View," I get the following errors:
Server: Msg 170, Level 15, State 1, Procedure GetFirstSlash, Line 2
Line 2: Incorrect syntax near '@.Comments'.
Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 11
Must declare the variable '@.Comments'.
Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 13
Must declare the variable '@.Comments'.
Server: Msg 178, Level 15, State 1, Procedure GetFirstSlash, Line 21
A RETURN statement with a return value cannot be used in this context.
"Scott Morris" wrote:

> It helps to tell us exactly what you are doing. It is also critical to tell
> us exactly what "throws errors" means. When you encounter an error, post
> the exact text of the error. And get in the practice of specifying which
> version (and service pack level) of sql server you are using.
>
> A view is effectively a pre-cannned select query. You cannot define a view
> that will accept arguments and pass those arguments to the function.
>
>
|||A view is a different thing compared to a function. A view cannot take parameters, not can it
contain code, except for one SELECT statement. In addition, your function isn't even a table values
function, it is a scalar function. A view exposes a set (a table) where a scalar function returns a
scalar value when executed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Josh" <Josh@.discussions.microsoft.com> wrote in message
news:C2DF362C-89AA-4F3D-A5CD-BDEE6F1A866B@.microsoft.com...[vbcol=seagreen]
> OK. Hope this will make sense to people unfamiliar with our specific
> database. We have a table with a "Comments" field that contains several
> pieces of information delimited by slashes (/). I didn't design it this way
> and would not have; I'd have put each piece of data in its own field. But
> what's done is done and I have to work with it. The DBA created functions to
> extract the individual portions of this field. One of them has the syntax
> below:
> CREATE FUNCTION dbo.GetFirstSlash
> ( @.Comments varchar(300) )
> RETURNS INT
> AS
> BEGIN
> DECLARE @.Result int
> SET @.Result = 0
> WHILE @.Result < LEN(LTRIM(RTRIM(@.Comments)))
> BEGIN
> IF SUBSTRING(@.Comments, @.Result, 1) = '/'
> BEGIN
> BREAK
> END
> SET @.Result = @.Result + 1
> END
> RETURN (@.Result)
> END
> When I try to create a view based on this function in QA by changing "Create
> Function" to "Create View," I get the following errors:
> Server: Msg 170, Level 15, State 1, Procedure GetFirstSlash, Line 2
> Line 2: Incorrect syntax near '@.Comments'.
> Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 11
> Must declare the variable '@.Comments'.
> Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 13
> Must declare the variable '@.Comments'.
> Server: Msg 178, Level 15, State 1, Procedure GetFirstSlash, Line 21
> A RETURN statement with a return value cannot be used in this context.
> "Scott Morris" wrote:
|||Once again - a view does not have arguments. You cannot simply replace
"create function" with "create view" in this case. Personally, I recommend
a different approach. First, write a script that contains a select query
that does what you want. Then, try to convert that to a view.
Seems to me that you probably want a view that is based on the table
containing the comments column and that uses some function (or logic) that
parses the comment column into its individual components.

Create View based on User-Defined Function

I would like to create a View in SQL Server 2000, based on a user-defined
function. However when I try it, the "Functions" tab is empty. There are no
functions to choose from. I can select tables and other views.Josh
Well , have you already created UDF?
"Josh" <Josh@.discussions.microsoft.com> wrote in message
news:4A014F2F-6429-4564-B0CD-A8896D5C1882@.microsoft.com...
>I would like to create a View in SQL Server 2000, based on a user-defined
> function. However when I try it, the "Functions" tab is empty. There are
> no
> functions to choose from. I can select tables and other views.|||Yes. I have 8 of them.
"Uri Dimant" wrote:
> Josh
> Well , have you already created UDF?
> "Josh" <Josh@.discussions.microsoft.com> wrote in message
> news:4A014F2F-6429-4564-B0CD-A8896D5C1882@.microsoft.com...
> >I would like to create a View in SQL Server 2000, based on a user-defined
> > function. However when I try it, the "Functions" tab is empty. There are
> > no
> > functions to choose from. I can select tables and other views.
>
>|||The problem is with your tool of choice. Use QA and just type the
appropriate create/alter view query yourself.|||Josh
I have no problems. Perhpas if you are using EM , you need to refresh
thisn tool im order to be able to see the functions
"Josh" <Josh@.discussions.microsoft.com> wrote in message
news:433B1DEC-B7AB-4C90-A9B1-932CCDE4EE3F@.microsoft.com...
> Yes. I have 8 of them.
> "Uri Dimant" wrote:
>> Josh
>> Well , have you already created UDF?
>> "Josh" <Josh@.discussions.microsoft.com> wrote in message
>> news:4A014F2F-6429-4564-B0CD-A8896D5C1882@.microsoft.com...
>> >I would like to create a View in SQL Server 2000, based on a
>> >user-defined
>> > function. However when I try it, the "Functions" tab is empty. There
>> > are
>> > no
>> > functions to choose from. I can select tables and other views.
>>|||Yeah, I've tried that but the function I'm trying to use contains variables
and the QA throws errors when I try to do it that way.
My ultimate goal is to use the function in MS Access. I was thinking that
I'd need to convert it to a view before I could import or link to it. If
there's another way I'd love to hear about it (I've posted this portion of
the question in one of the Access newsgroups).
Josh
"Scott Morris" wrote:
> The problem is with your tool of choice. Use QA and just type the
> appropriate create/alter view query yourself.
>
>|||> Yeah, I've tried that but the function I'm trying to use contains
> variables
> and the QA throws errors when I try to do it that way.
It helps to tell us exactly what you are doing. It is also critical to tell
us exactly what "throws errors" means. When you encounter an error, post
the exact text of the error. And get in the practice of specifying which
version (and service pack level) of sql server you are using.
> My ultimate goal is to use the function in MS Access. I was thinking that
> I'd need to convert it to a view before I could import or link to it. If
> there's another way I'd love to hear about it (I've posted this portion of
> the question in one of the Access newsgroups).
> Josh
A view is effectively a pre-cannned select query. You cannot define a view
that will accept arguments and pass those arguments to the function.|||OK. Hope this will make sense to people unfamiliar with our specific
database. We have a table with a "Comments" field that contains several
pieces of information delimited by slashes (/). I didn't design it this way
and would not have; I'd have put each piece of data in its own field. But
what's done is done and I have to work with it. The DBA created functions to
extract the individual portions of this field. One of them has the syntax
below:
CREATE FUNCTION dbo.GetFirstSlash
( @.Comments varchar(300) )
RETURNS INT
AS
BEGIN
DECLARE @.Result int
SET @.Result = 0
WHILE @.Result < LEN(LTRIM(RTRIM(@.Comments)))
BEGIN
IF SUBSTRING(@.Comments, @.Result, 1) = '/'
BEGIN
BREAK
END
SET @.Result = @.Result + 1
END
RETURN (@.Result)
END
When I try to create a view based on this function in QA by changing "Create
Function" to "Create View," I get the following errors:
Server: Msg 170, Level 15, State 1, Procedure GetFirstSlash, Line 2
Line 2: Incorrect syntax near '@.Comments'.
Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 11
Must declare the variable '@.Comments'.
Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 13
Must declare the variable '@.Comments'.
Server: Msg 178, Level 15, State 1, Procedure GetFirstSlash, Line 21
A RETURN statement with a return value cannot be used in this context.
"Scott Morris" wrote:
> > Yeah, I've tried that but the function I'm trying to use contains
> > variables
> > and the QA throws errors when I try to do it that way.
> It helps to tell us exactly what you are doing. It is also critical to tell
> us exactly what "throws errors" means. When you encounter an error, post
> the exact text of the error. And get in the practice of specifying which
> version (and service pack level) of sql server you are using.
> > My ultimate goal is to use the function in MS Access. I was thinking that
> > I'd need to convert it to a view before I could import or link to it. If
> > there's another way I'd love to hear about it (I've posted this portion of
> > the question in one of the Access newsgroups).
> >
> > Josh
> A view is effectively a pre-cannned select query. You cannot define a view
> that will accept arguments and pass those arguments to the function.
>
>|||A view is a different thing compared to a function. A view cannot take parameters, not can it
contain code, except for one SELECT statement. In addition, your function isn't even a table values
function, it is a scalar function. A view exposes a set (a table) where a scalar function returns a
scalar value when executed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Josh" <Josh@.discussions.microsoft.com> wrote in message
news:C2DF362C-89AA-4F3D-A5CD-BDEE6F1A866B@.microsoft.com...
> OK. Hope this will make sense to people unfamiliar with our specific
> database. We have a table with a "Comments" field that contains several
> pieces of information delimited by slashes (/). I didn't design it this way
> and would not have; I'd have put each piece of data in its own field. But
> what's done is done and I have to work with it. The DBA created functions to
> extract the individual portions of this field. One of them has the syntax
> below:
> CREATE FUNCTION dbo.GetFirstSlash
> ( @.Comments varchar(300) )
> RETURNS INT
> AS
> BEGIN
> DECLARE @.Result int
> SET @.Result = 0
> WHILE @.Result < LEN(LTRIM(RTRIM(@.Comments)))
> BEGIN
> IF SUBSTRING(@.Comments, @.Result, 1) = '/'
> BEGIN
> BREAK
> END
> SET @.Result = @.Result + 1
> END
> RETURN (@.Result)
> END
> When I try to create a view based on this function in QA by changing "Create
> Function" to "Create View," I get the following errors:
> Server: Msg 170, Level 15, State 1, Procedure GetFirstSlash, Line 2
> Line 2: Incorrect syntax near '@.Comments'.
> Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 11
> Must declare the variable '@.Comments'.
> Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 13
> Must declare the variable '@.Comments'.
> Server: Msg 178, Level 15, State 1, Procedure GetFirstSlash, Line 21
> A RETURN statement with a return value cannot be used in this context.
> "Scott Morris" wrote:
>> > Yeah, I've tried that but the function I'm trying to use contains
>> > variables
>> > and the QA throws errors when I try to do it that way.
>> It helps to tell us exactly what you are doing. It is also critical to tell
>> us exactly what "throws errors" means. When you encounter an error, post
>> the exact text of the error. And get in the practice of specifying which
>> version (and service pack level) of sql server you are using.
>> > My ultimate goal is to use the function in MS Access. I was thinking that
>> > I'd need to convert it to a view before I could import or link to it. If
>> > there's another way I'd love to hear about it (I've posted this portion of
>> > the question in one of the Access newsgroups).
>> >
>> > Josh
>> A view is effectively a pre-cannned select query. You cannot define a view
>> that will accept arguments and pass those arguments to the function.
>>|||Once again - a view does not have arguments. You cannot simply replace
"create function" with "create view" in this case. Personally, I recommend
a different approach. First, write a script that contains a select query
that does what you want. Then, try to convert that to a view.
Seems to me that you probably want a view that is based on the table
containing the comments column and that uses some function (or logic) that
parses the comment column into its individual components.|||On Wed, 27 Sep 2006 09:20:02 -0700, Josh wrote:
>CREATE FUNCTION dbo.GetFirstSlash
> ( @.Comments varchar(300) )
>RETURNS INT
>AS
>BEGIN
>DECLARE @.Result int
>SET @.Result = 0
>WHILE @.Result < LEN(LTRIM(RTRIM(@.Comments)))
> BEGIN
> IF SUBSTRING(@.Comments, @.Result, 1) = '/'
> BEGIN
> BREAK
> END
> SET @.Result = @.Result + 1
> END
>RETURN (@.Result)
>END
Hi Josh,
I know that this is not what you're asking, but exactly how does the
result of this function differ from
CHARINDEX('/', @.Comments)
?
--
Hugo Kornelis, SQL Server MVP