Preventing Deadlock: date

Showing posts with label date. Show all posts

Wednesday, March 28, 2012

Print Date and Time

I am currently using the global execution time in my reports, but I would
also like to show the user what time they view the report. I guess in
Crystal they distinguish between the two as "Data Date" which would be the
execution time and then "Print Date" which is the date and time the report
is viewed. Does any one know if there a way to show this is RS?
Thanks,
LisaPlease review .Net's DateTime functionality (Search on DateTime in MSDN).
In your case your could place, in a textbox, an expression like
=DateTime.Now.
You could also use the Now() function.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
news:OnOBSRDZEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am currently using the global execution time in my reports, but I would
> also like to show the user what time they view the report. I guess in
> Crystal they distinguish between the two as "Data Date" which would be the
> execution time and then "Print Date" which is the date and time the report
> is viewed. Does any one know if there a way to show this is RS?
> Thanks,
> Lisa
>|||Thank you!
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:O%23oBowEZEHA.712@.TK2MSFTNGP11.phx.gbl...
> Please review .Net's DateTime functionality (Search on DateTime in MSDN).
> In your case your could place, in a textbox, an expression like
> =DateTime.Now.
> You could also use the Now() function.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
> news:OnOBSRDZEHA.3716@.TK2MSFTNGP11.phx.gbl...
> > I am currently using the global execution time in my reports, but I
would
> > also like to show the user what time they view the report. I guess in
> > Crystal they distinguish between the two as "Data Date" which would be
the
> > execution time and then "Print Date" which is the date and time the
report
> > is viewed. Does any one know if there a way to show this is RS?
> >
> > Thanks,
> >
> > Lisa
> >
> >
>|||You can try =now().
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
news:OnOBSRDZEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am currently using the global execution time in my reports, but I would
> also like to show the user what time they view the report. I guess in
> Crystal they distinguish between the two as "Data Date" which would be the
> execution time and then "Print Date" which is the date and time the report
> is viewed. Does any one know if there a way to show this is RS?
> Thanks,
> Lisa
>

Friday, March 23, 2012

Primary Key with a datetime datatype

We're seeing a data conversion error after adding a primary key that contains
a datetime column.
SQL profiler indicated the app was passing a date value in format that is
incompatible (passing dd-mm-yyyy). I am able to reproduce the error in Query
Analyzer.
The vendor is insisting that the problem is due to the primary key. To
humor them, I removed the PK's and of course the problem still persists. But
now the vendor is telling us to re-install the app and migrate all the data
again without defining primary key.
Is there something I don't know about the internal storage of a primary
key/datetime datatype that would cause this error?
Responses appreciated!
"Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
news:DDBAF283-CC40-408A-B942-428DF8A157E3@.microsoft.com...
> SQL profiler indicated the app was passing a date value in format that is
> incompatible (passing dd-mm-yyyy). I am able to reproduce the error in
Query
> Analyzer.
> Is there something I don't know about the internal storage of a primary
> key/datetime datatype that would cause this error?
Aboslutely not...
But why did the date format that the app is passing change? Does the
vendor claim that it was that way before?
|||SORRY - THIS IS A DUPLICATE. I KEPT GETTING ERROR SAYING MY POST DIDN'T POST.
"Colleen Rossman" wrote:

> We're seeing a data conversion error after adding a primary key that contains
> a datetime column.
> SQL profiler indicated the app was passing a date value in format that is
> incompatible (passing dd-mm-yyyy). I am able to reproduce the error in Query
> Analyzer.
> The vendor is insisting that the problem is due to the primary key. To
> humor them, I removed the PK's and of course the problem still persists. But
> now the vendor is telling us to re-install the app and migrate all the data
> again without defining primary key.
> Is there something I don't know about the internal storage of a primary
> key/datetime datatype that would cause this error?
> Responses appreciated!
>
|||A primary key constraint won't affect the way a date is stored and
interpreted.
If the application passes dates as strings then it should use one of the
standard ISO formats such as YYYYMMDD. That's the best way to be sure that
date strings will be interpreted correctly.
David Portas
SQL Server MVP
|||Yes, the vendor does claim it's 'by design' and it's been this way for a long
time. However I found a 'set dateformat=dmy' statement in the trace output
which works in QA, allowing me to pass ddmmyyyy format without error, but it
didn't work in the app and that's teh real issue here.
Colleen
"Adam Machanic" wrote:

> "Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
> news:DDBAF283-CC40-408A-B942-428DF8A157E3@.microsoft.com...
> Query
> Aboslutely not...
> But why did the date format that the app is passing change? Does the
> vendor claim that it was that way before?
>
>
|||Perhaps the app passes appropriate SET DATEFORMAT command, but in a different connection? I'm
thinking things like connection pooling etc. It is definitely an app issue, and Profiler should
reveal what is really happening. I'd strongly suggest to the app vendor to use a "safe" format.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
news:75915596-9A5C-4665-950D-794B05F957C6@.microsoft.com...[vbcol=seagreen]
> Yes, the vendor does claim it's 'by design' and it's been this way for a long
> time. However I found a 'set dateformat=dmy' statement in the trace output
> which works in QA, allowing me to pass ddmmyyyy format without error, but it
> didn't work in the app and that's teh real issue here.
> Colleen
> "Adam Machanic" wrote:
|||Pass/retrieve it as text only. Then post-process
Jeff
"Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
news:DDBAF283-CC40-408A-B942-428DF8A157E3@.microsoft.com...
> We're seeing a data conversion error after adding a primary key that
contains
> a datetime column.
> SQL profiler indicated the app was passing a date value in format that is
> incompatible (passing dd-mm-yyyy). I am able to reproduce the error in
Query
> Analyzer.
> The vendor is insisting that the problem is due to the primary key. To
> humor them, I removed the PK's and of course the problem still persists.
But
> now the vendor is telling us to re-install the app and migrate all the
data
> again without defining primary key.
> Is there something I don't know about the internal storage of a primary
> key/datetime datatype that would cause this error?
> Responses appreciated!
>
sql

Primary Key with a datetime datatype

We're seeing a data conversion error after adding a primary key that contain
s
a datetime column.
SQL profiler indicated the app was passing a date value in format that is
incompatible (passing dd-mm-yyyy). I am able to reproduce the error in Quer
y
Analyzer.
The vendor is insisting that the problem is due to the primary key. To
humor them, I removed the PK's and of course the problem still persists. Bu
t
now the vendor is telling us to re-install the app and migrate all the data
again without defining primary key.
Is there something I don't know about the internal storage of a primary
key/datetime datatype that would cause this error?
Responses appreciated!"Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
news:DDBAF283-CC40-408A-B942-428DF8A157E3@.microsoft.com...
> SQL profiler indicated the app was passing a date value in format that is
> incompatible (passing dd-mm-yyyy). I am able to reproduce the error in
Query
> Analyzer.
> Is there something I don't know about the internal storage of a primary
> key/datetime datatype that would cause this error?
Aboslutely not...
But why did the date format that the app is passing change? Does the
vendor claim that it was that way before?|||SORRY - THIS IS A DUPLICATE. I KEPT GETTING ERROR SAYING MY POST DIDN'T POS
T.
"Colleen Rossman" wrote:

> We're seeing a data conversion error after adding a primary key that conta
ins
> a datetime column.
> SQL profiler indicated the app was passing a date value in format that is
> incompatible (passing dd-mm-yyyy). I am able to reproduce the error in Qu
ery
> Analyzer.
> The vendor is insisting that the problem is due to the primary key. To
> humor them, I removed the PK's and of course the problem still persists.
But
> now the vendor is telling us to re-install the app and migrate all the dat
a
> again without defining primary key.
> Is there something I don't know about the internal storage of a primary
> key/datetime datatype that would cause this error?
> Responses appreciated!
>|||A primary key constraint won't affect the way a date is stored and
interpreted.
If the application passes dates as strings then it should use one of the
standard ISO formats such as YYYYMMDD. That's the best way to be sure that
date strings will be interpreted correctly.
David Portas
SQL Server MVP
--|||Yes, the vendor does claim it's 'by design' and it's been this way for a lon
g
time. However I found a 'set dateformat=dmy' statement in the trace output
which works in QA, allowing me to pass ddmmyyyy format without error, but i
t
didn't work in the app and that's teh real issue here.
Colleen
"Adam Machanic" wrote:

> "Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
> news:DDBAF283-CC40-408A-B942-428DF8A157E3@.microsoft.com...
> Query
> Aboslutely not...
> But why did the date format that the app is passing change? Does the
> vendor claim that it was that way before?
>
>|||Perhaps the app passes appropriate SET DATEFORMAT command, but in a differen
t connection? I'm
thinking things like connection pooling etc. It is definitely an app issue,
and Profiler should
reveal what is really happening. I'd strongly suggest to the app vendor to u
se a "safe" format.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
news:75915596-9A5C-4665-950D-794B05F957C6@.microsoft.com...[vbcol=seagreen]
> Yes, the vendor does claim it's 'by design' and it's been this way for a l
ong
> time. However I found a 'set dateformat=dmy' statement in the trace outpu
t
> which works in QA, allowing me to pass ddmmyyyy format without error, but
it
> didn't work in the app and that's teh real issue here.
> Colleen
> "Adam Machanic" wrote:
>|||Pass/retrieve it as text only. Then post-process
Jeff
"Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
news:DDBAF283-CC40-408A-B942-428DF8A157E3@.microsoft.com...
> We're seeing a data conversion error after adding a primary key that
contains
> a datetime column.
> SQL profiler indicated the app was passing a date value in format that is
> incompatible (passing dd-mm-yyyy). I am able to reproduce the error in
Query
> Analyzer.
> The vendor is insisting that the problem is due to the primary key. To
> humor them, I removed the PK's and of course the problem still persists.
But
> now the vendor is telling us to re-install the app and migrate all the
data
> again without defining primary key.
> Is there something I don't know about the internal storage of a primary
> key/datetime datatype that would cause this error?
> Responses appreciated!
>

Wednesday, March 21, 2012

Primary Key Problem

OK, I create a table called Customer, I imported date from my as400, when i
want to set a primary key it telling me that i have duplicate key.
Keep in mind the layout of my table CustId, SubCustId, in CustId they can
have more then one same CustId, but SubCustID have not the same SubCustID.
example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
can be the same but the sub is always different. Could anyone help me on that
problem I have no more Idea.
Thanks!
JFSeem like the primary key should be the combination of CustId and SubCustId.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> OK, I create a table called Customer, I imported date from my as400, when i
> want to set a primary key it telling me that i have duplicate key.
> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> have more then one same CustId, but SubCustID have not the same SubCustID.
> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> can be the same but the sub is always different. Could anyone help me on that
> problem I have no more Idea.
> Thanks!
> JF|||Is the primary key defined on both columns? If it is, try to import the data
over without the primary key, then check for duplicates.
SELECT CustId, SubCustId FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
"Jean-Francois" wrote:
> OK, I create a table called Customer, I imported date from my as400, when i
> want to set a primary key it telling me that i have duplicate key.
> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> have more then one same CustId, but SubCustID have not the same SubCustID.
> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> can be the same but the sub is always different. Could anyone help me on that
> problem I have no more Idea.
> Thanks!
> JF|||I did
SELECT CustId, SubCustId FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
But it give me all my data. That seem to be that all my data is duplicate.
I try to import the data before put the primary key and after put the
primary key and get the same result.
"Tibor Karaszi" wrote:
> Seem like the primary key should be the combination of CustId and SubCustId.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > OK, I create a table called Customer, I imported date from my as400, when i
> > want to set a primary key it telling me that i have duplicate key.
> > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > have more then one same CustId, but SubCustID have not the same SubCustID.
> >
> > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > can be the same but the sub is always different. Could anyone help me on that
> > problem I have no more Idea.
> >
> > Thanks!
> > JF
>
>|||How many times have you try to import the data into SQL table? Did you
delete all records in the table before you import the data if you did it
multiple times?
"Jean-Francois" wrote:
> I did
> SELECT CustId, SubCustId FROM table
> GROUP BY CustId, SubCustId
> HAVING COUNT(*) > 1
> But it give me all my data. That seem to be that all my data is duplicate.
> I try to import the data before put the primary key and after put the
> primary key and get the same result.
>
> "Tibor Karaszi" wrote:
> > Seem like the primary key should be the combination of CustId and SubCustId.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > http://www.sqlug.se/
> >
> >
> > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > OK, I create a table called Customer, I imported date from my as400, when i
> > > want to set a primary key it telling me that i have duplicate key.
> > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > >
> > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > can be the same but the sub is always different. Could anyone help me on that
> > > problem I have no more Idea.
> > >
> > > Thanks!
> > > JF
> >
> >
> >|||I first delete the table and start over every time.
"Jack" wrote:
> How many times have you try to import the data into SQL table? Did you
> delete all records in the table before you import the data if you did it
> multiple times?
> "Jean-Francois" wrote:
> > I did
> > SELECT CustId, SubCustId FROM table
> > GROUP BY CustId, SubCustId
> > HAVING COUNT(*) > 1
> >
> > But it give me all my data. That seem to be that all my data is duplicate.
> > I try to import the data before put the primary key and after put the
> > primary key and get the same result.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> > > Seem like the primary key should be the combination of CustId and SubCustId.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > > http://www.sqlug.se/
> > >
> > >
> > > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > > OK, I create a table called Customer, I imported date from my as400, when i
> > > > want to set a primary key it telling me that i have duplicate key.
> > > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > > >
> > > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > > can be the same but the sub is always different. Could anyone help me on that
> > > > problem I have no more Idea.
> > > >
> > > > Thanks!
> > > > JF
> > >
> > >
> > >|||Does the total row returned from the "check duplicate" query match SELECT
COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file?
DTS?
"Jean-Francois" wrote:
> I first delete the table and start over every time.
> "Jack" wrote:
> > How many times have you try to import the data into SQL table? Did you
> > delete all records in the table before you import the data if you did it
> > multiple times?
> >
> > "Jean-Francois" wrote:
> >
> > > I did
> > > SELECT CustId, SubCustId FROM table
> > > GROUP BY CustId, SubCustId
> > > HAVING COUNT(*) > 1
> > >
> > > But it give me all my data. That seem to be that all my data is duplicate.
> > > I try to import the data before put the primary key and after put the
> > > primary key and get the same result.
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > > Seem like the primary key should be the combination of CustId and SubCustId.
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > > http://www.sqlug.se/
> > > >
> > > >
> > > > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > > > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > > > OK, I create a table called Customer, I imported date from my as400, when i
> > > > > want to set a primary key it telling me that i have duplicate key.
> > > > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > > > >
> > > > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > > > can be the same but the sub is always different. Could anyone help me on that
> > > > > problem I have no more Idea.
> > > > >
> > > > > Thanks!
> > > > > JF
> > > >
> > > >
> > > >|||I did an connection with client access driver to connect to the as400 and a
query the as400 to retreive the data.
"Jack" wrote:
> Does the total row returned from the "check duplicate" query match SELECT
> COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file?
> DTS?
> "Jean-Francois" wrote:
> > I first delete the table and start over every time.
> >
> > "Jack" wrote:
> >
> > > How many times have you try to import the data into SQL table? Did you
> > > delete all records in the table before you import the data if you did it
> > > multiple times?
> > >
> > > "Jean-Francois" wrote:
> > >
> > > > I did
> > > > SELECT CustId, SubCustId FROM table
> > > > GROUP BY CustId, SubCustId
> > > > HAVING COUNT(*) > 1
> > > >
> > > > But it give me all my data. That seem to be that all my data is duplicate.
> > > > I try to import the data before put the primary key and after put the
> > > > primary key and get the same result.
> > > >
> > > >
> > > > "Tibor Karaszi" wrote:
> > > >
> > > > > Seem like the primary key should be the combination of CustId and SubCustId.
> > > > >
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > http://www.solidqualitylearning.com/
> > > > > http://www.sqlug.se/
> > > > >
> > > > >
> > > > > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > > > > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > > > > OK, I create a table called Customer, I imported date from my as400, when i
> > > > > > want to set a primary key it telling me that i have duplicate key.
> > > > > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > > > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > > > > >
> > > > > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > > > > can be the same but the sub is always different. Could anyone help me on that
> > > > > > problem I have no more Idea.
> > > > > >
> > > > > > Thanks!
> > > > > > JF
> > > > >
> > > > >
> > > > >|||1> Bring the data over with primary defined in SQL table.
2> After the import, issue "ALTER TABLE table ADD id INT IDENTITY (1, 1).
This will create a primary key for this table.
3> Use this command to delete duplicates:
DELETE FROM table
WHERE id NOT IN (
SELECT MAX(id) FROM table
GROUP BY CustId, SubCustId)
4> Remove "id" column and set your primary key using CustId and SubCustId
Then you would need to compare the data between SQL and AS400.........
"Jean-Francois" wrote:
> I did an connection with client access driver to connect to the as400 and a
> query the as400 to retreive the data.
> "Jack" wrote:
> > Does the total row returned from the "check duplicate" query match SELECT
> > COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file?
> > DTS?
> >
> > "Jean-Francois" wrote:
> >
> > > I first delete the table and start over every time.
> > >
> > > "Jack" wrote:
> > >
> > > > How many times have you try to import the data into SQL table? Did you
> > > > delete all records in the table before you import the data if you did it
> > > > multiple times?
> > > >
> > > > "Jean-Francois" wrote:
> > > >
> > > > > I did
> > > > > SELECT CustId, SubCustId FROM table
> > > > > GROUP BY CustId, SubCustId
> > > > > HAVING COUNT(*) > 1
> > > > >
> > > > > But it give me all my data. That seem to be that all my data is duplicate.
> > > > > I try to import the data before put the primary key and after put the
> > > > > primary key and get the same result.
> > > > >
> > > > >
> > > > > "Tibor Karaszi" wrote:
> > > > >
> > > > > > Seem like the primary key should be the combination of CustId and SubCustId.
> > > > > >
> > > > > > --
> > > > > > Tibor Karaszi, SQL Server MVP
> > > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > > http://www.solidqualitylearning.com/
> > > > > > http://www.sqlug.se/
> > > > > >
> > > > > >
> > > > > > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > > > > > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > > > > > OK, I create a table called Customer, I imported date from my as400, when i
> > > > > > > want to set a primary key it telling me that i have duplicate key.
> > > > > > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > > > > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > > > > > >
> > > > > > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > > > > > can be the same but the sub is always different. Could anyone help me on that
> > > > > > > problem I have no more Idea.
> > > > > > >
> > > > > > > Thanks!
> > > > > > > JF
> > > > > >
> > > > > >
> > > > > >|||Add a Count(*) to the result set to see how many duplicates there are..
Then pick one and look to see whats up with the rows.
SELECT CustId, SubCustId,Count(*)
FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
Bill
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:8DE0E7B0-7EF9-472E-B8F7-CF53F4F1A25A@.microsoft.com...
> Is the primary key defined on both columns? If it is, try to import the
> data
> over without the primary key, then check for duplicates.
> SELECT CustId, SubCustId FROM table
> GROUP BY CustId, SubCustId
> HAVING COUNT(*) > 1
> "Jean-Francois" wrote:
>> OK, I create a table called Customer, I imported date from my as400, when
>> i
>> want to set a primary key it telling me that i have duplicate key.
>> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
>> have more then one same CustId, but SubCustID have not the same
>> SubCustID.
>> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
>> can be the same but the sub is always different. Could anyone help me on
>> that
>> problem I have no more Idea.
>> Thanks!
>> JF

Primary Key Problem

Wednesday, March 7, 2012

Previous YTD

Hi all

I am trying to perform Pervious Year To Date Total (which is like YTD but for the previous year. ie. ytd would work for this year's jan -> whatever month. But I need a total from Last year from jan -> whatever year to do comparision), However I've ran into the following issues.

Since Year is a variable and it can change depending on which year you select. I tried to Return the first member of the month which in theory should be January of a Year. However when I performed the below query I got Dec which was the overall period through out the entire Month this include 2002 dec -> 2005 May period so the head returned Dec.

SELECT { head(Descendants(
[Period].CurrentMember,
[Month]),1)} ON COLUMNS , { [BrandModel].[All BrandModel] } ON ROWS FROM [Running Report] WHERE ( [Measures].[Unit] )
So then I tried to return the Head of the Set of the current year using the below query. But no matter what I do I can not get it working.

SELECT { head(Descendants(
{[Period].CurrentMember, [Period].[Month]},
[Month]),1)} ON COLUMNS , { [BrandModel].[All BrandModel] } ON ROWS FROM [Running Report] WHERE ( [Measures].[Unit] )

How would you guys usually do a previous year to date comparision ? since year is a variable

Thanks
Tom

This isn't clear. Can you be more explicit?|||hi vector

I think you've answered my question on the other thread. I will test this when I get into office first thing in the morning.

thanks alot !!

previous week date parameters

Hope someone can help.
What i am trying to achieve is to bring in results from the previous Monday
to Friday into my report through the default paramters.
This would mean that any day this week that that i run the report, it would
bring in results for Monday to Friday last week.
Does anybody know of a way to achieve this using the default parameters.
ThanksThanks for your reply.
The code works well. It brings in the correct dates i.e. Monday to Friday
last week but the time comes in as the current time i.e. whenever the report
was run. Is there any way for it to ignore the time.
Thanks again
"Ameet" wrote:
> Try This
> Put this code in the Code Tab of Report Properties...
> ---
> Public Shared Function GetDate(Byval value as Double)
> value=(value)- Weekday(Now)
> GetDate=DateAdd("d", value, Now)
> End Function
> ----
> Now
> Create Two Parameters named 'StartDate' and 'EndDate'
> Change Datatype to DateTime
> Select 'Non-Queried' in Available Values Option and leave it blank
> Go to Default Values and select Non-Queried...
> for 'StartDate' Parameter put this value
> ---
> =Code.GetDate(-5)
> ---
> and for 'EndDate' Parameter use this
> ---
> =Code.GetDate(-1)
> ---
> I guess, this will solve your problem. Play around with the code and see if
> its possible using one parameter.. I am bit busy today...
>
> "pcalv" wrote:
> > Hope someone can help.
> >
> > What i am trying to achieve is to bring in results from the previous Monday
> > to Friday into my report through the default paramters.
> >
> > This would mean that any day this week that that i run the report, it would
> > bring in results for Monday to Friday last week.
> >
> > Does anybody know of a way to achieve this using the default parameters.
> >
> > Thanks|||I've not got any code example, but I've got an idea that might work.
Use code to find out what day of the week today is. Then subtract the
appropriate number of days from today to get last Monday. You would have
only 7 possibilities. For example, if today was Tuesday you would subtract
8, if today is Wednesday you would subtract 9. That would be your begin
date for the report, then add 5 to get your end date.
Hope this helps. Maybe?
"pcalv" <pcalv@.discussions.microsoft.com> wrote in message
news:E07BC00D-D05C-4E86-9462-252AEB95A3B1@.microsoft.com...
> I still haven't found a solution to this so if anyone could help out that
> would be great.
> Thanks Paul
>
> "Ameet" wrote:
> > Well I dont think you can go with only date having parameter data type
to
> > datetime. I am not sure though.. I would appreciate if someone could
help me
> > here..
> >
> > workaround is if you could use string datatype... change parameter
> > datatype to string .. and then add this line to the code function
> > ---
> > GetDate=FormatDateTime(GetDate,vbShortDate)
> > ---
> >
> > Actually it gave me error with parameter datatype to datetime.
> >
> > "REPORT PARAMETER DOES NOT HAVE THE EXPECTED VALUE"
> >
> > soo...this is what i could find out..
> >
> > anyhelp on this .. more than appreciated..
> >
> > "pcalv" wrote:
> >
> > > Thanks for your reply.
> > >
> > > The code works well. It brings in the correct dates i.e. Monday to
Friday
> > > last week but the time comes in as the current time i.e. whenever the
report
> > > was run. Is there any way for it to ignore the time.
> > >
> > > Thanks again
> > >
> > >
> > > "Ameet" wrote:
> > >
> > > > Try This
> > > >
> > > > Put this code in the Code Tab of Report Properties...
> > > > ---
> > > > Public Shared Function GetDate(Byval value as Double)
> > > > value=(value)- Weekday(Now)
> > > > GetDate=DateAdd("d", value, Now)
> > > > End Function
> > >
> ----
> > > >
> > > > Now
> > > >
> > > > Create Two Parameters named 'StartDate' and 'EndDate'
> > > >
> > > > Change Datatype to DateTime
> > > >
> > > > Select 'Non-Queried' in Available Values Option and leave it blank
> > > >
> > > > Go to Default Values and select Non-Queried...
> > > >
> > > > for 'StartDate' Parameter put this value
> > > > ---
> > > > =Code.GetDate(-5)
> > > > ---
> > > >
> > > > and for 'EndDate' Parameter use this
> > > > ---
> > > > =Code.GetDate(-1)
> > > > ---
> > > >
> > > > I guess, this will solve your problem. Play around with the code and
see if
> > > > its possible using one parameter.. I am bit busy today...
> > > >
> > > >
> > > > "pcalv" wrote:
> > > >
> > > > > Hope someone can help.
> > > > >
> > > > > What i am trying to achieve is to bring in results from the
previous Monday
> > > > > to Friday into my report through the default paramters.
> > > > >
> > > > > This would mean that any day this week that that i run the report,
it would
> > > > > bring in results for Monday to Friday last week.
> > > > >
> > > > > Does anybody know of a way to achieve this using the default
parameters.
> > > > >
> > > > > Thanks|||You can get the previous week's date using this expression:
=DateAdd(DateInterval.WeekOfYear, -1, Fields!Date.Value). From there, you
can derive the first and last day of the week. Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbadev/html/findingbeginningorendofweek.asp
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"pcalv" <pcalv@.discussions.microsoft.com> wrote in message
news:E07BC00D-D05C-4E86-9462-252AEB95A3B1@.microsoft.com...
> I still haven't found a solution to this so if anyone could help out that
> would be great.
> Thanks Paul
>
> "Ameet" wrote:
> > Well I dont think you can go with only date having parameter data type
to
> > datetime. I am not sure though.. I would appreciate if someone could
help me
> > here..
> >
> > workaround is if you could use string datatype... change parameter
> > datatype to string .. and then add this line to the code function
> > ---
> > GetDate=FormatDateTime(GetDate,vbShortDate)
> > ---
> >
> > Actually it gave me error with parameter datatype to datetime.
> >
> > "REPORT PARAMETER DOES NOT HAVE THE EXPECTED VALUE"
> >
> > soo...this is what i could find out..
> >
> > anyhelp on this .. more than appreciated..
> >
> > "pcalv" wrote:
> >
> > > Thanks for your reply.
> > >
> > > The code works well. It brings in the correct dates i.e. Monday to
Friday
> > > last week but the time comes in as the current time i.e. whenever the
report
> > > was run. Is there any way for it to ignore the time.
> > >
> > > Thanks again
> > >
> > >
> > > "Ameet" wrote:
> > >
> > > > Try This
> > > >
> > > > Put this code in the Code Tab of Report Properties...
> > > > ---
> > > > Public Shared Function GetDate(Byval value as Double)
> > > > value=(value)- Weekday(Now)
> > > > GetDate=DateAdd("d", value, Now)
> > > > End Function
> > >
> ----
> > > >
> > > > Now
> > > >
> > > > Create Two Parameters named 'StartDate' and 'EndDate'
> > > >
> > > > Change Datatype to DateTime
> > > >
> > > > Select 'Non-Queried' in Available Values Option and leave it blank
> > > >
> > > > Go to Default Values and select Non-Queried...
> > > >
> > > > for 'StartDate' Parameter put this value
> > > > ---
> > > > =Code.GetDate(-5)
> > > > ---
> > > >
> > > > and for 'EndDate' Parameter use this
> > > > ---
> > > > =Code.GetDate(-1)
> > > > ---
> > > >
> > > > I guess, this will solve your problem. Play around with the code and
see if
> > > > its possible using one parameter.. I am bit busy today...
> > > >
> > > >
> > > > "pcalv" wrote:
> > > >
> > > > > Hope someone can help.
> > > > >
> > > > > What i am trying to achieve is to bring in results from the
previous Monday
> > > > > to Friday into my report through the default paramters.
> > > > >
> > > > > This would mean that any day this week that that i run the report,
it would
> > > > > bring in results for Monday to Friday last week.
> > > > >
> > > > > Does anybody know of a way to achieve this using the default
parameters.
> > > > >
> > > > > Thanks

previous week date parameters

Hi,
Hope someone can help. I am trying to get the default value date parameters
for the previous working week. I then want to use these values while running
the report during the following week until the next Monday. I have almost got
it working with the following:
I put the following code in 'Report Properties>Code':
Public Shared Function GetDate(Byval value as Double)
value=(value)- Weekday(Now)
GetDate=DateAdd("d", value, Now)
End Function
and then the following parameters:
Date from: =Code.GetDate(-5)
Date to: =Code.GetDate(-1)
The only issue i have with this is that it brings in the following:
Date from: 08/11/2004 14:40:54
Date to: 12/11/2004 14:40:54
I don't want it to include the time though. Just the following:
Date from: 08/11/2004 00:00:00
Date to: 12/11/2004 00:00:00
Does anybody know how to get around this?
Any help would be appreciated.
PaulTo convert the date I use .ToShortDateString()
Example:
=Today.AddDays(-1).ToShortDateString()
"pcalv" wrote:
> Hi,
> Hope someone can help. I am trying to get the default value date parameters
> for the previous working week. I then want to use these values while running
> the report during the following week until the next Monday. I have almost got
> it working with the following:
> I put the following code in 'Report Properties>Code':
> Public Shared Function GetDate(Byval value as Double)
> value=(value)- Weekday(Now)
> GetDate=DateAdd("d", value, Now)
> End Function
> and then the following parameters:
> Date from: =Code.GetDate(-5)
> Date to: =Code.GetDate(-1)
> The only issue i have with this is that it brings in the following:
> Date from: 08/11/2004 14:40:54
> Date to: 12/11/2004 14:40:54
> I don't want it to include the time though. Just the following:
> Date from: 08/11/2004 00:00:00
> Date to: 12/11/2004 00:00:00
> Does anybody know how to get around this?
> Any help would be appreciated.
> Paul
>|||Thanks for the reply. Unfortunately this does not work with the custom code i
am using.
Any other help would be appreciated.
Paul
"datobin1" wrote:
> To convert the date I use .ToShortDateString()
> Example:
> =Today.AddDays(-1).ToShortDateString()
>
> "pcalv" wrote:
> > Hi,
> >
> > Hope someone can help. I am trying to get the default value date parameters
> > for the previous working week. I then want to use these values while running
> > the report during the following week until the next Monday. I have almost got
> > it working with the following:
> >
> > I put the following code in 'Report Properties>Code':
> >
> > Public Shared Function GetDate(Byval value as Double)
> > value=(value)- Weekday(Now)
> > GetDate=DateAdd("d", value, Now)
> > End Function
> >
> > and then the following parameters:
> >
> > Date from: =Code.GetDate(-5)
> > Date to: =Code.GetDate(-1)
> >
> > The only issue i have with this is that it brings in the following:
> >
> > Date from: 08/11/2004 14:40:54
> > Date to: 12/11/2004 14:40:54
> >
> > I don't want it to include the time though. Just the following:
> >
> > Date from: 08/11/2004 00:00:00
> > Date to: 12/11/2004 00:00:00
> >
> > Does anybody know how to get around this?
> >
> > Any help would be appreciated.
> >
> > Paul
> >
> >

Previous Month To Date Calculation

I am currenty attempting to add a calculation to get previous month to date calculation. I currently have the following calculation in to calculate previous Year to Date and am having trouble adapting it. Any help is appreciated. Please note I am fairly new at this.

Previous Year To Date:

([Time Calculations].[YTD Pr Yr]=
Aggregate(
Crossjoin({[Calendar Year].[Current Period]},
PeriodsToDate(
[Time].[Calendar Year].[Year],
ParallelPeriod(
[Time].[Calendar Year].[Year],1,
[Time].[Calendar Year].CurrentMember)))
)
);

Adam

Dear Friend,

Check this example:

MTD:

Code Snippet

SUM(PeriodsToDate([DimTime].[Hierarquia].[Month],
[DimTime].[Hierarquia].CurrentMember),
[Measures].[NC_ValorCarteira])

Gets Previous Member

Code Snippet

IIF(IsEMPTY(([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))
,0,([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))

Helped?

Regards!

|||That is not quite what I am looking for. I can calculate current month to date just fine, but what I am attempting to calcuate is month to date for the previous year. So from Aug 1, 2006 - Aug 21 2006. Given the nature of our business, know growth from this time last year is essential. This is what I am attempting to use and it returns the entire aggregate of the previous years period. Any help would be greatly appreciated.

Aggregate
(
PeriodsToDate(
[Time].[Year - Month - Week - Day of Week].[Month],
ParallelPeriod(
[Time].[Year - Month - Week - Day of Week].[Month],12,
[Time].[Year - Month - Week - Day of Week].CurrentMember)
),
[Measures].[Orders]
)

Adam|||

Hi Adam,

I'm not sure about the structure of your [Time] dimension, so here's a sample Adventure Works query:

Code Snippet

With

Member [Measures].[MTDSales] as

Aggregate(PeriodsToDate([Date].[Calendar].[Month]),

[Measures].[Sales Amount]),

FORMAT_STRING = 'Currency'

Member [Measures].[MTDSales-PY] as

([Measures].[MTDSales],

ParallelPeriod([Date].[Calendar].[Calendar Year])),

FORMAT_STRING = 'Currency'

select

{[Measures].[Sales Amount], [Measures].[MTDSales],

[Measures].[MTDSales-PY]} on 0,

Non Empty

{[Date].[Calendar].[Month].&[2003]&[7].Children,

[Date].[Calendar].[Month].&[2004]&[7].Children} on 1

from [Adventure Works]

|||Using that structure, I am still getting all of last years numbers. The ideal goal is to compare the current months MTD with the corresponding MTD of last year to evaluate growth. It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.
|||

AdamAtAirNWater wrote:

It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.

But that's how I thought the sample Adventure Works query worked. For example, compare these 2 result rows:

...

July 15, 2003 $30,792.07 $3,103,364.27 $2,642,983.51

...

July 15, 2004 $1,379.50 $23,234.19 $3,103,364.27

The [MTDSales-PY] for July 15, 2004 is $3,103,364.27, which is identical to [MTDSales] for July 15, 2003 (the total for all days of July, 2003 is $3,552,319.38). To better understand your issue, could you point out specific examples in the sample query results?

|||Perhaps I made an error in the way I adapted it into my calculation. I am attempting to put this into a calculation in a cube.

How would I translate that into an expression for a cube?

Adam
|||This is what I am currently using in my cube to calculate MTD and the Prior MTD. As I said, it is returning the entire value for the previous MTD

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month]),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year])),
FORMAT_STRING = "#",
VISIBLE = 1;

