Problem with calculating values

I have problem with calculating values, let me give an example:

Fact table:

tableID docID authorID numMetadata numSubDocs

1 10 1 3 5

2 10 14 3 5

3 10 6 3 5

4 49 2 1 0

5 49 4 1 0

6 15 8 6 1

.....

I want to get in a report like this:

docID numMetadata numSubDocs

10 3 5

49 1 0

Grand total: 4 5

Calculated member: Sum(numMetadata) / Count(docID) works fine in rows except in grand total, because there should be sum of all rows. Can anyone help me write correct MDX or any other suggestion?

Couldn't you just do a count on the tableID? |||

no, in num... columns are values from rows of OLTP DB. Problem is that I need for every docID his value of numMetadata, but in fact table docID's are duplicated, and values in numMetadata are same for the same docID. So if I create a measure Sum(numMetadata), I also need a measure Count(docID), so that I can create calculated measure Sum(numMetadata) / Count(docID), so that I can have for each docID his numMetadata. That works fine, but not for grand total (in Excel). I hope somebody understands the problem?

I need calculated measure or something, that calculates every row in excel: Sum(numMetadata) / Count(docID) but on TOTAL rows there is only SUM of previous calculated values.

|||

Assuming that you're using AS 2005, you may be better off modelling 2 fact tables/measure groups, like:

New fact table at {DocID} granularity (no AuthorID), with numMetadata and numSubDocs measures (could be a Named Query)|||I was thinking about having physical table with num columns, but your idea of having named query is even better! Thanks

problem with "select all" on 2 multi-value cascading parameters

Hello,

since some hours I'm struggling with 2 multi-value cascading parameters which default values should be always "Select ALL"

First Parameter:

available vlaues: From query

dataSetsGetCountry (defined as select from tblCountries)

ValueFied:ContryName

LabelField:CountryName

DefaultValues: from Query

dataSetsGetCountry

ValueFied:ContryName

SecondParameter:

available vlaues: From query

dataSetsGetAreas (stored procedure which parameter is the list of the selected countries)

ValueFied:AreaName

LabelField:AreaName

DefaultValues: from Query

dataSetsGetAreas

ValueFied:AreaName

First time I open the report

the first and the second parameters are properly filled and for both parameters "select all" is checked.

I select one option from the first parameter, the second parameter's content change dinamically and "Select All" option is still selected !Cool

Now I change the selection on the first Parameter, by checking AN ANOTHER item from the list , the second parameter list refresh dinamically but "Select all" IS NOT selected and only the item that were previously checked kept the selection !!!NO!!

Is this a bug in Reporting services and I have to say to ther user that is not possible to develop what they would like to have or there is , even programmatically, a way to solve it?

Thankx for any helps!

Marina B

Hello,

I notice that your second parameter is populated by a stored procedure. Is it possible for you to perform that query from within SRS rather than as a stored procedure?

I'm fairly new at this, so I hesitate to make an absolute statement, but multi-value parameters don't work well with stored procedures.

Also, the second query, the one accepting the first parameter must use an IN clause, rather than a WHERE clause, to filter the results.

I bow to any contradictory experience others may have.

Respectfully,

David Emigh

|||

Hi David,

I changed the definition of my parameters based on your advice

dataSet: dsGetCountry (defined as select from tblCountries)

ValueFied: ContryID

LabelField: CountryName

DefaultValues: from Query

dataSet: dsGetCountry

ValueFied: ContryName

Now the Second Parameter is setup in this ways

I created a new DataSet named: dsgetAreaByCountryID

select distinct(AreaName)
from [dbo].[tblArea] a
INNER JOIN [dbo].[tblcountry] c on a.CountryID = c.CountryID
where not AreaName is null and c.CountryID in (@.cc)
group by AreaName
order by AreaName asc

@.cc is a new Parameter which value is =Parameters!CountryID.Value

I tried to copy write directly c.CountryID in (Parameters!CountryID.Value)
but I get an error ( the ! is not welcome)

dataSet: dsgetAreaByCountryID

ValueFied: AreaName

LabelField: AreaName

DefaultValues: from Query

dataSet: dsgetAreaByCountryID

ValueFied: AreaName

Anyway I have the same problem as with the Stored Procedure!!

Did I follow exactly your advice?

Thank you very much
Marina B

|||

Have you adjusted the settings in the report parameters?

From the data or layout view:

Report menu > Parameters

|||

mmhhh ... your last reply is not completely clear to me.

what do you mean with that...

I have changed the parameter setting as I wrote in my last message...

Did I forgot something?

Thankx

Marina B.

|||I have had the same results. Don't think it's a bug. It looses track if you start going back and forth. If you want to start the parameter selection over you might be better off hitting refresh and selecting the parameters in the correct order. Be sure to explain the same to your end users. I do agree that it would be nice if it always keep track of them.|||

How is possible to hit the refresh?

Programmatically?

Thankx very much

MArina B.

problem with "select all" on 2 multi-value cascading parameters

Hello,

since some hours I'm struggling with 2 multi-value cascading parameters which default values should be always "Select ALL"

First Parameter:

available vlaues: From query

dataSetsGetCountry (defined as select from tblCountries)

ValueFied:ContryName

LabelField:CountryName

DefaultValues: from Query

dataSetsGetCountry

ValueFied:ContryName

SecondParameter:

