SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS Default dates if statement


SSRS Default dates if statement

Author
Message
cmw 66135
cmw 66135
Old Hand
Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 Visits: 82
Hi all,

Is there a way I can make reports default to a date via some kind of iff statement? What I am trying to achieve is if the from date is before 31st march in the current year then default the date to 1st april in the previous year, and if the to date is less than 31st march in the current year then default to the currunt year for the 31st march but if it is after 31st march in the current year then default to next years 31st March

I hope this makes sense

Thanks
Chris
Sue_H
Sue_H
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33176 Visits: 9441
cmw 66135 - Thursday, August 31, 2017 8:27 AM
date is before 31st march in the current year then default the date to 1st april in the previous year,
date is less than 31st march in the current year then default to the currunt year for the 31st march
after 31st march in the current year then default to next years 31st March

I just formatted that to list your conditions because it's not making sense to me. I'm not seeing something.
For the first two conditions what is the difference between a date before March 31 of current year and a date less than March 31 of the current year?

Sue




cmw 66135
cmw 66135
Old Hand
Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 Visits: 82
Hi Sue

Sorry my error, I should have proof read it before posting it. It made sense in my head before i typed it

Maybe its better to put it like this

So today is the 4th sept 2017 our reporting dates for a year fall between the 1st april 2017 (for example) in this year to the 31st march 2018 in the following year. What I want to put as a start date is 1st april as a default and the year to stay the same between that period even though when we move to 2018 on the 1st Jan I want the date to remain 01/04/2017 until we reach the 1st of april 2018 when I want the reporting year to change to 2018 for the same 1st April date, This will be the same for the end/to date that it will default to 2018 until we go to the 1st April 2018 when the year will change to 2019. Im also guessing that the condition will have to so if greater than or equals to for the start date and if less than or equals to the end date so it shows the reporting data for the 1st april and the 31st March.

Hope this makes more sense than my original post Smile

Thanks
Chris
HappyGeek
HappyGeek
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6789 Visits: 4383
I have used this in the past: Report Server Date Range and simply set the default to current financial year, the list available is easily amended to suit your own needs.

...
cmw 66135
cmw 66135
Old Hand
Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 Visits: 82
Wow just read that and I am not going to lie, that went way over my head. I wouldn't even know where to begin with code like that or where to put it.

The only other this I can see is I dont want the user to have any choice of date ranfe, these are solicitors that although are great with what they do, dates and figures go over their heads. It needs to be a click and view report which is why I was thinking that I could force the dates via the filter function.

Thank you for your reply though Happygeek

Thanks
Chris
HappyGeek
HappyGeek
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6789 Visits: 4383
cmw 66135 - Monday, September 4, 2017 12:49 AM
Wow just read that and I am not going to lie, that went way over my head. I wouldn't even know where to begin with code like that or where to put it.

The only other this I can see is I dont want the user to have any choice of date ranfe, these are solicitors that although are great with what they do, dates and figures go over their heads. It needs to be a click and view report which is why I was thinking that I could force the dates via the filter function.

Thank you for your reply though Happygeek

Thanks
Chris


I used this in a law firm, although I cut it down to current calendar year, current financial year and the four financial quarters, which was of value to the finance department.

Other than creating the initial view you should be able to do everything within SSRS when you build the report. If necessary add the default value and hide the parameter:

...
cmw 66135
cmw 66135
Old Hand
Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 Visits: 82
Thanks again Happygeek

So how would I add that to the query already running. I assume it isnt a simple copy and paste at the end of this?

