Pivoting Dataviews
THE IDEA
I would like to suggest that the dataview object retains all the native functionality available in horizontal alignment when using the vertical alignment (pivoted Dataview).
EXPLANATION
Currently, the dataview object is highly versatile in formatting and application when aligned horizontally. One can drill, conditionally format and generally customise the blocks to show in many different ways. The issue is when needing to pivot a dataview vertically. On so doing, the ability to drill and use multiple axes are lost. What's more, there is a disparity in how entities are displayed in that text based figures, which are left aligned and presented as though they were text, are converted to a numeric with thousand separator and right aligned when in vertically alignment.
Illustration
Below are two images to illustrate the issue of the vertical alignment re-formatting - pivoting inconsistency:
Image 1: Sample data presented horizontally in a standard dataview object
Image 1 shows a native, unchanged dataview with text entity members - there is no formatting of any kind applied to the dataview. It can be seen that a few of the members are composed solely as figures but presented as text. These entity members are text and were loaded as text in the datareader.
The datareader, and the way it has presented the entity members, provides the developer with the expected behaviour with which to understand potential applications for a dataview; I use datareaders in place of substitution formulae and rule based reports when I want to list a number of entity members related to a single dimension entity member like a property or a single lease.
Image 2: Sample data presented vertically in a standard dataview object
left: Item1 numeric member values converted Right: Item2 Alphanumeric member values shown correctly
Image 2 demonstrates the effect of pivoting the dataview on the vertical axis. The numeric text members have been converted to a number with thousand separator and aligned to the right. None of these formatting choices were made by the developer and so demonstrate a inconsistency in the expected behaviour of the dataview object. Curiously, the [Resource ID] has also been right aligned! The table to the right displays the next ITEM and all of the entity members have been now become left aligned with no numeric separators. This change in formatting from item to item demonstrates an inconsistent behaviour that the developer has no control over. For more on vertically aligned dataviews, click here
THE RISK
The main risk with the current dataview object is that a developer can deliver a model which is formatted and behaves as expected but that the client, on introducing new entity members of a numeric only format, would incur the formatting issues as described above and return to the developer for a response. The severity of the issue is amplified when the client is external and completed screen designs were originally agreed under contract.
WORKAROUNDS
I use two workarounds to compensate for the issue. The first simply adds additional blocks to the dataview but requires a little clairvoyance. The second method prevents the fault from occurring at any point in the future but adds to the size a complexity of the model as it adds individual cubes for each entity to be displayed vertically and so is a duplication of work at the very least .
1. Compensation blocks
To compensate for the outcome in the left hand table of Image 2, one solution is to add an additional block to the Dataview for each of the blocks that might be affected - see image 3 below. The algorithm is simple, it is the letter of the block to be repeated, remembering to change the format from numeric to text.
Image 3: vertically aligned dataview with compensating blocks to illustrate effect.
One drawback with this solution is that the developer must first identify the possibility of numeric only members being introduced to any entity at a later date. Alternatively, one could make clear to the client that the future introduction of numeric members would result in formatting issues.
2. Create Text Cubes
To avoid predicting which entities may, in future, contain mixed format members, I have implemented a best practice of creating text cubes to hold all entity members against the members of the dimension being used as the vertical axis.
This solution allowed me to further develop a method of creating multidimensional text cubes and my thanks go to J C for explaining how to dataflow entities members to infocube values in a process.
Below is the method (in brief) I use
Method - in brief
- Create a text cube containing the dimension of the axis to pivot by.
- Populate cube using a dataflow or datareader
3. Use the cube value to display entity member.
Both methods require the developer to duplicate original information and they both have pros and cons. for the second method, further considerations would have to be paid to powerusers who might get confused when seeing the same names and info for entities and infocubes.