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!

Easy User Question

I have a user set up on a SQL 200 server, which I am using to connect to a
database from an ASP.NET application I am making.
When I run the application from the same system as the database is on, the
connection works correctly ( my connection string is:
user id=BlueMaster;data source=BLUE;initial
catalog=BlueSky;password=bluerocks
)
but when I try to connect when running the application off another system,
the connection fails. What do I have to do to set up the user so the
connection will work from remote systems'
ThanksCheck you are using SQL server and Windows authentication in SQL Server (the
defualt is Windows only)
Do this from Enterprise manager - right click the server name, go to
properties and the Security tab. If you do have to change the mode you will
be prompted to restart SQL Server - this is necessary for the security
setting to take effect. If this is not the problem, can you please post the
error message coming back
"Ryan" <web@.balancestudios.com> wrote in message
news:e5321K71DHA.1100@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I have a user set up on a SQL 200 server, which I am using to connect to a
> database from an ASP.NET application I am making.
> When I run the application from the same system as the database is on, the
> connection works correctly ( my connection string is:
> user id=BlueMaster;data source=BLUE;initial
> catalog=BlueSky;password=bluerocks
> )
> but when I try to connect when running the application off another system,
> the connection fails. What do I have to do to set up the user so the
> connection will work from remote systems'
> Thanks
>

---

|||What error message do you get when it fails?
Rand
This posting is provided "as is" with no warranties and confers no rights.

Easy Report parameter Question

Im trying to hard code a Year in as an available value for the user to pick out of a drop down box. This is what i have so far.

Label Value

Travel Year 2006

well i want to go ahead and put two value for that one label , like this:

Label Value

Travel Year 2006, 2007

How do i do this? I tried putting a comma, and i tried putting a semi colon, but it always just grabs the first number "2006".

I know it cant be this hard! please help! THanks!

Enter another Label/Value combination like this:

Label Value

TravelYear 2006

TravelYear 2007

or you could write a little query to do it.

-Mike

|||

ok, this is actually not what im writing, i just tried to explain it in a simpler way, this is what i want

Label Value

Task National TN001, TN002, TN003, TN004, TN005, TN006, TN007

Task Vet TV001,TV002, TV003, TV004, TV005

Survey National SN001, SN002, SN003, SN004, SN005

and so on

Its a way of grouping the same type of tasks together in the parameter, so the user doesnt have to individually go through a long list of codes.

THere should be away to put one option with multiple values.

|||

You could manually create a dataset like this:

Select 'Task National' as Label, 'TN001, TN002, TN003, TN004, TN005, TN006, TN007' as Value

union

Select 'Task Vet' as Label, 'TV001,TV002, TV003, TV004, TV005' as Value

union

Select 'Survey National' as Label, 'SN001, SN002, SN003, SN004, SN005' as Value

Then parse the values and use them.

|||

I ended up just putting it in the where clause like this

Code Snippet

WHERE REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

so it grouped the ones with the same 2 first letters. Works great!|||Nice job. Sometimes you have to be a little creative to get things to work right. :-)

Easy Report parameter Question

Im trying to hard code a Year in as an available value for the user to pick out of a drop down box. This is what i have so far.

Label Value

Travel Year 2006

well i want to go ahead and put two value for that one label , like this:

Label Value

Travel Year 2006, 2007

How do i do this? I tried putting a comma, and i tried putting a semi colon, but it always just grabs the first number "2006".

I know it cant be this hard! please help! THanks!

Enter another Label/Value combination like this:

Label Value

TravelYear 2006

TravelYear 2007

or you could write a little query to do it.

-Mike

|||

ok, this is actually not what im writing, i just tried to explain it in a simpler way, this is what i want

Label Value

Task National TN001, TN002, TN003, TN004, TN005, TN006, TN007

Task Vet TV001,TV002, TV003, TV004, TV005

Survey National SN001, SN002, SN003, SN004, SN005

and so on

Its a way of grouping the same type of tasks together in the parameter, so the user doesnt have to individually go through a long list of codes.

THere should be away to put one option with multiple values.

|||

You could manually create a dataset like this:

Select 'Task National' as Label, 'TN001, TN002, TN003, TN004, TN005, TN006, TN007' as Value

union

Select 'Task Vet' as Label, 'TV001,TV002, TV003, TV004, TV005' as Value

union

Select 'Survey National' as Label, 'SN001, SN002, SN003, SN004, SN005' as Value

Then parse the values and use them.

|||

I ended up just putting it in the where clause like this

Code Snippet

WHERE REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

so it grouped the ones with the same 2 first letters. Works great!|||Nice job. Sometimes you have to be a little creative to get things to work right. :-)

Easy Report parameter Question

Im trying to hard code a Year in as an available value for the user to pick out of a drop down box. This is what i have so far.

Label Value

Travel Year 2006

well i want to go ahead and put two value for that one label , like this:

Label Value

Travel Year 2006, 2007

How do i do this? I tried putting a comma, and i tried putting a semi colon, but it always just grabs the first number "2006".

I know it cant be this hard! please help! THanks!

Enter another Label/Value combination like this:

Label Value

TravelYear 2006

TravelYear 2007

or you could write a little query to do it.

-Mike

|||

ok, this is actually not what im writing, i just tried to explain it in a simpler way, this is what i want

Label Value

Task National TN001, TN002, TN003, TN004, TN005, TN006, TN007

Task Vet TV001,TV002, TV003, TV004, TV005