<SemanticQuery xmlns="http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:qd="http://schemas.microsoft.com/sqlserver/2004/11/semanticquerydesign" xmlns:rb="http://schemas.microsoft.com/sqlserver/2004/11/reportbuilder">
<Hierarchies>
<Hierarchy>
<BaseEntity>
<!--Split Bills-->
<EntityID>G5df2da9f-e6f3-43a2-a6c8-d88c8857604d</EntityID>
</BaseEntity>
<Groupings>
<Grouping Name="Bill Ref">
<Expression Name="Bill Ref">
<AttributeRef>
<!--Bill Ref-->
<AttributeID>G92133802-8b8a-425f-b185-3066817a2a74</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
<Grouping Name="Bill Date">
<Expression Name="Bill Date">
<AttributeRef>
<!--Bill Date-->
<AttributeID>Gb4518022-6454-47d2-841c-f7008328a5fc</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
<Grouping Name="Split Amount">
<Expression Name="Split Amount">
<AttributeRef>
<!--Split Amount-->
<AttributeID>G261cd954-8eac-4c36-b249-a932709239b7</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
<Grouping Name="Fee Earner">
<Expression Name="Fee Earner">
<Path>
<RolePathItem>
<!--Fee Earner-->
<RoleID>G3b9e5d1d-c168-43de-a834-db4d347834b6</RoleID>
</RolePathItem>
</Path>
<EntityRef>
<!--Fee Earner-->
<EntityID>G71272ce9-9a2d-4d98-b4df-aabc29c9bf70</EntityID>
</EntityRef>
</Expression>
<Details>
<Expression Name="Fee Earner Reference">
<AttributeRef>
<!--Reference-->
<AttributeID>Gd3a85ca0-53db-492f-8c3c-4ac769bd8896</AttributeID>
</AttributeRef>
</Expression>
<Expression Name="Fee Earner Full Name">
<AttributeRef>
<!--Full Name-->
<AttributeID>G17390b58-2537-4cc5-815b-608d0eec39ec</AttributeID>
</AttributeRef>
</Expression>
</Details>
</Grouping>
<Grouping Name="Matter Ref">
<Expression Name="Matter Ref">
<Path>
<RolePathItem>
<!--Matter-->
<RoleID>G788cd775-559a-4c6f-806f-9c5cf4a67251</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--Matter Ref-->
<AttributeID>G7c418d9c-9193-4539-aac8-fee8f4772b4a</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
<Grouping Name="Matter Description">
<Expression Name="Matter Description">
<Path>
<RolePathItem>
<!--Matter-->
<RoleID>G788cd775-559a-4c6f-806f-9c5cf4a67251</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--Matter Description-->
<AttributeID>G79aa13c4-ded3-4d3b-8bfe-4d0721853b45</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
<Grouping Name="Fee Ref">
<Expression Name="Fee Ref">
<AttributeRef>
<!--Fee Ref-->
<AttributeID>G250dbd02-c28c-4ae5-9a3d-a94c38e2ff3c</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
<Grouping Name="Fee Earner Reference1">
<Expression Name="Fee Earner Reference1">
<Path>
<RolePathItem>
<!--Matter-->
<RoleID>G788cd775-559a-4c6f-806f-9c5cf4a67251</RoleID>
</RolePathItem>
<RolePathItem>
<!--Fee Earner-->
<RoleID>G5fe09b0b-806d-4823-a680-3c0fcb339a89</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--Reference-->
<AttributeID>Gd3a85ca0-53db-492f-8c3c-4ac769bd8896</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
</Groupings>
<Filter>
<Expression Name="filter">
<Literal>
<DataType>Boolean</DataType>
<Value>true</Value>
</Literal>
<CustomProperties>
<CustomProperty Name="qd:Filter" />
<CustomProperty Name="qd:ContextEntityID">
<Value xsi:type="xsd:string">G5df2da9f-e6f3-43a2-a6c8-d88c8857604d</Value>
</CustomProperty>
<CustomProperty Name="qd:AutoChangeBaseEntity" />
<CustomProperty Name="qdBigGrinesign">
<Value xsi:type="xsd:string">expr1</Value>
</CustomProperty>
</CustomProperties>
</Expression>
</Filter>
</Hierarchy>
</Hierarchies>
<MeasureGroups>
<MeasureGroup>
<BaseEntity>
<!--Split Bills-->
<EntityID>G5df2da9f-e6f3-43a2-a6c8-d88c8857604d</EntityID>
</BaseEntity>
<Measures>
<Expression Name="Total Client Balance">
<Function>
<FunctionName>Aggregate</FunctionName>
<Arguments>
<Expression>
<Path>
<RolePathItem>
<!--Matter-->
<RoleID>G788cd775-559a-4c6f-806f-9c5cf4a67251</RoleID>
</RolePathItem>
<RolePathItem>
<!--Matter Financial Balances-->
<RoleID>Ge216ebe0-3147-4bcf-8261-37b65da60cd3</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--Total Client Balance-->
<AttributeID>Gedc5039d-d9a0-44ed-9102-9ef92fc69361</AttributeID>
</AttributeRef>
</Expression>
</Arguments>
</Function>
</Expression>
<Expression Name="Total Office Balance">
<Function>
<FunctionName>Aggregate</FunctionName>
<Arguments>
<Expression>
<Path>
<RolePathItem>
<!--Matter-->
<RoleID>G788cd775-559a-4c6f-806f-9c5cf4a67251</RoleID>
</RolePathItem>
<RolePathItem>
<!--Matter Financial Balances-->
<RoleID>Ge216ebe0-3147-4bcf-8261-37b65da60cd3</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--Total Office Balance-->
<AttributeID>Gd55a38ed-3aa8-4f51-82f7-673933694f8c</AttributeID>
</AttributeRef>
</Expression>
</Arguments>
</Function>
</Expression>
<Expression Name="Total Deposit Balance">
<Function>
<FunctionName>Aggregate</FunctionName>
<Arguments>
<Expression>
<Path>
<RolePathItem>
<!--Matter-->
<RoleID>G788cd775-559a-4c6f-806f-9c5cf4a67251</RoleID>
</RolePathItem>
<RolePathItem>
<!--Matter Financial Balances-->
<RoleID>Ge216ebe0-3147-4bcf-8261-37b65da60cd3</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--Total Deposit Balance-->
<AttributeID>Gc345d8ca-ded4-43f1-81af-9e3429f691bd</AttributeID>
</AttributeRef>
</Expression>
</Arguments>
</Function>
</Expression>
<Expression Name="Total Unbilled Disbs Balance">
<Function>
<FunctionName>Aggregate</FunctionName>
<Arguments>
<Expression>
<Path>
<RolePathItem>
<!--Matter-->
<RoleID>G788cd775-559a-4c6f-806f-9c5cf4a67251</RoleID>
</RolePathItem>
<RolePathItem>
<!--Matter Financial Balances-->
<RoleID>Ge216ebe0-3147-4bcf-8261-37b65da60cd3</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--Total Unbilled Disbs Balance-->
<AttributeID>G62b48ba7-8995-4632-9ae3-ce52b0b323e3</AttributeID>
</AttributeRef>
</Expression>
</Arguments>
</Function>
</Expression>
<Expression Name="Bills Total Outstanding Amount">
<Function>
<FunctionName>Aggregate</FunctionName>
<Arguments>
<Expression>
<Path>
<RolePathItem>
<!--Matter-->
<RoleID>G788cd775-559a-4c6f-806f-9c5cf4a67251</RoleID>
</RolePathItem>
<RolePathItem>
<!--Bills-->
<RoleID>Gf4569edc-0402-4ed9-9765-cd1fb558584a</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--Total Outstanding Amount-->
<AttributeID>G48e338c0-1df6-4259-b38a-6754d73e8b47</AttributeID>
</AttributeRef>
</Expression>
</Arguments>
</Function>
</Expression>
</Measures>
</MeasureGroup>
</MeasureGroups>
</SemanticQuery>
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search