Showing posts with label project. Show all posts
Showing posts with label project. Show all posts

Friday, March 23, 2012

How to use SQL Server Management Studio to save existing SP to source safe?

Wiith SQL Server Management Studio(SQL Server 2005 tools), you can create a project and solution and connect to SourceSafe, then create SQL scripts.These scripts will be able to be checked in/out SourceSafe.

Question is: For existing SQL Server 2000 database, how can I add all existing Stored Procedure to this solution/project and then be able to use sourcesafe to maintain the SP code history?

What I would do is create a database solution/project for each instance in VS2005, script out the objects from the database server and add them to the solution, add the solution to your source control.

Wednesday, March 21, 2012

How to use SQL Server Management Studio to save existing SP to source safe?

Wiith SQL Server Management Studio(SQL Server 2005 tools), you can create a project and solution and connect to SourceSafe, then create SQL scripts.These scripts will be able to be checked in/out SourceSafe.

Question is: For existing SQL Server 2000 database, how can I add all existing Stored Procedure to this solution/project and then be able to use sourcesafe to maintain the SP code history?

What I would do is create a database solution/project for each instance in VS2005, script out the objects from the database server and add them to the solution, add the solution to your source control.

Monday, March 19, 2012

How to use parameters in the MDX?

Hi all,

I'm working on a project that builds a report in RS 2005 agains the data cube created in AS 2005.

I created a report in RS 2005 against database table, and the parameters were easy to be added in the T-SQl query.

There are about 15 parameters needs to add in the query, and I can use "IN, =, Betweent.. and ," in the "where" clause, e.g.,

select *
FROM Mytable1
WHERE EmployeeID IN (@.EmployeeID)
and EmployeeName] IN (@.EmployeeName)
...
and Salary between @.SalaryFrom and @.SalaryTo

... and Date between @.DateStart and @.DateEnd

most of the restriction fields are column fileds in the report.

Right now, I need to switch the datasource to the data cube, and then I have to use MDX to do the query. But I really cannot get enough information about MDX in the MSDN library --> SQL Server Language Reference -->MDX Reference to write a MDX query, especially with parameters.

Could somebody tell me how to do it?

please give me a complete example for the following T-SQL query:

select t1.EmployeeName, t2.Salary, t3.Date, t4.SaleAmount


FROM Mytable1 t1, mytable2 t2, mytable3 t3, mytable4 t4


WHERE t1.EmployeeName IN (@.EmployeeName)
...
and t2.Salary between @.SalaryFrom and @.SalaryTo

... and t3.Date between @.DateStart and @.DateEnd

and t4.SaleAmount betweent @.SaleAmountFrom and @.SaleAmountTo

in the cube, those data are stored in DimEmployee (field EmployeeName), DimSalary(filed Salary), DimDate, FactSales, and the SaleAmount will be the messure field.

Or if sombody can provide me the link to MDX tutorial other than what I can get from the "MDX reference" I mented above, I'll be very appreciated !!

Thanks!!

Jone

Once you set up your data source to use the Analysis Services provider, you will be thrown in the MDX Query Designer. From there, you should be able to create your reports by just dragging and droppping attributes and measures. You may find this link useful.|||

Thank you, Teo, that's really a help.

But I still have some problems when I set up the parameters.

1. After I set up couple of parameters, say 5, p1, p2, p3, p4,p5; then if I change the order of the parameters, like p1,p2,p5,p3,p4, in the parameter pane (I still change the order in the report-->report parameters), the query fails since the "forward dependency " isn't allowed.

My question here is : how to change the parameter's order?

2. I have some parameters in style " from .. to", such as "date". when defining the parameter, I choose "Range(Inclusive)" as the "Operator", and choose "all : all" . There are tones of date values in the table, so I think it's not a good idea to let the user choose one date from the drop-down list.

My question is: how to let the user input a date by themselves? how to show a calendar when user click on this parameter field?

Thank you very much

Jone

|||

1. Are these parameters dependent on each other? Please provide repro steps or sample report definition targeting the Adventure Works cube.

2. You can use a DateTime parameter as long as your main query can resolve it (StrToMember() function) to a valid member in in your Date dimension. The 4_ProductSalesQueryMode.rdl report in this download shows how. If I recall I had to set the Value property of the dimension key of the Date dimension to a DateTime column in the underlying database table to get it to work.

|||

Thanks Teo.

In my report,

1. some of those parameters are dependent on each other, while some are not(but all connected to Facttable). i tried to "move up" or "move down" the undependent parameters in the filter pane, I got errors on "forward dependency".

Upon each setting up of a parameter(say p2, and p1 has been set up before p2), a 'select (strtoset() ...) .. from)' was created automatically in such style ( in the report dataset and the parameter dataset):

FROM ( SELECT ( STRTOSET(@.DimP2, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DimP1, CONSTRAINED) ) ON COLUMNS FROM [My Report]))

after changing the order, I got errors.

2. I can get the calendar against the dataset against the database table, but failed against the datacube. I modified the parameter @.Dimdate from string to DateTime, then I got "constrain voilated".

If you have any idea on what I missed, please save me.

Thank you very much!

Jone

|||

Thanks Teo.

In my report,

1. some of those parameters are dependent on each other, while some are not(but all connected to Facttable). i tried to "move up" or "move down" the undependent parameters in the filter pane, I got errors on "forward dependency".

Upon each setting up of a parameter(say p2, and p1 has been set up before p2), a 'select (strtoset() ...) .. from)' was created automatically in such style ( in the report dataset and the parameter dataset):

FROM ( SELECT ( STRTOSET(@.DimP2, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DimP1, CONSTRAINED) ) ON COLUMNS FROM [My Report]))

after changing the order, I got errors.

2. I can get the calendar against the dataset against the database table, but failed against the datacube. I modified the parameter @.Dimdate from string to DateTime, then I got "constrain voilated".

If you have any idea on what I missed, please save me.

Thank you very much!

Jone

how to use parameters in mdx query

i have a problem with mdx query. here is a simple example of what i=B4m
trying to do
select {([Project_Owner].[Project Owner],[Date].[Year])}ON COLUMNS,
{([Customer].[Customer name])} ON ROWS
from [TestCube]
this query works fine... my problem is that the [Customer] dimension
has 3 members and i want to select just one of them, i tried to use
filter function, but it wasn=B4t working, i probably used it the wrong
way. can anyone help me with this? i=B4m working with sql server 2005...You should write it like this:
select {([Project_Owner].[Project Owner],[Date].[Year])}ON COLUMNS,
{([Customer].[Customer name].[BillyTheKid])} ON ROWS
from [TestCube]
The query with parameters should look like this:
="select {([Project_Owner].[Project Owner],[Date].[Year])}ON COLUMNS,
{([Customer].[Customer name].[" & Parameters!ClientName.Value & ")} ON ROWS
from [TestCube]"
Everything needs to be on a single line (it will do a line break, but if you
copy it to Notepad, it should all be on one line.)
Even if you're working with RS 2005, you might pick up some tips from an
article called
MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts
by William Pearson
http://www.databasejournal.com/features/mssql/article.php/10894_3386441_1
He's using RS 2000, but the way to do it is still similar. The good stuff
starts on page 8
http://www.databasejournal.com/features/mssql/article.php/10894_3386441_8
The previous pages are on how to copy and edit one of the sample reports.
Pearson has written lots of good articles on RS and Analysis Services. You
find the full list at http://www.databasejournal.com/article.php/1459531
Kaisa M. Lindahl Lervik
<cellardoor0716@.gmail.com> wrote in message
news:1155818962.580093.222920@.m79g2000cwm.googlegroups.com...
i have a problem with mdx query. here is a simple example of what i´m
trying to do
select {([Project_Owner].[Project Owner],[Date].[Year])}ON COLUMNS,
{([Customer].[Customer name])} ON ROWS
from [TestCube]
this query works fine... my problem is that the [Customer] dimension
has 3 members and i want to select just one of them, i tried to use
filter function, but it wasn´t working, i probably used it the wrong
way. can anyone help me with this? i´m working with sql server 2005...

Friday, March 9, 2012

how to use group by (group tasks based on projects)

Hi folks,

I have a Projects , each project have many tasks now i want to display tasks replated to each project:

for example:

Project1-->task1

task2

task3

task4

Project2-->task4

task5

task6

.............................................projectN.....................

how to write query for this

i have 2 tables:

Project .......>columns are projectid

Task->columns are projectid, taskid

|

Select p.projectid, t.Taskid
From Project p
Left Join Task t --For the case of no assigned tasks
ON p.projectid = t.taskid
order by p.projectid, t.taskid

In this case you would not have to use a group by.

Jens K. Suessmeyer.


http://www.sqlserver2005.de

|||its giving taskid 's null|||

Consider this example

Project

1

2

3

Task

1,A

1,B

2,C

I you want all tasks and their projects, you could write

SELECT ProjectID, TaskID

FROM Task

GROUP BY ProjectID, TaskID

ORDER BY ProjectID, TaskID

This would return

1,A

1,B

2,C

but would provide nothing about Project 3 because it has no tasks.

Given the structure of the table (no repeated tasks), you might even get away with

SELECT ProjectID, TaskID

FROM Task

ORDER BY ProjectID, TaskID

However, if you wanted all projects and to include their tasks, if available, then Jens is correct

Select p.projectid, t.Taskid
From Project p
Left Join Task t --For the case of no assigned tasks
ON p.projectid = t.taskid
order by p.projectid, t.taskid

This would return

1, A

1, B

2, C

3, NULL

The NULL on the last row exists because Project 3 is shown (from the Project table) but there is not equivalent task in the Task table.

The word LEFT from LEFT JOIN tells SQL to return all rows from the Project table and, if available, the row data from Tasks too. If there is no row in Task for a Project in Project, then NULL is placed in the task specific columns.

By dropping the word LEFT, it would limit the result to only those projects that had tasks (but the earlier examples are simpler).

If you want to replace NULL with something more useful, using ISNULL, you could write

SELECT p.projectid, ISNULL(t.Taskid, 'No associated tasks')
FROM Project p
LEFT JOIN Task t ON p.projectid = t.taskid
ORDER BY p.projectid, t.taskid

This would now return

1, A

1, B

2, C

3, No associated tasks

regards,

Niall.

Wednesday, March 7, 2012

how to use designer environment in cystal report 11

I have used crystal report 8.5 in a vb6.0 project using designer activex environment. Now i have installed crystal report11. But 11 does not apear in the activex environment. Vb refrence also shows activex run time library missing.
Please help me !
note : - Crystal Report is really a mess!You need to use Report Viewer

How to use data to generate a report

Hi,
I have one problem in my project. Generating the report plz help me.

I have one table called Emp, which consists of fields, empid,tdate,attn,reason.

Emp
-
empid | tdate | attn | reason

2281 6/3/2006 Present null
2282 6/3/2006 Tour Hyderabad

2283 6/3/2006 Present null
2281 6/4/2006 Present null
2282 6/4/2006 Present null
2283 6/4/2006 Tour null


I want to generate a report as given below based on the date submitted by the user.

Frm Date: 6/3/2006 To Date: 6/4/2006

empid Present Tour Absent No.of working days
2281 2 2
2282 1 1 1
2283 1 1 1


Please write the query and reply back to me. ASAP.

Thanx in advance.

This is typically called crosstab reports. Here is how you will do it SQL 2000.

Select EmpId,
Sum(Case When Attn = 'Present' Then 1 ELSE 0 END) As "Present",
Sum(Case When Attn = 'Tour' Then 1 ELSE 0 END) As "Tour",
Sum(Case When Attn = 'Absent' Then 1 ELSE 0 END) As "Absent",
FROM Emp
WHERE tDate >= '3 Jun 2006' AND tDate <= '4 Jun 2006'
GROUP BY EmpId
ORDER BY EmpId

The key is the SUM(CASE..END) section. The CASE statement checks if the record's Attn field contains the given value. If it does then 1 is added to the sum, otherwise 0 is added. So for your data...

2281 6/3/2006 Present 1 for present, 0 for tour and 0 for absent
2282 6/3/2006 Tour 0 for present, 1 for tour and 0 for absent
2283 6/3/2006 Present 1 for present, 0 for tour and 0 for absent
2281 6/4/2006 Present 1 for present, 0 for tour and 0 for absent
2282 6/4/2006 Present 1 for present, 0 for tour and 0 for absent
2283 6/4/2006 Tour 0 for present, 1 for tour and 0 for absent

So, when you start aggregating the data on emp id, you will get the data you need. However, this type of solution is suitable for known columns, if you dynamic columns then you will have to check for other alternatives.

SQL 2005 has new PIVOT predicates that you can explore in books online. It simplifies the SQL a lot.

|||

Hi Badri Narayanan,

I'm glad that you have answered my question and suggested the PIVOT predicates method to me. I will learn PIVOT predicates and try to solve my problem in easy way. If you have any suggestions plz forward to me.

Thanx for response.

Take care, Bye.

Regards

Ram

Friday, February 24, 2012

How to use cell data security with visual totals?

Hi,

We are working on a project with SQL Server 2005 and SSAS.

We created several roles using dimension security and one special role that requires cell data access permissions (i.e. cell security).

This last role is giving us trouble since we do not know how to implement visual totals on cell security.

A problem description follows:

1) We have a budget and expenses cube with a) Account, b) Time and c) Cost Center dimensions
2) The special role was created for a group of users who have the following requirement for data access permission:
- for some cost centers, restrict access to some accounts
- and for the rest of cost centers see all accounts
3) When looking at data at the cost center and account level, the role works fine. Restrictions are applied correctly.
4) However when looking at the data at the cost center level ONLY, the aggregated total shows a sum for all accounts, regardless of restrictions.


We know that if you use regular dimension security, you can enable Visual Totals in order to have aggregated values that reflect
the restrictions applied at the atomic level.

However, we do not know how to apply visual totals on cell data security. If anyone has faced similar situation,
we would appreciate any help on how to do this.


Another way to put it:

1) In a cube with a Product dimension (Family > Category > Product) and a Geography dimension (Country > State > City)
2) You can set dimension security to restrict access to certain products.
3) If you enable Visual Totals when creating the role, the aggregations at the Category level will show total values excluding amounts for those restricted products.
4) If you do not enable Visual Totals, the aggregated totals at the Category level will add up all products, regardless of access restrictions at the product level.

We are trying to achieve the same with Cell Data Access Permissions, but there is no option to Enable Visual Totals. For instance:

1) Same cube as above
3) Your create a role with cell data access permission; for some cities, you restrict acess to some products, and for all other cities allow access to all products.
4) When you look at data at the City level, you want to aggregate totals not for all products, but excluding amounts for those restricted ones.


We would appreciate your help on this enormously!

Cheers!
jbadillo@.streamnova.com

Cell security was not designed to support visual totals as you described.|||

Hi,

Is there a way around this? Instead of using visual totals, is there some other way to get the right totals?

Cheers

|||

One approach, which seems to work in limited testing with Adventure Works, would involve introducing cell security MDX conditions into the cube MDX script. Basically, a scoped calculation sets the secured cell values to NULL, which will cause the aggregation of those values up the hierarchy to adjust accordingly. However, when cube cells are browsed, the secured cells will still show "#N/A". The limitation with this approach, of course, is that user and/or role logic will be needed, so that unrestricted users are unaffected.

The simplified scenario in Adventure Works was as follows:

For user XYZ, secure the data for Bikes in CY 2003, and rollups in the Sales Summary measure group should reflect this.

The Cell Read Permission expression (for a role with user XYZ as member) is:

Not ([Date].[Calendar].CurrentMember is [Date].[Calendar].[CY 2003]
and [Product].[Product Categories].CurrentMember is [Product].[Product Categories].[Bikes])

And the cube calculation MDX script is something like:

Scope(MeasureGroupMeasures("Sales Summary"),

[Date].[Calendar Year].&[2003],

[Product].[Category].[All Products].[Bikes]);

IF UserName = "XYZ"

THEN this = NULL END IF;

End Scope;

How to use already created Dimensions in SSAS project in new Cube

I have an task in which I have to use already created Analysis Services project for building up new cube. now for building up new cube i need to use some of the dimensions that are already in place. and some i have to create.. Till Data Source View I have almost done. But while cube designing from Data Source View I am facing problem. I am not able to use already present dimensions.

I am using wizard to construct CUBE. now what happens when i create the cube using wizard it automatically creates already present dimensions under new name by appending 1 or 2 etc. I tried the other way by unchecking the already present dimensions but this generates other problem after finishing this way i couldn't see relationships presnt in fact and dimension table as expected by me.

I am new to Analysis Services being my first project i am unable to come out of situation. Somebody has told me by constructing CUBE by not using wizard instead doing it manually will sort out the things... Can anybody provide me any link in which there is explanation how to design cube manually OR solution to my problem

Please mail me at mandipjutla@.hotmail.com

Thanks
Mandip

Hi Mandip,

If you have the dimension already defined in the project all you will need to do is add them to the cube by taking the following steps.

1. Edit the cube that you want to add the dimension to. This OK even if you have created the cube via the wizard.

2. Remove the existing dimension created by the wizard from the cube, by right clicking and choosing delete.

3. Add in the existing dimension that you want by right clicking and choose add.

4. Even if it puts a different name, you can always rename it.

I hope this helps,

David Botzenhart

|||

Hi David,

Thanks for the help.

I have applied the same process as explained by you. But still i can't see the relationship between fact and newly added dimension on Cube's Data Source View.

I have also set up the relation betwwen fact and dimension by going into Dimension Usage tab.

Thanks,

Mandip