Survey National SN001, SN002, SN003, SN004, SN005

and so on

Its a way of grouping the same type of tasks together in the parameter, so the user doesnt have to individually go through a long list of codes.

THere should be away to put one option with multiple values.

|||

You could manually create a dataset like this:

Select 'Task National' as Label, 'TN001, TN002, TN003, TN004, TN005, TN006, TN007' as Value

union

Select 'Task Vet' as Label, 'TV001,TV002, TV003, TV004, TV005' as Value

union

Select 'Survey National' as Label, 'SN001, SN002, SN003, SN004, SN005' as Value

Then parse the values and use them.

|||

I ended up just putting it in the where clause like this

Code Snippet

WHERE REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

so it grouped the ones with the same 2 first letters. Works great!|||Nice job. Sometimes you have to be a little creative to get things to work right. :-)

easy question... i think

I have a ASP page where the user posts part of the name field. Then i need to provides the matches. (providing partial comparision of the field & correcting bad spelling)

example:

User posts 'softwair' (should be software)

the query look like this
Select Name from A where Name is like '%softwair%' <-- but appling soundex on it in the like field, or something like it.

I could make a copy of the column and update all info in the field to the soundex value, but then the like statement would not get it if the value was only part of it. for example say the data values where like 'Microsoft Software', or 'My Software Solutions', ect...You don't need to "update" the column to the soundex result, just create a computed column that would use soundex(field_name) as a function. But this will only answer the misspelling question.

select soundex('software'), soundex('softwair')

results in 'S136' for both.

Easy Admin Question

