build application: create

Showing posts with label create. Show all posts

Wednesday, March 28, 2012

How to change the column measure into Row Measure in Reporting services

Hi,

I am wondering how to create a matrix that contains 1 dimension for Top Label (Column), let's say "Year-Month"

and then 2 Measure to be in the row format rather than columnar format.

Example as below :

Year-Month on the column, and the measure is on the row :

2007-04 2007-05 2007-06 Amount Sales 1000 2000 3000 Unit Sales 10 20 30 Total 1010 2020 3030

Please share with me if you have this solution in Reporting services as it works in excel, hyperion brio, bo, cognos but somehow cannot see that function in Reporting Services.

Thanks

best regards,

Tanipar

This is easily supported (no need to quote every other tool under the sun to prove your piont).

You just have to drag the field from the dataset window to the right area and drop when you see a horizontal bar

How to change sql login username instead of create new one.

As title, thanks!Can you explain what you mean by "change sql login username"? Are you referring to the login name
(in master) or the user name (in your database)? Anyhow, you cannot change the name of a login or a
user, You will be able to rename a user in 2005, not sure about login, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pleo" <rx8@.hotmail.com> wrote in message news:eVAlmnv0FHA.2428@.tk2msftngp13.phx.gbl...
> As title, thanks!
>|||I'm not familiar sql. At enterprise server (sql2000) > security > logins >
(want to change name here).
Anyway, I guess it can't be changed there. Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ?
news:%23R4Ixpv0FHA.3068@.TK2MSFTNGP10.phx.gbl ?...
> Can you explain what you mean by "change sql login username"? Are you
referring to the login name
> (in master) or the user name (in your database)? Anyhow, you cannot change
the name of a login or a
> user, You will be able to rename a user in 2005, not sure about login,
though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pleo" <rx8@.hotmail.com> wrote in message
news:eVAlmnv0FHA.2428@.tk2msftngp13.phx.gbl...
> > As title, thanks!
> >
> >
>|||Hi,
As Tibor mentioned; In SQL 2000 you can not rename a SQL Server Login or
database user.
Thanks
Hari
SQL Server MVP
"Pleo" <rx8@.hotmail.com> wrote in message
news:ON0lytv0FHA.404@.TK2MSFTNGP09.phx.gbl...
> I'm not familiar sql. At enterprise server (sql2000) > security > logins >
> (want to change name here).
> Anyway, I guess it can't be changed there. Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ?
> news:%23R4Ixpv0FHA.3068@.TK2MSFTNGP10.phx.gbl ?...
>> Can you explain what you mean by "change sql login username"? Are you
> referring to the login name
>> (in master) or the user name (in your database)? Anyhow, you cannot
>> change
> the name of a login or a
>> user, You will be able to rename a user in 2005, not sure about login,
> though.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Pleo" <rx8@.hotmail.com> wrote in message
> news:eVAlmnv0FHA.2428@.tk2msftngp13.phx.gbl...
>> > As title, thanks!
>> >
>> >
>|||Hi,
Yes, you can rename a SQL Server Login. You have to manually change values
in master.dbo.syslogins and master.dbo.sysxlogins.
Gabi Tianu
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uW5cTJ80FHA.916@.TK2MSFTNGP10.phx.gbl...
> Hi,
> As Tibor mentioned; In SQL 2000 you can not rename a SQL Server Login or
> database user.
> Thanks
> Hari
> SQL Server MVP
> "Pleo" <rx8@.hotmail.com> wrote in message
> news:ON0lytv0FHA.404@.TK2MSFTNGP09.phx.gbl...
>> I'm not familiar sql. At enterprise server (sql2000) > security > logins
>> >
>> (want to change name here).
>> Anyway, I guess it can't be changed there. Thanks.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ?
>> news:%23R4Ixpv0FHA.3068@.TK2MSFTNGP10.phx.gbl ?...
>> Can you explain what you mean by "change sql login username"? Are you
>> referring to the login name
>> (in master) or the user name (in your database)? Anyhow, you cannot
>> change
>> the name of a login or a
>> user, You will be able to rename a user in 2005, not sure about login,
>> though.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Pleo" <rx8@.hotmail.com> wrote in message
>> news:eVAlmnv0FHA.2428@.tk2msftngp13.phx.gbl...
>> > As title, thanks!
>> >
>> >
>>
>|||sysxlogins is the name of the table, and syslogins is a view. So, the only place where a change
would be needed is sysxlogins. However, hacking system tables is not supported and Pleo would be on
his own by doing that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gabi Tianu" <gabi_tianu@.hotmail.com> wrote in message news:eS3wd$T1FHA.1108@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yes, you can rename a SQL Server Login. You have to manually change values in master.dbo.syslogins
> and master.dbo.sysxlogins.
> Gabi Tianu
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uW5cTJ80FHA.916@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> As Tibor mentioned; In SQL 2000 you can not rename a SQL Server Login or database user.
>> Thanks
>> Hari
>> SQL Server MVP
>> "Pleo" <rx8@.hotmail.com> wrote in message news:ON0lytv0FHA.404@.TK2MSFTNGP09.phx.gbl...
>> I'm not familiar sql. At enterprise server (sql2000) > security > logins >
>> (want to change name here).
>> Anyway, I guess it can't be changed there. Thanks.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ?
>> news:%23R4Ixpv0FHA.3068@.TK2MSFTNGP10.phx.gbl ?...
>> Can you explain what you mean by "change sql login username"? Are you
>> referring to the login name
>> (in master) or the user name (in your database)? Anyhow, you cannot change
>> the name of a login or a
>> user, You will be able to rename a user in 2005, not sure about login,
>> though.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Pleo" <rx8@.hotmail.com> wrote in message
>> news:eVAlmnv0FHA.2428@.tk2msftngp13.phx.gbl...
>> > As title, thanks!
>> >
>> >
>>
>>
>|||Very good point Tibor. Any unsupported change to the system table allways
has to be done first in a test environment.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OIynWFU1FHA.916@.TK2MSFTNGP10.phx.gbl...
> sysxlogins is the name of the table, and syslogins is a view. So, the only
> place where a change would be needed is sysxlogins. However, hacking
> system tables is not supported and Pleo would be on his own by doing that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Gabi Tianu" <gabi_tianu@.hotmail.com> wrote in message
> news:eS3wd$T1FHA.1108@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Yes, you can rename a SQL Server Login. You have to manually change
>> values in master.dbo.syslogins and master.dbo.sysxlogins.
>> Gabi Tianu
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> news:uW5cTJ80FHA.916@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> As Tibor mentioned; In SQL 2000 you can not rename a SQL Server Login or
>> database user.
>> Thanks
>> Hari
>> SQL Server MVP
>> "Pleo" <rx8@.hotmail.com> wrote in message
>> news:ON0lytv0FHA.404@.TK2MSFTNGP09.phx.gbl...
>> I'm not familiar sql. At enterprise server (sql2000) > security >
>> logins >
>> (want to change name here).
>> Anyway, I guess it can't be changed there. Thanks.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ?
>> news:%23R4Ixpv0FHA.3068@.TK2MSFTNGP10.phx.gbl ?...
>> Can you explain what you mean by "change sql login username"? Are you
>> referring to the login name
>> (in master) or the user name (in your database)? Anyhow, you cannot
>> change
>> the name of a login or a
>> user, You will be able to rename a user in 2005, not sure about login,
>> though.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Pleo" <rx8@.hotmail.com> wrote in message
>> news:eVAlmnv0FHA.2428@.tk2msftngp13.phx.gbl...
>> > As title, thanks!
>> >
>> >
>>
>>
>>
>|||Hi Gabi,
This is not recommended by microsoft to update or modify system tables.
from
Dollerssql

How to change sql login username instead of create new one.

Monday, March 26, 2012

How to change report page settings

Is there a way to reset the page margins for all new
reports? I would like to reset them to allways be .5".
Also is there a way to create a new template?
ThanksPosted to the Wiki...
http://www.ReportingServicesFAQ.com/ow.asp?PageMargins
Help build the Wiki!
--
Reporting Services Articles, Forums, Blogs and Wiki Community
www.ReportingServicesFAQ.com
donald wrote:
> Is there a way to reset the page margins for all new
> reports? I would like to reset them to allways be .5".
> Also is there a way to create a new template?
> Thanks

Wednesday, March 21, 2012

how to change colum values in a trigger

Hi

Does anyone of you know how I can create a trigger to do the following

Table tbs contain 3 columns : total, num_col and alpha_col

I'm doing an insert in table tbs:
insert into tbs (total) values ('111aaa')

Now I want the trigger to split '111aaa' in to parts: An numeric and a characterpart. The trigger should store the numeric part (111) in column num_col. And the characterpart should be stored in alpha_col.

I'm working on a MS SQL Server 7

Can anyone help ?!?!?I am using sql server 2000. I don't know if INSTEAD OF TRIGGERS are available in that version. But here is a piece of code that I came up with at least to deal with the parsing of the string. This assumes that the numeric part is always at the beginning. You could use a cursor inside the trigger to process the info or if you don't want to use cursors you could write the parsing of the string as a function and use that in your SELECT statement for inserting into your table.

DECLARE @.vInput VARCHAR(20);
DECLARE @.vNum VARCHAR(10);
DECLARE @.vAlpha VARCHAR(10);
DECLARE @.vPosition INTEGER;
DECLARE @.vNumPos INTEGER;

SET @.vInput = '111aaa';
SET @.vNum = '';
SET @.vAlpha = '';

SET @.vPosition = 1;
WHILE @.vPosition <= DATALENGTH(@.vInput)
BEGIN
WHILE ASCII(SUBSTRING(@.vInput, @.vPosition, 1)) BETWEEN 48 AND 57
BEGIN
SET @.vNum = @.vNum + SUBSTRING(@.vInput, @.vPosition, 1);
SET @.vPosition = @.vPosition + 1;
END -- while number
SET @.vAlpha = @.vAlpha + SUBSTRING(@.vInput, @.vPosition,1);
SET @.vPosition = @.vPosition + 1;
END -- while string

print @.vNum;
print @.vAlpha;sql

Monday, March 12, 2012

How to call the processing of a cube ?

I need to automatically process a cube. Do I have to use an Analysis Managment Object (AMO) ?

Otherwise, I know that it is possible to create a Sql Agent Job to run periodically, but I don't want to schedule this task. In fact, I need to "call" the processing of my cube, wich will run between other jobs. The "caller" is a Visual Basic 6 program from wich I am able to launch .exe files.

thanks

Hello. I am not sure if you are taking about SSSAS2000 or SSAS2005?

Anyway, you can make a dts-package in SQL Server 2000 or a SSIS-package in BI-Dev Studio that process the dimensions and the cubes.

These packages are possible to call by a VB-application but I think you must use VB6 for dts and VB.Net 2005 for SSIS(Integration services).

HTH

Thomas Ivarsson

|||

Hi. You can also use the command prompt utility "DTEXEC" from VB6 to call a package developed with SSIS if you have to. However, Thomas' suggestion of using VB .NET 2005 is best. Here's more information on the DTEXEC utility.

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

Paul Goldy

|||

Here are a couple of articles I published with step-by-step setup for the DTS / SSIS scenarios involved:

For SSAS 2k (DTS):

http://www.databasejournal.com/features/mssql/article.php/3503201

For SSAS 2k5 (SSIS):

http://www.databasejournal.com/features/mssql/article.php/3584306

Good Luck!

Bill

William E. Pearson III
CPA, CMA, CIA, MCSE, MCDBA
Island Technologies Inc.
931 Monroe Drive
Suite 102-321
Atlanta, GA 30308

404.872.5972 Office
wep3@.islandtechnologies.com
wep3@.msas-architect.com

www.msas-architect.com
-- -- --

Publisher Sites:

http://www.databasejournal.com/article.php/1459531

http://www.sql-server-performance.com/bill_pearson.asp

http://www.informit.com/authors/bio.asp?a=862acd62-4662-49ae-879d-541c8b4d656f

http://www.2000trainers.com/section.aspx?sectionID=17

|||

Thanks ! What I need is in the first article link you posted, Bill. I will create a DTS package.

The problem is, I can't do the step #9 on page 3. I don't have the Analysis Services Processing Task ICON in the TASKS TOOLBAR ... ?

If that can helps, I work with : SQL Server Enterprise Manager version 8.0
Analysis Manager version 5
Visual Basic 6 (the program that calls the DTS package)

So, what do you think ?

PS - I found how to call the package with VB6 on this page :
http://www.sqlservercentral.com/columnists/bknight/executingpackagefromvisualbasic.asp

EDIT : I found on the Microsoft web site that the Analysis Processing task is available only when SQL Server 2000 Analysis Services is installed.
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx

how to call sql store procedure in asp.net

I create a store procedure in sql, how do I call it?

thankssUse SqlCommand like this:

YourCommandName = New SQLCommand("YourStoredProcedureHere",YourConnection)
YourCommandName.CommandType = CommandType.StoredProcedure

You can also add parameters to the SqlCommand.|||got it.

thankss

Friday, March 9, 2012

How to call a stored procedure in a scheduled job

Hi All,

I have a stored procedure.

I need to create a scheduled job using that stored procedure.

I went to Enterprise Manager -->Management--> Jobs-->New Job
Properties

In the step tab, I can select db and put the codes.
Instead of writing the code there, I want to call the stored procedure
in the command box.

How can I do that?

System: MS SQL Server 2000

I would highly appreciate your help.

Thanks a million in advance.

Best regards,

mamunIn the Type Dropdown, select T-SQL & call your stored procedure like:

EXEC usp...

--
- Anith
( Please reply to newsgroups only )

How to call a stored proc?

What is the syntax to call a SQL server stored procedure from a report?
Say if I wanted to create a custom field that would be based on an
expression that calls the stored proc (which would return a single
value from a single field - a sum in this case).
What would be the syntax for doing this within a UDF?
Thanks for any help!Hi
it very similar with sending an sql statment.
from the new dataset window choose command type=storsd procedure, and press
OK.
now you will get the stored procedures list. select the wanted SP and run
the query.
now, go back to the dataset window, you will find there your SP parameters.
you can give some values to those parameters.
hope it was helpful
Shlomi
"megabyte" wrote:
> What is the syntax to call a SQL server stored procedure from a report?
> Say if I wanted to create a custom field that would be based on an
> expression that calls the stored proc (which would return a single
> value from a single field - a sum in this case).
> What would be the syntax for doing this within a UDF?
> Thanks for any help!
>|||Thanks for your reply - I think maybe I did not explain this well
enough: The place where I need to call the stored proc is not while
building the original datasets for the report - this stored proc needs
to run during the execution of the report, for each record returned by
one of the datasets - so really the best place for me to call it would
be in the custom "code" section of the report, in a
User-Defined-Function - do you know what the syntax would be for that?
(this stored proc would return one single value, a sum).
Shlomi wrote:
> Hi
> it very similar with sending an sql statment.
> from the new dataset window choose command type=storsd procedure, and press
> OK.
> now you will get the stored procedures list. select the wanted SP and run
> the query.
> now, go back to the dataset window, you will find there your SP parameters.
> you can give some values to those parameters.
>
> hope it was helpful
> Shlomi
>
> "megabyte" wrote:
> > What is the syntax to call a SQL server stored procedure from a report?
> > Say if I wanted to create a custom field that would be based on an
> > expression that calls the stored proc (which would return a single
> > value from a single field - a sum in this case).
> >
> > What would be the syntax for doing this within a UDF?
> >
> > Thanks for any help!
> >
> >|||Hi
I can think about two ways to do this:
1. you can write a custom code for reading from DB, and call it from the
development envionment.
2. you can retrive all data and write client function for dealing the
aggregations. (i did dit once by retrive an XML and write some XML function
to deal with the aggregation).
if you need the exact solution, i'll try to hel you.
Shlomi
"megabyte" wrote:
> Thanks for your reply - I think maybe I did not explain this well
> enough: The place where I need to call the stored proc is not while
> building the original datasets for the report - this stored proc needs
> to run during the execution of the report, for each record returned by
> one of the datasets - so really the best place for me to call it would
> be in the custom "code" section of the report, in a
> User-Defined-Function - do you know what the syntax would be for that?
> (this stored proc would return one single value, a sum).
>
> Shlomi wrote:
> > Hi
> > it very similar with sending an sql statment.
> > from the new dataset window choose command type=storsd procedure, and press
> > OK.
> > now you will get the stored procedures list. select the wanted SP and run
> > the query.
> > now, go back to the dataset window, you will find there your SP parameters.
> > you can give some values to those parameters.
> >
> >
> > hope it was helpful
> >
> > Shlomi
> >
> >
> > "megabyte" wrote:
> >
> > > What is the syntax to call a SQL server stored procedure from a report?
> > > Say if I wanted to create a custom field that would be based on an
> > > expression that calls the stored proc (which would return a single
> > > value from a single field - a sum in this case).
> > >
> > > What would be the syntax for doing this within a UDF?
> > >
> > > Thanks for any help!
> > >
> > >
>|||Yes, 1 is what I'm after - writing custom code for reading from the DB
at runtime and calling it from the dev. environment - that's exactly
what I don't know how to do - it's VB.Net language, right? I have no
idea how to do that - do you? Do you know of sample code out there?
Shlomi wrote:
> Hi
> I can think about two ways to do this:
> 1. you can write a custom code for reading from DB, and call it from the
> development envionment.
> 2. you can retrive all data and write client function for dealing the
> aggregations. (i did dit once by retrive an XML and write some XML function
> to deal with the aggregation).
> if you need the exact solution, i'll try to hel you.
> Shlomi
> "megabyte" wrote:
> > Thanks for your reply - I think maybe I did not explain this well
> > enough: The place where I need to call the stored proc is not while
> > building the original datasets for the report - this stored proc needs
> > to run during the execution of the report, for each record returned by
> > one of the datasets - so really the best place for me to call it would
> > be in the custom "code" section of the report, in a
> > User-Defined-Function - do you know what the syntax would be for that?
> > (this stored proc would return one single value, a sum).
> >
> >
> > Shlomi wrote:
> > > Hi
> > > it very similar with sending an sql statment.
> > > from the new dataset window choose command type=storsd procedure, and press
> > > OK.
> > > now you will get the stored procedures list. select the wanted SP and run
> > > the query.
> > > now, go back to the dataset window, you will find there your SP parameters.
> > > you can give some values to those parameters.
> > >
> > >
> > > hope it was helpful
> > >
> > > Shlomi
> > >
> > >
> > > "megabyte" wrote:
> > >
> > > > What is the syntax to call a SQL server stored procedure from a report?
> > > > Say if I wanted to create a custom field that would be based on an
> > > > expression that calls the stored proc (which would return a single
> > > > value from a single field - a sum in this case).
> > > >
> > > > What would be the syntax for doing this within a UDF?
> > > >
> > > > Thanks for any help!
> > > >
> > > >
> >
> >|||Hi
This MSDN article will tell you everything you want to know about it:
http://msdn2.microsoft.com/en-us/library/ms153561.aspx
if you have more questions, you will be more than welcome.
P.S.
Use the Microsoft enterprise library to read your data.
"megabyte" wrote:
> Yes, 1 is what I'm after - writing custom code for reading from the DB
> at runtime and calling it from the dev. environment - that's exactly
> what I don't know how to do - it's VB.Net language, right? I have no
> idea how to do that - do you? Do you know of sample code out there?
>
> Shlomi wrote:
> > Hi
> > I can think about two ways to do this:
> > 1. you can write a custom code for reading from DB, and call it from the
> > development envionment.
> >
> > 2. you can retrive all data and write client function for dealing the
> > aggregations. (i did dit once by retrive an XML and write some XML function
> > to deal with the aggregation).
> >
> > if you need the exact solution, i'll try to hel you.
> >
> > Shlomi
> >
> > "megabyte" wrote:
> >
> > > Thanks for your reply - I think maybe I did not explain this well
> > > enough: The place where I need to call the stored proc is not while
> > > building the original datasets for the report - this stored proc needs
> > > to run during the execution of the report, for each record returned by
> > > one of the datasets - so really the best place for me to call it would
> > > be in the custom "code" section of the report, in a
> > > User-Defined-Function - do you know what the syntax would be for that?
> > > (this stored proc would return one single value, a sum).
> > >
> > >
> > > Shlomi wrote:
> > > > Hi
> > > > it very similar with sending an sql statment.
> > > > from the new dataset window choose command type=storsd procedure, and press
> > > > OK.
> > > > now you will get the stored procedures list. select the wanted SP and run
> > > > the query.
> > > > now, go back to the dataset window, you will find there your SP parameters.
> > > > you can give some values to those parameters.
> > > >
> > > >
> > > > hope it was helpful
> > > >
> > > > Shlomi
> > > >
> > > >
> > > > "megabyte" wrote:
> > > >
> > > > > What is the syntax to call a SQL server stored procedure from a report?
> > > > > Say if I wanted to create a custom field that would be based on an
> > > > > expression that calls the stored proc (which would return a single
> > > > > value from a single field - a sum in this case).
> > > > >
> > > > > What would be the syntax for doing this within a UDF?
> > > > >
> > > > > Thanks for any help!
> > > > >
> > > > >
> > >
> > >
>

