No hard coded strings in reports

4 posts, 0 answers
  1. Paul
    Paul avatar
    9 posts
    Member since:
    Oct 2016

    Posted 13 Oct Link to this post

    Hi All,
       At present l'm trying to achieve a couple of things as a mini proof of concept for a project, one l can do, the other alludes me. Reports to be created by the standalone designer, which are then dropped into a directory, where any application that needs them can change any connection strings and then load them into their respective viewers, but with all reports having no hard coded strings.

    1. Create a report using the Standalone designer, then before rendering the report in a viewer have any / all SqlDatasources connection strings changed dynamically - OK (l can do this)

    2. Don't have any strings in the report hard coded i.e textboxes for displaying titles, column headers etc.

    I can do this by accessing the report in code, finding the appropriate item and settings its value before the report is rendered, however l would like to do this a bit more generically / the other way round; is there a way to set an item / collection on the report and then use an expression inside the standalone designer to bind say a textbox value to the an item in the collection (so it gets set at runtime), bearing in mind that the item may not have a datasource property for example a textbox that displays the name of a report on a page header.

    What l would really like to do, assuming l could set a collection of say reportText objects on the report (that have a LabelText property) would be to use some sort of expression in the designer (using c# as an example)
    textBoxTitle.Value = reportTextCollection.Where( item => item.controlID == "textBoxTitle").FirstOrDefault?.LabelText ?? "Default title value"
    Or
    textBoxTitle.Value = MyCustomExpressionAssembly.SetLabelText(controlID, reportTextCollection) // return see above

    Any thoughts help appreciated, Thank you
    Paul


  2. Paul
    Paul avatar
    9 posts
    Member since:
    Oct 2016

    Posted 14 Oct in reply to Paul Link to this post

    Anwering my own question, just incase it helps someone, here is what l have so far that meets my purpose and reports (not fully tested but seems to work fine) .
    I have a table (l use all the time in applications) that basically has a few fields such as ControlID, ScreenID, CultureID, LabelText, ToolTipText,  etc which holds all textual data for my applications the trick l use for my trial of Telerik reports (until l find something better, solution, not telerik) is to have the report datasource set to a query that basically says get the string values where ScreenID = 'ID of some report'.  My ControlID field holds textural id's such as textBoxName, labelDate etc and in order to be able to use these as a datasource with just a single row l pivot the table so l can get the ControlID field values as the Column Names with the LabelText values for the respective control i.e the pivot will produce:

    Column Names - textBoxFirstName, textBoxLastName, textBoxDateOfBirth
    Row values    "First name",     "Surname",       "Date of birth"

    Using the data explorer l just drag the apporiapriate item on to the report surface. For table headers where the table has its own datasource I just add a blank group to the detail section, and add the column headers in there using the report datasource (my controlid values) etc.

    Hope the above helps anyone, until a better anwser is provided
    Paul.
    P.S Some sql to get you started if you want to try this.

    Create Procedure aps.usp_GetReportText_ByScreenID
    (
    @ScreenID int
    )
    As
    SET FMTONLY OFF -- the magic ingredient for report designer / new to me
    Declare @ColumnNames nvarchar(max)
    Declare @ColumnSchema nvarchar(max)
    Declare @TableSql nvarchar(max)
    Declare @DataSql nvarchar(max)
    Select
    @ColumnNames = Coalesce(@ColumnNames + ',[' + cast(ControlNameID as nvarchar) + ']','[' + cast(ControlNameID as nvarchar)+ ']'),
    @ColumnSchema = Coalesce(@ColumnSchema + ',[' + cast(ControlNameID as nvarchar) + '] nvarchar(4000) Null','[' + cast(ControlNameID as nvarchar)+ '] nvarchar(4000) Null')
    From
    aps.ScreenTexts
    Where
    ScreenID = @ScreenID -- i.e 1 could be for Customer Report
    Group By
    ControlNameID

    Set @DataSql = N'Select ' + @ColumnNames + ' From (Select LabelText, ControlNameID From aps.ScreenTexts Where ScreenID = ' + Cast(@ScreenID As nvarchar) + ') As a Pivot (Min(LabelText) For ControlNameID In (' + @ColumnNames + ') ) As b'
    Create table #ReportScreenTexts(JustForCreate int)
    Set @TableSql = N'Alter Table #ReportScreenTexts Add ' + @ColumnSchema + '; Alter Table #ReportScreenTexts Drop Column JustForCreate;'
    Execute(@TableSql)
    Insert Into #ReportScreenTexts Execute(@DataSql)
    Select * From #ReportScreenTexts
    Return @@Error
    Go

  3. DevCraft banner
  4. Paul
    Paul avatar
    9 posts
    Member since:
    Oct 2016

    Posted 15 Oct in reply to Paul Link to this post

    Just to let anyone know reading this that may previous post does not work with regards to setting report parameter text. For me l'm going to use both approaches i.e do what l can in the standalone designer just so l can see stuff and then probably write a routine to enumerate all the controls / parameters and set any screen text to values in my table ScreenTexts in code before rendering.

    Amended the SQL to ditch the temp table as its not needed (forgot to remove before finding the FMTOnly option)

    ALTER Procedure [aps].[usp_GetReportText_ByScreenID]
    (
        @ScreenID int
    )
    As
    SET FMTONLY OFF -- the magic ingredient for report designer / new to me
    Declare @ColumnNames nvarchar(max)
    Declare @DataSql nvarchar(max) = N''
    Select
    @ColumnNames = Coalesce(@ColumnNames + ',[' + cast(ControlNameID as nvarchar) + ']','[' + cast(ControlNameID as nvarchar)+ ']')
    From
    aps.ScreenTexts
    Where
    ScreenID = @ScreenID -- i.e 1 could be for Customer Report
    Set @DataSql = N'Select ' + @ColumnNames + ' From (Select LabelText, ControlNameID From aps.ScreenTexts Where ScreenID = ' + Cast(@ScreenID As nvarchar) + ') As a Pivot (Min(LabelText) For ControlNameID In (' + @ColumnNames + ') ) As b'
    Execute(@DataSql)
    Return @@Error

  5. Katia
    Admin
    Katia avatar
    303 posts

    Posted 18 Oct Link to this post

    Hello Paul,

    It is possible to extend the built-in expression engine with your custom functions. In order to load the custom functions in Standalone Designer, the external assembly needs to be referenced in assemblyReferences element of the Telerik.Reporting configuration section - check Extending Report Designer and How to use external assemblies with custom user functions in the Report Designer articles.

    Note, that ReportParameter.Text property does not support expression as its value - Expressions as Values of Item Properties. Thus, the value needs to be hard-coded if you work with TRDP(TRDX) reports or changed at runtime if you work with report type definition.


    Regards,
    Katia
    Telerik by Progress
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top