It is so simple - just dates on a line graph...

[Matthew247]

All,

Too many hours wasted trying to get this to work... I have a report that has a Direct Query link to a Dynamics CRM table. The table contains fields that record when cases were created (Case). It uses the default Created On field which is a Date and Time field (Time zone adjustment User local if that is relevant). I want a chart that has dates along the bottom and Count of the Cases field to show how many cases were created each date. Simple...

But the chart only ever shows 1 record per date (with the odd exception) as its taking each time stamp as a unqiue value - see below. I just want it to group it by dates and ignore the time. I have attempted to play with formats but can't get it working. This is an out the box Microsoft Date and Time field so I know the data is good. Help please...!

[Matthew247]

Thanks - can I do that if its a Direct Query link to the data?

[lbendlin]

yes, you can have calculated columns in Direct Query as long as they originate from the same row.

[lbendlin]

Add a calculated column that gets the DATEVALUE() of your timestamp, and use that in the X axis instead.

[Matthew247]

For some reason its saying that the column is not suitable for this - but its a standard date time automatic field from Dynamics? 

[lbendlin]

You need to create a calculated column, not a measure.

[Matthew247]

Thanks for the reponse and patience. Like this? No joy

[lbendlin]

The sources for a calculated column in Direct Query mode must come from the same table row. Are you creating that column in the Incidents table?

[Matthew247]

So you are correct -wrong table. Now it is and accepted the col. But now when I just try and view it as a table it wont show the data? 

[lbendlin]

what does the error message details link say?

[Matthew247]

Sure: 

[lbendlin]

That's not cool. It should have worked. Sorry!