Member Formating Problem - Query & Analysis(Archived)

My essbase cube have five dimensionsFullYear(Time)Scenario Act Bud Measures(Accounts only) 0001 ProdLocation 500 600ActivityI have to create a report using Analyzer as 0001 Prod Act Bud Act Bud500 100 100 10.5 11.5600 200 200 12.5 9.5Here i want values corresponding to "0001" as whole numbers and values corresponding "prod" as to one decimal place accuracy.Right now I can able to format data using the Scenario members i.e either Act or Bud but i can not able to format using the Measures dimension i.e 0001 or Prod in the analyzer.I can able to set this formating In analyzer by using right click on the view->member formating menu item only for Scenario dimension. Even if change this setting for Measures dimension members, it is changing the format.Actually i want to set this formating option for the Measures dimension members.I don't why i can't able to do this. Any body come across this type of problem. Any help will really appreciated.Thanks in advance 

Go to Hyperion Analyzer Administration Tools->Manage->right click in the database->choose properties->choose the dimension->formatting->choose the members and start formatting as you need.Go dimension by dimension and member by member. By the way, if many members have the same formatting needs just use the shift key go down to all member needed and apply the formatting and all of them will be formated in one shot.Hope this helps


Can I simulate subtotal by Calc script?

Since there is no subtotal in Essbase, I wonder can subtotal be simulated by Calc Scrip if use BSO.
As far as I know, there are may other BI solutions which could provide subtotal.
It is very unfortunate that Essbase does not provide subtotal. 
what is mean by subtotal?
can you please through more light on this? 
Suppose, we have a dimension which has two levels.
In level 1, there is only one member. In level 0, there are five members.
If a user select all the five members in the dimension, Essbase coulbe correctly calculate the total.
However, if the users only have interest in three of them. Essbase would not show total of the three members.
Instead, Essbase would still show total of all members. 
Have you looked at the function #SUMRANGE. This function works in the same way as you are asking. It sums for a range of members, not all members
"First Half"=#SUMRANGE("USA",Jan:Jun);
This will give the USA value in "First Half" and not complete YearTotal. YearTotal can have all 12 months..
"3 Members" = #SUMRANGE(B,Prod1:Prod3)
This will add first 3 members, though the parent had 5 members, as in your example
Let me know if it helps.
First understand essbase is a server technology, it should be designed in suchway that will support the common calculation and reporting requirements.
Before implementing essbase you need to identify the common query patterns and incorporate the functionality required to support that.
you are asking about the subtotals, if the members defined for the subtotals are the groupings defined by a specific functinality or attributes.
You can define a attribute dimensions and can assign the attributes to these base members.
Then if you query the cube by the attributes by default you will get the sum of all base members(Subtotal which you are expecting).
If it is not the case and you are not in a position to predefine any thing and you want subtotals based on the result sets of dynmic querys.
Then It is better to try it out in the HFR like client side.
In the HFR you can insert a Formula row and can calculate the total based on the results.

Data forms new requirement, Members need to hide for diff combination

