Problems with groups

Hi!
I'm having some problems with creating a report, feels like it should
be fairly easy to solve but somehow I can't find the solution.
An example of my data (simplified) for a client:
Row1: Portfolio1, Stock1
Row2: Portfolio1, Stock2
Row3: Portfolio2, Stock3
Row4: Portfolio3, Stock1
What I want to do is display this data first grouped by Portfolio, and
then list the contents of that portfolio below it.
(group by Portfolio, then group by Stock)
Ie:
Portfolio1
Stock: Stock1
Stock2
Portfolio2
Stock: Stock3
Portfolio3
Stock: Stock1
I'm currenly using a table to display the data in "table details"-
rows. But the closest I can come up with is something like this:
Portfolio1
Stock: Stock1
Portfolio1
Stock: Stock2
I've also experimented with using "table headers" to display the
portfolio and then the portfolio contents in "table details" but this
will only display the first portfolio for the client, not the
following.
Any help is much appreciated!Managed to solve it by inserting a group at the stock-rows based on
Portfolio.Value, and then just adding the =Fields!Portfolio.Value to
the group header.|||On Aug 17, 5:08 am, mats.jogb...@.gmail.com wrote:
> Managed to solve it by inserting a group at the stock-rows based on
> Portfolio.Value, and then just adding the =Fields!Portfolio.Value to
> the group header.
It's no fun when you solve the easy ones yourself!
As an added tip, note that you can have as many group header (and
footer) rows as you want - sometimes the best place for column
headings is in an additional group header row that is just above the
details section.
And, while you can sort the group values in the report, it is
generally faster to do all your sorting in the database query - then
you don't have to specify any sorting in the report itself, since it
will process records in the (sorted) order in which they come from the
data source.

Problems with dual role of time dimension

I'm fairly new to SSAS, so forgive me for asking a question that’s probably quite easy to answer, if you know your way around cubes and dimensions.

The situation is this: I'm creating a cube for resource planning (dimensions include resource/team, project/customer and time, fact table include number of bookings and value of bookings/projects). The time dimension plays two roles:

1) The booking date. Meaning that ressource1 from Team A is booked for project X on given dates in the future.

2) Reporting date. The complete plan is loaded into a Data warehouse once a day. The reporting date should allow us to compare the current situation with a previous point in time (compare booking ratio, value of future projects etc). In effect the reporting date is the versioning of the plan.

I'm currently facing two problems with the dual role of the time dimension:

1) How can you make the Reporting date semi-additive without also affecting the booking date (booking count and value should be summed across booking dates for a given reporting date, but not across reporting dates)?

2) How can you set default values for reporting dates? - Default I would always want to see data for the latest reporting date (corresponding to the latest version of the plan). But this should not affect the booking date - for the latest version of the plan, I want to see all future bookings.

I guess these are common problems when building a planning cube with time versioning, so I hope somebody has some valuable input.

Thanks in advance.

Thomas N. S?rensen

Hi Thomas,

Here are links to past threads in this forum that could help you:

For problem 1) relating to semi-additive measures when there are multiple time dimensions:

lastnonempty & role playing date dimension

>>

Hello,

I'm just curious if in case of a role-playing date dimention it's possible to somehow tell SSAS to use only one role for LastNonEmpty aggregate?

Like we have a fact table with a few date related members - such as TransactionDate, DateOpened, DateClosed etc.

All measures in this fact table are set to aggregate as LastNonEmpty & everything works just fine as long as only TransactionDate is linked to dimDate.

If any other dates are linked then LastNonEmpty doesn't work properly anymore & we get unpredictable results.

So if it possible to set ONLY TransactionDate to be used as LastNonEmpty & for all other dates just aggregate as sum?

Thanks!

...

It is always only ONE role-playing time dimension. The problem is, you cannot control which one it is. Sorry, but there is no way to tell directly to SSAS which one. You can keep reordering the cube dimensions in AMO until SSAS picks the right one. After that, if the order doesn't change - it will always use the same one (it is stable algorithm w.r.t. order of dimensions).

---

Mosha - http://www.mosha.com/msolap
...

>>

For problem 2) relating to different default members for each role of a dimension:

Different Default Members for Role Playing Dimension

>>

...

You could try updating the default member for each role with cube MDX script statements instead, like:

Code Snippet

ALTER CUBE CurrentCube UPDATE DIMENSION [Base UOM].[UOM Abbrev],

DEFAULT_MEMBER = [Base UOM].[UOM Abbrev].&[Abbrev1];

ALTER CUBE CurrentCube UPDATE DIMENSION [Reporting UOM].[UOM Abbrev],

DEFAULT_MEMBER = [Reporting UOM].[UOM Abbrev].&[Abbrev2];

>>

Problems with an XML column....?

I have a table with an XML column that stores report definitions. For development purposes I have to delete and reinsert these reports on a fairly regular basis (300+ reports). When I do so, I notice the following: The size of the data file increases by roughly 30MB. The file I am loading the XML from (thru an SSIS package) is only 4MB. If I shrink the database, I can get rid of 20MB, but I still net a 10MB increase. The package gets run frequently. Right now my database is at about 250MB when it should be about 40-50MB. I am trying to avoid having to drop all constraints, truncating the table, and reapplying the constraints. If I disable the step in my package that deals with the XML column, I see no change in the database. It appears to be the DELETE that causes the most damage, as I have tested the package on purely an insert-basis and on a delete-insert-basis. Has anyone seen behavior like this?Since no one has responded in over a month, I am going to close this one out myself. I ended up dropping all of the constraints, truncating, and reapplying the constraints. I still have some growth in my data files but it has slowed considerably. I read that empty pages could be left behind by a DELETE statement, but I have never since file growth like this. Perhaps, SP1 addresses this. I don't know.