Create a script rename value of a column

Hi,
Is there a way, to make a script that changes the values of a column
I have a tabel called "fasesrt"
That tabel has 3 columns
Fase & Fasesrtom & fasegrpnr
In the column fasesrtom I have 6 values that I want to change,
I know the old value and the new value
Can Anyone help me to make a script, If I have a start, I think I can do it.
I just need a start.
Thanks
JosUPDATE Fasesrt
SET fasesrtom =
CASE fasesrtom
WHEN 'value 1' THEN 'new value 1'
WHEN 'value 2' THEN 'new value 2'
WHEN 'value 3' THEN 'new value 3'
WHEN 'value 4' THEN 'new value 4'
WHEN 'value 5' THEN 'new value 5'
WHEN 'value 6' THEN 'new value 6'
END
WHERE fasesrtom IN
('value 1',
'value 2',
'value 3',
'value 4',
'value 5',
'value 6')
David Portas
SQL Server MVP
--|||You have to use an UPDATE statement, and possibly the REPLACE function. See
SQL Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jos" <Jos.Jeurissen@.mail.ing.nl> wrote in message
news:046DE39B-8B5A-4397-9C10-33F89C6FFA36@.microsoft.com...
Hi,
Is there a way, to make a script that changes the values of a column
I have a tabel called "fasesrt"
That tabel has 3 columns
Fase & Fasesrtom & fasegrpnr
In the column fasesrtom I have 6 values that I want to change,
I know the old value and the new value
Can Anyone help me to make a script, If I have a start, I think I can do it.
I just need a start.
Thanks
Jos

Create a script rename value of a column

Hi,
Is there a way, to make a script that changes the values of a column
I have a tabel called "fasesrt"
That tabel has 3 columns
Fase & Fasesrtom & fasegrpnr
In the column fasesrtom I have 6 values that I want to change,
I know the old value and the new value
Can Anyone help me to make a script, If I have a start, I think I can do it.
I just need a start.
Thanks
Jos
UPDATE Fasesrt
SET fasesrtom =
CASE fasesrtom
WHEN 'value 1' THEN 'new value 1'
WHEN 'value 2' THEN 'new value 2'
WHEN 'value 3' THEN 'new value 3'
WHEN 'value 4' THEN 'new value 4'
WHEN 'value 5' THEN 'new value 5'
WHEN 'value 6' THEN 'new value 6'
END
WHERE fasesrtom IN
('value 1',
'value 2',
'value 3',
'value 4',
'value 5',
'value 6')
David Portas
SQL Server MVP
|||You have to use an UPDATE statement, and possibly the REPLACE function. See
SQL Server Books Online for more information.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jos" <Jos.Jeurissen@.mail.ing.nl> wrote in message
news:046DE39B-8B5A-4397-9C10-33F89C6FFA36@.microsoft.com...
Hi,
Is there a way, to make a script that changes the values of a column
I have a tabel called "fasesrt"
That tabel has 3 columns
Fase & Fasesrtom & fasegrpnr
In the column fasesrtom I have 6 values that I want to change,
I know the old value and the new value
Can Anyone help me to make a script, If I have a start, I think I can do it.
I just need a start.
Thanks
Jos
sql

Create a script rename value of a column

Hi
Is there a way, to make a script that changes the values of a colum
I have a tabel called "fasesrt
That tabel has 3 columns
Fase & Fasesrtom & fasegrpn
In the column fasesrtom I have 6 values that I want to change
I know the old value and the new valu
Can Anyone help me to make a script, If I have a start, I think I can do it
I just need a start
Thank
JoUPDATE Fasesrt
SET fasesrtom = CASE fasesrtom
WHEN 'value 1' THEN 'new value 1'
WHEN 'value 2' THEN 'new value 2'
WHEN 'value 3' THEN 'new value 3'
WHEN 'value 4' THEN 'new value 4'
WHEN 'value 5' THEN 'new value 5'
WHEN 'value 6' THEN 'new value 6'
END
WHERE fasesrtom IN
('value 1',
'value 2',
'value 3',
'value 4',
'value 5',
'value 6')
--
David Portas
SQL Server MVP
--|||You have to use an UPDATE statement, and possibly the REPLACE function. See
SQL Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jos" <Jos.Jeurissen@.mail.ing.nl> wrote in message
news:046DE39B-8B5A-4397-9C10-33F89C6FFA36@.microsoft.com...
Hi,
Is there a way, to make a script that changes the values of a column
I have a tabel called "fasesrt"
That tabel has 3 columns
Fase & Fasesrtom & fasegrpnr
In the column fasesrtom I have 6 values that I want to change,
I know the old value and the new value
Can Anyone help me to make a script, If I have a start, I think I can do it.
I just need a start.
Thanks
Jos

Create a Rolling summary field

I need to make a report that takes a rolling 12 month period.
Each row needs to show the sum of the current months value and all the 11 months prior.

For example, I have a count of items in their respective Months

