none
how do i reference a specific item from dataset

    Question

  • I have the following dataset;

    PS001      3/1/2011 12:00:00 AM 4.44 4.23 86 87
    PS002      3/1/2011 12:00:00 AM 12.19 3.03 42 43
    PS003      3/1/2011 12:00:00 AM 1.26 1.31 145 145
    PS004      3/1/2011 12:00:00 AM 2.13 2.54 116 116

    from this query;

    SELECT     Liftstation, Date, [P1 Runtime], [P2 Runtime], [P1 Cycles], [P2 Cycles]
    FROM         dbo.[Liftstation Model]
    WHERE     (Liftstation LIKE N'ps%') AND (Date BETWEEN DATEADD(dd, 0, DATEDIFF(dd, 0, @DateParam1)) AND DATEADD(ss, 59, DATEADD(mi, 59, DATEADD(hh,
                          23, DATEADD(dd, 0, DATEDIFF(dd, 0, @DateParam1))))))

    i cant seem to get this expression to work;

    =First(Fields!Liftstation.Value,

    "PS002")&" "&Round(Sum(Fields!P1_Runtime.Value, "PS002"),2) &" / "&Round(Sum(Fields!P2_Runtime.Value, "PS002"),2)& " " &Round(((Sum(Fields!P1_Runtime.Value, "PS002")+Sum(Fields!P2_Runtime.Value, "PS002"))*60*300),0)
    Wednesday, April 25, 2012 2:17 PM

Answers

  • Hello,

    In your case, could you please elaborate your requirement with more detail? If possible, please post an example about the requirement.

    Generally, the Sum() function syntax is:
    Sum(expression, scope, recursive)

    The scope can be a dataset name, group or data region. In the expression you posted above, I found "Sum(Fields!P1_Runtime.Value,"PS002")" statement. Do you have a group or data region or dataset named "PS002" in your report?

    Regards,


    Bin Long

    TechNet Community Support

    Monday, April 30, 2012 10:10 AM
    Moderator

All replies

  • I guess I'm having a hard time trying to understand what you want to do here. So you have the resultset as shown. What's the expression need to do here. Where are you putting this expression, at detail line, row group header?

    Think out of the box


    • Edited by i_h Wednesday, April 25, 2012 5:36 PM
    Wednesday, April 25, 2012 5:36 PM
  • Hi Mark223344,

    It seems that you want to calculate the “PS002” for the report. Right? If so, we can use IIF statement to achieve this. For example,
    =IIF(Fields!Liftstation.Value=”PS002”, Fields!P1_Runtime.Value,Nothing)

    Here is an article about expression example in Reporting Services for your reference, please see:
    http://msdn.microsoft.com/en-us/library/ms157328(v=sql.105).aspx

    If I have something misunderstood, please point out and elaborate your requirement with more detail.

    Regards,
    Bin Long

     


    Bin Long

    TechNet Community Support

    Friday, April 27, 2012 5:24 AM
    Moderator
  • when i create a textbox to reference the fields i need, i get {Report item not linked to a dataset.} when clicking on {Fields} under {Edit Expression}, has something broken?, or reload visual studio?, it used to work a year ago, not nothing shows up under {Fields}
    Friday, April 27, 2012 2:32 PM
  • Hello,

    In your case, could you please elaborate your requirement with more detail? If possible, please post an example about the requirement.

    Generally, the Sum() function syntax is:
    Sum(expression, scope, recursive)

    The scope can be a dataset name, group or data region. In the expression you posted above, I found "Sum(Fields!P1_Runtime.Value,"PS002")" statement. Do you have a group or data region or dataset named "PS002" in your report?

    Regards,


    Bin Long

    TechNet Community Support

    Monday, April 30, 2012 10:10 AM
    Moderator