May 6, 2020 at 1:50 pm
Hi all
I'm sure I've found a solution to this problem before but it may just have been updating the Language on the report to en-GB, which I've done on this report.
I have a fetchxml report (yes I know...) where processingdate <= @ProcessingDate. The parameter input is in UK format but whilst running it's using the parameter as if it were in US format so the results aren't even giving the correct output. If I input the date as anything greater than dd=12 e.g. 23/02/20 (23rd January 2020) or even 02/23/20, it appears to be loading then I get error message 'date/time format is not valid, or the value is outside the supported range'.
Can someone help please? It is, to say the least, somewhat irksome. Other dates on the report are being returned in the correct format, and my Month calculated field that returns the month number is also looking at the correct source and converting properly, so it's just the parameter playing up.
I hope someone can help! Last bit I need to configure on this report!
Cheers
Jules
May 6, 2020 at 2:21 pm
Jules
Is changing the report itself an option? If so, please post the code. It should be fairly simple to massage the date into an unambiguous format. I've never heard of fetchxml, but hopefully it won't be too difficult to pick the SQL parts out of it!
John
May 6, 2020 at 3:33 pm
Hi John
I'm using fetchxml rather than sql as I'm querying Dynamics 365 online. TBH Microsoft need to improve what you can do with fetch, it's not a patch on sql.
So here's the important bit of fetch for this dataset; specifies the entity, fields to be returned and the filters applied to it. I have an additional calculated field on the report for this dataset Month which is Month(Fields!cbi_datejoinedValue.Value). That enables me to put the data into the correct row in the table (the table has 12 columns, based on 6 different datasets so I cannot do a standard group on - each data set has this calculated Month field). Each dataset will have it's own parameter e.g. Rises will be <condition attribute="cbi_startdate" operator="on-or-before" value="@ProcessingDate" />.
I don't even actually need to use the date, just the month will do but apparently you cannot create custom columns in fetchxml. The Month(ProcessingDate), which would need to be a number not the month name, would have to be another calculated column in each dataset, then I'd have to have a parameter for each dataset?
To say this is driving me nuts is putting it mildly. Hope you can help!
Cheers
Jules
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" count="5000" page="1" no-lock="false">
<entity name="cbi_datatracker">
<attribute name="activityid" />
<attribute name="subject" />
<attribute name="createdon" />
<attribute name="cbi_year" />
<attribute name="cbi_wmrrecordtype" />
<attribute name="cbi_value" />
<attribute name="cbi_processingdate" />
<attribute name="cbi_dateleft" />
<attribute name="cbi_datejoined" />
<attribute name="cbi_account" />
<order attribute="subject" descending="false" />
<filter type="and">
<condition attribute="cbi_datejoined" operator="this-year" />
<condition attribute="cbi_wmrrecordtype" operator="eq" uiname="APP" uitype="cbi_wmrrecordtype" value="{094AF774-7078-E811-A841-000D3A2B2BA3}" />
<condition attribute="cbi_processingdate" operator="on-or-before" value="@ProcessingDate" />
</filter>
May 6, 2020 at 4:18 pm
I am a bit confused - if your parameter is setup as a date/time parameter then it shouldn't matter the 'format'. The parameter will be defined as a date/time and interpreted by SQL Server correctly.
If your parameter is actually a string - why?
SQL Server will interpret a date string based on the language in SQL Server - the language defined in the report won't make any difference.
If you cannot use a date/time picker and must use a string then make sure you format the string as YYYYMMDD. SQL Server will interpret this correctly every time.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 6, 2020 at 5:37 pm
Hi Jeffrey
The field I'm using for the parameter is a date/time field and the parameter is set to date/time type. It's allowing me to enter UK dates at Preview stage but then appears to read them as US dates when I click View Report. If I enter a date that is less than the 13th in UK format, the report renders but translates the output as if the parameter date was a US date. If I enter a date that is 13th or greater in UK format, the report refused to render So it does appear to be a bug in SSRS that is doing something strange unless there is a setting somewhere I don't know about. Dynamics 365 online is not a sql server database, but I can still use SSRS but the source query has to be in fetchxml.
But... here's the good news...
Oh oh I think I've fixed it :). I've taken the parameter out of the fetchxml query and put it into a filter on the dataset instead. It's now allowing me to put in date of 31/1/2020 and appears to be returning the correct data. Woo hoo!!!
Thanks chaps, I can breathe now.
Best
Jules
May 6, 2020 at 6:11 pm
Hi Jeffrey
The field I'm using for the parameter is a date/time field and the parameter is set to date/time type. It's allowing me to enter UK dates at Preview stage but then appears to read them as US dates when I click View Report. If I enter a date that is less than the 13th in UK format, the report renders but translates the output as if the parameter date was a US date. If I enter a date that is 13th or greater in UK format, the report refused to render So it does appear to be a bug in SSRS that is doing something strange unless there is a setting somewhere I don't know about. Dynamics 365 online is not a sql server database, but I can still use SSRS but the source query has to be in fetchxml.
But... here's the good news...
Oh oh I think I've fixed it :). I've taken the parameter out of the fetchxml query and put it into a filter on the dataset instead. It's now allowing me to put in date of 31/1/2020 and appears to be returning the correct data. Woo hoo!!!
Thanks chaps, I can breathe now.
Best
Jules
I didn't catch the part where you were using fetchxml as the dataset. The problem is still the same though - although a bit hidden...to build the XML to be sent to the service SSRS is building a string based on the client (UK) date format which does not work on the server which is set to US.
Here is your exact issue: https://meganvwalker.com/date-parameter-issue-fetchxml/
She was able to get around the issue by creating 2 hidden parameters which formatted the date parameters into YYYY-MM-DD format and then used those hidden parameters in the FetchXML query.
Your method also works but could be an issue with performance - depending on how much data is retrieved before the filter is applied. If that works and does not present any performance issues then I wouldn't bother changing to use hidden parameters.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 7, 2020 at 10:41 am
Hi Jeffrey
Thanks for the post and pointing me in Megan's direction. I see there is a workaround but I really shouldn't have to do that should I? When you say "which does not work on the server which is set to US", what server are you referring to?
Interestingly, Megan's post suggests her report works fine in VS but not in CRM, whereas my issue is while I'm still developing it in VS. Is this another Microsoft issue where they think everyone lives in the US?
Cheers
Jules
May 7, 2020 at 2:55 pm
In SQL Server - if you try to use a date string in DD/MM/YYYY format where the language is set to US (either for that connection or server) it will not be interpreted correctly. US date format would need to be MM/DD/YYYY.
When using FetchXML - SSRS is building a string to be passed to the database server to execute your code. The server where that code is being executed cannot convert the string date to an actual date and fails.
You either have to build a hidden parameter that formats the date in a way that is unambiguous - or you filter after the fact where SSRS (or Visual Studio) can parse the date as a date/time against the date/time column returned.
Because FetchXML is using a string - how that string is generated depends entirely on the client where that code is running and the language of the connection or SQL Server. So - if Visual Studio is running on your client and your client is set to US English and the database server is set to US English - no problem or error. If the server where SSRS is installed is UK English and the database server is US English - it will fail even if it is successful on your client workstation.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 5, 2020 at 11:46 am
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply