Does anyone know the proper syntax to use or have an example I can work off of?
I want to use SHOW TO BOTTOM. I take it you have to specify a field value and SHOW TO BOTTOM off of that. The link talks about using a WHEN filter, which i am not familiar with.
would it be something like:
TABLE FILE DM
SUM
Numbers
BY GL_Account_Finance_Tree_name HIERARCHY WHEN GL_Account_Finance_Tree_name EQ 'Revenue' SHOW TO BOTTOM
BY Date_Calendar_Month NOPRINT
Thanks!This message has been edited. Last edited by: Nicholas Spyrison,
_____ WF 8.1.04 Win 7// Windows Server 2012 R2 SASS OLAP Cube
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
November 05, 2015, 02:23 PM
Nicholas Spyrison
well, that's embarrassing.
But now I am getting an the following errors:
(FOC11207) ERROR IN THE QUERY:
(FOC11309) "IF" AND "WHERE" TESTS DO NOT COMBINE WITH "BY HIERARCHY": [GL Account].[Finance Tree]
Documentation code:
BY PRODUCTS_CAPTION HIERARCHY
WHEN PRODUCTS_CAPTION EQ 'LAPTOP';
My code:
BY GL_Account_Finance_Tree_name HIERARCHY
WHEN GL_Account_Finance_Tree_name EQ 'Total Property Revenues';
Error really seems to contradict the the whole idea of the documentation.
any thoughts?
_____ WF 8.1.04 Win 7// Windows Server 2012 R2 SASS OLAP Cube
November 05, 2015, 03:06 PM
MartinY
The only thing that I can think of it's that your TABLE FILE xxxxxxx is not requesting against a cube.
quote:
When you issue a request against a cube, some of the requirements and features available depend on the type of hierarchy you are reporting against.
Maybe someone else will have a different answer.
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
November 05, 2015, 04:12 PM
Nicholas Spyrison
quote:
The only thing that I can think of it's that your TABLE FILE xxxxxxx is not requesting against a cube.
it's against a cube, parent-child, BY GL_Account_Finance_Tree_name HIERARCHY works as expected without the WHEN.
_____ WF 8.1.04 Win 7// Windows Server 2012 R2 SASS OLAP Cube
November 05, 2015, 04:27 PM
Francis Mariani
Does the WF metadata for the cube table have the appropriate keywords to identify the hierarchy? Was the WF metadata (master, access) created using the reporting server create sysnonym option?
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
_____ WF 8.1.04 Win 7// Windows Server 2012 R2 SASS OLAP Cube
November 09, 2015, 12:39 PM
Nicholas Spyrison
I have DEFINE using IF GL_Account_Finance_Tree_name and a WHERE GL_Account_Finance_Tree_name. This alone opperates just fine.
If I add the WHEN then I get the (FOC11309) "IF" AND "WHERE" TESTS DO NOT COMBINE WITH "BY HIERARCHY". I can then comment out the DEFINE and WHERE and have the WHEN run correctly.
It seems like you can either have (IF and WHERE) or WHEN, but not both...
___ Edit: I can use IF, WHERE TOTAL, and WHEN together.This message has been edited. Last edited by: Nicholas Spyrison,
_____ WF 8.1.04 Win 7// Windows Server 2012 R2 SASS OLAP Cube
November 09, 2015, 01:04 PM
Francis Mariani
Good to know there's a decent solution to this.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server