Hi All,
I have a new requirement
I have created asymmetric forms and created a extra member in period dimension as per required.(Extra member name is ABC)
In layout page view i have given scenario dimension with members called budget,Actual,Forecast.
now when open data form combination to budget scenario Extra member has to appear,
if you select Forecast Extra members should not appear in the form.
am in mess how to achieve this requirement can any one help me out in acheiving this requirement.
Thanks in advance
Assuming you have accounts on the rows and periods on the columns, try this solution:
Create two account members called RowSuppresser (store) and RowSuppresserDyn (dynamic). Retrieve RowSuppresser in Excel combined with ABC (period) and Budget (scenario) along with one default member from all other dimension (i.e. no entity, no year etc). input a flag amount (0 or 1) and lock and send to Essbase. Member formula in RowSuppresserDyn should point to this particular input (i.e. RowSuppresser->"No Year"->"No Entity" etc.
Add a row on top of all other rows in your form and select (RowSuppresserDyn) account member. You can even hide this row if you wish. In the column where you added ABC period, enable suppress missing data. So when Budget is selected, this column appears, otherwise is gone.

Need all members except one

We need a report in FRS (for planning app) with all account members except one member (say XYZ), this dim is in page dim. Since we want the report to be dynamic, we tried few things:
1. Tried using All Accounts AND NOT XYZ
2. Created a UDA with all account members except XYZ. Used UDA in the report and it does not show up the right result. It shows only one page for each account.
Any suggestions will be helpful either in any of the above options or something new.
What exactly is the requirement? Do you want a total of all accounts excluding one or do you want to display all accounts excluding one?
What you are describing with the Page dimension is the way that the page is intended to work. If you want:
1. Subtotal of all accounts except one
a. Either add this member as a dyn calc member in the outline and call it directly or
b. Call the overall total and the account you wish to exclude and use a formula in the report to subtract one from the other
2. Display all accounts except one
a. Move what you have on the page of the report into rows (I suspect it may be too large to fit in columns)
Hope this helps

Turning off report based consolidation!

I'm creating a dashboard that uses Essbase as the backend data storage. The Essbase member being reported on is a calculated percentage. I have created an analysis table with a hierarchical column that can be drilled. At the top of the hierarchy the value for the essbase member is being consolidated from the values below it in the report. Because it is actually a calculated percentage, this functionality is incorrect. In Essbase the percentage for this top level member is calculated correctly and is not consolidated through the hierarchy.
Basically, I don't want the hierarchy members in the report to aggregate in OBI; I want them them to directly read the values directly from Essbase.
i have tried going in to column properties and changing consolidation to server based, but this does not work.
Any help would be very appreciated.

HFR member selection

Hi Experts,Can I get members on a grid based upon a member on other grid in same report. I want to pull data for trail balance accounts along with some historic accounts associated with TB accounts. I want to show corresponding accounts  data together in row.
Hi, First of all whether both the grids are pointing to the same cube? If yes then you can do it easily. If the cubes are different for different cubes then you need to make two grids with different connection setting, so that they can connect to the respective grids. Now to answer your query, you can hide the primary grids and create a third grid and put the cell reference for the Row/Column of Grid 1 & 2 as per your requirements. I guess it is possible because you can see functions like <MemberName>, which you can use to retrieve your set of accounts which you are looking for. Soumya
Hi SoumyaThanks for your reply. I am making HFM reports. I will try to elaborate my question here. lets say I have an account 12345678 i want to show it in a report. along with this I also want to show another account AA12345678. pls tell me how it can be achieved. I want to show data for both accounts either in a single row or in consecutive rows. like that for many accounts. hope I am clear now
Hi, I guess you are making reports in Hyperion Financial Reporting. To capture your requirement you need to insert a data row in a HFR grid & you have to specify the account/accounts in that row. Like if you want to show two accounts you can take two different rows and specify the accounts you want to show, also you can add these two accounts in a single row separated by a coma like below:-  Row1  12345678 Row2     AA12345678          ORRow1  12345678, AA12345678 Soumya
but that will be a manual process, I have a very big list of accounts.any other method?  or can it be dynamic?
Hi, It can be dynamic as well. For that you need to select the parent level member in a single row and then select the Descendants of that parent in the second row, so that if there will be any addition of members under the parent level member then it will automatically include that member in your report. Soumya
The report must have base members only, sorry if my understanding to ur reply is incorrect. I want all TB base members in report. Each base member should be followed by AA***** accounts.
Hi, You can take all the base level members of TB and then you can apply UDA for the members which will followed by AA*****. Soumya
I think this will first populate all TB members and than AA**** but my requirement is one TB member then AA**** member than next TB member and CORRESPONDING AA*** member. 
Hi, I guess then you can apply selecting the TB member (11232345) first and then assign an UDA (like TB MBR 11232345) to the corresponding members and show the same in the second row. Hope that will help you. Soumya