SSRS Default dates if statement

Question

SSRS Default dates if statement

cmw 66135

Ten Centuries

Points: 1272
More actions
August 31, 2017 at 8:27 am

#323332

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

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 Ten Centuries Points: 1272 More actions · Answer 2

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 🙂

Thanks
Chris

HappyGeek SSCoach Points: 18729 More actions · Answer 3

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 Ten Centuries Points: 1272 More actions · Answer 4

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 SSCoach Points: 18729 More actions · Answer 5

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 Ten Centuries Points: 1272 More actions · Answer 6

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>
   
   <EntityID>G5df2da9f-e6f3-43a2-a6c8-d88c8857604d</EntityID>
  </BaseEntity>
  <Groupings>
   <Grouping Name="Bill Ref">
    <Expression Name="Bill Ref">
    <AttributeRef>
     
     <AttributeID>G92133802-8b8a-425f-b185-3066817a2a74</AttributeID>
    </AttributeRef>
    </Expression>
   </Grouping>
   <Grouping Name="Bill Date">
    <Expression Name="Bill Date">
    <AttributeRef>
     
     <AttributeID>Gb4518022-6454-47d2-841c-f7008328a5fc</AttributeID>
    </AttributeRef>
    </Expression>
   </Grouping>
   <Grouping Name="Split Amount">
    <Expression Name="Split Amount">
    <AttributeRef>
     
     <AttributeID>G261cd954-8eac-4c36-b249-a932709239b7</AttributeID>
    </AttributeRef>
    </Expression>
   </Grouping>
   <Grouping Name="Fee Earner">
    <Expression Name="Fee Earner">
    <Path>
     <RolePathItem>
      
      <RoleID>G3b9e5d1d-c168-43de-a834-db4d347834b6</RoleID>
     </RolePathItem>
    </Path>
    <EntityRef>
     
     <EntityID>G71272ce9-9a2d-4d98-b4df-aabc29c9bf70</EntityID>
    </EntityRef>
    </Expression>
    <Details>
    <Expression Name="Fee Earner Reference">
     <AttributeRef>
      
      <AttributeID>Gd3a85ca0-53db-492f-8c3c-4ac769bd8896</AttributeID>
     </AttributeRef>
    </Expression>
    <Expression Name="Fee Earner Full Name">
     <AttributeRef>
      
      <AttributeID>G17390b58-2537-4cc5-815b-608d0eec39ec</AttributeID>
     </AttributeRef>
    </Expression>
    </Details>
   </Grouping>
   <Grouping Name="Matter Ref">
    <Expression Name="Matter Ref">
    <Path>
     <RolePathItem>
      
      <RoleID>G788cd775-559a-4c6f-806f-9c5cf4a67251</RoleID>
     </RolePathItem>
    </Path>
    <AttributeRef>
     
     <AttributeID>G7c418d9c-9193-4539-aac8-fee8f4772b4a</AttributeID>
    </AttributeRef>
    </Expression>
   </Grouping>
   <Grouping Name="Matter Description">
    <Expression Name="Matter Description">
    <Path>
     <RolePathItem>
      
      <RoleID>G788cd775-559a-4c6f-806f-9c5cf4a67251</RoleID>
     </RolePathItem>
    </Path>
    <AttributeRef>
     
     <AttributeID>G79aa13c4-ded3-4d3b-8bfe-4d0721853b45</AttributeID>
    </AttributeRef>
    </Expression>
   </Grouping>
   <Grouping Name="Fee Ref">
    <Expression Name="Fee Ref">
    <AttributeRef>
     
     <AttributeID>G250dbd02-c28c-4ae5-9a3d-a94c38e2ff3c</AttributeID>
    </AttributeRef>
    </Expression>
   </Grouping>
   <Grouping Name="Fee Earner Reference1">
    <Expression Name="Fee Earner Reference1">
    <Path>
     <RolePathItem>
      
      <RoleID>G788cd775-559a-4c6f-806f-9c5cf4a67251</RoleID>
     </RolePathItem>
     <RolePathItem>
      
      <RoleID>G5fe09b0b-806d-4823-a680-3c0fcb339a89</RoleID>
     </RolePathItem>
    </Path>
    <AttributeRef>
     
     <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="qd:Design">
     <Value xsi:type="xsd:string">expr1</Value>
    </CustomProperty>
    </CustomProperties>
   </Expression>
  </Filter>
</Hierarchy>
</Hierarchies>
<MeasureGroups>
<MeasureGroup>
  <BaseEntity>
   
   <EntityID>G5df2da9f-e6f3-43a2-a6c8-d88c8857604d</EntityID>
  </BaseEntity>
  <Measures>
   <Expression Name="Total Client Balance">
    <Function>
    <FunctionName>Aggregate</FunctionName>
    <Arguments>
     <Expression>
      <Path>
      <RolePathItem>
       
       <RoleID>G788cd775-559a-4c6f-806f-9c5cf4a67251</RoleID>
      </RolePathItem>
      <RolePathItem>
       
       <RoleID>Ge216ebe0-3147-4bcf-8261-37b65da60cd3</RoleID>
      </RolePathItem>
      </Path>
      <AttributeRef>
      
      <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>
       
       <RoleID>G788cd775-559a-4c6f-806f-9c5cf4a67251</RoleID>
      </RolePathItem>
      <RolePathItem>
       
       <RoleID>Ge216ebe0-3147-4bcf-8261-37b65da60cd3</RoleID>
      </RolePathItem>
      </Path>
      <AttributeRef>
      
      <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>
       
       <RoleID>G788cd775-559a-4c6f-806f-9c5cf4a67251</RoleID>
      </RolePathItem>
      <RolePathItem>
       
       <RoleID>Ge216ebe0-3147-4bcf-8261-37b65da60cd3</RoleID>
      </RolePathItem>
      </Path>
      <AttributeRef>
      
      <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>
       
       <RoleID>G788cd775-559a-4c6f-806f-9c5cf4a67251</RoleID>
      </RolePathItem>
      <RolePathItem>
       
       <RoleID>Ge216ebe0-3147-4bcf-8261-37b65da60cd3</RoleID>
      </RolePathItem>
      </Path>
      <AttributeRef>
      
      <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>
       
       <RoleID>G788cd775-559a-4c6f-806f-9c5cf4a67251</RoleID>
      </RolePathItem>
      <RolePathItem>
       
       <RoleID>Gf4569edc-0402-4ed9-9765-cd1fb558584a</RoleID>
      </RolePathItem>
      </Path>
      <AttributeRef>
      
      <AttributeID>G48e338c0-1df6-4259-b38a-6754d73e8b47</AttributeID>
      </AttributeRef>
     </Expression>
    </Arguments>
    </Function>
   </Expression>
  </Measures>
</MeasureGroup>
</MeasureGroups>
</SemanticQuery>