Showing posts with label building. Show all posts
Showing posts with label building. Show all posts

Tuesday, March 27, 2012

Calculated Member (beginner)

Hi,

I'm building a calculated member which should only calculate the measures in the calculated member if specific measure is > 0 (otherwise the value should be Null)

For example:

If physicalStock > 0

[Measures].[Netweight] * [Measures].[Physical]

How do I build this in the calculated member...?

(Using Analysis2000)

Thanks in advance

Haakon

With AS2000 Mosha have a blog post of this subject here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/06/30/mdx_is_empty.aspx

HTH

Thomas Ivarsson

|||

Here is what I am trying....

Iif([Measures].[Physical] > 0), [Measures].[Netweight] / [Measures].[amount

_count] * [Measures].[Physical]

Which don't work....any ideas - what I should do...?

Thanks

|||

IIF(([Measures].[Physical] > 0, [Measures].[Netweight] / ([Measures].[amount_count] * [Measures].[Physical]),Null)

HTH

Thomas Ivarsson

|||

Thanks for your input - but it still seems to be causing problems:

I am getting the following error in the calculated member builder:

"Unable to update the calculated member. Formula error - Syntax error - token is not valid"

I am using the following suggested value expression:

Iif(([Measures].[Physical] > 0,[Measures].[Netweight] / [Measures].[amount_count] * [Measures].[Physical]),Null)

Your suggestions are appriciated,

Thanks,

|||

It is my error. Try this:

IIF([Measures].[Physical] > 0,[Measures].[Netweight] / ([Measures].[amount_count] * [Measures].[Physical]),Null)

Copy and paste this example above.

Regards

Thomas Ivarsson

sql

Tuesday, March 20, 2012

Calculate the values from a underlying table

I am building a customerlist within the customer sales of a period. I have a dataset with two tables: "customer" and "customer_ledger_entry".

In the report I will present the customer number, the customer name and the sales. The problem is the sales value is not available as a field, but I have to calculate this value from the "customer_ledger_entry" table. In this table are several entries (invoices, credit notes, etc.)

How to calculate the values from a underlying table?

Here is a post that may work. You would populate the sales value into a dictionary object and then reference it based on the key you decide to use.

(from my blog at http://sqlrs.blogspot.com)

One common problem in reporting and BI solutions is how to incorporate data from both an OLAP cube and relational tables. The UDM in SQL 2005 attempts to solve this, however it really means you still need to build the information into your cubes and dimension attributes.

What if you don't want to or can't?

Reporting Services provides a Custom Code tab within the Report Properties page. You can access various VB.NET objects and system assemblies, and reference external assemblies. One of the internal assemblies is the Dictionary object.

Steps to lookup values from a reference table in SQL:

Drag a list onto the report.
Drag a textbox into the list, or a field from the relational dataset. Modify the textbox to contain =Code.setValue(Fields!KeyField.Value, Fields!ValueField.Value)

Create another list below. Drag another textbox into the list. Modify the textbox expression to hard-code the key for now. =Code.getValue("MyKey")

In the Code Properties window, try the following:

public dict as new System.Collections.Generics.Dictionary(Of System, System)

function setValue(value as object, value2 as object) as object
dict.Add(value,value2)
return value
end function

function getValue(value as object) as object
return dict(value)
end function

Afterwards, you can hide the list box (or table or whatever) that loads the variable with the setValue function. The dictionary still gets populated.

If you have properly bound a table to the first list control, you should be able to lookup results in the second table.

This can be applied in many scenarios, including adding relational reference data to MDX results, and creating a relationship between two separate datasets.

I'd be interested to know if anyone uses this. It seems to have many different applications. One could possibly involve showing two sets of information, for things like variances or budget vs. actual data. If a value doesn't exist in the dictionary, the original field could be returned. If it does exist, the adjustment could be returned.

Note that Generics is .NET 2.0 - for 2000 you may need to use a different syntax but the concept is the same. Basically you're using a dictionary object (could be a hash table or whatever) to store a value by a key. Then you're looking up that value in a table (or list or whatever) to do further calculations.

cheers,

Andrew

|||

Selectis,

I believe you may want to create 2 dataset for your report, the first dataset for the "customer" and the second dataset for "customer_ledger_entry". You can then calculate your values like SUM(Fields!Sales.value,"customer_ledger_entry", customer name like =(Fields!FirstName,"customer")

I hope this was what you were looking for.

Ham

|||

I've made a the two datasets like you said. My expression is:

=Sum(Fields!Sales__LCY_.Value, "customer_ledger_entry"), (Fields!Customer_No_.Value, "customer_ledger_entry") like = (Fields!No_.Value, "customer")

When I run the report I get the message "The Value expression for the textbox refers to a the field "Customer_No_". Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

|||

Selectis,

I must have misunderstood what you were trying to accomplish, I thought you when trying to display values from 2 different tables but I didn’t realize you want to reference the 2 datasets together.

If you can do the following:

You can nest data regions within other data regions. For example, if you want to create a sales record for each sales person in a database, you can create a list with text boxes and an image to display information about the employee, and then add table and chart data regions to show the employee's sales record.

I hope this helps

Ham

|||

I′ve found a way to calculate the value: I use one dataset with two tables: "customer" and "customer_ledger_entry".

The report contains a table and I've used the SUM function for the customer sales: =SUM(Fields!Sales.Value). Now my report calculates all the sales lines from the "customer_ledger_entry". That is not what I want. But the solution is to choose "Edit Group" for the detail line in your report. In the Group settings I added a sorting on the customer and I added a "Group on" expression. Now the SUM function only calculates the records for the customer listed on the line.

Monday, March 19, 2012

Calculate days till next birthday

Hi,
I'm building an aplication in with I need to calculate the number of days
till the persons next birthday. I already have an UDF that calculates the age
given the birthdate and the current date, and I would like to be able to do
an UDF that calculates the number of days till next birthday to use it on a
computed field.
Look at DATEDIFF system funtion in the BOL
"M?rcio Santos" <Mrcio Santos@.discussions.microsoft.com> wrote in message
news:F540944C-F830-44E1-AADB-A90F7E018E89@.microsoft.com...
> Hi,
> I'm building an aplication in with I need to calculate the number of days
> till the persons next birthday. I already have an UDF that calculates the
age
> given the birthdate and the current date, and I would like to be able to
do
> an UDF that calculates the number of days till next birthday to use it on
a
> computed field.
>
|||I have, i've used it to build the other UDF I reffered. But this one isn't
thar simple. I have a PHP Script that solves this problem, it's something
like this:
"
$nextbirthday =
mktime(0,0,0,"09","12",date("Y"));
// if $nextbirtday is in the past, add 1 year to $nextbirthday
if ($nextbirthday<$nowdate) {
$nextbirthday=$nextbirthday+(60*60*24*365);
}
// calculate the remaining day's till my next birthday
$daycount=intval(($nextbirthday-$nowdate)/(60*60*24));
echo " My next birthday is over $daycount day's.";
"
Now I want to transform this into an UDF, to be able to use it on a computed
field.
"Uri Dimant" wrote:

> Look at DATEDIFF system funtion in the BOL
>
> "M?rcio Santos" <Mrcio Santos@.discussions.microsoft.com> wrote in message
> news:F540944C-F830-44E1-AADB-A90F7E018E89@.microsoft.com...
> age
> do
> a
>
>
|||Hi
Did u try it this way:
DATEDIFF(d, getdate(), birthdate)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Márcio Santos" wrote:
[vbcol=seagreen]
> I have, i've used it to build the other UDF I reffered. But this one isn't
> thar simple. I have a PHP Script that solves this problem, it's something
> like this:
> "
> $nextbirthday =
> mktime(0,0,0,"09","12",date("Y"));
> // if $nextbirtday is in the past, add 1 year to $nextbirthday
> if ($nextbirthday<$nowdate) {
> $nextbirthday=$nextbirthday+(60*60*24*365);
> }
> // calculate the remaining day's till my next birthday
> $daycount=intval(($nextbirthday-$nowdate)/(60*60*24));
> echo " My next birthday is over $daycount day's.";
> "
> Now I want to transform this into an UDF, to be able to use it on a computed
> field.
>
> "Uri Dimant" wrote:
|||On Wed, 8 Jun 2005 03:46:02 -0700, "Mrcio Santos" <Mrcio
Santos@.discussions.microsoft.com> wrote:

>Hi,
>I'm building an aplication in with I need to calculate the number of days
>till the persons next birthday. I already have an UDF that calculates the age
>given the birthdate and the current date, and I would like to be able to do
>an UDF that calculates the number of days till next birthday to use it on a
>computed field.
>
Hi Mrcio,
It doesn't look pretty and I have the feeling that there should be an
easier way, but this code will at least get the job done:
DECLARE @.BirthDay smalldatetime
SET @.BirthDay = 'yyyymmdd' -- Insert test data here
SELECT DATEDIFF(day,
CURRENT_TIMESTAMP,
DATEADD(year,
DATEDIFF(year, @.BirthDay, CURRENT_TIMESTAMP)
+ CASE WHEN DATEADD(year, DATEDIFF(year,
@.BirthDay, CURRENT_TIMESTAMP), @.BirthDay) < CURRENT_TIMESTAMP THEN 1
ELSE 0 END,
@.BirthDay))
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thx... it works perfectly...
"Hugo Kornelis" wrote:

> On Wed, 8 Jun 2005 03:46:02 -0700, "Márcio Santos" <Márcio
> Santos@.discussions.microsoft.com> wrote:
>
> Hi Márcio,
> It doesn't look pretty and I have the feeling that there should be an
> easier way, but this code will at least get the job done:
> DECLARE @.BirthDay smalldatetime
> SET @.BirthDay = 'yyyymmdd' -- Insert test data here
> SELECT DATEDIFF(day,
> CURRENT_TIMESTAMP,
> DATEADD(year,
> DATEDIFF(year, @.BirthDay, CURRENT_TIMESTAMP)
> + CASE WHEN DATEADD(year, DATEDIFF(year,
> @.BirthDay, CURRENT_TIMESTAMP), @.BirthDay) < CURRENT_TIMESTAMP THEN 1
> ELSE 0 END,
> @.BirthDay))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Calculate days till next birthday

Hi,
I'm building an aplication in with I need to calculate the number of days
till the persons next birthday. I already have an UDF that calculates the ag
e
given the birthdate and the current date, and I would like to be able to do
an UDF that calculates the number of days till next birthday to use it on a
computed field.Look at DATEDIFF system funtion in the BOL
"M?rcio Santos" <Mrcio Santos@.discussions.microsoft.com> wrote in message
news:F540944C-F830-44E1-AADB-A90F7E018E89@.microsoft.com...
> Hi,
> I'm building an aplication in with I need to calculate the number of days
> till the persons next birthday. I already have an UDF that calculates the
age
> given the birthdate and the current date, and I would like to be able to
do
> an UDF that calculates the number of days till next birthday to use it on
a
> computed field.
>|||I have, i've used it to build the other UDF I reffered. But this one isn't
thar simple. I have a php Script that solves this problem, it's something
like this:
"
$nextbirthday =
mktime(0,0,0,"09","12",date("Y"));
// if $nextbirtday is in the past, add 1 year to $nextbirthday
if ($nextbirthday<$nowdate) {
$nextbirthday=$nextbirthday+(60*60*24*36
5);
}
// calculate the remaining day's till my next birthday
$daycount=intval(($nextbirthday-$nowdate)/(60*60*24));
echo " My next birthday is over $daycount day's.";
"
Now I want to transform this into an UDF, to be able to use it on a computed
field.
"Uri Dimant" wrote:

> Look at DATEDIFF system funtion in the BOL
>
> "M?rcio Santos" <Mrcio Santos@.discussions.microsoft.com> wrote in message
> news:F540944C-F830-44E1-AADB-A90F7E018E89@.microsoft.com...
> age
> do
> a
>
>|||Hi
Did u try it this way:
DATEDIFF(d, getdate(), birthdate)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Márcio Santos" wrote:
[vbcol=seagreen]
> I have, i've used it to build the other UDF I reffered. But this one isn't
> thar simple. I have a php Script that solves this problem, it's something
> like this:
> "
> $nextbirthday =
> mktime(0,0,0,"09","12",date("Y"));
> // if $nextbirtday is in the past, add 1 year to $nextbirthday
> if ($nextbirthday<$nowdate) {
> $nextbirthday=$nextbirthday+(60*60*24*36
5);
> }
> // calculate the remaining day's till my next birthday
> $daycount=intval(($nextbirthday-$nowdate)/(60*60*24));
> echo " My next birthday is over $daycount day's.";
> "
> Now I want to transform this into an UDF, to be able to use it on a comput
ed
> field.
>
> "Uri Dimant" wrote:
>|||On Wed, 8 Jun 2005 03:46:02 -0700, "Mrcio Santos" <Mrcio
Santos@.discussions.microsoft.com> wrote:

>Hi,
>I'm building an aplication in with I need to calculate the number of days
>till the persons next birthday. I already have an UDF that calculates the a
ge
>given the birthdate and the current date, and I would like to be able to do
>an UDF that calculates the number of days till next birthday to use it on a
>computed field.
>
Hi Mrcio,
It doesn't look pretty and I have the feeling that there should be an
easier way, but this code will at least get the job done:
DECLARE @.BirthDay smalldatetime
SET @.BirthDay = 'yyyymmdd' -- Insert test data here
SELECT DATEDIFF(day,
CURRENT_TIMESTAMP,
DATEADD(year,
DATEDIFF(year, @.BirthDay, CURRENT_TIMESTAMP)
+ CASE WHEN DATEADD(year, DATEDIFF(year,
@.BirthDay, CURRENT_TIMESTAMP), @.BirthDay) < CURRENT_TIMESTAMP THEN 1
ELSE 0 END,
@.BirthDay))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thx... it works perfectly...
"Hugo Kornelis" wrote:

> On Wed, 8 Jun 2005 03:46:02 -0700, "Márcio Santos" <Márcio
> Santos@.discussions.microsoft.com> wrote:
>
> Hi Márcio,
> It doesn't look pretty and I have the feeling that there should be an
> easier way, but this code will at least get the job done:
> DECLARE @.BirthDay smalldatetime
> SET @.BirthDay = 'yyyymmdd' -- Insert test data here
> SELECT DATEDIFF(day,
> CURRENT_TIMESTAMP,
> DATEADD(year,
> DATEDIFF(year, @.BirthDay, CURRENT_TIMESTAMP)
> + CASE WHEN DATEADD(year, DATEDIFF(year,
> @.BirthDay, CURRENT_TIMESTAMP), @.BirthDay) < CURRENT_TIMESTAMP THEN 1
> ELSE 0 END,
> @.BirthDay))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Calculate days till next birthday

Hi,
I'm building an aplication in with I need to calculate the number of days
till the persons next birthday. I already have an UDF that calculates the age
given the birthdate and the current date, and I would like to be able to do
an UDF that calculates the number of days till next birthday to use it on a
computed field.Look at DATEDIFF system funtion in the BOL
"M?rcio Santos" <Mrcio Santos@.discussions.microsoft.com> wrote in message
news:F540944C-F830-44E1-AADB-A90F7E018E89@.microsoft.com...
> Hi,
> I'm building an aplication in with I need to calculate the number of days
> till the persons next birthday. I already have an UDF that calculates the
age
> given the birthdate and the current date, and I would like to be able to
do
> an UDF that calculates the number of days till next birthday to use it on
a
> computed field.
>|||I have, i've used it to build the other UDF I reffered. But this one isn't
thar simple. I have a PHP Script that solves this problem, it's something
like this:
"
$nextbirthday =mktime(0,0,0,"09","12",date("Y"));
// if $nextbirtday is in the past, add 1 year to $nextbirthday
if ($nextbirthday<$nowdate) {
$nextbirthday=$nextbirthday+(60*60*24*365);
}
// calculate the remaining day's till my next birthday
$daycount=intval(($nextbirthday-$nowdate)/(60*60*24));
echo " My next birthday is over $daycount day's.";
"
Now I want to transform this into an UDF, to be able to use it on a computed
field.
"Uri Dimant" wrote:
> Look at DATEDIFF system funtion in the BOL
>
> "M?rcio Santos" <Mrcio Santos@.discussions.microsoft.com> wrote in message
> news:F540944C-F830-44E1-AADB-A90F7E018E89@.microsoft.com...
> > Hi,
> > I'm building an aplication in with I need to calculate the number of days
> > till the persons next birthday. I already have an UDF that calculates the
> age
> > given the birthdate and the current date, and I would like to be able to
> do
> > an UDF that calculates the number of days till next birthday to use it on
> a
> > computed field.
> >
> >
>
>|||Hi
Did u try it this way:
DATEDIFF(d, getdate(), birthdate)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Márcio Santos" wrote:
> I have, i've used it to build the other UDF I reffered. But this one isn't
> thar simple. I have a PHP Script that solves this problem, it's something
> like this:
> "
> $nextbirthday => mktime(0,0,0,"09","12",date("Y"));
> // if $nextbirtday is in the past, add 1 year to $nextbirthday
> if ($nextbirthday<$nowdate) {
> $nextbirthday=$nextbirthday+(60*60*24*365);
> }
> // calculate the remaining day's till my next birthday
> $daycount=intval(($nextbirthday-$nowdate)/(60*60*24));
> echo " My next birthday is over $daycount day's.";
> "
> Now I want to transform this into an UDF, to be able to use it on a computed
> field.
>
> "Uri Dimant" wrote:
> > Look at DATEDIFF system funtion in the BOL
> >
> >
> > "M?rcio Santos" <Mrcio Santos@.discussions.microsoft.com> wrote in message
> > news:F540944C-F830-44E1-AADB-A90F7E018E89@.microsoft.com...
> > > Hi,
> > > I'm building an aplication in with I need to calculate the number of days
> > > till the persons next birthday. I already have an UDF that calculates the
> > age
> > > given the birthdate and the current date, and I would like to be able to
> > do
> > > an UDF that calculates the number of days till next birthday to use it on
> > a
> > > computed field.
> > >
> > >
> >
> >
> >|||On Wed, 8 Jun 2005 03:46:02 -0700, "Márcio Santos" <Márcio
Santos@.discussions.microsoft.com> wrote:
>Hi,
>I'm building an aplication in with I need to calculate the number of days
>till the persons next birthday. I already have an UDF that calculates the age
>given the birthdate and the current date, and I would like to be able to do
>an UDF that calculates the number of days till next birthday to use it on a
>computed field.
>
Hi Márcio,
It doesn't look pretty and I have the feeling that there should be an
easier way, but this code will at least get the job done:
DECLARE @.BirthDay smalldatetime
SET @.BirthDay = 'yyyymmdd' -- Insert test data here
SELECT DATEDIFF(day,
CURRENT_TIMESTAMP,
DATEADD(year,
DATEDIFF(year, @.BirthDay, CURRENT_TIMESTAMP)
+ CASE WHEN DATEADD(year, DATEDIFF(year,
@.BirthDay, CURRENT_TIMESTAMP), @.BirthDay) < CURRENT_TIMESTAMP THEN 1
ELSE 0 END,
@.BirthDay))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thx... it works perfectly...
"Hugo Kornelis" wrote:
> On Wed, 8 Jun 2005 03:46:02 -0700, "Márcio Santos" <Márcio
> Santos@.discussions.microsoft.com> wrote:
> >Hi,
> >I'm building an aplication in with I need to calculate the number of days
> >till the persons next birthday. I already have an UDF that calculates the age
> >given the birthdate and the current date, and I would like to be able to do
> >an UDF that calculates the number of days till next birthday to use it on a
> >computed field.
> >
> Hi Márcio,
> It doesn't look pretty and I have the feeling that there should be an
> easier way, but this code will at least get the job done:
> DECLARE @.BirthDay smalldatetime
> SET @.BirthDay = 'yyyymmdd' -- Insert test data here
> SELECT DATEDIFF(day,
> CURRENT_TIMESTAMP,
> DATEADD(year,
> DATEDIFF(year, @.BirthDay, CURRENT_TIMESTAMP)
> + CASE WHEN DATEADD(year, DATEDIFF(year,
> @.BirthDay, CURRENT_TIMESTAMP), @.BirthDay) < CURRENT_TIMESTAMP THEN 1
> ELSE 0 END,
> @.BirthDay))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Sunday, March 11, 2012

CAL for MSDE for web site ?

I'm building a web site using MSDE (Rel A) & Visual Studio. Do I have to have any sort of licence to use MSDE ? If so, is it covered by the Visual Studio and/or redistribution rights under Release A ?
Sarah,
You can find vast info here on msde.
http://www.microsoft.com/sql/msde/
-oj
http://www.rac4sql.net
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:53059AD9-694E-4BF0-A57B-86C379D9A0C6@.microsoft.com...
> I'm building a web site using MSDE (Rel A) & Visual Studio. Do I have to have
any sort of licence to use MSDE ? If so, is it covered by the Visual Studio
and/or redistribution rights under Release A ?

Thursday, March 8, 2012

Caching Application Block and SQL 2005 SQL Dependency

I am building a web app using VS2005 and SQL 2005 I would like to use the Caching Application Block to cache objects from my BLL. I was wondering if there is a way of utilizing the build in SQLDependency in SQL 2005 with the Caching Application Block? Does anybody have tried this, are there any samples on the web?

Thanks,

Newbie

You can take a look at

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

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

Tuesday, February 14, 2012

Business Rules -> Using Lots of UDFs & Views

I am in the process of building my first "large scale" database system (after 15+ years of developing Windows Apps and Web Apps) - so I am very VERY "Green" when it comes to Database development & SQL et al.

A little context setting: I am building a multi-tier Statistical Analysis & Reporting system where the "end product" will be Reports created in Reporting Services. There are a ton of business rules that I am implementing in a Business Logic Tier (hidden from the "end user" by a Data Access Tier) comprised of SQL in the form of UDFs (scalar) and Views.

The question: I have been reading that UDFs cause a performance hit compared to things like in-line functions. Alot of the Rules (implemented as Scalar UDFs) build on each other so that the output of UDF #1 is used as input to UDF #2.

So far I am implementing the Business Logic as a hierarchy of Views (7 Views to be exact) with each view implementing multiple Rules; each Rule basically a Scalar UDF. Below is an example of what I am doing:

Example

View #1 -> Select A, B, C, funcX1(A) as ValueX1, funcY1(B, C) as ValueY1 FROM someView

Then

View #2 -> Select A, B, C, ValueX1, ValueY1, funcX2 (ValueX1) as ValueX2, funcY2(ValueY2) as ValueY2 FROM View#1

Currently I have a hierarchy of 7 views that each use UDFs to implement the Business Rules, where the value calculated from a UDF in one View is used as input to UDF in a View further down the Hierarchy.

Is there a better way of implementing all of the Rules instead of using multiple Views with a bunch of UDFs?

The "end product" dataset is then exposed as a Stored Procedure to the reports in Reporting Services.

Any help would be GREATLY appreciated.

Thanks!
- marty

You can make View #1 -> Select A, B, C, funcX1(A) as ValueX1, funcY1(B, C) as ValueY1 FROM someView
and

View #2 -> Select A, B, C, ValueX1, ValueY1, funcX2 (ValueX1) as ValueX2, funcY2(ValueY2) as ValueY2 FROM View#1

as indexed views (if your func's are deterministic ) that will improve performance

see Books Online (look for "indexed views")

To the other part I see a reccurence in building your views; another new feature of SQL 2005 is "common table expressions" that can permit creating recursive queries (look Books Online for "common table expressions" )

|||

Hi!

Well it seems you already got an answer, but here something I would like to add:

For business rules:

use 3NF design (or dw design), use constraints (fk, check constraints, uk/indexes) whenever possible

and use stored procedures as access layer (when not using dw design).

With stored procedures as layer between you highly control access and have freedom to change

your implementation (table design) behind the scenes without breaking code.

This comes escpecially useful, when you have to solve performance problems!

|||You guys Rock!

Using a CTE works like a charm! Using a CTE in a View allows me to use the output of 1 function as input to a 2nd function within the same View.

I am I'm now looking into Indexed Views to see about improving the performance of my Business Rules.

Thanks Again!

Sunday, February 12, 2012

Business Intelligence Report Parameter Prompt Multi

I have my 2 datasets and my parameter set up to display building to select from. My issue is I get multiple values in the Select Value Prompt. It will pull the correct data I just want to clean up the Select Value Prompt.

Example:

Building 1

Building 1

Building 1

Building 2

Building 2

Dataset 1:

Select *

From Table

Dataset 2:

SELECT *

From Table

WHERE (Table.Building = @.Building)

ORDER BY Client.ctg

I'm assuming that you're using Dataset1 to populate the parameter. If so, try using the keyword DISTINCT in your SQL query for Dataset1.

SELECT DISTINCT
BuildingNameField
FROM Table (NoLock)
ORDER BY BuildingNameField

|||I can't use the distinct because it will cut down my data and not give me an accurate query. Unless the is a way to do it that I am not aware of. What is the (nolock)?|||What had to do is make a 3rd dataset in the report, w/ just the select distinct statement in it and then, change the available values dataset to the one that is just the select distinct.|||

The "nolock" (also written as "with nolock") insures that the table isn't locked while running the query. I always use this as a general practice when I'm running a query that really doesn't need to lock the table.

About 99.9% of the time, I always create a seperate dataset for parameters. This way, if you need to make changes to the dataset, you don't have to worry about screwing up data for something else.

Thanks,

Micheal

BUSINESS DIRECTORIES FOR TARGET MARKETING

Target marketing your business is important to building your business. This is true even if you have the greatest product or service in the world.
LATEST DATABASES AVAIALABLE
1) All India Companies Database CD.
Contains more than 60,000 complete details of Companies / Industries including Exporters all over India. Details include Company Name, Product Profile, Full Postal Address, Phone No., Fax No., Email Address (about 21000), Website Address (wherever availa
ble). - Price : Rs.1500/-
2) Mumbai Companies Database CD.
Contains more than 75,000 complete details of Mumbai Companies /
Industries all over Mumbai. Details include Company Name, Contact Person Name and Designation, Full Postal Address, Phone No., Fax No., Email Address (wherever available), Website Address (wherever available), Activity etc. - Price : Rs.2500/-
3) Indian Corporates' Database CD.
(More than 2,50,000 (2.5 Lakhs) Indian Corporates with full contact
details including Name, Company Name, Address, Phone No. , Fax No. andEmail id.) ALL Records contains Email Addresses. -
Price:R s.2500/-
4) Over 7.5 Lacs Email ids of Indian Companies.Contains Email ids in
text files - Price : Rs. 750/-
5)NRIs (Non Resident Indians) Database CD.
The Database includes complete contact details of over 1,50,000 (1.5 Lac) NRIs residing all over the world. Details include Name, Full Postal Address including Country and Postal code, Phone Number, Fax Number and Email ID. ALL Records contains Email
Addresses. - Price : Rs.5000/-
6)High Society Peoples' Database CD.
Database includes details of approx. 450 High Society People including their Name, Full Residence and Office Address, Residence Phone Nos.,Office Phone Nos., Mobile No. (Wherever available) High Society People including: Ambanis (Reliance), Bajaj, Singhan
ia (Raymonds), etc. -Price : Rs.1000/-
7)American Companies Database CD.
Contains 1.6 million (16 Lacs) American companies records which
includes the following details: First Name, Last Name, Company Name, Email Address,Phone Number and Fax Number and SIC Code (Standard Industrial Classification Code) ALL Records contains Email Addresses. A good marketing tool for companies or individuals
looking to reach American Companies. - Price : Rs.5000/-
8) Chinese Companies' Database CD.
Contains more than 5,00,000 (5 Lacs) complete details of Suppliers,
Manufacturers, Companies / Industries all over China. Details include Company Name, Product Profile, Full Postal Address, Phone No., Fax No.,Email Address (wherever available), Website Address (wherever available).- Price : Rs.3000/-
9)Gulf Business Database CD.
Over 20,000 Records. Includes: Company Name, Full Postal Address, Telephone No., Fax No., Email Address (wherever available), Website Address(wherever available) and Activities indulged in or Products in which they are dealing - Price : Rs.1500/-
10)World Business Database CD.
(More than 3,00,000 (3 Lacs) records from all over the world with full contact details including email ids) ALL Records contains Email Addresses. - Price : Rs. 3000/-
11)Top 500 Blue Chip Companies CD.
The CD. covers Top 500 Blue Chip Companies. The Database includes Company Name, Name of CEO, Postal Address, City, State, Pincode, Phone Number, Fax Number and the Stock Exchanges on which they are listed. - Price : Rs.1500/-
12)Vnsl Ids of all major cities in India.
Approximate Quantity is 1.5 lacs and more. - Price : Rs.750/-
13)2.5 lacs Indian Hotmail email ids(Newly ADDED)
By using this new collection of email ids you can reach over lacs of Indian Hotmail users. - Price : Rs.750/-
14)Over 3 Lacs Rediffmail email addresses (Newly ADDED) - Price : Rs.750/-
15) 2.5 lacs Indian Yahoo email ids (Newly ADDED)
By using this new collection of email ids you can reach over lakhs of Indian Yahoo users. - Price : Rs.750/-
16) Pune Companies Yellow Page Database
Contains 1010 Records containing Company Name, Address, Telephone,Email, City and Category - Price : Rs.500/-
17) Singapore Exporters Database
Contains 3500 company names, addresses, postal code, telephone, telefax, web site address (where ever available), email address (where ever available). - Price : Rs. 1500/-
18) Mumbai DOCTORS
Contains 2000 Records of doctors in Mumbai and very close to Mumbai with details such as name, address, city, pin Telephone- Res and Clinic. - Price : Rs.1000/-
19) Young People of INDIA
contains 25,000 Records with details like name, age, sex, email. Price : Rs.500
20)Indian Hotels Directory CD.
Reach hundreds and thousands of Hotels and Restaurants all over India with Indian Hotels Directory, which is now available on a CD-Rom. FullDetails like Name, Address, Phone No. Fax No., Contact Person, Designation, Category, Cuisine Served etc. are given
. - Price : Rs.1000/-
21) 3 Million Indian email addresses (CD) - Price : Rs.1500
22) 220 Million International email addresses (2 CDs) - Price : Rs.5000
24) Over 7,50,000 Worldwide Importers Email Addresses +75,000 Exporters-Importers Company Database (Indian and International ) Incl. details like Name, Full Postal Address, Phone, Fax, Email, etc. (CD) - Price : Rs.2500
Most of the Databases are in MS-access and/or MS-excel format with facility to search,print labels etc . DATABASES LAST UPDATED IN December 2003.
Hope yhat you would be interested.
If so, send payment by MONEY ORDER or DEMAND DRAFT in favour of R.KRISHNAN payable at METTUR.
DO NOT MISS SUCH AN OPPORTUNITY !!!! BUY NOW !!!
R.KRISHNAN
R 3/2, R.E.QUARTERS
METTUR - 636 401
TAMILNADU
INDIA.
krishnan_raj30@.yahoo.co.in
ebizness@.sify.com
Mobile : 098426 -27664
DELIVERY : CDs will be despatched within 48 Hours.
FREE BONUS : AD-BLASTING software to submit your site to 2,500,000 websites automatically.
hi, i am marketing private aircharters and air ambulances so i wanted database catering to that class and high society people.. am also intrested in data base for all the major hospitals and major cardiac doctors and surgons..
but their maid id and telephone nos are very impaortant....if you can help plz revert back. my mail id is

anil.hiflying@.gmail.com