March 27, 2007 at 3:14 am
Guys,
I need your help on this, so please if you know the answer post me back.
I have a cube that shows courses and tests. Each course, may have a number of different test that the students write. Some of the tests have not been used by the university (lets say this year) however they exist in the dimension test. My problem is this.
I take the average mark of each test, and if average is greater lets say than 70%, i want to mark this test as "high", if the average is less than 50% i mark this test as low, and everything that falls in between is not marked, neither high, nor low. Also, i do not wish tests that have not been used to appear in the cube. Obviously, i do not want in the cube (that is viewed through excel, by the professors) to display the tests that have not been used in the university yet. My problem is that the command
iif (avg(test)>70, 1, iif (avg(test)<50,2,null) works fine. By fine i mean that in drilling down in the excel it does not appear those tests that have not been used from the univerisity and therefore are empty. However if i try to make this like iif (avg(test)>70, "HIGH", iif (avg(test)<50 ,"LOW" ,null) in order to make the calculate member display values "HIGH" or "LOW", the command does not work because of mixing strings ("HIGH", "LOW") and null. So if i try substituting null with the space " " , iif (avg(test)>70, "HIGH", iif (avg(test)<50 ,"LOW" ," "), the cube makes all tests appear even though they have not been used by the university.
This creates an extensively large sheet in excel which is unreadable. Imagine that a course may have 70 different test, but lets say this year we have used only 3-4 of them. The excel now displays all 70 test regardless if they have an avg or not, since it puts the space " ", to all tests that have not been marked as high or low. How can i make the unused tests, not appear in the cube (excel sheet?). What mdx command should i use?
any ideas, would be great help,
DF
March 28, 2007 at 8:21 pm
Assuming you're using a Pivot Table, there's a few options in Excel for handling empty cells. Try getting Field Settings and uncheck the "show items with no data" field. Under Table Options there's a "Show empty cells as: " option which may help too.
March 29, 2007 at 1:05 am
March 29, 2007 at 1:33 am
Sorry to harp on about Excel again, but would it help to use an empty string ("") instead of space (" ") in your IIF function?
I don't know much about MDX, but if I come across anything I'll let you know.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply