using fields returned form query as column names

• robin.rapport

  Grasshopper

  Points: 15

  More actions

  April 23, 2008 at 11:53 am

  #181600

  I am building reports against our TFS development db.

  One of the reports tracks days spent (Dwell Time) in various status categories (eg: New, Assigned, In Development, Hold, etc) for a given "ticket".

  For a fixed list of valuesfrom {Work Item].System_State, I can send the results (days in Assigned) to the column named (Assigned) for each status for each event in the [Work Item History], and then sum them for each ticket as:

  Ticket ID New Assigned InDev etc

  1230001 2 0 0 ....

  1230001 0 1 0 ....

  1230001 0 3 0 ....

  1230001 0 0 4 ....

  SUM 2 4 4 ....

  However, I have many different Projects, each of which uses their own Status names.

  I don't want to duplicate the same basic report, if I can avoid it.

  How can I name and generate this data for the unique Status list for each Project?

  Simplest analog is: name = First(Fields!Status.Value, "TFSdb")

  and allows value for a column name (category) as: =IIF(Fields!Status.Value = First(Fields!Status.Value, "TFSdb"), Fields!Days.Value, 0)

  However this fails beause:

  1. It only delivers the FIRST status value, and,

  2. I cannot SUM an expression which is itself an aggregate (using First).

  Constraints:

  This query result set is from a join on 4 tables;

  The “column names” I want to display are the results of a separate query and will be unique for each work group selected.

• vasamsetti_prasad

  SSC Eights!

  Points: 863

  More actions

  November 11, 2009 at 3:44 am

  #1077711

  Hi

  you can use the inbuilt function of SSRS RunningValue(expression, function, scope)

  How this function works you can find in below link.

  http://msdn.microsoft.com/en-us/library/ms159136.aspx

  Regards,

  Prasad