Grouping Multiple Dimensions?

We use Excel 2007 as our front end to our AS2005 cube. Is there a way to group/display multiple dimensions under a single area? I know that you can use Display folders with measures.

Are you wanting to have a folder displayed under which attributes from multiple dimensions are displayed, or are you just wanting attributes from multiple dimensions displayed on an axis of your pivot table?

B.

|||Hi Brian,

The former. I'd like to have a folder or section where attributes from multiple dimensions can be displayed in a group. For example, let's say I have a Products dimension with a Product Line attribute and a Ship Node dimension with a Ship Node Name attribute. I'd like to have the Product Line and Ship Node attributes under a grouping or displayed in a section called "xxx" so users would just navigate to "xxx" in the Field List to find the aforementioned attributes. Does that make sense? And is that possible in Excel 2007?
|||

When you pull data from an OLAP cube directly into Excel 2007, it gives the option of generating a Pivot Table or a Pivot Table & Chart. Either way, you get that field list on the side of the window that organizes everything the way it is organized in the cube.

I am not aware of an easy way to alter this. One thing that comes to mind is possibly connecting directly to the relational data warehouse that feeds to cube (but this would require you to by-pass SSAS security). But then, everything would be jumbled.

Another would be to have an SSRS report with a single table of the elements you want and then call the report's URL with rendering instructions for either CSV or XML (or just have the report generate an XLS and open that yourself) and then generating a pivot table off the Excel data set. Still, I can't really see going into production with the SSRS solutoin.

B.

|||

Hello! I do not think it is possible.

Actually I have created named sets with crossjoin of two separate dimensions like product and customer in AS2005 and the previous version. It is possible to build them on the server, but the problem is that no client I have seen, like ProClarity Professional, will show them(and support them). These sets(or attributes from different dimensions) are not supported in any client that I know about. They will not show up in dimension tools in clients.

Since I do not now about every client on the market I can be wrong.

HTH

Thomas Ivarsson

|||Thanks for the input Brian and Thomas! I'll give it to the rest of the day to see if I can come up with anything.

Grouping Data for Consolidated Notification

We have a requirement where we need to send a single consolidated list of items belonging to a user which get active on particular date.

So, for example Item 1, Item 2, Item 3 gets active. These entries are inserted as Events. Now, we need to send single notification to the user with the email as:

=========================

Dear User,

Your following items got active today:

Item 1

Item 2

Item 3

Thanks,

Customer Care

=========================

For this we set the DigestDelievery to true and the emails indeed were consolidated. But, what it did was that it send single email notification repeating the entire content for each item as follows:

=========================

Dear User,

Your following items got active today:

Item 1

Thanks,

Customer Care

Dear User,

Your following items got active today:

Item 2

Thanks,

Customer Care

Dear User,

Your following items got active today:

Item 3

Thanks,

Customer Care

=========================

Please let me know if we are missing any setting or any changes need to be made in the .xslt for this to work.

regards,

Rajiv

I assume you are using a built-in XSLT formatter.

Since I have no idea about how to use/configure XSLT, in my application I created a custom content formatter in C# (this is really easy), and over there (in the .cs class) I "manually" built the resulting HTML string and inserted my items in a loop. The result HTML string is then sent as the email body.

Advantages:

1. I have 1 message "header" and 1 "footer". What's in between, gets "populated" at runtime in a loop, whether it's just 1 item or many.

2. Maybe, it's just as easy when using XSLT, ... I just don't know. But the emails my customers get contain hyperlinks which bring them right to the web page(s) for those particular item(s). Your management will love this feature!

3. Although I did not have to use this in my project, but if you need this, you can easily fetch additional data from some other non-NS data sources and "plug" it into your email message. It's possible because in a custom content formatter (C# or VB.NET, - your choice) you can use whatever .NET techniques you need, such as ADO.NET, System.IO (if you need to read from, say, some XML files), and whatever else you might want to do: a custom content formatter is just a regular .NET assembly, and you can use it as such.

|||Hi Rajiv -

From a SSNS perspective, it sounds like you have everything configured for digest delivery properly. Since it's not being formatted the way you wish, the issue is in the content formatter.

If you are using the built-in XSLT content formatter, try adjusting the
XSLTransform document.