Wednesday, March 7, 2012

How to calculate the difference between two dates - aging

I have created a Model and using Report Builder to create a report. The
only thing I cannot get is the formula for the date difference. I need to
take an [Opened Date and Time] and the [Closed Date and Time] and get the
difference in DD:HH:MM.
Thanks in advance!Check this:
http://msdn2.microsoft.com/en-us/library/aa258269(SQL.80).aspx
Cheers,
MB
"Cliff Parker" <cliff.parker@.stewart.com> wrote in message
news:614DB115-6BA5-4824-B4EF-5D1455F1FEC6@.microsoft.com...
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and get the
>difference in DD:HH:MM.
> Thanks in advance!|||Hi
Take a look at DATEDIFF system function in the BOL.
"Cliff Parker" <cliff.parker@.stewart.com> wrote in message
news:614DB115-6BA5-4824-B4EF-5D1455F1FEC6@.microsoft.com...
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and get the
>difference in DD:HH:MM.
> Thanks in advance!|||The DATEDIFF function can give you the difference in minutes.
Expressing that in the form DD:HH:MM is not so simple, as there is
nothing in SQL Server in that format. If all you need to do is
display it you could turn it into a string.
Calculating the three parts is not as simple as using datepart three
times. It requires a bit of arithmetic.
DECLARE @.from datetime
DECLARE @.to datetime
SET @.from = '20060704 8:00'
SET @.to = '20061031 10:30'
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT DATEDIFF(minute,@.from, @.to) / (60 * 24) as Days
To put this into the DD:HH:MM format we could use something like:
DECLARE @.from datetime
DECLARE @.to datetime
SET @.from = '20060704 8:00'
SET @.to = '20061031 10:30'
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT DATEDIFF(minute,@.from, @.to) / (60 * 24) as Days
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT CONVERT(varchar(6),DATEDIFF(minute,@.from, @.to) / (60 * 24))
+ ':' +
RIGHT(CONVERT(varchar(6),(DATEDIFF(minute,@.from, @.to) / 60) %
24)+100,2)
+ ':' +
RIGHT(CONVERT(varchar(6),(DATEDIFF(minute,@.from, @.to) %
60))+100,2)
--
119:02:30
The trick used to add the leading zeroes was to add 100 before
converting to a string and taking the two rightmost characters.
Hopefully that gives you something to start with.
Roy Harvey
Beacon Falls, CT
On Wed, 21 Mar 2007 00:12:50 -0500, "Cliff Parker"
<cliff.parker@.stewart.com> wrote:
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and get the
>difference in DD:HH:MM.
>Thanks in advance!|||On Wed, 21 Mar 2007 08:02:47 -0400, Roy Harvey wrote:
>The DATEDIFF function can give you the difference in minutes.
>Expressing that in the form DD:HH:MM is not so simple, as there is
>nothing in SQL Server in that format. If all you need to do is
>display it you could turn it into a string.
>Calculating the three parts is not as simple as using datepart three
>times. It requires a bit of arithmetic.
(snip)
Hi Roy (and Cliff),
Well, for the HH:MM part, there is an alternative: compute the
difference in minutes, add that to a starting date (any date will do) at
midnight, and convert that to string using a "time only" format:
DECLARE @.from datetime;
DECLARE @.to datetime;
SET @.from = '20060704 8:00';
SET @.to = '20061031 10:30';
SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
+ CONVERT(char(5),
DATEADD(minute,
DATEDIFF(minute, @.from, @.to),
'19000101'), -- Any date will do
108);
Results in
119:02:30
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Wed, 21 Mar 2007 21:27:33 +0100, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALID> wrote:
>SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
> + CONVERT(char(5),
> DATEADD(minute,
> DATEDIFF(minute, @.from, @.to),
> '19000101'), -- Any date will do
> 108);
Using DATEDIFF for calculating days is not always correct depending on
the times of day of the two datetimes. Try it for times on either
side of midnight, such as
SET @.from = '20060704 21:00';
SET @.to = '20060705 01:30';
and it returns 1:04:30 rather than 1:04:30. Which is why I coded the
day calculation as:
DATEDIFF(minute,@.from, @.to) / (60 * 24)
Roy Harvey
Beacon Falls, CT|||On Wed, 21 Mar 2007 17:54:53 -0400, Roy Harvey wrote:
>On Wed, 21 Mar 2007 21:27:33 +0100, Hugo Kornelis
><hugo@.perFact.REMOVETHIS.info.INVALID> wrote:
>>SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
>> + CONVERT(char(5),
>> DATEADD(minute,
>> DATEDIFF(minute, @.from, @.to),
>> '19000101'), -- Any date will do
>> 108);
>Using DATEDIFF for calculating days is not always correct depending on
>the times of day of the two datetimes. Try it for times on either
>side of midnight, such as
>SET @.from = '20060704 21:00';
>SET @.to = '20060705 01:30';
>and it returns 1:04:30 rather than 1:04:30. Which is why I coded the
>day calculation as:
> DATEDIFF(minute,@.from, @.to) / (60 * 24)
Hi Roy,
That was a stupid error - thanks for catching it!
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Roy,
Thanks for the information. It was right on the money. You have helped me
out big time!
Many cudos!
Cliff Parker

How to calculate the difference between two dates - aging

I have created a Model and using Report Builder to create a report. The
only thing I cannot get is the formula for the date difference. I need to
take an [Opened Date and Time] and the [Closed Date and Time] and ge
t the
difference in DD:HH:MM.
Thanks in advance!Check this:
http://msdn2.microsoft.com/en-us/library/aa258269(SQL.80).aspx
Cheers,
MB
"Cliff Parker" <cliff.parker@.stewart.com> wrote in message
news:614DB115-6BA5-4824-B4EF-5D1455F1FEC6@.microsoft.com...
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and g
et the
>difference in DD:HH:MM.
> Thanks in advance!|||Hi
Take a look at DATEDIFF system function in the BOL.
"Cliff Parker" <cliff.parker@.stewart.com> wrote in message
news:614DB115-6BA5-4824-B4EF-5D1455F1FEC6@.microsoft.com...
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and g
et the
>difference in DD:HH:MM.
> Thanks in advance!|||The DATEDIFF function can give you the difference in minutes.
Expressing that in the form DD:HH:MM is not so simple, as there is
nothing in SQL Server in that format. If all you need to do is
display it you could turn it into a string.
Calculating the three parts is not as simple as using datepart three
times. It requires a bit of arithmetic.
DECLARE @.from datetime
DECLARE @.to datetime
SET @.from = '20060704 8:00'
SET @.to = '20061031 10:30'
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT DATEDIFF(minute,@.from, @.to) / (60 * 24) as Days
To put this into the DD:HH:MM format we could use something like:
DECLARE @.from datetime
DECLARE @.to datetime
SET @.from = '20060704 8:00'
SET @.to = '20061031 10:30'
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT DATEDIFF(minute,@.from, @.to) / (60 * 24) as Days
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT CONVERT(varchar(6),DATEDIFF(minute,@.from
, @.to) / (60 * 24))
+ ':' +
RIGHT(CONVERT(varchar(6),(DATEDIFF(minut
e,@.from, @.to) / 60) %
24)+100,2)
+ ':' +
RIGHT(CONVERT(varchar(6),(DATEDIFF(minut
e,@.from, @.to) %
60))+100,2)
119:02:30
The trick used to add the leading zeroes was to add 100 before
converting to a string and taking the two rightmost characters.
Hopefully that gives you something to start with.
Roy Harvey
Beacon Falls, CT
On Wed, 21 Mar 2007 00:12:50 -0500, "Cliff Parker"
<cliff.parker@.stewart.com> wrote:

>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and g
et the
>difference in DD:HH:MM.
>Thanks in advance!|||On Wed, 21 Mar 2007 08:02:47 -0400, Roy Harvey wrote:

>The DATEDIFF function can give you the difference in minutes.
>Expressing that in the form DD:HH:MM is not so simple, as there is
>nothing in SQL Server in that format. If all you need to do is
>display it you could turn it into a string.
>Calculating the three parts is not as simple as using datepart three
>times. It requires a bit of arithmetic.
(snip)
Hi Roy (and Cliff),
Well, for the HH:MM part, there is an alternative: compute the
difference in minutes, add that to a starting date (any date will do) at
midnight, and convert that to string using a "time only" format:
DECLARE @.from datetime;
DECLARE @.to datetime;
SET @.from = '20060704 8:00';
SET @.to = '20061031 10:30';
SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
+ CONVERT(char(5),
DATEADD(minute,
DATEDIFF(minute, @.from, @.to),
'19000101'), -- Any date will do
108);
Results in
119:02:30
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Wed, 21 Mar 2007 21:27:33 +0100, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALID> wrote:

>SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
> + CONVERT(char(5),
> DATEADD(minute,
> DATEDIFF(minute, @.from, @.to),
> '19000101'), -- Any date will do
> 108);
Using DATEDIFF for calculating days is not always correct depending on
the times of day of the two datetimes. Try it for times on either
side of midnight, such as
SET @.from = '20060704 21:00';
SET @.to = '20060705 01:30';
and it returns 1:04:30 rather than 1:04:30. Which is why I coded the
day calculation as:
DATEDIFF(minute,@.from, @.to) / (60 * 24)
Roy Harvey
Beacon Falls, CT|||On Wed, 21 Mar 2007 17:54:53 -0400, Roy Harvey wrote:

>On Wed, 21 Mar 2007 21:27:33 +0100, Hugo Kornelis
><hugo@.perFact.REMOVETHIS.info.INVALID> wrote:
>
>Using DATEDIFF for calculating days is not always correct depending on
>the times of day of the two datetimes. Try it for times on either
>side of midnight, such as
>SET @.from = '20060704 21:00';
>SET @.to = '20060705 01:30';
>and it returns 1:04:30 rather than 1:04:30. Which is why I coded the
>day calculation as:
> DATEDIFF(minute,@.from, @.to) / (60 * 24)
Hi Roy,
That was a stupid error - thanks for catching it!
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Roy,
Thanks for the information. It was right on the money. You have helped me
out big time!
Many cudos!
Cliff Parker

How to calculate the difference between two dates - aging

Friday, February 24, 2012

How To Calculate Deviation in Matrix?

Hi All,

I want to create following matrix report on SSRS.

Yearmonth

Jan-07

Feb-07

Mar-07

Apr-07

May-07

Sales

Deviation

Sales

Deviation

Sales

Deviation

Sales

Deviation

Sales

Deviation

Product X1 100 0 110 10 130 20 100 -30 90 -10

Here the Problem is i want calculate Deviation:

Deviation for Feb 07= Sales in Feb - Sales In Jan

Deviation For Mar 07 = Sales in Mar - Sales in Feb.

I don't know how to apply formula in Matrix:

Is anybody hele me?

Thank You.

Balwant Patel.

I do not think there is a striaght-forward formula for this since use of the Previous function is not supported in the Matrix data cell. You can either try calculating the deviation from the query itself, or implement it in the following manner using Custom Code:

Place the following VB.Net code in the code tab under report properties:

Shared Public PrevSales as Integer

Public Function CalDeviation(ColCount as Integer,CurSales as Integer) as Integer
Dim deviation as Integer

If ColCount=1 Then
PrevSales=CurSales
deviation=0

Else
deviation=CurSales-PrevSales
PrevSales=CurSales

End If

Return deviation

End Function

Then use the following call to the function in the textbox where you want to show the deviation:

=Code.CalDeviation(RunningValue(Fields!Month.Value,Count,"matrix1_Product"),Sum(Fields!Sales.Value))

Here, I have assumed the names Product, Month & Sales for the respective fields. Also, the Matrix Row Grouping is on Product & Column Grouping is on Month.

Hope this suits your purpose

-Aayush

|||

Hi Aayush,

Thank you very much for your response...

I tried the code given by you...But when try to write the code in code window as below:

=Code.CalDeviation

the function .CalDeviation doesn't come up....

Thank you,

Regards,

Balwant Patel.

|||

Hi Aayush,

The idea that you give me works now but it will not work when i view Report for Year Or Quarter .....

Please help me on this issue...

Thank you,

Regards,

Balwant Patel.

|||I guess it depends on your requirement, and how your Matrix is designed. What kind of functionality do you desire? Suppose you replace your Column Grouping from Month to either Year or Quarter, then it will work. If your column grouping changes on some condition etc, then it might be a bit more complicated.|||

Hi Aayush,

Thank you very much for your response...

I did solve the problem....I applied following logic to solve the problem...In my Data Table I add Six Columns Like CurrMonthSales , PrevMonthSales , CurreQuarterSales , PrevQuarterSales , CurrYearSales , PrevYearSales...And then Updates these columns through Stored proc...And Then Based in Inscope Function...I Apply The Formula.

Thank you very much again..

Regards,

Balwant Patel.

How To Calculate Deviation in Matrix?

Hi All,

I want to create following matrix report on SSRS.

Yearmonth

Jan-07

Feb-07

Mar-07

Apr-07

May-07

Sales

Deviation

Sales

Deviation

Sales

Deviation

Sales

Deviation

Sales

Deviation

Product X1 100 0 110 10 130 20 100 -30 90 -10

Here the Problem is i want calculate Deviation:

Deviation for Feb 07= Sales in Feb - Sales In Jan

Deviation For Mar 07 = Sales in Mar - Sales in Feb.

I don't know how to apply formula in Matrix:

Is anybody hele me?

Thank You.

Balwant Patel.

Place the following VB.Net code in the code tab under report properties:

Shared Public PrevSales as Integer

Public Function CalDeviation(ColCount as Integer,CurSales as Integer) as Integer
Dim deviation as Integer

If ColCount=1 Then
PrevSales=CurSales
deviation=0

