web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / SSRS Report to Get cou...
Microsoft Dynamics CRM (Archived)

SSRS Report to Get count Value for Each Option set Values and show the related records when the count is clicked

(0) ShareShare
ReportReport
Posted on by Community Member

Hi all,

I am creating a report where the Columns are based on the Option set Values.

I am using the field named as "Code" in all the below columns to fetch the related records. 

I am using From and To Parameters to get the records based on the selected dates. When the From and To dates are selected to get the records, the count of each Code values should be generated as a result.

After clicking any count result of any field (say Mobile), the number of records which is shown as count result should be fetched.

The schema name for Option set field Code is new_code.

Option Value for Mobile- 1

Option Value for Telecom- 2

Option Value for Others- 3

Option Value for Broadband- 4

Option Value for WiFi- 5

 

Stage 1 stage 2 stage 3 stage 4 stage 5
Mobile Telecom Others Conditional Closing
Count(Mobile) Count(Telecom) Count(Others) Count(Broadband) Count(WiFi)

i.e say, If Count(Mobile) result is clicked , the number of records related to the Option set value of Mobile should be shown.

I am able to get the Count of the whole Field but i am unable to get the count value for each option set value in each columns.

I have used this expression in the  Count(Mobile) : =Count(Fields!new_Code.Value,"DataSet1")

 

Need your valid suggestions! Awaiting your responses!

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Community Member Profile Picture
    Community Member on at

    You need to use check condition in expression for each category.

    1. For Mobile

    SUM(IIf(Fields!new_Code.Value = "Mobile", 1, 0), "DataSet1")

    2. For Telecom

    SUM(IIf(Fields!new_Code.Value = "Telecom", 1, 0), "DataSet1")

    Similarly change the Expression for all others

  • Community Member Profile Picture
    Community Member on at

    Thank you so much Sohail. How to view the records of the result of the Sum using the drill down reports. Say,I have Mobile column count result as 10. If 10 is clicked , it should give the 10 records (sub report) using the drill down approach. If possible, I request you to help me out on this! Thanks in advance!

  • Verified answer
    Community Member Profile Picture
    Community Member on at

    I  did not do such task open new sub-report but can gave you hint i hope it will work for your scenario

    Step 1:- First need to design sub-report with parameter of new_code e.g if we pass mobile it will filter only mobile records and so on...

    Step 2:- On field where is sum expression write open it's properties as shown in figure

    Subreport.PNG

    on Action select the sub-report name and add sub-report parameter name and specific value.

    it will open the report and filter the records with passed value.

  • Community Member Profile Picture
    Community Member on at

    Hi Sohail,

    I tried to give the Condition as per the screenshot of yours for Code by using the expression which I have used to get the Sum i.e =Sum(IIf(Fields!new_codeValue.Value = "1", 1, 0), "DataSet1") along with the From and To parameters as per the drill down report approach.

    Howver, When I saved this and tried to click the Sum's Result (say 13) , It went to the Sub report but I did not get the 13 records of Mobile code Option set value.

    In the Sub report, I gave it as "Integer" as Data Type in the Code's parameter as I have passed the Integer parameter in the main report.

    Help me if possible as where I am missing the logic.

  • Community Member Profile Picture
    Community Member on at

    I think you are passing From & To date parameters in sub-report but here you should create another parameter (e.g Code) also that is filter the records and take only those records as passed value.

    Plz send me screen shoot of the Sub-Report and Main(Parent) Report field Properties (Count Mobile) where you passing parameter 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans