Formatting of reports from data base

Question

Formatting of reports from data base

ramprasadv

SSC Rookie

Points: 32
More actions
October 15, 2010 at 8:26 am

#229769

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.

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

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

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 1

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

Jay_Noob SSCommitted Points: 1565 More actions · Answer 2

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

ramprasadv SSC Rookie Points: 32 More actions · Answer 3

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.

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 4

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."

Peter Rijs Hall of Fame Points: 3400 More actions · Answer 5

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