Finding End of Month

Does anyone have a VB function they could pass along for
converting a date to the end of the last month?
I have a report that will be run for the period ending the prior
month and want the parameter date to default as follows:
Assum they are running it on 12/21, date should default to 11/30/2005
Thanks!"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
@.TK2MSFTNGP15.phx.gbl:
> Does anyone have a VB function they could pass along for
> converting a date to the end of the last month?
> I have a report that will be run for the period ending the prior
> month and want the parameter date to default as follows:
> Assum they are running it on 12/21, date should default to 11/30/2005
> Thanks!
>
>
DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)|||Outstanding, many thanks!
"Asher_N" <compguy666@.hotmail.com> wrote in message
news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)|||Asher
I am using timedate format; can it be set to the end of the month 11:59 pm?
"Asher_N" <compguy666@.hotmail.com> wrote in message
news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)|||If your using 2005, you use my misc date project at
http://channel9.msdn.com/ShowPost.aspx?PostID=147390
Then use GetEndOfMonth(DateTime) UDF. This gives you the last day and
hour:minute:second:millisecond of that day (i.e. adding one more ms would
move the date to start of next day.)
--
William Stacey [MVP]
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:%235%236n4nBGHA.216@.TK2MSFTNGP15.phx.gbl...
> Asher
> I am using timedate format; can it be set to the end of the month 11:59
> pm?
> "Asher_N" <compguy666@.hotmail.com> wrote in message
> news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
>> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
>>
>> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)
>|||William
Thanks for your reply but I an still on RS 2000
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:eNviqHpBGHA.3896@.TK2MSFTNGP09.phx.gbl...
> If your using 2005, you use my misc date project at
> http://channel9.msdn.com/ShowPost.aspx?PostID=147390
> Then use GetEndOfMonth(DateTime) UDF. This gives you the last day and
> hour:minute:second:millisecond of that day (i.e. adding one more ms would
> move the date to start of next day.)
> --
> William Stacey [MVP]
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:%235%236n4nBGHA.216@.TK2MSFTNGP15.phx.gbl...
>> Asher
>> I am using timedate format; can it be set to the end of the month 11:59
>> pm?
>> "Asher_N" <compguy666@.hotmail.com> wrote in message
>> news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
>> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
>>
>> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)
>>
>|||I missed the VB function need. Here is a c# method you can convert:
public static DateTime GetEndOfMonth(DateTime date)
{
int daysInMonth = DateTime.DaysInMonth(date.Year, date.Month);
return new DateTime(date.Year, date.Month, daysInMonth, 23, 59,
59, 999);
}
private void button9_Click(object sender, EventArgs e)
{
DateTime date = DateTime.Parse("12/22/2005"); // Any date.
DateTime lastMth = date.AddMonths(-1);
DateTime endOfLast = GetEndOfMonth(lastMth);
Console.WriteLine("End of Last Month:" + endOfLast);
}
--
William Stacey [MVP]
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:unxvFipBGHA.3496@.TK2MSFTNGP11.phx.gbl...
> William
> Thanks for your reply but I an still on RS 2000
> "William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
> news:eNviqHpBGHA.3896@.TK2MSFTNGP09.phx.gbl...
>> If your using 2005, you use my misc date project at
>> http://channel9.msdn.com/ShowPost.aspx?PostID=147390
>> Then use GetEndOfMonth(DateTime) UDF. This gives you the last day and
>> hour:minute:second:millisecond of that day (i.e. adding one more ms would
>> move the date to start of next day.)
>> --
>> William Stacey [MVP]
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
>> news:%235%236n4nBGHA.216@.TK2MSFTNGP15.phx.gbl...
>> Asher
>> I am using timedate format; can it be set to the end of the month 11:59
>> pm?
>> "Asher_N" <compguy666@.hotmail.com> wrote in message
>> news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
>> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
>>
>> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)
>>
>>
>

Find Windows/SQL login used by RS