|||

Well, the expressions look similar to the Adventure Works sample, so I'm wondering whether there's an issue with the [Time].[Year - Month - Dayof Month] hierarchy not being natural. In any case, it's worth trying the full forms of PeriodsToDate() and ParallelPeriod(), like:

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month],

[Time].[Year - Month - Dayof Month].CurrentMember),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year], 1,

[Time].[Year - Month - Dayof Month].CurrentMember)),
FORMAT_STRING = "#",
VISIBLE = 1;

|||I am not entirely sure what you mean by the hierarchy not being natural. However, I am assuming this is the issue as it is still not working as intended. I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent." I am not entirely sure what to do to fix that however.

Adam
|||

AdamAtAirNWater wrote:

I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent."

This indicates that the [Year - Month - Dayof Month] hierarchy is not natural:

SQL Server 2005 Books Online

Attribute Relationships

...

Natural Hierarchy Relationships

A hierarchy is a natural hierarchy when each attribute included in the user-defined hierarchy has a one to many relationship with the attribute immediately below it.

...

Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it.

...

My guess is that the "Month" and/or 'DayOfMonth" attributes in the hierarchy are not unique across higher levels of the hierarchy. For example, if 'DayOfMonth" was like 1, 2, etc, the same member could appear under multiple months. So it should be qualified (could be by month and year) to make it unique. If you study how the [Date] dimension and Fiscal hierarchy in Adventure Works are designed, it will become clearer.

|||I have fixed the hierarchy issues and have even have the calculation almost exactly where I want it. If I use:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then its gets me the correct information. The only question I now have is there anyway that I can see this number at the month level of the hierarchy. When view my data in the cube, I have to drill down to the day and it give me the correct month to date on that sepecific day, but ideally I don't want to have to go that deep in order to see it. Is that possible?

Thanks again for all the help. It is greatly apprieciated and I have learned a lot.

Adam
|||But which date should be selected for browsing MTD at the month level - is it the last day of the month with data? I'm guessing that most months earlier than the current one have data for all days, so in those cases it will be the total for the month.|||I would want to show the last date with data of the current month for the previous years month.

Adam
|||

One way to do that would be to add a dedicated measure - which would work at any level. So if [MTD-PY] is defined as you indicated above:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then [LatestMTD-PY] could be like:

Code Snippet

([Measures].[MTD-PY],

Tail(NonEmpty([Time].[Year - Month - Date].[Simple Date],

{[Measures].[Orders]})).Item(0))

Previous Month To Date Calculation

Previous Year To Date:

Adam

Dear Friend,

Check this example:

MTD:

Code Snippet

SUM(PeriodsToDate([DimTime].[Hierarquia].[Month],
[DimTime].[Hierarquia].CurrentMember),
[Measures].[NC_ValorCarteira])

Gets Previous Member

Code Snippet

IIF(IsEMPTY(([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))
,0,([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))

Helped?

Regards!

Adam|||

Hi Adam,

I'm not sure about the structure of your [Time] dimension, so here's a sample Adventure Works query:

Code Snippet

With

Member [Measures].[MTDSales] as

Aggregate(PeriodsToDate([Date].[Calendar].[Month]),

[Measures].[Sales Amount]),

FORMAT_STRING = 'Currency'

Member [Measures].[MTDSales-PY] as

([Measures].[MTDSales],

ParallelPeriod([Date].[Calendar].[Calendar Year])),

FORMAT_STRING = 'Currency'

select

{[Measures].[Sales Amount], [Measures].[MTDSales],

[Measures].[MTDSales-PY]} on 0,

Non Empty

{[Date].[Calendar].[Month].&[2003]&[7].Children,

[Date].[Calendar].[Month].&[2004]&[7].Children} on 1

from [Adventure Works]

AdamAtAirNWater wrote:

It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.

But that's how I thought the sample Adventure Works query worked. For example, compare these 2 result rows:

...

July 15, 2003 $30,792.07 $3,103,364.27 $2,642,983.51

...

July 15, 2004 $1,379.50 $23,234.19 $3,103,364.27

|||Perhaps I made an error in the way I adapted it into my calculation. I am attempting to put this into a calculation in a cube.

How would I translate that into an expression for a cube?

Adam
|||This is what I am currently using in my cube to calculate MTD and the Prior MTD. As I said, it is returning the entire value for the previous MTD

Code Snippet

|||

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month],

[Time].[Year - Month - Dayof Month].CurrentMember),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year], 1,

[Time].[Year - Month - Dayof Month].CurrentMember)),
FORMAT_STRING = "#",
VISIBLE = 1;

Adam
|||

AdamAtAirNWater wrote:

I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent."

This indicates that the [Year - Month - Dayof Month] hierarchy is not natural:

SQL Server 2005 Books Online

Attribute Relationships

...

Natural Hierarchy Relationships

A hierarchy is a natural hierarchy when each attribute included in the user-defined hierarchy has a one to many relationship with the attribute immediately below it.

...

Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it.

...

|||I have fixed the hierarchy issues and have even have the calculation almost exactly where I want it. If I use:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

Thanks again for all the help. It is greatly apprieciated and I have learned a lot.

Adam
|||

One way to do that would be to add a dedicated measure - which would work at any level. So if [MTD-PY] is defined as you indicated above:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then [LatestMTD-PY] could be like:

Code Snippet

([Measures].[MTD-PY],

Tail(NonEmpty([Time].[Year - Month - Date].[Simple Date],

{[Measures].[Orders]})).Item(0))

Previous Month To Date Calculation

Previous Year To Date:

Adam

Dear Friend,

Check this example:

MTD:

Code Snippet

SUM(PeriodsToDate([DimTime].[Hierarquia].[Month],
[DimTime].[Hierarquia].CurrentMember),
[Measures].[NC_ValorCarteira])

Gets Previous Member

Code Snippet

IIF(IsEMPTY(([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))
,0,([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))

Helped?

Regards!

Adam|||

Hi Adam,

I'm not sure about the structure of your [Time] dimension, so here's a sample Adventure Works query:

Code Snippet

With

Member [Measures].[MTDSales] as

Aggregate(PeriodsToDate([Date].[Calendar].[Month]),

[Measures].[Sales Amount]),

FORMAT_STRING = 'Currency'

Member [Measures].[MTDSales-PY] as

([Measures].[MTDSales],

ParallelPeriod([Date].[Calendar].[Calendar Year])),

FORMAT_STRING = 'Currency'

select

{[Measures].[Sales Amount], [Measures].[MTDSales],

[Measures].[MTDSales-PY]} on 0,

Non Empty

{[Date].[Calendar].[Month].&[2003]&[7].Children,

[Date].[Calendar].[Month].&[2004]&[7].Children} on 1

from [Adventure Works]

AdamAtAirNWater wrote:

It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.

But that's how I thought the sample Adventure Works query worked. For example, compare these 2 result rows:

...

July 15, 2003 $30,792.07 $3,103,364.27 $2,642,983.51

...

July 15, 2004 $1,379.50 $23,234.19 $3,103,364.27

|||Perhaps I made an error in the way I adapted it into my calculation. I am attempting to put this into a calculation in a cube.

How would I translate that into an expression for a cube?

Adam
|||This is what I am currently using in my cube to calculate MTD and the Prior MTD. As I said, it is returning the entire value for the previous MTD

Code Snippet

|||

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month],

[Time].[Year - Month - Dayof Month].CurrentMember),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year], 1,

[Time].[Year - Month - Dayof Month].CurrentMember)),
FORMAT_STRING = "#",
VISIBLE = 1;

Adam
|||

AdamAtAirNWater wrote:

I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent."

This indicates that the [Year - Month - Dayof Month] hierarchy is not natural:

SQL Server 2005 Books Online

Attribute Relationships

...

Natural Hierarchy Relationships

A hierarchy is a natural hierarchy when each attribute included in the user-defined hierarchy has a one to many relationship with the attribute immediately below it.

...

Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it.

...

|||I have fixed the hierarchy issues and have even have the calculation almost exactly where I want it. If I use:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

Thanks again for all the help. It is greatly apprieciated and I have learned a lot.

Adam
|||

One way to do that would be to add a dedicated measure - which would work at any level. So if [MTD-PY] is defined as you indicated above:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then [LatestMTD-PY] could be like:

Code Snippet

([Measures].[MTD-PY],

Tail(NonEmpty([Time].[Year - Month - Date].[Simple Date],

{[Measures].[Orders]})).Item(0))

Saturday, February 25, 2012

Previous Month To Date Calculation

Previous Year To Date:

Adam

Dear Friend,

Check this example:

MTD:

Code Snippet

SUM(PeriodsToDate([DimTime].[Hierarquia].[Month],
[DimTime].[Hierarquia].CurrentMember),
[Measures].[NC_ValorCarteira])

Gets Previous Member

Code Snippet

IIF(IsEMPTY(([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))
,0,([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))

Helped?

Regards!

Adam|||

Hi Adam,

I'm not sure about the structure of your [Time] dimension, so here's a sample Adventure Works query:

Code Snippet

With

Member [Measures].[MTDSales] as

Aggregate(PeriodsToDate([Date].[Calendar].[Month]),

[Measures].[Sales Amount]),

FORMAT_STRING = 'Currency'

Member [Measures].[MTDSales-PY] as

([Measures].[MTDSales],

ParallelPeriod([Date].[Calendar].[Calendar Year])),

FORMAT_STRING = 'Currency'

select

{[Measures].[Sales Amount], [Measures].[MTDSales],

[Measures].[MTDSales-PY]} on 0,

Non Empty

{[Date].[Calendar].[Month].&[2003]&[7].Children,

[Date].[Calendar].[Month].&[2004]&[7].Children} on 1

from [Adventure Works]

AdamAtAirNWater wrote:

It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.

But that's how I thought the sample Adventure Works query worked. For example, compare these 2 result rows:

...

July 15, 2003 $30,792.07 $3,103,364.27 $2,642,983.51

...

July 15, 2004 $1,379.50 $23,234.19 $3,103,364.27

|||Perhaps I made an error in the way I adapted it into my calculation. I am attempting to put this into a calculation in a cube.

How would I translate that into an expression for a cube?

Adam
|||This is what I am currently using in my cube to calculate MTD and the Prior MTD. As I said, it is returning the entire value for the previous MTD

Code Snippet

|||

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month],

[Time].[Year - Month - Dayof Month].CurrentMember),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year], 1,

[Time].[Year - Month - Dayof Month].CurrentMember)),
FORMAT_STRING = "#",
VISIBLE = 1;

Adam
|||

AdamAtAirNWater wrote:

I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent."

This indicates that the [Year - Month - Dayof Month] hierarchy is not natural:

SQL Server 2005 Books Online

Attribute Relationships

...

Natural Hierarchy Relationships

A hierarchy is a natural hierarchy when each attribute included in the user-defined hierarchy has a one to many relationship with the attribute immediately below it.

...

Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it.

...

|||I have fixed the hierarchy issues and have even have the calculation almost exactly where I want it. If I use:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

Thanks again for all the help. It is greatly apprieciated and I have learned a lot.

Adam
|||

One way to do that would be to add a dedicated measure - which would work at any level. So if [MTD-PY] is defined as you indicated above:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then [LatestMTD-PY] could be like:

Code Snippet

([Measures].[MTD-PY],

Tail(NonEmpty([Time].[Year - Month - Date].[Simple Date],

{[Measures].[Orders]})).Item(0))

Wednesday, March 28, 2012

Print Date and Time

Friday, March 23, 2012

Primary Key with a datetime datatype

Primary Key with a datetime datatype

Wednesday, March 21, 2012

Primary Key Problem

Primary Key Problem

Primary Key Problem

Wednesday, March 7, 2012

Previous YTD

previous week date parameters

previous week date parameters

Previous Month To Date Calculation

Previous Month To Date Calculation

Previous Month To Date Calculation

Saturday, February 25, 2012

Previous Month To Date Calculation

Preventing Deadlock

Blog Archive

About Me