Customised totals

Hi,

I am creating a report using matrix. The reporting services subtotal, sums up the data on all rows. The data in rows contains numeric & percentage values. I need the total for numeric value as sum and for percentage values as weighted average. Does anyone know how to do this or is this limitation of RS. Thanks.

You mean you want to have different calculation done in the subtotal as oppose to the one used in detail cells? If so, you can use the InScope function to do it. http://msdn2.microsoft.com/en-us/library/ms156490.aspxsql

Custom Report Item: Textbox

Hi all,
If it is possible, how I can extend the Textbox item with a DataSet
property. In order to display a field from multiple rows, with a separator ?
Example:
a dataset with 10 rows (and 1 field "Name")
and I want to display in the Textbox
"Name1, Name2, Name3, Name4, Name5, Name6, Name7, Name8, Name9, Name10"
ThanksI'm sure this is possible, just to give you a point in one possible
direction...
From a Database side of things, you could pivot the data in your SQL
statement. SQL Server 2005 has new SQL commands called PIVOT and UNPIVOT...
Once your rows values are pivotted to columns in a single row, then you
could specify them easily enough in the text box.
Hope that helps.
Dan.
"gbouzebra" <gbouzebra@.discussions.microsoft.com> wrote in message
news:D8959BBA-3778-4129-9E3F-AB4443A58C5F@.microsoft.com...
> Hi all,
> If it is possible, how I can extend the Textbox item with a DataSet
> property. In order to display a field from multiple rows, with a separator
> ?
> Example:
> a dataset with 10 rows (and 1 field "Name")
> and I want to display in the Textbox
> "Name1, Name2, Name3, Name4, Name5, Name6, Name7, Name8, Name9, Name10"
> Thanks

