locked
Adding a field with date/time kills custom reports... RRS feed

  • Question

  • If I create a report and add any type of field that has date/time information to the layout columns and try to run it, the following happens:

    rsRuntimeErrorInExpression

    If I remove the field with the date/time info from the layout, it runs. Another strange thing is this, a while back, I exported a custom report, modified it, and imported it. It has a date field in it and it runs fine. I ran the event viewer on the CRM server and found this:

    Without being able to have dates on a report, that basically cripples about half of my custom reports.


    • Edited by nasyrax Tuesday, June 10, 2014 8:37 PM
    Tuesday, June 10, 2014 8:36 PM

All replies

  • Hi Nasyrax,

    This sounds like an issue with your date formats.

    It may be that your server is set to a different timezone then your CRM system.

    If you export the report and play with it in BIDS you are able to set what time-zone format to use.

    I would suggest checking your current time-zones and make sure they are consistent. :)

    ~ Atomic Coder

    Tuesday, June 10, 2014 9:43 PM
  • Well, I checked and CRM and SQL are on the same server and the time zone matches mine, so it doesn't appear that is the issue. We're going to try to run an SQL profiler to see if we can see where it's breaking. If you have any other ideas I would be very appreciative!

    UPDATE 1: OK, I found out that the server that CRM and SQL are on are set to GMT, whereas CRM stores time as UTC. But this hasn't changed to my knowledge and the reports worked previously. Also, we did install the Rollup 17 update over the weekend, but the reports worked for several hours after the update and then bombed.

    UPDATE 2: The following field types also kill the forms:

    • Floating Point Number
    • Whole Number
    • Decimal Number
    • Currency

    So apparently ANY field type with integers kills the forms. I have code from a field from a report that bombs:

    <SortExpression>=Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(Fields!pps_pps_aop_pkgrec1_date_receivedValue.Value, Parameters!CRM_UserTimeZoneName.Value)</SortExpression>


    • Edited by nasyrax Wednesday, June 11, 2014 8:35 PM
    Wednesday, June 11, 2014 12:57 PM
  • Hi Nasyrax,

    There appears to be another user on the forums experiencing the same issues;

    http://social.microsoft.com/Forums/en-US/e221a0fe-c168-4bb5-ad2e-86dd813204c0/report-with-a-date-or-number-field-fails-with-rsruntimeerrorinexpression?forum=crm

    This could mean that potentially it was a bug introduced by Microsoft in UR17.

    I'll let you know if I hear or think of anything else. :)

    ~ Atomic Coder


    Thursday, June 12, 2014 7:14 AM
  • if you can put this report in SSRS and check Hidden expression for the tablix ‘Table0’ , this expression is failing and throwing exception so verify all expression on table0 in report and this will help to fix this issue.

    MayankP
    My Blog
    Follow Me on Twitter

    Thursday, June 12, 2014 9:30 AM
    Answerer
  • Here are the contents of Table0:

    <Table Name="Table0">
        <DataSetName>DSMain</DataSetName>
        <DataElementOutput>Output</DataElementOutput>
        <NoRows>="No Data"</NoRows>
        <Left>4.349in</Left>
        <Top>1.25in</Top>
        <Style>
            <TextAlign>Left</TextAlign>
        </Style>
        <TableColumns>
            <TableColumn>
                <Width>0.781in</Width>
            </TableColumn>
            <TableColumn>
                <Width>0.521in</Width>
                <Visibility>
                    <Hidden>=(Globals!RenderFormat.Name = "EXCEL" or Globals!RenderFormat.Name = "EXCELOPENXML")</Hidden>
                </Visibility>
            </TableColumn>
            <TableColumn>
                <Width>0.521in</Width>
                <Visibility>
                    <Hidden>=(Globals!RenderFormat.Name = "PDF" or Globals!RenderFormat.Name = "IMAGE" or Globals!RenderFormat.Name = "WORD" or Globals!RenderFormat.Name = "WORDOPENXML" or Globals!RenderFormat.IsInteractive)</Hidden>
                </Visibility>
            </TableColumn>
        </TableColumns>
        <Header>
            <RepeatOnNewPage>true</RepeatOnNewPage>
            <TableRows>
                <TableRow>
                    <Height>.25in</Height>
                    <TableCells>
                        <TableCell>
                            <ReportItems>
                                <Textbox Name="Table0_Header0">
                                    <CanGrow>true</CanGrow>
                                    <Value>="Project Div ID"</Value>
                                    <Style>
                                        <BackgroundColor>#4f81bd</BackgroundColor><Color>#ffffff</Color><FontFamily>Tahoma</FontFamily><FontSize>8pt</FontSize><FontWeight>700</FontWeight><Language>en-US</Language><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><TextAlign>Center</TextAlign>
                                    </Style>
                                    <UserSort>
                                        <SortExpression>=Fields!pps_pps_aop_project_div.Value</SortExpression>
                                    </UserSort>
                                </Textbox>
                            </ReportItems>
                        </TableCell>
                        <TableCell>
                            <ReportItems>
                                <Textbox Name="Table0_Header1">
                                    <CanGrow>true</CanGrow>
                                    <Value>="Date Received"</Value>
                                    <Style>
                                        <BackgroundColor>#4f81bd</BackgroundColor><Color>#ffffff</Color><FontFamily>Tahoma</FontFamily><FontSize>8pt</FontSize><FontWeight>700</FontWeight><Language>en-US</Language><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><TextAlign>Center</TextAlign>
                                    </Style>
                                    <DataElementOutput>NoOutput</DataElementOutput>
                                    <UserSort>
                                        <SortExpression>=Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(Fields!pps_pps_aop_pkgrec1_date_receivedValue.Value, Parameters!CRM_UserTimeZoneName.Value)</SortExpression>
                                    </UserSort>
                                </Textbox>
                            </ReportItems>
                        </TableCell>
                        <TableCell>
                            <ReportItems>
                                <Textbox Name="Table0_Header2">
                                    <CanGrow>true</CanGrow>
                                    <Value>="Date Received"</Value>
                                    <Style>
                                        <BackgroundColor>#4f81bd</BackgroundColor><Color>#ffffff</Color><FontFamily>Tahoma</FontFamily><FontSize>8pt</FontSize><FontWeight>700</FontWeight><Language>en-US</Language><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><TextAlign>Center</TextAlign>
                                    </Style>
                                    <UserSort>
                                        <SortExpression>=Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(Fields!pps_pps_aop_pkgrec1_date_receivedValue.Value, Parameters!CRM_UserTimeZoneName.Value)</SortExpression>
                                    </UserSort>
                                </Textbox>
                            </ReportItems>
                        </TableCell>
                    </TableCells>
                </TableRow>
            </TableRows>
        </Header>
        <Details>
            <TableRows>
                <TableRow>
                    <Height>.25in</Height>
                    <TableCells>
                        <TableCell>
                            <ReportItems>
                                <Textbox Name="Table0_Details0">
                                    <CanGrow>true</CanGrow>
                                    <Value>=Fields!pps_pps_aop_project_div.Value</Value>
                                    <Action>
                                        <Hyperlink>=IIf(IsNothing(Fields!pps_aopfinaldbid.Value), Nothing, String.Format(System.Globalization.CultureInfo.InvariantCulture, "{0}?ID={1}&amp;LogicalName={2}", Parameters!CRM_URL.Value, Fields!pps_aopfinaldbid.Value, "pps_aopfinaldb"))</Hyperlink>
                                    </Action>
                                    <Style>
                                        <Language>=Microsoft.Crm.Reporting.RdlHelper.ReportCultureInfo.GetCultureName(Parameters!CRM_UILanguageId.Value)</Language><TextAlign>Left</TextAlign><FontFamily>Tahoma</FontFamily><FontSize>8pt</FontSize><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop>
                                    </Style>
                                </Textbox>
                            </ReportItems>
                        </TableCell>
                        <TableCell>
                            <ReportItems>
                                <Textbox Name="Table0_Details1">
                                    <CanGrow>true</CanGrow>
                                    <Value>=Fields!pps_pps_aop_pkgrec1_date_received.Value</Value>
                                    <Action>
                                        <Hyperlink>=Nothing</Hyperlink>
                                    </Action>
                                    <Style>
                                        <Calendar>=Parameters!CRM_CalendarTypeCode.Value</Calendar><Language>=Microsoft.Crm.Reporting.RdlHelper.ReportCultureInfo.GetCultureName(Parameters!CRM_UILanguageId.Value)</Language><TextAlign>Right</TextAlign><FontFamily>Tahoma</FontFamily><FontSize>8pt</FontSize><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop>
                                    </Style>
                                    <DataElementOutput>NoOutput</DataElementOutput>
                                </Textbox>
                            </ReportItems>
                        </TableCell>
                        <TableCell>
                            <ReportItems>
                                <Textbox Name="Table0_Details2">
                                    <CanGrow>true</CanGrow>
                                    <Value>=IIf(IsNothing(Fields!pps_pps_aop_pkgrec1_date_received.Value), Nothing, CDate(Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(Fields!pps_pps_aop_pkgrec1_date_receivedValue.Value, Parameters!CRM_UserTimeZoneName.Value)))</Value>
                                    <Action>
                                        <Hyperlink>=Nothing</Hyperlink>
                                    </Action>
                                    <Style>
                                        <Calendar>=Parameters!CRM_CalendarTypeCode.Value</Calendar><Language>=Microsoft.Crm.Reporting.RdlHelper.ReportCultureInfo.GetCultureName(Parameters!CRM_UILanguageId.Value)</Language><Format>=Parameters!CRM_ShortDatePattern.Value</Format><TextAlign>Right</TextAlign><FontFamily>Tahoma</FontFamily><FontSize>8pt</FontSize><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop>
                                    </Style>
                                </Textbox>
                            </ReportItems>
                        </TableCell>
                    </TableCells>
                </TableRow>
            </TableRows>
        </Details>
    </Table>

    • Edited by nasyrax Thursday, June 12, 2014 3:58 PM
    Thursday, June 12, 2014 12:20 PM