Report Builder and Text-based Calculated Members

Although not a common practice, UDM supports defining text-based calculated members. For example, here is an useless but valid text-based calculated member:

Create Member CurrentCube.[Measures].[Hello] As “Hello”;

Much to my surprise, a text-based member won’t show up in Report Builder. That’s because the Report Builder defaults the member’s data type to float and the text is simply “lost” when the report is run. How does the Report Builder determine the field data type with UDM as a data source. With regular measures, it’s simple: the Report Builder derives the field type from the DSV data type. It seems that for calculated members, however, the Report Builder always defaults the data type to float.

While there may other workarounds to resolve this issue, one way is to “correct” the Report Builder invalid assumption about the member data type. Since one would typically auto-generate the Report Builder model when UDM is used as a data source, the ultimate hack is to change the model directly. To do so, go to the model properties in the Report Manager and click the Edit button to save the model as a file. Then, find the text-based member (attribute) by name and change its type from float to string. Then, back to the Report Manager, click the Update button to update the model from the file. As a result, the report will show the member text values correctly.