none
How to insert a column in a Report Builder table (Project Server 2010) RRS feed

  • Question

  • Hello,

    In Report Builder 3.0, I created a table with 3 fields (columns). Now I want to add a 4th field (a custom one with a very simple formula: Work_md = Work/8). This field is listed in the DataSet.

    1° I insert a column

    2° I set a value in Text Box Propertie : =[Sum(Work_md)] at each group.

    3° I click Run

    Result : # Error on every row

    Any idea?

    Thanks for your help

     

    Thursday, November 24, 2011 10:40 AM

Answers

  • Hi again Wlid,

    Your problem seems that is coming from your first calculated field, i.e. Work_md =Work/8.
    Try to apply the format to that formula, and make sure that the result you are giving is the desired.

    Cint(Work/8)

     

    Cheers!


    Miguel Soler
    • Marked as answer by WLID1966 Saturday, November 26, 2011 5:54 PM
    Saturday, November 26, 2011 5:15 PM

All replies

  • Humm...

    I did nothing more but now when i Run the report, I get the right values for the basic level (the values for grouping levels are wrong, too small).

    Another issue : I set a custom Format number for this column: # ##0" md" but the values don't obey the format : they still display 4 or 6 decimal places...

    Is there something about the format number of such a field?

    Thanks

    Thursday, November 24, 2011 1:46 PM
  • Sorry,

    My custom field turned out to be Text type... I think that's why I cannot change its format.

    Is it possible, in a Report, to change the type of a field => Number ?

    Thanks

    Thursday, November 24, 2011 2:24 PM
  • Hi Wlid,

    What is exactly your aim??

    If I understood you want to convert a Text type to a Number using a table in Reporting Builder, no?

    You can try with SUM(FortmatNumber (Work_md.Value)).

     

    Hope this helps.

    Cheers!


    Miguel Soler
    Thursday, November 24, 2011 2:48 PM
  • Hi Miguel,

    Yes, that's exactly what I need to do.

    But the above formula returs an error when I run the report builder:

    Failed to preview report. Name 'FormatNumber' is not declared

    Any idea?

    Thanks again

    Thursday, November 24, 2011 3:16 PM
  • FormatNumber is a function in SSRS what do exactly what we are looking for.

    Try to don´t use the SUM to check if all is going ok, and, of course, put an equals before, like this:

    =SUM(FortmatNumber(Work_md.Value))

    I guess you are trying not just to change Text for Number, the "Text" you are trying to change has commas, and reporting Services doesn´t really like that. Try this too:

    =format(Work_md.Value, "€#,#.00")

     

    Let us know your advances!

    Kind regards :D


    Miguel Soler
    Thursday, November 24, 2011 3:29 PM
  • NB : I didn't forget the equals sign before the Sum :)

    The second formula: =Format(Work_md.Value,"€# ##0")

    returns the error:  "System.Web.Services.Protocols.SoapException: The Value expression for the textrun ‘Textbox4.Paragraphs[0].TextRuns[0]’ contains an error: [BC30451] Name 'Work_md' is not declared."

    If I try =Format(Fields!Work_md.Value,"€# ##0") I get the characters €# ##0 on every row.

    :-(?

    Thursday, November 24, 2011 4:02 PM
  • Ok,

     

    Arriving to this point, as we know, you didnt get anything. Do, for the purpose, could you paste some screen shoots about what you have developed in Report Builder and explain with detail what you want to get? I can´t figure out what is exactly happening...

    Thanks!


    Miguel Soler
    Thursday, November 24, 2011 8:02 PM
  • Miguel, thanks for your help,

    1st screen shot : the raw data for the "Man Days" column as it apears with the Expression:

    Fields!Work_md.Value 

    2nd screenshot : the Man Days column with the expression: =format(Fields!Work_md.Value,"€#,#.00")

    Thursday, November 24, 2011 9:43 PM
  • I'm not too far from the solution with this formila:

    =Format(Cint(Fields!Work_md.Value),"# ##0 md")

    but from time to time it multiplies by 100, like on the 3rd row for example : 9000 md instead of 90 :

     

    Thursday, November 24, 2011 10:20 PM
  • Hi,

    The issue is about the decimal separator: most of the time it is the dot "."; from time to time it's the comma ",". When it's the dot, the format formula works fine, when it's the comma, the result is multiplied by 100.

    Any idea to correct thi issue?

    Thanks

    Saturday, November 26, 2011 9:13 AM
  • The following sophisticated formula solves the issue for elementary rows:

    =IIf(InStr(Fields!Work_md.Value,",")>0,Format((Fields!Work_md.Value)/100,"# ##0 md"),Format(Cint(Fields!Work_md.Value),"# ##0 md"))

    But how to calculate the Total rows ?

    I hope that there is a more elegant setting somewhere which can do that...

    Saturday, November 26, 2011 10:16 AM
  • Hi again Wlid,

    Your problem seems that is coming from your first calculated field, i.e. Work_md =Work/8.
    Try to apply the format to that formula, and make sure that the result you are giving is the desired.

    Cint(Work/8)

     

    Cheers!


    Miguel Soler
    • Marked as answer by WLID1966 Saturday, November 26, 2011 5:54 PM
    Saturday, November 26, 2011 5:15 PM