RDL Header and Footer #Error issue with Shared Data Source and Dataset references

Anonymous — Wed, 21 Mar 2018 20:27:13 GMT

Ok, this issue is with the Power BI Report Server March 2018 release.

What we are seeing is that all of our reports that have logic like the following in the headers and footers are receiving an #Error when using a Shared Datasource and Shared Dataset reference in the report. It works fine when it is just local and embedded references.

The expression logic is like this in the textbox for the header:

=IIF(Parameters!Company.Label="All","Company Parent",Lookup(Parameters!Company.Label,Fields!CompanyNameShort.Value,Fields!CompanyName.Value,"dsCompany"))

and for the footer:

="This report includes the following states: "+ iif(Parameters!StateCode.Count=CountRows("dsParamValues"), "All States", join(Parameters!StateCode.Label,","))

Here is the sample RDL file code I created in Report Builder. This is using a Local SQL Instance. If you change it to a Shared Datasource reference and go to page 2 you will see that the Header switches to #Error and if you go back to page 1 you will see #Error as well.

If you switch the ParamValues dataset to a Shared Dataset and update the value list reference in the Parameter logic for StateCode (if the fields change for some reason) then you will see the #Error in the footer as shown in the screenshot above.

Please let me know how we should proceed with this and if there is anything else that I can provide.

Here is the RDL code since I can't do an attachment.

<?xml version="1.0" encoding="utf-8"?>
<Report MustUnderstand="df" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:df="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition/defaultfontfamily">
 <df:DefaultFontFamily>Segoe UI</df:DefaultFontFamily>
 <AutoRefresh>0</AutoRefresh>
 <DataSources>
 <DataSource Name="LocalSQL">
 <ConnectionProperties>
 <DataProvider>SQL</DataProvider>
 <ConnectString>Data Source=.;Initial Catalog=master</ConnectString>
 <IntegratedSecurity>true</IntegratedSecurity>
 </ConnectionProperties>
 <rd:SecurityType>Integrated</rd:SecurityType>
 <rd:DataSourceID>890fcf72-2863-4b83-888e-f1e757d9a71f</rd:DataSourceID>
 </DataSource>
 </DataSources>
 <DataSets>
 <DataSet Name="dsCompany">
 <Query>
 <DataSourceName>LocalSQL</DataSourceName>
 <CommandText>SELECT 'Company1' CompanyNameShort, 'Company1, LLC' CompanyName
UNION ALL
SELECT 'Company2' CompanyNameShort, 'Company2, Inc.' CompanyName
</CommandText>
 </Query>
 <Fields>
 <Field Name="CompanyNameShort">
 <DataField>CompanyNameShort</DataField>
 <rd:TypeName>System.String</rd:TypeName>
 </Field>
 <Field Name="CompanyName">
 <DataField>CompanyName</DataField>
 <rd:TypeName>System.String</rd:TypeName>
 </Field>
 </Fields>
 </DataSet>
 <DataSet Name="dsParamValues">
 <Query>
 <DataSourceName>LocalSQL</DataSourceName>
 <CommandText>SELECT 'AZ' StateCode, 'Arizona' StateName