Try placing the header and footer text directly in the XSLT document and the notification data in an <xsl:template> Match on "notification". You can then use teh <xsl:apply-templates> to call the notification section.

HTH...

Joe|||

Thanks for reply, I was able to resolve the issue as you have suggested.

regards,

Rajiv

grouped by month

Dear All,

I'd like to write a query that lists items from a single table but groups the listed items by a date (data of item entered into the table)

So all items matching a criteria and were entered during March should be listed underneath one-another
Then all items matching the same criteria but entered during April should be grouped again.

Not sure what would be the right approach here.

I'm thinkning, creating a temp table putting data in there but altering the data enterd field into just year and month, and then group the result by that field?

Will this work?group by month(Date)

More over
group by year(date), month(date)

group results into a string

how to group results from a select statement with single column into a comma
separated string? e.g.
results
--
a
b
c
...
string:
a, b, c, ...nono
I suggest you doing such operations on the client side
create table #test
(
col char(1)
)
insert into #test values ('a')
insert into #test values ('b')
insert into #test values ('c')
declare @.str varchar(20)
set @.str=''
select @.str=@.str+coalesce(col,'')+';' from #test
select @.str
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:BFA2B825-A041-4775-9692-1D1C5E0490AE@.microsoft.com...
> how to group results from a select statement with single column into a
comma
> separated string? e.g.
> results
> --
> a
> b
> c
> ...
> string:
> a, b, c, ...|||how can I do that in SQL Server?
"Uri Dimant" wrote:

> nono
> I suggest you doing such operations on the client side
> create table #test
> (
> col char(1)
> )
> insert into #test values ('a')
> insert into #test values ('b')
> insert into #test values ('c')
> declare @.str varchar(20)
> set @.str=''
> select @.str=@.str+coalesce(col,'')+';' from #test
> select @.str
>
> "nonno" <nonno@.discussions.microsoft.com> wrote in message
> news:BFA2B825-A041-4775-9692-1D1C5E0490AE@.microsoft.com...
> comma
>
>|||I did show you , did not I?
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:C9E42970-F176-4B03-ADA2-7ABFCEC1B944@.microsoft.com...
> how can I do that in SQL Server?
> "Uri Dimant" wrote:
>|||Thx Uri! It's really fantastic! Can u tell me what's the coalesce function
used for?
"Uri Dimant" wrote:

> I did show you , did not I?
>
> "nonno" <nonno@.discussions.microsoft.com> wrote in message
> news:C9E42970-F176-4B03-ADA2-7ABFCEC1B944@.microsoft.com...
>
>|||BOL says:
COALESCE
Returns the first nonnull expression among its arguments
Fore more details please refer to the BOL.
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:DB38C965-1514-4560-97ED-0C36AB7DC2F8@.microsoft.com...
> Thx Uri! It's really fantastic! Can u tell me what's the coalesce function
> used for?
> "Uri Dimant" wrote:
>
into a|||> Thx Uri! It's really fantastic!
Note that this method is also not supported, and does not guarantee correct
results in all possible cases.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Uri,
Would you mind mentioning the potential drawbacks of this method while
suggesting it, at least in the future? Several newbies with limited SQL
exposure tend to misunderstand such constructs as a valid SQL queries and
may even pass onto others as a recommended approach.
Anith|||Anith
If you read my post carefully I did mention what I would have done in such
situations

>I suggest you doing such operations on the client side
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23lPkKPqPFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Uri,
>
> Would you mind mentioning the potential drawbacks of this method while
> suggesting it, at least in the future? Several newbies with limited SQL
> exposure tend to misunderstand such constructs as a valid SQL queries and
> may even pass onto others as a recommended approach.
> --
> Anith
>|||>> If you read my post carefully I did mention what I would have done in
Ah..I did not notice that, my apologies :-(
Anith

GROUP BY/ HAVING CLAUSE problem

I'm trying to set up my adhoc query to return just one single record, which is aliased as 'foreign' in my sql statement (which is just the total amount of foreign overseas orders for just one day. All Sale_Type_Ids over 2 [integer datatype] are foreign orders):

SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) AS foreign
FROM Orders INNER JOIN
Processing ON Orders.ID = Processing.Order_ID
WHERE (Processing.Orderdate = '20050915') AND (Processing.status = 1)
GROUP BY CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END
HAVING (SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) >= 0)

