Default cube view in SSMS?

Hi,

When browsing a cube in management studio, is there a way set up default value in PivotTable?

Let’s say the cube has 20 dimensions and 10 measures, but user see 2 dimensions and one measure more often; when open a cube, can SSMS just show the default view (2 dimensions and one measure

Thanks in advance.
You can create a perspective with just the 2 dimensions and the measure more used. However, SSMS nor Excel will use one as the default (I think). Of course, if you a Pivot Table for the new perspective is just a question of refreshing that PT.|||
Thank you very much Tiago, your information is very helpful.
I really appropriated.

|||

SSMS is not a tool for end user.

If you, as administrator, want to see some predefined MDX queries you constuct it once then intercept the MDX with profiler and save the MDX in file. Then you can call those MDX queries in SSMS every time you want.

Declare in a view

hello,
I have a quick question, can you declare a varchar within a view?
the code at the bottom generate error: Incorrect syntax near the keyword
'declare'.
CODE:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*----
--
05/25/06 - RA : starting code to query data from shamrock db
----
--*/
ALTER VIEW vw_customer_usage_bgcolor
AS
declare @.loc_east varchar (20)
declare @.loc_west varchar (20)
set @.loc_west = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
'0%' ) AND
( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
'_____-___' ) AND
( inv_mast.delete_flag = 'N' ) AND
(inv_loc.location_id='102230' )
)
set @.loc_east = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
'0%' ) AND
( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
'_____-___' ) AND
( inv_mast.delete_flag = 'N' ) AND
(inv_loc.location_id='100001' )
)
declare @.B_color bit
set @.B_color =
(select case when @.loc_east > @.loc_west
then 0 --EAST
else 1 --WEST
end)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GONo, you cannot declare variables in a view. I think you want a stored
procedure or a table-valued function.
"ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
news:F8A6A242-7A31-4C7B-A629-A54C97124604@.microsoft.com...
> hello,
> I have a quick question, can you declare a varchar within a view?
> the code at the bottom generate error: Incorrect syntax near the keyword
> 'declare'.
> CODE:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> /*---
--
> 05/25/06 - RA : starting code to query data from shamrock db
> ----
--*/
> ALTER VIEW vw_customer_usage_bgcolor
> AS
> declare @.loc_east varchar (20)
> declare @.loc_west varchar (20)
> set @.loc_west = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
> JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
> WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
> '0%' ) AND
> ( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
> '_____-___' ) AND
> ( inv_mast.delete_flag = 'N' ) AND
> (inv_loc.location_id='102230' )
> )
> set @.loc_east = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
> JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
> WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
> '0%' ) AND
> ( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
> '_____-___' ) AND
> ( inv_mast.delete_flag = 'N' ) AND
> (inv_loc.location_id='100001' )
> )
> declare @.B_color bit
> set @.B_color =
> (select case when @.loc_east > @.loc_west
> then 0 --EAST
> else 1 --WEST
> end)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO|||ITDUDE27 wrote:
> hello,
> I have a quick question, can you declare a varchar within a view?
> the code at the bottom generate error: Incorrect syntax near the keyword
> 'declare'.
> CODE:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> /*---
--
> 05/25/06 - RA : starting code to query data from shamrock db
> ----
--*/
> ALTER VIEW vw_customer_usage_bgcolor
> AS
> declare @.loc_east varchar (20)
> declare @.loc_west varchar (20)
> set @.loc_west = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
> JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
> WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
> '0%' ) AND
> ( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
> '_____-___' ) AND
> ( inv_mast.delete_flag = 'N' ) AND
> (inv_loc.location_id='102230' )
> )
> set @.loc_east = (select sum(inv_loc.qty_on_hand) from inv_loc INNER
> JOIN inv_mast ON ( inv_mast.inv_mast_uid = inv_loc.inv_mast_uid )
> WHERE ( inv_mast.item_id not like '0%' and inv_mast.item_id not like
> '0%' ) AND
> ( inv_mast.item_id like '_____-___-___' or inv_mast.item_id like
> '_____-___' ) AND
> ( inv_mast.delete_flag = 'N' ) AND
> (inv_loc.location_id='100001' )
> )
> declare @.B_color bit
> set @.B_color =
> (select case when @.loc_east > @.loc_west
> then 0 --EAST
> else 1 --WEST
> end)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
No, you can't declare variables inside a view... Try this instead:
SELECT
CASE WHEN loc_west > loc_east THEN 1 ELSE 0 END
FROM
(
SELECT
SUM(CASE WHEN inv_loc.location_id = '102230' THEN inv_loc.qty_on_hnd
ELSE 0 END) AS loc_west,
SUM(CASE WHEN inv_loc.location_id = '100001' THEN inv_loc.qty_on_hnd
ELSE 0 END) AS loc_east
FROM inv_loc
INNER JOIN inv_mast
ON inv_loc.inv_mast_uid = inv_mast.inv_mast_uid
WHERE inv_mast.item_id NOT LIKE 0%
AND inv_mast.item_id LIKE '_____-___-___'
AND inv_mast.delete_flag = 'N'
) sums_table|||Check out in BOL index the topic "create function"
Under that you have something called
"Multi-statement Table-valued Functions"
That can handle your requirement.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||>> I have a quick question, can you declare a varchar within a view? <<
If you had ever read the first five pages of a chapter on VIEWs in any
SQL, you would know that a VIEW is a virtual table constructed from a
SELECT statement with some options.
So that did not answer your question? If you couldnot be bothered to
read a definition, wouldn't an error message a "strong hint"?
I also see that you write with bits and delete flags, just like
assembly language. Just like we did in the 1960's before RDBMS. You
also put the silly "volkwagen" suffix on view names to violate
ISO-11179 rules. All of those things are signs of really bad DDL and
DML.
Do you notice anything interesting about this predicate? Like it is
redundant?
(Inv_Mast.item_id NOT LIKE '0%'
AND
Inv_Mast.item_id NOT LIKE '0%' )
What you have posted here implies a LOT of serious errors. Stop
programming, do a full data audit and get some help from an SQL
porgrammer.

Decimals in view

Hello,

I want a view to always present my numeric fields with 2 decimals. In my table I have the following values in field "amount" (numeric(18,2))

181.25
176.5
170

I want the view to show
181.25
176.50
170.00

I have tried Cast but that doesn't seem to do the job.

Help!

RolfNote that this is very poor practice to use server side code to format data. Formatting ought to be done by the client.

With that said, you can use the Str() function to format it as a string.

-PatP

Decimal Help

I am having trouble with the following view in SQL:
SELECT CAST(AVG(L) AS Decimal(9, 1)) AS LAvg
FROM dbo.HOSurvey_holding
WHERE (L <> 0)
I want one decimal holder but when i have a whole number it doesnt put
a .0 after it? is this possible if so how would i do it? The data i
have in the database averages out to 4 and i would like for it to be
4.0
Any ideas on how i can fix the view so it adds the .0 after whole
numbers.That's a presentation layer issue basically. If you return numbers to your
application, your application should be able to present them in the
appropriate format to the user. VB, Excel, ASP etc all have the appropriate
controls for formatting.
But if you really need to do it in SQL Server, for example because you
create an export to a text file, you can use the STR function:
SELECT STR(AVG(L),10,1)AS LAvg
FROM dbo.HOSurvey_holding
WHERE (L <> 0)
Jacco Schalkwijk
SQL Server MVP
<pkruti@.hotmail.com> wrote in message
news:1113488579.279634.184360@.o13g2000cwo.googlegroups.com...
>I am having trouble with the following view in SQL:
> SELECT CAST(AVG(L) AS Decimal(9, 1)) AS LAvg
> FROM dbo.HOSurvey_holding
> WHERE (L <> 0)
> I want one decimal holder but when i have a whole number it doesnt put
> a .0 after it? is this possible if so how would i do it? The data i
> have in the database averages out to 4 and i would like for it to be
> 4.0
> Any ideas on how i can fix the view so it adds the .0 after whole
> numbers.
>

