Hi all,
I'm a newbie and I'm exercising with an ecommerce scenario.
In my data model I have an Orders table where is stored the zip code of the destination city of the products related to a particular order.
I'd like know how many orders are for a city and then, after a dimensional modeling of my data, drilling down over a zip code and know how many orders are for the selected zip code.
So, I would like perform a dynamic row count based on a particular zip code.
Before, I group my data for city e then for zip code with an having clause on the selected zip code.
Does anybody can tell me how can I do this with FM? Or is this functionality provided with Report Studio/Query Studio/Analysis Studio?
Thanks in advance
Regards
ops...I read the post "count distinct fact" and it seems very similar to my post.
If it is true, I'm sorry...
Even if in that post, the user use a "static" row count while I would like a row count based on a particular group by clause.
I can perform this with the same advices written in that post?
Quite easy with Report Studio, ? in Query Studio and not possible in Analysis Studio.
Personally I would define a seperate view or Query subject that does the distinct counting for you in order to use it in all Studio's
I agree with Blom, it would be best to model it in framework manager. You can acheive this type of aggregation in Report Studio or Framework Manager by using an argument on the aggregate function (count in your case). The argument you want to us is the 'FOR' argument. This allows you to perform an aggregate function at a different grain.
For example:
count([order_id] FOR [city])
count([order_id] FOR [ZIP])
Thanks so much for your replies.
I have obtained the result using the aggregation property in FM.
So, I added a measure "number of orders" in my fact table as suggested in another recent post.
In the future, I'll read with more attention previous post ::)
Regards