Else
deviation=CurSales-PrevSales
PrevSales=CurSales

End If

Return deviation

End Function

Then use the following call to the function in the textbox where you want to show the deviation:

=Code.CalDeviation(RunningValue(Fields!Month.Value,Count,"matrix1_Product"),Sum(Fields!Sales.Value))

Here, I have assumed the names Product, Month & Sales for the respective fields. Also, the Matrix Row Grouping is on Product & Column Grouping is on Month.

Hope this suits your purpose

-Aayush

|||

Hi Aayush,

Thank you very much for your response...

I tried the code given by you...But when try to write the code in code window as below:

=Code.CalDeviation

the function .CalDeviation doesn't come up....

Thank you,

Regards,

Balwant Patel.

|||

Hi Aayush,

The idea that you give me works now but it will not work when i view Report for Year Or Quarter .....

Please help me on this issue...

Thank you,

Regards,

Balwant Patel.

Hi Aayush,

Thank you very much for your response...

Thank you very much again..

Regards,

Balwant Patel.

How to calculate category value - perhaps use subqueries?

Hello all - I am trying to create a view that will calculate a category field
value based on several conditions regarding another field.
i.e. having a table PayCodes (employeeID, paycode)
employeeID paycode
1 01
1 02
1 02S
1 03S
1 71
2 01
2 02S
2 71
3 02
3 03H
4 01
4 02
I need to create a view that will output employeeID and overtimeType where
overtimeType = 1 if an employeeID has 02S or 02H
overtimeType = 2 if an employeeID has 03S or 03H
overtimeType = 3 if an employeeID has (02S or 02H) and (03S or 03H)
overtimeType = 0 if an employeeID has none of 02S, 02H, 03S, 03H
So given the above table, the view should return:
employeeID overtimeType
1 3
2 1
3 2
4 0
Any ideas?
Thanks in advance!!
Hellman.
On Tue, 4 Oct 2005 10:36:02 -0700, Hellman wrote:

>Hello all - I am trying to create a view that will calculate a category field
>value based on several conditions regarding another field.
>i.e. having a table PayCodes (employeeID, paycode)
>employeeID paycode
>--
> 1 01
> 1 02
> 1 02S
> 1 03S
> 1 71
> 2 01
> 2 02S
> 2 71
> 3 02
> 3 03H
> 4 01
> 4 02
>I need to create a view that will output employeeID and overtimeType where
> overtimeType = 1 if an employeeID has 02S or 02H
> overtimeType = 2 if an employeeID has 03S or 03H
> overtimeType = 3 if an employeeID has (02S or 02H) and (03S or 03H)
> overtimeType = 0 if an employeeID has none of 02S, 02H, 03S, 03H
>So given the above table, the view should return:
>employeeID overtimeType
>--
> 1 3
> 2 1
> 3 2
> 4 0
>Any ideas?
>Thanks in advance!!
>Hellman.
>
Hi Hellman,
I believe that the following will work:
SELECT employeeID,
SUM(overtimeType) AS overtimeType
FROM (SELECT DISTINCT
employeeID,
CASE
WHEN paycode LIKE '02[HS]' THEN 1
WHEN paycode LIKE '03[HS]' THEN 2
ELSE 0
END AS overtimeType
FROM PayCodes) AS d
GROUP BY employeeID
(untested - see www.aspfaq.com/5006 if you prefer tested results)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

how to build such a report with include unkown amount of subreports?

I have spent much time but still have no idea.
I need to dynamic create the report.especially the report (A) is made up of unknown amount of one composite report(B,which include serveral subreports).
An idea is to place subreports of B directly in A.

And the subreports include some tables(not the real table in database) which columns is not fixed.|||Put the SubReport under a Group instead of under a Page header, that way you will get a subreport each time you change groups.|||Would you please explain more detail?
And the other problem is that some tables(not the real table in database) which columns is not fixed.|||I'll try to explain in more detail, but I need to know more about what you're trying to do. What version of Crystal are you using, how are you building the report (using rpt files or RDC, etc...). Give me as much details as you can about what you have already done, what's not working, and what you're trying to do.|||My crystal reports' version is 9.2.

I dont mind using RDC or RPT.

For example there are 4 database tables name (The order information of garment)OrderStyle,ColorGroup,ClothSizeCode,OrderDetails
the sub reports is
sub report A
--------------
OrderCode | StyleCode | StyleName//In OrderStyle
--------------

sub report B
-------------------
|ColorGroupCode | S | M | L | XL | XXL | ...| Summary
|-----------------
|001 |1 |5 |1 | 5 | 9 | ...|21
|002 |1 |6 |2 | 6 |1 | ...|16
|.....................................................
-------------------
|summary |2 |11|3 |11 |10 | ...|37
------------------

The S,M,L,XL,XXL are come from table ClothSizeCode and which size is selected is decided by customer. I think that to use crosstab may be a good idea.
My problem is that how to fixed these 2 subreports in one report.
In power builder it is simple but the efficiency is so poor that it may take 2 minutes to get such a report with only 6 subreport A and 6 subreport B with a few rows.|||OK, here's my guess, tell me if it's close to what you need...

Group Header #1: Customer

... SubReport1:

... ... OrderCode | StyleCode | StyleName//In OrderStyle

... SubReport2:

... ... CrossTab with ClothSizeCode as column, ColorGroupCode as row, If you choose to display Totals, it will automatically summarize it for you

Group Footer #1

Remember that each Subreport is like a whole separate report, it doesn't have to be based on the exact same criteria as your main report. Put both SubReport1 and SubReport2 in the Group Header #1 section.

How to build FROM clause dynamically

