Max no. of parameters in functions

Hi,
Can anybody tell me the max no of parameters that we can use in a single
UDF?
Thanks!!BOL:

A function can have a maximum of 1,024 parameters.|||BOL:

A function can have a maximum of 1,024 parameters.
Thanks a lot Sean.|||Hi,
Can anybody tell me the max no of parameters that we can use in a single
UDF?
Thanks!!Half of me shudders every time I see a post about SQL Server's maximum capacity, and the other half tingles with excitement at the future potential consulting income.|||anybody ever made a UDF with that many no of parameters ?|||anybody ever made a UDF with that many no of parameters ?|||anybody ever made a UDF with that many no of parameters ?

Plz listen,don't blame me for that,I found the question in an online test for the MCDBA 70-229 exam (Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition).:D

and the other half tingles with excitement at the future potential consulting income.

Batman is an experienced person and has all the future insights which protect him from danger. :D ;)|||not blaming you for anything ... i was just thinking aloud ;)|||not blaming you for anything ... i was just thinking aloud ;)
yea,there you are,I too was running in that same road a few hours ago.:D :D|||I am a late riser ... mostly go out on evening walks :D

Matrix row number?

I have a matrix. For each person, they may have multiple rows in the dataset
but it is translated to a single row with multiple columns using the matrix.
I want to put a number in the first column indicating the occurence starting
at one and incrementing by 1 for each person: 1, 2, 3, 4, 5, etc. I've tried
RowNumber, Count, RunningValue with no success. Perhaps I am using the wrong
scope.
Does anyone know how to create the row number for the matrix?
StephanieI found it:
RunningValue(Fields!Country.Value,CountDistinct,Nothing)
Stephanie
"Stephanie" wrote:
> I have a matrix. For each person, they may have multiple rows in the dataset
> but it is translated to a single row with multiple columns using the matrix.
> I want to put a number in the first column indicating the occurence starting
> at one and incrementing by 1 for each person: 1, 2, 3, 4, 5, etc. I've tried
> RowNumber, Count, RunningValue with no success. Perhaps I am using the wrong
> scope.
> Does anyone know how to create the row number for the matrix?
> Stephanie

matrix column grouping

Hi,

I have a requirement from my users to be able to drill down for a single column in a matrix. I've been able to implement drilldown for all of the measures (all columns grouped at the same time) in my matrix but they now want to be able to group different columns independently of each other. In other words they want to be able to group the data in different columns by different things.

An example might make my question clearer, the report would need to look like this:

Client Name	Sales -			Costs +	Revenue -
Client A +	Europe +	Middle East +	Asia Pacific +	$12,000,000	Products +	Investments +
	$12,000,000	$8,000,000	$4,000,000		$10,000,000	$22,000,000
Client B +	$77,000,000	$16,000,000	$9,000,000	$22,000,000	$8,000,000	$32,000,000

As you an see, the Sales measure has been expanded so it's grouped by region so the sales figures can be seen for all clients and the 3 regions. The revenue column has also been expanded/grouped so that you can see revenue figures for products and investments for all clients. Costs is not expanded but it could be by clicking on the '+' which would group the data in that column by something else. They have asked for more than 1 level of drilldown, so clicking on "Europe" above would allow another level for the sales measure which might be country. There will also be regular measure columns that are not drilldown/groupable columns.

Is it possible to implement this with SSRS 2005?

Thanks,

Lachlan

...Hmm, I'll assume it's not possible to do this at the moment. Looks like I'll be doing my reports in asp.net for now.|||

There's no built in way of doing this. There are always workarounds but hese tend to be messy.

It will be fairly straight forward if using SQL less straight forward with MDX.

Basically need to consider the data in terms which portion of the report it is to appear on rather than what the actual entity is. Let me clarify. For your example instead thinking of Europe, Middle East and Asia Pacific as regions think of them column grouping level 1. Similarly the products fall into this same category. Hence you can generalise your SQL query for the report to have this 1 column instead of having region and product e.g.

SELECT client
, measure = 'Sales'
, column_level_1 = region
, amount = SUM(sales)
FROM some_table
GROUP BY client
, region

UNION ALL

SELECT client
, measure = 'Revenue'
, column_level_1 = product
, amount = SUM(revenue)
FROM some_table
GROUP BY client
, product

The result is a dataset that looks like:

client measure column_level_1 amount
=============================================
Client A Sales Europe 12000000
Client A Sales Middle East 8000000
Client A Sales Asia Pacific 4000000
Client B Sales Europe 77000000
Client B Sales Middle East 16000000
Client B Sales Asia Pacific 9000000
Client A Revenue Products 10000000
Client A Revenue Investments 22000000
Client B Revenue Products 8000000
Client B Revenue Investments 32000000

|||Thanks Adam. I'm using MDX, I'll give this some thought.|||

hi, can anyone help on this? i am new to MDX and also encounter this problem.
Thanks a lot in advance.

Best regards,

Tommy

matrix column grouping

Hi,

I have a requirement from my users to be able to drill down for a single column in a matrix. I've been able to implement drilldown for all of the measures (all columns grouped at the same time) in my matrix but they now want to be able to group different columns independently of each other. In other words they want to be able to group the data in different columns by different things.

An example might make my question clearer, the report would need to look like this:

