February 8, 2006 at 10:34 am
I am trying to learn MSAS 2000, and have become confused when it comes to drillthroughs. I have a cube in which I have measures that represent periods for different accounting fields (i.e.: Expenses 0-15 Days, 16-30 Days, etc.). I also have calculated members that simply total these values, like "Total Expenses".
My goal is to create a way for users to view the calculated members when creating reports, and then view the detailed measures via drillthrough. For instance, an accountant may want to view the total expenses for all active projects, and then click on the total expenses cell to see the breakdown for that particular project.
If anyone understands what I am trying to do or can provide any useful links I would greatly appreciate it.
Thanks in advance
February 9, 2006 at 3:12 am
I haven't worked with accounting much but maybe something like this could work:
Make type of time dimension with a hierarchy something like this:
All Time
0 - 15 Days
15 - 30 Days
etc
Put the expenses in a single measure and then you can slice the measure using the time dimension
This probably means redesigning quite a lot of your etl and cube. It looks like you want to drill down on one measure and return another measure - not sure if it can be done.
February 9, 2006 at 4:07 pm
In the cube designer you can select which columns are returned for the drill through queries (this is a bit annoying because it means that you either don't supply enough for the end user, or too many i.e. they don't get to choose what they want).
re: drilling on your calculated member, you can't do this. Depending on what tool you're using in the front end, it may use a different 'real' measure to allow you, but in general, drilling on calc measures is not supported.
Steve.
February 9, 2006 at 5:42 pm
Thanks for the help Steve and Grasshopper.
Now that I've installed 2005, any new suggestions, or should I just bite the bullet and start playing with new design options?
Thanks and TIA,
Chris
February 9, 2006 at 6:28 pm
2005 has changed the way drill thru works. It could be argued that it isn't really drill thru anymore. One of the guys from the AS team published a doco/whitepaper re: drill through in 2K5 that you should try to take a look at. Basic take-aways are that drilling through is now an action (like any other) and that the data must all exist within the UDM, so in essence it's drilling down/thru to the records as they exist within the cube/udm. What this does do tho is ensure that the security isn't compromised - AS2K would return all of the drill throughcolumns, so say your role locked you out of seeing 'cost', if it was a col in the drill thru setup, you'd get it back.
You could (in theory, i haven't tried this yet) drill thru to the source (like before) by using an action to kick of an RS report that accepts parms. You'd obviously have to make your action pass the parms to the report.
HTH,
Steve.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply