SSRS: Issue with Refresh Field in Dataset properties

user_15072520 0
Viewed 288

I'm modifying the existing .rdl report by adding a new field in the expression builder. When I click OK in Expression builder and Refresh Fields in Dataset properties there is no that newly added field. I'm using Visual Studio 2019.

I was trying:

  1. copying existing query in the new Dataset in the same report (didn't show/refresh any field)
  2. delete .rdl.data file
  3. copy the entire .rdl report
  4. create new .rdl report and only copy the query for the dataset, connection to the database and parameters (didn't show/refresh any field)

In every option, I was trying:

  • Copying the existing query without modification
  • Copying query with newly added field
  • Modifying the existing query by deleting a field

Is there another way to refresh fields? Or am I doing something wrong?

In expression builder is a message "No issues found" and I think that query is not wrong. But still, my query look like this:

="with stat(NASIF, ZUPANIJA, OPCINA, MBZUP, MBGO) as (SELECT distinct cast(nas.NAS_MB as int), nas.ZUPANIJA_NAZIV, nas.NAZIV_GO, nas.MB_ZUP, nas.MB_GO  FROM [sde].[sde].[REP_STAT_NASELJA] nas) SELECT "
&
Switch(Parameters!Grouping.Value = 1, "'RH' as NASRazina, "
      ,Parameters!Grouping.Value = 2, "stat.ZUPANIJA as NASRazina, "
      ,Parameters!Grouping.Value = 3, "stat.OPCINA as NASRazina, "
      ,Parameters!Grouping.Value = 4, "isnull(street.GEOCODE_NASELJE, 'Nepoznato') as NASRazina, stat.ZUPANIJA as Zupanija, ")
&
"street.OPERATER, CAST (sum(street.SumOfiEOP_2_4) AS INT) AS SumOfiEOP_2_4, CAST (sum(street.SumOfiEOP_4_10) AS INT) AS SumOfiEOP_4_10, CAST (sum(street.SumOfiEOP_10_20) AS INT) AS SumOfiEOP_10_20, CAST (sum(street.SumOfiEOP_20_30) AS INT) AS SumOfiEOP_20_30, CAST (sum(street.SumOfiEOP_30_50) AS INT) AS SumOfiEOP_30_50, CAST (sum(street.SumOfiEOP_50_100) AS INT) AS SumOfiEOP_50_100, CAST (sum(street.SumOfiEOP_100) AS INT) AS SumOfiEOP_100, CAST(sum(street.SumOfIBrzinaNijeUgovorena) AS INT) AS SumOfIBrzinaNijeUgovorena, street.kvartal, sum(street.SumOfiEOP_2_4 + street.SumOfiEOP_4_10 + street.SumOfiEOP_10_20 + street.SumOfiEOP_20_30 + street.SumOfiEOP_30_50+ street.SumOfiEOP_50_100 + street.SumOfiEOP_100 + street.SumOfIBrzinaNijeUgovorena) as 'UkupnoPRIKLJ' " 
&
"from sde.REP_BR_UG_KOR_OPERATOR_NAS street left join stat on street.GEOCODE_SIFNAS = stat.NASIF "
&
" where (street.kvartal in (@Kvartal) or 'Svi' in (@Kvartal)) and (street.OPERATER in (@Operater) or 'Svi' in (@Operater)) and (stat.MBGO in (@GradOpcina) or 0 in (@GradOpcina)) "
&
" and (isnull(street.GEOCODE_NASELJE,'') like '%'+@Naselje+'%') and (stat.MBZUP in (@Zupanija) or 0 in (@Zupanija)) "
& 
"group by street.OPERATER, street.kvartal "
&
Switch(Parameters!Grouping.Value = 2, ", stat.ZUPANIJA"
      ,Parameters!Grouping.Value = 3, ", stat.OPCINA"
      ,Parameters!Grouping.Value = 4, ", isnull(street.GEOCODE_NASELJE, 'Nepoznato'), stat.ZUPANIJA")

New field is: CAST(sum(street.SumOfIBrzinaNijeUgovorena) AS INT) AS SumOfIBrzinaNijeUgovorena

reporting-services
1 Answers

Alan is correct, the dynamic script you have for the query will not refresh the fields.

The following query will refresh the fields and can be run in SSMS for testing:

WITH 
stat
AS 
(
   SELECT DISTINCT 
        [NASIF] = CAST([NAS_MB] AS INT)
      , [ZUPANIJA] = [ZUPANIJA_NAZIV]
      , [OPCINA] = [NAZIV_GO]
      , [MBZUP] = [MB_ZUP]
      , [MBGO] = [MB_GO]
   FROM 
      [sde].[REP_STAT_NASELJA] 
)
SELECT
   [NASRazina] = 
      CASE
         WHEN @Grouping = 1 THEN 'RH'
         WHEN @Grouping = 2 THEN [stat].[ZUPANIJA]
         WHEN @Grouping = 3 THEN [stat].[OPCINA]
         WHEN @Grouping = 4 THEN ISNULL([street].[GEOCODE_NASELJE], 'Nepoznato')
      END
   , [Zupanija] = CASE WHEN @Grouping = 4 THEN [stat].[ZUPANIJA] ELSE NULL END
   , [street].[OPERATER]
   , [SumOfiEOP_2_4] = CAST(SUM([street].[SumOfiEOP_2_4]) AS INT)
   , [SumOfiEOP_4_10] = CAST(SUM([street].[SumOfiEOP_4_10]) AS INT)  
   , [SumOfiEOP_10_20] = CAST(SUM([street].[SumOfiEOP_10_20]) AS INT)  
   , [SumOfiEOP_20_30] = CAST(SUM([street].[SumOfiEOP_20_30]) AS INT)  
   , [SumOfiEOP_30_50] = CAST(SUM([street].[SumOfiEOP_30_50]) AS INT)  
   , [SumOfiEOP_50_100] = CAST(SUM([street].[SumOfiEOP_50_100]) AS INT)  
   , [SumOfiEOP_100] = CAST(SUM([street].[SumOfiEOP_100]) AS INT)  
   , [SumOfIBrzinaNijeUgovorena] = CAST(SUM([street].[SumOfIBrzinaNijeUgovorena]) AS INT)  
   , [street].[kvartal]
   , [UkupnoPRIKLJ] = SUM([street].[SumOfiEOP_2_4] + [street].[SumOfiEOP_4_10] + [street].[SumOfiEOP_10_20] + [street].[SumOfiEOP_20_30] + [street].[SumOfiEOP_30_50] + [street].[SumOfiEOP_50_100] + [street].[SumOfiEOP_100] + [street].[SumOfIBrzinaNijeUgovorena])
FROM
   [sde].[REP_BR_UG_KOR_OPERATOR_NAS] AS [street]
   LEFT JOIN [stat] ON [street].[GEOCODE_SIFNAS] = [stat].[NASIF]
WHERE
   1=1
   AND ([street].[kvartal] IN(@Kvartal) OR 'Svi' IN(@Kvartal))
   AND ([street].[OPERATER] IN(@Operater) OR 'Svi' IN(@Operater))
   AND ([stat].[MBGO] IN(@GradOpcina) OR 0 IN(@GradOpcina))
   AND (ISNULL([street].[GEOCODE_NASELJE], '') LIKE '%' + @Naselje + '%')
   AND ([stat].[MBZUP] IN(@Zupanija) OR 0 IN(@Zupanija))
GROUP BY 
      CASE
         WHEN @Grouping = 1 THEN 'RH'
         WHEN @Grouping = 2 THEN [stat].[ZUPANIJA]
         WHEN @Grouping = 3 THEN [stat].[OPCINA]
         WHEN @Grouping = 4 THEN ISNULL([street].[GEOCODE_NASELJE], 'Nepoznato')
      END
   , CASE WHEN @Grouping = 4 THEN [stat].[ZUPANIJA] ELSE NULL END
   , [street].[OPERATER]
   , [street].[kvartal];

Also, it looks like you're doing some aging groups in one of your tables/views.

Here's how I do mine so I can change the groupings quickly. Then you can use a matrix in SSRS to view it. It makes your report a bit more dynamic.

DECLARE @AgingDate AS DATE = GETDATE(); 

WITH
test_data
AS
(
   SELECT tbl.* FROM (VALUES
    ( '08-Jan-2022', 458.58)
   , ( '07-Jan-2022', 756.47)
   , ( '01-Jan-2022', 853.45)
   , ( '22-Dec-2021', 756.94)
   , ( '12-Dec-2021', 8754.53)
   , ( '22-Nov-2021', 456.55)
   , ( '03-Oct-2021', 887.14)
   , ( '03-May-2021', 295.67)
   ) tbl ([TRAN_DATE], [AMOUNT]) 
)
,
aging_metric
AS
(
   SELECT 
      tbl.*
      , [date_min] = DATEADD(DAY, -[days_min], @AgingDate)
      , [date_max] = DATEADD(DAY, -[days_max], @AgingDate)
      , [metric_label] =
         CASE
            WHEN [days_max] IS NULL THEN '> ' + CONVERT([VARCHAR], [days_min])
            ELSE (CONVERT([VARCHAR], [days_min]) + '-') + CONVERT([VARCHAR], [days_max])
         END + ' Days'
   FROM (VALUES
    ( 2, 4) -- might be a good idea to store this in a table if you're going to use it more than once
   , ( 4, 10)
   , ( 10, 20)
   , ( 20, 30)
   , ( 30, 50)
   , ( 50, 100)
   , ( 100, NULL)
   ) tbl ([days_min], [days_max]) 
)
SELECT 
    am.[metric_label] -- you can use this as a column group in a SSRS matrix
   , am.[days_min] -- sort on this in the column grouping
   , am.[date_min]
   , am.[date_max] 
   , [d].[Amount]
FROM
   test_data AS [d] 
   LEFT JOIN aging_metric AS am ON [d].[TRAN_DATE] < am.[date_min] AND [d].[TRAN_DATE] >= ISNULL(am.[date_max], [d].[TRAN_DATE]);

SSMS example:

enter image description here

SSRS matrix example:

enter image description here

SSRS RDL XML code:

<?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>Calibri</df:DefaultFontFamily>
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="SQL_DATA">
      <DataSourceReference>SQL_DATA</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID>a5d1b78c-77c9-47b2-9891-17332e03f92f</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="Detail">
      <Query>
        <DataSourceName>SQL_DATA</DataSourceName>
        <CommandText>    DECLARE @AgingDate AS DATE = GETDATE(); 

    WITH
    test_data
    AS
    (
       SELECT tbl.* FROM (VALUES
        ( '08-Jan-2022', 458.58)
       , ( '07-Jan-2022', 756.47)
       , ( '01-Jan-2022', 853.45)
       , ( '22-Dec-2021', 756.94)
       , ( '12-Dec-2021', 8754.53)
       , ( '22-Nov-2021', 456.55)
       , ( '03-Oct-2021', 887.14)
       , ( '03-May-2021', 295.67)
       ) tbl ([TRAN_DATE], [AMOUNT]) 
    )
    ,
    aging_metric
    AS
    (
       SELECT 
          tbl.*
          , [date_min] = DATEADD(DAY, -[days_min], @AgingDate)
          , [date_max] = DATEADD(DAY, -[days_max], @AgingDate)
          , [metric_label] =
             CASE
                WHEN [days_max] IS NULL THEN '&gt; ' + CONVERT([VARCHAR], [days_min])
                ELSE (CONVERT([VARCHAR], [days_min]) + '-') + CONVERT([VARCHAR], [days_max])
             END + ' Days'
       FROM (VALUES
        ( 2, 4) --might be a good idea to store this in a table if you're going to use it more than once
       , ( 4, 10)
       , ( 10, 20)
       , ( 20, 30)
       , ( 30, 50)
       , ( 50, 100)
       , ( 100, NULL)
       ) tbl ([days_min], [days_max]) 
    )
    SELECT 
        am.[metric_label] -- you can use this as a column group in a SSRS matrix
       , am.[days_min]
       , am.[date_min]
       , am.[date_max] 
       , [d].[Amount]
    FROM
       test_data AS [d] 
       LEFT JOIN aging_metric AS am ON [d].[TRAN_DATE] &lt; am.[date_min] AND [d].[TRAN_DATE] &gt;= ISNULL(am.[date_max], [d].[TRAN_DATE]);</CommandText>
      </Query>
      <Fields>
        <Field Name="metric_label">
          <DataField>metric_label</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="days_min">
          <DataField>days_min</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="date_min">
          <DataField>date_min</DataField>
          <rd:TypeName>System.DateTime</rd:TypeName>
        </Field>
        <Field Name="date_max">
          <DataField>date_max</DataField>
          <rd:TypeName>System.DateTime</rd:TypeName>
        </Field>
        <Field Name="Amount">
          <DataField>Amount</DataField>
          <rd:TypeName>System.Decimal</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
  </DataSets>
  <ReportSections>
    <ReportSection>
      <Body>
        <ReportItems>
          <Tablix Name="Tablix1">
            <TablixCorner>
              <TablixCornerRows>
                <TablixCornerRow>
                  <TablixCornerCell>
                    <CellContents>
                      <Textbox Name="Textbox4">
                        <CanGrow>true</CanGrow>
                        <KeepTogether>true</KeepTogether>
                        <Paragraphs>
                          <Paragraph>
                            <TextRuns>
                              <TextRun>
                                <Value>Group</Value>
                                <Style>
                                  <FontSize>9pt</FontSize>
                                  <Color>White</Color>
                                </Style>
                              </TextRun>
                            </TextRuns>
                            <Style>
                              <TextAlign>Center</TextAlign>
                            </Style>
                          </Paragraph>
                        </Paragraphs>
                        <rd:DefaultName>Textbox4</rd:DefaultName>
                        <Style>
                          <Border>
                            <Color>LightGrey</Color>
                            <Style>Solid</Style>
                          </Border>
                          <BackgroundColor>#005f83</BackgroundColor>
                          <VerticalAlign>Middle</VerticalAlign>
                          <PaddingLeft>2pt</PaddingLeft>
                          <PaddingRight>2pt</PaddingRight>
                          <PaddingTop>2pt</PaddingTop>
                          <PaddingBottom>2pt</PaddingBottom>
                        </Style>
                      </Textbox>
                    </CellContents>
                  </TablixCornerCell>
                </TablixCornerRow>
              </TablixCornerRows>
            </TablixCorner>
            <TablixBody>
              <TablixColumns>
                <TablixColumn>
                  <Width>1.57396cm</Width>
                </TablixColumn>
              </TablixColumns>
              <TablixRows>
                <TablixRow>
                  <Height>0.6cm</Height>
                  <TablixCells>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="Amount">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>=Sum(Fields!Amount.Value)</Value>
                                  <Style>
                                    <FontSize>9pt</FontSize>
                                    <Format>#,0;(#,0)</Format>
                                    <Color>=IIF(Me.Value &lt; 0, "Red", "Black")</Color>
                                  </Style>
                                </TextRun>
                              </TextRuns>
                              <Style />
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>Amount</rd:DefaultName>
                          <Style>
                            <Border>
                              <Color>LightGrey</Color>
                              <Style>Solid</Style>
                            </Border>
                            <BackgroundColor>White</BackgroundColor>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                        </Textbox>
                      </CellContents>
                    </TablixCell>
                  </TablixCells>
                </TablixRow>
              </TablixRows>
            </TablixBody>
            <TablixColumnHierarchy>
              <TablixMembers>
                <TablixMember>
                  <Group Name="date">
                    <GroupExpressions>
                      <GroupExpression>=Fields!metric_label.Value</GroupExpression>
                    </GroupExpressions>
                  </Group>
                  <SortExpressions>
                    <SortExpression>
                      <Value>=Fields!days_min.Value</Value>
                    </SortExpression>
                  </SortExpressions>
                  <TablixHeader>
                    <Size>2.08166cm</Size>
                    <CellContents>
                      <Textbox Name="metric_label">
                        <CanGrow>true</CanGrow>
                        <KeepTogether>true</KeepTogether>
                        <Paragraphs>
                          <Paragraph>
                            <TextRuns>
                              <TextRun>
                                <Value>=Fields!metric_label.Value</Value>
                                <Style>
                                  <FontSize>9pt</FontSize>
                                  <Format>MMMM</Format>
                                  <Color>White</Color>
                                </Style>
                              </TextRun>
                            </TextRuns>
                            <Style>
                              <TextAlign>Left</TextAlign>
                            </Style>
                          </Paragraph>
                        </Paragraphs>
                        <rd:DefaultName>metric_label</rd:DefaultName>
                        <Style>
                          <Border>
                            <Color>LightGrey</Color>
                            <Style>Solid</Style>
                          </Border>
                          <BackgroundColor>#005f83</BackgroundColor>
                          <VerticalAlign>Middle</VerticalAlign>
                          <PaddingLeft>2pt</PaddingLeft>
                          <PaddingRight>2pt</PaddingRight>
                          <PaddingTop>2pt</PaddingTop>
                          <PaddingBottom>4pt</PaddingBottom>
                          <WritingMode>Rotate270</WritingMode>
                        </Style>
                      </Textbox>
                    </CellContents>
                  </TablixHeader>
                </TablixMember>
              </TablixMembers>
            </TablixColumnHierarchy>
            <TablixRowHierarchy>
              <TablixMembers>
                <TablixMember>
                  <Group Name="text">
                    <GroupExpressions>
                      <GroupExpression>=""</GroupExpression>
                    </GroupExpressions>
                  </Group>
                  <TablixHeader>
                    <Size>2.5cm</Size>
                    <CellContents>
                      <Textbox Name="text1">
                        <CanGrow>true</CanGrow>
                        <KeepTogether>true</KeepTogether>
                        <Paragraphs>
                          <Paragraph>
                            <TextRuns>
                              <TextRun>
                                <Value>=""</Value>
                                <Style>
                                  <FontSize>9pt</FontSize>
                                </Style>
                              </TextRun>
                            </TextRuns>
                            <Style />
                          </Paragraph>
                        </Paragraphs>
                        <rd:DefaultName>text1</rd:DefaultName>
                        <Style>
                          <Border>
                            <Color>LightGrey</Color>
                            <Style>Solid</Style>
                          </Border>
                          <BackgroundColor>White</BackgroundColor>
                          <PaddingLeft>2pt</PaddingLeft>
                          <PaddingRight>2pt</PaddingRight>
                          <PaddingTop>2pt</PaddingTop>
                          <PaddingBottom>2pt</PaddingBottom>
                        </Style>
                      </Textbox>
                    </CellContents>
                  </TablixHeader>
                </TablixMember>
              </TablixMembers>
            </TablixRowHierarchy>
            <DataSetName>Detail</DataSetName>
            <Height>2.68166cm</Height>
            <Width>4.07396cm</Width>
            <Style>
              <Border>
                <Style>None</Style>
              </Border>
            </Style>
          </Tablix>
        </ReportItems>
        <Height>2.68166cm</Height>
        <Style />
      </Body>
      <Width>4.07396cm</Width>
      <Page>
        <PageHeight>21cm</PageHeight>
        <PageWidth>29.7cm</PageWidth>
        <LeftMargin>0.25cm</LeftMargin>
        <RightMargin>0.25cm</RightMargin>
        <TopMargin>0.25cm</TopMargin>
        <BottomMargin>0.25cm</BottomMargin>
        <ColumnSpacing>0.13cm</ColumnSpacing>
        <Style />
      </Page>
    </ReportSection>
  </ReportSections>
  <ReportParametersLayout>
    <GridLayoutDefinition>
      <NumberOfColumns>2</NumberOfColumns>
      <NumberOfRows>2</NumberOfRows>
    </GridLayoutDefinition>
  </ReportParametersLayout>
  <rd:ReportUnitType>Cm</rd:ReportUnitType>
  <rd:ReportID>96c3ec07-9609-4982-aeb1-6ac8d4426666</rd:ReportID>
</Report>
user_90594240
Related