OK, As an Admin on the server, domain, SQL Server and Reportin
Services, when I log into the Report Manager website, I see the folder
"User Folders". I can navigate into that and see everyone's personal
"My Reports" folders.
Now, I have made a developer a Reporting Services Admin by adding him
to the RS Site Role of "System Admin" so that he can modify and
extract some user's reports that they saved in their My Reports
location.
For some reason he is unable to see the "User Folders" folder. I have
verified he is assigned to the System Admin role in RS so I'm unsure
what else I need to do to allow him to see the "User Folders" folder.
Any help anyone could provide is greatly appreciated!
Thanks in advance.
MartinSystem Administrator role does not include permissions to access catalog
items.
Quoting BOL (http://msdn2.microsoft.com/en-us/library/ms156470.aspx)
The System Administrator role is a predefined role that includes tasks that
are useful for a report server administrator who has overall responsibility
for a report server, but not necessarily for the content within it.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<martinghale@.gmail.com> wrote in message
news:1173921810.864000.92000@.n59g2000hsh.googlegroups.com...
> OK, As an Admin on the server, domain, SQL Server and Reportin
> Services, when I log into the Report Manager website, I see the folder
> "User Folders". I can navigate into that and see everyone's personal
> "My Reports" folders.
> Now, I have made a developer a Reporting Services Admin by adding him
> to the RS Site Role of "System Admin" so that he can modify and
> extract some user's reports that they saved in their My Reports
> location.
> For some reason he is unable to see the "User Folders" folder. I have
> verified he is assigned to the System Admin role in RS so I'm unsure
> what else I need to do to allow him to see the "User Folders" folder.
> Any help anyone could provide is greatly appreciated!
> Thanks in advance.
> Martin
>|||Thank you Lev,
So after reading that and messing around with all the settings, I was
finely able to get it working. Had to set permissions at the Folder
and Report levels so that I, as an Admin, could 'control' a user's
reports they created and stored in their My Folders location.
Thanks for the link. It helped in my understanding and tracking this
down.

Early User Questions for developing large number of reports

Hi All,
Firstly, let me say who we are.....a group of us are developing a BI
product where we have decided to use Report Services as the reporting
tool. We are long term BI experts and IT consultants......We are
experienced in all manner of reporting and query tools we are just new
to RS. We settled on RS as our 'lower end' reporting tool for our
product and we will give our clients the 'option' of selecting other
reporting tools but the 'out of the box' reports will be in RS.
We have been dilligent and done our reading (WROX RS book) and testing
and figured out most of what we want to do. We have our first report up
and running and we are learning what the limits of RS seem to be to us.
So hopefully I will not ask too many 'dumb' questions... :-)
We also hope by asking questions here that other users of RS might also
benefit from the public discussion.
Q1. We are also working on our 'templates' for reports and looking at
how we develop a large number (200-300) of reports according to a
'template'. In the WROX Book they talk about codesmith and a way of
handling templates. But we didn't 'get' it...Also, when we try using
the VS.NET report wizard it seems that it insists on a query rather
than a stored procedure for it's data for the wizard to work.
So we are thinking we need to write each report by copying the rdl xml,
editting it by hand and then putting it into vs.net to further edit.
We have no real problem with that but we were thinking someone must
have come up with a better idea than editing the XML by now!!!
Has anyone published papers/ideas/tools (fee or free) to manage the
development and deployment of large numbers of reports?
Q2. We see MSFT has been out talking up RS2005/SQL Server 2005 and
talking about the pricing of SQL Server 2005. But we have not seen any
comment on the possible cost of RS2005 if it is NOT purchased with SQL
Server 2005. We would like to have the option of deploying our product
onto SQL Server with RS running either on that machine or elsewhere. Is
there any public comment from MSFT as to the possible pricing of RS2005
when not purchased with SQL Server?
Q3. Is there any published information on 'best practice' or 'best
examples of usage' of report services? For example MicroStrategy,
Business Objects etc post demos of their products to showcase the 'best
possible use' of the product. Dundas have also put out some really
great demos. But the RS demos from MSFTs web site look a little
'plain' especially the graphics. Are there any really great examples
of RS 'out there' or are the demos on the MSFT web site the best there
is? We have decided to go with DNN for the portal and we are currently
testing RS/DNN integration.
Q4. Layout of prompts on a report.
We have reports with quite a few prompts and they are 'grouped'. For
example we have a set of 4 prompts for the user to select the levels in
the company reporting structure. ANd then we have prompts for the
client to select year and month. Then we might have campaign structure
selection which is 3 levels....so we would like to lay out
Line 1: 4 prompts
Line 2: 2 Prompts
Line 3: 3 prompts
We are told this is not supported...but it also seems like it only
ever places two prompts on one line? If we cannot group prompts can we
at least get it to put more than 2 prompts per line?
Thanks in Advance
Peter Nolan
www.peternolan.comRS is like most of the other MS products: a "back-end" tool with high
custimization available. You can extend it from many way.
Q2:
The licensing is the same as 2000. RS is part of SQL 2005 and if you want
report capabilities only, you have to buy a license of SQL 2005. But the RS
repository required SQL Server.
So for your answer, yes RS can be purchased "separatly" but at the full
price of SQL 2005.
Q3:
On the MS web site, there is some samples reports for different usage:
CRM, Exchange, Axapta, SQL Logs, RS logs.
Best pratices & usages depend on your needs.
Designing printing reports is different from web based usage only reports.
Today there is nothing except simple samples. But these small samples don't
limit me to use RS.
Generally I start from an overall report, then I link to detailed reports.
Q4:
Create you own prompt page if you want to create a specific layout.
Remember that RS is designed for developpers to include reporting capability
into their applications.
But there is a lack of sample and helps to create front end tools around RS.
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1120988013.636226.99630@.o13g2000cwo.googlegroups.com...
> Hi All,
> Firstly, let me say who we are.....a group of us are developing a BI
> product where we have decided to use Report Services as the reporting
> tool. We are long term BI experts and IT consultants......We are
> experienced in all manner of reporting and query tools we are just new
> to RS. We settled on RS as our 'lower end' reporting tool for our
> product and we will give our clients the 'option' of selecting other
> reporting tools but the 'out of the box' reports will be in RS.
> We have been dilligent and done our reading (WROX RS book) and testing
> and figured out most of what we want to do. We have our first report up
> and running and we are learning what the limits of RS seem to be to us.
> So hopefully I will not ask too many 'dumb' questions... :-)
> We also hope by asking questions here that other users of RS might also
> benefit from the public discussion.
>
> Q1. We are also working on our 'templates' for reports and looking at
> how we develop a large number (200-300) of reports according to a
> 'template'. In the WROX Book they talk about codesmith and a way of
> handling templates. But we didn't 'get' it...Also, when we try using
> the VS.NET report wizard it seems that it insists on a query rather
> than a stored procedure for it's data for the wizard to work.
> So we are thinking we need to write each report by copying the rdl xml,
> editting it by hand and then putting it into vs.net to further edit.
> We have no real problem with that but we were thinking someone must
> have come up with a better idea than editing the XML by now!!!
> Has anyone published papers/ideas/tools (fee or free) to manage the
> development and deployment of large numbers of reports?
>
> Q2. We see MSFT has been out talking up RS2005/SQL Server 2005 and
> talking about the pricing of SQL Server 2005. But we have not seen any
> comment on the possible cost of RS2005 if it is NOT purchased with SQL
> Server 2005. We would like to have the option of deploying our product
> onto SQL Server with RS running either on that machine or elsewhere. Is
> there any public comment from MSFT as to the possible pricing of RS2005
> when not purchased with SQL Server?
>
> Q3. Is there any published information on 'best practice' or 'best
> examples of usage' of report services? For example MicroStrategy,
> Business Objects etc post demos of their products to showcase the 'best
> possible use' of the product. Dundas have also put out some really
> great demos. But the RS demos from MSFTs web site look a little
> 'plain' especially the graphics. Are there any really great examples
> of RS 'out there' or are the demos on the MSFT web site the best there
> is? We have decided to go with DNN for the portal and we are currently
> testing RS/DNN integration.
>
> Q4. Layout of prompts on a report.
> We have reports with quite a few prompts and they are 'grouped'. For
> example we have a set of 4 prompts for the user to select the levels in
> the company reporting structure. ANd then we have prompts for the
> client to select year and month. Then we might have campaign structure
> selection which is 3 levels....so we would like to lay out
> Line 1: 4 prompts
> Line 2: 2 Prompts
> Line 3: 3 prompts
> We are told this is not supported...but it also seems like it only
> ever places two prompts on one line? If we cannot group prompts can we
> at least get it to put more than 2 prompts per line?
>
> Thanks in Advance
>
> Peter Nolan
> www.peternolan.com
>|||Hi Jerome,
Q2. In licensing MSFT talks about buying RS when it needs to be
deployed on a server other than the SQL Server...and the WROX book
talks about this as being a 'medium' sized deployment.....if that
means that we need to buy a full SQL Server license (I guess workgroup)
to run RS on a separate server that's ok...we just wanted to
know....but that seems a bit pricey....
Q4. I found examples on odetocode web site but most of the examples I
am finding are 'fragments' intended for developers to include into
already designed/built apps.....that's ok..if there are no other
examples out there we can start from where we are at...just wanted to
check there was not something more publicly available before we spent
our time and effort...it's kind of suprising MSFT has not published
some more things to give developers a better base to work from to go up
against the likes of Crystal/Business Objects/Cognos...maybe more is
coming'
Best Regards
Peter|||Q1, Q3, and Q4--answered in depth in Peter Blackburn and William Vaughn's
Hitchhiker's Guide!
Amazon: http://shrinkster.com/6km
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1120988013.636226.99630@.o13g2000cwo.googlegroups.com...
> Hi All,
> Firstly, let me say who we are.....a group of us are developing a BI
> product where we have decided to use Report Services as the reporting
> tool. We are long term BI experts and IT consultants......We are
> experienced in all manner of reporting and query tools we are just new
> to RS. We settled on RS as our 'lower end' reporting tool for our
> product and we will give our clients the 'option' of selecting other
> reporting tools but the 'out of the box' reports will be in RS.
> We have been dilligent and done our reading (WROX RS book) and testing
> and figured out most of what we want to do. We have our first report up
> and running and we are learning what the limits of RS seem to be to us.
> So hopefully I will not ask too many 'dumb' questions... :-)
> We also hope by asking questions here that other users of RS might also
> benefit from the public discussion.
>
> Q1. We are also working on our 'templates' for reports and looking at
> how we develop a large number (200-300) of reports according to a
> 'template'. In the WROX Book they talk about codesmith and a way of
> handling templates. But we didn't 'get' it...Also, when we try using
> the VS.NET report wizard it seems that it insists on a query rather
> than a stored procedure for it's data for the wizard to work.
> So we are thinking we need to write each report by copying the rdl xml,
> editting it by hand and then putting it into vs.net to further edit.
> We have no real problem with that but we were thinking someone must
> have come up with a better idea than editing the XML by now!!!
> Has anyone published papers/ideas/tools (fee or free) to manage the
> development and deployment of large numbers of reports?
>
> Q2. We see MSFT has been out talking up RS2005/SQL Server 2005 and
> talking about the pricing of SQL Server 2005. But we have not seen any
> comment on the possible cost of RS2005 if it is NOT purchased with SQL
> Server 2005. We would like to have the option of deploying our product
> onto SQL Server with RS running either on that machine or elsewhere. Is
> there any public comment from MSFT as to the possible pricing of RS2005
> when not purchased with SQL Server?
>
> Q3. Is there any published information on 'best practice' or 'best
> examples of usage' of report services? For example MicroStrategy,
> Business Objects etc post demos of their products to showcase the 'best
> possible use' of the product. Dundas have also put out some really
> great demos. But the RS demos from MSFTs web site look a little
> 'plain' especially the graphics. Are there any really great examples
> of RS 'out there' or are the demos on the MSFT web site the best there
> is? We have decided to go with DNN for the portal and we are currently
> testing RS/DNN integration.
>
> Q4. Layout of prompts on a report.
> We have reports with quite a few prompts and they are 'grouped'. For
> example we have a set of 4 prompts for the user to select the levels in
> the company reporting structure. ANd then we have prompts for the
> client to select year and month. Then we might have campaign structure
> selection which is 3 levels....so we would like to lay out
> Line 1: 4 prompts
> Line 2: 2 Prompts
> Line 3: 3 prompts
> We are told this is not supported...but it also seems like it only
> ever places two prompts on one line? If we cannot group prompts can we
> at least get it to put more than 2 prompts per line?
>
> Thanks in Advance
>
> Peter Nolan
> www.peternolan.com
>|||thanks for the pointer goodman 93...I'll see if I can get a copy of
that too.....
Peter

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 using different schema names in Oracle

