Analysis Services & Default Value

Is it possible to define a default value for each a parameter also in a dataset?

I have a dataset with a parameter from the dimension Time. I would like to change the default value from the parameter. I tried to create a dataset to get the current year unique name, but if I set it in the parameter dialog it does not recognize the value.

I have a dataset in a report with the parameter Year from the dimension Time.

In the dataset I have to select a default value for the parameter. There is no other option.

In the reports parameters list I can specify a default value. This is not recognized, if it does not mach previous one.

Is it possible to set the default value of such a parameter to the current year value?

An if...else task

Hi,

Im running a database query and I want to compare the value of a datetime field to see what I wanna do with my data.

Is there any easy way of doing this with something from the Toolbox or would you recommend using a Script Task?

Thank you.

What do you want to compare it to? Can you give more detail about what it is you are trying to do?

-Jamie

|||

I have 100 rows of query data.

The rows contain a datetime field that I wanna check.

If the row happens before 10 o'clock:

- Do something

After 10 o'clock:

- Do something else

Thank you.

|||All depends on what Something and Something Else is.

You could use a conditional split to send the rows to different places or add an additional column with the flag stating the change or use the script task if you want to change the value of a variable if the condition is found.|||

I would say that conditional split is almost certainly going to be what you are looking for.

in your example you would just set a single case wherein your example datetime column is compared to less than or equal to 10 o'clock and all other rows are sent to default (greater than 10 o'clock) ...

An explicit value for the identity column in table

for some unknow reasons.. my store proc stop working.. and i got an error..
i have installaed the latest SP4 for SQL server 2000 and still have the problem !
any ideas why ??
Message "An explicit value for the identity column in table 'LCMS_Modules' can only be specified when a column list is used and IDENTITY_INSERT is ON."
CREATE procedure LCMS_Modules_Add

@.PageID int,
@.ModuleDefID int,
@.Panename nvarchar(32),
@.Title nvarchar(128),
@.Admins nvarchar(256)

as

insert into LCMS_Modules
values(@.PageID, @.ModuleDefID, 99, @.Panename, @.Title, '0;', @.Admins, 0, '', 'Center', '', '', '', 1, 0)
GO

Have you reordered the columns in your LCMS_Modules table? I amguessing that you did, and that the identity column had previously beenat the end and is now in the beginning or somewhere in the middle.
Specify your column names in your INSERT statement. This is a best practice anyway and should take care of your problem.
insert into LCMS_Modules (column1, column2, column3, ...etc..., column15),
values(@.PageID, @.ModuleDefID, 99, @.Panename, @.Title, '0;', @.Admins, 0, '', 'Center', '', '', '', 1, 0)

An Amazing Errorrrrrrrrrrrrrrrr

Hi All Friends

i have an amazing error in my project

i wrote a project in ASP.NET 2

a part of this project change a value in a row in a table in sql 2005 to another value

i used it in my computer (With Local host and my own iis) and it works properly and without any problem

but when i upload it in internet it have this error:

"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. "

can anyone help me?

i think the error is from my table in sql 2005 but i dont know what is wrong in it

thanks for your cares friends and im waiting for your usefull answers

It means that someone changed the row between the time you initially retrieved it, and the time you did the update. Or you are pointing at the wrong database for the update.

|||

No it cannot happen

because this form has a password that no one have it and no one know about this form

it cant be

but it's a Notice here :When i delete my table and recreate it again it work probably but sometimes it has this error again

|||

Well, if you're absolutely sure that there is no way that the row could have been changed--there could be other forms or other apps that are updating the table--then there is something wrong in the concurrency code. Did a wizard generate this for you, or did you write some custom code?

Don

AMO - measure numeric Value

I'm building a cube dynamically and i want to list just the columns in the fact table that are numeric in order to make Sum's, AVG's and so on.

How can i do this using AMO ?, i'm wondering if i can do this from the FactTable's (Data Table) DataColumns, but there is no metadata that tells me if the values there are or not valid numeric measures.

Regards Lisber.

Hi. I don't know the answer to your AMO question. However, I do know that you can get the metadata about your fact table from the system views in SQL Server 2005. For example, if you want to know the columns in a fact table with data type Money or Float the following query works against the sales fact in AdventureWorks schema:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'FactInternetSales'
AND (DATA_TYPE = 'float' OR DATA_TYPE = 'money')

A more extensive article about SQL Server 2005 metadata is available from Database Journal at this link:

http://www.databasejournal.com/features/mssql/article.php/3508881

Hope this helps - Paul Goldy

|||Thanks for your answer Paul,