UNION ALL
SELECT 'IA' StateCode, 'Iowa' StateName
UNION ALL
SELECT 'IL' StateCode, 'Illinois' StateName
UNION ALL
SELECT 'MN' StateCode, 'Minnesota' StateName
UNION ALL
SELECT 'SD' StateCode, 'South Dakota' StateName
UNION ALL
SELECT 'WI' StateCode, 'Wisconsin' StateName
</CommandText>
 </Query>
 <Fields>
 <Field Name="StateCode">
 <DataField>StateCode</DataField>
 <rd:TypeName>System.String</rd:TypeName>
 </Field>
 <Field Name="StateName">
 <DataField>StateName</DataField>
 <rd:TypeName>System.String</rd:TypeName>
 </Field>
 </Fields>
 </DataSet>
 </DataSets>
 <ReportSections>
 <ReportSection>
 <Body>
 <ReportItems>
 <Textbox Name="Textbox4">
 <CanGrow>true</CanGrow>
 <KeepTogether>true</KeepTogether>
 <Paragraphs>
 <Paragraph>
 <TextRuns>
 <TextRun>
 <Value>Page 2</Value>
 <Style />
 </TextRun>
 </TextRuns>
 <Style />
 </Paragraph>
 </Paragraphs>
 <rd:DefaultName>Textbox3</rd:DefaultName>
 <Top>0.83854in</Top>
 <Left>1.95708in</Left>
 <Height>0.25in</Height>
 <Width>1in</Width>
 <Style>
 <Border>
 <Style>None</Style>
 </Border>
 <PaddingLeft>2pt</PaddingLeft>
 <PaddingRight>2pt</PaddingRight>
 <PaddingTop>2pt</PaddingTop>
 <PaddingBottom>2pt</PaddingBottom>
 </Style>
 </Textbox>
 <Rectangle Name="Rectangle1">
 <ReportItems>
 <Textbox Name="Textbox3">
 <CanGrow>true</CanGrow>
 <KeepTogether>true</KeepTogether>
 <Paragraphs>
 <Paragraph>
 <TextRuns>
 <TextRun>
 <Value>Page 1</Value>
 <Style />
 </TextRun>
 </TextRuns>
 <Style />
 </Paragraph>
 </Paragraphs>
 <rd:DefaultName>Textbox3</rd:DefaultName>
 <Height>0.25in</Height>
 <Width>1in</Width>
 <Style>
 <Border>
 <Style>None</Style>
 </Border>
 <PaddingLeft>2pt</PaddingLeft>
 <PaddingRight>2pt</PaddingRight>
 <PaddingTop>2pt</PaddingTop>
 <PaddingBottom>2pt</PaddingBottom>
 </Style>
 </Textbox>
 </ReportItems>
 <PageBreak>
 <BreakLocation>End</BreakLocation>
 </PageBreak>
 <KeepTogether>true</KeepTogether>
 <Top>0.29167in</Top>
 <Left>1.95708in</Left>
 <Height>0.30035in</Height>
 <Width>1in</Width>
 <ZIndex>1</ZIndex>
 <Style>
 <Border>
 <Style>None</Style>
 </Border>
 </Style>
 </Rectangle>
 </ReportItems>
 <Height>1.51042in</Height>
 <Style>
 <Border>
 <Style>None</Style>
 </Border>
 </Style>
 </Body>
 <Width>6in</Width>
 <Page>
 <PageHeader>
 <Height>0.45708in</Height>
 <PrintOnFirstPage>true</PrintOnFirstPage>
 <PrintOnLastPage>true</PrintOnLastPage>
 <ReportItems>
 <Textbox Name="Textbox1">
 <CanGrow>true</CanGrow>
 <KeepTogether>true</KeepTogether>
 <Paragraphs>
 <Paragraph>
 <TextRuns>
 <TextRun>
 <Value>=IIF(Parameters!Company.Label="All","Company Parent",Lookup(Parameters!Company.Label,Fields!CompanyNameShort.Value,Fields!CompanyName.Value,"dsCompany"))</Value>
 <Style>
 <FontSize>14pt</FontSize>
 <FontWeight>Bold</FontWeight>
 </Style>
 </TextRun>
 </TextRuns>
 <Style>
 <TextAlign>Center</TextAlign>
 </Style>
 </Paragraph>
 </Paragraphs>
 <rd:DefaultName>Textbox1</rd:DefaultName>
 <Top>0.11333in</Top>
 <Left>0.69667in</Left>
 <Height>0.34375in</Height>
 <Width>4.82292in</Width>
 <Style>
 <Border>
 <Style>None</Style>
 </Border>
 <PaddingLeft>2pt</PaddingLeft>
 <PaddingRight>2pt</PaddingRight>
 <PaddingTop>2pt</PaddingTop>
 <PaddingBottom>2pt</PaddingBottom>
 </Style>
 </Textbox>
 </ReportItems>
 <Style>
 <Border>
 <Style>None</Style>
 </Border>
 </Style>
 </PageHeader>
 <PageFooter>
 <Height>0.46875in</Height>
 <PrintOnFirstPage>true</PrintOnFirstPage>
 <PrintOnLastPage>true</PrintOnLastPage>
 <ReportItems>
 <Textbox Name="ExecutionTime">
 <CanGrow>true</CanGrow>
 <KeepTogether>true</KeepTogether>
 <Paragraphs>
 <Paragraph>
 <TextRuns>
 <TextRun>
 <Value>=Globals!ExecutionTime</Value>
 <Style />
 </TextRun>
 </TextRuns>
 <Style>
 <TextAlign>Right</TextAlign>
 </Style>
 </Paragraph>
 </Paragraphs>
 <rd:DefaultName>ExecutionTime</rd:DefaultName>
 <Top>0.2in</Top>
 <Left>4in</Left>
 <Height>0.25in</Height>
 <Width>2in</Width>
 <Style>
 <Border>
 <Style>None</Style>
 </Border>
 <PaddingLeft>2pt</PaddingLeft>
 <PaddingRight>2pt</PaddingRight>
 <PaddingTop>2pt</PaddingTop>
 <PaddingBottom>2pt</PaddingBottom>
 </Style>
 </Textbox>
 <Textbox Name="Textbox2">
 <CanGrow>true</CanGrow>
 <KeepTogether>true</KeepTogether>
 <Paragraphs>
 <Paragraph>
 <TextRuns>
 <TextRun>
 <Value>="This report includes the following states: "+ iif(Parameters!StateCode.Count=CountRows("dsParamValues"), "All States", join(Parameters!StateCode.Label,","))</Value>
 <Style />
 </TextRun>
 </TextRuns>
 <Style />
 </Paragraph>
 </Paragraphs>
 <rd:DefaultName>Textbox2</rd:DefaultName>
 <Top>0.21875in</Top>
 <Left>0.13417in</Left>
 <Height>0.25in</Height>
 <Width>3.63542in</Width>
 <ZIndex>1</ZIndex>
 <Style>
 <Border>
 <Style>None</Style>
 </Border>
 <PaddingLeft>2pt</PaddingLeft>
 <PaddingRight>2pt</PaddingRight>
 <PaddingTop>2pt</PaddingTop>
 <PaddingBottom>2pt</PaddingBottom>
 </Style>
 </Textbox>
 </ReportItems>
 <Style>
 <Border>
 <Style>None</Style>
 </Border>
 </Style>
 </PageFooter>
 <LeftMargin>1in</LeftMargin>
 <RightMargin>1in</RightMargin>
 <TopMargin>1in</TopMargin>
 <BottomMargin>1in</BottomMargin>
 <Style />
 </Page>
 </ReportSection>
 </ReportSections>
 <ReportParameters>
 <ReportParameter Name="Company">
 <DataType>String</DataType>
 <DefaultValue>
 <Values>
 <Value>Company1</Value>
 </Values>
 </DefaultValue>
 <Prompt>Company</Prompt>
 <ValidValues>
 <ParameterValues>
 <ParameterValue>
 <Value>All</Value>
 <Label>All</Label>
 </ParameterValue>
 <ParameterValue>
 <Value>Company1</Value>
 <Label>Company1</Label>
 </ParameterValue>
 <ParameterValue>
 <Value>Company2</Value>
 <Label>Company2</Label>
 </ParameterValue>
 </ParameterValues>
 </ValidValues>
 </ReportParameter>
 <ReportParameter Name="StateCode">
 <DataType>String</DataType>
 <DefaultValue>
 <DataSetReference>
 <DataSetName>dsParamValues</DataSetName>
 <ValueField>StateCode</ValueField>
 </DataSetReference>
 </DefaultValue>
 <Prompt>StateCode</Prompt>
 <ValidValues>
 <DataSetReference>
 <DataSetName>dsParamValues</DataSetName>
 <ValueField>StateCode</ValueField>
 <LabelField>StateCode</LabelField>
 </DataSetReference>
 </ValidValues>
 <MultiValue>true</MultiValue>
 </ReportParameter>
 </ReportParameters>
 <ReportParametersLayout>
 <GridLayoutDefinition>
 <NumberOfColumns>4</NumberOfColumns>
 <NumberOfRows>2</NumberOfRows>
 <CellDefinitions>
 <CellDefinition>
 <ColumnIndex>0</ColumnIndex>
 <RowIndex>0</RowIndex>
 <ParameterName>Company</ParameterName>
 </CellDefinition>
 <CellDefinition>
 <ColumnIndex>1</ColumnIndex>
 <RowIndex>0</RowIndex>
 <ParameterName>StateCode</ParameterName>
 </CellDefinition>
 </CellDefinitions>
 </GridLayoutDefinition>
 </ReportParametersLayout>
 <rd:ReportUnitType>Inch</rd:ReportUnitType>
 <rd:ReportID>9cee2aa7-90bd-4ed3-a4b6-d8da79ab36a1</rd:ReportID>
</Report>

Re: RDL Header and Footer #Error issue with Shared Data Source and Dataset references

Anonymous — Wed, 21 Mar 2018 21:46:11 GMT

Thanks for reporting this! We are looking into it.

Re: RDL Header and Footer #Error issue with Shared Data Source and Dataset references

BenMackin — Fri, 30 Mar 2018 20:02:29 GMT

Anonymous this is very similar to my issue, with regards to using either Aggregate functions in header/footer, or using LOOKUP anywhere on a report.

See my post here:

http://community.powerbi.com/t5/Report-Server/PBRS-March-Update-breaking-existing-RDL-reports-that-use-LOOKUP/td-p/384067

The last information I got was they are aware of the issue, and have a promissing fix in the works.

Re: RDL Header and Footer #Error issue with Shared Data Source and Dataset references

topic Re: RDL Header and Footer #Error issue with Shared Data Source and Dataset references in Report Server

RDL Header and Footer #Error issue with Shared Data Source and Dataset references

Re: RDL Header and Footer #Error issue with Shared Data Source and Dataset references

Re: RDL Header and Footer #Error issue with Shared Data Source and Dataset references

Re: RDL Header and Footer #Error issue with Shared Data Source and Dataset references