Client Name	Sales -			Costs +	Revenue -
Client A +	Europe +	Middle East +	Asia Pacific +	$12,000,000	Products +	Investments +
	$12,000,000	$8,000,000	$4,000,000		$10,000,000	$22,000,000
Client B +	$77,000,000	$16,000,000	$9,000,000	$22,000,000	$8,000,000	$32,000,000

As you an see, the Sales measure has been expanded so it's grouped by region so the sales figures can be seen for all clients and the 3 regions. The revenue column has also been expanded/grouped so that you can see revenue figures for products and investments for all clients. Costs is not expanded but it could be by clicking on the '+' which would group the data in that column by something else. They have asked for more than 1 level of drilldown, so clicking on "Europe" above would allow another level for the sales measure which might be country. There will also be regular measure columns that are not drilldown/groupable columns.

Is it possible to implement this with SSRS 2005?

Thanks,

Lachlan

...Hmm, I'll assume it's not possible to do this at the moment. Looks like I'll be doing my reports in asp.net for now.|||

There's no built in way of doing this. There are always workarounds but hese tend to be messy.

It will be fairly straight forward if using SQL less straight forward with MDX.

Basically need to consider the data in terms which portion of the report it is to appear on rather than what the actual entity is. Let me clarify. For your example instead thinking of Europe, Middle East and Asia Pacific as regions think of them column grouping level 1. Similarly the products fall into this same category. Hence you can generalise your SQL query for the report to have this 1 column instead of having region and product e.g.

SELECT client
, measure = 'Sales'
, column_level_1 = region
, amount = SUM(sales)
FROM some_table
GROUP BY client
, region

UNION ALL

SELECT client
, measure = 'Revenue'
, column_level_1 = product
, amount = SUM(revenue)
FROM some_table
GROUP BY client
, product

The result is a dataset that looks like:

client measure column_level_1 amount
=============================================
Client A Sales Europe 12000000
Client A Sales Middle East 8000000
Client A Sales Asia Pacific 4000000
Client B Sales Europe 77000000
Client B Sales Middle East 16000000
Client B Sales Asia Pacific 9000000
Client A Revenue Products 10000000
Client A Revenue Investments 22000000
Client B Revenue Products 8000000
Client B Revenue Investments 32000000

|||Thanks Adam. I'm using MDX, I'll give this some thought.|||

hi, can anyone help on this? i am new to MDX and also encounter this problem.
Thanks a lot in advance.

Best regards,

Tommy

matrix column grouping

Hi,

I have a requirement from my users to be able to drill down for a single column in a matrix. I've been able to implement drilldown for all of the measures (all columns grouped at the same time) in my matrix but they now want to be able to group different columns independently of each other. In other words they want to be able to group the data in different columns by different things.

An example might make my question clearer, the report would need to look like this:

Client Name	Sales -			Costs +	Revenue -
Client A +	Europe +	Middle East +	Asia Pacific +	$12,000,000	Products +	Investments +
	$12,000,000	$8,000,000	$4,000,000		$10,000,000	$22,000,000
Client B +	$77,000,000	$16,000,000	$9,000,000	$22,000,000	$8,000,000	$32,000,000

As you an see, the Sales measure has been expanded so it's grouped by region so the sales figures can be seen for all clients and the 3 regions. The revenue column has also been expanded/grouped so that you can see revenue figures for products and investments for all clients. Costs is not expanded but it could be by clicking on the '+' which would group the data in that column by something else. They have asked for more than 1 level of drilldown, so clicking on "Europe" above would allow another level for the sales measure which might be country. There will also be regular measure columns that are not drilldown/groupable columns.

Is it possible to implement this with SSRS 2005?

Thanks,

Lachlan

...Hmm, I'll assume it's not possible to do this at the moment. Looks like I'll be doing my reports in asp.net for now.|||

There's no built in way of doing this. There are always workarounds but hese tend to be messy.

It will be fairly straight forward if using SQL less straight forward with MDX.

Basically need to consider the data in terms which portion of the report it is to appear on rather than what the actual entity is. Let me clarify. For your example instead thinking of Europe, Middle East and Asia Pacific as regions think of them column grouping level 1. Similarly the products fall into this same category. Hence you can generalise your SQL query for the report to have this 1 column instead of having region and product e.g.

SELECT client
, measure = 'Sales'
, column_level_1 = region
, amount = SUM(sales)
FROM some_table
GROUP BY client
, region

UNION ALL

SELECT client
, measure = 'Revenue'
, column_level_1 = product
, amount = SUM(revenue)
FROM some_table
GROUP BY client
, product

The result is a dataset that looks like:

client measure column_level_1 amount
=============================================
Client A Sales Europe 12000000
Client A Sales Middle East 8000000
Client A Sales Asia Pacific 4000000
Client B Sales Europe 77000000
Client B Sales Middle East 16000000
Client B Sales Asia Pacific 9000000
Client A Revenue Products 10000000
Client A Revenue Investments 22000000
Client B Revenue Products 8000000
Client B Revenue Investments 32000000

|||Thanks Adam. I'm using MDX, I'll give this some thought.|||

hi, can anyone help on this? i am new to MDX and also encounter this problem.
Thanks a lot in advance.

Best regards,

Tommy