Custom protection of rows (problem with updatability of viwes with "WITH VIEW_METADATA" an

My question is connected with tip "Using Views to Control User Access to
Data":
http://www.microsoft.com/sql/techinfo/tips/administration/controlledaccess.asp
I need to update (using Access 2000/2002) table defended in such way,
but it cause some additional problems.
1. I can't update this view when user haven't SELECT permissions
on the base table (user have only permissions to the view, because I try to
defend base table).
As I understand ADO tries directly update base table and fails.
2. When I add "WITH VIEW_METADATA" to enforce ADO to update
view, not base tables I have another problem - I need to add
prymary key columns from "authtable" to make view updateble
for ADO. In such way I also had to add some additional procedures
to dataform in Access to fill this additional columns automatically (for
insert operation).
It helps but it cause additional procedures at client side.
3. The best way to resolve this problem only on server side as I thought
was to create view like this (using "WITH VIEW_METADATA" and "IN" clauses):
CREATE VIEW v_data
WITH VIEW_METADATA
AS
SELECT <column_list>
FROM dbo.mytable AS a
WHERE a.Pkey
IN
(
SELECT b.DataKey
FROM dbo.authtable AS b
WHERE b.userid = suser_sname()
)
But this view is not updatable in Access 2000/2002
because ADO DOES NOT SEE PRIMARY KEY INFORMATION
when there is combination "WITH VIEW_METADATA" and "IN" clauses.
I cann't use this view without "WITH VIEW_METADATA", in this case
view is "updatable" for ADO, but updates fails as described in p.1.
I send this bug year ago to MS when was mdac 2.7.
Now we have MDAC 2.8, SQL SP 3, Yukon Beta - ADO has the same problem with
updates
when there is combination "WITH VIEW_METADATA" and "IN" clauses
I think, it helps to make simple customised rowbased security system not
only for SELECT
but also for INSERT, UPDATE and DELETE in combination "SQL Server - MS
Access" or other ADO based clients.Hello Max:
You wrote on Wed, 28 Apr 2004 19:37:02 +0300:
ii> 1. I can't update this view when user haven't SELECT permissions
ii> on the base table (user have only permissions to the view, because I
ii> try to defend base table).
ii> As I understand ADO tries directly update base table and fails.
In Access 2002, open the view in design mode, open properties, select
"Update using view rules".
ÿê òàì Ëüâiâ? ÿ òàì â÷èâñÿ.
Vadim
---
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com|||"Vadim Rapp" <vrapp@.nospam.polyscience.com> ñîîáùèë/ñîîáùèëà â íîâîñòÿõ
ñëåäóþùåå: news:e1IXvQULEHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hello Max:
> You wrote on Wed, 28 Apr 2004 19:37:02 +0300:
> ii> 1. I can't update this view when user haven't SELECT permissions
> ii> on the base table (user have only permissions to the view, because I
> ii> try to defend base table).
> ii> As I understand ADO tries directly update base table and fails.
> In Access 2002, open the view in design mode, open properties, select
> "Update using view rules".
> ÿê òàì Ëüâiâ? ÿ òàì â÷èâñÿ.
> Vadim
> ---
> Vadim Rapp Consulting
> SQL, Access, VB Solutions
> 847-685-9073
> www.vadimrapp.com
>
Hello Vadim,
Thanks for reply,
Chekbox "Update using view rules" exactly adds "WITH VIEW_METADATA" clause
to view definition and this situation adds aditional problems as described
in p.2. But when I want add more sophisticated rules for user selection
permissions and make it updatable in Access I get very processor time
cosuming solutions. Execution plans for view scheme discribed in p.3 for my
tasks and needed rules some times more than 10 time quick than the same that
Access (ADO) understands as updatable - this the main problem...
And root of problem is ADO uncorrect understanding of combination of clauses
"WITH VIEW_METADATA" and "IN" (also "EXISTS").
Ó Ëüâîâ³ êëàñíî, ïðàâäà ùå òðîõè çèìíî áóâàº, ³ ÷àñ â³ä ÷àñó äîùèòü. Â
íåä³ëþ 2 òðàâíÿ áóäåìî ñâÿòêóâàòè äåíü ì³ñòà.
MAX|||Hello Max:
You wrote in conference microsoft.public.sqlserver.server on Thu, 29 Apr
2004 14:05:18 +0300:
II> And root of problem is ADO uncorrect understanding of combination of
II> clauses "WITH VIEW_METADATA" and "IN" (also "EXISTS").
It looks like it's not ADO but Access. I see that Access indeed does not
allow to add records to such a view; however, I successfully executed the
following pure ado code in VB:
rs.CursorLocation = adUseClient
rs.Open "view1", conn, adOpenKeyset, adLockBatchOptimistic
rs.AddNew
rs!c1 = "a"
rs!c2 = "b"
rs!id = 14
rs.UpdateBatch
rs.Close
where view1 was
ALTER VIEW dbo.View1
WITH VIEW_METADATA
AS
SELECT dbo.t1.c1, dbo.t1.c2, dbo.t1.id, dbo.t1.auth
FROM dbo.t1 INNER JOIN
dbo.authtable ON dbo.t1.auth >= dbo.authtable.minvalue
WHERE (dbo.authtable.userid = USER_NAME())
Note that I did not include in the view anything from authtable.
Another interesting possibility is described in BOL in "Create View"
article, which says: "INSTEAD OF triggers can be created on a view in order
to make a view updatable".
As a side note: you might receive more advise if you asked the question in
more relevant newsgroups. This one is mainly read by server administrators;
you might try .access.adp.sqlserver and .data.ado (though, as I said, it
looks like ADO is innocent here).
Êðiì òîãî, êîëè òè ïèøåø â àíãëiéñüêó ãðóïïó, òî ïèøè ñâîº iì'ÿ
ïî-àíãë³éñüêè.
regards,
Vadim
---
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com

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 Destination Component Logging - wrote 0 rows

I wrote a custom destination component. Everything works fine, except there is a logging message that is displayed that I cannot get rid of or correct. Here is the end of the output of a package containing my component:

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x0 at Data Flow Task, MyDestination: Inserted 40315 rows into C:\temp\file.txt
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "MyDestination" (9)" wrote 0 rows.
SSIS package "Package.dtsx" finished: Success.

I inserted a custom information message that contains the correct number of rows written by the component. I would like to either get rid of the last message "... wrote 0 rows", or figure out what to set to put the correct number of rows into that message.

This message seems to happen in the Cleanup phase. It appears whether I override the Cleanup method of the Pipeline component and do nothing, or not. Any ideas?

public override void Cleanup()

{

ComponentMetaData.FireInformation(0, ComponentMetaData.Name,

"Inserted " + m_rowCount.ToString() + " rows into " + m_fileName,

"", 0, ref m_cancel);

base.Cleanup(); // or not

}

This message comes from the engine and you can not stop it from occuring. To set it correctly you need to call the IncrementPipelinePerfCounters(counter, difference) method on the IDTSComponentMetaData90 interface.

The counter values are:

RowsRead: 101

RowsWritten: 103

BlobBytesRead: 116

BlobBytesWritten: 118

The difference value is the amount to increment the counter by.

Thanks,

Matt

|||

Thanks. That did the trick.

Apparently there are constants defined for the counter types, but I'm not sure where they are.

Here's the official help page:

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.pipeline.wrapper.idtscomponentmetadata90.incrementpipelineperfcounter.aspx

|||

The constants for the counter types are listed in the Remarks section of the BOL page for which you pasted the link. Are those not what you were looking for?

-Doug

|||

Those constants work. I was hoping to be able to access the constants by an actual constant, instead of having to hard-code the number.

I would rather have code that looks like this:

ComponentMetaData.IncrementPipelinePerfCounter(DTS_PIPELINE_ROWS_WRITTEN, m_rowCount);

than this:

ComponentMetaData.IncrementPipelinePerfCounter(103, m_rowCount);

For now I just created a local constant with the above name and that works fine.

private const uint DTS_PIPELINE_ROWS_WRITTEN = 103;

Maybe those constants are exposed somewhere, but I could not figure out where.

|||

Excuse me for missing the point.

I suspect that these counter constants are defined in the native pipeline engine and not exposed in any of the managed classes...that would explain why this managed method expects an integer value and not an enum value. You could create your own enumeration for this purpose.

-Doug

custom assembly

hello
after getting the first row of data using .net assemblies, the remaining
rows show #error.
any ideas?problem solved. error was in .net assembly.
"Peter Fuller" wrote:
> hello
> after getting the first row of data using .net assemblies, the remaining
> rows show #error.
> any ideas?|||here is another problem.
the report has 36 rows. when i change the select statement to have the top
1 then the data is correct.
when i remove the top 1 then some of the fields are incorrect. does report
server create separate instances of the .net assembly for each field?
"Peter Fuller" wrote:
> problem solved. error was in .net assembly.
> "Peter Fuller" wrote:
> > hello
> >
> > after getting the first row of data using .net assemblies, the remaining
> > rows show #error.
> >
> > any ideas?|||this is really weird
i hard code the value into the variable passed to the .net assembly. then
upon returning 4 rows, i expect to see the same data. however, the first row
is correct, the 2-4 rows are an incorrect number but identical.
any ideas out there?
"Peter Fuller" wrote:
> here is another problem.
> the report has 36 rows. when i change the select statement to have the top
> 1 then the data is correct.
> when i remove the top 1 then some of the fields are incorrect. does report
> server create separate instances of the .net assembly for each field?
> "Peter Fuller" wrote:
> > problem solved. error was in .net assembly.
> >
> > "Peter Fuller" wrote:
> >
> > > hello
> > >
> > > after getting the first row of data using .net assemblies, the remaining
> > > rows show #error.
> > >
> > > any ideas?