I look trough the forum, but did not find any simular problem. Somebody, help, please!
What I need to do is to write an algorithm which create a FROM clause for SQL query, using tables and joined fields, specified by the user. There could be up to 25 tables with any type of join (INNER, OUTER, FULL, CROSS). I know the basic structure of the FROM clause: "from T1 inner(or other type) join T2 on T1.field=T2.field" etc., but the main problem that users can specify tables in any order and I have to re-arrange them to create valid statement.A SQL Server stored procedure is a poor option for giving users ad-hoc query capability. You would need to write a routine that parsed their input statement (very difficult considering that users have little understanding of relational databases) and then would create a logic execution plan from the statement given known relationships among tables.

Wait a minute...that's what Query Analyzer does! Why not just allow the user to submit adhoc query statements? (Make sure your security is tight and you have a query governor active!)

Either that, or check into some of the data-mining software packages such as DI Diver or Cognos.

Where do you live in Melbourne? I spent two years as a kid in Box Hill.|||Thaks for your reply,

but I am not writing the store procedure and I do not have an option using any packages, like Cognos, Crystal etc. I am writing the procedure in VB (it is not up to me). And the main idea, that uses SHOULD NOT HAVE any idea about relational database. Uses just say: I want SQL Server database(could be others - like Oracle, Sybase or MS Access), I supply them names of available servers, they choose the server, I supply names of availabe databases, then tables, then fields, they choose whatever the want, then they point at the related fields of the tables they chose before, and "magic" happened - they have a report. I've done almost everything, just bits and pieces left. AND a FROM clause! It works for simple queries, but for more complicated it works sometimes, which obviousy is not good enough. So I nee an algorithm and stuck with that.

I live at Moorabbin and I know Box Hill - very nice area. Where do you live now?|||*ack* the whole joining thing is the problem...

I have tried this before... basically unless you can query the db to find out what the foreign keys are you are kinda stuffed...

Otherwise the users have to know enough about the database to be able to define the relationships themseleves...|||it sounds to me that the best solution for your problem
which is:
dynamic sql statements
ad hoc queries
no sql knowledge at the end user

you are an excellent candidate for ENGLISH QUERY
There are sample apps available for this product
code samples and etc.

English Query (http://www.microsoft.com/sql/evaluation/features/english.asp)|||I think Ruprect's English Query suggestion is your best shot. The problem is that a user who does not know anything about relational database is more likely than not to get the WRONG ANSWER to a problem due to not understanding relational set manipulation. This is what DBAs and SQL developers are for.

Think about it. Basic SQL is not that complicated. If a user can't understand "Select columna, columnb from sometable where columnc = somevalue" they shouldn't be mucking about in a database anyway. I mean, the syntax is practically an English sentence anyway. Hey, how about a procedure that lets them submit it in Australian?

"Grab beer, prawns, lamington from cooler where label = 'Fosters'"

By the way, now I am back in the United States (Midwest), but I still remember my old address down under 25 years ago: 17 Simmons Street, Box Hill.|||if you build an application that can correctly join any combination of tables from 25 possible tables, whether SQL Server, Oracle, Sybase or MS Access, using the appropriate (often proprietary) sql, with joins utilizing the right columns as determined by an analysis of foreign keys in the information schema, then you have something which you can go out and sell as commercial software against cognos, crystal, etc.

in other words, it ain't as easy as you think|||Depending on how many tables the users are going to want to access and how fluid the ad-hoc queries are, you could work this in two other ways - which aren't elegant but might be enough to serve your purpose:

Either, restrict the queries that the users can create by offering them a list of possible query options that you have already generated the SQL for, or create a table that holds the correct joining criteria for your tables so that you can select the required code.

These won't work if you do want it to be a completely open ended query tool - but I would have thought that if you're getting to the point of ad-hoc queries using OUTER JOINS, then your users will probably have the SQL knowledge already...

Just a thought...|||If you just want to give them slice, dice, and filter capability on defined recordsets. then consider a pivot table linked to a view from either a spreadsheet or a web page.|||Originally posted by r937
if you build an application that can correctly join any combination of tables from 25 possible tables, whether SQL Server, Oracle, Sybase or MS Access, using the appropriate (often proprietary) sql, with joins utilizing the right columns as determined by an analysis of foreign keys in the information schema, then you have something which you can go out and sell as commercial software against cognos, crystal, etc.

in other words, it ain't as easy as you think

That is exactly what our company is trying to do and I am aware that is not easy task, but I have to do it somehow.|||in that case i would suggest investigating INFORMATION_SCHEMA views to see if you can create queries that can access the tables, column, and especially primary/foreign keys

i think with MS access you are up the creek, but i believe the other databases all support INFORMATION_SCHEMA

good luck and let us know how your project turns out

Sunday, February 19, 2012

How to build a report from a cube in SSRS 2005

Hi, All

I am trying to create a report from analysis service cube but I am facing some problems.

While creating new dataset in report designer, MDX query designer is not displaying while giving Sql Server Analysis Service in Data source.

Please give the details of creating reports using cube.

When you look at the data source, what is it's type? It should be "Microsoft SQL Server Analysis Services" and not "OLEDB" if you want a multidimensional view of the data.

Wednesday, March 28, 2012

How to change the column measure into Row Measure in Reporting services

How to change sql login username instead of create new one.

How to change sql login username instead of create new one.

How to change sql login username instead of create new one.

Monday, March 26, 2012

How to change report page settings

Wednesday, March 21, 2012

how to change colum values in a trigger

Monday, March 12, 2012

How to call the processing of a cube ?

how to call sql store procedure in asp.net

Friday, March 9, 2012

How to call a stored procedure in a scheduled job

How to call a stored proc?

Wednesday, March 7, 2012

How to calculate the difference between two dates - aging

How to calculate the difference between two dates - aging

How to calculate the difference between two dates - aging

Friday, February 24, 2012

How To Calculate Deviation in Matrix?

How To Calculate Deviation in Matrix?

How to calculate category value - perhaps use subqueries?

How to calculate category value - perhaps use subqueries?

how to build such a report with include unkown amount of subreports?

How to build FROM clause dynamically

Sunday, February 19, 2012

How to build a report from a cube in SSRS 2005

build application

Blog Archive

About Me