Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Can I create Meta Data (e.g. Column Description)

Author  Topic 

Kristen
Test

22859 Posts

Posted - 2013-12-02 : 10:28:42
I'm new to SSRS. If I pick some columns from a table to appear on my report why doesn't it take the Meta Column Descriptions from the Table Definition for the column headings?

And if it can, in some way, how would that work with VIEWs? (which don't seem to have any such Column Meta Descriptions)

Hope the question is clear, I may have asked it in the wrong way - the answer may come at the problem from a completely different angle.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-02 : 10:40:39
the column names in report are taken from column names in dataset used in report. so they may be same as table or same as alias used in query/proc used for dataset.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2013-12-02 : 12:47:20
Thanks Visakh.

Presumably no easy way to have Aliases pre-defined within SSRS?

Only way I can think of is to have VIEWs:

SELECT [Pretty name] = ActualColumnName

and my immediate probably with that is that they won't easily be unique.

Plus we have a naming convention where a table is "XXX_YYY_TableName" and the columns are "xxx_yyy_ID", "xxx_yyy_Description" etc. such that a foreign key in another table "AAA_BBB_SomeName" would be "aaa_bbb_xxx_yyy_ID" (which joins to xxx_yyy_ID :) ) and if I rename those to "ID" using the Alias in the VIEW then the whole benefit of knowing which ID's join "naturally" would be lost for the numpty users that will be building reports.

I'm a bit amazed to find that this isn't in-built as clients we have would much prefer that everything was described "Uniformly", so a central repository of descriptions would be far preferable to each user giving them descriptive names "at will" :(
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-02 : 23:21:25
quote:
Originally posted by Kristen

Thanks Visakh.

Presumably no easy way to have Aliases pre-defined within SSRS?

Only way I can think of is to have VIEWs:

SELECT [Pretty name] = ActualColumnName

and my immediate probably with that is that they won't easily be unique.

Plus we have a naming convention where a table is "XXX_YYY_TableName" and the columns are "xxx_yyy_ID", "xxx_yyy_Description" etc. such that a foreign key in another table "AAA_BBB_SomeName" would be "aaa_bbb_xxx_yyy_ID" (which joins to xxx_yyy_ID :) ) and if I rename those to "ID" using the Alias in the VIEW then the whole benefit of knowing which ID's join "naturally" would be lost for the numpty users that will be building reports.

I'm a bit amazed to find that this isn't in-built as clients we have would much prefer that everything was described "Uniformly", so a central repository of descriptions would be far preferable to each user giving them descriptive names "at will" :(


Yep..thats true
First time you execute code behind ie view/proc the metadata gets created in report for the dataset. any further changes in metadata requires you to change the query behind by adding aliases. You may extend the available fields to add computed columns though in the dataset but you cant redefine the names of columns in dataset.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2013-12-03 : 11:01:15
quote:
Originally posted by visakh16


Yep..thats true




quote:
any further changes in metadata requires you to change the query behind by adding aliases


I tried adding an Alias - it broke the report (template said "[xxx_yyy_ColumnName]" which no longer existed as my SQL then said

SELECT xxx_yyy_ColumnName AS [Pretty Name]


It all looks very nice and colourful (genuinely), but I think its going to be easier to add "Select any columns you want" and "Output to XLS" to our application, rather than letting a bunch of naive users loose with SSRS.

Or am I missing some golden opportunity that is staring me in the face?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-04 : 00:18:26
quote:
Originally posted by Kristen

quote:
Originally posted by visakh16


Yep..thats true




quote:
any further changes in metadata requires you to change the query behind by adding aliases


I tried adding an Alias - it broke the report (template said "[xxx_yyy_ColumnName]" which no longer existed as my SQL then said

SELECT xxx_yyy_ColumnName AS [Pretty Name]


It all looks very nice and colourful (genuinely), but I think its going to be easier to add "Select any columns you want" and "Output to XLS" to our application, rather than letting a bunch of naive users loose with SSRS.

Or am I missing some golden opportunity that is staring me in the face?


As i told before once you set metadata and used in report you cant change it.
If you want to change afterwards you need to refresh dataset and change any references within the report for old field to the new field.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2013-12-04 : 03:16:01
Yup, I had realised that, but I just think it is daft that they designed it that way. Opportunity lost IMHO.
Go to Top of Page
   

- Advertisement -