in

Prologika Forums

Business Intelligence to the Masses

This Blog

Syndication

News

  • Visit prologika.com for information about Teo's publications, the latest on the Microsoft Business Intelligence initiative, and more...

Prologika (Teo Lachev's Weblog)

Teo Lachev is a consultant, mentor, and author focusing on the design and development of Microsoft .NET business intelligence solutions. Read about Teo's work and life. Registered users can post comments.

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.

Only published comments... Mar 09 2007, 09:49 PM by tlachev

Comments

 

furmangg said:

Well put, Teo. I've run into this issue before on a calculated member which has different format strings depending on what slice of the cube you look at it. That's because the calc script uses the Format_String(this)="#,#" syntax to customize the format string for different slices of the cube.

The only way I could figure out to tackle this is to do what you suggested... change the XML of the report model like you said... and then make the calculated measure spit out a string with the formatted value.

I think the ultimate solution to this problem is in having Report Builder support per-cell, not per-field calculated measures. If you agree, you might vote for:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=225085

March 10, 2007 1:51 PM
 

tlachev said:

Yes, it will be nice to have the same capability as the Report Designer, e.g. FormattedValue property. Conditional formatting is a must-have too.

March 11, 2007 9:10 AM
Copyright © 2005 Prologika, LLC
Powered by Community Server (Commercial Edition), by Telligent Systems