I want to access some Views in Oracle on machine <M01> with a user <user01>.
The views belong to different schemas e.g. user01.view01 and user02.view02.

When I transport my package to another machine <M02> I am facing a different situation:
The viewnames remain the same but the schemas have changed, e.g. user05.view01
and user06.view02.

I have tried to parametrize my Source SQL query but it is restricted to use parameters in the
WHERE-clause and not in the FROM-clause where I would place something like
SELECT *
FROM ?.view01

The main problem here are the differences between machine M01 and M02!
How would you handle this?
Hm, I think the best would be using an Expression in the DataFlow Source task wherein I use a
package variable. The expression would be something like:

"SELECT * FROM " + @.[SchemaName01] + ".view01"

The value of the package variable would be saved in a configuration. The configuration can be machine-dependant so the package doesn't need to be re-compiled.

The only disadvantage is, that I have plenty of SELECT statements which are stored in expressions. Not quite comfortable...

If someone has a better idea I would be graetful.
Fridtjof
|||I haven't found any practical solution.
I believe that this could be a common problem. Any suggestion on this?
|||

Your solution of using expressions sounds like the best way to go for sure. As you have observed this means you may have alot of expressions to handle but is this really so much of a problem?

-Jamie

|||Well I have plenty of SELECTs in my Oracle Data Sources. As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

By the way: I can imagine that one can meet this situation with an SQL Server having different
Schemas although I didn't see that so far.

Fridtjof
|||

Friedel wrote:

As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

I completely agree. What I always do is construct the expression elsewhere. I usually use the expression editor attached to the package's Description property. This is the safest bet as it won't do any real damage in case you press the OK button instead of the Cancel button after building the expression.