Decimal Formatting

Hi Group,
This is my probelm, I have a SQL view which holds prices to 2 decimal place
in the Management Console they are displayed to two decimal place i.e. 1.99.
However when I run an ODBC report the values are all to mutiple decimal
places i.e. 1.9900000000000000000. I have been told there is a way to forma
t
the vie win SQL to 2 decimal place but don't how is there a KB article or
help file that can oint me in the right direction or is there a way in the
ODBC SQL to force the formatting?
I am using SQL 2000
TIAFormatting result sets is best handled in the client UI. If you add a
formatting function on the server side, then it's a separate function
call for every row.
-mary
On Thu, 31 May 2007 01:55:00 -0700, Jonathan
<Jonathan@.discussions.microsoft.com> wrote:

>Hi Group,
>This is my probelm, I have a SQL view which holds prices to 2 decimal place
>in the Management Console they are displayed to two decimal place i.e. 1.99
.
>However when I run an ODBC report the values are all to mutiple decimal
>places i.e. 1.9900000000000000000. I have been told there is a way to form
at
>the vie win SQL to 2 decimal place but don't how is there a KB article or
>help file that can oint me in the right direction or is there a way in the
>ODBC SQL to force the formatting?
>I am using SQL 2000
>TIA|||Thank you Mary I have somehow solved it by using the STR function and a
little but of luck in the client app, but thank you again for your help
Jonathan
"Mary Chipman [MSFT]" wrote:

> Formatting result sets is best handled in the client UI. If you add a
> formatting function on the server side, then it's a separate function
> call for every row.
> -mary
> On Thu, 31 May 2007 01:55:00 -0700, Jonathan
> <Jonathan@.discussions.microsoft.com> wrote:
>
>

Decimal Formatting

Hi Group,
This is my probelm, I have a SQL view which holds prices to 2 decimal place
in the Management Console they are displayed to two decimal place i.e. 1.99.
However when I run an ODBC report the values are all to mutiple decimal
places i.e. 1.9900000000000000000. I have been told there is a way to format
the vie win SQL to 2 decimal place but don't how is there a KB article or
help file that can oint me in the right direction or is there a way in the
ODBC SQL to force the formatting?
I am using SQL 2000
TIA
Formatting result sets is best handled in the client UI. If you add a
formatting function on the server side, then it's a separate function
call for every row.
-mary
On Thu, 31 May 2007 01:55:00 -0700, Jonathan
<Jonathan@.discussions.microsoft.com> wrote:

>Hi Group,
>This is my probelm, I have a SQL view which holds prices to 2 decimal place
>in the Management Console they are displayed to two decimal place i.e. 1.99.
>However when I run an ODBC report the values are all to mutiple decimal
>places i.e. 1.9900000000000000000. I have been told there is a way to format
>the vie win SQL to 2 decimal place but don't how is there a KB article or
>help file that can oint me in the right direction or is there a way in the
>ODBC SQL to force the formatting?
>I am using SQL 2000
>TIA
|||Thank you Mary I have somehow solved it by using the STR function and a
little but of luck in the client app, but thank you again for your help
Jonathan
"Mary Chipman [MSFT]" wrote:

> Formatting result sets is best handled in the client UI. If you add a
> formatting function on the server side, then it's a separate function
> call for every row.
> -mary
> On Thu, 31 May 2007 01:55:00 -0700, Jonathan
> <Jonathan@.discussions.microsoft.com> wrote:
>

Debugging triggers on views in SQL Server 2005?