2 - Jan 06
5 - Feb 06
2 - Mar 06
4 - April 06
2 - May 06
1 - June 06
2 - July 06
5 - Aug 06
2 - Sep 06
2 - Oct 06
4 - Nov 06
2 - Dec 06
2 - Jan 07
3 - Feb 07
1 - Mar 07

What I need as an output is

Jan 07 = 35
Feb 07 = 33
Mar 07 = 32

So each row sums the current and previous 11 rows.

I am using Crystal Report v10 and SQL Database.

Thanks,
DavidThe quick solution in my mind is.

Create a subreport and store the whole data in an array.
Now come to the actual report and while showing the current record you can sum the previous 11 months records from the array and show it there.|||Great thanks for the Idea, I got it working with an array and then another formula summing the 12 in the array. I haven't played with the chart function yet, i have to get all these fields totaling first. Any suggestions getting all the fields in the details section ploted into a chart (the ultimate goal. Each displayed point is a date and the summed 12 month total.|||Keep one thing in mind while dealing with the charts.

You can show/draw more then one values on change of some value. e.g on change of date you can show the sum of previous 11 months.

Also you can get the accumulative .

You have to explore the charts for your project.|||Thanks so much for your help. Sometimes I just need a little bump into the right direction and I can figure it out. I got it working now.

Create a calculated field that gives me the avg 75 percentile

I'm trying to create a calculated field that gives me the avg 75 percentile.

Right now I get this value by doing the following:

Create data set:

Select top 75 percent <field>

from <table>

Then I create the following calculated field

Avg(Fields!<field>.value,"<data_set_name>")

But I wanted to be able to create a calculated field that gives me the avg 75 percentile without creating a separate data set to get the top 75 percent Value.

Is it possible?

Thanks!

Either you can use the existing dataset and have a filter on it (Edit Dataset -> Filters tab) with Top %

OR

Write a custom function in report code (Report -> Report Properties -> Code) that will take an array of values and returns avg of top 75% from that array. To do that just call the function in your textbox expression as:

Code.AvgFunction(Fields!YourField.Value)

When you just sent Fields!YourField.Value, a whole array of values in that fields will be sent and you can receive it in an object array in your function. Then use any of the most optimized logics to calculate top 75% from that array and then calculate the average using any of the vb functions.

Shyam

|||

Shyam,

Thanks for your posting...

I'm having problems displaying correct data using TOP 75 %

I use the following data set:

select top 75 percent <field>

from <table>

order by <field>

My report is grouped by month Jan 2007, Feb 2007, etc...

If I select one month date range, for instance february the TOP 75 % returns 474 rows and that's correct, however if my date range includes other months, for instance range from 01/01/2007 thtough 02/28/2007 the Top 75 % results increases for the month of february and January. It will return 490 rows for february and more rows for January too.

My report only returns correct data if I select one month range.

What am I doing wrong?

Thanks, Susan

|||

Sorry for a delayed reply. The solution for your problem is simple.

Edit your group in the report and in the Filters tab and under Expression type "=Fields!YourField.Value" and under Operator select Top % and under Value type 75.

Shyam

|||

Shyam,

Thanks for your response.

Adding the filter above didn't make a difference. It's returning the same results.

when I display data for the month of February I get the following:

@.Month--Count of records--Accept Top 75%

Feb 2007 --498--231.79

when I run for the month of February and January I get the following:

@.Month--Count of records--Accept Top 75%

Feb 2007--512--260.46

Jan 2007--565--309.43

and so fourth, here I added december:

@.Month--Count of records--Accept Top 75%

February-2007-- 508-- 252.29

January-2007-- 560-- 300.59

December-2006-- 488-- 258.64

I expected results for February to remain the same when add months to my view.

Thanks, Susan

|||Remove the TOP 75% clause from your SQL query and have them only in group filters.|||

Shyam,

I had tried that before but the Top% in the Filter tab of the group does't seem to work. Whem I remove the top 75 percent from the sql statement and just leave the Top% =75 filter it totally ignores the filter and it displays the total row count and total average instead.

Well, thanks for the help.... :-)

|||

Top 75% of feb month alone is always going to be different from top 75% of both jan and feb combined. So first of all, you have to remove the top 75% from your sql query. Then make sure you are adding the filter on the group on the same field which you had used in your query. Also, try to convert the field to string using CStr function in the filter expression.

Shyam

|||

Shyam,

Thanks for all your help so far.

I noticed that the filter works based on the row count of the group it's in. My Month group only returns on row, that's why the filter doesn't seem to work, because it's trying to return 75% of one row, which is one row. If I put the filter in the detail row of the report it works, but the problem is that I have to group the detail row before the filter can work, if I group the detail row I won't return all the detail data I need. Is there a way to make the month group filter look at the field of the detail row?

Also, in the report I have to use Bottom% =75 which is the equivalent of the SQL - top 75 percent.

|||

You can use any field from the dataset that is bound to the table for filyering in your group. Using the appropriate field for filtering also makes a big difference in the results obtained.

Shyam

Create a calculated field that gives me the avg 75 percentile

I'm trying to create a calculated field that gives me the avg 75 percentile.

Right now I get this value by doing the following:

Create data set:

Select top 75 percent <field>

from <table>

Then I create the following calculated field

Avg(Fields!<field>.value,"<data_set_name>")

But I wanted to be able to create a calculated field that gives me the avg 75 percentile without creating a separate data set to get the top 75 percent Value.

Is it possible?

Thanks!

Either you can use the existing dataset and have a filter on it (Edit Dataset -> Filters tab) with Top %

OR

Write a custom function in report code (Report -> Report Properties -> Code) that will take an array of values and returns avg of top 75% from that array. To do that just call the function in your textbox expression as:

Code.AvgFunction(Fields!YourField.Value)

When you just sent Fields!YourField.Value, a whole array of values in that fields will be sent and you can receive it in an object array in your function. Then use any of the most optimized logics to calculate top 75% from that array and then calculate the average using any of the vb functions.

Shyam

|||

Shyam,

Thanks for your posting...

I'm having problems displaying correct data using TOP 75 %

I use the following data set:

select top 75 percent <field>

from <table>

order by <field>

My report is grouped by month Jan 2007, Feb 2007, etc...

If I select one month date range, for instance february the TOP 75 % returns 474 rows and that's correct, however if my date range includes other months, for instance range from 01/01/2007 thtough 02/28/2007 the Top 75 % results increases for the month of february and January. It will return 490 rows for february and more rows for January too.

My report only returns correct data if I select one month range.

What am I doing wrong?

Thanks, Susan

|||

Sorry for a delayed reply. The solution for your problem is simple.

Edit your group in the report and in the Filters tab and under Expression type "=Fields!YourField.Value" and under Operator select Top % and under Value type 75.

Shyam

|||

Shyam,

Thanks for your response.

Adding the filter above didn't make a difference. It's returning the same results.

when I display data for the month of February I get the following:

@.Month--Count of records--Accept Top 75%

Feb 2007 --498--231.79

when I run for the month of February and January I get the following:

@.Month--Count of records--Accept Top 75%

Feb 2007--512--260.46

Jan 2007--565--309.43

and so fourth, here I added december:

@.Month--Count of records--Accept Top 75%

February-2007-- 508-- 252.29

January-2007-- 560-- 300.59

December-2006-- 488-- 258.64

I expected results for February to remain the same when add months to my view.

Thanks, Susan

|||Remove the TOP 75% clause from your SQL query and have them only in group filters.|||

Shyam,

I had tried that before but the Top% in the Filter tab of the group does't seem to work. Whem I remove the top 75 percent from the sql statement and just leave the Top% =75 filter it totally ignores the filter and it displays the total row count and total average instead.

Well, thanks for the help.... :-)

|||

Top 75% of feb month alone is always going to be different from top 75% of both jan and feb combined. So first of all, you have to remove the top 75% from your sql query. Then make sure you are adding the filter on the group on the same field which you had used in your query. Also, try to convert the field to string using CStr function in the filter expression.

Shyam

|||

Shyam,

Thanks for all your help so far.

I noticed that the filter works based on the row count of the group it's in. My Month group only returns on row, that's why the filter doesn't seem to work, because it's trying to return 75% of one row, which is one row. If I put the filter in the detail row of the report it works, but the problem is that I have to group the detail row before the filter can work, if I group the detail row I won't return all the detail data I need. Is there a way to make the month group filter look at the field of the detail row?

Also, in the report I have to use Bottom% =75 which is the equivalent of the SQL - top 75 percent.

|||

You can use any field from the dataset that is bound to the table for filyering in your group. Using the appropriate field for filtering also makes a big difference in the results obtained.

Shyam

sql

CREATE / EXECUTE Stored Procedure (SQL Server 7.0)

Hi,
I create one stored procedure to return some objects, this procedure accepts
two parameters and this parameters have a default value.
When i execute the procedure with no values the default parameters are used
and every thing goes fine, when i need to input these parameters the
following error appears.
--exec procedure_name db_name, user_name
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user_name'.
Can you help me.
Best regardsI guess it should be
exec procedure_name 'db_name', 'user_name'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
> accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
> used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>|||If quoting the parameters doesn't fix it, then post the SP source so we can
see.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>

CREATE / EXECUTE Stored Procedure (SQL Server 7.0)

Hi,
I create one stored procedure to return some objects, this procedure accepts
two parameters and this parameters have a default value.
When i execute the procedure with no values the default parameters are used
and every thing goes fine, when i need to input these parameters the
following error appears.
--exec procedure_name db_name, user_name
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user_name'.
Can you help me.
Best regardsI guess it should be
exec procedure_name 'db_name', 'user_name'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
> accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
> used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>|||If quoting the parameters doesn't fix it, then post the SP source so we can
see.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>sql

CREATE / EXECUTE Stored Procedure (SQL Server 7.0)

Hi,
I create one stored procedure to return some objects, this procedure accepts
two parameters and this parameters have a default value.
When i execute the procedure with no values the default parameters are used
and every thing goes fine, when i need to input these parameters the
following error appears.
--exec procedure_name db_name, user_name
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user_name'.
Can you help me.
Best regards
I guess it should be
exec procedure_name 'db_name', 'user_name'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
> accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
> used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>
|||If quoting the parameters doesn't fix it, then post the SP source so we can
see.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>

CR11 - How do I choose a NULL value (or ALL RECORDS) in a dynamic parameter?

We are using CR11 and our own OLE DB provider that, in turn, uses our SQL Server database.

We are creating dynamic parameters that allow the user to pick from all the distinct values that a field can have. All but NULL, and we need to allow the user to pick NULL if there's any in the data.

In reality, we just need to find a way to let the user pick ALL values. However, if we create a dynamic parameter that doesn't accept multiple values, the user is forced to pick one of the available values or, if he doesn't, then CR will say the value is not valid when you hit OK to the parameter selections. If instead we create a parameter that accepts multiple values instead, the user has the option to pick all the available values, which at first seems to solve the problem, but these wouldn't include NULLs and those records would be filtered out in the results.

Thank you in advance.Anybody?|||You can try using two separate parameters in your Crystal Report (http://www.shelko.com). One can be a choice between all or specific and the second could be your list of specifics. Then base your selection criteria on the combination of these two parameters.

CR as blank report

1. is it possible to create just blank report and pass the value for it via VB?

2. is it possible that in that blank report i want to use ORDER BY certain condition that determined by condition that user want..ex : by date, by supplier, etc

can anyone explain me how to do it..
thx..Why would you want to design a report that has no fields and no data connection?
You use Crystal to establish the data structures used by the report, and how they will be grouped, etc.
How do you propose to test the report to ensure that it work properly, if you don't design it first?

dave|||thx springsoft
sorry for my unclear question

until now..i always use CR to design the report..like what field to display, connection, group by, etc..and i just use vb just to call the report to display (FYI : i'm using just CR control in VB)..using selectionformula which i realize it's took some time if there's alot record..i'm planning using recordset and pass it to CR so that the value is just came from my query in VB (not take some time anymore to display since the 'selection' thing already done at my query)..is it true what i assuming? or is there any better method??

a couple days ago..i read about to 'code' CR with VB (maybe like using ADODC and ADODB in VB) but i don't understand how to do it in VB..

what i mean blank report is..i created report in CR that just use ParameterField..and fill its value from VB..so whenever someone open .rpt..it only show 'blank' report..

now..i'm gonna use CR to display a record that exactly from same table..but different in 'ORDER BY' thing..if i'm using 'old' way..i'm gonna make a several report for several ORDER BY..this isn't a good way
since i'm new at this point..could u or anyone suggest or guide me how to do this and other stuff..

hope u understand what i'm saying
thx|||See my post in this link:

http://www.dev-archive.com/forum/showthread.php?t=309534&highlight=ado|||thx malleyo
that's not what i mean but it certainly can be useful for me

let me clarify my question
first i create a .rpt using CR which the value in detail section will be from parameter field that i pass later through VB

so the question will be..
1. if i use parameter field..can the .rpt be sort by or group by certain field that controlled by my VB app?so i don't need to create alot .rpt if the sort condition is alot too
is it possible to do that? any suggestion how i do it?

thx|||What about this:

The following code can be used to decide the sort order of your rows through VB Code:

1. First, In Crystal Reports, create a Formula Field and add 1 column (a String column works best).
2. Insert a Group and choose your Formula for the GroupBy.
3. Now, you can comment out the Column you entered, or you can leave it there, your choice.
4. In VB, add this line of code, substituting {ttxFileName.ColumnName} for the column you want to sort by: Report.FormulaFields(1).Text = "{ttxFileName.ColumnName} ". The FormulaFields can only take a long for the index, so you need to know what number your Formula is so you don't replace the wrong one. The numbers start at 1 and are incremented based on the order the formulas were created (they are listed in chronological order).

NOTE: This example works with CR 8.5 and VB6, you may have to tweak it a bit if you're using different versions.|||thx malleyo
sorry for late reply..coz it's holiday here and i don't have internet at home :)
so that's how it works for ORDER BY problem..

still have another problem..
i'm want to restricted .rpt just to display certain record like SupplierID = 'Erick'
1. is it the same way as previous just different at record/group selection formula section?
2. can it be done using same report as previous so that i'm not working with alot of .rpt?
3. a couple weeks ago..i realized that using .selectionformula method is not a good way to display certain data(like just display where SupplierID = 'Erick')..any suggestion for this one?

thanks|||If you only want to display certain criteria, use a conditional suppress.

To specify the condition at runtime, create a formula like you did from my previous post. Like this:
'Erick'
Then set the Conditional Suppress for the field or section you want to suppress. Like this:
{FieldName} <> {FormulaYouCreated}

Whenever the FieldName matches what you plugged into the formula from VB, the field will be displayed.

An alternative is to set the {FormulaYouCreated} to this:
{FieldName} <> 'Erick'
and the Conditional Suppress formula to this:
{FormulaYouCreated}

If you do it the second way, you can leave the {FormulaYouCreated} blank and it will display all the records.

Doing it the second way, this will show only records for 'Erick':
Report.FormulaFields(1).Text = "{FieldName} <> 'Erick' "

Doing it the second way, this will show all records:
Report.FormulaFields(1).Text = "//Comment as Placeholder"

Doing it the second way, this will show records for everyone EXCEPT 'Erick':
Report.FormulaFields(1).Text = "{FieldName} = 'Erick' "|||thx alot malleyo

i have to give it a try about this one..

Counting Rows

I want to define a measure which is count of rows in a fact table satisfying certain criteria. I just need a single value and hence this measure need not be additive from any dimension.

There are 2 possible scenarios -

(1) The criteria involves columns from the fact table itself (Example: [Fact Table].[Transaction Type] = 1 or 2 or 3)

(2) The criteria involves columns from dimension tables (Example: [Dimension Table].[Transaction Category] = 'D')

I am novice to SS BI and I have tried-out various solutions involving MDX Queries, Calculated Member, Named Set etc. but not successful. Kindly suggest me, how to achieve this.

Thanks & Regards.

Is my question so naive?|||

Maybe the best solution (for performance and smart use) should be to create an additive measure.

Try it in this way:

1) Create a Named Calculation in your fact table in your data source view

