configuration management and version history SQL 2005

I have a Database on SQL 2005.I want to trace what all changes were made in
this database on a particular date. A kind of report that will highlight all
changes made to the DB on that date. How do I acheive that ? Please advice.
NAT (NAT@.discussions.microsoft.com) writes:
> I have a Database on SQL 2005.I want to trace what all changes were made
> in this database on a particular date. A kind of report that will
> highlight all changes made to the DB on that date. How do I acheive that
> ? Please advice.
What changes? Data? Metadata?
The default traces captures some of the metadata changes (I can't vouch
for that it captures it all). You can access this information by right-
clicking the database, select Reports and then Schema Changes History.
If the server has been restarted since that particular date, this is not
like to work out.
If you are looking for changes in data, a log reader is your sole
alternative. And only if you run with full recovery and still have
the transaction log around. Log readers are third-party tools and some
vendors are Lumigent, Log PI and ApexSQL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Are you looking for a change control tool? If so then Embarcedaro's Change
Manager is very good
Sincerely,
John K
Knowledgy Consulting
http://knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
"NAT" <NAT@.discussions.microsoft.com> wrote in message
news:60C27EC6-F62F-4328-87BC-FA6BBCCC25E2@.microsoft.com...
>I have a Database on SQL 2005.I want to trace what all changes were made in
> this database on a particular date. A kind of report that will highlight
> all
> changes made to the DB on that date. How do I acheive that ? Please
> advice.

Conditionally hiding column in matrix

I have a matrix with the following format
Date
Month RowGroup1 Group2 Amount
I have month in the rows because I want to page on month when
exporting to excel in order to produce a new sheet. This seem to work
ok except for the following.
1. I'm passing in a date range (7/1/2004 - 8/31/2004) When the report
is displayed, I correctly get a report paged by month but the date
column shows all date between 7/1 and 8/31 on both sheets, regardless
of having a value in the amount field. So for July all August dates
are displayed and for July all August values are displayed. Can I
hide dates where the amount is null or missing?
2. When I export to excel the individual sheets are called sheet1,
sheet2... Is there a way to give the name of each sheet the
corresponding month value?
Thanks for your assistance?
DaveRather than putting the month in the matrix, put the matrix in a list which
groups by month.
You cannot control the Excel sheet names in the current version.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Dave" <davidbr93@.yahoo.com> wrote in message
news:703390f1.0408311012.2310ed6f@.posting.google.com...
> I have a matrix with the following format
> Date
> Month RowGroup1 Group2 Amount
>
> I have month in the rows because I want to page on month when
> exporting to excel in order to produce a new sheet. This seem to work
> ok except for the following.
>
> 1. I'm passing in a date range (7/1/2004 - 8/31/2004) When the report
> is displayed, I correctly get a report paged by month but the date
> column shows all date between 7/1 and 8/31 on both sheets, regardless
> of having a value in the amount field. So for July all August dates
> are displayed and for July all August values are displayed. Can I
> hide dates where the amount is null or missing?
> 2. When I export to excel the individual sheets are called sheet1,
> sheet2... Is there a way to give the name of each sheet the
> corresponding month value?
> Thanks for your assistance?
> Dave

Conditional statement with a cast from string to date

My source file is showing column 10 as string. My destination table is datetime. I am using the derived transformation with a conditional statement. How do I convert the value from string to date. Everywhere I try the (DT_DATE) I get an error.

[Column 10] == "01/01/0001" ? " 01/01/1801" : [Column 10] <= "12/31/1801" ? "12/31/1801" : [Column 10]

What's the error?|||

I modified it to the following but I get an error when I try to debug.

[Column 10] == "01/01/0001" ? (dt_date)" 01/01/1801" : [Column 10] <= "12/31/1801" ? (dt_date)"12/31/1801" : (dt_date)[Column 10]

Error message is:

...conversion between types dt_str and db_timestamp is not supported

|||What is the output column data type specified as in the derived column?|||Where do I check that? I only see the input defined in the derived column transformation which is dt_string 50. The column is defined as datetime in the table.|||http://ssistalk.blogspot.com/2007/01/derived-column.html

I would expect to see the data type of the derived column be DT_DBTIMESTAMP (or DT_DBDATE). The expression should be:

[column10] == "xxxxxx" ? "01/01/1801" : ......

Make sure "Derived Column" is set to "add as new column".|||I don't want to add it as a new column. I am using the conditional statement instead of the SQL case statement. I have dates from Oracle that are outside SQL's range that I need to convert.|||Right, but you can't replace the column because it's a DT_STR.... So if you want a date data type, you need to have a new column. This is the proper way to do it. Then in the data flow, you just ignore [column10] and use [DateColumn10], for instance.|||I tried that but now I get the same error for my new column10.|||

RMooreFL wrote:

I tried that but now I get the same error for my new column10.

Okay, I've posted a new example.

http://ssistalk.blogspot.com/2007/01/derived-column.html

Conditional split on date ?

Hi,

I have a DT_DATE column. I'd like to achieve a conditional split to ignore all records for which the date is below a specific hardcoded date (eg: 2007-03-01).

I'm having a hard time trying to express this using the conditional split transform.

What is the correct syntax to express a DT_DATE literal ?

eg:
[date] < (DT_DATE) "2007-03-01"

regards

Thibaut

What you have should work fine. I built a little test package to verify, and each of these worked as expected:

Code Snippet

HireDate < (DT_DATE)"1998-01-30"

Code Snippet

[HireDate] < (DT_DATE)"1998-01-30"

Code Snippet

HireDate < (DT_DATE)"01/30/1998"

Code Snippet

[HireDate] < (DT_DATE)"01/30/1998"

What behavior are you experiencing that prompts you to ask the question?

|||Are you sure [date] is a DT_DATE column and not a DT_DBTIMESTAMP column? That is, does it contain a time component?

Just double checking.

Conditional Split - Compare DATETIME with constant

Hi,

I have to compare a DATETIME Field with '1/1/1900 12:00:00 AM". Which is default DATE TIME Value in SQL Server.

I did compare like

TRADEAGREEMENTFROMDATE != (DT_DBTIMESTAMP)(DATEPART("mm",(DT_DBTIMESTAMP)"1/1/1900 12:00:00 AM"))

but (DT_DBTIMESTAMP)(DATEPART("mm",(DT_DBTIMESTAMP)"1/1/1900 12:00:00 AM")) returns "12/31/1800 12:00:00:AM"

Thanks,

Aravind

So the value I got was slightly different.

(DT_DBTIMESTAMP)(DATEPART("mm",(DT_DBTIMESTAMP)"1/1/1900 12:00:00 AM")) = 31/12/1899 00:00:00

Why are you using DATEPART? Do you not just want -

(DT_DBTIMESTAMP)"1/1/1900" = 01/01/1900 00:00:00

That is the same as '1/1/1900 12:00:00 AM' which you asked for above, infact the time format is just my local settings UK rather than US, the values are exactly the same.

Conditional Report Parameters

I have a report with required parameters of company and project. I then have 3 parameters that I want to be conditional - start date, end date and cycle.

By conditional I mean if cycle is chosen then start & end date are not required. If start and end date are chosen then cycle is not required.

I have made all 3 parameters 'Allow Null Values" however cycle's available values are set from a query once a project is chosen. When the field refreshes I lose my null checkbox for cycle, so it ends up always being required.

Am I going about this the wrong way? I'm using 2005.

I'm not sure that conditional report parameters exist, but could you accomplish this using optional parameters?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=621340&SiteID=1

|||

Hmm, not really. I don't want all 3 to be optional, either cycle or start/end date need to be required.

I'll keep banging my head against the wall... Thanks though.

Conditional Formatting with a date

Hi all,
I have some dates in a recordset in english format dd/mm/yyyy
If the date returned is 1st Jan of any year then i just want the year to be
displayed.
i.e date returned = 1/1/2000 i just want to display the year so... (yyyy)
if the date is 1/3/2000 then return the whole date
Is this possible through conditional formatting?
--
Thanks in advance,
Dave HuntDave,
I don't think it will work via conditional formatting.
But you can use the following expresion for your value:
=IIF( date.Value = New Date(Year( date.Value), 1, 1), Year(date.Value),
date.Value) with date.Value as your date return value.
Jan Pieter Posthuma
"DustpanDave" wrote:
> Hi all,
> I have some dates in a recordset in english format dd/mm/yyyy
> If the date returned is 1st Jan of any year then i just want the year to be
> displayed.
> i.e date returned = 1/1/2000 i just want to display the year so... (yyyy)
> if the date is 1/3/2000 then return the whole date
> Is this possible through conditional formatting?
> --
> Thanks in advance,
> Dave Hunt|||hi jan,
=IIF (Fields!StartDate.Value =New Date(Year(Fields!StartDate.Value), 1,
1),'yyyy','MMMM yyyy')
If i do this code in the function of the properties of the field then i get
an error saying 'Expression expected'
would this code work if i put it in the value part of the field?
--
Thanks in advance,
Dave Hunt
"Jan Pieter Posthuma" wrote:
> Dave,
> I don't think it will work via conditional formatting.
> But you can use the following expresion for your value:
> =IIF( date.Value = New Date(Year( date.Value), 1, 1), Year(date.Value),
> date.Value) with date.Value as your date return value.
> Jan Pieter Posthuma
> "DustpanDave" wrote:
> > Hi all,
> >
> > I have some dates in a recordset in english format dd/mm/yyyy
> >
> > If the date returned is 1st Jan of any year then i just want the year to be
> > displayed.
> >
> > i.e date returned = 1/1/2000 i just want to display the year so... (yyyy)
> >
> > if the date is 1/3/2000 then return the whole date
> >
> > Is this possible through conditional formatting?
> > --
> > Thanks in advance,
> > Dave Hunt|||Hi,
Something like this might work...?
=iif(datepart("d",Fields!StartDate.Value) &
datepart("m",Fields!StartDate.Value) =11,datepart("yyyy",Fields!StartDate.Value),Fields!StartDate.Value)
"DustpanDave" wrote:
> hi jan,
> =IIF (Fields!StartDate.Value =New Date(Year(Fields!StartDate.Value), 1,
> 1),'yyyy','MMMM yyyy')
> If i do this code in the function of the properties of the field then i get
> an error saying 'Expression expected'
> would this code work if i put it in the value part of the field?
> --
> Thanks in advance,
> Dave Hunt
>
> "Jan Pieter Posthuma" wrote:
> > Dave,
> >
> > I don't think it will work via conditional formatting.
> > But you can use the following expresion for your value:
> > =IIF( date.Value = New Date(Year( date.Value), 1, 1), Year(date.Value),
> > date.Value) with date.Value as your date return value.
> >
> > Jan Pieter Posthuma
> >
> > "DustpanDave" wrote:
> >
> > > Hi all,
> > >
> > > I have some dates in a recordset in english format dd/mm/yyyy
> > >
> > > If the date returned is 1st Jan of any year then i just want the year to be
> > > displayed.
> > >
> > > i.e date returned = 1/1/2000 i just want to display the year so... (yyyy)
> > >
> > > if the date is 1/3/2000 then return the whole date
> > >
> > > Is this possible through conditional formatting?
> > > --
> > > Thanks in advance,
> > > Dave Hunt|||Dave,
You should use double quotes (") for the date format parts.
Jan Pieter Posthuma
"DustpanDave" wrote:
> hi jan,
> =IIF (Fields!StartDate.Value =New Date(Year(Fields!StartDate.Value), 1,
> 1),'yyyy','MMMM yyyy')
> If i do this code in the function of the properties of the field then i get
> an error saying 'Expression expected'
> would this code work if i put it in the value part of the field?
> --
> Thanks in advance,
> Dave Hunt
>
> "Jan Pieter Posthuma" wrote:
> > Dave,
> >
> > I don't think it will work via conditional formatting.
> > But you can use the following expresion for your value:
> > =IIF( date.Value = New Date(Year( date.Value), 1, 1), Year(date.Value),
> > date.Value) with date.Value as your date return value.
> >
> > Jan Pieter Posthuma
> >
> > "DustpanDave" wrote:
> >
> > > Hi all,
> > >
> > > I have some dates in a recordset in english format dd/mm/yyyy
> > >
> > > If the date returned is 1st Jan of any year then i just want the year to be
> > > displayed.
> > >
> > > i.e date returned = 1/1/2000 i just want to display the year so... (yyyy)
> > >
> > > if the date is 1/3/2000 then return the whole date
> > >
> > > Is this possible through conditional formatting?
> > > --
> > > Thanks in advance,
> > > Dave Hunt

Conditional count

Hello,

I have a report, for which I want to do a conditional count. The table is something like this:
ID Date Description
1 30-09-2000 10:36:06 Desc1
2 15-03-2000 11:45:11 Desc2
3 12-07-2005 16:21:10 Desc3
4 10-09-2006 11:12:18 Desc4
(...)

I want to count only the entries where the Date field has the value 2000.
The expressiona that I am using is the following one:
=Sum(IIF(Fields!Date.Value.ToString().Contains("2000"),1,0))
It is giving me the right value but my problem is that the value apears repeated. Something like:
CountResult
2
2
2
(...)
What I want to have is just one value of the conditional count.
Has anyone passed by something like this? Any solutions?

Thanks and best regards

vjn

By the look of the layout, you are using a table. In the details cell don't use the Sum() function you only put this in the group/table footer to give you the subtotal. So if my understaning is correct:

Details cell expression: =IIF(Fields!Date.Value.ToString().Contains("2000"),1,0)
Footer cell expression : =Sum(IIF(Fields!Date.Value.ToString().Contains("2000"),1,0))

ID Date Description CountResult
===================================================
1 30-09-2000 10:36:06 Desc1 1
2 15-03-2000 11:45:11 Desc2 1
3 12-07-2005 16:21:10 Desc3 0
4 10-09-2006 11:12:18 Desc4 0
===================================================
TOTAL 2

|||You are right. That works perfectly!
Thank you Adam.

Best regards

vjn

conditional color property based on date

I want to use an IIF statement to set the color property for a text box.
Here is my code that is not working:
=IIf(DateAdd('m', -6, Fields!Orig_expireDate.Value) <
Now(), "red", "black")Use " instead of '
( ' is a comment for vb, even in reporting services)
Mike G.
"ladydi_1226" <ladydi1226@.discussions.microsoft.com> wrote in message
news:92B486DB-EFFA-49BC-AEE3-76CBBF4A24AE@.microsoft.com...
>I want to use an IIF statement to set the color property for a text box.
> Here is my code that is not working:
> =IIf(DateAdd('m', -6, Fields!Orig_expireDate.Value) <
> Now(), "red", "black")

COndition Spli - Error date condition

Dear friends,

I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...

I need to filter rows in a dataflow...

I created a condition spli to that... maybe there is a better solution...

And the condition is: Datex != NULL(DT_DATE)

(Some DATE != NULL)

[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.

What is wrong?

Regards,

Pedro

Hi Pedro,

What if you use IsNull(Datex) to define your condition? You could then use the default output for all rows where Datex is not Null.

Hope this helps,

Andy

|||

oooohhhh Men... soo simple... last week is being too hard for me... lot of work.... jesus!!!

Thanks!!

|||

Hi Pedro,

You're welcome! Don't be so hard on yourself. SSIS isn't intuitive and I've been working with it a while. We're all still learning!

Andy

Condition on a field date in a query data set

I need to extraxt from a table of my db the records that satisfy a condition
on a field date.
I use this query string for the data set:
="SELECT Cliente, NrDocumento, DataDocumento, NrRegistrazione,
DataRegistrazione, TipoDocumento, Descrizione, DataScadenza, Importo,
ImportoIncassato FROM Partitario WHERE (Azienda = " & "'" &
Parameters!Azienda.Value & "') AND (Cliente = " & "'" &
Parameters!Cliente.Value & "') AND (Importo - ImportoIncassato > 0) AND
(DataScadenza<=" & Format(Globals!ExecutionTime,"d") & ")"
In my table Partitario exists a record with datascadenza < actual date
calculated by Globals!ExecutionTime: but this query doesn't extract nothing!
Is the syntax right?
How can I solve this issue?
Many thanksUse a datediff instead of a <= comparison.
Craig
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:E6FB900D-CE25-4688-A88F-9384592FBA3A@.microsoft.com...
>I need to extraxt from a table of my db the records that satisfy a
>condition
> on a field date.
> I use this query string for the data set:
> ="SELECT Cliente, NrDocumento, DataDocumento, NrRegistrazione,
> DataRegistrazione, TipoDocumento, Descrizione, DataScadenza, Importo,
> ImportoIncassato FROM Partitario WHERE (Azienda = " & "'" &
> Parameters!Azienda.Value & "') AND (Cliente = " & "'" &
> Parameters!Cliente.Value & "') AND (Importo - ImportoIncassato > 0) AND
> (DataScadenza<=" & Format(Globals!ExecutionTime,"d") & ")"
> In my table Partitario exists a record with datascadenza < actual date
> calculated by Globals!ExecutionTime: but this query doesn't extract
> nothing!
> Is the syntax right?
> How can I solve this issue?
> Many thanks|||Or use GETDATE() in SQL query string.
Many thanks
"Craig" wrote:
> Use a datediff instead of a <= comparison.
> Craig
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:E6FB900D-CE25-4688-A88F-9384592FBA3A@.microsoft.com...
> >I need to extraxt from a table of my db the records that satisfy a
> >condition
> > on a field date.
> > I use this query string for the data set:
> > ="SELECT Cliente, NrDocumento, DataDocumento, NrRegistrazione,
> > DataRegistrazione, TipoDocumento, Descrizione, DataScadenza, Importo,
> > ImportoIncassato FROM Partitario WHERE (Azienda = " & "'" &
> > Parameters!Azienda.Value & "') AND (Cliente = " & "'" &
> > Parameters!Cliente.Value & "') AND (Importo - ImportoIncassato > 0) AND
> > (DataScadenza<=" & Format(Globals!ExecutionTime,"d") & ")"
> >
> > In my table Partitario exists a record with datascadenza < actual date
> > calculated by Globals!ExecutionTime: but this query doesn't extract
> > nothing!
> > Is the syntax right?
> >
> > How can I solve this issue?
> >
> > Many thanks
>
>

Condensing/Modularizing several cumulative calculated measures

In my cube, I have a number of calculated measures - around 30 per date tree and sales type combination (each different sales type has a different fiscal year). I have a Product.Sales Type attribute that can flicker the results between the different sale types, but the issue of working with different hierarchies is still present

Is there a way to condense this statement or somehow hookup some pieces so that one doesn't have to keep coding the same logic over and over? Either reducing the # of calculated members, or simply using some supported functionality to only code the logic in 1 spot, passing in the 'sale type' and 'measure name' and the inner logic would calculate the correct date tree and year level all within itself.

The pieces that change are bolded

(Date Tree hierarchy name,

Date Tree year level,

Calculated measure name

)

CASE

WHEN [Date].[Date Tree].CurrentMember.Level IS

[Date].[Date Tree].[(All)]

THEN [Measures].[Calculated Sales]

ELSE

Sum

(

PeriodsToDate

( [Date].[Date Tree].[Fiscal Year],

[Date].[Date Tree].CurrentMember

),

[Measures].[Calculated Sales]

)

END

One of my thoughts is to do something like this, but can someone feed me some ideas and fill in the syntax holes?

public [return type?] GetCumulativeMeasure( ‘Group 1’, ‘Calculated Sales 1’)

public [return type?] GetCumulativeMeasure(SalesType as string, MeasureName as string)

{

string Hierarchy = “”;

string HierarchyYearLevel = “”;

switch (SalesType)

{

case ‘Group 1’:Hierarchy = “[Date].[Date Tree]”; HierarchyYearLevel = Hierarchy + “.[Fiscal Year]”;

case ‘Group 2’:Hierarchy = “[Date].[Date Tree B]”; HierarchyYearLevel = Hierarchy + “.[Year Period]”;

}

//build calculated measure string to get a calculated measure for the year period and MeasureName variables

}

One thing you can do is create what's known as a 'time utility' or 'shell' dimension. There's a good writeup of what this means here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

Incidentally, your use of the CASE statement to check what level you're at inside your calculation is not the most efficient way of writing the expression. Take a look at Mosha's blog entry on this subject for details on how you can use scoped assignments to do this instead:

http://sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx

This might also reduce the amount of code you need to write.

HTH,

Chris

|||

Thank you for the links! It's always nice to know that there's a better way - I had a feeling about that as well, as things were too clunky.

|||

I've read over the material, but I can't picture how to put in mosha's suggestions into the mix.
Some date calculations I'd be doing are L30, L60, and Cumulative based on year.

The Last _ Days should only work with the Date level and always go back 30 days from the current membe,
while cumulative would be limited to the year that's being used.

I envision a shell dimension attribute with the following members:
L30 Days
L60 Days
MTD
LY MTD
YTD
LYTD
Yearly Cumulative

Could someone provide an example with a few of the calculations or let me know what other info would be helpful to post? Thank you for working with me through this process!!

|||

I think the logic you've already got in your calculations will stay pretty much the same; it's scoping the calculations that will be tricky. We'd need to know some details about your Date dimension structure.

Chris

|||

The date dimension hierarchy structure's right now are as follows. What more info is needed?

[Date].[Date Tree A]
Year Type A (spans from jan-dec)
Month
Day

[Date].[Date Tree B]
Year Type B (spans from march-july this year, then next year goes from Aug-July)
Month
Day

[Date].[Date Tree C]
Year Type C (spans from oct-sept)
Month
Day

Calculated measures include:

hierarchy based:
(Cumulative based on year and hard coded to a specific date tree)
Cumulative Type A Sales
Cumulative Type A Internet Sales
Cumulative Type B Sales
Cumulative Type B Internet Sales
Cumulative Type C Sales
Cumulative Type C Internet Sales

(hopefully obtainable in both hierarchy and non hierarchy form (from the Month or Day level):

LY MTD Type A Sales
LY MTD Type B Sales
LY MTD Type C Sales

MTD Type A Sales
MTD Type B Sales
MTD Type C Sales

Not hierarchy based:
L30 Days Sales
L30 Days Internet Sales
L60 Days Sales
L60 Days Internet Sales
(for these, I'm hoping to somehow just have a reusable 'L30' and 'L60' scoping, but that would allow you to see measures in both time periods in the same axis. If it's a good route to use a time shell dimension, then that's what I would do, but I'm looking for a little bit more instruction if possible.

Condensing/Modularizing several cumulative calculated measures

In my cube, I have a number of calculated measures - around 30 per date tree and sales type combination (each different sales type has a different fiscal year). I have a Product.Sales Type attribute that can flicker the results between the different sale types, but the issue of working with different hierarchies is still present

Is there a way to condense this statement or somehow hookup some pieces so that one doesn't have to keep coding the same logic over and over? Either reducing the # of calculated members, or simply using some supported functionality to only code the logic in 1 spot, passing in the 'sale type' and 'measure name' and the inner logic would calculate the correct date tree and year level all within itself.

The pieces that change are bolded

(Date Tree hierarchy name,

Date Tree year level,

Calculated measure name

)

CASE

WHEN [Date].[Date Tree].CurrentMember.Level IS

[Date].[Date Tree].[(All)]

THEN [Measures].[Calculated Sales]

ELSE

Sum

(

PeriodsToDate

( [Date].[Date Tree].[Fiscal Year],

[Date].[Date Tree].CurrentMember

),

[Measures].[Calculated Sales]

)

END

One of my thoughts is to do something like this, but can someone feed me some ideas and fill in the syntax holes?

public [return type?] GetCumulativeMeasure( ‘Group 1’, ‘Calculated Sales 1’)

public [return type?] GetCumulativeMeasure(SalesType as string, MeasureName as string)

{

string Hierarchy = “”;

string HierarchyYearLevel = “”;

switch (SalesType)

{

case ‘Group 1’:Hierarchy = “[Date].[Date Tree]”; HierarchyYearLevel = Hierarchy + “.[Fiscal Year]”;

case ‘Group 2’:Hierarchy = “[Date].[Date Tree B]”; HierarchyYearLevel = Hierarchy + “.[Year Period]”;

}

//build calculated measure string to get a calculated measure for the year period and MeasureName variables

}

One thing you can do is create what's known as a 'time utility' or 'shell' dimension. There's a good writeup of what this means here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

Incidentally, your use of the CASE statement to check what level you're at inside your calculation is not the most efficient way of writing the expression. Take a look at Mosha's blog entry on this subject for details on how you can use scoped assignments to do this instead:

http://sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx

This might also reduce the amount of code you need to write.

HTH,

Chris

|||

Thank you for the links! It's always nice to know that there's a better way - I had a feeling about that as well, as things were too clunky.

|||

I've read over the material, but I can't picture how to put in mosha's suggestions into the mix.
Some date calculations I'd be doing are L30, L60, and Cumulative based on year.

The Last _ Days should only work with the Date level and always go back 30 days from the current membe,
while cumulative would be limited to the year that's being used.

I envision a shell dimension attribute with the following members:
L30 Days
L60 Days
MTD
LY MTD
YTD
LYTD
Yearly Cumulative

Could someone provide an example with a few of the calculations or let me know what other info would be helpful to post? Thank you for working with me through this process!!

|||

I think the logic you've already got in your calculations will stay pretty much the same; it's scoping the calculations that will be tricky. We'd need to know some details about your Date dimension structure.

Chris

|||

The date dimension hierarchy structure's right now are as follows. What more info is needed?

[Date].[Date Tree A]
Year Type A (spans from jan-dec)
Month
Day

[Date].[Date Tree B]
Year Type B (spans from march-july this year, then next year goes from Aug-July)
Month
Day

[Date].[Date Tree C]
Year Type C (spans from oct-sept)
Month
Day

Calculated measures include:

hierarchy based:
(Cumulative based on year and hard coded to a specific date tree)
Cumulative Type A Sales
Cumulative Type A Internet Sales
Cumulative Type B Sales
Cumulative Type B Internet Sales
Cumulative Type C Sales
Cumulative Type C Internet Sales

(hopefully obtainable in both hierarchy and non hierarchy form (from the Month or Day level):

LY MTD Type A Sales
LY MTD Type B Sales
LY MTD Type C Sales

MTD Type A Sales
MTD Type B Sales
MTD Type C Sales

Not hierarchy based:
L30 Days Sales
L30 Days Internet Sales
L60 Days Sales
L60 Days Internet Sales
(for these, I'm hoping to somehow just have a reusable 'L30' and 'L60' scoping, but that would allow you to see measures in both time periods in the same axis. If it's a good route to use a time shell dimension, then that's what I would do, but I'm looking for a little bit more instruction if possible.

Condensing/Modularizing several cumulative calculated measures

In my cube, I have a number of calculated measures - around 30 per date tree and sales type combination (each different sales type has a different fiscal year). I have a Product.Sales Type attribute that can flicker the results between the different sale types, but the issue of working with different hierarchies is still present

Is there a way to condense this statement or somehow hookup some pieces so that one doesn't have to keep coding the same logic over and over? Either reducing the # of calculated members, or simply using some supported functionality to only code the logic in 1 spot, passing in the 'sale type' and 'measure name' and the inner logic would calculate the correct date tree and year level all within itself.

The pieces that change are bolded

(Date Tree hierarchy name,

Date Tree year level,

Calculated measure name

)

CASE

WHEN [Date].[Date Tree].CurrentMember.Level IS

[Date].[Date Tree].[(All)]

THEN [Measures].[Calculated Sales]

ELSE

Sum

(

PeriodsToDate

( [Date].[Date Tree].[Fiscal Year],

[Date].[Date Tree].CurrentMember

),

[Measures].[Calculated Sales]

)

END

One of my thoughts is to do something like this, but can someone feed me some ideas and fill in the syntax holes?

public [return type?] GetCumulativeMeasure( ‘Group 1’, ‘Calculated Sales 1’)

public [return type?] GetCumulativeMeasure(SalesType as string, MeasureName as string)

{

string Hierarchy = “”;

string HierarchyYearLevel = “”;

switch (SalesType)

{

case ‘Group 1’:Hierarchy = “[Date].[Date Tree]”; HierarchyYearLevel = Hierarchy + “.[Fiscal Year]”;

case ‘Group 2’:Hierarchy = “[Date].[Date Tree B]”; HierarchyYearLevel = Hierarchy + “.[Year Period]”;

}

//build calculated measure string to get a calculated measure for the year period and MeasureName variables

}

One thing you can do is create what's known as a 'time utility' or 'shell' dimension. There's a good writeup of what this means here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

Incidentally, your use of the CASE statement to check what level you're at inside your calculation is not the most efficient way of writing the expression. Take a look at Mosha's blog entry on this subject for details on how you can use scoped assignments to do this instead:

http://sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx

This might also reduce the amount of code you need to write.

HTH,

Chris

|||

Thank you for the links! It's always nice to know that there's a better way - I had a feeling about that as well, as things were too clunky.

|||

I've read over the material, but I can't picture how to put in mosha's suggestions into the mix.
Some date calculations I'd be doing are L30, L60, and Cumulative based on year.

The Last _ Days should only work with the Date level and always go back 30 days from the current membe,
while cumulative would be limited to the year that's being used.

I envision a shell dimension attribute with the following members:
L30 Days
L60 Days
MTD
LY MTD
YTD
LYTD
Yearly Cumulative

Could someone provide an example with a few of the calculations or let me know what other info would be helpful to post? Thank you for working with me through this process!!

|||

I think the logic you've already got in your calculations will stay pretty much the same; it's scoping the calculations that will be tricky. We'd need to know some details about your Date dimension structure.

Chris

|||

The date dimension hierarchy structure's right now are as follows. What more info is needed?

[Date].[Date Tree A]
Year Type A (spans from jan-dec)
Month
Day

[Date].[Date Tree B]
Year Type B (spans from march-july this year, then next year goes from Aug-July)
Month
Day

[Date].[Date Tree C]
Year Type C (spans from oct-sept)
Month
Day

Calculated measures include:

hierarchy based:
(Cumulative based on year and hard coded to a specific date tree)
Cumulative Type A Sales
Cumulative Type A Internet Sales
Cumulative Type B Sales
Cumulative Type B Internet Sales
Cumulative Type C Sales
Cumulative Type C Internet Sales

(hopefully obtainable in both hierarchy and non hierarchy form (from the Month or Day level):

LY MTD Type A Sales
LY MTD Type B Sales
LY MTD Type C Sales

MTD Type A Sales
MTD Type B Sales
MTD Type C Sales

Not hierarchy based:
L30 Days Sales
L30 Days Internet Sales
L60 Days Sales
L60 Days Internet Sales
(for these, I'm hoping to somehow just have a reusable 'L30' and 'L60' scoping, but that would allow you to see measures in both time periods in the same axis. If it's a good route to use a time shell dimension, then that's what I would do, but I'm looking for a little bit more instruction if possible.

concatnate values for different rows

Hi ,

I have a situation where i need to concatnate values from different rows and store it a one string.

sample

dealid date

1 1/5/2007

1 2/4/2009

2 5/5/2004

2 8/5/2006

2 4/8/2006

so for one particular deal how many ever dates there are , i need to concatnate them all separated by a comma(,) and return and one string.

Is ther any way i could do it, Any suggestions appreaciated

Thanks

Ashsih

Maybe something like:

select distinct dealId,
reverse(substring(reverse(
( select convert(varchar(10), date, 101) + ', ' as [text()]
from theTable b
where a.dealId = b.dealId

order by date desc
for xml path('')
)), 3, 300))
from theTable a

|||

For a particular DealD:

declare @.targetDeal int set @.targetDeal = 1

select reverse(substring(reverse

( select convert(varchar(10), date, 101) + ', ' as [text()]
from theTable

where dealId = @.targetDeal

order by date desc
for xml path('')

), 3, 300)) as DealDates