The Problem that i have needs another solution because :( , i'm creating the cube dynamically:

I have the DataTable related to the fact table, but when i start creating the measures i need to do this:

(Copied from AdventureWorks - AMO Example)

meas.Source = CreateDataItem(db.DataSourceViews[0], "FactInternetSalesReason", "SalesOrderLineNumber");

but when i'm doing this dynamically i need to verify if "SalesOrderLineNumber" is or not a measure.

So in my case a have the factTable lets say "FactInternetSalesReason ", and i can make a loop through all of its dataTableColumns, here is where i need to know if that column is or not a valid measure in order to add it.

I'm following the AdventureWorks example, but as far as i can see all the cube creation is hard coded.|||HI. I understand the issue better now. Sorry, but I don't know how to determine if a column is a valid measure in your scenario. Paul Goldy|||Hey Lisber..

I'm not sure i fully understand, but what i gather is you want to add the measures from the fact table if it is some sort of numeric datatype in the table?

If that's the case, you've already got the datatable, so you can do something like this:

Dim DC as new DataColumn

For each DC in YOURDATASET.tables("YOURTABLENAME").Columns
If DC.DataType.Name = "Int32" '//Just an example
'//Add your measure here
End if
Next DC

If that's not what you mean, sorry :) If that is what you mean, and need more additional information, let me know..
C|||

Hi

Chris !, exactly i was thinking in the solution you described before , but i was having some troubles, here is the code that i use to figure out if a

data column is or not a measure ( ie, if its numeric ).

// 1o I make a loop through all the columns of my fact table ("FactResellerSales"), here is the code:

private void showMeasures()
{
for (int i = 0; i < this.dataSet.Tables["FactResellerSales"].Columns.Count; i++)
{
DataColumn column = this.dataSet.Tables["FactResellerSales"].Columns;
if (isMeasure(column.DataType))
Response.Write(column.ColumnName + "<br>");
}

}

private bool isMeasure(Type type)
{

switch (type.Name)
{
case "Int16": return (true);
case "Int32": return (true);
case "Int64": return (true);
case "Decimal": return (true);
case "Double": return (true);
case "Byte": return (true);
case "Boolean": return (true); // see better
default: return (false);

}
}

If anyone knows another way to figure out which columns in the fact table are measures or not, please tell me, because this solution is for sure not the best one !

am i doing this right?

i am trying to count the no of time a value appears in a column for a given event ID. i have written the following code:

private

void TotalChanged()
{
///<summary>
///this procedure calculates the total amount of times the event has been
///modified altogether. the number is read only and written to a text box.
///</summary>int evID = Convert.ToInt32(Request["EID"]);
myda =new SqlDataAdapter("SELECT COUNT (TotalChanged) FROM EventDateChange WHERE EventID = '" + evID + "'", mycn);
myda.Fill(ds3, "ChangedEvent");
foreach (DataRow drin ds3.Tables[0].Rows)
{
int evTotal = (int)dr["TotalChanged"];
Txt_Changes.Text = evTotal.ToString();
}
}

i know the queries ok, as i tested it in SQl Query analyser, but how do i write that value to the textbox called text-changes? everytime i run it i get the errorColumn 'TotalChanged' does not belong to table ChangedEvent.??

Anyone any ideas?? Thanks in advance!

TrySELECT COUNT (TotalChanged)AS TotalChanged FROM EventDateChange WHERE EventID = '" + evID + "'",

This is because when you use COUNT(), there is no column name. After you add AS TotalChanged, you assign the column name to the count.

|||Doh!! Thanks! Works perfectly

Alternative to IIF ? Case ?

Hi,
Need to display a value in a text box based upon perhaps one of four
values a parameter could have. This example is great for two values, = iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
Is there an alternative , case... anything if we need to test for over
parameter values i.e. HHH1, or HHH2.
Many thanks, Steve.Try Switch:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctSwitch.asp
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:77DEB13A-6813-415E-A4A2-58190F3A7CF4@.microsoft.com...
> Hi,
> Need to display a value in a text box based upon perhaps one of four
> values a parameter could have. This example is great for two values, => iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
> Is there an alternative , case... anything if we need to test for over
> parameter values i.e. HHH1, or HHH2.
> Many thanks, Steve.|||Report parameters have a Value and a Label property. You could use the Label
property for this purpose.
Regarding alternatives for IIF please check the following MSDN links:
* =Choose(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctchoose.asp
* =Switch(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctswitch.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:77DEB13A-6813-415E-A4A2-58190F3A7CF4@.microsoft.com...
> Hi,
> Need to display a value in a text box based upon perhaps one of four
> values a parameter could have. This example is great for two values, => iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
> Is there an alternative , case... anything if we need to test for over
> parameter values i.e. HHH1, or HHH2.
> Many thanks, Steve.|||Thank you Robert !
"Robert Bruckner [MSFT]" wrote:
> Report parameters have a Value and a Label property. You could use the Label
> property for this purpose.
> Regarding alternatives for IIF please check the following MSDN links:
> * =Choose(...)
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctchoose.asp
> * =Switch(...)
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctswitch.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:77DEB13A-6813-415E-A4A2-58190F3A7CF4@.microsoft.com...
> > Hi,
> > Need to display a value in a text box based upon perhaps one of four
> > values a parameter could have. This example is great for two values, => > iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
> >
> > Is there an alternative , case... anything if we need to test for over
> > parameter values i.e. HHH1, or HHH2.
> >
> > Many thanks, Steve.
>
>|||Switch rocks !!! Thanks Jeff. Happy New year.
"Jeff A. Stucker" wrote:
> Try Switch:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctSwitch.asp
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:77DEB13A-6813-415E-A4A2-58190F3A7CF4@.microsoft.com...
> > Hi,
> > Need to display a value in a text box based upon perhaps one of four
> > values a parameter could have. This example is great for two values, => > iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
> >
> > Is there an alternative , case... anything if we need to test for over
> > parameter values i.e. HHH1, or HHH2.
> >
> > Many thanks, Steve.
>
>