2) In the Expression write a CASE to test your condition to have 1=True and Null=False, something like this:

CASE
WHEN ([Transaction Type] = 1) THEN 1
WHEN ([Transaction Type] = 2) THEN 1
WHEN ([Transaction Type] = 3) THEN 1

WHEN ([Transaction Category] = 'D') THEN 1
ELSE NULL
END

3) In your cube create an additive measure based on your Named Calculation

Now you should use directly your measure in every MDX query.

Let me know if it work fine.

Francesco

|||Another approach is to create a row count measure and create a dimension based on Transation Type in addition to the with the dimension containing Transaction Category. You can then filter the count on Transaction Type and Transaction Category at run-time. Or, if you never wanted to see a certain type of transaction, you could put in a named query in your DSV or a query binding in your partition that filters out those rows.

Counting Rows

I want to define a measure which is count of rows in a fact table satisfying certain criteria. I just need a single value and hence this measure need not be additive from any dimension.

There are 2 possible scenarios -

(1) The criteria involves columns from the fact table itself (Example: [Fact Table].[Transaction Type] = 1 or 2 or 3)

(2) The criteria involves columns from dimension tables (Example: [Dimension Table].[Transaction Category] = 'D')

I am novice to SS BI and I have tried-out various solutions involving MDX Queries, Calculated Member, Named Set etc. but not successful. Kindly suggest me, how to achieve this.

Thanks & Regards.

Is my question so naive?|||

Maybe the best solution (for performance and smart use) should be to create an additive measure.

Try it in this way:

1) Create a Named Calculation in your fact table in your data source view

2) In the Expression write a CASE to test your condition to have 1=True and Null=False, something like this:

CASE
WHEN ([Transaction Type] = 1) THEN 1
WHEN ([Transaction Type] = 2) THEN 1
WHEN ([Transaction Type] = 3) THEN 1

WHEN ([Transaction Category] = 'D') THEN 1
ELSE NULL
END

3) In your cube create an additive measure based on your Named Calculation

Now you should use directly your measure in every MDX query.

Let me know if it work fine.

Francesco

|||Another approach is to create a row count measure and create a dimension based on Transation Type in addition to the with the dimension containing Transaction Category. You can then filter the count on Transaction Type and Transaction Category at run-time. Or, if you never wanted to see a certain type of transaction, you could put in a named query in your DSV or a query binding in your partition that filters out those rows.

Counting Result Set from SQL Query Before Fill()ing a DataSet

I need to display something like "Results x-y of z." The problem is, I can't figure out how to get the right value for z.

I am using SqlDataAdapter and Fill()ing a DataSet with subset x through y of the result set. How can I get the right value for z?

You are going to have to query the database to get the record number.

Select Count(OneOfYourFields) from YourTable.

You can command.executescalar to get the result

|||

Thank you for the reply.

I am using a Stored Procedure, so this makes things a bit more complicated. I know with MySQL you can use mysql_num_rows() to fetch the number of rows a query will return, without actually grabbing all the data from that query and pulling it onto the web server. Is there any similar functionality in C#/ASP.NET?

|||What ken means is that you need to run a count(*) query to retrieve the number of rows. ExecuteScalar will return the first record in the first row which is all you need since you only need the count. Then just display that number.|||I think I understood what Ken said. However, I am not just selecting from one table, I am executing a complicated Stored Procedure on multiple tables. How can I run a COUNT(*) on the Stored Procedure?|||

