How to load data and not the hierarchy

[amybirc77]

Hi All,

I am importing data from SQL Server table, the date is in the MM/DD/YYYY Format. When I add it to the report it by default is loading as YEAR,qtr,month and day. Is there a way where it can just load as date insted?

Thanks

[jgeddes · Accepted solution]

You can change the behaviour of the date field by right clicking on the Date field when in the build pane and select date instead of date hierarchy.

[aniaquen232]

It sounds like your reporting tool is automatically interpreting the date field from your SQL Server table and breaking it down into separate components like year, quarter, month, and day. To load the date as a single unit instead of breaking it down into its hierarchical components, you can follow these steps:

1. Format Date in SQL Query: When fetching data from your SQL Server table, format the date field to be in a format that the reporting tool recognizes as a single date unit. For example, you can use the SQL CONVERT() function to format the date in a standard format like YYYY-MM-DD.

   SELECT CONVERT(varchar, your_date_column, 23) AS formatted_date FROM your_table;

2. Adjust Reporting Tool Settings: Check the settings or preferences in your reporting tool to see if there's an option to treat the date field as a single unit instead of breaking it down into components. Look for options related to date formatting, data types, or default behavior when loading date fields.

3. Explicitly Specify Date Field Type: If your reporting tool allows it, explicitly specify that the field from your SQL query is a date type. This can sometimes override the default behavior of breaking down the date into hierarchical components. Read more

[jgeddes · Accepted solution]

You can change the behaviour of the date field by right clicking on the Date field when in the build pane and select date instead of date hierarchy.

[amybirc77 · Accepted solution]

Thanks  that fixed it.

[aniaquen232]

Ok dear