combining Sat-Sun and also Fri-Sun

One of the dimensions in my cube is Weekday, with 1=Monday through 7=Sunday.

I have created named sets {1,2,3,4,5} for Mon-Fri and {6,7} for Sat and Sun. However many people also want to include Friday as the weekend, they prefer {1,2,3,4} and {5,6,7}, so I created named sets for those too.

However, I can't find any way to use these at the client level (my client is Excel Pivot tables, but I evaluated many other clients). I find named sets are only usable within SSAS's own cube browser (as a filter). Further, I can't find any way to get the cube to pre-calculate the groups. I know named sets don't get calculated, I am open to another way to get this done. I use VBA within Excel to select multiple weekdays in the PivotTable, but this causes lengthy OLAP queries each time.

If I had only one way of breaking weekday vs weekend, I would create a new table with 7 records:

1 Weekday

2 Weekday

...

6 Weekend

7 Weekend

and then create a hierarchy, the weekday/weekend level would then get pre-calculated. But since I have two possibilities, I can't do this.

I've tried all kinds of creative possibilities, does anyone have a suggestion on how to get all 11 "members" of this dimension (7 individual weekdays, plus the 4 named sets) to be pre-calculated? Thanks in advance!

Hello! The simple solution is that you use a client that will present SSAS2005 named sets, like ProClarity Professional 6.3 or Excel 2007.

Older versions of Excel do not support named sets.

If this is not possible I suggest that you add two new attributes for the two different weekday/weekend classifications.

HTH

Thomas Ivarsson

Combining multiple columns into one column.

Combing multiple columns like [LastName],[FirstName] and
[MiddleName]into one column named as [Name] is very simple in Access,
but how will i do that in SQL? Any suggestions? PLease?
Thanks in advance,
Geri
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!You have to add another column, update with existing data,
and then drop existing columns.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Geri Gavertz" <gerific@.yahoo.com> wrote in message
news:ezPCuV8GFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Combing multiple columns like [LastName],[FirstName] and
> [MiddleName]into one column named as [Name] is very simple in Access,
> but how will i do that in SQL? Any suggestions? PLease?
> Thanks in advance,
> Geri
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||same as in Access
Select LastName+' ' + FirstName + ' ' + FirstName as Name from Table
Madhivanan|||same as in Access
Select LastName+' ' + FirstName + ' ' + MiddleName as Name from Table
Madhivanan|||<madhivanan2001@.gmail.com> wrote in message
news:1109400766.869281.200560@.o13g2000cwo.googlegroups.com...
> same as in Access
> Select LastName+' ' + FirstName + ' ' + FirstName as Name from Table
> Madhivanan
>
You might want to wrap them in IsNull so that a NULL in one of the columns
doesn't NULL out the entire result:
Select IsNull(FirstName, '') + ' ' + IsNull(MiddleName, '') + ' ' +
IsNull(LastName, '') As FullName from MyTable
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company

Combining multiple columns into one column.

Dear all,
I am having a problem on how to merge 3 columns into one column. I have a
columns named [LastName], [FirstName] and [MiddleName], I want those columns
to be as one and name it as [Name]. How will I do that? I already tried the
trick like what I did in Access but it does'nt work in SQL. Please help me
with this. Any suggestions will be much appreciated.
Thanks in advance,
Jir
Try:
alter table dbo.MyTable
add
MyComputedCOlumn as LastName + ', ' + FirstName + ' ' + MiddleName
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Jir" <Jir@.discussions.microsoft.com> wrote in message
news:F64E0FD3-3E0E-4B9A-AB49-0CFFE29B5D8F@.microsoft.com...
Dear all,
I am having a problem on how to merge 3 columns into one column. I have a
columns named [LastName], [FirstName] and [MiddleName], I want those columns
to be as one and name it as [Name]. How will I do that? I already tried the
trick like what I did in Access but it does'nt work in SQL. Please help me
with this. Any suggestions will be much appreciated.
Thanks in advance,
Jir

Combining established columns into one

I have a table whose schema is already defined and populated with data. I would like to create a column named Name that combines the first and last name columns in the following format "last name, first name". I tried to create a formula that concatenated these two columns, but it kept spitting up on me. Any ideas?Could you please post your syntax?|||It is best to do the formatting for display purposes on the client. What if you want to change the formatting later? You hav e to make schema changes even if you use computed columns or views or queries in SPs.|||

you may wish to use a calculated column

use northwind
select * from employees
go
alter table employees
add
fullname as rtrim(lastname)+','+rtrim(firstname)
go

select fullname,lastname,firstname from employees

|||I realize that it would be best to do all the formatting on the client. The problem is that I have about 50 stored procedures that were developed on another database that was "supposed to" have the same table schemas. Unfortunately, the developer decided to split apart the names into first name and last name fields. It would be easier to just created a computed column.|||Actually, splitting the name into it's constituent parts and storing it is the correct way. You can use a computed column or view with the computed expression or modify your SP to include the computed expression. With all these methods, you can get the required column for display purposes. But if you want to search on this concatenated string then it is a different deal. Performance depends on lot of factors like index on the computed column, whether optimizer matches the computed column expression and uses the index and so on.

columns not appearing.

I have generated a .dsv using named queries.

The dsv consists of 3 tables:

1)CLIENT

2)client_guarantor.

3)client_contacts.

The client_guarantor and client_contacts tables both have a foreign key into the CLIENT table via the column CLIENT.client_id. To give you a visual, the .dsv design view would look something like this:

client_guarantor <- CLIENT -> client_contacts.

I use this .dsv to generate a model and deploy it. My problem arises once I get into report builder. It will not let me build a report that uses columns from all three tables. For instance, if i pull a column from the CLIENT table, then a column from the client_contacts table, and next try to get anything from the client_guarantor table, report builder lets me navigate back to the client_guarantor table (because i have added a column from CLIENT) but wont let me add any columns from client_guarantor to the report.

Am i missing something? I assume that since the CLIENT table can see both the client_guarantor and client_contacts tables, if I add anything from the CLIENT table into my report, I should have access to both other tables located in my model. Am I wrong in this assumption, or do I need to add some flag to my model or something? Any help or insight is appreciated.

I searched through the forum, and for anyone interested, the answer is here:

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

Column Query with a Variable

I have 30 fields in a table called ADP_Pay_Detail that I need to query with
the exact same query. Each field is named DED_CD_1, DED_CD_2, etc. up to
DED_CD_30. I have coded a counter with a WHILE statement to increment the
last number of this string, and assign the entire string to a varchar
variable named @.column_amt. All of the type conversions I need to do are
correct. I then try to write a query using this variable as a field name.
An example would be
SELECT *
FROM ADP_Pay_Detail
where @.column_amt = 30
This isn't working at all. Can anyone give me a suggestion about how to
accomplish what I want to do? The query I have to write is much more
complicated than my example, and I'd hate to have to write it 30 times to
query each column!
Thanks!
Keith
Keith>I have 30 fields in a table called ADP_Pay_Detail that I need to query with
> the exact same query. Each field is named DED_CD_1, DED_CD_2, etc. up to
> DED_CD_30.
Just curious if you have considered a more sensible and relational design?
Are you going to add DED_CD_31 -> DED_CD_n at some point?

> SELECT *
> FROM ADP_Pay_Detail
> where @.column_amt = 30
> This isn't working at all.
That is right, because T-SQL is not a language that allows you to construct
queries like this, you must tell it about the metadata and not let it decide
on its own. My first suggestion would be to pass all 30 columns back, and
let the app display only the column(s) it needs. This will be more flexible
as you can later decide to show 2 or 3 or all columns and you won't have
much change to do...
My more intuitive response would be to store the data and its value as data,
instead of mixing data and metadata. An example might be:
CREATE TABLE dbo.ADP_Pay_Detail
(
pdID INT PRIMARY KEY
-- , other columns
)
CREATE TABLE dbo.ADP_Pay_Detail_DED_CD
(
pdID INT FOREIGN KEY REFERENCES dbo.ADP_Pay_Detail(pdID),
column_amt TINYINT, -- CHECK CONSTRAINT perhaps?
value INT
)
Now you can say
SELECT * FROM ADP_Pay_Detail d
INNER JOIN ADP_Pay_Detail_DED_CD c
ON d.pdID = c.pdID
WHERE c.column_amt = 30
And you don't have any silly column names with data embedded in them. (Next
you can work on manageable table names.)

