Query

Hi,

objSqlCmd.Parameters.Add("@.Cnt", SqlDbType.Int).Direction = ParameterDirection.Output

What is the meaning of .(dot) direction. and ParameterDirection.Output

Thanking you

Abdul

SqlParameter.Direction property: Gets or sets a value that indicates whether the parameter is input-only, output-only, bidirectional, or a stored procedure return value parameter (from MSDN).

ParameterDirection: To set the value of Direction property.

Heres more on it:http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.direction.aspx

query

Is it possible to design a reporting services report so that you can choose
the parameters that you are after (i.e. selecting from drop downs, etc... to
pass parameters to the underlying sql query)?
ThanksYes it is.
Behold:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_interactive_v1_50fn.asp
"farshad" wrote:
> Is it possible to design a reporting services report so that you can choose
> the parameters that you are after (i.e. selecting from drop downs, etc... to
> pass parameters to the underlying sql query)?
> Thankssql

Queries against Analysis Server executed several times in SSRS

Using Reporting Services 2005 with SSAS 2005 I have a report working with
some parameters. In queries for the parameters some default values were
calculated. E.g. the current date. Then these values were used as input to
create mdx statements. Parameters!kdjfaj.value
When watching what is happening on the SSAS Server with SQL Profiler I
watched the queries executing SEVERAL times ("query begin" as event class).
Is this a normal behaviour of Reporting Services?
Or is it just a gag of the Profiler Tool to repeat the queries in time?
Or is this depending on the order of the datasets for the report? If this
would be the case - how could I change the order of execution of the dataset
queries?
Best regards,
StefoonI have a similar problem. I have a report created in Visual Studio
based on a querry that retrieves data.
Today however we found a need to count how many times rhis report was
printed. Piece of cake I thought just put an UPDATE query at the botom
of the querry that retrieves data.
tested my simple UPDATE Invoice.ReminderNo = Invoice.ReminderNo + 1 in
managment studio and it worked as i shoud.
But when i tried running the report it suddenly added 10. so I put in
another UPDATE that added one letter/execution to a text field. sure
anough 10 pretty A:s.
The query contains 5 separate datasets and is run 10 times when i
render my report. I find that to striking to be a coincidence. I
believe that the querry is run twice/dataset.
How many datasets do you have, how many times is the querry run?
On 1 Mar, 14:25, Stefoon23 <Stefoo...@.discussions.microsoft.com>
wrote:
> Using Reporting Services 2005 with SSAS 2005 I have a report working with
> some parameters. In queries for the parameters some default values were
> calculated. E.g. the current date. Then these values were used as input to
> create mdx statements. Parameters!kdjfaj.value
> When watching what is happening on the SSAS Server with SQL Profiler I
> watched the queries executingSEVERALtimes("querybegin" as event class).
> Is this a normal behaviour of Reporting Services?
> Or is it just a gag of the Profiler Tool to repeat the queries in time?
> Or is this depending on the order of the datasets for the report? If this
> would be the case - how could I change the order of execution of the dataset
> queries?
> Best regards,
> Stefoon|||Hi Markus,
the proportion is about the same. It seems that any query is executed 2
times.
I understand, that the parameter queries could run several times if you have
cascading picklists or something, but the query for the report's data?
Very mysterious.
Stefoon
"markus.rohsto@.gmail.com" wrote:
> I have a similar problem. I have a report created in Visual Studio
> based on a querry that retrieves data.
> Today however we found a need to count how many times rhis report was
> printed. Piece of cake I thought just put an UPDATE query at the botom
> of the querry that retrieves data.
> tested my simple UPDATE Invoice.ReminderNo = Invoice.ReminderNo + 1 in
> managment studio and it worked as i shoud.
> But when i tried running the report it suddenly added 10. so I put in
> another UPDATE that added one letter/execution to a text field. sure
> anough 10 pretty A:s.
> The query contains 5 separate datasets and is run 10 times when i
> render my report. I find that to striking to be a coincidence. I
> believe that the querry is run twice/dataset.
> How many datasets do you have, how many times is the querry run?
> On 1 Mar, 14:25, Stefoon23 <Stefoo...@.discussions.microsoft.com>
> wrote:
> > Using Reporting Services 2005 with SSAS 2005 I have a report working with
> > some parameters. In queries for the parameters some default values were
> > calculated. E.g. the current date. Then these values were used as input to
> > create mdx statements. Parameters!kdjfaj.value
> > When watching what is happening on the SSAS Server with SQL Profiler I
> > watched the queries executingSEVERALtimes("querybegin" as event class).
> > Is this a normal behaviour of Reporting Services?
> > Or is it just a gag of the Profiler Tool to repeat the queries in time?
> >
> > Or is this depending on the order of the datasets for the report? If this
> > would be the case - how could I change the order of execution of the dataset
> > queries?
> >
> > Best regards,
> > Stefoon
>
>

Queried parameters

Hello,

I need to be able to set the date parameters of a report dynamically when it is run based on system time. The problem I am having is being able to compare the dates (StartDate & EndDate) against [Service Date 1]. Essentially this report will only pull the current month's data.

The date fields being created with the GETDATE, DATEADD & DATEDIFF functions are working correctly. Do I need to create a separate dataset to be able to run the parameters automatically in the actual report?

Any help would be greatly appreciated!

SELECT TodaysDate =GetDate()-2,dbo.[Billing Detail].[Service Date 1], DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0) AS StartDate, DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0)) AS EndDate, dbo.[Billing Detail].Billing, dbo.[Billing Detail].Chart, dbo.[Billing Detail].Item,
dbo.[Billing Detail].[Sub Item], dbo.Patient.[Patient Code], dbo.Patient.[Patient Type], dbo.[Billing Header].Charges, dbo.Practice.Name
FROM dbo.[Billing Detail] INNER JOIN
dbo.Patient ON dbo.[Billing Detail].Chart = dbo.Patient.[Chart Number] INNER JOIN
dbo.[Billing Header] ON dbo.[Billing Detail].Billing = dbo.[Billing Header].Billing CROSS JOIN
dbo.Practice
WHERE (dbo.[Billing Detail].Item = 0) AND (dbo.[Billing Detail].[Sub Item] = 0) AND (dbo.[Billing Detail].[Service Date 1] Between StartDate AND EndDate

Phorest,

You should be able to add the parameters to your query. If you are going against SQL Server, you can replace your parameters with @.StartDate AND @.EndDate. Then in the properies of the dataset, you can assign those parameters to Parameters!StartDate.Value and Parameters!EndDate.Value, respectively.

Jessica

|||

Thanks for your reply!

OK,

I think what I need to do is write the expression as a non-queried default value. However when I paste in what I know works in SQL Management Studio it returns an error "Name 'mm' is not declared"

<@.StartDate> =DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0)

<@.EndDate> =DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0))

I tried putting an integer after DATEADD(mm, X , 102 DATEDIFF... but i can't get beyond intellisense. How can I fix my expression to work with Reporting Services?

What I need is to have expressions to choose the first day of the month to the last day of the same month compared to NOW()

|||

Apparently that is the trick to use non-queried default values as an expression, However what I posted yesterday will not work as an expression due to the expressions limitations in SSRS:

<@.StartDate> =DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0)

<@.EndDate> =DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0))

Now I am using:

<@.StartDate> =DATEADD("D", -30, NOW())

<@.EndDate> =DATEADD("D", 1, NOW())

After much searching and experimentation I can get this to work well, but it isn't exactly what I want. Does any one have any tips as to being able to write the expression to select the first day of the current month and last day of the month?

It seems to be just beyond my grasp at this time...

Thanks!

|||

Phorest,

I'm afraid I misunderstood what you're trying to do. If you want a query that returns rows where the [Billing Detail].[Service Date 1] is between the start and the end of the current month, you can do that all in SQL.

It would look something similar to:

WHERE dbo.[Billing Detail].[Service Date 1]

BETWEEN dateadd(mm, datediff(mm,0,getdate()), 0)

AND dateadd(ms,-3,dateadd(mm, datediff(m,0,getdate() ) + 1, 0))

Does that work for you?

Jessica

|||

I'll have to try that in the SQL, though I was more after an expression more as a datetime datatype so it picks all the dates in the current month only and the user can then adjust the parameter manually after the initial running of the report if they so choose.

Thanks!

|||

I found what I was looking for here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1581230&SiteID=1

In the Report Parameters properties I set the DataType to DateTime and using the Default Values, Non-Queried radio button set the expressions like the following:

@.StartDate =DateSerial(Year(NOW()), Month(NOW)) +0,1) gives me the first date of the current month.

@.EndDate =DateSerial(Year(NOW()), Month(NOW)) +1,0) gives me the last date of the current month.

All is wellnow!

Queried Parameter Null Values

Hello,

For queried parameters, I need to have the option of using a null value as well. Is there a way to include a null value, or do I have to include it in the query that is returned?

Thanks.

Use DBNull.Value in .NET as the parameter value.

If this does not help you, please clarify your question.

|||

I have a parameter that pulls back data to it. But, this field should be optional, so the user should also be able to select null as another value. How do I allow the user to select null? I can't figure this out... By the way, I tried setting the default value to System.DBNull.Value, and that didn't work... nothing returns, but I did try the query and the query did work. For some reason, this is with RS...

|||

What are you using to send the command to the database? An ObjectDataSource, custom ADO.NET, etc. If you can tell us what you're using, how the users are currently selecting values, etc, it would help.

|||

I'm using a Reporting Services DataSet, standard report... The query works fine, and it has been tested. I can't figure out how to send a null value to the stored procedure parameter. That is what the problem is.

|||

Hello,

Here is the answer that I've found: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1789337&SiteID=1