conditional grouping (to group or not to group)

I have 3 groups: region, district, facility.
Depending upon user input, I want to either display the data in a drilldown
such as:
region 1
district 1
facility A data data data
facility C data data data
facility F data data data
district 2
facility B
facility D
...etc
or I want to only display the facility data without the drilldown and
without displaying which region and district those facilities belong to.
Obviously, this could be done by using 2 separate reports, but I need to have
this functionality for all of my reports. So if there is a simple way to
allow for it in the same report, I would like to do that.
ThanksThe closest you can get is a conditional grouping expression like this:
=iif(Parameters!GroupOnRegion.Value = True, Fields!Region.Value, 1)
Note: Grouping on a constant value will just generate 1 group that contains
all values.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Stads" <Stads@.discussions.microsoft.com> wrote in message
news:CC17E391-B188-492F-980E-C8F3B4298F97@.microsoft.com...
> I have 3 groups: region, district, facility.
> Depending upon user input, I want to either display the data in a
drilldown
> such as:
> region 1
> district 1
> facility A data data data
> facility C data data data
> facility F data data data
> district 2
> facility B
> facility D
> ...etc
> or I want to only display the facility data without the drilldown and
> without displaying which region and district those facilities belong to.
> Obviously, this could be done by using 2 separate reports, but I need to
have
> this functionality for all of my reports. So if there is a simple way to
> allow for it in the same report, I would like to do that.
> Thanks
>

Conception of my cube

Hello,
During my internship, I had to create a cube for sales based on only one dB (of an ERP).
So I've created a view grouping information I need.
So I've only one "table" (a view actually) which is my fact table and my only dimension table.
Actually, the dimension are the attributes of my sole dimesion.

The problem is that I've to write my report and I don't know if I've made the good choice. I can also create a dimension table with Customer, for countries ... but the result would be the same.

Thanks in advance for your advices.

Hey there,

If you are happy with the performance, the SQL is maintainable and you will only ever have one cube for your company, then your solution will be adequate.

However, you may find that you run into performance, scalability and maintenance issues as your data grows and if you need to start working with other business processes (e.g. order processing or invoicing).

By having a single view you are bypassing some of the automated work a cube / Analysis Services can give you. Also with the view, you may end up replicating some logic for bringing back dimension values (e.g. country). If you were to have another view for a new cube, you would have to replicate all the SQL for extracting the country information to return in your flattened view.

By using dedicated dimension tables, you will gain re-use of dimension content (conformed dimensions), requiring only a single extract from the source system.

There are numerous other benefits of having a more structured data warehouse/data extract process, such as change tracking, referential integrity checking, introduction of surrogate keys (non source system dependent keys if you are dealing with multiple data sources).

Hope that made sense,

Jonathon

Concatenating a field while grouping records

All,

Given multiple records with identical values in all fields except a
single varchar field, is there an efficient query that will group the
records into a single record and concatenate the aforementioned
varchar field into a single field with each of the source records'
values separated by commas?

Example:
Record 1 'Doug' , '1'
Record 2 'Doug' , '2'

Output record 'Doug' , '1,2'

Thanks in advance,
DougSELECT col1,
MIN(CASE seq WHEN 1 THEN col2 END)+
COALESCE(', '+MIN(CASE seq WHEN 2 THEN col2 END),'')+
COALESCE(', '+MIN(CASE seq WHEN 3 THEN col2 END),'')+
COALESCE(', '+MIN(CASE seq WHEN 4 THEN col2 END),'')+
COALESCE(', '+MIN(CASE seq WHEN 5 THEN col2 END),'')
FROM
(SELECT S1.col1, S2.col2, COUNT(*) AS seq
FROM Sometable AS S1
JOIN Sometable AS S2
ON S1.col1 = S2.col1
AND S1.col2 <= S2.col2
GROUP BY S1.col1, S2.col2) AS X
GROUP BY col1

--
David Portas
----
Please reply only to the newsgroup
--|||This is trivial with the RAC utility for S2k.
No cursors,no complicated code and no hassles.

More info @.
http://www.rac4sql.net/onlinehelp.asp?topic=236

RAC v2.2 and QALite released.
www.rac4sql.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!