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) / Chart skips weeks/mont...
Microsoft Dynamics CRM (Archived)

Chart skips weeks/months when no data present

(0) ShareShare
ReportReport
Posted on by Joe Woltering 12,163

I have set up a chart to show a Count of Opportunities grouped by week over a year. So my Series is the Opportuntiy and my Category is 'Created On: Week'. If there is a week where there were no records created, the chart skips that week e.g. Week 1, Week 2, Week 5, Week 6, Week 8, etc.

I still want to show the Week label on the bottom axis with 0 as the value. How do I achieve this?

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Thomas David Dayman Profile Picture
    Thomas David Dayman 11,323 on at

    Hi Joe,

    You might need to write some custom FetchXML in order to show zeros in your charts.

    Follow this link: crmchartguy.wordpress.com/.../include-records-with-no-value-in-charts

  • Joe Woltering Profile Picture
    Joe Woltering 12,163 on at

    Hi Thomas,

    I've been playing with that for a while now, but it doesn't seem to work with "Created on" groupings. When I apply the filter it just turns any data in the filter to 0.

  • Thomas David Dayman Profile Picture
    Thomas David Dayman 11,323 on at

    Do you mind if I could have a look at your xml?

  • Joe Woltering Profile Picture
    Joe Woltering 12,163 on at

    Sure, thanks!

    <visualization>

     <visualizationid>{Internal_GUID}</visualizationid>

     <name>Opportunity by Created On</name>

     <primaryentitytypecode>opportunity</primaryentitytypecode>

     <datadescription>

       <datadefinition>

         <fetchcollection>

           <fetch mapping="logical" aggregate="true">

             <entity name="opportunity">

               <attribute groupby="true" alias="_CRMAutoGen_groupby_column_Num_0" name="createdon" dategrouping="month" />

               <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">

                 <attribute alias="_CRMAutoGen_aggregate_column_Num_0" name="opportunity_name" aggregate="countcolumn" />

                 <filter>

                   <condition attribute="createdon" operator="this-year" />

                 </filter>

               </link-entity>

             </entity>

           </fetch>

         </fetchcollection>

         <categorycollection>

           <category alias="_CRMAutoGen_groupby_column_Num_0">

             <measurecollection>

               <measure alias="_CRMAutoGen_aggregate_column_Num_0" />

             </measurecollection>

           </category>

         </categorycollection>

       </datadefinition>

     </datadescription>

     <presentationdescription>

       <Chart Palette="None" PaletteCustomColors="91,151,213; 237,125,49; 160,116,166; 255,192,0; 68,114,196; 112,173,71; 37,94,145; 158,72,14; 117,55,125; 153,115,0; 38,68,120; 67,104,43; 124,175,221; 241,151,90; 186,144,192; 255,205,51; 105,142,208; 140,193,104; 50,125,194; 210,96,18; 150,83,159; 204,154,0; 51,90,161; 90,138,57;">

         <Series>

           <Series ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>

         </Series>

         <ChartAreas>

           <ChartArea BorderColor="White" BorderDashStyle="Solid">

             <AxisY LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">

               <MajorGrid LineColor="239, 242, 246" />

               <MajorTickMark LineColor="165, 172, 181" />

               <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />

             </AxisY>

             <AxisX LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">

               <MajorTickMark LineColor="165, 172, 181" />

               <MajorGrid LineColor="Transparent" />

               <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />

             </AxisX>

           </ChartArea>

         </ChartAreas>

         <Titles>

           <Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59"></Title>

         </Titles>

       </Chart>

     </presentationdescription>

     <isdefault>false</isdefault>

    </visualization>

  • Suggested answer
    Thomas David Dayman Profile Picture
    Thomas David Dayman 11,323 on at

    HI Joe,

    Been a while since I looked at charts and its all coming back to me. I tried your xml on my end and I was having a hard time bringing months where no opportunities were made.

    The reason why you cant show months that were 0 is because there are no dates within the dataset that fall in that range, so those months don't exist in terms of crm. I assume you are using the view 'All Opportunities' or something similar.

    I tried looking on forums and lots of similar people have the same question but nobody is able to answer. You might have to figure some work around to solve this. You could add a workflow which creates a opportunity every month and you could add a flag in there to say that its a dummy opportunity. You could then filter this out in your chart xml to ignore these and then your dataset will see that the dates exist in your dataset.

    Hopefully this made sense ha :)

    Or some other crm expert might come in an answer this question no problem.

    Here is the xml i made anyway

    <datadescription>
    <datadefinition>
      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="opportunity">
			<attribute name="createdon" groupby="true" alias="groupby_column" dategrouping="month" />
				<filter>
               <condition attribute="createdon" operator="this-year" />
             </filter>
			 <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
			 <attribute name="name" aggregate="countcolumn" alias="_CRMAutoGen_aggregate_column_Num_0" />
			 </link-entity>
          </entity>
        </fetch>
      </fetchcollection>
      <categorycollection>
        <category>
          <measurecollection>
            <measure alias="_CRMAutoGen_aggregate_column_Num_0" />
          </measurecollection>
        </category>
      </categorycollection>
    </datadefinition>
  </datadescription>


  • Joe Woltering Profile Picture
    Joe Woltering 12,163 on at

    Yeah, I read about creating dummy records too but to my knowledge there's no way to pre-date records so that the Created On date is in the past. Maybe through some type of SQL manipulation but I really don't want to go there.

    Yep, it's a real pain in the butt! Thanks for taking a look though.

    Cheers!

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