Once you have a working expression you can copy/paste it into the Expression property of your variable.

Its far from perfect I know, but it works!

By the way, I know it doesn't help you now but you'll be pleased to know that SP1 will provide an expression editor for the Expression property of a variable.

-Jamie

sql

Dynamically using different schema names in Oracle

I want to access some Views in Oracle on machine <M01> with a user <user01>.
The views belong to different schemas e.g. user01.view01 and user02.view02.

When I transport my package to another machine <M02> I am facing a different situation:
The viewnames remain the same but the schemas have changed, e.g. user05.view01
and user06.view02.

I have tried to parametrize my Source SQL query but it is restricted to use parameters in the
WHERE-clause and not in the FROM-clause where I would place something like
SELECT *
FROM ?.view01

The main problem here are the differences between machine M01 and M02!
How would you handle this?
Hm, I think the best would be using an Expression in the DataFlow Source task wherein I use a
package variable. The expression would be something like:

"SELECT * FROM " + @.[SchemaName01] + ".view01"

The value of the package variable would be saved in a configuration. The configuration can be machine-dependant so the package doesn't need to be re-compiled.

The only disadvantage is, that I have plenty of SELECT statements which are stored in expressions. Not quite comfortable...

If someone has a better idea I would be graetful.
Fridtjof
|||I haven't found any practical solution.
I believe that this could be a common problem. Any suggestion on this?
|||

Your solution of using expressions sounds like the best way to go for sure. As you have observed this means you may have alot of expressions to handle but is this really so much of a problem?

-Jamie

|||Well I have plenty of SELECTs in my Oracle Data Sources. As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

By the way: I can imagine that one can meet this situation with an SQL Server having different
Schemas although I didn't see that so far.

Fridtjof
|||

Friedel wrote:

As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

I completely agree. What I always do is construct the expression elsewhere. I usually use the expression editor attached to the package's Description property. This is the safest bet as it won't do any real damage in case you press the OK button instead of the Cancel button after building the expression.

Once you have a working expression you can copy/paste it into the Expression property of your variable.

Its far from perfect I know, but it works!

By the way, I know it doesn't help you now but you'll be pleased to know that SP1 will provide an expression editor for the Expression property of a variable.

-Jamie

Dynamically populating an IN() clause within an SSIS package.

Hi,

I currently have a list of User IDs (in a flat file) and I need to connect to a database I have read-only access to, so that I can retrieve additional data about these users.

I imagined a package that ran a query something like:

SELECT * FROM table WHERE UserID IN (<dynamically populated from flat file>).

Can somebody give me some advice as to how I can achieve this (either the way I suggested or another way).

Kind Regards,

Adam.

First thought would be to do it on the server if you have access to the file. Create a temp table bulk insert the file into it then join to the temp table for the query.|||I think the simplest solution is to use a script task to read your file and create the list of users for an IN clause as you mention above. You'd put the list into a variable, and then build your query in an expression-based variable, and set your ole db source's data access mode to "sql command from variable".

Maybe the script would look something like this:

Code Snippet

Public Sub Main()
Dim UserNames() As String = System.IO.File.ReadAllLines(Dts.Variables("FileName").Value.ToString())
Dim s As New System.Text.StringBuilder
Dim IsFirst As Boolean = True
For Each UserName As String In UserNames
If Not IsFirst Then
s.Append(",")
End If
s.Append("'")
s.Append(UserName)
s.Append("'")
IsFirst = False
Next
Dts.Variables("InList").Value = s.ToString()
'Windows.Forms.MessageBox.Show("InList = " + Dts.Variables("InList").Value.ToString())
Dts.TaskResult = Dts.Results.Success
End Sub

|||

2 more:

Have a for each loop to iterate through the file to get each value; then inside of the conatiner have the query logic using and equi-join (=). The thing is that you would run the query as many times as values in the file Have an script task to read the file and build the query; put it in a variable; then you can use that variable as the source uof your query in a Excute SQL task or OLE DB source component.|||

AdamSQLMan wrote:

Hi,

I currently have a list of User IDs (in a flat file) and I need to connect to a database I have read-only access to, so that I can retrieve additional data about these users.

I imagined a package that ran a query something like:

SELECT * FROM table WHERE UserID IN (<dynamically populated from flat file>).

Can somebody give me some advice as to how I can achieve this (either the way I suggested or another way).

Kind Regards,

Adam.

There are many ways to accomplish this. My first thought is this:

Use a data flow to load the flat file, and output it to a data reader destination. Then use a script task to process the data reader into a comma delimited string, and use that in an expression to build your query for use in a second data flow.

|||

NigelRivett wrote:

First thought would be to do it on the server if you have access to the file. Create a temp table bulk insert the file into it then join to the temp table for the query.

If the number of user name is large, then this is probably better than using an IN statement.
|||

JayH wrote:

NigelRivett wrote:

First thought would be to do it on the server if you have access to the file. Create a temp table bulk insert the file into it then join to the temp table for the query.

If the number of user name is large, then this is probably better than using an IN statement.

I Agree.

|||

This would make a good interview question. How would you filter a resultset from a list of IDs in a text file.

Whatever the first answer is then ask what you would do if the tool suggested wasn't available. If the file was a lot bigger than you thought, if it might have invalid data etc.

Dynamically passing a Parameter Name to Custom Code

Hello,

I'm trying to create a custom code function for Reporting Services. I would like to have it user-friendly and give the user the ability to pass a report parameter name into the function (so it can be a generic function that can be used for many reports).

Is there a way to do this so inside the code I can have access to other properties of the object?

I envision something like:

Function Blah(ParameterName as String) as String

Dim MaxNum as Integer
Dim ParamValue as String

MaxNum = Reports.Parameters!ParameterName.Count - 1
ParamValue = Reports.Parameters!ParameterName.Value

etc...

Is this possible? I can't figure out how to do this. Using dynamic SQL you can do this very easily by concatenating string values together and then executing the string. Is there something similar to this in VB?

Help! Thanks

Below is an example for a custom code function that you can call in a textbox e.g. as

=Code.ShowParametersValues(Parameters!Country)

Public Function ShowParameterValues(ByVal parameter as Parameter) as String
Dim s as String
If parameter.IsMultiValue then
s = "Multivalue: "
For i as integer = 0 to parameter.Count-1
s = s + CStr(parameter.Value(i)) + " "
Next
Else
s = "Single value: " + CStr(parameter.Value)
End If
Return s
End Function

-- Robert

Dynamically number of parameters

I have 3 paramemters. How i can dynamically show/hide other two parameter based on the value as 1 or 2 input from the user from the first parameter? (for example: if the user enter 1, i will show the second parameter, if tyhe user enter 2, i will sho the thirs parameter on the report so the user can enter other value in these dynamical parameters?)Thats not possible. What is possible to still display them , but to clean them to show no value. That can be done using a query in the parameter definition.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

dynamically Hide/show columns

Hi,
I need to display columns of user choice in report.
User has to select columns,those columns only will get displayed in report.
Any idea '
Thanks ManjushaThis is not possible in the current release of the product.
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Manjusha" <Manjusha@.discussions.microsoft.com> wrote in message
news:ADFECD07-3750-439C-A50E-418860C184BE@.microsoft.com...
> Hi,
> I need to display columns of user choice in report.
> User has to select columns,those columns only will get displayed in
report.
> Any idea '
> Thanks Manjusha|||Hmm,
You could define a report parameter (showColumnA) of type boolean and
hide/display that column based on the user selection. Had to create a report
with similar requirement (hide/show columns) .
Email me back if you wish further help with this.
Regards,
--
Noel H
"Ravi Mumulla (Microsoft)" <ravimu@.microsoft.com> wrote in message
news:%23cMyJxIWEHA.2840@.TK2MSFTNGP11.phx.gbl...
> This is not possible in the current release of the product.
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Manjusha" <Manjusha@.discussions.microsoft.com> wrote in message
> news:ADFECD07-3750-439C-A50E-418860C184BE@.microsoft.com...
> > Hi,
> > I need to display columns of user choice in report.
> > User has to select columns,those columns only will get displayed in
> report.
> > Any idea '
> >
> > Thanks Manjusha
>|||You could also dynamically choose the columns/order to display by using this syntax:
=Fields (Parameters!FirstFieldToShow.Value).Value
rather than
=Fields!FieldName.Value
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"Noel H" <dot.net@.sliNnOgSsPhAoMt.co.nz> wrote in message
news:Ogq145JWEHA.2952@.TK2MSFTNGP09.phx.gbl...
> Hmm,
> You could define a report parameter (showColumnA) of type boolean and
> hide/display that column based on the user selection. Had to create a report
> with similar requirement (hide/show columns) .
> Email me back if you wish further help with this.
> Regards,
> --
> Noel H
> "Ravi Mumulla (Microsoft)" <ravimu@.microsoft.com> wrote in message
> news:%23cMyJxIWEHA.2840@.TK2MSFTNGP11.phx.gbl...
> > This is not possible in the current release of the product.
> >
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Manjusha" <Manjusha@.discussions.microsoft.com> wrote in message
> > news:ADFECD07-3750-439C-A50E-418860C184BE@.microsoft.com...
> > > Hi,
> > > I need to display columns of user choice in report.
> > > User has to select columns,those columns only will get displayed in
> > report.
> > > Any idea '
> > >
> > > Thanks Manjusha
> >
> >
>

Dynamically generate where condition

If we have a pre-defined SQL statement without "where conditions", how do I dynamically insert the where conditions into it (by user input or whatever)? Is there any free tool available for carrying out this kind of work?

Regards,

Ricky.

Ricky,

If the Where condition must be added to a Sql statement say within a Stored Procedure,

then you could use dynamic SQL statements.

declare @.sql varchar(255),

Select @.sql ="Predefined SQL statement" + "The where condition which can be input to the SP"

Exec @.sql

Sandoty

|||

Be cautious when you use the dynamic sql queries. It may cause two preaches,

1. SQL Injection

2. Permission issues

SQL Injection,

Your code is allowing the end user to inject the SQL command on your own code. So they can vulnerable your database be cautious here. Try to eliminate the comments (-- or /**/) as your input. You can control it on the UI itself. There are lots more practices are there. Google it for SQL Injection. You will get better idea.

Permission Issues,

Dynamic sql will expect the required permission on the source tables, if current user doesn’t have the permission on the table level (but on the SP), the dynamic SQL won’t work.

You can achieve the filter conditions from the SPs or prepared query itself. If the intention is reducing the code & time to achieve this task Pls take care the above issues.

|||

One more point about dynamic queries is the performance issues.

Each time you want to run them the sql command has to be recompiled in order to get an execution plan.

This may be positive in some situations. But most of the time the best way is to set your application to run with specific queries.|||

You can find a lot of information about this theme in the following articles.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

Dynamic Search Conditions in T-SQL

http://www.sommarskog.se/dyn-search.html

AMB

Dynamically edit DataSet

Hi,

I have a web application, where the user specifies the criteria for filtering data (with like, In, between... operators and data retrieved from the db). So, the selection criteria query (SQL) is generated. This is somthing similar to Query Wizard. It builds the SQL for WHERE clause only. This needs to be attached with the query (the developer specified during report-design, which is under dataset->commandtext in RDL file).

To clarify, do not want to use parameters. The idea is, the report design need not be modified each time any filters to be added or removed. The user can simply, add/remove the fields and corresponding sql statements for each field from the db (e.g. QueryWizard table).

The functionality to attach the where clause (from web page) to query (specified in reprot-design) is ready. I need to know, is there anyway I can retrieve the query from the RDL programmatically and set it back before the report is rendered (generated).

I heard something about QueryDefinition.Query, which returns the query, but not sure.

Anybody, any suggestion, will be a great help !!

Have you tried Report Builder? What you want sounds very close to what Report Builder is doing (RS 2005)|||

I have already explored the option of using the Report Builder. I do not want the end-user to design the report. The end-user should be able to specify the criteria (easy-steps thru Query Wizard) and view the report.

However, in case of Report Builder also, if I want to add new fields for selection criteria, the design of the report requires modification, which is what I dont want to do.

Is there any option? Cannt I edit the dataset programmatically ?

|||

Well, DataSet.Query.CommandText can be an expression.
You can try something like this: ="select * from mytable " + ReportParameters!Filter.Value

Dynamically Change the Grouping

Hi!
I am trying to change the Grouping on the report based on which field a user
select. Basically I want to change the way report looks when the user select
a particular field as Group By option. Can any one help me with is.
For Example:
A Report has ClientName, Address, State, City. On the Report, I set Grouping
Property to "ClientName". I want to pass in a parameter that will change
the Grouping Expression to "State" and show the result.
I really need help on this. Can you provide me with an example?
Thank you
Darshan1) Create a new Parameter and call it GroupBy.
2) Since you only have two values to group by, just hard code your
selectable values into this parameter. i.e. ClientName and State
3) Edit your grouping. Instead of using =Fields!ClientName.Value for your
hardcoded group value, refer to the newly created parameter.
To do so, in the group property screen select <Expression...>. Next select
the newly created parameter on the left box, and insert it into the right
box. Or manually type the new parameter in the expression box. i.e.
=Parameters!GroupBy.Value
Now it you have dynamic grouping. Hope that helps - Holler if I was unclear.
"Darshan" wrote:
> Hi!
> I am trying to change the Grouping on the report based on which field a user
> select. Basically I want to change the way report looks when the user select
> a particular field as Group By option. Can any one help me with is.
> For Example:
> A Report has ClientName, Address, State, City. On the Report, I set Grouping
> Property to "ClientName". I want to pass in a parameter that will change
> the Grouping Expression to "State" and show the result.
> I really need help on this. Can you provide me with an example?
> Thank you
> Darshan
>
>|||Ok, ignore my first instructions. /shakes off the rust...
Here's how you do it.
1) Create a Parameter call it GroupBy
2) Add two new static values
3) Set the first Label = "ClientName", set its Value to 0
4) Set the second Label = "State", set its Value to 1
Now in your group by expression you have to build a conditional expression:
=iif(Parameters!GroupBy.Value = 0,Fields!ClientName,Fields!State.Value)
which translates to If GroupBy equals zero, then group by ClientName field,
else group by State field.
"Darshan" wrote:
> Hi!
> I am trying to change the Grouping on the report based on which field a user
> select. Basically I want to change the way report looks when the user select
> a particular field as Group By option. Can any one help me with is.
> For Example:
> A Report has ClientName, Address, State, City. On the Report, I set Grouping
> Property to "ClientName". I want to pass in a parameter that will change
> the Grouping Expression to "State" and show the result.
> I really need help on this. Can you provide me with an example?
> Thank you
> Darshan
>
>|||had a typo in my sample expression, it should be:
=iif(Parameters!GroupBy.Value = 0,Fields!ClientName.Value,Fields!State.Value)
"Darshan" wrote:
> Hi!
> I am trying to change the Grouping on the report based on which field a user
> select. Basically I want to change the way report looks when the user select
> a particular field as Group By option. Can any one help me with is.
> For Example:
> A Report has ClientName, Address, State, City. On the Report, I set Grouping
> Property to "ClientName". I want to pass in a parameter that will change
> the Grouping Expression to "State" and show the result.
> I really need help on this. Can you provide me with an example?
> Thank you
> Darshan
>
>|||Thank you for the example. I have another issue on the same topic:
I have multiple fields then hard coding the value may not be the solution.
Is there any other way to achieve the grouping.
Basically, the user can pick any one field from the list that they can group
by. Can I code a vb function on the report that would allow me to pass the
value.
Example
Public Shared Function GroupByClause(ByVal GroupByStr as String) Dim lsDir
as String
lsDir = "Fields!" + GroupByStr +".Value"
GroupByClause = lsDir
End Function
Let me know.
Again thank you for all the help in advance.
"RS_CZAR" <ichijoe@.hotmail.com> wrote in message
news:9ACFAE3D-38A6-42DB-AA9A-84217686E49D@.microsoft.com...
> had a typo in my sample expression, it should be:
> =iif(Parameters!GroupBy.Value => 0,Fields!ClientName.Value,Fields!State.Value)
>
> "Darshan" wrote:
>> Hi!
>> I am trying to change the Grouping on the report based on which field a
>> user
>> select. Basically I want to change the way report looks when the user
>> select
>> a particular field as Group By option. Can any one help me with is.
>> For Example:
>> A Report has ClientName, Address, State, City. On the Report, I set
>> Grouping
>> Property to "ClientName". I want to pass in a parameter that will change
>> the Grouping Expression to "State" and show the result.
>> I really need help on this. Can you provide me with an example?
>> Thank you
>> Darshan
>>|||Try using the Fields() collection passing in the indexer of your fieldname as
is shown here:
http://blogs.msdn.com/chrishays/archive/2004/07/15/DynamicGrouping.aspx
Let me know if you get this to work...I am using SSRS 2005 and cannot get it
to work if I don't specify a grouping...
"Darshan" wrote:
> Thank you for the example. I have another issue on the same topic:
> I have multiple fields then hard coding the value may not be the solution.
> Is there any other way to achieve the grouping.
> Basically, the user can pick any one field from the list that they can group
> by. Can I code a vb function on the report that would allow me to pass the
> value.
> Example
>
> Public Shared Function GroupByClause(ByVal GroupByStr as String) Dim lsDir
> as String
>
> lsDir = "Fields!" + GroupByStr +".Value"
>
> GroupByClause = lsDir
> End Function
> Let me know.
>
> Again thank you for all the help in advance.
> "RS_CZAR" <ichijoe@.hotmail.com> wrote in message
> news:9ACFAE3D-38A6-42DB-AA9A-84217686E49D@.microsoft.com...
> > had a typo in my sample expression, it should be:
> >
> > =iif(Parameters!GroupBy.Value => > 0,Fields!ClientName.Value,Fields!State.Value)
> >
> >
> > "Darshan" wrote:
> >
> >> Hi!
> >>
> >> I am trying to change the Grouping on the report based on which field a
> >> user
> >> select. Basically I want to change the way report looks when the user
> >> select
> >> a particular field as Group By option. Can any one help me with is.
> >>
> >> For Example:
> >>
> >> A Report has ClientName, Address, State, City. On the Report, I set
> >> Grouping
> >> Property to "ClientName". I want to pass in a parameter that will change
> >> the Grouping Expression to "State" and show the result.
> >>
> >> I really need help on this. Can you provide me with an example?
> >>
> >> Thank you
> >> Darshan
> >>
> >>
> >>
>
>

Dynamicallly Importing csv file into MS SQL

I have an app written in delphi v5 with a MS SQL Database and I'm struggling with an import feature i need

The user needs a function to import Contact data from a txt/csv file into the Contact Table

Details of feature:
the user enters the different parameters into the Delphi app e.g.
File Location
Files Includes Column Headings or not
Whether the file Comma or Tab Seperated
Mapping the fields

Then i need to use those parameters and run some sort of import routine putting the data into the specific table.

The tables consists of 3 fields - First Name, Surname, Mob Number - but these fields can be in any order in the file. for example Col 1 (in file) needs to go into Field 3 in DB. this is sorted in the Mapping Fields above

How do I do this using MS SQL??

I've been looking at using the BULK INSERT command e.g
BULK INSERT Contact
FROM 'C:\Import_data.csv'
WITH
{
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
}
but at the minute i just get error -
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation.

Is this the correct command to use??

Do you know any websites that can point me in the right direction??

ThanksIt's syntax error.
Use () instead of {} as below:

BULK INSERT Contact
FROM 'C:\Import_data.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)|||if you still hit error, try to see if you have grant the right permission in Windows for your csv files.

Dynamic YTD based on user selection on Month

I have a user how wants to be able to see the YTD numbers based on the month they select in an attribute hierarchy with in the Time Dimension.

I would like to create a calculated member called YTD that they can use to get this information based on the Month they select in the Month attribute

The Time Dimensions are as follows: Fiscal time(Year, Qtr, Month) Month(Month)

Measure = Net Sales

View Format should be

Years

2000 2001 2002 2003 2004 2005

Customer $ $ $ $ $ $

YTD should work if the user drills down to Qtr or Month, if they selected a member in the Month attribute or not.

The Time dimension is only loaded with the years that are in the cube so if the user selects Dec but no data is in the cube for Dec 2005. 2005 should still show up on the report.

One of the issues I have run into is determining if the user is looking(selected) something from the Fiscal Hierarchy of from the Month Attirbute. If I use [Time].[Month].currentmember.name I get the name of the month the user selected in Month Attribute but if the user selects "All" months in the Month attribute but selects the Months from the Fiscal hierarchy I still get the month name and not the value "All".

Please Help.

Here is an example of a calculated member that I added to the "AdventureWorks" cube that will return the "Calendar" YTD internet sales amount based on what has been selected as the current member of the Date.Calendar hierarchy. The MDX will also work in AS2K.

Create Member CurrentCube.[Measures].[CYTD Internet Sales Amount]

AS

Aggregate(

PeriodsToDate(

[Date].[Calendar].[Calendar Year],

[Date].[Calendar].CurrentMember),

Measures.[Internet Sales Amount]),

Format_String = "Currency",

Non_Empty_Behavior = { Measures.[Internet Sales Amount] };

HTH,

- Steve