Easy way for users to add new members or measures?

Hi I am wondering if there is an easy way for users to add new members or measures if the office dimension had a new office or if the user wanted to create a new calc?

I write enabled a dimension, but it had no effect in the BIDS or in Excel. I see the menu options change when browsing the write-enabled dimension in the BIDS, but "create sibling" and "create child" are disabled. I have full permissions and the criteria for those options to be enabled when writeback is enabled is met.

But even if write enabling was working, does it update the source data? From the books online, it seems like it would update dimension source data, but not measures or data points. which brings me to another question. Where do you enable writeback on measures and cubes? I don't see those properties or menu options.

Thanks

Hello. Write enabled dimensions are not client features, only server based. You can only use this in BI-Dev Studio.

When you add dimension members to a write enabled dimension these members are written to the source dimension table.

Write enabling a cube permits you to write to cells in the cube. To write enable a cube is a server based feature. The only way to activate this is in the Management Studio. You must have a client that supports writeback, not all do.

To add new measures to a cube requires a client that support this. ProClarity is one client that does support this but these calculated measures are not created in the cube but on a ProClarity server.

HTH

Thomas Ivarsson

|||

Hello Thomas,

Thanks for the response.

I found this in the books online:

Business users can update a write-enabled dimension by using client applications that support dimension writeback.

The following restrictions to dimension writeback apply:

When you create a new member, you must include every attribute in a dimension. You cannot insert a member without specifying a value for the key attribute of the dimension. Therefore, creating members is subject to any constraints (such as non-null key values) that are defined on the dimension table. You should also consider columns optionally specified by dimension properties, such as columns specified in the CustomRollupColumn, CustomRollupPropertiesColumn or the UnaryOperatorColumn dimension properties.

Dimension writeback is supported only for star schemas. In other words, a dimension must be based on a single dimension table directly related to a fact table. After you write-enable a dimension, Analysis Services validates this requirement when you deploy to an existing Analysis Services database or when you build an Analysis Services project.|||

Hello. I have not seen a client that directly support writeback to dimensions, yet. If you have found a client that supports this I would not recommend to use it because you will have no control of what will happen to your dimensions and your cube. I must admit that I have no good advice regarding this.

I have used dimension writeback to support the creation of account members that do not exist in the source system.

Your write enable a cube/partition in SQL Server Management Studio.

Regards

Thomas Ivarsson

|||Interesting that you have to enable cube or partition writeback outside of the BIDS. Thanks for the feedback!

Each user as database user

Good day!I'm trying to implement my own MemebershipProvider.Problem is, that different users should be different database users. Not each user.Any idea, how to solve this? Because only way how to use some database user I know it is to change connection string...

Why dont you use Windows Authentication and let the Active Directory take care of your memberships. Is this an internal application?

|||It is not.

I already wrote one post about it and I will tell it again.

Application is based on classicla win application. The same database. Problem is, that usual database is made for ONE company. But I need more of them in the web application.
Easy way how to do this is to create tables for each user. Usual dbo.Something will be user.Somethig for each user.
If you fire SELECT * FROM Something, it always goes against YOUR me.Something.

Elsewhere I would need to remake whole database and each upgrade would have to be made twice for desktop (one-company) databse and for mine.

Dynamically display/hide the parameter input

I have a handful of reports that are currently used by sales reps, and I'm trying to make them available to their regional VP's, and coporate users (executives and administrative staff that support Sales nationwide).

Currently, the reports take the UserID and resolve it to show the information that is only appropriate for that specific rep.

What I would like to do is have the parameter section at the top of the report be displayed for higher level users, so they could select an individual sales rep from a drop-down. (Ideally, the RVP's would only be able to select from reps in their region, but the corporate users would be able to select any rep.) The problem is, I don't want any of the sales reps to be able to select a rep other than themselves, for obvious reasons.

Is there a way to have the parameter section hidden/displayed dynamically, based on the UserID, so that users other than reps would have the ability to enter the desired rep name, but reps would not?

If you are using the .NET ReportView, it is just:

ReportViewer1.ShowParameterPrompts = False

|||I am using Report Manager.|||If you want any level of control, you are going to have to host the ReportViewer control in an ASP.NET page and customize the web page/report based on the user. There is not much you can do in ReportManager as far as fine control.|||

1. One way is to control this by writting a html page instead of report manager home page within which you will have a link to each of the report. Depending on who the user is you will change the path of the link (with or without parameters), meaning you will default all the parameters and pass them within the hidden link, but this wont work if you have more that 1 parameter and would want to hide only the user id parameter

2. Another option is to control this within the report. As your userid parameter list is being populated by a stored procedure, you will make the stored procedure accept a input parameter which is the userId of the person that is trying to run the report. Depending on who the user is the SP will return only the required users list.

Meaning

1. if XXX is passed and if XXX is RVP then it will return only the sales persons of XXX's region.

2. If YYY is passed and if YYY is sales person then it will return back only YYY

3. If CORP is passed and if CORP is a corporate user then the procedure will return all sales person's names

Hope this is clear and helps!!!

|||I think another way of achieving this is writting specific RDL code.|||

Thanks.

In the short term, this was the best solution. I wrote a sp that populates the drop down based on the NetLogin value.

So, the sales reps can see the drop down, but it only has their name in it.

|||You can also set the parameters visible in the URL using the &rsarameters=True/False.

i.e.

Code Snippet

http://SERVER/ReportServer/ReportPath&rs:Command=Render&rs:Parameters=False

HTH,
Jimmy

Dynamically determining the SQL statement?

Hello all,

I am working with a client who will be creating "template" report for users to be able to customize. The customization settings will be stored in meta data on a SQL Server somehere.

Part of the customization features will allow users to filter the data that they view. There may be 20 plus filters on any given report and they may want to filter in many different ways (i.e. sometimes to an Equals, other times an IN (), other times a LIKE, etc...), but only one way per field, per customization.

With this many possible filters, I want to avoid having the report authors be responsbile for coding these as parameters on the CommandText node (it is just using a SQL SELECT statement).

Well, let me get to the question...What is the best way to dynamically set the Report's CommandText or "main" dataset (and I guess while I am at it, the main datasource/connection information).

BTW...currently using SSRS 2005 + ASP.NET 2.0.

Thanks for your help!!

-BrianYou can create a function (sketch) like this:
public static string PrepareStatement(string templateSql, string userID)
{
string userFilters = GoGetUserPrefsFromDB(userID);
return templateSql + " WHERE " + userFilters;
}
Put it into a custom assembly, ref the assembly from your reports and have
<CommandText>=MyClass.PrepareStatement("select * from qqq", User!UserID)</CommandText>

Make sure you grant/assert CAS permissions.|||That is a good idea, the only problem with that I see is that it might be difficult to debug since the rendering engine is going to be responsible for creating the parameters...no to mention the client would have to maintain and deploy this assembly to each reporting server in the SSRS farm...

I guess the *best* way to set the command text and datasource from the front end is the use parameters?

Are there any other possible solutions?

Thanks!

-Brian

Dynamically Creating Columns

I have an .aspx page where users can select columns from a table. I want to pass the Select statement to a SQL Reporting Services report, probably as a stored procedure, and open the report with the records for the columns selected as an Excel file. My problem is that I am not sure how to dynamically create the column headings and values in a table. Any thoughts on how to achieve this? Thanks!

HI,amiejoye:

When do you want to dynamically create the column heading? In report data retrieving or other period?

|||

if you want to dynamically add headers then you can use code section in reporting service.

Else you can use drag and drop the fileds(clumn name ) that is comin from the dataset

|||

I want to create the column headings and retrieve the records when I click Submit from the .aspx page. Basically, when I click Submit, that's how I want the age to open, with the data from the Select statement I created.

|||

I think that should not be aproblem because the vb code section in the reporting service depends on the select query only

Dynamically change report grouping

Currently our users can filter data but they cannot manually group data
differently without us having to create them a new report with their
required group options.
Is there a way perhaps to hard code maybe 1 or 2 group levels in a reports
and then turn them off or turn them on dynamically depending on how the user
wanted to view the results.
Any help is appreciated.We use a 'Group By' parameter that the stored proc uses to do the
grouping. The results come back in the same format each time of
course, they're just grouped differently. This can sometimes lead to
some icky VB.NET conditionals inlined into the RDL, but in general it
has worked very well.|||Alternatively, group on an expression that is based on the value of a
parameter.
In our case, the client's requirements needed 6 reports but this
technique allowed us to give them one.
The stored proc must return a result set at a grouping level just above
the two alternative groupings.
BrianK
www.bolign.com

Dynamic Text Field

We have purchase orders that we are pulling from a MSSQL backend, and the report works fine.

However, in the subreport, the users would like to be prompted to enter up to 8 lines of text to be inserted into the report when they execute the report.

I am not sure if this is possible, or how to go about it. I tried using a parameter field, but there is simply not enough space for them to enter in info, and they have to enter multiple lines. I would like to make this as user friendly as possible.

We are using Crystal Reports 7, and may be upgrading to Crystal 11 by the end of the year.

Thank you in advance.Yes u can use parameter field, unfortunately, it does not display multiline but it can accomodate ur requirement, i used it at max length 65000 characters.

im using CRXI|||Actually, I figured out a neat way to do it.

You can create a Word or .rtf file in Wordpad or MSWord, and link it via OLE. That way, they can actually make the text look exactly the way they want it to on the report. It ends up looking really nice, and you can do all the shading and bordering in CR and it works fine.

I've used it in both the .rpt format and compiled it to .exe and they both work as long as the text they want to use was edited and saved before they open the .exe. I just trained the users to do this, and all is working great.

So after banging my head awhile, I finally saw through it and figured out this way.

Thank you for your help, I had tried that, but my users were upset by not being able to view their text. This was the next best way.

Dynamic Table Creation/Modification

I'm wondering if there is a control available for creating/modifying db tables through a web interface. I want for users to be able to add/remove, rename, and change the datatype of certain fields in a database table. I've been searching all day online if such a control exists in asp.net but haven't found anything.

The introduction to AJAX video centres around this very idea - he uses a GridView bound to a DataSet which is in turn bound to a SQL Express table. Anyway, you can view the video athttp://download.microsoft.com/download/7/8/f/78f2d61a-74c6-47b6-835c-0d1efa5524af/ScuttGu_asp_net_atlas.wmv. It's a good video to watch, anyway, very informative.

dynamic sqlconnection

I have a database for each of my customers and want to connect to a database depending on who is logged in.

all my users have their database name in the user profile.

how can i use the database name in the user profile to change the initial catalog to connect to aaother database?

thanks

There are 2 options: build the connection string for the user by uing the corresponding database name; or change to corresponding database in SqlCommand. For example:

string dbname="userdbname";

SqlConnection conn = new SqlConnection(@."Data Source=.;Integrated Security=SSPI;Database=" + dbname);

SqlCommand cmd = new SqlCommand("use " + dbname + ";select * from sysobjects", conn);

|||

You can do it probably ten different ways.

1) Don't use the data controls, and use a data access layer that abstracts the database calls, connections, opens, etc from your code, and place the logic to build a connection string on the fly into it.

2) Inherit all your pages from your own page class, that during initialization or load, it goes through all your controls recursively, and changes the connection string of all the datasource controls.

3) Write a function and that does the same as #2, and call it from each pages Page_Load.

4) Do all your data access manually and build your connection string as you see fit.

5) During log in, store the users connection string in a session variable, and always use that.

6) During authentication, store the users connection string in a session variable and always use that.

7) During page_load, build your connection string, and set all your data controls connection string to it.

8) Derive all your pages from your own page class, and during page_load recursively walk the pages control tree and set all sqldatasource's connection string to your own.

ways 9-10 to be filled on by other users.

|||Scratch option #4, lori beat me to it.|||

Thank you for the info i will try it and see how it goes.

Thanks

|||

How about doing it with a SqlDataSource object? I have the following on a page but keep getting an error. The error I'm getting is "incorrect syntax near '@.dbName'". The parameters @.dbName and @.userID are coming from two DropDownList objects which I know are working correctly in binding their data.

<asp:SqlDataSourceID="dsUserHistory"runat="server"ConnectionString="<%$ ConnectionStrings:portal1ConnectionString %>"SelectCommand="USE @.dbName SELECT loginDateStamp, logoutDateStamp FROM user_logins WHERE user_id = @.userID ORDER BY loginDateStamp">
<SelectParameters>
<asp:ControlParameterControlID="ddlUserHistoryCases"DefaultValue="portal1"Name="dbName"PropertyName="SelectedValue"Type="String"/>
<asp:ControlParameterControlID="ddlUserHistoryUsers"DefaultValue="1"Name="userID"PropertyName="SelectedValue"Type="String"/>
</SelectParameters>
</asp:SqlDataSource>

