grouping question

Hi,

I have a cube which has a "Product" dimension that has 4 levels which are:

- High Level Product Name
- Mid Level Product Name
- Low Level Product Name
- Product Name

My fact table has measures for all 4 levels.

I have a report with an MDX data source. The report contains a table control which has 2 columns, "product name" and a measure column.

In my report I have defined two groups, one for product level 1, and another for product level 2. This will mean that my data will be grouped by products as follows: (below products a, b and c are level 1, x, y and z are level 2)...

Product Measure
- Prod A $1,500.00
- Prod B $1,200.00
- Prod X $400.00
- Prod Y $400.00
- Prod Z $400.00
- Prod C

Running the report works ok, but for some reason an additional unwanted total row is appended at the beggining of each of my level 2 groups which shows the total for that group. To explain what I mean, the report looks like this...

Product Measure
- Prod A $1,500.00
- Prod B $1,200.00
$1,200.00
- Prod X $400.00
- Prod Y $400.00
- Prod Z $400.00
- Prod C

Note that under product B a blank row has been added with the total. I don't want this and I can't figure out how to get rid of it. Anyone know what I am doing wrong?

If it helps my query is below (reporting services matches the product names to group them)...

SELECT
non empty {[Measures].[Ytd Average Capital] } ON columns,
non empty { [Product].[High Level Product Name].members, [Product].[Mid Level Product Name].members,[Product].[Low Level Product Name].members, [Product].[Product Name].members } ON rows
FROM
[MyCube]

Thanks,

Lachlan

Hi,

I fixed my problem by changing my query to the following...

SELECT
non empty {[Measures].[Ytd Average Capital] } ON columns,
non empty {[Product].[Product Name].members} ON ROWS
FROM tacticaleva

Notice I now only select the lowest level product [Product].[ProductName] rather than selecting all of my product levels in one mdx query.

I then changed the measure expressions in the product group rows to use =Sum(Fields!Measures_Ytd_Average_Capital.Value) rather than just the measure without Sum(). The lowest level product is not in a group it's just in a row so I didnt need to use Sum(), I just used =Fields!Measures_Ytd_Average_Capital.Value.

I'm not sure why it works this way and not the first way that I tried it, but I'm new to both MDX and SSRS and still learning :)

Lachlan

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 in Cube's dataset, Please Help

have a measure called [Person COUNT] and an Attribute called [Injury Class]

Injury Class has four members [Injury A] [Injury B] [Injury c] [Injury d]
I want to get the Person count for each one individually, in seperate
columns. So it would look like this

[Person Count A] [Person Count b] [Person Count C] [Person Count d]
125 254 4 1245
441 11 21 24
454 57 12 42

I want this all in One single Dataset. So that when i put it into a table, i dont get errors about being outside the scope, also everything will be lined
up correctly. Please keep in mind, im new to mdx, i tried some code, but the
stuff i tried didnt work. Is there a way to do this as a calculated member?
how so? Anywa help will be gratelly appriciated. Thanks!You cant do this

Grouping data by x-axis labels

Hey guys,

I have thousands of records in the data source(i.e. Cube), which includes datetime information. I want to aggregate and present the data on a bar chart on monthly basis. That means the interval between the dates should be one month. Assume that the dates value will be labeled on the x-axis of the bar chart.

I tried to find out the solution for many days. I will really appreciate if anybody give me some idea.

Sincerely,

Amde

Try creating a category group with two expressions, using the following grouping expressions.

=Fields!DateField.Value.Year
=Fields!DateField.Value.Month

For the label you could use something like, =Fields!DateField.Value.ToShortDateString()

You could also use two category groups, if you wanted an inner set of labels for the month and another outer set for the year.|||

Hi,

Thank youy for your feedback, however that doesn't solve my problem: here is the thing;

I created a bar chart report. The x-axis value of this chart is a timestamp(datetime) field of a dimension. Thousands of records are inserted in to this field everyday, as a result, I will have the timestamp information every second or minute. So here is the thing, If for instance, I want to present last 5 months data in the bar chart(x-axis), the chart can not accomodate all the data and it doesn't look good to present a data which occured every minute or hours. So I want to present the data on monthly basis based on the StartDate and EndDate parameters value provided by the user.

Assume the user wants to preview 5 months record from 2006-03-04 to 2006-07-04, the data should be aggregated and presented on monthly basis as shown below, instead of directly displaying all the data as they appear in the dataset.

---

2006-03-04 2006-04-04 2006-05-04 2006-06-04 2006-07-04

Please let me know if you need more clarification.

Sincerely,

Amde

|||Adding the category fields with the groupings mentioned above should produce the grouping structure you are looking for. When you tried it what happened that was incorrect?

The bounds provided by the StartDate and EndDate parameters can either be used in the the sql query. Or, if it can't be done there, then you can set a filter for the category group. Also, the reason there is a group expression for Year is that the data may span multiple years and I'm assuming that you don't want the data for the same month in multiple years to be aggregated together.

Here is a sample report that uses the northwind database to show the number of orders placed for each month. It contains a bar chart and two parameters, which are used in the sql query.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=localhost; Initial Catalog=Northwind</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>40232364-d6a5-4917-bcad-13308e3a8f62</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="StartDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>7/1/1996</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Start Date</Prompt>
</ReportParameter>
<ReportParameter Name="EndDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>11/30/1996</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>End Date</Prompt>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Chart Name="chart1">
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Subtype>Plain</Subtype>
<Title />
<Height>5.125in</Height>
<CategoryAxis>
<Axis>
<Title />
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<PointWidth>0</PointWidth>
<Left>0.375in</Left>
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<DataSetName>Northwind</DataSetName>
<Top>0.125in</Top>
<PlotArea>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BackgroundColor>LightGrey</BackgroundColor>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
<Type>Bar</Type>
<Width>6.5in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!OrderDate.Value.Year</GroupExpression>
<GroupExpression>=Fields!OrderDate.Value.Month</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=MonthName(Fields!OrderDate.Value.Month)</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Count(Fields!OrderID.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
</Chart>
</ReportItems>
<Height>5.75in</Height>
</Body>
<rd:ReportID>6323408e-15e1-4a7f-8151-ac96e7ebf862</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="Northwind">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>="SELECT OrderDate, OrderID FROM Orders WHERE Orders.OrderDate BETWEEN '" & Parameters!StartDate.Value & "' AND '" & Parameters!EndDate.Value & "'"</CommandText>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="OrderDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>OrderDate</DataField>
</Field>
<Field Name="OrderID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>OrderID</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>9.375in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>|||

Yes, that is correct. The assumption, group expression for year is also correct.

Thank you for your cooperation.

sql

Grouping and Filters

Hi everyone

I am using SSRS2005 with an SSAS cube building in BI

I need to create a custom grouping. Here's what i mean:

I give my period parameter some default Values. Like :
Period = 200501,200502,200601,200602
Now when building the report, I filter 2 Tables on the 2 years respectively.
Grouped by Period

So one table list all the Measures for 2005 and the other for 2006.
Now I want to use a Chart to Display the two totals. I can only get the Chart to
display the by monthley periods. IE:

30 o
|--|-||
20 o o
|--|-||
10 o
|--|-||
200501 200502 200601 200602 (instead of 2005 and 2006 as I need)

I need to create a grouping by which i can tell the chart what data to use.
I can't group by period.year because the Period field is an Integer

Any help is greatly appreciated
If I am unclear about anything please point it out to me

Thanks in advance
Gerhard Davids

Ok

So I sorted this out and it seems I was being really retarded.

I used the following statments in the grouping of the Chart.

Series group : =iif(Left(CStr(Fields!Period.Value),4) = "2004", 2004, iif(Left(CStr(Fields!Period.Value),4) = "2005",2005,iif(Left(CStr(Fields!Period.Value),4) = "2006",2006,Nothing)))

Category group : =iif(Right(Cstr(Fields!Period.Value),2) = "01" ,01,iif(Right(Cstr(Fields!Period.Value),2) = "02",02,iif(Right(Cstr(Fields!Period.Value),2) = "03",03,iif(Right(Cstr(Fields!Period.Value),2) = "04",04,iif(Right(Cstr(Fields!Period.Value),2) = "05",05,iif(Right(Cstr(Fields!Period.Value),2) = "06",06,iif(Right(Cstr(Fields!Period.Value),2) = "07",07,iif(Right(Cstr(Fields!Period.Value),2) = "08",08,iif(Right(Cstr(Fields!Period.Value),2) = "09",09,iif(Right(Cstr(Fields!Period.Value),2) = "10",10,iif(Right(Cstr(Fields!Period.Value),2) = "11",11,iif(Right(Cstr(Fields!Period.Value),2) = "12",12,iif(Right(Cstr(Fields!Period.Value),2) = "13",13,Nothing)))))))))))))

This allowed it to group the periods together but seperate for each year
and in the series explanation it gave me the total for
each year respectiveley.

In the data section I then simply sumed my measure

G