Custom SQL

Hi,

I have a sql statement:

SELECT [ItemName], [Startprice], [Percentreduction], [Quantityavailable], [PhotoURL], [proID] FROM [items] WHERE ([featured] = @.featured)

but I would like to add in 2 more where clauses. One is AND (aswell as) the current one, so

WHERE ([featured] = @.featured) AND ([Quantityavailable > @.Quantityavailable) ?(@.Quantity available value set to 0)(is that right?)

and also I want another AND which is taken from another column. i.e:

WHERE ([featured] = @.featured) AND ([Quantityavailable > @.Quantityavailable) AND ([numberclickedin< *the number from the numtaken column*])

So I guess my 2 questions are:

1. is the format right for my custom sql statements.

2. how do I get the number from the numtaken column to dynamically enter into the third statement?

Thanks,

Jon

SELECT [ItemName], [Startprice], [Percentreduction], [Quantityavailable], [PhotoURL], [proID] FROM [items] WHERE ([featured] = @.featured) AND ([Quantityavailable] > @.Quantityavailable) AND ([numberclickedin] < [numtakenin])

You can use a field just like you use the @.QuantityAvailable parameter value. So one field can be compared against another just fine. I don't see any problem with that query...

|||

Hi

SELECT [ItemName], [Startprice], [Percentreduction],[Quantityavailable], [PhotoURL], [proID]

FROM [items]

WHERE (

([featured]= @.featured) AND

([Quantityavailable > 0) AND -- Comment: You can also say ([Quantityavailable > @.Quantityavailable) or both using AND --

Just not sure what you mean by the last part numclickedin...? I think you probably need Count( A Field Name ) as totalNum. Can you explain a bit more please.

|||

Hi, thank you both for you help!

To bmains: So it is as simple as that, to compare columns against each other you just put them in? You dont have to use extra code or anything to retrieve their data?

To anyone:

How about using data from 2 columns to fill in a third columns value?e.g. numberproducts = ([numberclickedin] *divided by* [numtaken]) ? or adding, subtracting and multiplying?

Thanks,

Jon!

|||

Hey,

Yes, it is that simple, and you can use multiplication and such, just beware of division if the value is zero (to avoid division by zero, you could even add to the where statement "and numtaken <> 0). Actually, you would be surprised how dynamic you can get with queries, when it comes to T-SQL in SQL Server, and PL-SQL in Oracle.

|||

Hi,

Im glad something is simple!

What symbols do I use for multiplication etc? * / + - ?

Thanks,

Jon

|||

Yep, the standard arithmetic operators are the ones you use, plus ( ) in the standard math way.

The [ ] brackets you were using in your first statement were gummed up.

They are used around database names, schema names, table/view names and column names.

They are not used around expressions. Take a look back at your statement and you will see partial [ ] pairs. You have to be careful about that!

Also, they are only needed if you have spaces or weird characters in the name in question, or if you have used a keyword.

My basic rule of thumb is "Don't do that." and everyone's life is simpler.

|||

Thanks!

Jon

Custom Report Items

Hi,
I want change reports' style after the deployment, so each client can have his style.
Nobody know I can do?

(change the color of table/matrix, insert a image...)Hi,

this can′t be done by a normal predefined report, you have to use the report builder for that. By creating a report model you wil lhave the avaibility to let the user create his own reports from scratch.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Custom Report Item

Hello all,

Could any one point me to some documents on building custom report items.

Thanks!!

SqlNew

A good place to start would be the BOL (Books Online).

Here is the starting point for Custom Report Items: http://msdn2.microsoft.com/en-us/library/ms345231.aspx

Custom Order By...Revisited

Hi

I would like to return rows in a custom order, similar to this post. However, in my case I do not have a fixed number of 'sort by' items. The order I would like to have returned from Col2 is:

'A' Numeric Values in numeric order 0-99 All other alpha values

For example:
'A', '0', '1', '3', '65', 'MyValue1', 'MyValue2'

How could I achieve this?

thanks

Richard

Here You Go...

Code Snippet

Create Table #sorttest (

[Col2] Varchar(100)

);

Insert Into #sorttest Values('A');

Insert Into #sorttest Values('12');

Insert Into #sorttest Values('1');

Insert Into #sorttest Values('3');

Insert Into #sorttest Values('5');

Insert Into #sorttest Values('6');

Insert Into #sorttest Values('78');

Insert Into #sorttest Values('100');

Insert Into #sorttest Values('MyValue1');

Insert Into #sorttest Values('MyValue3');

Insert Into #sorttest Values('MyValue2');

Select

*

From

#sorttest

Order By

Case When Col2 Like '[A-Z]' Then 1

When Isnumeric(Col2)=1 Then 2

Else 3 End,

Case When Isnumeric(Col2)=1 Then Convert(float,Col2) End,

Col2

/*

::Output

Col2

-

A

1

3

5

6

12

78

100

MyValue1

MyValue2

MyValue3

*/

|||hi, try this

SELECT MyMixedField
FROM MyTable
WHERE PATINDEX('%[0-9]%', MyMixedField) = 0
UNION ALL
SELECT MyMixedField
FROM MyTable
WHERE ISNUMERIC(MyMixedField) = 1
UNION ALL
SELECT MyMixedField
FROM MyTable
WHERE PATINDEX('%[0-9]%', MyMixedField) > 0
and ISNUMERIC(MyMixedField) <> 1|||i think manivannan's solution is much better,

@.manivannan..

it think its better to use patindex('%[0-9]%', col2) = 0 for alpha valued records

Order By

Case When patindex('%[0-9]%', col2) = 0 Then 1

When Isnumeric(Col2)=1 Then 2

Else 3 End,

Case When Isnumeric(Col2)=1 Then Convert(float,Col2) End,

Col2|||thank you both very much for your quick and accurate responses!

Works perfectly
|||

It seems that manivannan's suggested use of

Like '[A-Z]'

is most likely the best option to sort on single letter alpha. Don't you think that the

patindex('%[0-9]%', col2) = 0

suggestion 'might' include things like an asterisk, period, @. symbol, $ symbol, etc.? (-As well as allowing multi-character alpha entries on the first sort level...)

However, I would caution that using manivannan's suggestion to use isnumeric() 'could' cause problems. For example, the following evaluates to TRUE

SELECT isnumeric( '$' )

But is it a number? Should it sort with the alphas?

|||Hi

Good point, but actually in this case we don't allow non-alphanumeric characters in this field, so it's not really an issue!

thanks everyone for help on this

Custom Grouping

My reqmt is to group records based on items purchased. I hv 2 groups cat1 ,cat2 with different set of items in them. My third group cat3 will contain customers who have purchased any items from cat1 AND cat2.

My formula is something like
if {table.itemnumber} in ['abc','def',..] then 'cat1:'
else
if {table.itemnumber} in ['xyz','lkm',...] then 'cat2:'
else
if ({table.itemnumber} in ['abc','def',...] AND {table.itemnumber} in ['xyz','lkm',...]) then 'cat3'

Am not able to get the cat3(3rd Group) on my report. Any thots on wat I cud b missing?

Am using Crystal Reports XII'd guess that your condition checks are in the wrong order.
You'll never get to the final if statement 'cos one of the previous two will always be true if the 3rd is true.|||Sorry if i have confused u. My reqmt is to get a count of all customers who buy items. Cat1 will have a set of items. Cat2 will have another set of items. My third group will contain items frm both cat1 and cat2. I sud basically get a count of customers in each cat.

I have grouped my report on items using the formula
if {table.itemnumber} in ['abc','def',..] then 'cat1:'
else
if {table.itemnumber} in ['xyz','lkm',...] then 'cat2:'
else
if ({table.itemnumber} in ['abc','def',...] AND {table.itemnumber} in ['xyz','lkm',...]) then 'cat3'

My report sud luk something like this :
Count(customer)
Cat1 xxxx
Cat2 xxxxx
Cat3 xxx(sud be the count of customers who hv bought frm cat1 and cat2)|||Assuming that your cat1 items and cat2 item lists are mutually exclusive then the 3rd if statement can never be true.
If there is an overlap (which I suspect there isn't, otherwise an item would be both a cat1 and cat2 item) then my previous comment is still true.

i.e. you will never get a cat3 with that formula.

Your main problem, however, is that you want to catagorise a customer, and your formula categorises an item.

Custom Formatting

I need to Format some items in a different way.
for example I have values that are .962 and want
the report to format this to 0.962 but
if say the value is .65 I want it to format it as 0.65
how would I do this?Patrick,
Try Format(Convert.ToDouble("<your field>"), "0.00")
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Patrick K" <PatrickK@.discussions.microsoft.com> wrote in message
news:FD864917-BC4A-4DFB-912F-B893AD8B6AC5@.microsoft.com...
> I need to Format some items in a different way.
> for example I have values that are .962 and want
> the report to format this to 0.962 but
> if say the value is .65 I want it to format it as 0.65
> how would I do this?|||"Teo Lachev [MVP]" wrote:
> Patrick,
> Try Format(Convert.ToDouble("<your field>"), "0.00")
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Patrick K" <PatrickK@.discussions.microsoft.com> wrote in message
> news:FD864917-BC4A-4DFB-912F-B893AD8B6AC5@.microsoft.com...
> > I need to Format some items in a different way.
> > for example I have values that are .962 and want
> > the report to format this to 0.962 but
> > if say the value is .65 I want it to format it as 0.65
> >
> > how would I do this?
>
> That is good but what if the values are mixed, some are .962 and others are .65|||Then use a custom function which will determine the length of the string and
figure out the format specifier.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Patrick K" <PatrickK@.discussions.microsoft.com> wrote in message
news:74808F24-4605-4DAD-8F55-00D80D677B8F@.microsoft.com...
>
> "Teo Lachev [MVP]" wrote:
> > Patrick,
> >
> > Try Format(Convert.ToDouble("<your field>"), "0.00")
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ---
> >
> > "Patrick K" <PatrickK@.discussions.microsoft.com> wrote in message
> > news:FD864917-BC4A-4DFB-912F-B893AD8B6AC5@.microsoft.com...
> > > I need to Format some items in a different way.
> > > for example I have values that are .962 and want
> > > the report to format this to 0.962 but
> > > if say the value is .65 I want it to format it as 0.65
> > >
> > > how would I do this?
> >
> >
> > That is good but what if the values are mixed, some are .962 and others
are .65

Custom DataFlow Transformation not showing up in toolbox

I've created a custom data flow tranformation and it isn't showing up in the Tool Box Items to be added under the Data Flow Items tab (right click on tool box, 'Choose Items...', then clicked Data Flow Items).

I have done the following:

signed the assembly,

added to GAC,

copied the dll to C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents.

It worked previously when I was just starting out, however now I cannot see it. What would cause it to not show up? Everything compiles fine. How would I determine how to fix it so that it shows up?

Apart from the component being in the GAC, there are two key steps to be taken for a custom component to be recognized by the BI Studio:

placing the binary under the folder where SSIS design time checks to enumerate pipeline components, which is at <SQL Installation Folder>\90\DTS\Pipeline Components (I'd double check this on the target machine via the reg key at the very bottom, cause it could be different on a 64bit or in a production server)