..but my resultset is returning two records. If I remove the HAVING clause, it will return three records, with one being blank.
?
.netsports

In caculations COUNT (* ) is the only aggregate function in SQL Server that caculates NULL values, so your results will be different if you use COUNT (* ) if any of you columns allow NULLs. Try the link below for more about SQL Server NULLs. Hope this helps.
http://www.akadia.com/services/dealing_with_null_values.html|||i am using four table(ForumMain,ForumThreads,ReplyToThread,Authentication) in my forum.I have 4 asp.net pages in this forum. On the very first page, I am showing the Main category of forums.i.e all forums,last thread posted,total threads so far and the total number of replies to each thead and of course the name of the user who generated or added last thread.
To do this, i am using count function to count the total replies to each thread,RepliesToThread table is doing that(not counting total threads yet),Forum Category field from the ForumMain table,ThreadName from the ForumThreads table and the username from the Authentication table.
I am using Group By clause as well but every time a new thread is added from AddThread.aspx page, the name of the main category which the new thread is added into, is repeated on the main page.
i.e. if I add a new thread in main category DATABASE, and this main category has already one thread, the main page show me like
DATABASE already existing category.
date:25/09/2005
DATABASE new category
date:26/09/2005
rather than it should show me
DATABASE new category
date:26/09/2005
What should I do to avoid this repetition?
Thanks in advance.|||Try the link below and see if GROUP BY with CUBE or ROLLUP operator will help with you problem and some restrictions apply. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp|||Caddre, this linke you providehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp is not providing help to solve my problem.
I am looking forward to more helpful replies from you or anybody else.|||Hi,
You're summingSale_Type_Id values from Orders table. I thinks this is not what you want to get. You can Count theSale_Type_Id values to have the number of orders.

SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) AS [foreign]
And if your records in Orders table have Sale_Type_Id values greaterthan 2, for each distinct value of Sale_Type_Id you'll get a differentrow.
Because you group your records due to Sale_Type_Id's. Note that if itis 2 or less. You group them as nulls. And remove only the null groupby using the Having clause.
So you still have groups having Sale_Type_Id's greater than 2
I hope it is helpfull
Eralper
http://www.eralper.com

Group By Table Question

Is there a way to force a group in a table to stay together on a single page? I have a group that is showing the group by on one page and the details of the group on the next page and am wondering if there is a way to force them to be together? I don't want my groups to be broken into 2 or more pages and I don't want to necessarily always force a new page break if the group will fit on the page with another group.

Thanks...

Were you able to solve this problem? My situation is even a little simpler. I just want a group to go to the next page if it won't fit on the current. I.e. If my report will contain four groups, three of which will fit on the first page the fourth of which would be broken across to the second page, I want a break to occur before the fourth group. Obviously if a group is too large for a single page, a break will occur in the middle of that group.

I know you could do this in the Oracle reporting tool, and I thought I remembered doing it in Crystal... but, I can't find the option here.
|||Hello,

There is the ability to do this at the table level, but not the group level.
However, there are a couple of workarounds possible.

The first workaround that comes to mind is to use nested Lists, where the inner list is set to be kept together on the same page, if possible. This is a checkbox at the bottom of the general tab of the List properties dialog.

Another potential resolution would be enable Repeat group headers and/or footers on each page, so that at least the continuation of the group will contain the header and/or footer on all the pages it spans. This is a checkbox at bottom of the general tab of the Grouping and Sorting property dialog.

Group By Table Question

Is there a way to force a group in a table to stay together on a single page? I have a group that is showing the group by on one page and the details of the group on the next page and am wondering if there is a way to force them to be together? I don't want my groups to be broken into 2 or more pages and I don't want to necessarily always force a new page break if the group will fit on the page with another group.

Thanks...

Were you able to solve this problem? My situation is even a little simpler. I just want a group to go to the next page if it won't fit on the current. I.e. If my report will contain four groups, three of which will fit on the first page the fourth of which would be broken across to the second page, I want a break to occur before the fourth group. Obviously if a group is too large for a single page, a break will occur in the middle of that group.

I know you could do this in the Oracle reporting tool, and I thought I remembered doing it in Crystal... but, I can't find the option here.
|||Hello,

There is the ability to do this at the table level, but not the group level.
However, there are a couple of workarounds possible.

