Formatting of reports from data base

  • Hi all,

    i am working with 3000+ reports, and it is a such a big pain when client asks for formatting changes like font color, ont style, alignment, italic.....etc.we tryed to manage in many ways...Finally we were able to control the report formatting from data base.Below is the solution.

    1. Create one table in your database with all possible required formatting fields.

    ex: table name: Report_style

    CREATE TABLE [dbo].[Report_Style](

    [Id] [int] NULL,

    [Header_font] [varchar](50) NULL,

    [Header_Color] [varchar](50) NULL,

    [Header_Size] [varchar](50) NULL,

    [Data_Font] [varchar](50) NULL,

    [Data_Color] [varchar](50) NULL,

    [Data_Size] [varchar](50) NULL,

    [Heading_Font] [varchar](50) NULL,

    [Heading_Color] [varchar](50) NULL,

    [Heading_Size] [varchar](50) NOT NULL,

    [Header_Style] [varchar](50) NULL,

    [Style] [varchar](50) NULL,

    [Effects] [varchar](50) NULL,

    [Note_Font] [varchar](50) NULL,

    [Note_Color] [varchar](50) NULL,

    [Note_size] [varchar](50) NULL,

    [Note_Effects] [varchar](50) NULL,

    [Note_Style] [varchar](50) NULL

    )

    2. See the sample data in the attachment

    3. Go to the report, add a new data set which will be as a result set of above table.

    select * from SS_Style.

    4. Go to the testbox/table properties, the we have expression for font , style , size.

    5. Use the field from data base column name as a input value for that.

    6. see attachment for reference.

    7. Next time onwords, just update the style in DB , the same will be reflect in all reports.

  • Hi RamPrasad,

    Thanks for the article. This indeed is a new approach. Please correct me if wrong, can you not support all the format and styling at UI level itself, thereby avoiding the need to access the DB for styling purpose.

    Raunak J

  • That's a great idea! I'm totally stealing that 😀

  • No we can not, that is the reason we are geting the values from DB. This is the easiest way which i found for all of them.

  • See Barry King's blog about this in more depth (multiple styles/etc): http://www.simple-talk.com/sql/reporting-services/reporting-services-with-style/[/url]

    Works great. If you want it in the header/footer, you have to pass the value to a parameter and grab it from the parameter instead of the dataset, but works like a charm.

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • This is indeed a simpler example than the ones I have seen before, as it requires no codebehind on the report.

    There is one pitfall however that should be noted: the "styles" are in column, which means that adding a new style means adding a column, which in turn will invalidate all existing reports that use the dataset coming from select * from SS_Style.

    Beside that, you can also use this approach to easily use several style-sets for the same report, for instance when you have 2 customers that require the same report in their own colours. I think that is the reason the CREATE TABLE statement from the OP has an id. Unfortunately that id is not used when filling the dataset, where it should be coupled with some other parameter to select the proper styleset.

    Apart from that: nice and easy working solution!

    Peter Rijs
    BI Consultant, The Netherlands

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply