How to Create Store Procedure

Hi all,
I am not familiar with the Store Procedure, so just want to know how to create the store procedure? For example, i want to write a store procedure for Login validation to check whether the username and password is correct. So what should i do?Okay, first off I am FAMOUS for writing the kludgiest code on the planet. This proc may or may not work for you (it does for me). It may or may not be very fast. It may or may not meet with best practices guidelines (it probably doesn't). But it should give you some things to work with (a stored procedure, parameters, working with results). You need to do a bit of research on your own. Google is your friend.

Supporting Tables

create table Users (Username varchar(255), [Password] varchar(255))

Supporting Data

insert Users (UserName, [Password]) values ('tscott','123')
go
insert Users (UserName, [Password]) values ('ascott','abc')
go
insert Users (UserName, [Password]) values ('bscott','Abc')
go

select * from users2

Stored Procedure

CREATE PROC spValidatePassword ( @.UserName varchar(255),
@.Password varchar(255)
)

/************************************************** *****
* spValidatePassword
* hmscott
*
* Validates a username/password against a table of known username/passwords
* Warning: data is not encrypted; these passwords are not secure. This
* sample is for demonstration only and is not intended for production
* use.
*
* Parameters:
* IN: UserName The usernmae provided by the user
* Password The password provided by the user
*
* Returns:
* Single-row recordset (ReturnCode):
* -1: UserName not found
* 0: Password incorrect for this user
* 1: Password correct for this user
************************************************** ****/

AS

DECLARE @.LocalPassword varchar(255), @.ReturnCode int

SELECT @.LocalPassword = [Password]
FROM dbo.Users2
WHERE UserName = @.UserName

IF @.LocalPassword IS NULL
BEGIN
SELECT @.ReturnCode = -1 -- User not valid
END
ELSE
BEGIN
IF @.LocalPassword = @.Password COLLATE Latin1_General_CS_AS
-- IF @.LocalPassword = @.Password -- Use this line instead of the above line if you want the passwords to be case insensitive
BEGIN
SELECT @.ReturnCode = 1 -- Password matches
END
ELSE
BEGIN
SELECT @.ReturnCode = 0 -- Password does not match
END
END

SELECT @.ReturnCode as ReturnCode

spValidatePassword 'nouser', 'Foo'

spValidatePassword 'bscott', 'Abc'

spValidatePassword 'bscott', 'abc'sql

How to create reports dinamically?

Hello. Can I make a report for example with all the columns and make a
program in asp.net in which the user can select the columns he wants to see?
Thanks.Yes of course, unless RDL is a open Language you can stick your parts
together as the User wants. But I would keep in mind that there are already
thrid party tools to do that, therefore looking at these will eventually
save you time and money fordeveloping that on your own.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Luis Esteban Valencia" <levalencia@.avansoft.com> schrieb im Newsbeitrag
news:OZDxaTydFHA.1684@.TK2MSFTNGP09.phx.gbl...
> Hello. Can I make a report for example with all the columns and make a
> program in asp.net in which the user can select the columns he wants to
> see?
> Thanks.
>|||Your page doesnt work
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> escribió
en el mensaje news:elyDct0dFHA.220@.TK2MSFTNGP12.phx.gbl...
> Yes of course, unless RDL is a open Language you can stick your parts
> together as the User wants. But I would keep in mind that there are
already
> thrid party tools to do that, therefore looking at these will eventually
> save you time and money fordeveloping that on your own.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Luis Esteban Valencia" <levalencia@.avansoft.com> schrieb im Newsbeitrag
> news:OZDxaTydFHA.1684@.TK2MSFTNGP09.phx.gbl...
> > Hello. Can I make a report for example with all the columns and make a
> > program in asp.net in which the user can select the columns he wants to
> > see?
> >
> > Thanks.
> >
> >
>sql

How to create indented, outline formatted report

Anyone thought of a clever way to create a proper outline format - for
example using the results from a recursive hierarchy via the Level function?
The recommended approach seems to be to use the left pad textbox attribute,
setting it to a formula based on the indentation level desired (e.g., =5 +
(Level * 10) & "Pt"). This sort of works, but all it does is make the
starting char move over to the right in a fixed position text box. If the
text is almost as long as the text box, it will wrap - unless you make the
text box (table control column) very wide (or merged with other cols), which
is not feasible if you have other table colums to its right.
The right way to do it would be to shift the position of the text box itself
to the right, but text box posn can not controlled by a formula. This seems
to be true even if the text box is in a list control.
Has anyone figured out a good way to do this?On Oct 12, 7:05 pm, isaksp00 <isaks...@.discussions.microsoft.com>
wrote:
> Anyone thought of a clever way to create a proper outline format - for
> example using the results from a recursive hierarchy via the Level function?
> The recommended approach seems to be to use the left pad textbox attribute,
> setting it to a formula based on the indentation level desired (e.g., =5 +
> (Level * 10) & "Pt"). This sort of works, but all it does is make the
> starting char move over to the right in a fixed position text box. If the
> text is almost as long as the text box, it will wrap - unless you make the
> text box (table control column) very wide (or merged with other cols), which
> is not feasible if you have other table colums to its right.
> The right way to do it would be to shift the position of the text box itself
> to the right, but text box posn can not controlled by a formula. This seems
> to be true even if the text box is in a list control.
> Has anyone figured out a good way to do this?
One way to do it (albeit a lot of work) could be to programmatically
create the RDL file. That way you can control the text box width on
the fly. Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks for the reply. I have all but concluded that what I want to do (which
seems like something that is not at all uncommon) just isn't supported by RS.|||On Oct 13, 8:28 am, isaksp00 <isaks...@.discussions.microsoft.com>
wrote:
> Thanks for the reply. I have all but concluded that what I want to do (which
> seems like something that is not at all uncommon) just isn't supported by RS.
You're welcome. Another small thing that might control the table size
and expansion w/the option you mentioned is to put the table(s) in a
rectangle control. Here's an MSDN link to the option I mentioned
previously.
http://msdn2.microsoft.com/en-us/library/ms170667.aspx
Regards,
Enrique Martinez
Sr. Software Consultant

How to create dimension with attributes from different tables?

Hi, all here,

I am having a question about how to create dimension with attributes from different tables? For example, I am going to create a dimension called Country-region with attibutes from country table and region table. So how can it be done then?

Thanks a lot in advance for any guidance and help.

Hi,

You will need to create a 'named query' in your DSV and use this named query as a source for your dimension.

HTH,

Eric

|||Hi, Aiwa, thanks a lot. But would you please post some sample code about named query for that then? Thanks a lot.|||

Hi,

I don't know your tables structure but you could create a named query that would look like that:

SELECT DISTINCT a.IdCountry, a.CountryCode, a.CountryName, a.OtherCountryAttributes,b.IdRegion,b.RegionCode, b.RegionName,b.OtherRegionAttributes

FROM Country a

JOIN Region b on b.IdCountry = a.IdCountry

Hopefully, your fact table keeps the region id or region code information. You will then create your Dimension based on the named query and btw, the id of the dimension should be the IdRegion column.

HTH,

Eric

|||Hi, Aiwa, thanks a lot. Got it done.

How to create a storeprocedure

Hi,
I'm very very new in SQLServer.
I need to create a store procedure that execute a sql file located in a PATH
for example:
C:\Inetpub\Reserved\update.sql
HOw can I made this?
Thanks a lot!Hi
what version of sql server you using.
if you have sql server installed on your computer, then you can open .sql
file in query analyser or management studio to execute it
i still don't understand why you a need a SP to execute update.sql file
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>|||Andrea
Sorry, I type it from the memory, please test it
EXEC master..xp_cmdshell 'osql.exe -S SVR -U sa -P pswd -i
"c:\Inetpub\Reserved\update.sql" -o "C:\output.txt"'
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>sql

How to create a SAPConnection in .Net Code with .NET Data Provider

In the SQL Server Technical Article Microsoft .NET Data Provider for mySAP? Business Suite

is a Code example of how to invoke a BAPI in .Net Code. First line shows how to create a Connection to SAP, but there is no information in which lib the SAPConnection type is stored:

SAPConnection con = new SAPConnection("ASHOST=<SAPserver>; CLIENT=<client>;SYSNR=<sysnr>;USER=<user>;PASSWD=<password>;LANG=<logon

language>");
con.Open();

Does somebody can help me?Assembly Microsoft.Adapter.SAP.SAPProvider|||Correct! That really helped me out! Thanks.

How to create a report using multiple databases

Hi, I am a beginner of Reporting Service. I am trying to create a report using multiple databses. For example, I want to create a report called RevenueByCustomer, so I need to get data from the Customer Table of CRM database, which contains customer information, and I also need to get data from Transaction table of Sales database, which contains all the revenue information. In order to get data from both database, I have created two dataset. One is Customer dataset, which get all required customer data from CRM database, and the other is Revenue dataset, which gets data from Sales database, they used seperate datasource (because each datasource only contains one database connection). Now my problem is how can I make them be displayed in one report ? It seems to be like a Master-Detail report, I need to sum up all trasactions for a particular customer and also need to display the customer name with each TotalAmount, but how can I make these two dataset can be merged together or used an extra query to do it?

Please help me, thanks a lot

This Query below is used to calculate the total amount for each customer in Revenue dataset:

SELECT CustomerID, sum(TotalAmount - TotalTaxAmount) AS TotalExcTax, sum(TotalAmount) AS TotalIncTax
FROM TransactionMaster
WHERE (Updated >= @.Start) AND (Updated <= @.End)
Group By CustomerID

the other one here is used to get customer code and name in Customer dataset:

SELECT ID, Code, Name
FROM Customer

SELECT ID, Code, Name
FROM Customer

UNION ALL

SELECT CustomerID, sum(TotalAmount - TotalTaxAmount) AS TotalExcTax, sum(TotalAmount) AS TotalIncTax
FROM TransactionMaster
WHERE (Updated >= @.Start) AND (Updated <= @.End)
Group By CustomerID

|||

I think your easiest solution would be to join the two recordset's together via the CustomerID and put it in one dataset. Then you can have Reporting Services do the grouping.

SELECT c.Code, c.Name, sum(t.TotalAmount - t.TotalTaxAmount) AS TotalExcTax, sum(t.TotalAmount) AS TotalIncTax
FROM CustomerDB.dbo.Customer c
inner join SalesDB.dbo.TransactionMaster t on c.ID = t.CustomerID
WHERE (t.Updated >= @.Start) AND (t.Updated <= @.End)

Hope this helps.

Jarret

|||

Hi,

You might be able to do that on the SQL server and return that as a view, but you can't access two separate database connections in the SQL statement in the RS dataset designer. (unless this has changed..)

If you want to create a master - detail report in BIDS, try creating two connections, two seperate datasets and then create a 'master' report on one dataset and a subreport for the detail on the other dataset. Then pass that subreport the current master record's ID field using parameters..

pl

|||

pl,

You can join from other databases as in my example. I think I mis-worded it, instead of '...join the two recordset's together via...', I should have said '...join the two table's together via...'.

Sorry for the confusion. I still think this would be the easiest solution, rather than creating sub-reports.

Jarret

|||

Hi Jarret,

I don't believe you can join two tables from different databases in SQL dataset editor in BIDS. Have you ever tried?

pl

|||

pl,

Yes, I have tried, and it works (as long as the user account has access to both databases). I have a few reports in production that link between databases.

Jarret

|||

Hi Jarret,

Really? Which version of RS are you using? I have RS 2005 (no upgrades to sp1..) and BIDS (SQL Server Business Intelligence Development Studio). Even though I create two connections to two databases in a solution using BIDS, I cannot refer to 'the other database' in the SQL view designer when I'm creating a new dataset.

Is this post correct: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=177599&SiteID=1

Here is what I do:

1) Create a new Report Server Project in BIDS 2005

2) Add a datasource to the Shared Data Sources. Hook it up to database1.

3) Add another datasource to the Shared Data Sources. Hook it up to database2. Now you have two datasources in one report project.

4) Add a report.

5) Create a new dataset. (Click on the "Data" tab, hit the drop-down list and choose <new dataset>) Notice that when that dataset dialog opens up, you can only specify one datasource for it. Specify the datasource.

6) In the dataset designer, try to access the other datasource that you did not specify in the dataset's connection (ie: select * from database2.dbo.table1) . It doesn't work. You get an error message that says "Invalid object name... Error: 208"

You are saying that this works for you? Are you sure you aren't maybe doing this in query analyzer on the server and producing a view which you then use to populate a datasource in RS using only one connection?

pl

|||

pl,

I think you misunderstand what I have done (and suggested) here. I am using RS 2005 SP1 and BIDS. In my example, the code I pasted (below) was the query for 1 single dataset. Of course, if these databases are on separate servers, this wouldn't be possible without a linked server. However, Fern.Andy didn't mention anything about the DB's being on separate servers, so I assumed they weren't.

SELECT c.Code, c.Name, sum(t.TotalAmount - t.TotalTaxAmount) AS TotalExcTax, sum(t.TotalAmount) AS TotalIncTax
FROM CustomerDB.dbo.Customer c
inner join SalesDB.dbo.TransactionMaster t on c.ID = t.CustomerID
WHERE (t.Updated >= @.Start) AND (t.Updated <= @.End)

I am not saying that I can link two dataset's together from within BIDS. In short, I joined the two tables together in the query, rather than trying to get RS to join two different datasets.

1. Create a new report
2. Create the connection string to the server
3. Paste the above query into the query string

Jarret

|||

Hey Jarret,

I've got it working now. Thank you very much. I appreciate your patience.

Seems I had a spelling mistake in the database name. Once I had that fixed, I had to give access to the same user to the second database. But, it does work now. And, I only needed one connection in RS. This is going to be a very handy. Much better than using sub-reports.

Thanks again.

pl

How to create a report using multiple databases

Hi, I am a beginner of Reporting Service. I am trying to create a report using multiple databses. For example, I want to create a report called RevenueByCustomer, so I need to get data from the Customer Table of CRM database, which contains customer information, and I also need to get data from Transaction table of Sales database, which contains all the revenue information. In order to get data from both database, I have created two dataset. One is Customer dataset, which get all required customer data from CRM database, and the other is Revenue dataset, which gets data from Sales database, they used seperate datasource (because each datasource only contains one database connection). Now my problem is how can I make them be displayed in one report ? It seems to be like a Master-Detail report, I need to sum up all trasactions for a particular customer and also need to display the customer name with each TotalAmount, but how can I make these two dataset can be merged together or used an extra query to do it?

Please help me, thanks a lot

This Query below is used to calculate the total amount for each customer in Revenue dataset:

SELECT CustomerID, sum(TotalAmount - TotalTaxAmount) AS TotalExcTax, sum(TotalAmount) AS TotalIncTax
FROM TransactionMaster
WHERE (Updated >= @.Start) AND (Updated <= @.End)
Group By CustomerID

the other one here is used to get customer code and name in Customer dataset:

SELECT ID, Code, Name
FROM Customer

SELECT ID, Code, Name
FROM Customer

UNION ALL

SELECT CustomerID, sum(TotalAmount - TotalTaxAmount) AS TotalExcTax, sum(TotalAmount) AS TotalIncTax
FROM TransactionMaster
WHERE (Updated >= @.Start) AND (Updated <= @.End)
Group By CustomerID

|||

I think your easiest solution would be to join the two recordset's together via the CustomerID and put it in one dataset. Then you can have Reporting Services do the grouping.

SELECT c.Code, c.Name, sum(t.TotalAmount - t.TotalTaxAmount) AS TotalExcTax, sum(t.TotalAmount) AS TotalIncTax
FROM CustomerDB.dbo.Customer c
inner join SalesDB.dbo.TransactionMaster t on c.ID = t.CustomerID
WHERE (t.Updated >= @.Start) AND (t.Updated <= @.End)

Hope this helps.

Jarret

|||

Hi,

You might be able to do that on the SQL server and return that as a view, but you can't access two separate database connections in the SQL statement in the RS dataset designer. (unless this has changed..)

If you want to create a master - detail report in BIDS, try creating two connections, two seperate datasets and then create a 'master' report on one dataset and a subreport for the detail on the other dataset. Then pass that subreport the current master record's ID field using parameters..

pl

|||

pl,

You can join from other databases as in my example. I think I mis-worded it, instead of '...join the two recordset's together via...', I should have said '...join the two table's together via...'.

Sorry for the confusion. I still think this would be the easiest solution, rather than creating sub-reports.

Jarret

|||

Hi Jarret,

I don't believe you can join two tables from different databases in SQL dataset editor in BIDS. Have you ever tried?

pl

|||

pl,

Yes, I have tried, and it works (as long as the user account has access to both databases). I have a few reports in production that link between databases.

Jarret

|||

Hi Jarret,

Really? Which version of RS are you using? I have RS 2005 (no upgrades to sp1..) and BIDS (SQL Server Business Intelligence Development Studio). Even though I create two connections to two databases in a solution using BIDS, I cannot refer to 'the other database' in the SQL view designer when I'm creating a new dataset.

Is this post correct: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=177599&SiteID=1

Here is what I do:

1) Create a new Report Server Project in BIDS 2005

2) Add a datasource to the Shared Data Sources. Hook it up to database1.

3) Add another datasource to the Shared Data Sources. Hook it up to database2. Now you have two datasources in one report project.

4) Add a report.

5) Create a new dataset. (Click on the "Data" tab, hit the drop-down list and choose <new dataset>) Notice that when that dataset dialog opens up, you can only specify one datasource for it. Specify the datasource.

6) In the dataset designer, try to access the other datasource that you did not specify in the dataset's connection (ie: select * from database2.dbo.table1) . It doesn't work. You get an error message that says "Invalid object name... Error: 208"

You are saying that this works for you? Are you sure you aren't maybe doing this in query analyzer on the server and producing a view which you then use to populate a datasource in RS using only one connection?

pl

|||

pl,

I think you misunderstand what I have done (and suggested) here. I am using RS 2005 SP1 and BIDS. In my example, the code I pasted (below) was the query for 1 single dataset. Of course, if these databases are on separate servers, this wouldn't be possible without a linked server. However, Fern.Andy didn't mention anything about the DB's being on separate servers, so I assumed they weren't.

SELECT c.Code, c.Name, sum(t.TotalAmount - t.TotalTaxAmount) AS TotalExcTax, sum(t.TotalAmount) AS TotalIncTax
FROM CustomerDB.dbo.Customer c
inner join SalesDB.dbo.TransactionMaster t on c.ID = t.CustomerID
WHERE (t.Updated >= @.Start) AND (t.Updated <= @.End)

I am not saying that I can link two dataset's together from within BIDS. In short, I joined the two tables together in the query, rather than trying to get RS to join two different datasets.

1. Create a new report
2. Create the connection string to the server
3. Paste the above query into the query string

Jarret

|||

Hey Jarret,

I've got it working now. Thank you very much. I appreciate your patience.

Seems I had a spelling mistake in the database name. Once I had that fixed, I had to give access to the same user to the second database. But, it does work now. And, I only needed one connection in RS. This is going to be a very handy. Much better than using sub-reports.

Thanks again.

pl

How To Create a PDF from a stored procedure

I am trying to write a stored procedure that generates a PDF file for example my PDF file will look something like this (there should be spaces between the columns):

First Name Last Name Address
Mike Mik Jr 141552 South
Charlie D 1422141

Lets say my table name whichthat has all these data is called dbo.TestTable
I spent so much time in google and I have not found one simple good example. Can you help me please

Thanks in advance for your help

Hi,

You can refer this link: http://www.sqlservercentral.com/columnists/mivica/creatingapdffromastoredprocedure.asp

I guess it solves your problem.

Thanks & Regards,

Kiran.Y

|||

Thank you for your replay. I' ve seen this article before but it did not really help. Exmple1 and 2 in the article are straight forward but you can’t see how to output the PDF file. Also it does not show you how to loop through the pdf table. I did run the stored procedure under The link SQL2PDF.TXT but it did not generate the PDF File.

Thank you

How To Create a PDF from a stored procedure

I am trying to write a stored procedure that generates a PDF file for example my PDF file will look something like this (there should be spaces between the columns):

First Name Last Name Address
Mike Mik Jr 141552 South
Charlie D 1422141

Lets say my table name whichthat has all these data is called dbo.TestTable
I spent so much time in google and I have not found one simple good example. Can you help me please

Thanks in advance for your help

Hi,

You can refer this link: http://www.sqlservercentral.com/columnists/mivica/creatingapdffromastoredprocedure.asp

I guess it solves your problem.

Thanks & Regards,

Kiran.Y

|||

Thank you for your replay. I' ve seen this article before but it did not really help. Exmple1 and 2 in the article are straight forward but you can’t see how to output the PDF file. Also it does not show you how to loop through the pdf table. I did run the stored procedure under The link SQL2PDF.TXT but it did not generate the PDF File.

Thank you

How To Create a PDF from a stored procedure

I am trying to write a stored procedure that generates a PDF file for example my PDF file will look something like this (there should be spaces between the columns):

First Name Last Name Address
Mike Mik Jr 141552 South
Charlie D 1422141

Lets say my table name which has all these data is called dbo.TestTable.
I would like my stored procedure to output a PDF file with data from TestTable.
I spent so much time in google and I have not found one simple good example. Can you help me please

Thanks in advance for your help

Quote:

Originally Posted by goal2007

I am trying to write a stored procedure that generates a PDF file for example my PDF file will look something like this (there should be spaces between the columns):

First Name Last Name Address
Mike Mik Jr 141552 South
Charlie D 1422141

Lets say my table name which has all these data is called dbo.TestTable.
I would like my stored procedure to output a PDF file with data from TestTable.
I spent so much time in google and I have not found one simple good example. Can you help me please

Thanks in advance for your help

There's no way a stored procedure can directly output its results to a PDF file that I know of. I suggest that you make a .NET component that creates a database connection, executes your stored procedure retrieving a dataset, creates a PDF file and writes the output to it. You can leverage a free iTextSharp library http://itextsharp.sourceforge.net/ for making PDF files. It provides for basic formatting and may be just what you need. Then, you have a few options of setting up your component to run either as a SQL job or Windows service or even invoke it from inside the stored procedure itself (requires CLR integration). Hope this is helpful. Let me know if you need more details.|||

Quote:

Originally Posted by davef

There's no way a stored procedure can directly output its results to a PDF file that I know of. I suggest that you make a .NET component that creates a database connection, executes your stored procedure retrieving a dataset, creates a PDF file and writes the output to it. You can leverage a free iTextSharp library http://itextsharp.sourceforge.net/ for making PDF files. It provides for basic formatting and may be just what you need. Then, you have a few options of setting up your component to run either as a SQL job or Windows service or even invoke it from inside the stored procedure itself (requires CLR integration). Hope this is helpful. Let me know if you need more details.

Thank you for your replay. Here is what i am trying to do. I am trying to setup sql schedule which is going to execute a stored procedure that gets some data then put these data into a pdf file. After that it will send an e-mail to the manger with new created pdf file. How can i accomplish that?
Thank you|||

Quote:

Originally Posted by goal2007

Thank you for your replay. Here is what i am trying to do. I am trying to setup sql schedule which is going to execute a stored procedure that gets some data then put these data into a pdf file. After that it will send an e-mail to the manger with new created pdf file. How can i accomplish that?
Thank you

You can create a Windows service that establishes a connection to the database, polls the database table(s), creates a new PDF file with the data retrieved and sends an email to the manager with this attachment. In the service, you embed a timer component for scheduling tasks. The actual scheduling info can be placed in .config file for starters.

How to create a MAX Named Set for a date dimension?

I have an OLAP cube and I need to create a Named Set to return a latest date from a date dimension. For example I have the following dimension:

[Account Period].[Account Period].[Prescription Date].[Prescription Date].12-July-2006

Based on this dimension structure, how to create a named set only to return the latest date in any grouping of the other dimensions. Or to create a named set is not the correct approach to this?

You can simply use the Tail function unless your date dimension contains dates into the future for forecasting or other measures:

Tail([Account Period].[Account Period].[Prescription Date].Members,1)

If you have dates into the future you can use a measure group to filter the dates so that only dates with data for that measure group are considered:

Tail(Exists([Account Period].[Account Period].[Prescription Date].Members,,"Sales"),1)

HTH,

Steve

|||

Thank you for your reply. I guess that it is best to put down what I really want to achieve here and for the expert to give me the best advice. I am designing a report for a pharmist on a OLAP cube. the OLAP cube consists of the following dimensions:

Measure: Count of the logical primary key (treatment count)

Dimension: Patient_ID.Patient_ID.Patient_ID

Dimension: Account_Period.Prescription_Date.Prescription_Date

Dimension: Drug.Drug_Name.Drug_Name

Dimension: Hospital.Hospital.Hospital

Dimension: Drug.Drug_Ingriedient..Drug_Ingriedient

I need an MDX query for the report so that for a selected Drug_Name (first report single parameter) and the selected Drug_Ingriedient (second report multiple parameters) the "latest" Prescription_Date of the selected Drug_Name, the "earliest" Prescription_Date of the selected Drug_Name, the duration of the selected Drug_Name given to this Patient_ID, the Drug_Ingridient at the "latest" Prescription_Date (Drug_Ingridient is one to one relationship to the Drug_Name, different Drug_Ingridient can occour on the same Drug_Name but not on the same Prescription_Date), the hospital where the Drug_Name prescripted to the Patient_ID at the "latest" Prescription_Date (Patient can be prescripted the selected Drug_Name from different hospital over the period of several years) and the total count of the Drug_Name treatment.

The dataset should consists of eight columns, Patient_ID, Latest_Date, Earliest_Date, Duration, Drug_Name, Drug_Ingridient, Treatment_Count, Hospital.

Thanks.

|||

This will be tough to do via MDX on this discussion board. There are several questions that I need to ask.

1.) What is the formula for "Duration"?

2.) Are the "Earliest" and "Latest" dates for all prescriptions given to the patient?

My email is stevepon@.microsoft.com if you want to send me information off-line. A copy of your AS project files would be a big help.

Steve

how to create a data warehouse or data mart

i want to create a data mart from an existing OLTP database. for example northwind or i will create an OLTP database. i dont know how i can create data mart from OLTP database. i want to learn that step by step. help me? please!!There is no step-by-step method for creating data warehouses.
You could try Kimball's one-size-fits-all star schema approach to create a datamart, but I don't hold a high opinon of that.
My recommendation would be read some of the books by Inmon and his associates.|||i read it. but now i have a project. i need simple examples. i saw the nwindmart from the northwind database. but i dont understand how they create it from the northwind.|||Creating a true "data warehouse" is an iterative process that involves both the development team and the end-users. If you have read Inmon's work, then you know that the methodology is to start with a small set of important and related tables, and prune and enhance from there as business needs are determined.
Are you just talking about creating a data cube?|||firstly i must create a simple OLTP database. I will make it like northwind or pubs. then i must create a simple data mart from this OLTP database. this data mart has 4 or 5 dimension. and then i must create a cube that has 3 dimension. i have northwind database and nwindmart. i try to understand that how they create nwindmart from the northwind.

i need simple example for example how can create salesfact table from northwind? they used DTS or ...

i dont read that book exactly. but i read some parts.|||You need to read Books Online and other available documentation, and then come back to the forum with specific questions.

how to count the total rows for a query with group by?

Hi,
I have a query like
select tbl1.f1, tbl2.f2, count(tbl1.f1) as YY from tbl1, tbl2 where
tbl1.f1=tbl2.f2 group by tbl1.f1, tbl2.f2
for example, I can get the results like
f1 f2 YY
--
x1 y1 2
x2 y2 5
x3 y3 1
As you can see, total rows for the query is 3 (rows). How can I get
the "3" in the one select query like:
f1 f2 YY Total_Rows
--
x1 y1 2 3
x2 y2 5 3
x3 y3 1 3
or write another query to count the total rows (to get the "3") only?
Thanks a lot.
Ouyanghi
just see this:
select tbl1.f1, tbl2.f2, count(tbl1.f1) as YY ( select count(*) from tbl1,
tbl2 where
tbl1.f1=tbl2.f2 group by tbl1.f1, tbl2.f2 ) as total_rows from tbl1, tbl2
where
tbl1.f1=tbl2.f2 group by tbl1.f1, tbl2.f2
probably this solves your problem
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"zxo102" wrote:

> Hi,
> I have a query like
> select tbl1.f1, tbl2.f2, count(tbl1.f1) as YY from tbl1, tbl2 where
> tbl1.f1=tbl2.f2 group by tbl1.f1, tbl2.f2
> for example, I can get the results like
>
> f1 f2 YY
> --
> x1 y1 2
> x2 y2 5
> x3 y3 1
>
> As you can see, total rows for the query is 3 (rows). How can I get
> the "3" in the one select query like:
> f1 f2 YY Total_Rows
> --
> x1 y1 2 3
> x2 y2 5 3
> x3 y3 1 3
> or write another query to count the total rows (to get the "3") only?
>
> Thanks a lot.
> Ouyang
>|||You may also want to summarise the data in a report style query using
COMPUTE, the link below will take you to an example on how to implement
this:
http://www.mssql.com.au/kb/html/gmg... />
&@.sa_id=58
"zxo102" <zxo102@.gmail.com> wrote in message
news:1122690598.655507.63480@.g47g2000cwa.googlegroups.com...
> Hi,
> I have a query like
> select tbl1.f1, tbl2.f2, count(tbl1.f1) as YY from tbl1, tbl2 where
> tbl1.f1=tbl2.f2 group by tbl1.f1, tbl2.f2
> for example, I can get the results like
>
> f1 f2 YY
> --
> x1 y1 2
> x2 y2 5
> x3 y3 1
>
> As you can see, total rows for the query is 3 (rows). How can I get
> the "3" in the one select query like:
> f1 f2 YY Total_Rows
> --
> x1 y1 2 3
> x2 y2 5 3
> x3 y3 1 3
> or write another query to count the total rows (to get the "3") only?
>
> Thanks a lot.
> Ouyang
>|||Hi Chandra,
this sql query:
"select count(*) from tbl1, tbl2 where tbl1.f1=tbl2.f2 group by
tbl1.f1, tbl2.f2"
gives group count instead of total count from the query with "group
by".
Any ideas?
Thanks.
Ouyang|||hi
can you please send the ddl so that i can help u out
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"zxo102" wrote:

> Hi Chandra,
> this sql query:
> "select count(*) from tbl1, tbl2 where tbl1.f1=tbl2.f2 group by
> tbl1.f1, tbl2.f2"
> gives group count instead of total count from the query with "group
> by".
> Any ideas?
> Thanks.
> Ouyang
>|||hi
i am sorry, you can try this:
select count(*) from tbl1, tbl2 where tbl1.f1=tbl2.f2
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"zxo102" wrote:

> Hi Chandra,
> this sql query:
> "select count(*) from tbl1, tbl2 where tbl1.f1=tbl2.f2 group by
> tbl1.f1, tbl2.f2"
> gives group count instead of total count from the query with "group
> by".
> Any ideas?
> Thanks.
> Ouyang
>|||Hi Guillaume,
Thanks for your information. I got the total row using "compute".
But the returned results from SQL server are in two parts. The value
of the total row is in the second part.
Since I call this query from python application which can not grab the
value from the second part, do you know how to get the total row in a
query which just return the total row only?
Thanks a lot.
Ouyang|||Hi Chandra,
My ddl is as follows:
select t_all_status.enter_diag AS r_ICDCODE,
count(t_all_status.enter_diag) AS r_SUM2,
t_icd.chinese_diag AS r_CHINESE,
t_dept.dept_name as r_DEPT_NAME
from t_all_status, t_icd,t_dept
where (t_all_status.enter_dept=t_dept.dept_code)
AND (t_all_status.enter_diag not like 'M%')
AND (t_all_status.enter_diag = t_icd.icdcode)
group by t_dept.dept_name,t_all_status.enter_diag, t_icd.chinese_diag
order by r_SUM2 DESC
Thanks
Ouyang|||Put the row count in an output variable. Maybe something like this:
select *
from ...
set @.finalRowCount = @.@.rowcount
@.finalRowCount must be declared as an in/out parameter of your procedure.
ML|||Hi ML,
I don't know very much about the stuff you mentioned, can you give
me an exmaple for that using my DDL as follows:
select t_all_status.enter_diag AS r_ICDCODE,
count(t_all_status.enter_diag) AS r_SUM2,
t_icd.chinese_diag AS r_CHINESE,
t_dept.dept_name as r_DEPT_NAME
from t_all_status, t_icd,t_dept
where (t_all_status.enter_dept=3Dt_dep=ADt.dept_code)
AND (t_all_status.enter_diag not like 'M%')
AND (t_all_status.enter_diag =3D t_icd.icdcode)
group by t_dept.dept_name,t_all_status.=ADenter_diag,
t_icd.chinese_diag
order by r_SUM2 DESC=20
I really appraciate your help.
Ouyang