Article shows how relatively "easy" to provide records in a table comparing the facts and show differences between them. Data Model (simplified): Table of facts: a column in January, February, ... December, at the level of business model Resolved totals for quarters (Q1 to Q4 ) and the total sum. Dimension: Release schedule plan is created for a year, each version refine it based on new facts or existing fact. The report has the following format: The task is to create a similar report that displays data from 2 versions; user selects and displays the differences between them. Preferably without having to interfere in the business layer and make do with only the client part of Oracle BI. This result looks like: Here's how: Prompt: after selecting the values of both versions of set variables (eg, v1, v2) Answer: we do a combination of two questions First question we normally (basically a copy of the basic Antero - see first picture). The only restriction is non-standard query using variables v1 and v2) The second question we like the first, it is omitted in column version, instead we give a constant (eg) 'Change', a text that will appear.Conditions for selection are the same as the preceding one: Instead of the original column values January, February, ... is now a formula, eg for January: SUM (CASE WHEN tB_form_Consol.Verze = '@ {v2} {xx}' THEN -tB_form_Consol.leden/1000 END ELSE tB_form_Consol.leden/1000) (in other words, when version = v2, then turn around and then sign all added up, then get the difference between the value of v1 and v2) Graphic design: coloring and highlighting differential values - like this (Properties Columns -> conditional formatting) Finally, a few tricks that are used when creating reports: 1st Answer: The 17 columns that have a very similar pattern - it can easily be edited in XML format in the Advanced tab. Caution - do not forget to check after adjustment Omit the cache and press Set XML 2nd Dashboard: place the completed report to the dashboard. Upon entering the prompt blank and created the Answer report errors, which a normal user does not understand.Therefore, the property is set section - guided navigation, which displays the Answer only if it returns data. 3rd Prompt: to prevent, to me the value of prompt (version) used as a challenge to limit the query (because of this fundamental Answer Answer I called another form of interaction columns / values and I needed to pass the value and the version passed by the variables), I gave prompt value into function, for example: CONCAT (tVerze_csl_0.Verze,'') , the above mentioned tricks are described in other articles.
Posts
