How to Dynamically Report on Extended Members from the Top Consolidation Cube?
Depending on the circumstances, you could find yourself into a situation where you need to report numbers on Extended dimension Members from the Top Consolidation Cube. In a Vertical Extensibility Cube set up this gets tricky since the data is only available at the lower cube levels. To work-around this challenge (considering the limitations exposed below), I have created a dynamic calculation Business Rule that allows you to achieve that by leveraging a specific UD8 Dynamic calculation Member. Following it is an overview of the Solution: Problem Statement: When using Cube Vertical Extensibility, reporting from Tot Group cube is not possible for those dimensions that have been extended. In the example, UD1 is only available at Summary Level for Tot Group: Solution Overview: UD8 Dynamic Calculation to derive the Extended members from the Base Entities consolidating to the Parent Entity Level (E#Tot Group). Data is dynamically retrieved from the base entities and translated on the fly based on the Parent/Child Entity relationships: Works with any extended Account Type Dimension (A#, F#, U1#-U7#) without having to modify the Business Rule. Dynamic Calculation driven from the UD8 Text1 field: Considerations: Solution aggregates and translates on the fly the base data without: Eliminations % of Consolidation (It could potentially be incorporated into the solution) Suitable for reporting/analysis being done at O#BeforeElim. When used in large Cube View/Quick Views queries could create slow retrieval performance. Technical Set Up: Create the UD8 Member and Define the Text 1 property referencing the Dimension Tag you’d like to report on (Ie: DimTag=[U1#]). Add the following Member Formula within the UD8 Member: Dim referenceBR As New OneStream.BusinessRule.Finance.XF_DynamicExtensibilityReporting.MainClass 'Return referenceBR.DynamicProductReporting(si, globals, api) Dim Value As Decimal = referenceBR.DynamicExtensibilityReporting(si, globals, api) If Not Value.Equals(0) Return Value Else Return Nothing End If Create a new Finance Business Rule, make sure that the Business Rule is set to Global:Microsoft Power BI vs. Excel: A Comparison
Power BI is Not Excel 😊 Microsoft Power BI vs. Excel: A Hilariously Technical Showdown Purpose and Functionality: Think of Microsoft Power BI as the flashy superhero of the data world, designed to turn boring numbers into eye-popping, interactive dashboards and reports. It’s the show-off cousin who just got a sports car. Conversely, Excel is the dependable, slightly nerdy sibling who’s been around forever, excelling (pun intended) at data entry, analysis, and complex calculations. The trusty old sedan has seen it all and done it all. Data Handling: Power BI is like a data-hungry beast that devours vast amounts of information from various sources – clouds, databases, and even the web's dark corners. It’s all about real-time munching and crunching. Excel? Well, it's the meticulous librarian of data, handling large datasets with care but getting frazzled if you dump a truckload of info on it simultaneously. It’s great for detailed work, but it prefers its data to be served in manageable portions. Visualization and Reporting: Power BI is the Picasso of data visualization – dragging, dropping, and creating interactive masterpieces that make you go, “Wow!” While competent with charts and graphs, Excel is more like your high school art class. It can make things look good, but it’s not winning any gallery showings. Power BI’s dashboards are so dynamic that they jog when you click on them. Collaboration and Sharing: Power BI is the social butterfly at the party, effortlessly sharing dashboards and reports through Power BI Service, embedding them into apps, and updating them in real time. It’s always up-to-date and ready to mingle on your phone. Excel, bless its heart, tries its best with Office 365 sharing and collaborative editing, but sometimes it feels like sending out invitations via snail mail. Advanced Analytics: Power BI has been hitting the gym with AI and machine learning, flexing its muscles to uncover patterns and predict trends. It even shows off with R and Python integrations. With its powerful functions and add-ins like Power Query and Power Pivot, Excel is like the seasoned chess player – brilliant at what it does but not ready to jump into the AI and ML ring without serious prep. Conclusion: Power BI is the flashy, tech-savvy hero in this epic battle, ideal for real-time visualization, advanced analytics, and collaborative reporting. Excel remains the stalwart, detail-oriented veteran perfect for intricate data analysis and spreadsheet wizardry. Both tools have unique charms and are best used to make your data journey as entertaining and efficient as possible. So, why choose one superhero when you can have the whole team? Coming soon: Power BI OneStream Connector.Enhance your OneStream experience with Emojis!
Integrating emojis into OneStream workflows, like :memo: for forms and :crystal_ball: for forecasts, enhances clarity and user engagement. Simple to set up, just copy and paste your chosen emoji into workflow titles for an instant visual upgrade. Interested in setting this up in OneStream? Dive into the world of emojis and elevate your interface today! :rocket:
Bridge Origin Import to BeforeElim/Top with journal entry details
Hello! A user has requested a CV/report that starts with imported data and layers on journal entry details by each specific JE. High level it would look something like this with the pink highlights representing the detailed balances of each individual journal entry: Have any of you built something similar? Is there an easy way to get the journal entry name details into a CV? I’m guessing I can get them from the JournalHeader and JournalLineItem database tables but wondering if you’ve ever worked on a request like this. I've also submitted a support ticket so we'll see. Thanks!
Stop Force Consolidate in Excel
I am stopping users from running forced calculate/translate/consolidate with the following in "WcfEventHandler". If args.IsBeforeEvent AndAlso (args.WcfServiceType = WcfServiceType.Calculate) AndAlso (args.OperationName="CalculateChartLogic" OrElse args.OperationName= "StartConsolidation" OrElse args.OperationName= "StartProcessCube" OrElse args.OperationName= "Translate" ) Then Dim isForced As Boolean = args.inputs(2) If isForced = "True" Then args.Cancel = True Throw New XFException(si, New Exception("You do not have access to Force Calculate/Translate/Consolidate.")) End If End If How do I stop users from running these functions in a report in excel?
How can I retrieve in a cube view the assigned workflow for every entity ?
Hi, A question that come back rather often is how to retrieve in a "user friendly" format the workflow an entity is assigned to. You can find hereunder a dynamiccalc you can assign to an UD8 in order to retrieve that information. Hope this helps. Kind regards, Aymar 'This dynamiccalc member will show the workflow profile of the Entity as an Annotation 'User need to have access to the CubeRoot workflow profile Info If api.View.IsAnnotationType() Then Dim sourceSQL As New Text.StringBuilder Dim returnedText As New Text.StringBuilder Dim wfCubeRootInfo As WorkflowCubeRootInfo = TryCast(BRApi.Workflow.Metadata.GetCubeRootInfo(si, BRApi.Workflow.General.GetWorkflowUnitPk(si).ProfileKey, False),WorkflowCubeRootInfo) If Not wfCubeRootInfo Is Nothing Then sourceSQL.Append("SELECT [EntityMemberID]") sourceSQL.AppendLine(",[ProfileName]") sourceSQL.AppendLine("FROM [WorkflowProfileEntities]") sourceSQL.AppendLine("INNER Join [WorkflowProfileHierarchy] On [WorkflowProfileKey] = [ProfileKey]") sourceSQL.AppendLine("WHERE CubeRootProfileKey = '" & wfCubeRootInfo.CubeRootProfile.UniqueID.ToString & "'") Dim dt As DataTable = api.Functions.GetCustomBlendDataTable(BlendCacheLevelTypes.Custom, "entity_WFPAssignment", "Application", sourceSQL.ToString) If dt.Select("EntityMemberID = " & api.Pov.Entity.MemberId).Count() > 0 Then For Each dtRow In dt.Select("EntityMemberID = " & api.Pov.Entity.MemberId) returnedText.Append(dtRow(1).ToString).AppendLine Next Return returnedText.ToString Else Return "Not Assigned" End If End If Else 'Return a NoData Datacell Return api.Data.CreateDataCellObject(0.0,True, False) End If
SQL query to retrieve base members of a hierarchy
Hi, All Just wanted to share a SQL query I wrote to retrieve base members and parent of a given hierarchy from within a dashboard grid. (Note that the parameter supplies the member ID of the top member in the chosen hierarchy.) Any thoughts on how to improve are welcome! With TEST AS (SELECT childid, parentID, dimtypeid, 0 AS level FROM relationship WHERE parentid = |!SelectDimension!| UNION ALL SELECT ft.childid, ft.parentID, ft.dimtypeID, level + 1 FROM relationship ft JOIN TEST d ON ft.parentid = d.childid ) SELECT m.name As Child_Name, m.description as Child_Description, P.name AS Parent_Name, Description as Parent_Description FROM TEST d JOIN relationship a ON d.parentid = a.parentid JOIN member AS M ON a.childID = m.memberid JOIN member as P on d.parentid = p.memberid where NOT EXISTS ( SELECT NULL FROM TEST d WHERE d.parentID = m.memberID ) group by m.name, m.description, P.name, p.description