I've also tried setting the SelectCommand to "SELECT loginDateStamp, logoutDateStamp FROM @.dbName.dbo.user_logins...", but that doesn't work either obviously. Any ideas?

|||

In dbUserHistory_Selecting:

e.Command.CommandText="USE " & ddlUserHistoryCases.SelectedValue & " " &e.Command.CommandText

Dynamic SQL in Stored Proc

Environment:
Window2K workstation, SQL Server 2000 Vesion 8.00.760 (SP3)
Setup:
I have a database setup so that NO users (except dbo) have READ,
UPDATE, or DELETE access to my database. But I have a single role
called MySPUser that is granted EXECUTE access to all of my stored
procs that do all data access for the system. The MySPUser role has a
single user in that group called MyUser, which is a windows domain
level account. My Webserver then impersonates that user when it calls
the stored procs. This setup worked on both my development machine and
my development test machine.
Problem:
So everything was going great for about a year when my dev machine
crashed. When I rebuilt the box with the same software (os and sql
included) everything seemed to be working just fine. The impersonated
user can still call all the stored proc and either retrieve or update
data. The only problem is that I have 2 stored proc that require
Dynamic SQL and they have stopped working. I now receive the following
error message when executing one of the stored procs.
SELECT permission denied on object 'tblMyTable', database
'MyApplication-Dev', owner 'dbo'.
I have tried deleting the users from the database and server and fully
rebuilding the users and roles with no luck. If I change my connection
string to point to my Test machine, which was built a year ago and also
uses Win2K and SQL2K SP3 everything seems to work fine.
Question:
What could cause Dynamic SQL Stored Procs to execute under a different
security context than Non-Dynamic SQL Stored Procs?
Any help would be greatly appreciated.
Will
P.S. I need to user dynamic sql because the sql statement is a query
for data by the user that can be searched on 12 different fields
simultaniously. Therefore the number of combinations of statements I
would need to build would be huge.It sounds like the user was granted access to the underlying tables.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Will" <WillCWirtz@.Yahoo.com> wrote in message
news:1129649346.104637.10300@.g43g2000cwa.googlegroups.com...
Environment:
Window2K workstation, SQL Server 2000 Vesion 8.00.760 (SP3)
Setup:
I have a database setup so that NO users (except dbo) have READ,
UPDATE, or DELETE access to my database. But I have a single role
called MySPUser that is granted EXECUTE access to all of my stored
procs that do all data access for the system. The MySPUser role has a
single user in that group called MyUser, which is a windows domain
level account. My Webserver then impersonates that user when it calls
the stored procs. This setup worked on both my development machine and
my development test machine.
Problem:
So everything was going great for about a year when my dev machine
crashed. When I rebuilt the box with the same software (os and sql
included) everything seemed to be working just fine. The impersonated
user can still call all the stored proc and either retrieve or update
data. The only problem is that I have 2 stored proc that require
Dynamic SQL and they have stopped working. I now receive the following
error message when executing one of the stored procs.
SELECT permission denied on object 'tblMyTable', database
'MyApplication-Dev', owner 'dbo'.
I have tried deleting the users from the database and server and fully
rebuilding the users and roles with no luck. If I change my connection
string to point to my Test machine, which was built a year ago and also
uses Win2K and SQL2K SP3 everything seems to work fine.
Question:
What could cause Dynamic SQL Stored Procs to execute under a different
security context than Non-Dynamic SQL Stored Procs?
Any help would be greatly appreciated.
Will
P.S. I need to user dynamic sql because the sql statement is a query
for data by the user that can be searched on 12 different fields
simultaniously. Therefore the number of combinations of statements I
would need to build would be huge.