The first workaround that comes to mind is to use nested Lists, where the inner list is set to be kept together on the same page, if possible. This is a checkbox at the bottom of the general tab of the List properties dialog.

Another potential resolution would be enable Repeat group headers and/or footers on each page, so that at least the continuation of the group will contain the header and/or footer on all the pages it spans. This is a checkbox at bottom of the general tab of the Grouping and Sorting property dialog.

GROUP BY / HAVING clauses problem

I'm trying to set up my adhoc query to return just one single record,
which is aliased as 'foreign' in my sql statement (which is just the
total amount of foreign overseas orders for just one day. All
Sale_Type_Ids over 2 [integer datatype] are foreign orders):
SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
Orders.Sale_Type_Id ELSE NULL END) AS foreign
FROM Orders INNER JOIN
Processing ON Orders.ID = Processing.Order_ID
WHERE (Processing.Orderdate = '20050915') AND (Processing.status =
1)
GROUP BY CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id
ELSE NULL END
HAVING (SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
Orders.Sale_Type_Id ELSE NULL END) >= 0)
..but my resultset is returning two records. If I remove the HAVING
clause, it will return three records, with one being blank.
?
.netsportsIf I understand correctly, your GROUP BY is on:
All Orders.Sale_Type_ID greater than 2
NULL
So, without the HAVING I would expect one row returned for each Sale_Type_ID
> 2 and one for all the rest which become NULL. Do you have two ID>2 within
the rows covered by your WHERE clause.
The HAVING apparently is able to prune out the NULL value.
Perhaps all you wanted was:
SELECT SUM(Orders.Sale_Type_Id) AS foreign
FROM Orders INNER JOIN
Processing ON Orders.ID = Processing.Order_ID
WHERE (Processing.Orderdate = '20050915') AND (Processing.status =1) AND
Orders.Sale_Type_ID > 2
RLF
".Net Sports" <ballz2wall@.cox.net> wrote in message
news:1127848228.326875.56120@.g49g2000cwa.googlegroups.com...
> I'm trying to set up my adhoc query to return just one single record,
> which is aliased as 'foreign' in my sql statement (which is just the
> total amount of foreign overseas orders for just one day. All
> Sale_Type_Ids over 2 [integer datatype] are foreign orders):
> SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
> Orders.Sale_Type_Id ELSE NULL END) AS foreign
> FROM Orders INNER JOIN
> Processing ON Orders.ID = Processing.Order_ID
> WHERE (Processing.Orderdate = '20050915') AND (Processing.status =
> 1)
> GROUP BY CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id
> ELSE NULL END
> HAVING (SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
> Orders.Sale_Type_Id ELSE NULL END) >= 0)
> ..but my resultset is returning two records. If I remove the HAVING
> clause, it will return three records, with one being blank.
> ?
> .netsports
>|||If you only want a single row then remove the GROUP BY clause. GROUP BY
returns one row per group.
David Portas
SQL Server MVP
--|||Thanks. Looks like i'm getting the desired resultset. This sql
statement was sort of a permutation of an extensive one that would
bring back multiple records, but yes, I was starting to think if the
Group By was really necessary.|||I'm inferring your schema to be something like this:
CREATE TABLE Orders
(
ID INT PRIMARY KEY ?
, Sale_Type_Id INT
, Order_Total MONEY ?maybe
)
CREATE TABLE Processing
(
OrderID INT REFERENCES Orders(OrderID)
, Orderdate DATETIME
, Status INT
)
Your question sounds like you're either trying to find a count of
orders, or total amount purchased (which is why I made up that
ordertotal field). If either of those are what you're looking for,
there are much syntactically simpler solutions.
e.g.
SELECT COUNT(*) CountOfForeignOrders
, SUM(Order_Total) TotalOfForeignOrders
FROM Orders
WHERE Sale_Type_Id > 2 AND EXISTS(SELECT * FROM Processing WHERE
OrderID = Orders.ID AND OrderDate = '20050915' AND Status = 1)
When performing aggregates with join clauses, it's possible to
aggregate the same value more than once if a join causes the same row
to appear multiple times in the resultset. GROUP BY and HAVING aren't
necessary unless you're selecting your data based on your aggregate,
and if you'd return multiple aggregate sets.
-Alan