Parent-Child Hierarchies & User-defined hierarchies not appearing in Report Builder

Hi "Team SSAS",

I have this weird situation where Parent-Child hierarchies, and User-defined hierarchies show up in Visual Studio, but once I'vedeployed/processed the solution, they do not appear in Report Builder.

Is this a known product bug, or has someone else encountered this and have a solution, PLEASE?

Thanks in advance and in anticipation

Gregg Withers

gwithers atsymbol jhancock dotsymbol com

Hi Greg,

Report Builder can't handle parent/child hierarchies at all; with user-defined hierarchies it just shows the underlying attributes that make up each level if they themselves are visible. I know, it's a bit rubbish...

Take a look at this white paper for more information:

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssrs_reportmodel.mspx

HTH,

Chris

|||

Chris,

Thanks for your hyperlink to the intro to report models.

I'm having a tough time swallowing that answer - It just doesn't make sense that parent-child hierarchies are not available for ad-hoc reporting. I can't think of any decent-sized organization that does not need an parent-child type org chart. Also, if your answer also pertains to user-defined hierarchies, I have the same response.

Anyone else run into this problem, please?

TIA

Gregg

Parameters in Client Report Builder

Hello,

Is there a way for a regular user of the client report builder to add parameters to their reports? Right now you can only add them by creating a report from visual studio and publishing to the report server, which regular users can't do. Please let me know if I'm missing something here...

Thanks,
Edward

In the Report Builder filter dialog, click the name of the item you want the parameter on and select "Prompt". Example: if you have a condition Last Name = Smith, click on Last Name and select Prompt.
-- Robert||| I've been looking for a way to do that for ages.

Do you know if there is any way to get the prompt to be a drop down box?|||Great! Thanks! |||hey, I am also looking for drop down in report parameter. If you are having any kind of solution for displaying a dropdown list to choose a specific filter criteria on the fly will be a great help!

At present, I am using report parameter with free text which is not the exact solution need to be provided.

By selection of any list item, report will be displayed for specific list item.

e.g. Project name is my report parameter should have dropdown list with data list like "Project1", "Project2" etc. selection of "Project1" will display only Project1 related information in report.

Parameters for Report Builder

Is there a way to set parameters for a Report Model? I have a database that includes data for a lot of users and I want to be able to use a Report Model but only allow the user to see data related to their UserID when they are using Report Builder to build a report.

Is this at all possible? Would I have to set up some custom authentication?

Thanks for any help.

Please take a look at Model Item Security.

http://msdn2.microsoft.com/en-us/library/ms156505.aspx

|||Ok, I understand I can limit access to certain parts of a report or datasource, but I want to be able to filter data based on what user is doing the ad hoc querying. So I can do a Select * From tbl_UserData Where UserID = @.UserID. I want to be able to set this @.UserID paramater somehow, but not have the user select it.

Is this possible? Or would I need to set up a different report model or data view for each user who can do ad hoc querying and only allow users to use the report model assigned to them?

If this was explained in the link you posted, I'm sorry but I didn't see how.

Thanks for any help.|||

This post should help:

http://blogs.msdn.com/bobmeyers/articles/Implementing_Data_Security_in_a_Report_Model.aspx

Parameterized query?

Can someone please help me with this parameterized query? Its is not working.

builder.Append("select datepart(dd, datetime) as 'Day', datepart(hh,datetime) as 'Hour', count(*) as 'Count' ");
builder.Append("from @.table_name with (nolock) ");
builder.Append("where datetime > @.date_time ");
builder.Append("group by datepart(dd, datetime), datepart(hh, datetime) ");
builder.Append("order by datepart(dd, datetime), datepart(hh, datetime");
dateTime = DateTime.Now.ToLongTimeString() + " " + DateTime.Now.ToLongTimeString();
cmd.CommandType = CommandType.Text;
cmd.CommandText = builder.ToString();
cmd.Parameters.Add("@.table_name", tableName);
cmd.Parameters.Add("@.date_time", dateTime);

Please define "not working" and provide any error messages you are receiving.

I believe the problem is likely that you are attempting to use a parameter for the table name, which will not work and will generate an error from SQL Server. SQL Server does not provide for a variable as the table name in a query. You will instead need to append that table name as part of your string builder.

Terri|||Check out this tutorial on parameterized queries:
http://aspnet101.com/aspnet101/tutorials.aspx?id=1