Passing DateTime Parameter to MDX Query

HI All,
Looking for a little help with this issue: I currently have an MDX
query which is accepting 2 parameters for a date range. The query is
as follows:
SELECT NON EMPTY { [Measures].[Line Item Sales Amount] } ON COLUMNS
FROM ( SELECT ( STRTOMEMBER(@.FromDateDateForReporting, CONSTRAINED) :
STRTOMEMBER(@.ToDateDateForReporting, CONSTRAINED) ) ON COLUMNS FROM
[KIT Retail Sales]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
The parameters pull their respective values from a dataset populated
with this query:
WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Date For
Reporting].CURRENTMEMBER.MEMBER_CAPTION' MEMBER
[Measures].[ParameterValue] AS '[Date].[Date For
Reporting].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel]
AS '[Date].[Date For Reporting].CURRENTMEMBER.LEVEL.ORDINAL' SELECT
{[Measures].[ParameterCaption], [Measures].[ParameterValue],
[Measures].[ParameterLevel]} ON COLUMNS , [Date].[Date For
Reporting].ALLMEMBERS ON ROWS FROM [KIT Retail Sales]
In the Report Parameters window, I would like these parameters to be
DateTime datatypes so my users would get the handy Calendar control.
The only way I can get them to work now is by setting them to string
datatype. This works, but the dropdown list is populated with
thousands of date strings (in the form 01/01/2005, 01/02/2005, etc...)
which makes it difficult for users.
Whenver I attempt to make them DateTime datatypes, I get the error:
"The Property 'ValidValues' of the report parameter
'FromDateDateForReporting' doesn't have the expected type" when I run
the report.
My question is: How can I do the conversion to get the datetime back
to a string to use in the first MDX query above?Quick followup: In the report parameters window, if I choose to make
the available values "Non-queried", I can see the calendar control on
the preview page. However when choosing dates for the range, and then
clicking "view report", I get the following error:
The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
function have been violated.|||Guys-
This appears to work fine. However, now go add a column to your main query
(the one with the parameter) and now your report parameter is trashed because
SSRS decided it needed to refresh it. Anybody know of a way to tell it to
leave that parameter alone?
"Bret Updegraff" wrote:
> This is the method from my class file. It is in C# so if you are using
> VB.net or if you are putting this in the Code tab under Report-->
> Properties you will need to convert to VB.NET
> public string ParseDateToMDX( DateTime sDate )
> {
> string year = sDate.Year.ToString();
> string month = sDate.Month.ToString();
> string day = sDate.Day.ToString();
> StringBuilder MDXValue = new StringBuilder();
> if (month.Length == 1)
> {
> month = "0" + month; //pad month
> }
> if (day.Length == 1)
> {
> day = "0" + day; //pad day
> }
> MDXValue.Append("[PostingDate].[Date].&[").Append(year.ToString()).Append("-");
> MDXValue.Append(month.ToString()).Append("-");
> MDXValue.Append(day.ToString()).Append("T00:00:00]");
> return MDXValue.ToString();
> }
> Hope this helps
> "dvdastor@.yahoo.com" <dvdastor@.yahoo.com> wrote in message
> news:1137680384.850501.248380@.g49g2000cwa.googlegroups.com:
> > Quick followup: In the report parameters window, if I choose to make
> > the available values "Non-queried", I can see the calendar control on
> > the preview page. However when choosing dates for the range, and then
> > clicking "view report", I get the following error:
> > The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
> > function have been violated.
>
> --
> Bret Updegraff, MCAD,MCSD,MCDBA
> Microsoft MVP - SQL Server
> Crowe Chizek and Company LLC
> President - Indianapolis Professional Association for SQL Server
> Join our SQL Server Community http;//www.IndyPASS.org
> 317.208.2538 - FAX (317.706.2660) -BUpdegraff@.CroweChizek.com
>|||I hear this may be fixed in SP1:
http://prologika.com/CS/forums/thread/1069.aspx
"FurmanGG" wrote:
> Guys-
> This appears to work fine. However, now go add a column to your main query
> (the one with the parameter) and now your report parameter is trashed because
> SSRS decided it needed to refresh it. Anybody know of a way to tell it to
> leave that parameter alone?
> "Bret Updegraff" wrote:
> > This is the method from my class file. It is in C# so if you are using
> > VB.net or if you are putting this in the Code tab under Report-->
> > Properties you will need to convert to VB.NET
> >
> > public string ParseDateToMDX( DateTime sDate )
> > {
> > string year = sDate.Year.ToString();
> > string month = sDate.Month.ToString();
> > string day = sDate.Day.ToString();
> > StringBuilder MDXValue = new StringBuilder();
> >
> > if (month.Length == 1)
> > {
> > month = "0" + month; //pad month
> > }
> > if (day.Length == 1)
> > {
> > day = "0" + day; //pad day
> > }
> >
> > MDXValue.Append("[PostingDate].[Date].&[").Append(year.ToString()).Append("-");
> > MDXValue.Append(month.ToString()).Append("-");
> > MDXValue.Append(day.ToString()).Append("T00:00:00]");
> >
> > return MDXValue.ToString();
> > }
> >
> > Hope this helps
> >
> > "dvdastor@.yahoo.com" <dvdastor@.yahoo.com> wrote in message
> > news:1137680384.850501.248380@.g49g2000cwa.googlegroups.com:
> >
> > > Quick followup: In the report parameters window, if I choose to make
> > > the available values "Non-queried", I can see the calendar control on
> > > the preview page. However when choosing dates for the range, and then
> > > clicking "view report", I get the following error:
> > > The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
> > > function have been violated.
> >
> >
> > --
> > Bret Updegraff, MCAD,MCSD,MCDBA
> > Microsoft MVP - SQL Server
> > Crowe Chizek and Company LLC
> > President - Indianapolis Professional Association for SQL Server
> > Join our SQL Server Community http;//www.IndyPASS.org
> > 317.208.2538 - FAX (317.706.2660) -BUpdegraff@.CroweChizek.com
> >
> >

Passing Date to Stored procedure as variable

Thanks in advance for your help!!!!!
I have created a stored procedure that uses a date range for paramenters. As long as I hardcode the dates in (3/21/03, 3/25/03) I get no errors. As soon as I replace the dates with the variable name and try to run the sp, I get an error message that it can't convert string to date.

It is my plan to call this sp from a web page and pass the date paramenters where they look like this 3/21/03

Here is what I have
@.BegDate Smalldatetime,
@.EndDate Smalldatetime
....
....
Where EntryDate BETWEEN '@.BegDate' AND '@.EndDate'

Could someone please help me understand what I am missing?

Thanks,
Leeyou are searching where EntryDate is between the string '@.BegDate' and the string '@.EndDate' not the values contained in @.BegDate and @.EndDate.

use Where EntryDate BETWEEN @.BegDate AND @.EndDate|||Thanks so much!!!!!! ' '

Passing DataSet to MS Access Report

I have many MS Access reports that process recordsets obtained from a MySQL database, based on a user-selected date range. This uses VBA and input boxes. I'm now creating .aspx pages to get the user input via the web, and am successful in creating a DataSet. My .aspx.vb code includes using Automation to open the Access report in Snapshot Viewer (DoCmd.OutputTo). How do I pass the DataSet to MS Access to replace using recordsets?

My VBA code in Access used to be this:

Code Snippet

Dim ws As Workspace
Dim strConnection As String
Dim dbs As Database

Dim rst_chg As Recordset

Set ws = DBEngine.Workspaces(0)
strConnection = "ODBC;DSN=xxx;DATABASE=xxx;" _
& "SERVER=10.1.144.xxx;" _

& "UID=xxx;PWD=xxx;PORT=xxx;OPTION=0;" _

& "STMT=set wait_timeout=100000;;"

Set dbs = ws.OpenDatabase("", True, True, strConnection)

Set rst_chg = dbs.OpenRecordset("SELECT ...")

'process the recordset ...

I'm thinking I should be able to eliminate most of this code and Set rst_chg = DataSet. I've been successful with using WriteXml in the .aspx.vb page and Application.ImportXML in my VBA to pass the data using XML, but this writes to the hard drive, and also creates a database in Access (overhead I would rather not have to deal with). Again, is there a way to open the DataSet directly in my VBA code?

Thanks,

Guy Rivers

By DataSet you mean ado.net object? If so, I'm afraid there is no way to directly access a managed object from a native VBA script.

Passing DataSet to MS Access Report

I have many MS Access reports that process recordsets obtained from a MySQL database, based on a user-selected date range. This uses VBA and input boxes. I'm now creating .aspx pages to get the user input via the web, and am successful in creating a DataSet. My .aspx.vb code includes using Automation to open the Access report in Snapshot Viewer (DoCmd.OutputTo). How do I pass the DataSet to MS Access to replace using recordsets?

My VBA code in Access used to be this:

Code Snippet

Dim ws As Workspace
Dim strConnection As String
Dim dbs As Database

Dim rst_chg As Recordset

Set ws = DBEngine.Workspaces(0)
strConnection = "ODBC;DSN=xxx;DATABASE=xxx;" _
& "SERVER=10.1.144.xxx;" _

& "UID=xxx;PWD=xxx;PORT=xxx;OPTION=0;" _

& "STMT=set wait_timeout=100000;;"

Set dbs = ws.OpenDatabase("", True, True, strConnection)

Set rst_chg = dbs.OpenRecordset("SELECT ...")

'process the recordset ...

I'm thinking I should be able to eliminate most of this code and Set rst_chg = DataSet. I've been successful with using WriteXml in the .aspx.vb page and Application.ImportXML in my VBA to pass the data using XML, but this writes to the hard drive, and also creates a database in Access (overhead I would rather not have to deal with). Again, is there a way to open the DataSet directly in my VBA code?

Thanks,

Guy Rivers

By DataSet you mean ado.net object? If so, I'm afraid there is no way to directly access a managed object from a native VBA script.

Pass Date Range from VB to CR

Hi All,

i m Currently using VB,CR 9 and MS-Access

i have Create Cr report,

in my Vb form two text boxes Start date and end date,Simply i just want to display my report between two dates..

how can i do this..

AnyoneCan help me...

Thanx in Advance

Regards,
SabinaI am glad that you ask this question because I have the same problem and wanted to ask this to. I hope that someone can help us.

Greetings, Sjaaaf|||Create 2 global variables in module.bas
Public gstrFrom as String
Public gstrTo as String

Let say your form frmPrint.frm has textboxes txtFrom.text and txtTo.text
Put inside command button Print

Private Sub cmdPrint_Click()
gstrFrom = Trim(txtFrom.text)
gstrTo = Trim(txtTo.text)

Dim RptViewer As New frmRPrint 'frmRPrint.frm has a CRViewer object
RptViewer.Show
End Sub

Inside frmRPrint code section:

Option Explicit

Public Report As New ProdReport 'ProdReport.dsr is a Designer file

Private Sub Form_Load()
Dim adoc As ADODB.Command
Dim strSQL As String
Dim conn As ADODB.Connection

Me.Left = (Screen.Width - Me.Width) / 2
Me.Top = (Screen.Height - Me.Height) / 2

Screen.MousePointer = vbHourglass

Set adoc = New ADODB.Command
Set conn = New ADODB.Connection

conn.Open ConnString 'Connection String to MS Access Database
adoc.ActiveConnection = conn

strSQL = "SELECT ItemID, Quantity" & _
" FROM Production" & _
" WHERE ProdDate BETWEEN '" & gstrFrom & "' AND '" & gstrTo & "'"
adoc.CommandText = strSQL
adoc.CommandType = adCmdText

Report.Database.AddADOCommand conn, adoc
Report.AutoSetUnboundFieldSource crBMTName

Report.ItemID.SetUnboundFieldSource ("{ADO.ItemID}")
Report.Quantity.SetUnboundFieldSource ("{ADO.Quantity}")

conn.Close
Set conn = Nothing

CRViewer1.ReportSource = Report

CRViewer1.ViewReport
Screen.MousePointer = vbDefault

End Sub

Note:
You also can use
strSQL = "... WHERE ProdDate > '" & gstrFrom & "' AND ProdDate <'" & gstrTo & "'"
If your date is in date time format, concantenate the Todate with "11.59 pm" or "< gstrTo +1" so it will also include records on that day or else it will only display records until d/m/yy 12.00am|||hi

Thanx u very much..

If i get any error,than i will again ask u question.....again thnx

God with u always,

Regards,

Sabina|||hi

As u gave me Code of the passing Date range i did it

but when i use

Report.Database.AddADOCommand conn, adoc
Report.AutoSetUnboundFieldSource crBMTName

Report.ItemID.SetUnboundFieldSource ("{ADO.ItemID}")
Report.Quantity.SetUnboundFieldSource ("{ADO.Quantity}")

this codding lines

it not show me database,AutoSetUnboundFieldSource crBMTName like these properties

my report is a .dsr file...if i have Crystal report (.rpt file)than wha can i do...?

tell me plz what wil i do ,where Database etc. properties not display ...othertings u sais it is fine

Thanx in Advance

Regards,

Sabina|||Hi..

Instead of doing that one, try this one

XSTART = txtFrom.text
XEND = txtEnd.txt

Dim APP As New CRAXDRT.Application
Dim REPORT As CRAXDRT.Report

REPORT = APP.OpenReport("<your path>\<your reportfilename>.rpt")
REPORT.RecordSelectionFormula = "{<Table.Datefield>} >= #" & XSTART & "# AND {<Table.Datefield>} <= #" & XEND & "#"

Don't forget to Add the Crystal Report ActiveX Designer Run Time Library to your COM References ^_^|||Try this...

Place 2 Formula Fields in yr report and in the editor write "Date".

Then write the below code in yr VB codings and then execute the report.
CrystalReport1.Formulas(0) = "fdate='" & Format(DTPicker1.Value, "MMMM yyyy") & "'"
CrystalReport1.Formulas(1) = "tdate='" & Format(DTPicker2.Value, "MMMM yyyy") & "'"

Revert, if u still require assistance..

pass a value from form to report

I have a form on which the user enters a date range. I would like the date range to be displayed at the top of the report

like: For Period 08/01/2006 - 08/31/2006

Is it possible to pass a value from a form into a label in a report?

You can pass information to a report using parameters. The form fields must be named exactly like the report parameters. You could then set the value of the label to the parameter value passed in from your form.

I have enountered problems when a form contained server side .NET controls. (if there are 2 report parameters, there can only be 2 fields in your form... and .net adds 2 form fields automatically to handle post-back and state information) To get around this limitation I used querystring parameters instead of posting via a form. (there is also a parameter length limitation using this approach however)

http://msdn2.microsoft.com/en-us/library/ms153563.aspx