available vlaues: From query

dataSetsGetAreas (stored procedure which parameter is the list of the selected countries)

ValueFied:AreaName

LabelField:AreaName

DefaultValues: from Query

dataSetsGetAreas

ValueFied:AreaName

First time I open the report

the first and the second parameters are properly filled and for both parameters "select all" is checked.

I select one option from the first parameter, the second parameter's content change dinamically and "Select All" option is still selected !Cool

Now I change the selection on the first Parameter, by checking AN ANOTHER item from the list , the second parameter list refresh dinamically but "Select all" IS NOT selected and only the item that were previously checked kept the selection !!!NO!!

Is this a bug in Reporting services and I have to say to ther user that is not possible to develop what they would like to have or there is , even programmatically, a way to solve it?

Thankx for any helps!

Marina B

Hello,

I notice that your second parameter is populated by a stored procedure. Is it possible for you to perform that query from within SRS rather than as a stored procedure?

I'm fairly new at this, so I hesitate to make an absolute statement, but multi-value parameters don't work well with stored procedures.

Also, the second query, the one accepting the first parameter must use an IN clause, rather than a WHERE clause, to filter the results.

I bow to any contradictory experience others may have.

Respectfully,

David Emigh

|||

Hi David,

I changed the definition of my parameters based on your advice

dataSet: dsGetCountry (defined as select from tblCountries)

ValueFied: ContryID

LabelField: CountryName

DefaultValues: from Query

dataSet: dsGetCountry

ValueFied: ContryName

Now the Second Parameter is setup in this ways

I created a new DataSet named: dsgetAreaByCountryID

select distinct(AreaName)
from [dbo].[tblArea] a
INNER JOIN [dbo].[tblcountry] c on a.CountryID = c.CountryID
where not AreaName is null and c.CountryID in (@.cc)
group by AreaName
order by AreaName asc

@.cc is a new Parameter which value is =Parameters!CountryID.Value

I tried to copy write directly c.CountryID in (Parameters!CountryID.Value)
but I get an error ( the ! is not welcome)

dataSet: dsgetAreaByCountryID

ValueFied: AreaName

LabelField: AreaName

DefaultValues: from Query

dataSet: dsgetAreaByCountryID

ValueFied: AreaName

Anyway I have the same problem as with the Stored Procedure!!

Did I follow exactly your advice?

Thank you very much
Marina B

|||

Have you adjusted the settings in the report parameters?

From the data or layout view:

Report menu > Parameters

|||

mmhhh ... your last reply is not completely clear to me.

what do you mean with that...

I have changed the parameter setting as I wrote in my last message...

Did I forgot something?

Thankx

Marina B.

|||I have had the same results. Don't think it's a bug. It looses track if you start going back and forth. If you want to start the parameter selection over you might be better off hitting refresh and selecting the parameters in the correct order. Be sure to explain the same to your end users. I do agree that it would be nice if it always keep track of them.|||

How is possible to hit the refresh?

Programmatically?

Thankx very much

MArina B.

sql

Problem using CAST with Zeroed out Field Values

Hello --
I have a field with date values like: 20050714. When using SQL Server
to convert it to a Date in a View, I use the following:
CAST(MyDate as DateTime)

That works great. The problem is that some rows have the value:
00000000

When it hits one of these, I get a SQL Server Enterprise Manager Error:

Database Server: Microsoft SQL Server
Version: 08.00.0760
Runtime Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.

Is there a workaround for this? Any assistance greatly appreciated.

RBollinger1) Quit storing dates as strings and numbers. This is the REAL answer.
And kill the guy who did this so he will not screw up the rest of the
database.

2) You did not give us specs; did you want the non-existent date
0000-00-00 to be a NULL or what? The error is exactly right ad shows
that your design has some serious problems. .

CAST (CASE @.my_string_date
WHEN '00000000'
THEN NULL -- or dummy value
ELSE @.my_string_date END AS DATETIME)

A better would be to do an update on the column, get rid of the
mythical dates and then replace that column with a temporal one after
validating the existing dates with a procedure. Don't just mop the
floor; fix the leak!|||robboll (robboll@.hotmail.com) writes:
> I have a field with date values like: 20050714. When using SQL Server
> to convert it to a Date in a View, I use the following:
> CAST(MyDate as DateTime)
> That works great. The problem is that some rows have the value:
> 00000000
> When it hits one of these, I get a SQL Server Enterprise Manager Error:
> Database Server: Microsoft SQL Server
> Version: 08.00.0760
> Runtime Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
> conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> Is there a workaround for this? Any assistance greatly appreciated.

You can use the isdate() function to check whether a string is a
legal date value or not.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Combining your answer with the second guys answer, I think I will try
something like:

CAST(CASE@.my_string_date
WHEN isdate(my_string_date) = True
THEN NULL
ELSE @.my_string_date END AS DATETIME)

Thanks for your help!
RBollinger|||"isdate(my_string_date) = TRUE " is wrong; there is no Boolean datatype
or constants in SQL. You need a numeric value Did you fix the leak?|||--CELKO-- wrote:
> "isdate(my_string_date) = TRUE " is wrong; there is no Boolean
datatype
> or constants in SQL. You need a numeric value Did you fix the leak?

Yes I did "fix the leak". I ended up removing all invalid date values
as part of the append process -- the recommended solution.

Thanks.