none
SSRS REPORT EXPRESSION WITH PARAMETERS

    Question

  • hi, guys

    I am very new to SSRS and updating an existing rdl file from others

    I am lost about one cell expression in a report.

    For this cell, it already has a parameter named "projectID", which outputs to many report tables group by projectID.

    But for one reports table, it has a column named "JobGroup". For different projectID, the JobGroup are different. And the report has made it by a field in a dataset.

    I am wondering how can I add another column to calculate the numbers for each "JobGroup". Do I need another parameter?

    The SQL Query should be like this:

    SELECT projectID, JobGroup

    FROM ...

    GROUP BY projectId, JobGroup

    Anyone can help me with it? Thanks!!!

    Monday, September 09, 2013 7:55 PM

Answers

  • What do you think about following code?

    SELECT projectID, JobGroup, COUNT(*) As JobGroupCount
    FROM ...
    GROUP BY projectId, JobGroup
    


    Remember to mark as an answer if this post has helped you.

    • Marked as answer by Sosa727 Wednesday, September 11, 2013 4:24 PM
    Tuesday, September 10, 2013 8:10 PM
  • Hi Sosa727,

    From the description, I understand that you want to add a column in the tablix to calculate the countnumber of each JobGroup. In this case, I suggest to create a new dataset named Product which includes  the field value that calculates the count number for each JobGroup. 

    First, we can add the following commands in Query Designer of the Product dataset as Igor posted:

    SELECT projectID, JobGroup, COUNT(*) As JobGroupCount
    FROM YourTableName
    GROUP BY projectId, JobGroup

    If you use the version of SQL Server 2008 R2 Reporting Service or higher, we can use Lookup Function in SQL Server Reporting Service. Lookup Function can help us retrieve the value from the specified dataset. For example, for an ID field in a table, we can use Lookup to retrieve the corresponding Name field from a dataset that is not bound to the data region. Based on your requirement, I add the expression to the column including the JobGroupCount field value as follows:

    =Lookup(Fields!JobGroup.Value, Fields!JobGroup.Value, Fields!JobGroupCount.Value, "Product") 

    For the detailed information about Lookup Function, please see the link: http://technet.microsoft.com/en-us/library/ee210531.aspx

    If you use the version of SQL Server 2008 Reporting Service or lower, I suggest to create a subreport and add the subreport to the column which displays the number for the JobGroup in the report. We can refer to the link to add a subreport: http://technet.microsoft.com/en-us/library/ms160348(v=sql.100).aspx

    If I have any misunderstanding, please post the report screenshot  for further analysis.

    Regards,
    Heidi Duan

    • Marked as answer by Sosa727 Wednesday, September 11, 2013 4:24 PM
    Wednesday, September 11, 2013 1:45 PM

All replies

  • Hi Sosa727,

    Try

    Select JobGroup, Count(*) as JobGroupCount
    From ...
    Group By JobGroup
    

    Thanks.


    Remember to mark as an answer if this post has helped you.

    Monday, September 09, 2013 8:49 PM
  • Then how to write the expression to show the JobGroupCount?
    Tuesday, September 10, 2013 7:57 PM
  • What do you think about following code?

    SELECT projectID, JobGroup, COUNT(*) As JobGroupCount
    FROM ...
    GROUP BY projectId, JobGroup
    


    Remember to mark as an answer if this post has helped you.

    • Marked as answer by Sosa727 Wednesday, September 11, 2013 4:24 PM
    Tuesday, September 10, 2013 8:10 PM
  • Hi Sosa727,

    From the description, I understand that you want to add a column in the tablix to calculate the countnumber of each JobGroup. In this case, I suggest to create a new dataset named Product which includes  the field value that calculates the count number for each JobGroup. 

    First, we can add the following commands in Query Designer of the Product dataset as Igor posted:

    SELECT projectID, JobGroup, COUNT(*) As JobGroupCount
    FROM YourTableName
    GROUP BY projectId, JobGroup

    If you use the version of SQL Server 2008 R2 Reporting Service or higher, we can use Lookup Function in SQL Server Reporting Service. Lookup Function can help us retrieve the value from the specified dataset. For example, for an ID field in a table, we can use Lookup to retrieve the corresponding Name field from a dataset that is not bound to the data region. Based on your requirement, I add the expression to the column including the JobGroupCount field value as follows:

    =Lookup(Fields!JobGroup.Value, Fields!JobGroup.Value, Fields!JobGroupCount.Value, "Product") 

    For the detailed information about Lookup Function, please see the link: http://technet.microsoft.com/en-us/library/ee210531.aspx

    If you use the version of SQL Server 2008 Reporting Service or lower, I suggest to create a subreport and add the subreport to the column which displays the number for the JobGroup in the report. We can refer to the link to add a subreport: http://technet.microsoft.com/en-us/library/ms160348(v=sql.100).aspx

    If I have any misunderstanding, please post the report screenshot  for further analysis.

    Regards,
    Heidi Duan

    • Marked as answer by Sosa727 Wednesday, September 11, 2013 4:24 PM
    Wednesday, September 11, 2013 1:45 PM
  • Thank you for your help!

    I have already fixed it!

    Thanks!!!

    Wednesday, September 11, 2013 4:24 PM