SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Can I create Meta Data (e.g. Column Description)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/02/2013 :  10:28:42  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/02/2013 :  10:40:39  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 12/02/2013 :  12:47:20  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/02/2013 :  23:21:25  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 12/03/2013 :  11:01:15  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/04/2013 :  00:18:26  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 12/04/2013 :  03:16:01  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000