January 20, 2015 at 11:00 am #189
Something that came up during a discussion with a customer and is an interesting technique is the ability to show Inventory levels in a Stratum Viewer view while filtered by Salesperson and/or customer. In a general sense this is difficult because usually inventory does not include either the salesperson or customer dimension. So if either of these is include in the filter or drill down the inventory numbers go to null. There are two ways you can get around this (that I know of, could be others:) ). The first is to use the hyper link concept and link to a view that shows inventory, you could pass the item/sku number in the link to show inventory for the specific item/sku displayed a specific row. The other option is to use the following calc to ignore the customer and/or salesperson dimensions and show info for just the item/sku or in my case product that is displayed on the row.
So the idea is that you have a View filtered by customer (in my example) or rep showing products on order. To display the inventory you can use a calculation that ignores the filter on the customer level (this could be any dimension in your version of Stratum, it just happens to be customer in mine). This calculations does 3 things. First if verifies that there is actual data on the row so that we only display do the calc rows that have data, second it verifies we are looking at the product level since we need to have the product level to see the inventory and last is where it actual gets the inventory value while ignoring the customer value.
This is my example calc – use at your own risk:)
//Only do the calc if data exists for the row
Iif([Measures].[Data2 (Open Orders Amount)]=null AND [Measures].[Data1 (Open Orders)] = null,null,
//If the product is null then we aren’t at the product level so don’t try to find the onhand
iif([Product].[Product].CurrentMember IS [Product].[Product].DefaultMember, NULL,
//Ignore the customer dimension
([Customer SoldTo].[Customer SoldTo].DefaultMember, [Measures].[Data12 (Onhand)])))
As I mentioned earlier the discussion I had was around salesperson and being able to see inventory, but the same concept appliers, you would just need to ignore the salesperson dimension either along with or instead of the customer dimension.
The fun you can have with Stratum!!!
January 20, 2015 at 12:15 pm #190Jeff PetersonParticipant
- Topics Started - 1
- Replies Written - 8
Oooh wow.. That’s a sneaky way of utilizing MDX in Viewer. 🙂
That question has commonly come up. I want to see how much inventory is on hand next to the customer’s open order quantity measure, but On hand inventory has *NOTHING* to do with customer.
I hope this comes up again!
I know that Planner is old school, but people have often asked, “Can I embed SQL in a calculation?”
ie: SELECT qtyOnHand FROM mySchema.myInventoryFile WHERE partNo = <dim1value>..
Intesresting concept, except users could really beat up the system.
February 20, 2015 at 3:15 pm #203
Jeff – I don’t think you can imbed SQL into a Planner calc. You can use the ProfileString calc (if I remember correctly) to grab data from a text table that lives outside of Stratum. We’ve used this to grab things like the work days in a year, month etc. I supposed you could output the on hand to a text file and go from there. A bit messy but it could work.
September 18, 2015 at 10:58 am #266
Discovered an interesting aspect of an Excel pivot table directly attached Stratum. When looking at data filtered by a dimension that is not included in the category (measure group) that contains the data (which would be Inventory Units in the example previously in this post) and showing rows of data that contain a dimension that is in the category containing the measure (Inventory) Excel will show the data, and does not take the filter for the dimension data that is not part of the category into account. So no special calc needed. This is cool in the case described here, but could cause someone to see data at a level they should not be when they don’t understand that the filter they’ve applied is not being applied to one of the measures they are looking at.
You must be logged in to reply to this topic.