filter in Reporting Services: Best way?

Hi there,
I'm a newbe to reportig services and try to make up a financial report:
store currentYear pastYear Delta DeltaIn%PastYear
I tried for several hours with the table control:
- Gain the date with a query from ssas
- set a parameter for the year
- put several tables on the report and set a filter to the year dimension
The problem I have with this approach is:
- Formatting: I would prefer one table
- I don't know how to calculate the deltas (currentYear-pastYear)
Regards
TomPressed the post button to fast...
My questions are:
- Can I set a filter on a column in a table control?
- Can I calculate in a table control a value, that derives from other
columns in the same or different tables?
- Or is it better to use different controls (like the matrix or textboxes)?
Please help me - I have a deadline and running out of time!
Thanks in advance,
Tom
"Tomilee" wrote:
> Hi there,
> I'm a newbe to reportig services and try to make up a financial report:
> store currentYear pastYear Delta DeltaIn%PastYear
> I tried for several hours with the table control:
> - Gain the date with a query from ssas
> - set a parameter for the year
> - put several tables on the report and set a filter to the year dimension
> The problem I have with this approach is:
> - Formatting: I would prefer one table
> - I don't know how to calculate the deltas (currentYear-pastYear)
> Regards
> Tom|||Hi,
Just answering to your questions.
1. Can I set a filter on a column in a table control?
No for the full row you can set filter.
2. Can I calculate in a table control a value, that derives from other
columns in the same or different tables?
Yes very much. using <Expressions> option.
Regards
Amarnath
"Tomilee" wrote:
> Pressed the post button to fast...
> My questions are:
> - Can I set a filter on a column in a table control?
> - Can I calculate in a table control a value, that derives from other
> columns in the same or different tables?
> - Or is it better to use different controls (like the matrix or textboxes)?
> Please help me - I have a deadline and running out of time!
> Thanks in advance,
> Tom
> "Tomilee" wrote:
> > Hi there,
> >
> > I'm a newbe to reportig services and try to make up a financial report:
> >
> > store currentYear pastYear Delta DeltaIn%PastYear
> >
> > I tried for several hours with the table control:
> > - Gain the date with a query from ssas
> > - set a parameter for the year
> > - put several tables on the report and set a filter to the year dimension
> >
> > The problem I have with this approach is:
> > - Formatting: I would prefer one table
> > - I don't know how to calculate the deltas (currentYear-pastYear)
> >
> > Regards
> >
> > Tom|||Tomilee,
to get store currentYear pastYear Delta DeltaIn%PastYear
you should do all of this in the MDX query ( you mentioned using SSAS)...
To get the PastYear info, look into the ParallelPeriod function in MDX. the
Delta is current - ParallelPeriod, etc
in answer to your other questions... you can NOT put a filter on a textbox,
but you can filter on a group, table, etc... But what you can do is use a
conditional if on the textbox expression... ie
IIF (Fields!fieldname.Value > 5,truepart, falsepart)
Using this you might can get the effect of a filter..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Tomilee" wrote:
> Hi there,
> I'm a newbe to reportig services and try to make up a financial report:
> store currentYear pastYear Delta DeltaIn%PastYear
> I tried for several hours with the table control:
> - Gain the date with a query from ssas
> - set a parameter for the year
> - put several tables on the report and set a filter to the year dimension
> The problem I have with this approach is:
> - Formatting: I would prefer one table
> - I don't know how to calculate the deltas (currentYear-pastYear)
> Regards
> Tom|||Thanks for your Help! Tom
"Wayne Snyder" wrote:
> Tomilee,
> to get store currentYear pastYear Delta DeltaIn%PastYear
> you should do all of this in the MDX query ( you mentioned using SSAS)...
> To get the PastYear info, look into the ParallelPeriod function in MDX. the
> Delta is current - ParallelPeriod, etc
> in answer to your other questions... you can NOT put a filter on a textbox,
> but you can filter on a group, table, etc... But what you can do is use a
> conditional if on the textbox expression... ie
> IIF (Fields!fieldname.Value > 5,truepart, falsepart)
> Using this you might can get the effect of a filter..
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Tomilee" wrote:
> > Hi there,
> >
> > I'm a newbe to reportig services and try to make up a financial report:
> >
> > store currentYear pastYear Delta DeltaIn%PastYear
> >
> > I tried for several hours with the table control:
> > - Gain the date with a query from ssas
> > - set a parameter for the year
> > - put several tables on the report and set a filter to the year dimension
> >
> > The problem I have with this approach is:
> > - Formatting: I would prefer one table
> > - I don't know how to calculate the deltas (currentYear-pastYear)
> >
> > Regards
> >
> > Tom