Hi,

As far as I know, we cannot run COUNT() on the result set of a stored procedure. The only way I think is to add some SELECT COUNT(*) for each table in the stored procedure and return the count as OUTPUT parameters.

counting records in a view

I was wondering if i would be able to add a column in a view that assigns a value to each record and incriments the number each time by 1. Like the way an identity field works in a table.
Is this possible using a view?I was wondering if i would be able to add a column in a view that assigns a value to each record and incriments the number each time by 1. Like the way an identity field works in a table.

Is this possible using a view?

One of the more frequently asked questions. Not doable in 7.0 or 2000. You can try something similar by creating a temp table (with an identity column); or you can do it with a table function.

Regards,

hmscott|||One of the more frequently asked questions. Not doable in 7.0 or 2000. You can try something similar by creating a temp table (with an identity column); or you can do it with a table function.

Regards,

hmscott

Thanks for your reply. I was hoping i would be able to work around it without having to create a table, but it looks like that might just be the way do go afterall.

Thanks again,

Steve|||How many rows are we talking about?

How about the DDL for the table|||One of the more frequently asked questions. Not doable in 7.0 or 2000. You can try something similar by creating a temp table (with an identity column); or you can do it with a table function.

Regards,

hmscott

Thanks for your reply. I was hoping i would be able to work around it without having to create a table, but it looks like that might just be the way do go afterall.

Thanks again,

Steve|||Yes, you can do this in any version of sql server as long as you have a unique column (or columns) that you can order by.
select MyTable.*,
(select count(*)
from MyTable SubTable
where SubTable.SortColumn <= MyTable.SortColumn) as OrdinalValue
from MyTable

counting occurrences of a value

I have a column in a report with values Y or N or V. In the header, I have to show the number of times each value appeared.

I used RepeatingValue() with IIF() but it's counting all the values as same, e.g., if there are four occurrences of Y, two of N and one of V, I want something like this:

Y Cnt=4, N Cnt=2, V Cnt=1

My assumption was that this will work (white spaces added for readability:

=iif(

Fields!myCol.Value = "Y",

"Y Cnt=" & RunningValue(Fields!myCol.Value, Count, "dsMyDataSet"),

iif(Fields!myCol.Value = "N",

"N Cnt= & RunningValue(Fields!myCol.Value, Count, "dsMyDataSet"),

iif(Fields!myCol.Value = "V",

"V Cnt= & RunningValue(Fields!myCol.Value, Count, "dsMyDataSet"),

"NULL"

)

)

)

Please help.

Can we declare variables and use them to keep the total?|||

As before, no one could help. After trying millions of different ways, I found the solution (white spaces for readability):

="Y Cnt=" &

Count(

Switch(Fields!myCol.Value = "Y", 1)

, "dsMyDataSet"

)

& ", N Cnt=" &

Count(

Switch(Fields!myCol.Value = "N", 1)

, "dsMyDataSet"

)

& ", V Cnt=" &

Count(

Switch(Fields!myCol.Value = "V", 1)

, "dsMyDataSet"

)

counting number of specific rows in SQL

hi,

I would like to create a user defined SQL function which returns the number of rows which meets certain condition, and the average value of one of the culomns. I cannot find a code example for it. Please help.

Thanks,

Dror.

I don't think a SQL Server function is the best option for you. If you are returning this to SSRS, you'll probably want to leverage a stored procedure. Still, here is a generic representation of what you're asking for:

create function dbo.MyFunction( @.MyParam varchar(10) )

returns @.ret table (MyCount int, MyAvg float)

begin

insert into @.ret (MyCount, MyAvg)

select count(*), avg(MyField) from MyTable where MyOtherField=@.MyParam

return

end

This function returns a table. To retrieve data, you will call it as follows:

select *

from dbo.MyFunction( @.MyParam)

Again, I don't think this is the best approach to this problem. There are more easily read and consumed approaches to this problem.

Thanks,
Bryan

|||I am aware of the fact I can perform the same in VB - even eassier, but there is a reason for using function on the server. Supposed I want to set the number of rows to a variable, how do I do it ?|||

A VB function is not a good choice either. Are you using this data in SSRS?

Thanks,
Bryan

|||

Hi Bryan,

To tell the truth - I do not even know what SSRS is...I am new to this field, still learning. I tried to minimize my application for security (I'm affraid of paople will restore my code and see my logic creating the code), so some of the important stuff will perform as stored procedures on the SQL (which I'm also learning now...) Now I managed to calculate the average, and count the rows. But my function needs to return the result in a variable: the first variable - numbers of rows, the second - the average of a column, and the third - I need to find the fifth biggest value in a specific column. The prolem is assigning the results into variables.

Thanks again for your help,

Dror.

|||

Try something like this with appropriate substitutions. Please note, the data types I'm using for the parameters does not imply that these would be the approapriate data types for your application.

Code Snippet

create proc MyProc @.MyParam varchar(10), @.MyCount int OUTPUT, @.MyAvg float OUTPUT

as

select @.MyCount = count(*), @.MyAvg = avg(MyField)

from MyTable

where MyOtherField = @.MyParam

return 0

end

You can look up more info on using output parameters by reading the Books Online entry for the CREATE PROCEDURE statement.

Thanks,
Bryan Smith

|||thank you very much for your great help Bryan.

Counting no. of records

Hi

I need the ability to calculate the no. of records based on the no. of times a value in the sql report is given. For example based on a table shown below:

Ref No. First Name Surname 18 test test 18 test test 18 test test 19 test test 19 test test

I need to calulate the records returned on the ref no. I have managed to set page breaks based on a new ref no. with grouping and therefore the count will be displayed at the end of each of the records returned. As you can see there are three records returned for ref no. 18 and 2 for 19. How can I achieve this.

Many thanks in advance

If an aggregate of that column doesn't already exist then create one using something like

SUM(Ref No) AS 'Number of Records'

You could do this in the SQL or you can create it as a function which would look something like

=SUM(Ref No)

This field should be added to the footer column of the group.

Hope this helps

|||Hi,
I have created the table with the same information you have given.
I opened my Sql Server Reporting Services 2005, creating the new report.
First I placed the table control, placing the ref no, firstname, surname then i have created new group in the table called - "refno" ....in the group footer i have used the function ===> =CountRows("refno")

I think you know, how to set page break after group ends....

Do this....You will get the answer?
If you have any queries..let me know...

M Sivakumar|||Thanks for your reply Harley.

I have tried what you have suggested but =SUM is calculating the total for the Ref No. So if the ref no. 18 occurs twice, I get the value 36.. and so forth. Is this the right function? I thought this function would be to calculate the numbers mathematically. I know there is a count function but do not know the syntax of the expression. I need to understand how to use this function to calculate the no. of records returned for the ref no. e.g. if ref.no 18 occurs 10 times in the report, then below it should say total records = 10 and so forth for other ref no.s. I hope this makes more sense.

Cheers|||Hi Siva

First of all thanks for your help in trying to help, greatly appreciated.

Right, I do know how to create groups and set page breaks which I have implemented in my report and have the group footer below my rows. I tried using the countrows function but I think I am doing this wrong, the way I have added this is by using:

=CountRows(Fields!RefNo.Value)

the function with the fieldname in the brackets (is this the correct way?)
Do I need the speech marks also, I have tried all possibilites but it gives me an error:

The value expression for the textbox ‘textbox70’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

Thank you again for your help

|||Sorry,

I meant use COUNT(RefNo) that should work, I always make that mistake then wonder why I get ridiculous answers :)