What is the 'supported' way to find out which Windows/SQL login has been
configured for RS to use when it connects to the report server database?
RSconfig.exe doesn't seem to have a read-only option.
LinchiThere isn't one. You can find out by looking at the RSExec role in the
database your using and checking it's members. If you see machine\ASPNET
and builtin\Network Service then you're using the service credentials.
Otherwise you'll see either a domain account or a sql login.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Linchi Shea" <linchi_shea@.NOSPAM.ml.com> wrote in message
news:%23liDwCoeEHA.3124@.TK2MSFTNGP09.phx.gbl...
> What is the 'supported' way to find out which Windows/SQL login has been
> configured for RS to use when it connects to the report server database?
> RSconfig.exe doesn't seem to have a read-only option.
> Linchi
>|||Thanks! I guess that's a workaround. There really should be a page in Report
Manager to list all the Report Server configurations.
Linchi
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
news:eY4Sf$oeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> There isn't one. You can find out by looking at the RSExec role in the
> database your using and checking it's members. If you see machine\ASPNET
> and builtin\Network Service then you're using the service credentials.
> Otherwise you'll see either a domain account or a sql login.
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Linchi Shea" <linchi_shea@.NOSPAM.ml.com> wrote in message
> news:%23liDwCoeEHA.3124@.TK2MSFTNGP09.phx.gbl...
> > What is the 'supported' way to find out which Windows/SQL login has been
> > configured for RS to use when it connects to the report server database?
> > RSconfig.exe doesn't seem to have a read-only option.
> >
> > Linchi
> >
> >
>

Find text in collapsed drilldown report

I have a drilldown report. Without opening a drilldown item, is there a way
to search for a text string using the "Find" link when viewing a report? If
not, what would be the best recommendation to accomplish this.Hello Parker,
Without opening the drilldown item, you could not use the "Find" button to
find the text in the drilldown item.
This is by design because the find operation only search the HTML which is
appearanced.
My suggestion is that add a parameter in the report to control to expanded
all the items.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Find out if a row is subtotal or not?

How do I check if a row is a subtotal or not? I have a report where one
column shows Average hour rate. Right now, the subtotal line shows the
Average column as a subtotal of all the other averages... So I'd like to
have a statement that does something like
if row is a subtitle row then
show nothing (or something else)
else
show the Average value
end if
I bet it's trivial, but I can't figure it out. Please help. :)
Kaisa M. LindahlDid you look at the InScope function? It will allow you to distinguish
between cells in subtotals and cells in the groupings. More information on
InScope is available at:
http://msdn.microsoft.com/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
E.g. =iif(InScope("ColumnGroup"), Avg(Fields!F1.Value), Nothing)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:%23glpsPj1EHA.3468@.TK2MSFTNGP14.phx.gbl...
> How do I check if a row is a subtotal or not? I have a report where one
> column shows Average hour rate. Right now, the subtotal line shows the
> Average column as a subtotal of all the other averages... So I'd like to
> have a statement that does something like
> if row is a subtitle row then
> show nothing (or something else)
> else
> show the Average value
> end if
> I bet it's trivial, but I can't figure it out. Please help. :)
> Kaisa M. Lindahl
>

Find objects not in use.

Hi!
Anyone know of any report or dynamic view where i can find objects
that has never been executed or accessed? I would like to make som
clean up in our database and that would be a really good start :)
Br, OlaHi
check this site
http://www.sqlservercentral.com/columnists/lPeysakhovich/2582.asp
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Stuck" <stuckish@.gmail.com> wrote in message
news:1182412428.873704.81340@.q75g2000hsh.googlegroups.com...
> Hi!
> Anyone know of any report or dynamic view where i can find objects
> that has never been executed or accessed? I would like to make som
> clean up in our database and that would be a really good start :)
> Br, Ola
>

Find objects not in use.

Hi!
Anyone know of any report or dynamic view where i can find objects
that has never been executed or accessed? I would like to make som
clean up in our database and that would be a really good start
Br, OlaHi
check this site
http://www.sqlservercentral.com/col...hovich/2582.asp
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Stuck" <stuckish@.gmail.com> wrote in message
news:1182412428.873704.81340@.q75g2000hsh.googlegroups.com...
> Hi!
> Anyone know of any report or dynamic view where i can find objects
> that has never been executed or accessed? I would like to make som
> clean up in our database and that would be a really good start
> Br, Ola
>

Final Part - Min() Function

I have the final part of my report to complete. Yesterday I posted the most confusing set of threads ever on a public forum. So to cut the confusion, I am starting this thread again as now I know exactly what I need.

I have two tables each of which have a function that gathers data from them based around a set of parameters. This is then manipulated by a third function and the results of which are passed into a Stored Procedure. Here is the T-SQL syntax used for the SP;

SELECT fnWTRalldataReport.Areacode, fnWTRalldataReport.siteref, fnWTRalldataReport.estatename, fnWTRalldataReport.Securitised,

fnWTRalldataReport.unitref, fnWTRalldataReport.unittype, fnWTRalldataReport.unittype_count, fnWTRalldataReport.tenantname,

fnWTRalldataReport.tenantstatus, fnWTRalldataReport.tenantstatus_count, fnWTRalldataReport.unitstatus, fnWTRalldataReport.unitstatus_count,

fnWTRalldataReport.floortotal, fnWTRalldataReport.floortotocc, fnWTRalldataReport.floorspaceperc, fnWTRalldataReport.initialvacarea,

fnWTRalldataReport.initialvacnet, fnWTRalldataReport.TotalRent, fnWTRalldataReport.NetRent, fnWTRalldataReport.FinalRtLsincSC,

fnWTRalldataReport.rentrolldiscperc, fnWTRalldataReport.netrentpersqft, fnWTRalldataReport.ErvTot, fnWTRalldataReport.tenancyterm,

fnWTRalldataReport.landact, fnWTRalldataReport.datadate, fnWTRalldataReport.div_mgr, fnWTRalldataReport.portfolio_mgr,

fnWTRalldataReport.propcat, fnWTRalldataReport.budgeted_net_rent, fnWTRalldataReport.budgeted_occupancy,

fnWTRbudgetdata_1.budgeted_net_rent AS budget_rent, fnWTRbudgetdata_1.budgeted_occupancy AS budget_occ

FROM dbo.fnWTRalldataReport(@.dt_src_date, @.chr_div, @.vch_portfolio_no, @.vch_prop_cat) AS fnWTRalldataReport LEFT OUTER JOIN

dbo.fnWTRbudgetdata(@.dt_src_date) AS fnWTRbudgetdata_1 ON fnWTRalldataReport.siteref = fnWTRbudgetdata_1.site_ref

The result of this SQL places a value for budget_rent and budget_occ against every row that the 1st function's result set. What I want to achieve is that where the site_ref is equal in both functions results, I want it to place the budget_rent & budget_occ value against the first row only of each site_ref only.

To explain briefly the structure. Table one has various fields including site_ref and unit_ref. There are many unit_ref's per site_ref in this table. Table 2 has only site_ref and budget info. Someone yesterday suggested that I could achieve this my using something along the lines of the Min() function e.g. Min(unit_ref).

Could someone please elaborate on this for me. I have gone through my SQL book and read about min() and also BOL, but I can't quite work the syntax out to put the budget info against only one line per site based around the lowest unit_ref per site_ref.

This might seem confusing, but it is easier to read than the other thread I assure you.

Regards

If this is just for display, then I would suggest you just let the UI sort through this.

If not, you could try using temp tables to hold the results of the function calls:

select *, cast(0 as bit) as maxUnitRef
into #FNWTRALLDATAREPORT
from dbo.FNWTRALLDATAREPORT(parms)

... same with the other, no added column though

Then

UPDATE #FNWTRALLDATAREPORT
SET maxUnitRef = 1
WHERE unitRef = (select max(unitRef)
from #FNWTRALLDATAREPORT as f2
where f2.siteRef = #FNWTRALLDATAREPORT.siteRef)

Then in the final output just:

case when maxUnitRef = 1 then #FNWTRALLDATAREPORT.BUDGETED_NET_RENT else null end as BUDGETED_NET_RENT..

Does this make any sense? You might also be able to fabricate the maxUnitRef column in the function. Either way it is far easier to do this in the UI, instead of using SQL.

|||

Thank you for this, I will sort through it now. Yes in answer to your question, it is purely for display purposes only on the report.

Regards

Final Model

I am creating a report model and have to keep going back and forth between VS2005 and Report Builder to see what the "Final" report is going to look like. I am using folders to group together some really nasty list of fields, denormalizing lookup tables, etc. Is there any way to see this without having to reload Report Builder each time? I have thought about an XSLT for the SMDL, but that is some seriously ugly XML (especially with the denormalizations).

R

If you leave RB open, create a new report based on some other model, then create a report on the model you're working on again, it will clear the cache and you will see your changes.

Inside Model Designer, you can edit the Expression property of any attribute, and the formula dialog will have a model explorer showing all your most recent changes. However, it will be rooted on the entity to which that attribute belongs, so it may take some navigation to see changes you've made to another entity.

Hope that helps!

Filters in combination with the aggregate function

Hi all,
I have a report and use the aggregate function in a table. Now I want
to show the top 50 from that table. But when I use a filter on a group
and use the top N function i get a error message:
The aggregate function "Aggregate" cannot be used in a report that
contains any filters.
How can i filter the top 50 with aggregate functions in a report?
Regards,
Robbert
HollandIn your dataset you can use your select statement.
Select Top(50) Field1
From Sometable
Group By Field1
That will set the whole dataset to show only your top 50. THen you can
graph it or do whatever you want with those records.
<robbert_visscher@.hotmail.com> wrote in message
news:1155037378.956732.155110@.i3g2000cwc.googlegroups.com...
> Hi all,
> I have a report and use the aggregate function in a table. Now I want
> to show the top 50 from that table. But when I use a filter on a group
> and use the top N function i get a error message:
> The aggregate function "Aggregate" cannot be used in a report that
> contains any filters.
> How can i filter the top 50 with aggregate functions in a report?
> Regards,
> Robbert
> Holland
>|||My query is on a Cube. If I use top 50 it is not working. Now i have
used topcount to select the top 50 but is it not possible to do een top
50 in a report with aggregate functions? If not, why?

Filters for dataset from Parameter - How to ignore if the parameter is NULL ?

Hi,

I am starting to use reporting services and I created a report that takes 4 parameters for a Filter on the dataset.

The idea is the create snapshot nightly and then display the report filtered based on parameters to users.

I'd like that the filter be ignored for a parameter if the parameter is passed as NULL,

Right now I defined it this way :

Left =Fields!RegionCode.Value Operator = Right =IIF(IsNothing(Parameters!RegionCode.Value),Fields!RegionCode.Value,Parameters!RegionCode.Value)

I do this for the 4 parameters that are passed, if they are null, make then equals to the field.

I was wondering if there is a way to ignore the whole parameter all together, I would guess it'll be faster to execute.

Thanks

One way would be to use this in your stored procedure or query, with coalesce...

eg. AND COALESCE(@.RegionCode,[RegionCode]) = [RegionCode]

That way you're not bringing the data into the report and then filtering it out, you're filtering at the source.

|||

No I don't want to do that,

The idea is the get ALL the data in a snapshot nightly then apply filters on the snapshot when we display to the users.

The result is that the DB will not get hammered everytime a report is run but only once at night.

So the coalesce will not work.

|||

I guess you could do something then with the left side of the filter and an if statement?

If the parameter on the left side is null, then set it to 1, otherwise set it to the field. If the parameter on the right side is null, set it to 1, otherwise set it to the parameter. Then 1=1 and it will ignore the parameter.

Not sure if this will work but it sounds faster than comparing field to field in theory.

cheers,

Andrew

|||

Andrew - T4G wrote:

I guess you could do something then with the left side of the filter and an if statement?

If the parameter on the left side is null, then set it to 1, otherwise set it to the field. If the parameter on the right side is null, set it to 1, otherwise set it to the parameter. Then 1=1 and it will ignore the parameter.

Not sure if this will work but it sounds faster than comparing field to field in theory.

cheers,

Andrew

Thanks, I'll try something like that.

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?

Filters - need help

I am having issues applying a filter to my report.
I have one dataset that i want to filter and display serveral times in one
report.
The query is complicated and somewhat slow(yes i am working on optimizing
it), so i want to pull it once, and then use it in 4 different tables in my
report.
The first table, will be unfiltered, the others will be filtered by one of
the fields in my dataset that returns an int datatype, none of the values are
null.
I tried following the instructions here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
but it doesn't work.
When i preview the report, it returns no build errors or warnings, but i get
this dialog:
Processing Errors
An error has occurred during the report processing.
The processing of filter expression for the table 'table1' cannot be
performed. The comparison failed. Please check the data type returned by the
filter expression.
----
What am i doing wrong?
This is driving me nuts...
--
RyanWhat is the filtering expression?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
>I am having issues applying a filter to my report.
> I have one dataset that i want to filter and display serveral times in one
> report.
> The query is complicated and somewhat slow(yes i am working on optimizing
> it), so i want to pull it once, and then use it in 4 different tables in
> my
> report.
> The first table, will be unfiltered, the others will be filtered by one of
> the fields in my dataset that returns an int datatype, none of the values
> are
> null.
> I tried following the instructions here:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> but it doesn't work.
> When i preview the report, it returns no build errors or warnings, but i
> get
> this dialog:
> Processing Errors
> An error has occurred during the report processing.
> The processing of filter expression for the table 'table1' cannot be
> performed. The comparison failed. Please check the data type returned by
> the
> filter expression.
> ----
> What am i doing wrong?
> This is driving me nuts...
> --
> Ryan|||Expression: Fields!ProcessStage.Value>=80
I think i found part of my issue.
When you select a field from the drop down in Table
Properties->Filters/Expressions, it leaves a "=" at the beginning. By
removing that, i no longer get the error. But it does not seem to filter the
data either...
"Lev Semenets [MSFT]" wrote:
> What is the filtering expression?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> >I am having issues applying a filter to my report.
> >
> > I have one dataset that i want to filter and display serveral times in one
> > report.
> > The query is complicated and somewhat slow(yes i am working on optimizing
> > it), so i want to pull it once, and then use it in 4 different tables in
> > my
> > report.
> > The first table, will be unfiltered, the others will be filtered by one of
> > the fields in my dataset that returns an int datatype, none of the values
> > are
> > null.
> >
> > I tried following the instructions here:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > but it doesn't work.
> >
> > When i preview the report, it returns no build errors or warnings, but i
> > get
> > this dialog:
> > Processing Errors
> > An error has occurred during the report processing.
> > The processing of filter expression for the table 'table1' cannot be
> > performed. The comparison failed. Please check the data type returned by
> > the
> > filter expression.
> > ----
> > What am i doing wrong?
> > This is driving me nuts...
> > --
> > Ryan
>
>|||Ok;
I figured i out, picky little application:
Expression: =Fields!ProcessStage.Value
Operator: >=Value: =80
Thanks...
"Ryan" wrote:
> Expression: Fields!ProcessStage.Value>=80
> I think i found part of my issue.
> When you select a field from the drop down in Table
> Properties->Filters/Expressions, it leaves a "=" at the beginning. By
> removing that, i no longer get the error. But it does not seem to filter the
> data either...
>
> "Lev Semenets [MSFT]" wrote:
> > What is the filtering expression?
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> > news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> > >I am having issues applying a filter to my report.
> > >
> > > I have one dataset that i want to filter and display serveral times in one
> > > report.
> > > The query is complicated and somewhat slow(yes i am working on optimizing
> > > it), so i want to pull it once, and then use it in 4 different tables in
> > > my
> > > report.
> > > The first table, will be unfiltered, the others will be filtered by one of
> > > the fields in my dataset that returns an int datatype, none of the values
> > > are
> > > null.
> > >
> > > I tried following the instructions here:
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > > but it doesn't work.
> > >
> > > When i preview the report, it returns no build errors or warnings, but i
> > > get
> > > this dialog:
> > > Processing Errors
> > > An error has occurred during the report processing.
> > > The processing of filter expression for the table 'table1' cannot be
> > > performed. The comparison failed. Please check the data type returned by
> > > the
> > > filter expression.
> > > ----
> > > What am i doing wrong?
> > > This is driving me nuts...
> > > --
> > > Ryan
> >
> >
> >|||I tore my hair out for about 24 hours until I found this post.. Thanks
Ryan - likely I would be tearing my hair out for days more had I not
happened on this. The nest question is when will someone from MS get
around to addressing this "feature."|||Thanks for the post Ryan...you bet the application is really picky...I hope
the new version is really fine tuned in such areas
"Ryan" wrote:
> Ok;
> I figured i out, picky little application:
> Expression: =Fields!ProcessStage.Value
> Operator: >=> Value: =80
> Thanks...
> "Ryan" wrote:
> > Expression: Fields!ProcessStage.Value>=80
> >
> > I think i found part of my issue.
> > When you select a field from the drop down in Table
> > Properties->Filters/Expressions, it leaves a "=" at the beginning. By
> > removing that, i no longer get the error. But it does not seem to filter the
> > data either...
> >
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> > > What is the filtering expression?
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >
> > > "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> > > news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> > > >I am having issues applying a filter to my report.
> > > >
> > > > I have one dataset that i want to filter and display serveral times in one
> > > > report.
> > > > The query is complicated and somewhat slow(yes i am working on optimizing
> > > > it), so i want to pull it once, and then use it in 4 different tables in
> > > > my
> > > > report.
> > > > The first table, will be unfiltered, the others will be filtered by one of
> > > > the fields in my dataset that returns an int datatype, none of the values
> > > > are
> > > > null.
> > > >
> > > > I tried following the instructions here:
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > > > but it doesn't work.
> > > >
> > > > When i preview the report, it returns no build errors or warnings, but i
> > > > get
> > > > this dialog:
> > > > Processing Errors
> > > > An error has occurred during the report processing.
> > > > The processing of filter expression for the table 'table1' cannot be
> > > > performed. The comparison failed. Please check the data type returned by
> > > > the
> > > > filter expression.
> > > > ----
> > > > What am i doing wrong?
> > > > This is driving me nuts...
> > > > --
> > > > Ryan
> > >
> > >
> > >|||It is so helpful. Thanks!!!!!
"Ryan" wrote:
> Ok;
> I figured i out, picky little application:
> Expression: =Fields!ProcessStage.Value
> Operator: >=> Value: =80
> Thanks...
> "Ryan" wrote:
> > Expression: Fields!ProcessStage.Value>=80
> >
> > I think i found part of my issue.
> > When you select a field from the drop down in Table
> > Properties->Filters/Expressions, it leaves a "=" at the beginning. By
> > removing that, i no longer get the error. But it does not seem to filter the
> > data either...
> >
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> > > What is the filtering expression?
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >
> > > "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> > > news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> > > >I am having issues applying a filter to my report.
> > > >
> > > > I have one dataset that i want to filter and display serveral times in one
> > > > report.
> > > > The query is complicated and somewhat slow(yes i am working on optimizing
> > > > it), so i want to pull it once, and then use it in 4 different tables in
> > > > my
> > > > report.
> > > > The first table, will be unfiltered, the others will be filtered by one of
> > > > the fields in my dataset that returns an int datatype, none of the values
> > > > are
> > > > null.
> > > >
> > > > I tried following the instructions here:
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > > > but it doesn't work.
> > > >
> > > > When i preview the report, it returns no build errors or warnings, but i
> > > > get
> > > > this dialog:
> > > > Processing Errors
> > > > An error has occurred during the report processing.
> > > > The processing of filter expression for the table 'table1' cannot be
> > > > performed. The comparison failed. Please check the data type returned by
> > > > the
> > > > filter expression.
> > > > ----
> > > > What am i doing wrong?
> > > > This is driving me nuts...
> > > > --
> > > > Ryan
> > >
> > >
> > >|||Thanks Ryan!
I still have issue with the following combination
Expression: =Fields!X.Value
Operator: =Value: =0
(Operator: < or > worked fine)
I got around this problem by casting X in my sql statement to a varchar(1)
and setting -> Value: 0
"Ryan" wrote:
> Ok;
> I figured i out, picky little application:
> Expression: =Fields!ProcessStage.Value
> Operator: >=> Value: =80
> Thanks...
> "Ryan" wrote:
> > Expression: Fields!ProcessStage.Value>=80
> >
> > I think i found part of my issue.
> > When you select a field from the drop down in Table
> > Properties->Filters/Expressions, it leaves a "=" at the beginning. By
> > removing that, i no longer get the error. But it does not seem to filter the
> > data either...
> >
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> > > What is the filtering expression?
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >
> > > "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> > > news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> > > >I am having issues applying a filter to my report.
> > > >
> > > > I have one dataset that i want to filter and display serveral times in one
> > > > report.
> > > > The query is complicated and somewhat slow(yes i am working on optimizing
> > > > it), so i want to pull it once, and then use it in 4 different tables in
> > > > my
> > > > report.
> > > > The first table, will be unfiltered, the others will be filtered by one of
> > > > the fields in my dataset that returns an int datatype, none of the values
> > > > are
> > > > null.
> > > >
> > > > I tried following the instructions here:
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > > > but it doesn't work.
> > > >
> > > > When i preview the report, it returns no build errors or warnings, but i
> > > > get
> > > > this dialog:
> > > > Processing Errors
> > > > An error has occurred during the report processing.
> > > > The processing of filter expression for the table 'table1' cannot be
> > > > performed. The comparison failed. Please check the data type returned by
> > > > the
> > > > filter expression.
> > > > ----
> > > > What am i doing wrong?
> > > > This is driving me nuts...
> > > > --
> > > > Ryan
> > >
> > >
> > >

filters

does anyone know if it is possible to add a filter to a report that is
passed as a user-defined parameter? i know i can do this by modifying the
sql that generates the report, but i'd like to do this inside of the report
so as to not hit the database each time the user selects a different filter.On Sep 24, 2:18 pm, "JTL" <j...@.clickstreamtech.com> wrote:
> does anyone know if it is possible to add a filter to a report that is
> passed as a user-defined parameter? i know i can do this by modifying the
> sql that generates the report, but i'd like to do this inside of the report
> so as to not hit the database each time the user selects a different filter.
If I'm understanding you correctly, you should be able to pass a
report parameter value to a table/matrix control via: right-clicking
the table/matrix control -> select Properties -> select the Filters
tab -> below Expression, enter an expression similar to the following:
=Parameters!ParamName.Value
And then compare it to the Value accordingly.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Filtering through Matrix or groups in matrix

I have a report thats fully functional. I just want to add a filter so that my "Visits" field only displays the Visits per day that are less then 6. When i try to filter out the matrix or the group, it tells me the datatypes are different . Something about int32. Its in a matrix, but i have seen this happen in a table too, so i guessing thats not the problem. I just want to be able to display the information for Sales Reps with less then 6 Visits. Any help, will be greatlly appreciated.

Filter work fine with "=" sign so what u need to do is

fileter condition < =6 (In expression use =6 instead of plain 6)

or you may try using =cint(6)

Filtering the parameter content

Hello,

I have built a report using a Cube (and not a relational database).

I have a date as a parameter and I would like to filter its content: the parameter goes from 1900 to 2090 and I would like the user to see only from 2006 to 2090.

Can you help me by giving me tricks to do it ? There may have several ways of doing it but I can't find them.

Thank you in advance !

Alexis

If you always want it to start from 2006( not change to 2007 to 2090 when it is 2008) it′s easy in MDX:

SELECT {[Time].[Year].&[2006] : NULL } on rows, bla bla bla.

This will make it take every member from the time dimension from 2006 up til the end, in your case 2090.|||

Thank you, it works perfectly !

It would be interesting to make dynamic date range according to the actual year but.... next time !

Filtering the dataset

Hi All,
I have reports which return employee information. I want the report to have
an optional 3 parameters representing Entity, Business Unit and Cost Centre.
If any of these parameters(or a combination is used) I want to filter the
returned dataset to display only relevant rows.
Before anyone suggests using the parameters in the stored procedure, forget
it! The stored procedure is complex enough that it has to figure out the
users access level...so I'd rather filter after the fact.
I've hit a brick wall with this one, so if anyone has any fantastic ideas,
Id love to hear them.
Kind Regards,
Terry PinoFiltering the dataset can be done with the filter tab in the dataset.
U can try with the filters - Expression & Value. using custom expressions.
To help U out, I need more details abt the parameters and returned columns
with which u filter the dataset.
"Terry" wrote:
> Hi All,
> I have reports which return employee information. I want the report to have
> an optional 3 parameters representing Entity, Business Unit and Cost Centre.
> If any of these parameters(or a combination is used) I want to filter the
> returned dataset to display only relevant rows.
> Before anyone suggests using the parameters in the stored procedure, forget
> it! The stored procedure is complex enough that it has to figure out the
> users access level...so I'd rather filter after the fact.
> I've hit a brick wall with this one, so if anyone has any fantastic ideas,
> Id love to hear them.
> Kind Regards,
> Terry Pino|||Thanks Chans,
I worked it out after posting this article.Basically I ended up with 3
filter values each one something like this:
iif(Parameter.value1 is null,"",Fields.value1) = iif(Parameter.value1 is
null,"",Parameter.value1)
Getting around my problem of the filter parameter being null.
Always helps to talk about things;-)
Cheers,
Terry
"Chans" wrote:
> Filtering the dataset can be done with the filter tab in the dataset.
> U can try with the filters - Expression & Value. using custom expressions.
> To help U out, I need more details abt the parameters and returned columns
> with which u filter the dataset.
>
> "Terry" wrote:
> > Hi All,
> >
> > I have reports which return employee information. I want the report to have
> > an optional 3 parameters representing Entity, Business Unit and Cost Centre.
> > If any of these parameters(or a combination is used) I want to filter the
> > returned dataset to display only relevant rows.
> > Before anyone suggests using the parameters in the stored procedure, forget
> > it! The stored procedure is complex enough that it has to figure out the
> > users access level...so I'd rather filter after the fact.
> > I've hit a brick wall with this one, so if anyone has any fantastic ideas,
> > Id love to hear them.
> >
> > Kind Regards,
> > Terry Pino|||Did you filter the report or the dataset?
"Terry" wrote:
> Thanks Chans,
> I worked it out after posting this article.Basically I ended up with 3
> filter values each one something like this:
> iif(Parameter.value1 is null,"",Fields.value1) = iif(Parameter.value1 is
> null,"",Parameter.value1)
> Getting around my problem of the filter parameter being null.
> Always helps to talk about things;-)
> Cheers,
> Terry
> "Chans" wrote:
> > Filtering the dataset can be done with the filter tab in the dataset.
> > U can try with the filters - Expression & Value. using custom expressions.
> >
> > To help U out, I need more details abt the parameters and returned columns
> > with which u filter the dataset.
> >
> >
> > "Terry" wrote:
> >
> > > Hi All,
> > >
> > > I have reports which return employee information. I want the report to have
> > > an optional 3 parameters representing Entity, Business Unit and Cost Centre.
> > > If any of these parameters(or a combination is used) I want to filter the
> > > returned dataset to display only relevant rows.
> > > Before anyone suggests using the parameters in the stored procedure, forget
> > > it! The stored procedure is complex enough that it has to figure out the
> > > users access level...so I'd rather filter after the fact.
> > > I've hit a brick wall with this one, so if anyone has any fantastic ideas,
> > > Id love to hear them.
> > >
> > > Kind Regards,
> > > Terry Pino

Filtering results after execution and multivalue parameter questions

Hi. 2 questions:

1.
I'm trying to execute a report, then allow the user filter the results using a value list from the report dataset in a dropdown list. When I create a filtering parameter, i can't figure out how to make it work on results after the report is executed.

2.
How can I make a multivalue parameter default to (Select All)?

thanks!
#1: You can simulate the "user-driven filtering" by using drillthrough actions to the report itself and setting the filter parameter accordingly. In any case, it will require the report to be reprocessed because filtering data may impact many other things (such as pagination).

#2: Make the valid values list and the default value both query-based and use
the same dataset column.

-- Robert

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 & "#"