May 27, 2020 at 2:35 pm
Hi everyone
Sorry to bother you with what is probably a really easy solution but I've given up trying to work it out.
I have a dataset (dynamics crm 365 data so fetchxml, not sql) where I want to measure the Value column twice - once based on the record's status in January and once based on it's current status, across 12 categories.
I've created to calculated fields 'Opening' which is if Opening20 = Yes, use NetValue else 0, and 'Current', which is based on another iif and returns NetValue or 0. They are my two Value columns. Then I use a field Sector which is straight out of the crm dataset (there's a filter on the table and the chart to only show records that have a Sector).
I put all the data in a table first to make sure everything is tickety-boo, and my Opening, Current and Sector columns all return the correct data. Then I create a chart (simple column chart) with the two value fields as my Values, and the sector field as my Category.
When I run the report (VisualStudio2015), no data. Nada. The legend is showing the two value fields and the 12 categories are showing along the y-axis, but no data. The table is fine. Exporting the table and running it through a pivot/chart is fine. Strangely, if I change the filter on Sector to show those records that have no sector, or just remove the filter altogether, I do get the data for those records.
Hellpppp! What am I doing wrong? It's driving me crazy. Please, for the love of God, small children and puppies, someone put me out of my misery.
Cheers
Jules
May 27, 2020 at 3:20 pm
If I were you, I'd never rely on values derived in the SSRS report. I try to compute all the values I need in my query that feeds the dataset, and then I know I can do whatever I need in the report without having to rely on expressions that may or may not work the way I might want them to. This is especially true when an expression occurs at a group level... things can be messy...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 27, 2020 at 3:28 pm
If it were running off a sql database I'd absolutely do that, unfortunately Microsoft, in their omnipotent wisdom, are forcing me to use fetchxml as it's a D365 in-the-cloud CRM. Absolutely hate it but there you are. I do have a sql version of the DB I can report off but it means this report would only be available to run through the report server and as it's will form part of a larger suite of management reports that can currently be run from within CRM, that would be rather untidy. Just don't see why SSRS does it - it's not like it's an over-complicated report, I don't need dual axes, lines over columns, anything special, just 12 sets of dual financial columns.
May 27, 2020 at 3:52 pm
I hear you, but make your dataset provide those values... and the problems go away.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 27, 2020 at 4:03 pm
Update: worked it out. Just noticed error on the report ref aggregate datatypes. Changed the expression on the chart, adding CDec() around the values (sum(CDec(Opening.Value)) and it works a treat.
Hurrah
May 27, 2020 at 4:42 pm
Excellent!
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 27, 2020 at 5:43 pm
Thanks Steve I knew it would be something simple 🙂
Datasets in fetchxml are a right pain but if you know of any useful fetch training I'd be glad to hear of it. I'm sure there's stuff I could do with knowing about, it can't be as basic and useless as it appears.
Cheers
Jules
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply