Help again edit parameters from MDB to ADP

I need to convert this from Jet SqL to SQL Server. Moving out of an MDB to an ADP some of the queries I need to change over, I inherited this database and the bosses want all databases moved over from MDB to ADP.

SELECT Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Action Type], Count([Main Table].[Action Type]) AS [CountOfAction Type]
FROM [Main Table]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Action Type]
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the Month and the Year]));SELECT Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Action Type], Count([Main Table].[Action Type]) AS [CountOfAction Type]
FROM [Main Table]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Action Type]
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the Month and the Year]));

1. For FORMAT$, look in SQL BOL for Convert. Something like Convert(Varchar(7), [Main Table].[Date]

2. For your parameter ([Enter the Month and Year]), you will likely have to create the query as a stored procedure with an input parameter (or two). Something like:

CREATE PROC spMyProc (@.MonthYear varchar(7) )

AS

SELECT ...
FROM
WHERE
MyDateString = @.MonthYear

You would have to validate that @.MonthYear was valid (like mm/yyyy). I would not necessarily do it this way. I would use one of the following options:

1. Declare the incoming parameter as a datetime and calculate the first and last day of the month. Then use two local variables and a BETWEEN statement. This should render the best performance for large amounts of data.

2. or Declare the incoming parameter as two smallint variables. Then you could use DatePart function twice to get month and year.

Regards,

Hugh Scott

Help - Unable to Add Column to Report

I have inherited a report that appears to be too big for the VS designer.
The query is 664 lines long and every time I move to the Data tab, Reporting
Services locks up and stops responding. (I am using the generic query
designer)
The report runs fine, my problem is I need to add another data column to the
report. I did this by editing the RDL in a text editor but when I reload in
RS and got to the Layout tab to add the column to the report, it is not
listed in the Fields list. I tried to manually add it but I then get the
'Index out of bounds error'. So it seems the field list needs to be updated
by accessing the Data tab and then moving to the Layout or Preview tab but
every time I try to do this it locks up.
I thought I might try to temporarily remove part of the query so the field
list could be updated but the report has the columns scattered throughout so
I can't locate a large enough block of contiguous query data that does NOT
affect the fields list.
Is there any work around for this?
Many thanksHi Mike,
I'm not sure what the preview issue is, but if you are ok with editing the
RDL, you should be able to do it.
When you added the field to the RDL, did you add it to both the query and to
the fields collection?
The fields collection maps results returned from the query to fields you can
put on the report. If you added a column to the query in the RDL, makes
sure you add a field element (under datasets/fields) that matches the column
name. If you did both of those and still have the problem then I don't
know.
I hope that helps!
Chris
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OO$4Noz3FHA.4080@.tk2msftngp13.phx.gbl...
>I have inherited a report that appears to be too big for the VS designer.
>The query is 664 lines long and every time I move to the Data tab,
>Reporting Services locks up and stops responding. (I am using the generic
>query designer)
> The report runs fine, my problem is I need to add another data column to
> the report. I did this by editing the RDL in a text editor but when I
> reload in RS and got to the Layout tab to add the column to the report, it
> is not listed in the Fields list. I tried to manually add it but I then
> get the 'Index out of bounds error'. So it seems the field list needs to
> be updated by accessing the Data tab and then moving to the Layout or
> Preview tab but every time I try to do this it locks up.
> I thought I might try to temporarily remove part of the query so the field
> list could be updated but the report has the columns scattered throughout
> so I can't locate a large enough block of contiguous query data that does
> NOT affect the fields list.
> Is there any work around for this?
> Many thanks
>