question

LeeChamberlain-1963 avatar image
0 Votes"
LeeChamberlain-1963 asked LukasYu-msft commented

SSRS - Fetchxml - display monthly totals even if 0

Hi

I'm writing a SSRS report using fetchxml. I want the monthly totals for an entity, However some months have 0 results.

As such my matrix is only showing January and April, where I want all months.

Is there a way to force SSRS to show months even if zero results are returned?

Thanks
Lee

sql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered

Hi,

Are there no records for those "0 results" months? Well, technically, there is no field in the XML dataset.

If you want to show those missing months, I am afraid we could not do much with FetchXML query to work this out.

My suggestion would be that, you could build a dataset with only nature months, like, 12 records.

And try join these to datasets, and show the missing months. We could try such as lookup function to combine them in SSRS. Also you could try to process the table in SQL. This depends on the desired structure of your report. These are just some directions.

You could show us your desired outcome and the sample data, we would see what we can do specificlly.

Regards,
Lukas

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LeeChamberlain-1963 avatar image
0 Votes"
LeeChamberlain-1963 answered LukasYu-msft commented

My results are as below.

I can get why there are no results, but as months exists outside of the number of results, there must surely be some way to code the issue. I've no SQL skills so it has to be in fetchxml.

92941-image.png



image.png (12.4 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

From the small table we could only assue there were no data for other months. Or maybe they were order under invalid month. The info is to less for further debugging...

0 Votes 0 ·