thanks|||

Hi,
No It is not the correct way. After creating the table, you just the create the new group and by default the group name will be "table1_Group1", you just modify the name into "refno" if you want to make it meaningful or you just leave it..
Now in the group footer...any of your cell you just add
"table1_Group1"
=CountRows("table1_Group1")

if u renamed into "refno"
=CountRows("refno")

I think, it will help your need.

|||I think I have found the solution to this. Siva first of all thank you for pointing me to the right direction.

The countrows function is indeed the right function for counting the rows returned. The way I have got this to work is by saying the following:

=CountRows("groupname") where groupname is the name of your group you have defined. It is imperative to include the speech marks.

I had a group set up as my criteria and have used this and seems to be working .

By the way, a good reference for this function and others supported by Microsoft use this website, its really good:

http://msdn2.microsoft.com/en-us/library/ms226986(en-US,SQL.90).aspx|||The above may be a better way of doing it but the way explained does work!|||Hi Harley

I'm sure it does work, not tried using it but I have the countrows function working so will stick with this one. In fact the reason why I feel the countrows function is better is due to the records returning actually fulfill my criteria which I specified in the group.

Cheers for your help too.

Counting items

Hi,

I'm trying to include the COUNT(*) value of a sub-query in the results of a parent query. My SQL code is:

SELECT appt.ref, (Case When noteCount > 0 Then 1 Else 0 End) AS notes FROM touchAppointments appt, (SELECT COUNT(*) as noteCount FROM touchNotes WHERE appointment=touchAppointments.ref) note WHERE appt.practitioner=1

This comes up with an error basically saying that 'touchAppointments' isn't valid in the subquery. How can I get this statement to work and return the number of notes that relate to the relevant appointment?

Cheers.Hi!

Would this one help out?

SELECT appt.ref
, (Case When noteCount > 0 Then 1 Else 0 End) AS notes
FROM touchAppointments appt
, (SELECT appointment
, COUNT(*) as noteCount
FROM touchNotes) note
WHERE appt.practitioner=1
AND appt.ref = note.appointment

Greetings,
Carsten|||I would have writen like this
<code>
SELECT appt.ref , count(*)/count(*) AS notes
FROM touchAppointments as appt inner join touchNotes as note
on appt.ref = note.appointment
WHERE appt.practitioner=1 group by appt.ref
</code>|||You would?

count(*)/count(*) is at best going to return only 1s or Nulls, and at worst would return DivZero errors.

There are serveral ways to do this. CarstenK had one, though it is preferable to use a JOIN rather than linking tables in the WHERE clause.

Here are two more methods:

SELECT touchAppointments.ref, cast(count(touchNotes.appointment) as bit) notes
FROM touchAppointments
left outer join touchNotes on touchNotes.appointment = touchAppointments.ref
WHERE touchApointment.practictioner = 1
GROUP BY touchAppointments.ref

SELECT touchAppointments.ref, isnull(notesSubquery.hasnotes, 0) as notes
FROM touchAppointments
left outer join (select distinct touchNotes.appointment, 1 as hasnotes from touchNotes) notesSubquery
on notesSubquery.appointment = touchAppointments.ref
WHERE touchApointment.practictioner = 1|||you are right
count(*)/count(*) is at best going to return only 1s
but how come nulls and div by zero error(even at worst case) with inner join on touchAppointments.ref.|||How do you get the "0" paulbrooks wants to get with his CASE (....)?

Carsten|||Blindman,

Your second solution worked the trick. It returns a 1 for true and 0 for false, which is exactly what I needed.

Thanks a lot, guys.

Paul

Counting Group fields

Is there a way I can count the number of grouped items in a textbox in a
report?
When I try to use =count(Fields!UserID.Value), the report gives me the
amount of records in the query.
I also tried using count distinct but that returned the same results.
Is there anyway I can just count the grouped listings? Or maybe count the
number of table rows?
TIA,
JacksonI think you can specify a scope for the count method ie
=count(Fields!UserID.Value, "GroupName")
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_9bp0.asp|||Thanks Thomo,
Where would I be able to find the group name? The only group names I've
found is called table1_UserID and table1_details_group but they don't seem
to work.
Thanks again for the help,
Jackson
"Thomo" <greg.thomson@.gmail.com> wrote in message
news:1147102016.645069.295180@.e56g2000cwe.googlegroups.com...
>I think you can specify a scope for the count method ie
> =count(Fields!UserID.Value, "GroupName")
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_9bp0.asp
>|||Those sound like legit group names. You can find group names by right
clicking on the beginning of a group row and selecting edit group from
the context menu. The name will be on the top portion of the general
tab of the Grouping and Sorting Properties window. The names work fine
as the scope argument for the count statement, making sure that the
group name is in double quotes since it needs to be a string argument.
You didn't say what kind of error you're getting, but maybe its the
placement of your aggregate field. Try putting it in the group footer,
for example, and see if it works.|||Putting it into the group footer worked. I was trying it in the Page
footer. But the problem is that when I do a count it counts all of the
records.
I just want to include a count of how many Users are included in the report.
In my report I have UserID's grouped and within the group is the details of
their previous transactions. And for some reason it's counting the records
instead of just the groups.
Any suggestions?
Thanks again,
Jackson
"dba56" <louvig4@.att.net> wrote in message
news:1147115725.622812.317540@.y43g2000cwc.googlegroups.com...
> Those sound like legit group names. You can find group names by right
> clicking on the beginning of a group row and selecting edit group from
> the context menu. The name will be on the top portion of the general
> tab of the Grouping and Sorting Properties window. The names work fine
> as the scope argument for the count statement, making sure that the
> group name is in double quotes since it needs to be a string argument.
> You didn't say what kind of error you're getting, but maybe its the
> placement of your aggregate field. Try putting it in the group footer,
> for example, and see if it works.
>|||doh, nevermind. I took a look at the link that Thomo had sent and I tried
using countdistinct again in the footer and it worked.
I could have sworn I tried countdistinct and it came out different.
Oh well thanks for all of the help.
Jackson
"Jackson" <jackson_num5@.yahoo.com> wrote in message
news:eK%23I1ntcGHA.3632@.TK2MSFTNGP02.phx.gbl...
> Putting it into the group footer worked. I was trying it in the Page
> footer. But the problem is that when I do a count it counts all of the
> records.
> I just want to include a count of how many Users are included in the
> report.
> In my report I have UserID's grouped and within the group is the details
> of their previous transactions. And for some reason it's counting the
> records instead of just the groups.
> Any suggestions?
> Thanks again,
> Jackson
> "dba56" <louvig4@.att.net> wrote in message
> news:1147115725.622812.317540@.y43g2000cwc.googlegroups.com...
>> Those sound like legit group names. You can find group names by right
>> clicking on the beginning of a group row and selecting edit group from
>> the context menu. The name will be on the top portion of the general
>> tab of the Grouping and Sorting Properties window. The names work fine
>> as the scope argument for the count statement, making sure that the
>> group name is in double quotes since it needs to be a string argument.
>> You didn't say what kind of error you're getting, but maybe its the
>> placement of your aggregate field. Try putting it in the group footer,
>> for example, and see if it works.
>