> Can anyone give me a suggestion about how to
> accomplish what I want to do?
As a last resort, dynamic SQL. PLEASE READ THE FOLLOWING and heed the
warnings seriously:
http://www.sommarskog.se/dynamic_sql.html|||>> I have 30 fields [sic] in a table called ADP_Pay_Detail that I need to query w
ith
the exact same query. Each field [sic] is named DED_CD_1, DED_CD_2,
etc. up to DED_CD_30. <<
Rows are not records; fields are not columns; tables are not files.
You need to get a book on RDBMS basics and read the chapter on Normal
Forms. What you have here is a 1950's COBOL file layout with a fake
OCCURS clause.
Apparently SQL is the first compiled language you have ever used. It
is also a declarative language, so writing loops in a proprietary 3GL
is a sign of poor coding.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Column Headings in Result Sets

Does the ANSI SQL Standard specify how columns are named in result sets?

I ask this because I just came across some behavior that surprised me, or went against my expectations.

Given the table: t1 (c1 int)
and this query: select t1.c1 from t1

the results are displayed as follows on all databases I tried it on (SQL Server, Oracle, DB2, mySQL and Sybase):

c1
--
1
2
..etc

I would have expected the column heading to be different, as follows:

t1.c1
--
1
2
...etc

So, if you say 't1.c1' in the select list, you should see t1.c1 in the column heading in the result set.

Given another table, t2 (c1 int), the column headings in the result set of the query 'select t1.c1, t2.c1 from t1,t2' were the same on all database platforms:
c1 | c1
----
1 | 2
2 | 3

This strikes me as ambiguous, because how do you know which result column came from which table ? I know that you can use column aliases if you want unique names in your column headings, but is there a good reason why the column headings 't1.c1' and 't2.c1' wouldn't be used by default if you specify 't1.c1' and 't2.c1' as items in your select list?

Thanks,
Colm.... is there a good reason why the column headings 't1.c1' and 't2.c1' wouldn't be used by default if you specify 't1.c1' and 't2.c1' as items in your select list?yes -- because by the time the result set is constructed, the database has forgotten which table each column came from

:)

more accurately, a column name is an identifier, whereas "t1.c1" is a string

you could always do this, if you really need it --

select t1.c1 as "t1.c1", ...

colors

Does anyone know if you can update background colors in RS 2005 using hex
values? It looks like you can only assign named colors. When I go to
custom colors and use the palette and assign a custom color, RS puts a hex
value in the expressio box, but it returns an error that it is an
un-recognized color.
Thanks
BillBill,
In theory you can use an expression for the background colour.
I have tried expressions like =rgb(255,0,255) which the Expression
Editor accepts as a syntactically correct expression. Stylistically
it's a bit overpowering! :)
However, although the expression shows in the textbox for the
BackgroundColor property in the Properties pane it is ignored when
previewing.
I assume it is a bug in September CTP.
Andrew Watt
MVP - InfoPath
On Mon, 17 Oct 2005 22:54:18 -0700, "wsaHarem" <spammers@.screwYou.com>
wrote:
>Does anyone know if you can update background colors in RS 2005 using hex
>values? It looks like you can only assign named colors. When I go to
>custom colors and use the palette and assign a custom color, RS puts a hex
>value in the expressio box, but it returns an error that it is an
>un-recognized color.
>Thanks
>Bill|||A color value in RDL is a either a color name or a hex HTML color string of
the form #HHHHHH. See also:
http://msdn.microsoft.com/library/default.asp?url=/workshop/author/dhtml/reference/colors/colors.asp
If you want to use the RGB function, you would need to convert the resulting
integer into a string. I.e.
=String.Format("#{0:x6}",RGB(255, 0, 255))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
news:m8i9l1l10dtp2c27q35bpdmmujkleb97mf@.4ax.com...
> Bill,
> In theory you can use an expression for the background colour.
> I have tried expressions like =rgb(255,0,255) which the Expression
> Editor accepts as a syntactically correct expression. Stylistically
> it's a bit overpowering! :)
> However, although the expression shows in the textbox for the
> BackgroundColor property in the Properties pane it is ignored when
> previewing.
> I assume it is a bug in September CTP.
> Andrew Watt
> MVP - InfoPath
>
> On Mon, 17 Oct 2005 22:54:18 -0700, "wsaHarem" <spammers@.screwYou.com>
> wrote:
>>Does anyone know if you can update background colors in RS 2005 using hex
>>values? It looks like you can only assign named colors. When I go to
>>custom colors and use the palette and assign a custom color, RS puts a hex
>>value in the expressio box, but it returns an error that it is an
>>un-recognized color.
>>Thanks
>>Bill
>|||Thanks, Robert.
Unfortunately the Expression Editor doesn't like your code (or my
attempts to correct it). Versions which the Expression Editor likes
seem to be ignored in display.
Nor have I found a way to express a custom colour using the Hex
notation that actually displays.
Any further suggestions?
Thanks
Andrew Watt
MVP - InfoPath
On Tue, 18 Oct 2005 19:54:20 -0700, "Robert Bruckner [MSFT]"
<robruc@.online.microsoft.com> wrote:
>A color value in RDL is a either a color name or a hex HTML color string of
>the form #HHHHHH. See also:
>http://msdn.microsoft.com/library/default.asp?url=/workshop/author/dhtml/reference/colors/colors.asp
>If you want to use the RGB function, you would need to convert the resulting
>integer into a string. I.e.
>=String.Format("#{0:x6}",RGB(255, 0, 255))
>
>-- Robert
>This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
>news:m8i9l1l10dtp2c27q35bpdmmujkleb97mf@.4ax.com...
>> Bill,
>> In theory you can use an expression for the background colour.
>> I have tried expressions like =rgb(255,0,255) which the Expression
>> Editor accepts as a syntactically correct expression. Stylistically
>> it's a bit overpowering! :)
>> However, although the expression shows in the textbox for the
>> BackgroundColor property in the Properties pane it is ignored when
>> previewing.
>> I assume it is a bug in September CTP.
>> Andrew Watt
>> MVP - InfoPath
>>
>> On Mon, 17 Oct 2005 22:54:18 -0700, "wsaHarem" <spammers@.screwYou.com>
>> wrote:
>>Does anyone know if you can update background colors in RS 2005 using hex
>>values? It looks like you can only assign named colors. When I go to
>>custom colors and use the palette and assign a custom color, RS puts a hex
>>value in the expressio box, but it returns an error that it is an
>>un-recognized color.
>>Thanks
>>Bill
>|||The expression is valid - just try to run the report. The expression editor
marking the expression as invalid at design time is a bug.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
news:vifcl1t2g9o34g6utcdsphe3u1rq6vkcqn@.4ax.com...
> Thanks, Robert.
> Unfortunately the Expression Editor doesn't like your code (or my
> attempts to correct it). Versions which the Expression Editor likes
> seem to be ignored in display.
> Nor have I found a way to express a custom colour using the Hex
> notation that actually displays.
> Any further suggestions?
> Thanks
> Andrew Watt
> MVP - InfoPath
> On Tue, 18 Oct 2005 19:54:20 -0700, "Robert Bruckner [MSFT]"
> <robruc@.online.microsoft.com> wrote:
>>A color value in RDL is a either a color name or a hex HTML color string
>>of
>>the form #HHHHHH. See also:
>>http://msdn.microsoft.com/library/default.asp?url=/workshop/author/dhtml/reference/colors/colors.asp
>>If you want to use the RGB function, you would need to convert the
>>resulting
>>integer into a string. I.e.
>>=String.Format("#{0:x6}",RGB(255, 0, 255))
>>
>>-- Robert
>>This posting is provided "AS IS" with no warranties, and confers no
>>rights.
>>
>>
>>"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
>>news:m8i9l1l10dtp2c27q35bpdmmujkleb97mf@.4ax.com...
>> Bill,
>> In theory you can use an expression for the background colour.
>> I have tried expressions like =rgb(255,0,255) which the Expression
>> Editor accepts as a syntactically correct expression. Stylistically
>> it's a bit overpowering! :)
>> However, although the expression shows in the textbox for the
>> BackgroundColor property in the Properties pane it is ignored when
>> previewing.
>> I assume it is a bug in September CTP.
>> Andrew Watt
>> MVP - InfoPath
>>
>> On Mon, 17 Oct 2005 22:54:18 -0700, "wsaHarem" <spammers@.screwYou.com>
>> wrote:
>>Does anyone know if you can update background colors in RS 2005 using
>>hex
>>values? It looks like you can only assign named colors. When I go
>>to
>>custom colors and use the palette and assign a custom color, RS puts a
>>hex
>>value in the expressio box, but it returns an error that it is an
>>un-recognized color.
>>Thanks
>>Bill
>>
>|||Thanks, Robert.
There also seem to be bugs in the Layout tab and in the Preview tab
relating to this.
I will wait and see what the situation is in RTM then, if necessary,
post some bugs to Product Feedback Center then.
Andrew Watt
MVP - InfoPath
On Wed, 19 Oct 2005 09:29:49 -0700, "Robert Bruckner [MSFT]"
<robruc@.online.microsoft.com> wrote:
>The expression is valid - just try to run the report. The expression editor
>marking the expression as invalid at design time is a bug.
>-- Robert
>This posting is provided "AS IS" with no warranties, and confers no rights.
>
>"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
>news:vifcl1t2g9o34g6utcdsphe3u1rq6vkcqn@.4ax.com...
>> Thanks, Robert.
>> Unfortunately the Expression Editor doesn't like your code (or my
>> attempts to correct it). Versions which the Expression Editor likes
>> seem to be ignored in display.
>> Nor have I found a way to express a custom colour using the Hex
>> notation that actually displays.
>> Any further suggestions?
>> Thanks
>> Andrew Watt
>> MVP - InfoPath
>> On Tue, 18 Oct 2005 19:54:20 -0700, "Robert Bruckner [MSFT]"
>> <robruc@.online.microsoft.com> wrote:
>>A color value in RDL is a either a color name or a hex HTML color string
>>of
>>the form #HHHHHH. See also:
>>http://msdn.microsoft.com/library/default.asp?url=/workshop/author/dhtml/reference/colors/colors.asp
>>If you want to use the RGB function, you would need to convert the
>>resulting
>>integer into a string. I.e.
>>=String.Format("#{0:x6}",RGB(255, 0, 255))
>>
>>-- Robert
>>This posting is provided "AS IS" with no warranties, and confers no
>>rights.
>>
>>
>>"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
>>news:m8i9l1l10dtp2c27q35bpdmmujkleb97mf@.4ax.com...
>> Bill,
>> In theory you can use an expression for the background colour.
>> I have tried expressions like =rgb(255,0,255) which the Expression
>> Editor accepts as a syntactically correct expression. Stylistically
>> it's a bit overpowering! :)
>> However, although the expression shows in the textbox for the
>> BackgroundColor property in the Properties pane it is ignored when
>> previewing.
>> I assume it is a bug in September CTP.
>> Andrew Watt
>> MVP - InfoPath
>>
>> On Mon, 17 Oct 2005 22:54:18 -0700, "wsaHarem" <spammers@.screwYou.com>
>> wrote:
>>Does anyone know if you can update background colors in RS 2005 using
>>hex
>>values? It looks like you can only assign named colors. When I go
>>to
>>custom colors and use the palette and assign a custom color, RS puts a
>>hex
>>value in the expressio box, but it returns an error that it is an
>>un-recognized color.
>>Thanks
>>Bill
>>
>