I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Server
2005. I setup a stored procedure to insert to the view, then (in Visual
Studio) put a stop in the stored procedure. This works fine, until I try to
step into the trigger. Then it tells me that no code is available.
If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won't
allow me, saying "A breakpoint could not be inserted at this location".
This only happens for any trigger on the view. If I try to set a breakpoint
on a trigger on a table, it works fine.
Does anyone know a way around this? I really would like to step though the
trigger on the view.Is anyone going to respond to this? I thought these news groups were managed
and a response could be expected in 48 hours.
"BrianInHouston" wrote:
> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Server
> 2005. I setup a stored procedure to insert to the view, then (in Visual
> Studio) put a stop in the stored procedure. This works fine, until I try to
> step into the trigger. Then it tells me that no code is available.
> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won't
> allow me, saying "A breakpoint could not be inserted at this location".
> This only happens for any trigger on the view. If I try to set a breakpoint
> on a trigger on a table, it works fine.
> Does anyone know a way around this? I really would like to step though the
> trigger on the view.|||I don't know the answer to the debugging question, but yes, if you're an
MSDN subscriber and posted this using the account you registered when you
signed up for managed newsgroups, you should get a response within 2
business days. If you don't, the FAQ page points you at
https://support.microsoft.com/common/survey.aspx?scid=sw;en;1296&showpage=1&ws=msdn&sd=msdn&pa=msdnw
to contact the folks at MSDN about it.
The first thing to do, however, is to ensure that the alias that you're
posting with is registered properly. To do that, go to
http://msdn.microsoft.com/subscriptions/, sign in, and click the Managed
Newsgroups link.
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianInHouston" <BrianInHouston@.discussions.microsoft.com> wrote in message
news:66D6AC1C-BB18-4FC4-B30C-F94D33A441E2@.microsoft.com...
> Is anyone going to respond to this? I thought these news groups were
> managed
> and a response could be expected in 48 hours.
> "BrianInHouston" wrote:
>> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL
>> Server
>> 2005. I setup a stored procedure to insert to the view, then (in Visual
>> Studio) put a stop in the stored procedure. This works fine, until I try
>> to
>> step into the trigger. Then it tells me that no code is available.
>> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it
>> won't
>> allow me, saying "A breakpoint could not be inserted at this location".
>> This only happens for any trigger on the view. If I try to set a
>> breakpoint
>> on a trigger on a table, it works fine.
>> Does anyone know a way around this? I really would like to step though
>> the
>> trigger on the view.

Debugging triggers on views in SQL Server 2005?

I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Server
2005. I setup a stored procedure to insert to the view, then (in Visual
Studio) put a stop in the stored procedure. This works fine, until I try to
step into the trigger. Then it tells me that no code is available.
If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won't
allow me, saying "A breakpoint could not be inserted at this location".
This only happens for any trigger on the view. If I try to set a breakpoint
on a trigger on a table, it works fine.
Does anyone know a way around this? I really would like to step though the
trigger on the view.Is anyone going to respond to this? I thought these news groups were manage
d
and a response could be expected in 48 hours.
"BrianInHouston" wrote:

> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Serve
r
> 2005. I setup a stored procedure to insert to the view, then (in Visual
> Studio) put a stop in the stored procedure. This works fine, until I try
to
> step into the trigger. Then it tells me that no code is available.
> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won
't
> allow me, saying "A breakpoint could not be inserted at this location".
> This only happens for any trigger on the view. If I try to set a breakpoi
nt
> on a trigger on a table, it works fine.
> Does anyone know a way around this? I really would like to step though th
e
> trigger on the view.|||I don't know the answer to the debugging question, but yes, if you're an
MSDN subscriber and posted this using the account you registered when you
signed up for managed newsgroups, you should get a response within 2
business days. If you don't, the FAQ page points you at
https://support.microsoft.com/commo...d=msdn&pa=msdnw
to contact the folks at MSDN about it.
The first thing to do, however, is to ensure that the alias that you're
posting with is registered properly. To do that, go to
http://msdn.microsoft.com/subscriptions/, sign in, and click the Managed
Newsgroups link.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianInHouston" <BrianInHouston@.discussions.microsoft.com> wrote in message
news:66D6AC1C-BB18-4FC4-B30C-F94D33A441E2@.microsoft.com...[vbcol=seagreen]
> Is anyone going to respond to this? I thought these news groups were
> managed
> and a response could be expected in 48 hours.
> "BrianInHouston" wrote:
>|||Is anyone going to respond to this? I thought these news groups were manage
d
and a response could be expected in 48 hours.
"BrianInHouston" wrote:

> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Serve
r
> 2005. I setup a stored procedure to insert to the view, then (in Visual
> Studio) put a stop in the stored procedure. This works fine, until I try
to
> step into the trigger. Then it tells me that no code is available.
> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won
't
> allow me, saying "A breakpoint could not be inserted at this location".
> This only happens for any trigger on the view. If I try to set a breakpoi
nt
> on a trigger on a table, it works fine.
> Does anyone know a way around this? I really would like to step though th
e
> trigger on the view.|||I don't know the answer to the debugging question, but yes, if you're an
MSDN subscriber and posted this using the account you registered when you
signed up for managed newsgroups, you should get a response within 2
business days. If you don't, the FAQ page points you at
https://support.microsoft.com/commo...d=msdn&pa=msdnw
to contact the folks at MSDN about it.
The first thing to do, however, is to ensure that the alias that you're
posting with is registered properly. To do that, go to
http://msdn.microsoft.com/subscriptions/, sign in, and click the Managed
Newsgroups link.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianInHouston" <BrianInHouston@.discussions.microsoft.com> wrote in message
news:66D6AC1C-BB18-4FC4-B30C-F94D33A441E2@.microsoft.com...[vbcol=seagreen]
> Is anyone going to respond to this? I thought these news groups were
> managed
> and a response could be expected in 48 hours.
> "BrianInHouston" wrote:
>

Debugging SQL Server statements in code

How can I view the raw SQL code that ASP.NET sends to SQL Server when using parameters?
The code below doesn't display the actual SQL statement, but instead:System.Data.SqlClient.SqlCommand.

string SQL = "INSERT INTO [BLAH] VALUES (@.startDate)";
SqlConnection mySqlConn = new SqlConnection(ConfigurationSettings.AppSettings["DSN"]);
SqlCommand mySqlCmd = new SqlCommand(SQL, mySqlConn);
mySqlCmd.Parameters.Add(new SqlParameter("@.startDate", SqlDbType.DateTime));
mySqlCmd.Parameters["@.startDate"].Value = Request.Form["start_date"];
lblSQLDebug.Text = mySqlCmd.ToString();
TIA.
Use SQL Profiler. It allows you to see all the SQL sent to the SQL Server. Very useful tool.|||Hi Douglas.
Following your suggestion, I setup the profiler. Unfortunately, itdoesn't receive my query before I receive the same ambigious error fromASP.NET.
Is it possible to gather the dynamic string assembled and sent to the database before it's actually executed?

|||If the query does not make it into SQL Profiler, then the problem is not a SQL Server problem. Whatexactlyis the error you are getting?|||

String was not recognized as a valid DateTime.

mySqlCmd.Parameters.Add(new SqlParameter("@.startDate", SqlDbType.DateTime));
mySqlCmd.Parameters["@.startDate"].Value = Request.Form["start_date"];
I'm using a third party component to generate the date via a pop-up calendar.
The line # of the error points here:
mySqlCmd.ExecuteNonQuery();
which seems to indicate that the SQL Query being sent to SQL Server isbad. I can't fix it until I know what the actual value of @.startDate isthat is being sent to the database server.
Thanks for helping me out.

|||This doesn't answer myquestion, but did help me debug. I enabled trace="true" and it allowedme to see how the control was submitting the values (different thanexpected).
I'm just surprised that ASP.NET doesn't have a method like RawQuerythat you could use to display the SQL before it's sent to the databaseprogramatically.
E.g., mySqlCmd.RawQuery.ToString();
I appreciate your support Douglas.
|||ASP.NET does not have such a facility. SQL Server does, but again, if the info does not get to SQL Server, of course it will not show up in the Profiler. You can, of course, debug the application and inspect values as they are added to the Parameters collection, or examine the entire Parameters collection in the debugger.