Find SQL solutions with free eBooks. Search the ebooks NOW.

XML and SQL: Developing Web Applications
http://www.ebook5.com/readingeBook5...ageno
=2
SQL Performance Tuning
http://www.ebook5.com/ebook5details...=1&subcatid=136
Microsoft SQL Server 2000: A Guide to Enhancements and New Features
http://www.ebook5.com/readingeBook5...ageno
=5
and much more!Are these true? Does anybody know?
--
I like solving problems, and making friends. If this post helpful to you,
please click Yes.
"MCAD Poster" wrote:

> XML and SQL: Developing Web Applications
> http://www.ebook5.com/readingeBook5...age
no=2
> SQL Performance Tuning
> http://www.ebook5.com/ebook5details...age
no=5
> and much more!
>

Financial Periods YTD & Date Ranges

I am developing a set of reports for a site based on AS2000.

The reports will have columns for each finance period in the current year from period 01 to the current reporting period from the Primary Time dimension.

i.e. If period 4 is the default period, The report should show columns for periods 1,2,3,4 with a total that sums the four columns.

When the default period 5 the columns should be 1,2,3,4,5.

I cannot use the current year and explode that to show all periods in the year, as there are postings in future periods beyond the current accounting period that should not be displayed on the report.

I am setting the default member of the time dimension to the current reporting period. (2005/08 for example)
I am trying to use a named set based on an MDX expression to set the column range for the report.

The first test I tried was to set the MDX for the period range to some fixed values
{[Primary Time].[ACCSCAL].&[200501]:[Primary Time].[ACCSCAL].&[200508]}
This gave the expected result showing columns 01/2005 to 08/2005

I then tried changing the last period to use the default member of the dimension
{[Primary Time].[ACCSCAL].&[200501]:[Primary Time].[ACCSCAL].DefaultMember}
This again gave the expected result range and I could change the default member on the dimension and see the columns on the report update correctly.

I then tried testing that the first period can be found correctly
{[Primary Time].[ACCSCAL].&[200508].FirstSibling:[Primary Time].[ACCSCAL].&[200508]}
This again gave the expected result range from 01/2005 to 08/2005

I then tried combining the default member and first sibling calculations
{[Primary Time].[ACCSCAL].DefaultMember.FirstSibling:[Primary Time].[ACCSCAL].DefaultMember}
This gave a single column "All Primary Time", this was not what I was expecting.

So I tried changing the mdx to test the value of the first sibling of the default member, expecting 01/2005
{[Primary Time].[ACCSCAL].DefaultMember.FirstSibling}
This again gave a single column "All Primary Time".

So I then tried changing the mdx to test that a single period would work in the named set
{[Primary Time].[ACCSCAL].DefaultMember}
This again gave a single column "All Primary Time".

So I tried changing the mdx to test the value of the default member, expecting 08/2005
{[Primary Time].[ACCSCAL].&[200508]}
This again gave a single column for 08/2005 as expected

By now this has me totally confused!

Do you have any idea why the default member works when used as the second part of the range but does not work when used on its own or as the first element of the range?

Is this the correct way of achieving the report I am aiming for or is there a better method to use?

Any suggestions you have would be welcome.

Apologies is this is a FAQ.

What is the actual MDX expression for the Default Member of [Primary Time].[ACCSCAL]?|||The Default Member is [Primary Time].[ACCSCAL].&[200508]

Filters & Parameters pane missing in MDX Query Designer

I'm developing my first Analysis Server report in SRS. Everything was going well when I discovered the filters & parameters pane was missing in the MDX Query Designer in data mode. I have my columns and rows but need to drag a dimension into the filters & parameters pane in MDX Query Designer and set a filter and parameter on it. Any idea how to make it viewable?

I may have made a discovery why I am not seeing the filter and parameter pane in the MDX Query Designer in SQL Server Business Intelligence Development Studio.The filter and parameter pane does appear in the MDX Query Designer in SQL Server Business Intelligence Development Studio on a report created from a SQL Server 2005 database, but not on a SQL Server 2000 database. Am I on to something and is there a work around for SQL Server 2000 databases?

Filters & Parameters pane missing in MDX Query Designer

I'm developing my first Analysis Server report in SRS. Everything was going well when I discovered the filters & parameters pane was missing in the MDX Query Designer in data mode. I have my columns and rows but need to drag a dimension into the filters & parameters pane in MDX Query Designer and set a filter and parameter on it. Any idea how to make it viewable?

I may have made a discovery why I am not seeing the filter and parameter pane in the MDX Query Designer in SQL Server Business Intelligence Development Studio.The filter and parameter pane does appear in the MDX Query Designer in SQL Server Business Intelligence Development Studio on a report created from a SQL Server 2005 database, but not on a SQL Server 2000 database. Am I on to something and is there a work around for SQL Server 2000 databases?

Filtering records between 2 dates for the report(Using CR 9.2)

HI,
I am developing this application which stores purchases requisitions information from the user in a d/b.I have to generate reports based on the user inputted dates.The reports should list all the records between the 2 dates which the user inputs at run time.
This is the code I have provided to do the filtering of records for the CR.
********************************************************
MsgBox "Please enter 2 dates in between which to generate the Cost allocation Report", vbOKOnly + vbInformation, "Printing Reports.."
date1 = InputBox("Please enter the starting date for the Report!", "Cost Allocation Report")
date2 = InputBox("please enter the ending date for the Report!", "Cost Allocation Report")
date1 = CDate(MakeDateFormated(date1, "/"))
date2 = CDate(MakeDateFormated(date2, "/"))
Dim str3
str3 = "{purchase.invno}<> '" & invnonil & "' AND {purchase.podate} >= '" & date1 & "' AND {purchase.podate} <= '" & date2 & "'"
CrystalReport6.RecordSelectionFormula = str3
CrystalReport6.PrintOut True, 1
CrystalReport6.DiscardSavedData
********************************************************
Date1 and date 2 are of type Date.
When this code is run,I get an error message which says that "a Date-Time is required here" for the line giving the Record selection formula.
When I checked the CR in the designer,the datatype of the field "podate" is shown as Date-Time instead of just Date,even though,it is of type date in the Database.
I have tried deleting the field and adding it again to the report.It still shows datatype to be Date-Time.
How can I change this?
Please help me as I am very very new to this area.
Thank you for your time.Your help is highly appreciated.
Lakshmi VinayTry this

str3 = "{purchase.invno}<> '" & invnonil & "' AND {purchase.podate} >= #" & date1 & "# AND {purchase.podate} <= #" & date2 & "#"