none
SSRS Project Workspace Report Formatting Problem RRS feed

  • Question

  • Hi All,

    I am trying to create a project report in SSRS, to bring back Workspace List Items. This is nearly working great, however I have one issue at the moment. Hope this makes some sense as I am pretty new to the development element.

    When I deploy my report from BIDS, I run the report and one of the cells in my report was showing an untidy result;

    float;#7.20000000000000

    I managed to figure this out and format it by firstly applying the following code to the report properties code section;

    Public Function CleanSPSInput(strIn As String) As String
    Dim RegEx as System.Text.RegularExpressions.Regex
    ‘ Replace invalid characters with empty strings
    if strIn = "" OR IsDBNull(strIn)
    Return ""
    Else
    Return Regex.Replace(strIn, ".+;#", "")
    End If
    End Function

    Then applying this expression to the value in question:

    =code.CleanSPSInput(Fields!Exposure.Value)

    This gets rid of the float;# part so thats great.

    However, now with the problem. I want to also reduce the amount of decimal places to just one. Currently it shows a heap of 0000s. I normally use this code for other cells which do not have this issue (and it works fine);

    =FormatNumber(Fields!Exposure.Value,1)

    However i cannot figure out how to use the two to avoid #Error in the report when its run.

    Any ideas? I am new to this!


    Rob Hardy
    Wednesday, November 16, 2011 1:19 PM

Answers

  • Well lets backup. it might be easier to just NOT go get the exposure and just have the report calculate it for you based on the impact and probability numbers. Im assuming that those are coming back as valid numbers, right?


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn
    • Marked as answer by EPM Tester Thursday, November 17, 2011 11:04 AM
    Wednesday, November 16, 2011 6:26 PM
    Moderator

All replies

  • You should  be able to handle this with the Text Box properties dialog in Report Builder itself.

    1. Right click on the cell
    2. select Text Box Properties
    3. Click on Number
    4. Select the format (likely Number)
    5. Select the number of decimal points to display

    If you are doing this in Visual Studio (BI studio) there should be a similar dialog.


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn
    Wednesday, November 16, 2011 4:01 PM
    Moderator
  • Hi,

    Thanks for the response. I tried that and unfortunately it didn't do anything. It doesn't knock any of the 0's off.


    Rob Hardy
    Wednesday, November 16, 2011 5:18 PM
  • Try converting that cleaned up string to a numeric field type. I think that might be why the solution I gave is not working. The value in there is not a number. it is a string.

     

    Odd also since I dont remember having to clean up Exposure values from the Risks list in Project workspaces when i worked with them. Can you share your query?


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn
    Wednesday, November 16, 2011 5:41 PM
    Moderator
  • Hi Brian,

    Sorry where abouts do I change this?

    Which bit of the code would you like to see?


    Rob Hardy
    • Edited by EPM Tester Wednesday, November 16, 2011 6:23 PM
    Wednesday, November 16, 2011 6:22 PM
  • Well lets backup. it might be easier to just NOT go get the exposure and just have the report calculate it for you based on the impact and probability numbers. Im assuming that those are coming back as valid numbers, right?


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn
    • Marked as answer by EPM Tester Thursday, November 17, 2011 11:04 AM
    Wednesday, November 16, 2011 6:26 PM
    Moderator
  • Thanks Brian, that works just fine!
    Rob Hardy
    Thursday, November 17, 2011 11:04 AM