D
Dwight Specht
All:
I am using the Analysis Cubes add-in for Excel 2003 to build out a
dashboard, so this is a free-form report.
Environment: SQL Server 2000 with Analysis Services. Using a prebuilt set
of Cubes from Microsoft related to the Microsoft Dynamics GP product, such
cubes collectively being called Analysis Services for Excel for Microsoft
Dynamics GP. Assume the cube design and structure is correct.
Problem1:
I can place dimensions into the report, but, if I refresh the report data or
connection, the field value in the field where the dimension is located goes
blank. Anyone seen this?
Problem2:
On the sheet I have one page filter on Week End Date
(=cubecellmember("GL-FINAC01", "Cal Week End".All Cal Week End.01 Jan
2007")).
What I want to do is display 4 columns. The first is the same as the week
end date. The next 3 are each one week apart. Ex:
Week End Date: 01/01/2007
A B C D
01/1/2007 01/07/07 01/14/07 01/21/07
I have a dimension in the GL Cube called CLWeekEnd that I added from the
MasterDate table. That is what is being chosen by the page filter. In each
column, I want to list the CellMember as the value as follows:
Col A: =cubecellmember("GL-FINAC01", "Cal Week End".All Cal Week End.01 Jan
2007")
Col B: =cubecellmember("GL-FINAC01", "Cal Week End".All Cal Week End.07 Jan
2007")
Col C: =cubecellmember("GL-FINAC01", "Cal Week End".All Cal Week End.14 Jan
2007")
Col D: =cubecellmember("GL-FINAC01", "Cal Week End".All Cal Week End.21 Jan
2007")
However, I want these formulas to update dynamically based on the page
filter. So, I tried this:
Col A: =cubecellmember("GL-FINAC01", "Cal Week End".All Cal Week
End."&TEXT(A1,"DD MMM YYYY")). A1 is the cell that the page filter is on.
This works fine when I enter it. However, the minute I refresh the data or
connection, it either goes blank or it shows #Value. If I type it in
exactly, I can get it to work but it fails when I refresh the data.
Put simply: How do I make CubeCellMember dynamic based on a page filter and
how do I stop dimensions from going blank on refresh?
Thanks in advance for the help.
Dwight
--
I am using the Analysis Cubes add-in for Excel 2003 to build out a
dashboard, so this is a free-form report.
Environment: SQL Server 2000 with Analysis Services. Using a prebuilt set
of Cubes from Microsoft related to the Microsoft Dynamics GP product, such
cubes collectively being called Analysis Services for Excel for Microsoft
Dynamics GP. Assume the cube design and structure is correct.
Problem1:
I can place dimensions into the report, but, if I refresh the report data or
connection, the field value in the field where the dimension is located goes
blank. Anyone seen this?
Problem2:
On the sheet I have one page filter on Week End Date
(=cubecellmember("GL-FINAC01", "Cal Week End".All Cal Week End.01 Jan
2007")).
What I want to do is display 4 columns. The first is the same as the week
end date. The next 3 are each one week apart. Ex:
Week End Date: 01/01/2007
A B C D
01/1/2007 01/07/07 01/14/07 01/21/07
I have a dimension in the GL Cube called CLWeekEnd that I added from the
MasterDate table. That is what is being chosen by the page filter. In each
column, I want to list the CellMember as the value as follows:
Col A: =cubecellmember("GL-FINAC01", "Cal Week End".All Cal Week End.01 Jan
2007")
Col B: =cubecellmember("GL-FINAC01", "Cal Week End".All Cal Week End.07 Jan
2007")
Col C: =cubecellmember("GL-FINAC01", "Cal Week End".All Cal Week End.14 Jan
2007")
Col D: =cubecellmember("GL-FINAC01", "Cal Week End".All Cal Week End.21 Jan
2007")
However, I want these formulas to update dynamically based on the page
filter. So, I tried this:
Col A: =cubecellmember("GL-FINAC01", "Cal Week End".All Cal Week
End."&TEXT(A1,"DD MMM YYYY")). A1 is the cell that the page filter is on.
This works fine when I enter it. However, the minute I refresh the data or
connection, it either goes blank or it shows #Value. If I type it in
exactly, I can get it to work but it fails when I refresh the data.
Put simply: How do I make CubeCellMember dynamic based on a page filter and
how do I stop dimensions from going blank on refresh?
Thanks in advance for the help.
Dwight
--